Examine sub balances in balance history tab

Google Sheets
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