🏆 Account Reconciliation - Google Sheets

If you search in explore for account reconciliation, you don’t see it?

Screenshot 2023-09-19 at 6.14.53 PM

If you do, click on the square icon to the left and that should take you to the install.

I do, but when I click on it, it takes me to a search window:
image

Is there a manual install option?

That’s strange, you’re missing entire categories that I can see (including Bank Statements). @randy is there a way he can repair or uninstall/reinstall the extension?

This is wild. I’ve never seen this behavior. Thanks for trying to work through it with @bryman, everyone.

I will reach out to him and get this sorted 1:1.

(I think the most likely culprit is an add-on crash due to a spreadsheet incompatibility— not handled gracefully— that is causing the add-on to not render completely… essentially the add-on is frozen mid-render.)

@jpfieber I think the recent upgrade to the Fidelity API is impacting this sheet. Fidelity is now pulling in backend debits and credits that weren’t there before. They look to be the transactions they make when they are moving money in and out of my main Spending (Checking) account to their backend holding account. Even though I have those transactions categorized as Transfers they are still showing up in Account Reconciliation and preventing accounts from being balanced.

These transactions are listed as “Purchase into Core Account…” and “Redemption from Core Account…” if it matters.

When it comes to calculating the balance of an account, transfers in and out matter too, so they are not filtered out. You need to compare the transactions in Tiller against the transactions on your bank statement. Presumably, either at least one transaction wasn’t imported into Tiller, or there are duplicates. If it’s anything like PayPal, you may end up having to manually create some transactions every time they do a transfer, which is a pain. Another possibility is that some transactions on the bank statement are coming in a day or two early or late and being counted in the wrong statement. I cover how to handle these in the instructions by adding a ‘Reconcile Date’ in the Transactions sheet that will move it from one statement to another in Account Reconciliation. You can use the “Troublewhoot Unbalanced Statement” tool to help find these.

Yeah, the issue is that these transactions aren’t really happening, if that makes sense. They started flowing in directly after the Fidelity API was updated a few weeks ago. They are churn on the backend at Fidelity that you wouldn’t normally see, and they are not included in the official bank statement nor are they in a user’s account portal.

Fidelity is actually holding your money at a secondary bank behind the curtain and these are the backend transactions when they move money in and out of your main checking account that you normally would never see. For whatever reason, these are now in the upgraded Fidelity API (I can only presume this is what happened because I never saw them until Tiller moved to the new Fidelity API standard).

Thus, I can no longer balance against my official bank statement. This isn’t a problem with your sheet; this seems to be an issue with the Fidelity API as I doubt they intended for these transactions to flow through.

@randy You may want to see this one.

If they don’t actually change your accounts balance, personally I’d delete them. It’s extra manual work, but the easiest way to make everything behave. Would be nice if you can get the import not to include these, but I wouldn’t hold your breath on it. Good luck!

Yep, probably my only option right now.

I am new to Tiller and deciding whether I will switch from Quicken. As I understand this add-on, it checks to see if the Tiller balance matched the entered balance. How do you find any possible over/under charges or unexpected charges? I was guessing that entries were entered manually then merged with the downloaded charges to reconcile account activity.

The template compares the date and account total you input from a bank statement against the transactions you have in your Transactions sheet (not the “Balance” on your balances sheet, transactions and balances are separate in Tiller and may not match depending on the timing of your balance and transaction downloads). If they don’t match, there is a tool to help locate which transactions may be the problem. I’ve created a lot of templates, but this is one that I consider critical for keeping my transactions sheet healthy.

I am not able to Add this Template. I keep getting this error.

image

As i tried running this multiple times. Now i see Multiple copies of Account Reconcilation

I was able to add it to a test sheet, so I don’t think it is a problem with the add-on or gallery. Try using File History to go back before you tried the first time and give it another try.

Just wanted to take a moment and dole out some praise for this worksheet. What used to take me HOURS to reconcile, now takes just a couple of minutes. I currently 26 accounts and I’m not done adding them all. Honestly, I think this worksheet single-handedly has saved me probably 30 hours.

I haven’t had any discrepancies yet, but it feels easy to navigate if I do.

So huge shout out to @jpfieber for creating such a useful, value add, time saving, headache saving, tool. Thank you sir. I bow to you.

2 Likes

Thanks for the kind words, glad it’s working for you!

Aaaand now I found a challenge. I have a statement that’s not reconciling - but I cannot figure out why. The previous month’s reconciliation is working great. This particular statement, there are only 2 transactions in the whole statement, so it’s not hard to track down what’s going on. The statement itself is accurate (I triple checked, to make sure the bank didn’t make a mistake, and again not hard because there are only 2 transactions). Using the troubleshooting section of the statement, it’s picking up both transactions, the deposits and withdrawals match, the opening and closing balances match. The discrepancy is that the closing balance of troubleshooting section, which is accurate, is not matching the "tiller ending balance’ in the regular section of the worksheet.

I’m uploading a screenshot so you can see. The balance is accurate in the troubleshooting section, the balance is NOT accurate in the regular section. This is after I did a restore of the sheet, just in case.

Any ideas?

I should add, there are transactions in the sheet I have manually added, but the account, account numbers, and institution cells are all accurate. And this is the 3rd account I’ve done this for (manually adding historical transactions) and the other 2 reconciled delightfully.

Looking at the image you provided, I think you might be misunderstanding how to use it. There should be one line for each account, and you update the dates each month. In your case, you have two lines for the same account. The one has an opening date and balance, so it is working, but the second doesn’t have an opening date and balance, so it doesn’t. It might be possible to have one line per statement if you set the opening date and balance to the ending date and balance of the last statement, (never tested that as it wasn’t the planned workflow) but to keep things simple, just have one line per account (so all 26 accounts are on the same sheet) and each month, you just update the closing date and closing balance, and see if everything stays green.

Ah, interesting. It WAS working to add a new line for each account statement date. I have imported data back from like 4 years ago in some cases, and was just going through and adding a 12/31/2020, 12/31/2021 etc for each account, that way I could narrow down where there was a discrepancy, if anywhere (and I found a few, and figured out the discrepancy, added/removed a transaction, and viola, it all balanced again)… So it was working like that for the other accounts, and it was kinda nice because I could see what was already reconciled and it served as a check of sorts as I’m adding more historical transactions (that I’m not screwing anything up).

But you’re right, if I just delete the 2nd line and just use one line for the account, it works great.

I wonder why it worked for all the other ones, and not for this one (I had one account in there that had maybe 6 lines, all worked great). Anyway, not relevant, but I do miss ‘seeing’ the account reconciliation history now!

Feel to free to use it as you like! I suspect it would work fine the way you were doing it on accounts that had a “$0” opening balance.