Tracking cash transactions with automated balance updating

Google Sheets Show & Tell
, ,
1

This workflow allows you to keep track of cash transactions and for each manual transaction see your balance update on the Balances sheet in the Tiller Foundation Template (or any sheet where a Balances, Balance History, and Transactions sheet exists).

41%20AM
41%20AM2774×1588 270 KB

1. Manually add transactions for cash including the “transfer” to the cash account from the ATM.

You should already understand the basics of cash tracking for transactions with Tiller. In my example, I use the Super Tidy Cash Tracking method.

54%20AM
54%20AM2768×1592 260 KB

2. Create a “cash” account in the Balance History sheet.

In order to have the “cash account” show up on the Balances sheet we need to enter a Balance History record for the “account” - everything appearing in the Balances sheet comes from Balance History.

Insert a row above row 2 on the Balance History sheet (possibly hidden in your sheet) and enter =today() for the date. This formula will ensure that your balance for Cash is always current and at the top of your Balance History sheet.

Fill in the other details

  • for Time you can just enter 12:00AM
  • for Account enter “Cash” (or whatever name you like for cash)
  • make up a unique account number formatted like the others
  • make up a unique account ID that starts with manual:xxxxxx (NOTE: this column is probably hidden. It is required for the account to show on Balances. Unhide it to enter a manual ID)
  • enter =SUMIFS(Transactions!E1:E, Transactions!H1:H,"Cash")for the amount. This will sum up transactions on the Transactions sheet that are assigned to the “Cash” account. If you use the Super Tidy Method this balance should be whatever cash is still in your wallet.
  • Enter =(if(row($B2)=1,"Month",iferror(date(year($B2),month($B2),1)))) for the Month. This assumes your dates are in column B and your cash balance line is in row 2.
  • Enter =(if(row($B2)=1,"Week", iferror(DATE(YEAR($B2), 1, -3 + 7 * WEEKNUM($B2) - WEEKDAY(DATE(YEAR($B2), 1, 4), 1) + 1))) ) for the Week. This assumes your dates are in column B and your cash balance line is in row 2.
  • Enter Cash as the Type and Asset as the Class.

34%20AM
34%20AM2772×1588 227 KB

26%20AM
26%20AM2774×1588 238 KB

Now each time you add a new Cash transaction to the Transactions sheet your balance will update automatically on the Balances sheet.

Important notes:

This method will not give you a historical view of your Cash account. It gives you a current day snapshot based on what’s in your Transactions sheet. If you prefer to have a historical view of your cash account balances you’ll need to create a manual entry in the Balance History sheet each time you add a cash transaction.

If you get a negative balance for your cash it means you likely didn’t enter the transfer from ATM to the “Cash account” as two transfer transactions on the Transactions sheet.

2 Likes
2

I got it to work right up until the time I update the account balances and the manually entered account values shift down the spreadsheet. For example, I manually entered the “Transactions!E2:E,” but when the new transactions populate and E2 becomes another balance, the text I entered becomes E8 or something, it screws the whole thing up.

3

I should add, I’m using the monthly budget (rollover template).

4

I’m using this method to track balances on serveral manuakl account. The formula “=SUMIFS(Transactions!E2:E, Transactions!H2:H,”[account name]")" changes when I manually add tranctionas. If I add one, the line becomes “=SUMIFS(Transactions!E3:E, Transactions!H3:H,”[account name]")". The more manual transactions I add the more it incraments. Is there an easy solution?
I am manually adding the transactions via Tiller Labs’ Tool for Manual Tranactions.

5

@pixel.dick,

I think if you change it to =SUMIFS(Transactions!E$2:E, Transactions!H$2:H …

That will lock in the row 2.

Good catch. Give it a try and let me know if that works and I’ll update the doc here.

Heather

6

I had already tried that and it has no effect.

7

The sheet I noticed it on it mostly manual transactions. I don’t think this happens when transactions are added via the Tiller feed.

8

Hmm yes, I did notice that too after I added a manual transaction. Not sure how to fix it honestly, I am definitely NOT an advanced spreadsheet nerd, just an intermediate tinkerer :wink:

9

I suspect the fix is in the way the manual transaction is being added to the sheet. It would appear that it is being inserted above Row 2. I am working around the issue by manually inserting blank lines below Row 2. I don’t get automatic update of the date columns, but I’m not sure I use them.
Do you know of anything else I would be missing by inserting rows?

10

I found the solution. It was too simple :slight_smile:
=SUMIFS(Transactions!E$1:E, Transactions!H$1:H,"[account name]")
Sheets knows to ignore the text in the header. The transaction is inserted in Rox 2 and it doesn’t effect the formula.

1 Like
11

You can track balance history IF you remember to save the balance before entering new manual transactions. You select the entire row of the balance you wish to save. Then you copy (shortcut Ctrl-C), then you insert a row below then you Paste Special Values (shortcut Ctrl-Shift-V) into the new blank row you just created.

I also noted a fractional cent got into the balance somehow on a couple of my manual accounts. For example of my balances was 36.1699999999998. I corrected for this by modifying the formula that sums the balance to:
=ROUND(SUMIFS(Transactions!E$1:E, Transactions!H$1:H,"[account name]"),2)