@sskennel The new sheet is designed to pull the balance as of the last transaction date, rather than the latest balance, because it seems that transaction data generally lags balance data. Let me know if this isnât the case for your accounts. The trade off is between using the latest balance but having to fiddle more with manual reconciliation vs using a slightly older balance but having the automatic reconciliation work better. I can include a toggle to choose between the approaches.
@rhowell - Thanks for the explanation. Yes, transaction data does lag balance data in my spreadsheet. The problem is that I often have transactions in my account that arenât listed in Scheduled Transactions. The spreadsheet âknowsâ that my future balances are different from what the Scheduled Transactions sheet is showing me, even if it doesnât yet know about the transactions that are causing the discrepancy. Iâd like to see those transactions reflected in my future balances as early as possible. Iâm not sure what the best tradeoff is here.
@sskennel Try changing the value in hidden cell DZ1 to 0 to get the latest balances.
I updated Scheduled Transactions to version 0.91:
- added filters for the transactions table and
- adjusted the logic that returns the balance dates and amounts.
2 Likes
@rhowell When I tried to copy the account name from cell BH1 in version 0.9 to cell BJ1 in version 0.92 I get:
âThe data you entered in cell BJ1 violates the data validation rules set on this cell.â
UPDATE: I needed to choose an account from the dropdown menu in cell A2 before I could enter an account name in cell BJ1.
@rhowell Columns J and BX (Credit Card Closing Date) shouldnât be hidden. The user needs to fill in data in J and copy data from BX.
Theyâre not hidden; theyâre grouped under the plus sign above the credit card payment column. But you make a good point that they should probably be expanded by default.
Edited to add: Iâve expanded all the grouped columns in my shared sheet.
I should have said collapsed rather than hidden.
@rhowell - Iâve updated to version 0.96 of the Scheduled Transactions template. I have a transaction that recurs weekly. Sometimes one or more such transactions remains outstanding when a new transaction is created. The template often has trouble knowing which transactions to match when this happens. The template should not automatically reconcile a transaction in the Scheduled Transactions sheet with one in the Transactions sheet if the Transaction Date of the transaction in the Scheduled Transaction sheet is earlier than the Date of the matching transaction in the Transactions sheet.
1 Like
@sskennel Matching transactions is a bit of a shot in the dark. The sheet looks for actual transactions that are within a specified number of days and dollars of the scheduled transaction. It works for most transactions, but, as youâve found, transactions with the same value that occur near each other in time are difficult to distinguish. I donât want to switch the sheetâs default to match actual transactions that only occur on or after the scheduled date; I find the imprecision in both directions more useful than not.
If your scheduled transaction dates are generally precise, my first suggestion is to lower the value in hidden cell AE1 from the default of 4 (days) to a value that knocks out the mismatches. As you play with the value, look at cell AG1 for feedback on how many transactions the sheet is still matching.
If that doesnât solve the problem, you can replace the formula in cell AG1 with the below, which will match actual transactions that only occur on or after (within the number of days in AE1) the date of the scheduled transaction:
=let(
matched_array_,
map(A:A,B:B,M:M,
lambda(account_,date_,amt_,
let(
account_id_,filter(DR:DR,DQ:DQ=account_),
if((account_<>"")*(date_<>"")*(amt_<>0),
ifna(
query(
filter({EZ:EZ,EW:EW-date_,abs(EY:EY-amt_)},
FA:FA=account_id_,
EW:EW-date_>=0,
EW:EW-date_<=AE1,
sign(EY:EY)=sign(amt_),
abs(EY:EY/amt_-1)<=AF1),
"select Col1 order by Col2 asc limit 1",0),
iferror(1/0)),
IFERROR(1/0))))),
{"Matched Transaction ID"&CHAR(10)&"<- Filter Boundaries -> "&counta(matched_array_)&" matched transactions";chooserows(matched_array_,sequence(rows(matched_array_)-1,1,2,1))})
Let me know what works for you. If lowering the value in AE1 works, I may lower the default. If changing the formula works, I may substitute separate + and - days values for the current +/- value.
1 Like
@rhowell - Thanks! Iâll experiment with this as transactions come in and see what value makes the most sense for my situation.
1 Like
Iâve finally had some time to complete the documentation for this solution, so Iâm re-releasing it.
It lets you schedule future transactions (both one-time, like checks, and recurring, like bills) and uses them to project the balance of one or more accounts. Functions like a check register on steroids.
If you havenât checked it out since it was called Short Term Cash Flow, take a look. It has a couple of powerful new features:
- Calculates credit card payments: Shows upcoming credit card payment amounts and current activity.
- Reconciles automatically: When Tiller downloads an actual transaction that matches a scheduled transaction, the scheduled transaction is marked as reconciled, removed from the balance calculation, and hyperlinked to the actual transaction.
- Projects balances for multiple accounts: Itâs useful to see balances for all your cash accounts if you regularly transfer money between accounts, such as taking advantage of high interest rates by moving deposits from checking to savings and then moving money back to checking.
2 Likes
Hi Rob,
Iâm really enjoying using your Scheduled Transactions template. It is so helpful for cash flow planning!
Iâm running into an issue with the âCredit Card Current Activityâ column. Itâs not populating with data, even though Iâve set up a new credit card in the template with the following details:
- Transaction Date: 5/31/2024 (statement balance deduction date)
- Closing Date: 5/10/2024
I can see transactions after the closing date in the âTransactionsâ tab, but they arenât appearing in the âCredit Card Current Activityâ section.
Also, In the âCredit Card Pmt. Amountâ column, the credit card balance is the same as that shown in the âBalancesâ tab, rather than the amount due to the due date.
Could you please help me troubleshoot this? Iâd be very grateful for any guidance you can provide.
Thanks so much for your time and for creating this valuable resource!
Hey @rajgrover511 , thanks for the kind words.
Regarding the credit card calculations, have you entered the due date in the Starts Recurring column?
Hmm. Can you unhide the columns on the right side of the sheet and share whatâs in columns EJ - EU?
Thanks, thatâs helpful.
For your credit card accounts, can you verify that the Account Id s shown in column DT on the Scheduled Transactions sheet are the same as the Account ID s shown on the Transactions sheet for transactions from those accounts?
Hi Rob,
Apologies for the delayed response, Iâm currently in Singapore and we may be in different time zones.
The Account IDs for credit card transactions in the Transactions sheet are empty. Since these credit cards are set up as manual accounts and transactions are uploaded via CSV, Iâm wondering if there might be a connection.
Yes, I think youâve identified the problem. The Scheduled Transactions sheet uses the Account ID as the key to link transactions to accounts. If you fill in the Account ID column on the Transactions sheets with the corresponding values from the Accounts sheet, the credit card calculations ought to work.