Examine sub balances in balance history tab

Google Sheets
1

I have several sub accounts in my sheet. I’d like to get a list of balances on the balance history tab by sub account so that I can see how the balances in those sub accounts change over time. I can do this by copying the google sheet to excel, but that’s a bit of a pain.

2

I had the same need and I solved it by creating a new sheet where I have a matrix of accounts and dates (I picked last 14 days leading up to today, but that is up to you). I then use QUERY on the balance history sheet to populate the matrix.

  1. Create a data range for your balance history (unhide Balance History sheet, select all data in that sheet, go to Data>Named Ranges and add selected data as new range; I named my range Balance_History for reference in the formulas below)

  2. Create a new sheet for your balance history synthesis (use a different name than balance history) – below I assume accounts are in column A and dates in row 1

  3. Make a column with all the unique accounts in your history (for example use this formula in cell A2):

=unique(‘Balance History’!D2:D691)

with the last row being the last row of your balance history sheet (unless you never update your manual accounts, you probably just need to grab the last couple of days or weeks of data to capture all accounts)

  1. Make a row of dates starting with today using this formula in cell B1: =today(), for every cell to the right decrement by -1 from that date, so dates count down a day for each column to the right; I used 14 dates – last 2 weeks – if you use much more the calculation time will increase quite a bit

  2. Use QUERY to find the balance histories for each account / date combination.

Use this formula in cell B2:

=iferror(index(query(Balance_History,“select H where (B = date '”&TEXT(B$1,“yyyy-mm-dd”)&"’ and D =’"& $A2 &"’)",1),2),)

Copy and paste into all other cells of the matrix.

If there is an entry in Balance History for that date/account combination it will be shown, if not, the cell will be empty.

If you wanted to see the latest entry per account (i.e. of a given date or if none, the latest entry, use this formula instead:

=index(query(Balance_History,“select H where (D =’”& $A2 &"’) order by B DESC",1),2)
This is calculation intensive, and I am sure there is a more elegant way to do this that uses far less QUERY calls, but my QUERY-fu is weak and I couldn’t figure it out.

1 Like
3

@mikesalzberg have you checked out the Net Worth Tracker from Labs? Let me know if this doesn’t solve it for you as I’m curious how it’s different.

4

I don’t see net worth tracker in my Tiller Labs???

Mike

5

If you go to add-ons at the top banner of google sheets, Tiller Money Labs should be an option. If you launch that add-on, there should be Add a Solution at the top of the list. Inside of this option is a list where you should find the Net Worth Tracker.

6

OK, found it. Works for me. Can I ask to see more columns, e.g., every day/week/month/year??

Mike

7

How did you set up the sub accounts?

8

Not sure I understand your question.

Mike

9

Hi Mike,

In your original message you stated:
*I have several sub accounts in my sheet. I’d like to get a list of balances on the balance history tab by sub account so that I can see how the balances in those sub accounts change over time. I can do this by copying the google sheet to excel, but that’s a bit of a pain."

Can did you set up some accounts?

Thanks,
Patrick

10

Actually I do have some accounts within the sheet. I’m not sure they are sub accounts, but they show up on my account tab. For example, I have an AMEX card. I’d like to see the spending on that card net of the payments I’ve made.

thanks

Mike