🏆 Scheduled Transactions template: Project future account balances; calculate credit card payments; auto-reconcile transactions

[rajgrover511]

It worked !

Thank you for your help.

[rajgrover511]

Hi Rob,

I’m in need of help to with a discrepancy in my Credit Card Statement and the Scheduled Transactions sheet.

Here’s the situation:

Credit card statement balance for May: $581.24
Statement billing cycle: 4/12 - 5/8
Transactions in Tiller (within billing cycle): Add up to $581.24
Scheduled Transactions Sheet: Transaction Amount $538.50
Credit card closing date: 5/8

I’ve double-checked the dates and amounts in both the statement and Tiller, and I can’t seem to find where the $42.74 difference is coming from. I’m wondering if there might be something I’m overlooking or if there could be another factor I haven’t considered.

In addition, auto-reconcile is not working so I wonder if the problem could be related.

Do you have any suggestions on how to troubleshoot this? Any help would be greatly appreciated!

[rhowell]

Hey @rajgrover511

The Scheduled Transactions sheet calculates the Credit Card Pmt. Amount by subtracting transactions that occur after the closing date from the current credit card account balance. What happens if you add up the transactions in Tiller after 5/8 and subtract them from the current credit card balance?

Regarding auto-reconcile, do you have an example of a scheduled transaction and the actual transaction that should be matched?
For the scheduled transaction, provide values from these columns: Asset Account, Transaction Date, and Transaction Amount.
For the actual transaction, provide values from these columns: Date, Amount, Account, and Transaction ID.

[sskennel]

Rob,

Thanks for updating the Scheduled Transaction sheet. I continue to enjoy using it.

I noticed a very minor regression from version 0.96 to version 1: cells in column M in otherwise empty rows contain $0.00. Previously these cells were blank, which seems cleaner.

Regards,

Roger

[rhowell]

Hey Roger,

I agree, it’s much cleaner if those cells are blank. I’ll update shortly. I’m also looking into a bug with the Current Credit Card Activity calculation. Let me know if it seems off for you.

[rajgrover511]

Hi Rob,

The problems have been resolved. I had an incorrect date for the credit card cloding date and after my last download auto-reconcile is working correctly. Thanks again for your help.

Raj

[sskennel]

Hi Rob,

Yes, there’s a discrepancy in the Credit Card Current Activity column for one of my cards, but I believe this is due to the card having been used for an unauthorized transaction, canceled by the issuer, and replaced with a new card, which resulted in a new Account name midway through the monthly billing cycle. My other cards have correct values for Credit Card Current Activity.

— Roger

[rhowell]

Roger, thanks for checking the Credit Card Activity.

I updated the sheet to remove the extraneous zeros in column M and incremented the version number to 1.01. You can copy over the new sheet or just update the formula in M1 to this:

=arrayformula(if(row(A:A)=1,"Transaction Amount",if(isnumber(I:I),round(I:I,2),if(isnumber(L:L),round(L:L,2),if(isnumber(H:H),round(H:H,2),if(isnumber(K:K),round(K:K,2),iferror(1/0)))))))

[sskennel]

Thanks, Rob. Works great!

— Roger

[dylan]

@rhowell Hey Rob, really appreciate you putting this together. I’m testing the Credit Card Payments and the column" Credit Card Current Activity" seems to be including transactions classified as [Transfer] from the Transactions sheet which is leading to inaccurate totals. For example, a credit card statement goes from 6/2 - 7/1 and payment is due on 7/25. However, the previous month balance (5/2 - 6/1 statement) was due on 6/25 and classified as [Transfer] on the Transactions sheet, but is being included in the total between 6/2 - 7/1. This leads to the total balance due on 7/25 being incorrect. Let me know if this makes sense or if I am missing anything.

[rhowell]

Hey @dylan,

Thank you! I thought there was a bug in the current activity calculation, but I hadn’t found it. For my cards, the difference between the due date and the next closing date is only 3 days, and I guess I haven’t look at the current activity in that window.

I’d previously accounted for the possibility that a payment would throw off the current activity calculation, but I then changed the balance calculation to use the current card activity when no other values (actual, card payment, or recurring amounts) are available, which resulted in a circular dependency.

There are two options to resolve this:

1. Set the current card activity calculation to sum purchases (debits) only, or
2. Remove the current card activity value from the balance calculation and sum all activity less reconciled payments.

Option 1: paste the following formula into cell ER1:

=map(EJ:EJ,EM:EM,lambda(account_,prev_closing_date_,if(row(account_)=1,"From Transactions:"&CHAR(10)&"Activity After Previous Closing Date",
  if(account_<>"",
  ifna(
  LAMBDA(data_, query_, headers_,
    QUERY({data_}, 
      LAMBDA(query_text_, data_row1_,
        REDUCE(query_text_, FILTER(data_row1_, ISTEXT(data_row1_)), 
          LAMBDA(query_text_result_, data_col_name_, REGEXREPLACE(query_text_result_, "(?i)`" & data_col_name_ & "`", "Col" & MATCH(data_col_name_, data_row1_, 0))))
      )
      (query_, ARRAY_CONSTRAIN(data_, 1, COLUMNS(data_))), headers_)
  )
  (Transactions!A:ZX, "select sum(`Amount`) where `Account ID` = '"&filter(DT:DT,DS:DS=account_)&"' and `Date` > date '"&text(prev_closing_date_,"yyyy-MM-dd")&"' and `Amount` < 0 label sum(`Amount`) ''", 1),0),iferror(1/0)))))

Option 2, part A: paste the following formula into cell M1:

=arrayformula(
  if(row(A:A)=1,"Transaction Amount",
    if(isnumber(I:I),round(I:I,2),
      if(isnumber(L:L),round(L:L,2),
        if(isnumber(H:H),round(H:H,2),
          iferror(1/0))))))

and part B: paste the following formula into cell K1:

=map(C:C,G:G,J:J,
  lambda(descr_,recur_date_,closing_date_,
    if(row(descr_)=1,"Credit Card Current Activity",
      if((istext(filter(DS:DS,DS:DS=descr_))),
      if(closing_date_="",iferror(1/0),
      ifna(filter(EU:EU,EJ:EJ=descr_,EM:EM=closing_date_),iferror(1/0))),
      iferror(1/0)))))

I’ll probably include both options in a update. Let me know which one you prefer. That goes for other users, as well.

[dylan]

Thank you for the quick update. I went with option 1 for now, but still playing around with your tool to get what I need. I am trying to convert yours into a complete cash flow tracker for my small business. I have something built in Google Sheet (not using Tiller), but it is not nearly as sophisticated. Happy to share the file and a video demo I did to see if it gives you any inspiration.

[rhowell]

Sounds interesting. What additional functions do you need in order to track your cash flow?

[dylan]

I need a way to input expected future transactions per credit card and the expected transaction date. I would also need a way to add expected future. I would also need an area to add future expected income transactions. The cashflow graph would then take all of that into account and tell me if I spend some money today if I will my bank account will over draw any day into the future.

Here is my sample sheet and video if you are curious.

[rhowell]

Regarding the first part, generating future credit card transactions to see their effect on cash flow:

One way to do this with the Scheduled Transactions sheet is to enter the transactions as debits from the asset account you’ll use to pay the credit card bill, but rather than setting their dates to when they’ll occur, set them to the date the credit card bill will be due. It works great, but if you’re using the sheet to automatically calculate credit card payments, you’ll have to manually reconcile the transactions when Tiller downloads the corresponding actual credit card transaction to avoid deducting them twice. You can use the notes column to track which credit card they’ll be charged to.

As for the future income transactions, that’s exactly what the Scheduled Transactions sheet does, unless I don’t understand you.

[dylan]

Future income makes sense. For future credit card transactions, I was hoping to store the information (start, end and due date) for the credit cards in one place (see my sample sheet above) and then just have to the put the anticipated date of the charge in and the sheet would do the rest of the work.

[rhowell]

For a more modular approach, @1Email2RuleThemAll has a couple sheets that you might be interested in:

Credit Card Settings Sheet for Google Sheets

Overview What This Workflow Does The Credit Card Settings template sheet allows users to add a few more metrics to the Credit accounts present in the Tiller core Account sheet, and receive some much needed data points in return. This sheet can be used as-is to track available credit card balance, card expiration date, upcoming total due, current statement purchase total, and next due date for all credit accounts. Developers can also use this as a base for building reports using the data. I us…

Generated Recurring Expenses Workflow

Overview This documentation describes how the sheets that I developed can be used as a base by others to develop their own custom reports. I will be releasing a few solutions that use this same sort of setup. Write a brief description of what the template does. Essentially, this template takes a user supplied list of inter-grain recurring expenses on one sheet (Recurring Expense Entry), and then dynamically generates a row for every expected due date of those expenses that fall between two u…

…

[ramji241]

Hi @rhowell, first of all, thank you for this template. It is exciting to see the community building upon Tiller’s product, and I’m impressed by your spreadsheet/coding skills.

I’m sure the worksheet is working as intended but I’m a bit confused by results. Here are three issues I’ve encountered:

1. Today is 7/10/2024. I created credit card and recurring transactions with dates in the Starts Recurring column that will occur over the next week. However, generated transactions begins on 7/23/2024, skipping over these transactions completely.
2. I receive rental income at the beginning of each month. I entered these recurring transactions to begin on 8/1/2024, but generated transactions only shows them beginning on 9/1/2024. To get them to start on 8/1/2024, I have to set the Starts Recurring date to 7/1/2024 even though that’s in the past.
3. I have a solar bill that gets paid on the 1st of each month and that I’ve estimated at $400/month under the Recurring Amount column. I received my bill and it is lower, so I entered the amount from the bill under Actual Amount. But generated transactions still shows $400. How should I use recurring transactions where I estimate the monthly amount but want to override the next payment amount when I receive my bill?

Thank you again!
Ashwin

[jessa256]

Hi, @rhowell - I love this sheet however I am running into a problem. I have two transactions on the same day, of the same amount, from the same account (one is a security deposit and one is last months rent payment - both $211) and one is reconciled while the other is not. I tried to dig into the issue and both transactions show up in columns EW:FC (rows 207 and 208 from the transactions sheet) but the option to override doesn’t show the correct transaction…any help would be appreciated!

[rhowell]

Hi @jessa256 and welcome!
You’ve bumped up against one of the limitations of the way the sheet presents transactions to choose in the override dropdown: if multiple transactions occur on the same day with the same amount and the same description, and one of them is reconciled (either automatically or picked to override), the others will be filtered out of the override dropdowns.

You can either

• copy the transaction ID of the second transaction from the Transactions sheet and paste it (‘Paste special - values only’) into column O on the Scheduled Transactions sheet, or
• change the description of one of the transactions on the Transactions sheet (e.g., to Justin Scott Payment - deposit), which should allow you to select it from the override dropdown, or
• check the box in column P on the Scheduled Transactions sheet to reconcile the second transaction.

Glad you’re finding it useful!