Fill Specific Cells with Account Balance

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

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_,
  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_(Accounts!E:ZZ,"select `Account` where `Hide` = 'Hide' label `Account` '' ",1),""),
liability_accts_,iferror(
  query_(Accounts!E:ZZ,"select `Account` where `Class` = 'Liability' label `Account` '' ",1),
  query_('Balance History'!A:ZZ,"select `Account` where `Class` = 'Liability' label `Account` '' ",1)),
balance_history_,
  query_('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),
query(
  sortn(
   {choosecols(balance_history_,1,2),
    choosecols(balance_history_,3)*if(ifna(match(choosecols(balance_history_,2),liability_accts_,0),0),-1,1),
    eomonth(choosecols(balance_history_,4),0)},9^9,2,4,0,2,1),
 "select Col2, max(Col3) group by Col2 pivot Col4",1))

You can change the 12 in the second line to however many months of data you want to return.

4 Likes

I edited the formula above to respect hidden accounts.

1 Like

Edited again to report liability account balances as negative numbers.

2 Likes

Will try and let you know!

:wave:, @AutomationNation

Did any of these suggestions help? If so, please mark one as the solution.

@morgan - Thanks for following up. Testing now :slight_smile:

1 Like

@rhowell -

Hi!

Just created a fresh sheet to try this out.

  1. Created AutofillAccountBalanceMonthly Google Sheet.
  2. Linked it to Tiller.
  3. Ran Extensions → Tiller Money Feeds → Launch, which created 3 Sheets:

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

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.

@rhowell

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

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

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

1 Like