🏆 Upcoming Recurring Expense / Account Health Dashboard

I have found a minor calculation issue I believe in the sheet. The counting of credit card payments due seemed to be showing incorrectly. I had more than 1 credit card due within the period, but the information in cell K15 was showing that only one expense was from credit card debt. The formula in cell AD29 is looking at the Pay Method column instead of the Expense column from the Filter formula. I believe it should be specifying P4:P35.

=IFERROR(QUERY(UNIQUE(QUERY(FILTER({S4:S35}, MATCH($P$4:P35, INDIRECT($AK$13),0)), “SELECT Col1”)), “SELECT COUNT(Col1) LABEL COUNT(Col1)‘’”),0)

This should be:

=IFERROR(QUERY(UNIQUE(QUERY(FILTER({P4:P35}, MATCH($P$4:P35, INDIRECT($AK$13),0)), “SELECT Col1”)), “SELECT COUNT(Col1) LABEL COUNT(Col1)‘’”),0)

After changing this, the count showed the correct number of expenses that were going to credit cards.

Also, I found that the dollar values being displayed in K14 through K18 were not showing 2 decimal places when the numbers ended with 0’s. The formatting for the TEXT in these cells are “$####.##”, but should be “$#,##0.00” to make sure that the value always shows 2 decimal places and to (optionally) include the comma for the thousands separator. The easy fix would be to do the Find and replace (CTRL+H) and put $####.## to find, and $#,##0.00 for replace, make sure that Also search within formulas is selected, then Replace all.

1 Like

Thanks for catching that! The text insights area was sort of a last minute addition, and I don’t even have them in the version that I use. So I wouldn’t have caught it during normal use. My version of this sheet has a picture of my dog taking up about the same amount of space. When offering it to the community, I decided to add some text insights because it looked kind of bare without them.

Thanks for the formatting tip! I tried for a little bit to get this exact formatting, but didn’t know zeros were the answer. I’ve updated the template to fix these findings. Thanks again!

Hi! Finally getting around to working through this setup… and I feel like i’m almost there! But having a few stumbles too - hoping you can help?

Found I had some errors on the Generated Expenses - Upcoming sheet as I didn’t have the “Note” column in my transaction sheet. Fixed that one :smile:

On the Upcoming Expenses sheet under both checking accounts and savings accounts, I have #N/A

And on the ‘Generated Expenses - Upcoming’ not all transactions are being recognised as paid - not sure how to dig into this thou?

1 Like

Sorry to hear you’re having some troubles! I’m here to help though! When you hover over the #N/A error, what does it say? I’m not exactly sure what that could be off the top of my head. The checking accounts are coming from columns X7:X16, which are being pulled in from the Accounts sheet. All accounts with a type of Checking should be present. Similarly, savings accounts ultimately come from columns X20:X34, which should be all accounts on the Accounts sheet with a type of Savings. If you look in column X on the sheet, does it list the accounts there correctly, or is there an error there as well?

For the Generated Expenses - Upcoming sheet, the logic to mark an expense as paid is looking at a timeframe depending on the frequency chosen for the expense. This is to hone into the right expense to mark off, and avoid expenses falling off erroneously. Each frequency has their own timeframe they look at. For a weekly expense, it will only mark the expense as ‘paid’ if the transaction date you tagged in Transactions is within +/- 3 days of the expense date. So a weekly expense due on January 23rd has to be tagged to a transaction with a date between January 20th and January 26th in order to be marked as paid and fall off. Could this be what’s happening here?

If the expenses that are not falling off are under the frequency of “One-Time”, there was a bug that another user found that is causing this. The fix can be found here.

Expense frequency windows:

  • Weekly: +/- 3 days
  • Bi-weekly: +6 / -7 days
  • Semi-monthly: +6 / -7 days
  • Monthly: +14 / -13 days
  • Bi-monthly: +/- 29 days
  • All Else: +/- 60 days
2 Likes

Hello @1Email2RuleThemAll !

For some reason I’m having an issue with Upcoming Expenses not showing anymore.

I tracked it down to the ‘Generated Expenses - Upcoming’ worksheet, for some reason everything is showing up at N/A on the Generated Expenses window.

It has an error : “Array arguments to REPT are of different size.”

Here is a screenshot :

I looked up that error and it has something to do with rows not matching the array…but I know I haven’t messed with anything like that!

Let me know, thanks!

1 Like

This typically happens if there’s an expense entered without a frequency chosen on Recurring Expense Entry, or if there’s a frequency and start date, but no Expense name.

If you look at the list in columns A:J on the Generated Expenses - Upcoming sheet, do you see any errors there? Specifically in the Occurrences column? Take a look at the below screenshot where I removed the frequency for the Mortgage entry. I suspect something like this is going on in your workbook. The solution is to go to that entry on Recurring Expense Entry and set the value that’s missing. Please let me know if this helps, or if we need to dig further! Thanks!

4 Likes

That solved it.

Thank you!

1 Like

Hello. Is there a way to hide the recurring transaction if it is zero automatically? For example, I created a formula that pulls the balance of several credit cards that I have. Some of them have a balance while others do not.
=IF(A1 = 0, 0, IF(A1 >= 25, A1 * 0.02, B1))

A1 is changed to another sheet that has the balance static. I couldn’t use the balance sheet as I am unsure if it lists them by the highest amount. I used the Business Dashboard in my case (‘Business Dashboard’!G71). and B1 is changed to the cell in your Recurring Expense Sheet (Gxx).
This formula in essence pulls in the minimum due as opposed to the full balance showing up as pending. Maybe there was an inbuilt method to do this, but I couldn’t find it. So now that is fixed, I now am listed upcoming transactions of zero. Now I know that I can just deactivate that row so it disappears from the Upcoming Expenses Sheet but wanted to know if there was a way that I could automatically do that without actually needing to manually uncheck it in the Recurring Expense Sheet.

Also, for some reason my Chase credit card is pulling the entire balance in the Due next section regardless of what I do. It doesn’t do the same for my other credit cards that are listed on the credit card sheet.

I went ahead and completed this running a script. I’ll add it below for anyone interested.

function onEdit(e) {
var range = e.range;

// Check if the edited cell is in column G
if (range.getColumn() == 7) {
var sheet = range.getSheet();
var dropdownCell = sheet.getRange(range.getRow(), 1); // Corresponding cell in column A

// Get the value in column G
var valueG = range.getValue();

// Only modify column A if column G is not blank
if (valueG !== "") {
  if (valueG > 0) {
    // Set the dropdown to "✔" if the value in column G is greater than zero
    dropdownCell.setValue("✔");
  } else {
    // Set the dropdown to empty if the value in column G is zero or less
    dropdownCell.setValue("");
  }
}

}
}

And the following one will be to have the script run retroactively so you dont need to reinput the numbers.

function applyScriptToExistingData() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange(“G:G”); // Adjust the range as needed
var values = range.getValues();
var outputRange = sheet.getRange(“A:A”); // Corresponding range in column A

for (var i = 0; i < values.length; i++) {
var value = values[i][0];
var cell = outputRange.getCell(i + 1, 1);

if (value !== "") {
  if (value > 0) {
    cell.setValue("✔");
  } else {
    cell.setValue("");
  }
}

}
}

I apologize for the late reply. I had fractured my hand recently, and have been minimizing my exposure to typing.

So if I understand correctly:

  • You want to figure out the minimum amount due to avoid a late fee, instead of the minimum amount due to avoid interest charges.
  • To achieve this, you’re entering credit cards in as recurring expenses on the Recurring Expense Entry sheet.
  • To get the desired balance for each credit card, you created a formula that looks at the Business Dashboard solution.
  • This formula multiplies the total amount due by .002 if that amount is above $25 to get 2% of the balance. Credit cards without a balance are written as zero.
  • These zero balanced cards are showing up on the Upcoming Expenses dashboard in the “Expenses due by next check” section to the right.
  • You want to filter these cards with a zero balance from showing up in this section.

Recurring expenses are supposed to be expenses with a regular or semi-regular amount that occur on a cadence (weekly, bi-weekly, etc…). Credit cards and balances are handled by a different part of my solution. I never really intended on credit card totals ever being entered into the recurring expense list, so your fix might cause some unforeseen issues.

You can remove the cards with a zero balance from showing up by just adding another filter exception to the formula in P3 on the Upcoming Expenses sheet to filter out expenses that have a balance of zero. This is a more lightweight and “Tiller friendly” solution than your script. It will also have less unintended effects vs. using that script. Just replace P3 on Upcoming Expenses with:

={"Expense","Due Date","Amount","Pay Method","Auto-Pay";
    { 
        SORT(
        {
            IFERROR(QUERY(FILTER({INDIRECT($AF$11), INDIRECT($AF$12), INDIRECT($AF$13), INDIRECT($AF$14), INDIRECT($AF$15), INDIRECT($AF$16), INDIRECT($AF$17)}, INDIRECT($AF$17) = 1, INDIRECT($AF$16) = "" , INDIRECT($AF$12) < Q1, INDIRECT($AF$13) <> 0), "SELECT Col1, Col2, Col3, Col4, Col5"), {"","","","",""});
            IFERROR({QUERY(FILTER({INDIRECT($AK$13), INDIRECT($AK$16), INDIRECT($AK$15), INDIRECT($AK$17), INDIRECT($AK$20)}, INDIRECT($AK$13) <> "", INDIRECT($AK$16) < Q1, VALUE(INDIRECT($AK$15)) > 0), "SELECT Col1, Col2, Col3, Col4, Col5")}, {"","","","",""})
        }, 2, TRUE)
    }
}

But I would like to suggest a different solution altogether:

Credit cards really do not belong in Recurring Expenses. It would be better to change the Credit Card Settings sheet to add a new column that holds your calculation. Then, you can pull that column in to use on the Upcoming Expenses dashboard instead of the Total Due amount currently being used.

I can probably write down some instruction for you to implement this on the side, if you’re interested. I do not want to incorporate this logic into the main solution, however, as I don’t really want to promote using the minimum amount due to avoid a late fee. It is antithetical to maintaining proper financial health. I personally do not acknowledge that amount exists. The minimum amount due, to me, is always the minimum amount you need to pay to avoid interest charges (your previous statement balance).

Ok, now I understand that you are incorporating the statement balance. That would definitely be the way I go about it once I pay off my credit card with a high balance. I use my credit card to pay all my transactions and then pay as I go throughout the month and then throw extra money to pay off the high balance if there is one. So, I usually never look at the minimum payment unless it’s for my Chase Unlimited, which has a 0% interest for like 15 months after I did a Credit Balance Transfer. I am now focusing on the other card, then will pay off the Chase before the interest rates are due which is a while away. If you incorporate the statement balance, then could you have a formula that doesn’t give the entire balance if the card is at a zero percent interest? Could you also explain the difference in Total Due and Total Pending? Does the Total Due incorporate the statement balance while the Total Pending incorporate the full current balance on the credit card?

Also, how would I sort the Savings Accounts section via Account Name or amount of Balance Available/Allocation? Also, the same thing for the Credit Card section. I don’t know if I would break any of the formulas if I did any sorting myself. Best wishes with your fractured hand btw

I’m hoping you can help me troubleshoot an issue I’m having with the Upcoming Expenses dashboard. I’m seeing accounts labeled #N/A in both my Checking and Savings account sections.

Tiller NA Error 2

I’ve tried to track down the source of this error, but I can’t seem to locate it. I’ve double-checked the sheets that the dashboard is linked to, but everything seems to be in order there as well.

Any guidance would be greatly appreciated!

1 Like

When you select the cell with the error, the formula bar should be populated with a formula that may have a clue on what is causing the N/A. Can you please send a screenshot of the formula bar when you select the cell with the error?

1 Like

Hello,

The accounts that show up here are those on the Accounts sheets where the Type of account is Checking or Savings. The #N/A error is typically because there are no accounts marked as these types.

Could you please open the Accounts sheet and verify what the Type is coming through as for each of your checking and savings accounts?

So glad they highlighted this in the email list! This is exactly what I have been trying and struggling to build myself! Can’t wait to dig into it all! Thank you so so much

1 Like

I could kiss you for this! Thank you so much and I’m so sad I had to wait until now to find it!

1 Like

If you are finding value in this solution, I have just released a companion solution that projects upcoming paycheck expense periods! This has been in the works for a long time, and I finally got around to finishing it up!

Paycheck Expense Forecast

@mcmiller
@jjfelton4
@hickson567
@annaisakiwi
@YouBet96
@kyle1234
@sparker81
@MidnightPrzm
@beccap2010
@nikkinisly