Running Balance on Transactions Sheet

Google Sheets Show & Tell
1

Overview

This adds a new column that shows a running balance in the Transactions sheet. You can configure which accounts show their balance, as well as how many transactions back in history you want to show the balance.

It’s a bit of a performance hog, so you don’t want to go too far back. By default, I have it set to just show the balance of the latest transaction for each account. As a point of reference, though, I have 7500+ transactions, and my sheet was only slowed a little when I had 8 accounts turned on and 30 transactions per account showing a balance.

image

Installation

Get the worksheet here (Tiller-Running-Total - Google Sheets):

  1. Copy the Transactions-Running Balance sheet into your Tiller sheet.
  2. Create a new column at the right of the Transactions sheet, and copy in this formula:
={"Running Balance";
   LET(
      rowsAll,MAP(INDIRECT('Transactions-Running Balance'!$G$3),LAMBDA(t,ROW(t))),
      amountsAll,INDIRECT('Transactions-Running Balance'!$G$4),
      accountAll,INDIRECT('Transactions-Running Balance'!$G$5),
      transactionsIDAll,INDIRECT('Transactions-Running Balance'!$G$6),
      MAP(
         transactionsIDAll,
         accountAll,
         rowsAll,
         LAMBDA(transID,account,row,
            IF(
               ISNUMBER(MATCH(transID,INDIRECT('Transactions-Running Balance'!$K$1),0)),
               SUMIFS(amountsAll,accountAll,account,rowsAll,">="&row)+VLOOKUP(account,'Transactions-Running Balance'!$A$3:$B$12,2,false),
               IFERROR(1/0)
            )
         )
      )
   )
}

If you get a “circular reference” error, that’s because the Transaction ID column is to the right of the Running Balance column. You can either turn on Iterative Calculation in the Google Sheets settings, or move the Running Balance column to the right of the Transaction ID column.

Setup

image

Cell B1 tells the formula how many historical transactions to calculate the balance on. By default I have it set to 1, which is just the most recent transaction for each account selected below. You can make this as high as you want, but be warned it takes a toll on your CPU.

A3:B12 is where you select which accounts to calculate the balance for. Column B is optional, and is if your entire account history ISN’T in the Transaction sheet, and you need to set a starting balance.

Usage

As you add new transactions to your sheet, the balance should update automatically.

Notes

I threw this together on a bit of a lark to see if I could do it, and it mostly does what I wanted. The big downside is the poor performance when you get too many accounts and/or transactions. I would love some advice from anyone on improving the efficiency of the balance calculation.

FAQ

Optionally, add common questions and answers…

2

I’m curious if you could try/compare/comment on what you have here vs this?

={"Running Balance";
LET(
datesAll,B2:B,
amtsAll,M2:M,
acctsAll,T2:T,
MAP(acctsAll,datesAll,LAMBDA(acctRow,dateRow,SUMIFS(amtsAll,acctsAll,acctRow,datesAll,"<="&dateRow)))
)}
1 Like
3

Hi @Mark.S, thank you for the tip! That’s a great formula, and I took some elements from it and updated mine. I still like being able to control which accounts show a balance, which keeps it from getting too crowded on the Transactions sheet.

Thanks!

4

Sure thing, credit to @kyle.sullivan.me . How does the performance compare - better/worse?

2 Likes
5

Performance seems much better. I’m more of an Excel guy so have a lot to learn about optimizing Google Sheets. Thanks, again!

1 Like
6

Glad my version could help performance! I like the idea of being able to choose which accounts show a balance as well. I may give yours a shot.

@heather thoughts on incorporating this into the master transactions template?

1 Like
7

Thanks for sharing this, @alan.heatherley. It’s a common customer request and your solution is elegantly implemented. Thanks for engaging and improving on the original, @Mark.S.

3 Likes
8

@kyle.sullivan.me thanks for the question. It’s not something we’d add to the Transactions sheet at this time. We try to keep the core Transactions sheet free of formula driven columns (even if the formulas are performant) and powered by the feeds.

Running balance is one of those data points we could add to the Transactions sheet that comes straight from the aggregator, but there were some complexities/caveats with doing it that made us decide to hold off on it (though I’d have to dig in and refresh myself on what that was).