Transactions vs. Balances

[mtjfinancial]

I created a sheet which reconciles “per bank” vs. “per books” to make sure I have everything recorded in Tiller. This also alerts me to any manual transactions I may have double counted or rows I may have accidentally deleted. My expectation was that the formula which sums up account transactions would match downloaded balances, barring some mistake on my part.

However, this morning, I noticed two transactions have hit the transaction sheet and yet my balance for the account is actually yesterday’s balance (although the updated status indicates “today”). I’m guessing this might happen from time to time, but it makes me wonder how frequently this occurs. Has anyone else noticed this?

[warren]

@mtjfinancial I have seen this happen a few times myself. I can usually get this reconciled by completing another update my spreadsheet again via the Tiller Money Feeds.

[Chris5]

I’m looking to do something like this but I’m not good with formulas at all, how did you create a sheet which reconciles “per bank” vs. “per books”? thanks!

[mtjfinancial]

I’m not the best with formulas, but after some experimenting, this worked for me…

For per bank, I did a lookup from the balances sheet:

=VLOOKUP(A9,Balances!B:D,3,0)

where A9 is the account name.

For per books, I used:

=B9+(SUMIF(Transactions!$B:$B, A9, Transactions!$K:$K)-SUMIFS(Transactions!$K:$K, Transactions!$B:$B, A9, Transactions!$P:$P, “manual”))

where B9 is the beginning balance (for me this was 12/31/19). Also, I changed that + sign immediately after B9 to a - if it were a liability account.

Any difference between these “bank” and “books” means, most likely, something in Tiller is off.

[mtjfinancial]

I should add that in “manual” there are * symbols in there, like " manual " (but without spaces) but those don’t seem to show up in my post

[heather]

@mtjfinancial I think if you use the “code block” it will show the * - Discourse thinks you’re trying to italicize the text