Account Register - Google Sheets

Ahh, hadn’t considered that. Yeah, I should be able to correct that, I’ll start looking into it. Thanks for pointing out the issue!

OK @jshelham , I updated the shared template to reverse the polarity of transactions in a liability account. Delete the template and re-copy and let me know if it’s behaving!

I am getting an error in cell R5, “Did not find value ‘Start Date’ in VLOOKUP evaluation”. Perhaps I copied into my main file incorrectly?

Found the problem, sorry for the hassle, delete your sheet and recopy from the shared template.
Found a few more small issues, 1.12 is now the current version and seems to be working fine in my Foundation template.

Working perfectly now. Thank you!

Congratulations, @jpfieber, on another well-built template that delivers on a popular user request. I’m impressed at how you continue to hone in on key functionality that can be delivered or improved, like the running balance, and knocking it out of the park.

No pressure to change anything— I know you are busy— but the only two observations I had when I goofed around with it were:

  • The period includes a “Custom” option but I couldn’t figure out where to enter those dates.
  • I absolutely see the synergies of integrating with your Account Reconciliation template but I wonder if adding a lookup table for starting balance overrides in the hidden area would allow register/not-reconciliation focused users to use the new template when the Account Reconciliation sheet is not present.

As part of the Tiller Builder Rewards Program, the Tiller team is excited to award you $250 for building the Account Register and documenting it so it can be used by the entire Tiller community.

:raised_hands:

1 Like

Funny you should post this today as I’ve been working on the Excel version and should have it ready for release tonight. Thanks for the award and the feedback!

For your first comment, I think you’re using an old version, which did have that problem. I use Conditional Formatting to hide the date fields when Custom isn’t chosen, and there were some issues with that in the first couple versions I shared. Try downloading the template again and it should/better be working.

I have already been giving thought to your second suggestion, as many users won’t want to go through the effort of the reconciliation process. The problems I’m seeing are:

  • Getting an accurate starting balance from “Balance History” may be a problem if manual transactions have been entered without updating the history, especially if transactions were migrated/added from before the first Tiller sync.
  • Having an accurate running balance relies on all the transactions between the opening balance and the beginning of the chosen period being accurate (no duplicates/missing/mis-categorized, etc)

I’ll continue to give thought to the possibility as it would be nice for more users to be able to take advantage of this template. If you, or anyone else, have thoughts on how to get an accurate running balance without relying on my Account Reconciliation template I’m happy to consider adding other options!

Hi Jpfieber! Congratulations! I love your new sheet that you created. I only use Google sheets, AND I am an ultra newbie, terrified a bit of Tiller and $. Is this a sheet you would still recommend for me? I love the functionality, but I am unable to handle anything too complex. Is it straightforward to install, or use even if I don’t know what I am doing?

Thank you!

Account Register is super simple to use, so that part shouldn’t be much of a challenge. For it to have reliable numbers, you need to have a known ‘starting balance’ for each account, and know that all your transactions are ‘clean’ (no duplicates/missing/miscategorized, etc) which is what my Account Reconciliation sheet helps with. It’s very easy to install since it’s available in the Community Solutions add-on, but depending on how many accounts and transactions you have, can take a little time and effort to use. Once you make sure all of your transactions are clean and your balances are showing correctly (bank statements match totals in Account Reconciliation sheet), it’s easy to keep up with, and then Account Register works like a charm.

Wow, thank you for this quick response. So it sounds like I should first go in and install your account reconciliation sheet? Correct? I have loads of accounts, 4 bank accounts, 5 credit cards, and well I think that is all. How will I know if my bank statements match to totals in account rec sheet? Aren’t those number fluctuating on a daily basis?

I will go in and play with it a bit…I hope I don’t mess anything up in my account. :slight_smile:

That is always the fear.

THANK YOU SO MUCH,

Fallynn

Yea, install Account Reconciliation (Account Reconciliation - Google Sheets) and follow the instructions in that post. That sheet ‘reconciles’ your transactions with bank statements. Once you have the first month for the account matching your bank statement, it’s a simple matter of putting in the date and balance of the next statement and seeing if it comes up green. If so, you can move to the next month, if not, you need to find how your transactions don’t match the statement and fix it. Once you get all of your accounts balanced in Account Reconciliation, Account Register will display a running balance for any of them.

Wow, cannot wait to try this out, thank you so much!

1 Like

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.