Balance on transactions sheet

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

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

2 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. :slight_smile:

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’!

If you add a formula that requires you have to manually fill it down a column, you might run into some problems. When Tiller adds new Transactions and needs to add new rows to fit them, the formula wouldn’t be in those new rows. That’s why using an arrayformula in the top row is a better approach.

I suppose you could add a lot of empty rows and fill the formula into those empty rows, so Tiller wouldn’t create new rows without the filled down formula. But if you ever forgot to keep enough empty rows filled, you would have a problem.

Also, you are likely taking a performance hit with all those formulas.

Regarding making the formula cumulative up, you could probably write a formula in the bottom row (actually 2nd to bottom row so, bottom row minus current row) and fill the formula UP the sheet. Again, I wouldn’t recommend it.

You might look at this arrayformula which when put in a new column can create a running total of another column. If the column you want to total is Column B, then this would go in the running total column top row:

=ArrayFormula(If(len(B2:B),(SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B)),))

Finally, I would highly recommend you do these running balances in a separate sheet. I know you must have some reasons for using the Transactions sheet, but I don’t think using the Transactions sheet for the calculations you want is a best practice. Consider the Transactions sheet as a database. And if you want a certain view of that database, with running totals, create a new sheet with the view you want. You won’t be able to edit the database from the view sheet, but it is much more efficient and powerful doing it that way.

Jon

@jono thanks for the detailed reply and advice.
I’ll consider springboarding to another sheet from the transaction sheet.

1 Like