I’m a new Tiller user and working on a new template (initially for me, but something I’d like to share), separate from but built on top of the core Balances sheet. My idea is to take the reported bank balances, and subtract off any manually entered transactions that marked to be cleared. My goal is to give a clear projected future cash flow.
So for example if I had a $2000 checking account balance today, but several future payments totaling $1500 that I’ve entered manually, the Pending Balances sheet would report $500 for this account.
Working from a copy of the Balances sheet, I’ve made some initial edits to get the Sheet References updated (including the Account, Amount, and Metadata columns from Transactions). My desired algorithm from there is to add a column to the “Sorted Assets” and “Sorted Liabilities” table data computing the sum of the transactions, and then modify the values displayed with
Balance - Transactions.
Assuming the Account reference is in
$Z$23 and the Amount reference is in
$Z$24, then I can use a (simplified) formula like
=SUMIF(INDIRECT($Z$23), "=Checking", INDIRECT($Z$24)) (“Checking” would be dynamically replaced with the Account in each row of Sorted Assets/Sorted Liabilities). This gives me all transactions for that account.
The trick here is parsing the JSON stored in metadata. I could do it with a script, but from the builder’s guide, looks like those are discouraged. I could do it with a regex, but that could get a bit slow for an account with a lot of history (to be fair, so could a script). For anyone not sure what the metadata looks like, here’s a sample:
I need to go after anything where
manualTransaction.reconcile = true.
Any suggestions on the best way to accomplish this? What does the Reconcile Transaction logic use to get at this data? Or where can I find that logic to inspect myself?