How can I add balances to the transactions sheet? For instance, on the transactions sheet I would like to have a column with the running balance right beside the transactions with the current balance after that traction.
Is this something I can do manual, or is there a add on?
1 Like
I am not sure why the feature is not part of transactions sheet. It would very useful for always failed balance and balance history that never update to correct balance!
1 Like
Hi @bynon7262,
Each transaction row uses different accounts. When using running balances, thatās usually the running balance of a specific account. But the transactions sheet has multiple accounts, so Iām not sure how that would work.
Or are you just looking for the running balance for all transactions no matter what account they are coming from.
Perhaps you could share a little bit of information on this use case or what your goal is of getting this information?
Jon
1 Like
Hi,
Not the original author, but did have the same question. I am only using the transactions sheet for my primary checking account and would like to see my account balance after each transaction. Is that something that can be done in the Foundation template?
Just for a little more information, I would only be using this for one account. Thanks!
Hi @ocarranza and @bynon7262,
Would a solution work if there was a separate sheet where you could see all the transactions for the one account with a running balance? Or, is it important to you that you see the running total on the Transactions sheet?
There is a formula you could add to a new Column in the Transactions sheet that would create a running total from the top of the sheet to the bottom. Since the Transactions sheet gets sorted in reverse chronological order (newest on top), the formula is a bit backwards. However, if you re-sort the sheet, keeping row 1 on top and the oldest transactions under that, it would work.
The formula for the top row of the new column would be:
={"Running Balance";ArrayFormula(If(len(E2:E),(SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E)+0),))}
The formula assumes that the Amounts are in Column E. If not, change the Eās to the new Amount Column letter.
Also, in place of +0 a the end of the formula, you would need to add your starting balance of the account, if it wasnāt zero.
You could also make a new sheet that included your transaction information, but sort it by date chronologically. Then use a similar formula for a running balance column.
Jon
Hello, getting into this conversation if okay. A running balance sheet would be awesome. As a user coming off of Quicken where you had the Recurring Transactions we relied on the Balance column to assist with that. Could you not expand on the Lab item āAccount Filterā and incorporate a running balance there? Just my two cents. Thanks.
Trying to use this but keep getting missing parenthesis?
Hi @cgoneal,
There was an extra parenthesis at the end of the formula above. It should be:
={āRunning Balanceā;ArrayFormula(If(len(E2:E),(SUMIF(ROW(E2:E),"<="&ROW(E2:E),E2:E)+0),))}
Iām going to update the formula in the original post above some others wonāt have this issue.
Let us know if the new formula works for you.
Thanks,
Jon
Iām an almost 30 year Quicken user who finally gave up because the software is so buggy that it constantly corrupts my data. Iām currently using CountAbout but am starting to get frustrated with the difficulty of massaging data. Google Sheets just makes so much sense in this space. Iām a newbie here.
The thing I donāt think I can live without is running balances for each of my accounts. Iām not as much interested in my balance history as I am about my balance after a particular transaction. One of my primary use cases is making sure my accounting is matching my bank/credit cardā¦accounts. I just donāt see how that can be done without running balances at the account level.
Anyhow, just curious if anyone has attempted running balances at the account level.
Thanks,
Dave
Hi @davidmark,
Iām glad to hear you agree Google Sheets in the way to go. I think you will find it much more flexible that the alternatives.
I think it would be confusing to have the running balance per account built into the Transactions sheet.
But I built my own separate sheet, called Account Details, that lets me see Running Balances on an Account level for a selected Account. I can see all Accounts, just one at a time.
To do this, you need a place where you put the starting balances for each of your Accounts. You could add a simple sheet that has each account in Column A and the starting balance in Column B. If you donāt want to add a sheet with this, it could be in the hidden part of this new Account Details sheet.
Then, on this new Account Details sheet, i have a selector cell to pick which Account I want to review. Thatās in cell B1. F is my Account column.
I run a QUERY() to show me the rows from the Transactions sheet that match the Account Name.
=QUERY(Transactions!A:I,"SELECT * WHERE F = '"&B1&"' ORDER BY A")
Then, I add a column and use the Running Balance formula from above. That arrayformula calculates all the results for the entire column. Or you could simply write a formula that takes the running balance from a prior row and adds the new amount. And fill that formula down the whole column.
For the 1st rowās result, you need to add the starting balance of the account using a VLOOKUP() formula to look up the Account name and then get the starting balance. Then either use the arrayformula or a simply formula filling it down for the rest of the column running balances.
Iām not sure what your comfort level is with writing formulas, but this method should work. The sheet that I built isnāt quite in a shareable form, since I have a few other things going on in the sheet. But if you run into trouble with this concept, i have try to share a rough template.
Jon
3 Likes
Hi @jono . Looks exactly what Iām looking for. Definitely donāt want/need running balances on the transactions screen. It wouldnāt make sense to do so. Iāll give your suggestions a try and will let you know how I make out. Really appreciate the prompt and excellent feedback.
Dave
2 Likes
@davidmark,
I just remembered this.
You could probably use the existing Account Filter sheet as a base and then add your running balance and starting balance info to it.
See:
Jon
2 Likes
@jono Thanks again for your help here. Sincerely appreciated.
So, I tried both your suggestions.
#1 New Tab
- created a new tab called āRunning Balancesā
- placed this in A1: =Query(Transactions!B:F,āSELECT * WHERE F=āXXXā ORDER BY B ASCā,1)
Replaced XXX with an account name. - placed this in F2: =ArrayFormula(If(len(D2:D),(SUMIF(ROW(D2:D),"<="&ROW(D2:D),D2:D)+YYY),))
Replaced YYY with my opening account balance
It works. I will create a drop-down cell so I can select an account and also the associated opening balances for those accounts.
I havenāt done much playing around with it but it would be so nice if the newest transactions could be at the top of the list so thereās not so much scrolling as the number of transactions increase over time
#2 Account Filter Tab
- added a new column F
- inserted this in F6: =ArrayFormula(If(len(D6:D),(SUMIF(ROW(D6:D),"<="&ROW(D6:D),D6:D)),))
Note: I didnāt add my opening balance here as I havenāt had a chance to play with associating the opening balance with the account selected - I then modified the formula in A5 by changing DESC at the end to ASC so that oldest transactions are at the top
I think the second option makes more sense as the ability to filter by account already exists - but I still need to invest some time to get the opening balances into the tab.
Had a lot of fun learning about the Query and ArrayFormula functions. Really great stuff. While I actually understand the Query used, still struggling with the ArrayFormula. For example, I know what len is for but not sure what len(D2:D) does. I presume it makes sure the function doesnāt go past the last row of dataā¦ Not expecting anyone to explain it here but if there are any good tutorials out there, would appreciate the referral. The more I actually understand this stuff, the more Iāll be able to contribute.
LEN(D2:D) is checking the length of the text in cell D2 then D3 (because of the ARRAYFORMULA())ā¦ Essentially, if the cell is empty, LEN() returns a zero which is the same as a logical FALSE. So, when the cell has text, it will run the formula in the first part of the IF() statementā¦ and when the cell is empty, it will run the second part.
Personally, I prefer the formula ISBLANK() since it is a little more straightforward. If you decide to switch to ISBLANK() youāll need to swap your IF() field order.
1 Like
Thanks so much. Makes sense. I just need to spend some time learning the syntax. One tip I read was to start with the innermost component, learn what it does, and then work outwards. So appreciative of the help.
@davidmark,
Iām glad to see you are growing your Google Sheets knowledge to help you customize Tiller to your needs.
Regarding your comment:
it would be so nice if the newest transactions could be at the top of the list so thereās not so much scrolling as the number of transactions increase over time
The running balance formula that I showed you only works to calculate a running balance from top to bottom. The formula is a bit of a hack itself, by using ROW() functions that calculate the row number. Unfortunately, Iām not aware of a formula way to do the running balance in reverse.
That said, there is a way to achieve it. You could take your original table with the running balance going top to bottom and put that whole table in columns on the right side of the table that is hidden. Then use a QUERY or SORT() function, sorting that whole hidden table but using the date column from high to low. The running balances would then be reversed.
We are happy to help you (and others) in the Tiller Community here learn more about these techniques. Let us know if you have more questions.
One great resource for learning about Google Sheets, query, arrayformulas, etc, is Ben Collins @benlcollins website: https://www.benlcollins.com/
Jon
1 Like
Thanks @jono. Again, sincerely appreciate the assistance. Iāll probably give it a try at some point but the main reason I wanted it was to help reconcile my accounts. I used the feature frequently in Quicken.
However, Iāve since discovered the Statement Details sheet. Itās so clever! Makes total sense to put the statement date on each transaction so you can quickly determine what might be throwing you out. Iāve started to think how reconciling with splits could be a challenge but weāll see once I try it for real.
Re Ben Collins. Already found him! But thanks for the reference. It reminded me to keep up with my 30 day challenge that I signed up for. 
1 Like
Glad you found the Statement Details template, @davidmark. That is another @jono invention!
1 Like
Hi all,
A bit of a long post but this interests me.
I know this is an older thread but did anyone manage to get running account totals into the transaction sheet? I am a fan of running balances directly into the transaction sheet as it helps me reconcile with my manual bank feeds easily when updating balances.
Hereās a formula Iāve made copied down the whole column
=IF(ISBLANK(E2),"",SUMIF($J$2:J2,J2,$E$2:E2))
Where column E is the amount, J is the Account for referencing- thus it works with multiple accounts, but in reverse direction (ie cumulative amount down the spreadsheet, not up). It also works with filters, which Iāve noticed arrayformula does not.
@jono is there any risk with using a formula like this unique to each cell in the column of the transaction sheet? Ie would it be compatible given the automatic data feed for linked accounts? And if compatible, do you have any advice / insight on how to edit the formula to be cumulative up the sheet?
EDIT: After further tinkering a formula I have for a running total for multiple accounts in the correct order in the transaction sheet is (for the first cell in the column, copied down):
=IF(ISBLANK(E2),"",SUMIF($J2:$J,J2,$E2:$E))
Hurray! But I would still love to know if the formulae are āauto feed-proofā!