🏆 Scheduled Transactions template: Projected account balances and credit card payments; auto-reconciled transactions

@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