Account Register - Google Sheets

Overview

A community member asked if there was anything like a “Checkbook Register” that not only includes the date, description and amount of a transaction, but a running balance so you can see what remains in your account. A few others have asked for similar functionality, and I thought the code required is similar to a few templates I’ve already released or are in the works, so I was able to throw this together pretty quickly.

Installation

Getting a reliable running balance requires having a reliable starting balance. Because of this, the “Account Register” template relies on the “Account Reconciliation” template. You can install the “Account Reconciliation” template from the Tiller Community Solutions Add-on. Once installed, take the time to fully reconcile the accounts that you’d like to use in the “Account Register” template. “Account Register” gets it’s list of accounts, and their opening dates and balances from “Account Reconciliation”, so it’s an important step. Once you have that done, open the link to the “Account Register” template below. On the “Account Register” sheet name at the bottom of the screen, click the triangle and choose “Copy To”, then “Existing Spreadsheet” and select your Tiller foundation template.

Usage

Once the template has been copied, it’s a simple matter of choosing the account you want to see the register for, and the time period you’d like to see the transactions from, both selections are highlighted in green. Everything looks correct in my setup (the balances match those on my Balance sheet), but use caution and don’t base any important financial decisions on this template without double-checking the numbers! Let me know if you run into any problems or if the numbers aren’t coming up right.

Notes

I kept this pretty simple, mimicking the functionality of a real checkbook register. Note that it’s use isn’t limited to checking accounts, it should work fine with any accounts that you reconcile transactions for in Account Reconciliation. An Excel version is available here.

This is super cool, @jpfieber. It’s a frequently requested feature.

I’m impressed at how you have structured your many templates to build on one another.

1 Like

I think I followed all of your instructions, but when I click on the drop down to select the account, none appear. I installed and reconciled my account first.

1 Like

Click the ‘+’ above column H to open the helper data. In column L, row 11 there should be a list of the accounts from the Account Reconciliation sheet, do you see those? If not, what shows up in the formula bar when you click on L11?

hi jpfieber - yes, I clicked the + and saw both Accounts(Checking, Savings) that are coming from the account reconciliation tab. when I click in the Accounts dropdown, it’s blank, nothing shows. I played around with the Data verification and changed the range to L11:L20 and that fixed the issue. I most likely dumbed-down some code but so far so good.

Ahh, I just checked the shared template and somehow the data validation range is set wrong. Anyone that is having the same issue (@laura ) can either delete the template and re-download it, or select cell G3, choose “Data Validation” from the “Data” menu, and change the ‘List from a range’ setting from =$L20:$L to =$L$11:$L.
Thanks for reporting that issue!

1 Like

Thank you! I changed the data verification range and it works now. Thanks for the new template. I have been sorting my transactions by account and then copying them to another sheet to get my bank balance. Some things are on more of a cash flow basis (credit card payments) than a monthly expense basis.

1 Like

Thanks for this new template! Since credit card balances are shown as positive as liabilities, the register then decreases the running balance as charges (which show as negative) are added. Is there a way to identify this as a liability so the starting balance shows negative and grows as charges are made?

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