šŸ† Account Register - Google Sheets

I tried a demo of tiller a while back and chose to not proceed because it lacked the account register and bill pay reminder capability. Very much considering Tiller now because of this!

1 Like

This is fantastic! Thanks for sharing. Iā€™m going to get a lot of use out of it. I have a few cosmetic recommendations that I intend to apply to my template:

  • Zebra-striping the rows
  • Color-coding the Amount column for debit vs. credit
  • Conditionally color-coding the balance column on a checking (or other asset) account to highlight balances < 0

OTOH, it may be harder to find a one-size-fits-all style and leave that to individual tastes.

Either way, I think I can replace the Account Filter sheet with this one for my needs.

I like the alternating row formatting suggestion, so I added it to the shared template. Not sure about the other two though since they arenā€™t as ā€˜standardā€™ a thing. Will keep them in mind, thanks for the suggestions!

Your new Account Register is a very helpful tool. I have been able to set it up for one of my checking accounts. However, the running balance is not correct and it is consistently off by the same amount for every month. I think I entered the correct amount for a ā€œstarting balanceā€ in the Account Reconciliation going back to 2020 when I first started using Tiller. Any suggestions?

It sounds like something still isnā€™t right in your account reconciliation. In the Account Reconciliation sheet, when you put in the date and closing balance from last monthā€™s statement for that account does it match the ā€œTiller Ending Balanceā€? If not, you need figure out why. If putting in the date and closing balance from the very first month of full transactions in Tiller (sounds like some time in 2020 for you) matches the ā€œTiller Ending Balanceā€ then that suggests your ā€œTiller Opening Balanceā€ is correct, and youā€™ll need to move forward through the statements until you find where it goes wrong. If that first month doesnā€™t match, then confirm all your transactions for that month match your statement, and adjust the opening balance until the TIller ending balance matches your statement closing balance.

I finally figured out there was a column for ā€œOpening Balanceā€ and put the opening balance for July 2020 which actually was the amount that the running balance was off. The numbers now look good! Now I will know what to do when I set up another account. Thanks for your help and for providing the template!

1 Like

Finally got around to adding this to my sheet after reconciling the accounts I was wanting to use. Something I did note is that on days that have multiple transactions and a deposit, the deposits sometimes are listed last and causes the running balance to sometimes appear negative, however at least for my bank they apply the deposits first before any debits, is there a way that the deposits could be shown first but still ordered by date?

Not sure if it matters or not, but upon closer inspection some of the time periods such as last quarter appear to do the deposits first, but last month and this quarter doesnā€™t seem to respect this order. I canā€™t speak for all of the time periods as I only reconciled about 6 months back instead of all the way to the beginning of my tiller transactions. Could this be causing this behavior?

Otherwise this is great. Thank you for all of your templates.

The Account Register should be accurate for any time periods that you reconciled for. If you include any time periods that you didnā€™t reconcile, then all bets are off. To show the deposits first, I think we can just add a sort on the amounts, try changing B3 to this:
=IFNA(QUERY({R4:R,S4:S,T4:T,U4:U}, "select Col1,Col2,Col3,Col4 where Col1 >= date '"&TEXT(P3,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(P4,"yyyy-mm-dd")& if(G5=FALSE,"'","' Order by Col1, Col3 Label Col1 'Date', Col2 'Description', Col3 'Amount', Col4 'Balance'")))

The only difference is adding ā€˜, Col3ā€™ to the ā€˜Order byā€™ section of the query so within each date it sorts by amount. You could add ā€™ DESCā€™ after ā€˜, Col3ā€™ if itā€™s not sorting in the right order. Let me know if that works, itā€™s an easy change I can add to the shared template.

@jpfieber Thanks for this. It worked great for checking, but I have two credit cards with the same bank and it couldnā€™t distinguish between those accounts. I might create a whole new spreadsheet for the new year. What do I need to do so it will recognize both of the Credit Card accounts. Is it dependent on how I name the accounts originally?
Thanks.

It pulls the names from column H of your Accounts sheet. I believe that name is the one you create when you first setup the account, and I think you can change it in the console.

Thanks so much for that! Iā€™m a pretty new user so didnā€™t know I could change those. Appreciate it.

1 Like

Be careful with renaming though, on an existing spreadsheet thatā€™s already using the old name, I think you might have to go back and do a search/replace to convert the old names in the transactions/balance history to the new names.

Got it. Thanks! Thatā€™s very helpful.

The Account Register template for Google Sheets is now in the Tiller Community Solutions Add-on! Iā€™ve removed the link to the shared template, you can now install from the gallery, and it will also install the required ā€œAccount Reconciliationā€ sheet.

Added a bug fix, please update TCS when you get a chance @Randy
Version 1.30
Adjusted how transactions are sorted to correct issue with running balance being out of place

Just updated. Let me know if it works for you.
May need 15ā€™ to clear caches.

That worked, thanks!

Thanks for sharing this!
Is there away to sort the transactions from most recent to oldest?

1 Like

Change the command in B3 to:
=IFNA(QUERY({R4:R,S4:S,T4:T,U4:U}, "select Col1,Col2,Col3,Col4 where Col1 >= date '"&TEXT(P3,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(P4,"yyyy-mm-dd")& if(G5=FALSE,"'","' ORDER BY Col1 DESC Label Col1 'Date', Col2 'Description', Col3 'Amount', Col4 'Balance'")))

2 Likes

That worked- Thank you!