I’m looking to fill a custom sheet like follows:
Account1 BalanceEndOfJanuary BalanceEndOfFebrurary Etc.
Account2 BalanceEndOfJanuary BalanceEndOfFebrurary Etc.
AccountN BalanceEndOfJanuary BalanceEndOfFebrurary Etc.
I searched the forums and tried the Net Worth tracker formulas, but don’t see how they would accomplish this.
Any simple way to automatically import the account balance for an account into a cell that I specify?
Help appreciated 
Hey @AutomationNation ,
Give this formula a try. It should return the end-of-month balances for each of your accounts for the last 12 months:
=let(
months_,12,
query_by_col_name_,
LAMBDA(data_, query_, headers_,
QUERY({data_},
LAMBDA(query_text_, data_row1_,
REDUCE(query_text_, FILTER(data_row1_, ISTEXT(data_row1_)),
LAMBDA(query_text_result_, data_col_name_, REGEXREPLACE(query_text_result_, "`" & data_col_name_ & "`", "Col" & MATCH(data_col_name_, data_row1_, 0)))))
(query_, ARRAY_CONSTRAIN(data_, 1, COLUMNS(data_))), headers_)),
hidden_accts_,iferror(query_by_col_name_(Accounts!E:ZZ,"select `Account` where `Hide` = 'Hide' label `Account` '' ",1),""),
liability_accts_,iferror(
query_by_col_name_(Accounts!E:ZZ,"select `Account` where `Class` = 'Liability' label `Account` '' ",1),
query_by_col_name_('Balance History'!A:ZZ,"select `Account` where `Class` = 'Liability' label `Account` '' ",1)),
balance_history_,
query_by_col_name_('Balance History'!A:ZZ,
"select `Date`, `Account`, `Balance`, `Month`
where `Month` > date '"&TEXT(eomonth(today(),-months_-1),"yyyy-MM-dd")&"'
and `Month` <= date '"&TEXT(eomonth(today(),-1),"yyyy-MM-dd")&"'
"&ifna(join(" ",arrayformula(" and `Account` <> """&hidden_accts_&""" ")),"")&"
order by `Date` desc, `Time` desc",1),
balance_table_,
query(
sortn(
{choosecols(balance_history_,1,2),
choosecols(balance_history_,3)*if(ifna(match(choosecols(balance_history_,2),liability_accts_,0),FALSE),-1,1),
eomonth(choosecols(balance_history_,4),0)},9^9,2,4,FALSE,2,TRUE),
"select Col2, max(Col3) group by Col2 pivot Col4",1),
{balance_table_;BYCOL(balance_table_,LAMBDA(col_,if(col_=CHOOSECOLS(balance_table_,1),"Total",sum(col_))))})
You can change the 12 in the second line to however many months of data you want to return.
I edited the formula above to respect hidden accounts.
Edited again to report liability account balances as negative numbers.
Will try and let you know!
@rhowell -
Hi!
Just created a fresh sheet to try this out.
-Categories
-Transactions
-Balance History
Transactions imported and the Balance History sheet looks like it has the data that we need, but how do we create a separate Sheet (e.g., “MonthlyAccountBalances”) that has each account and a cell where we can use the formula?
Help is appreciated
Just create a new sheet - use the + sign down to the left of the tabs - and put the formula in cell A1 in that sheet.
Ok, I did that and this is the result:
When I hover over the red line in the query, it says that the query takes 3 parameters, but that there are 6.
Any help appreciated
It looks as if your sheet doesn’t include the core Accounts sheet. I’ve updated the formula above to handle the missing sheet. Give it a try.
@rhowell -
Wow, that worked quite like magic! Thank you so much.
Currently, after entering the formula, I see:
Account1 BalanceEndOfJanuary
Account2 BalanceEndOfJanuary
AccountN BalanceEndOfJanuary
My ultimate goal is for new columns to be populated at the end of each month, automatically like so:
Account1 BalanceEndOfJanuary BalanceEndOfFebrurary Etc.
Account2 BalanceEndOfJanuary BalanceEndOfFebrurary Etc.
AccountN BalanceEndOfJanuary BalanceEndOfFebrurary Etc.
Do I need to make any further configuration changes for this to happen, or add the formula to other cells?
Curious your thoughts on the best way to accomplish this
Glad it worked! The formula will always report the last 12 months (or however many months you put in line 2; assuming the data exists), so there’s no need to update it to get the latest values.
On March 1, you should see a new column with the values for February without having to change anything. You’ll still see the January column as well.
Let me know if you run into any problems.
@rhowell -
That is slick. Great work.
I’ll mark yours as the solution for now, but will post back either way with how it goes.
Thanks again
can this be more generalized into a function that takes an account name and date and gives the balance for that date?
I have a custom sheet where I track account balance history today, last week, last month, last quarter, and last year. I created multiple hidden net worth snapshot sheets and grab the balances from there but I’m sure that’s horribly inefficient and my sheet updates are slow.
Hey @rayfes,
Sure! The formula below should return the balance for the account in cell A2 on the date in B1, so you’ll want to edit the cell references in the second and third lines:
, 
=let(
account_,$A2,
date_,B$1,
query_by_col_name_,
LAMBDA(data_, query_, headers_,
QUERY({data_},
LAMBDA(query_text_, data_row1_,
REDUCE(query_text_, FILTER(data_row1_, ISTEXT(data_row1_)),
LAMBDA(query_text_result_, data_col_name_, REGEXREPLACE(query_text_result_, "`" & data_col_name_ & "`", "Col" & MATCH(data_col_name_, data_row1_, 0)))))
(query_, ARRAY_CONSTRAIN(data_, 1, COLUMNS(data_))), headers_)),
liability_accts_,iferror(
query_by_col_name_(Accounts!$E:$ZZ,"select `Account` where `Class` = 'Liability' label `Account` '' ",1),
query_by_col_name_('Balance History'!$A:$ZZ,"select `Account` where `Class` = 'Liability' label `Account` '' ",1)),
balance_history_,
query_by_col_name_('Balance History'!$A:$ZZ,
"select `Balance`
where `Account` = """&account_&""" and `Date` <= date '"&TEXT(date_,"yyyy-MM-dd")&"'
order by `Date` desc, `Time` desc limit 1 label `Balance` '' ",1),
ifna(balance_history_*if(ifna(match(account_,liability_accts_,0),FALSE),-1,1),""))
Note that this is far from the only way to return a balance for a given account on a given date. It’s a particularly flexible formula, because I don’t need to know which columns on your Balance History sheet contain account names, dates, and balances. Other solutions to the two-way match problem use nested XLOOKUPs or INDEX-MATCH-MATCH.
Edited the formula to fix a problem retrieving hidden accounts. Also added a Totals row.