Credit Card Settings Sheet for Google Sheets

[1Email2RuleThemAll]

Overview

What is the goal of your workflow? What problem does it solve?
The Credit Card Settings template sheet allows users to add a few more metrics to the Credit accounts 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 use this sheet in two other custom solutions that do just that. I only open this sheet to update the “Prior Close Day” when needed.

How did you come up with the idea for your workflow?
I built my own finance tracker in Google Sheets back in 2019, before I knew of Tiller. This sheet was a part of that workflow. When I joined Tiller, there were a few solutions that I built for myself that I could not live without. Those solutions used this sheet, so I ported it over to work with Tiller.

Installation

Only two Tiller core sheets are required for this to work:

• Accounts - Used to pull back all accounts of type Credit or class Asset.
• Transactions - Used to pull back the sum of all transactions against each credit account since the last statement end date.

The following solutions have a dependency on the Credit Card Settings template:

• Generated Recurring Expenses Workflow

To install:

• Open the attached workflow
• Right click the Credit Card Settings sheet
• Copy to your Tiller solution
• Rename from Copy of Credit Card Settings to Credit Card Settings
• Setup

Setup

Select a credit account using the dropdown. Once selected, the Total Balance, Hidden status, and AccountID (hidden in column A by default) will populate for that account. If you add the same account more than once, you will be alerted by the cell turning yellow for the duplicate instances.

For each row, add the requested info into the green fields. The ones marked optional are not needed to make this sheet function (not used in calculations). They may or may not be used in other solutions that I (or others) develop, however.

• Active - Required if the account is currently active. Leave blank if it is not.
• Card APR - Optional. Enter the card interest rate percentage here.
• Expiration Date - Optional. Enter the date that the credit card will expire.
• Credit Limit - Required. What you enter for the credit limit is subtracted from the total balance to calculate the available credit total of the card.
• Statement Close Day - Optional. Enter the day that the current active period for the card will close.
  – It should be visible on the credit card account website, usually near the current balance.
  – This is only used to generate the current Statement Close Date, which is not used for any totals (only a “heads up, this is closing soon”). If it’s not provided, the current close day will be 1 month from the prior close day.
• Prior Close Day - Required. Enter in the day that your most recent statement for the card closed.
  – This can be found on your most recently drafted credit card statement.
  – Unfortunately, statement close days can change, depending on the credit card. If your statement close day changes from month-to-month, you must change this value after each statement period closes in order for the totals due to be accurate.
• Due Day - Required. Thankfully, this does not change from month-to-month (unless you change it), so you can set and forget this.
  – There is a chance that your credit card company can change it, but they have to notify you well in advance before doing so.
• Primary Pay Method - Optional. This is a dropdown list that is populated from a list in the hidden section. This list is queried from the Tiller Accounts sheet.
  – There is a place in the hidden area to adjust what accounts can be selected (see below).
  – You can choose to make only Checking accounts show and/or exclude hidden accounts here.
• Auto-Pay - Optional. There are two options to select from:
  – : Auto-pay is set up to pay the full amount due with no restrictions
  – ●: Auto-pay is set up with restrictions. Some scenarios include setting auto-pay to:
  (1) the minimum due monthly amount
  (2) a set amount that may or may not cover the expense
  (3) not auto-pay if the amount is over a certain threshold
• Notes - Optional. You can enter anything you want in this field.

When you enter the values above, the grey area on the right will populate with calculated metrics.

• Available Credit - Calculated from subtracting the Total Balance of the card from the Credit Limit provided.
• Statement Purchases - The total amount that falls under the current statement period for the card. This amount is not due on the next due date, but rather on the due date after that. It is the sum of all purchases against the credit card since the Prior Close Day.
• Statement Close Date - Generated from the Statement Close Day, or prior close day + 1 month.
• Prior Close Date - Generated from the provided Prior Close Day for the card.
• Upcoming Total Due - The total due on the next due day. Calculated by subtracting the Statement Purchases total from the Total Balance of the card.
• Next Due Date - The date that the next payment is due for the card.
• Current Statement Due - The date that the current open statement will be due for the card.

Usage

Once you download and setup the sheet, you are good to go.

The only time changes are needed is when one of your credit card statements end. You must change the Prior Close Day value to reflect the day that the most recent statement for the card closed, otherwise the Statement Purchases and Upcoming Total Due might be off.

Some credit cards are easy, and have the same close day every month, but most seem to vary by 1 or 2 days.

Permissions

Is it ok for others to copy, use, and modify your workflow?
Yes! If you can get any value out of my solutions, then please adopt, use, modify to your hearts content.

Notes

Always compare your due balances projected in this sheet with your credit card statements to make sure nothing is out of sync.

Please note, redeeming credit card points to cover transactions will throw the calculations off for the current and prior periods. This is because the credit card companies decided rewards redemption does not apply to your prior statement balance like normal payments. Instead, it is subtracted from the current statement balance before it touches the prior statement purchases.

FAQ

Please ask any questions you have below and I will reply, and update this section accordingly.

Solution Link

Credit Card Settings

Version changes

• 1.1: Removed a bug that was causing the due dates to not align for a few days out of the month.
  – To update from 1.0, replace the formula for Next Due Date in cell U6 to the below.
  – If you changed the position of the Prior Close Day column, change J7:J in the formula to reflect the new column.

=ARRAYFORMULA({"Next Due Date"; IF( K7:K, 
IF
(
    TODAY() <= DATE(YEAR(TODAY()), MONTH(Today()), J7:J),                 
    DATE(YEAR(TODAY()), MONTH(Today()), K7:K),                 
    DATE(YEAR(TODAY()), MONTH(TODAY())+1, K7:K)
), 
""
)})

• 1.2: Fixed a bug that was causing the Next Due Date to project a month ahead if the due day was at the end of the month, and the statement close day was the beginning of the next month. This bug was introduced by the version 1.1 change, and overwrites it.
  – To update from 1.0 or 1.1, replace the formula for Next Due Date in cell U6 to the below. If you have not moved or added any columns, the formula will work automatically.
  – If you changed the position of the Due Day column, change all instances of K7:K in the formula to reflect the new Due Day column.
  – R7:R should reference the Statement Close Date column

=ARRAYFORMULA({"Next Due Date";
IF
(
	K7:K, 
    IF
    (
        TODAY() <= R7:R,                 
        DATE(YEAR(TODAY()), MONTH(Today()), K7:K),
        DATE(YEAR(TODAY()), MONTH(TODAY())+1, K7:K)
    ), 
    IFERROR(1/0)
)})

• 1.3 (July 6, 2023): The formula fix in version 1.2 for Next Due Date was incomplete. This new formula should account for all scenarios needed, and overwrites/corrects all previous changes.
  – To update from any previous version, replace the formula for Next Due Date in cell U6 to the below. If you have not moved or added any columns, the formula will work automatically.
  – If you changed the position of the Due Day column, change all instances of K7:K in the formula to reflect the new Due Day column.
  – If you changed the position of the Prior Close Day column, change all instances of J7:J in the formula to reflect the new Prior Close Day column.
  – R7:R should reference the Statement Close Date column

=ARRAYFORMULA({"Next Due Date"; IF
(K7:K, 

        IF
        (
                 J7:J < K7:K
                ,IF
                (
                        TODAY() <= R7:R,                 
                        DATE(YEAR(TODAY()), MONTH(Today()), K7:K),                 
                        DATE(YEAR(TODAY()), MONTH(TODAY())+1, K7:K)
                )
                ,IF
                (
                        TODAY() >= DATE(YEAR(TODAY()), MONTH(Today()), J7:J),                 
                        DATE(YEAR(TODAY()), MONTH(Today())+1, K7:K),                 
                        DATE(YEAR(TODAY()), MONTH(TODAY()), K7:K)
                )
        ), 
    ""
)})

• 2.0: This is a major revision that fixes an issue with tying the credit cards directly to the Accounts sheet. Instead of pulling back credit card accounts directly, this version pulls them into a list in the hidden area. You then select the credit card accounts you want to set the extra metrics on via a dropdown. The rest of the functionality is the same as version 1.3.
  – To update from any previous version, rename your current Credit Card Settings sheet to something else, follow the install instructions above to install the new version, and then delete the old sheet after copying all the data in the green cells over where they belong.

• 2.1: Adds support for accounts that do not come through on the Accounts sheet as having a type of “Credit”. The accounts that can be selected are filtered down to just ‘Credit’ accounts to reduce clutter. Unfortunately, account types are set by the institution, and cannot be changed. If your institution sends an account through as a different type (i.e: “OTHER”), I have added a section in the hidden area (Y7:Y) that will allow you to select additional types. Adding a type to the list will add all accounts of that type to the dropdown.
  – To update from any previous version, rename your current Credit Card Settings sheet to something else, follow the install instructions above to install the new version, and then delete the old sheet after copying all the data in the green cells over where they belong.
  

  

[YouBet96]

Nice. I will use this one. I found another solution on the web at one point, but it was overkill for what I wanted as it was focused around the whole CC Churn game that many like to play (especially with Chase cards).

This core information is more of what I care about.

[1Email2RuleThemAll]

Thanks! I’m glad to see others already finding use for it I built this initially as just a small part of an overall larger solution for myself, but then realized the value it has as a standalone template, not just for developers but end users that just want to see these data points.

Please let me know if you have any questions, suggestions, or find anything awry!

[susandennis]

Very cool!! I have a tab where I manually keep a lot of this info but yours is way more inclusive and prettier! Thanks.

[1Email2RuleThemAll]

@susandennis @YouBet96

I’ve found a small issue with a formula in the sheet that calculates Next Due Date. I originally set this to change over dynamically once the due date for the month had been reached. This needs to be set up to change over when the Statement Close Day has been reached instead.

I’ve already corrected the template. To fix your copy, you can replace the formula in cell U6 (Next Due Date) with the below:

=ARRAYFORMULA({"Next Due Date"; IF( K7:K, 
IF
(
    TODAY() <= DATE(YEAR(TODAY()), MONTH(Today()), J7:J),                 
    DATE(YEAR(TODAY()), MONTH(Today()), K7:K),                 
    DATE(YEAR(TODAY()), MONTH(TODAY())+1, K7:K)
), 
""
)})

The way the due dates are generating now is technically correct, as once the due date for the month is reached, they update to reflect the new “next” due dates.

The issue is that the Statement Purchases and Upcoming Total Due totals do not update until the Statement Close Date has been reached.

The Current Statement Due date should always reflect the date that the Statement Purchases total will be due. This issue is causing a disconnect between these columns, but only between the days set for the Prior Close Day and Due Day.

• e.g.: You set the due day to 18, and prior close day to 22. From the 18th to the 21st, the Current Statement Due date will show that the Statement Purchases total is due a month later than it actually is. Then, when the 22nd hits, the total in the Statement Purchases column moves to the Upcoming Total Due column, and everything realigns.

The above code fix will make it to where the Next Due Date and Current Statement Due dates do not update until the Statement Close Date has been reached (generated from J7:J). This keeps everything harmonious within the sheet.

[randy]

Thanks for sharing this template with the community, @1Email2RuleThemAll. As with your other shares, the documentation both in the community and within the template itself is excellent.

I think this template strikes a nice balance for users interested in forecasting. It pulls in real-world transactions within a statement period, but doesn’t go too heavy on mining the Transactions sheet for payment history in ways that might cause performance issues.

I can see it being a useful modular component as other community members build up their workflows.

As part of the Tiller Builder Rewards Program , the Tiller team is excited to award you $50. Thanks so much for sharing and documenting this.

[1Email2RuleThemAll]

Thank you! I’ve been really happy with this sheet. It’s saved me from missing a payment a few times now!

[1Email2RuleThemAll]

Version 1.2 notes: I fixed a small bug that was causing the Next Due Date to project a month ahead if the due day was at the end of the month, and the statement close day was in the beginning of the next month. This bug was introduced by the version 1.1 change that stopped the due date from updating until the statement close date had been reached.

For people who have already downloaded the template, there is only one formula to change in cell U6.
This will overwrite the bug fix that was put in place for version 1.1.

– If you changed the position of the Due Day column, change all instances of K7:K in the formula to reflect the new Due Day column.
– R7:R should reference the Statement Close Date column

=ARRAYFORMULA({"Next Due Date";
IF
(
	K7:K, 
    IF
    (
        TODAY() <= R7:R,                 
        DATE(YEAR(TODAY()), MONTH(Today()), K7:K),
        DATE(YEAR(TODAY()), MONTH(TODAY())+1, K7:K)
    ), 
    IFERROR(1/0)
)})

[bwentwor]

I don’t have the skills to make some of these complex sheets, so who am I to poke holes? However, here are a few things that I noticed:

• If you make any changes to the ‘Accounts’ Sheet in terms of adding, removing, or hiding accounts, those changes do not get reflected in the ‘Credit Card Settings’ Sheet. Is the list a one-time import and any changes would require you to start over on the 'Credit Card Settings" Sheet?
• The import of accounts into the ‘Credit Card Settings’ sheet pulled in some accounts that are hidden on the “Accounts” sheet. If they are hidden on that sheet, I would want to hide them on the "‘Credit Card Settings’ Sheet. Is there some way to add that option. When I looked at cell Y4 initially, I thought that the setting would do that for me, but it was for some other purpose.
• Can you alphabetize the Account column without breaking the sheet’s linkages? That is one of my biggest issues with the 'Account" Sheet as everything breaks when you alphabetize there.
• Why are some columns like “Account,” “Statement Close Day,” and others listed with the tick mark in the top right of the column name right after adding the sheet? Is there a problem with the code?

Despite my questions and comments above, I want to thank you for a sheet that seems rather useful. I made a comment months ago that I wish statement close dates was something that Tiller could automatically pull from CC data. This approach is the next best thing!

[1Email2RuleThemAll]

@bwentwor

The first grey area to the left contains a live query that’s looking at the Accounts sheet. If a new account is added that is of the type of Credit, the query will recognize it, and pull it into the Credit Card Settings sheet. If you added a new credit card account and it did not show up, I would imagine that the Type of the account is not equal to ‘Credit’ on the Accounts sheet.

Original:

Adding Card:

The behavior around deleted and hidden accounts is intentional. In order to remove an account from the Accounts sheet, I believe you would need to delete all instances of that account in the Balance History sheet. If you were to do that, which is not recommended, the account name would disappear from the list on Credit Card Settings, but the values you entered for that credit card in the green fields would not. This would cause all credit cards listed after that to shift up, and align with the wrong settings.

The same behavior is present when hiding accounts. I initially had this set up to hide accounts that were set as hidden, but changed that when I realized this issue. If you set values for a credit card, then set that card to hidden, it would disappear from the list and the same issue as deleting them would occur. I am pulling in all accounts, regardless of if it was hidden or not to mitigate this issue.

Here’s an example. If I delete or hide the Discover Platinum card, the New Credit Card will shift up, and the settings to the right will be off.

Since this is built on top of the Accounts sheet, and there is special logic throughout, it unfortunately cannot be alphabetized.

The tick marks you are seeing indicate cells that have comments in them. Hovering over a cell with a tick mark will display the comment. This is just helper text that I included for better understanding of what’s going on. There is nothing wrong with the formulas.

[pixel.dick]

I’m starting to fill out my Credit Card Settings Sheet and I noted that some of my account showed hidden, even though they are not:

I investigated and found that on my accounts sheet, the sort order of the accounts in the visible portion of the sheet doesn’t match the sort order of the hidden section of the accounts sheet.

Is this something I need to change?
I noticed this when setting up my Primary Pay Method for my Cap1 Quicksilver. I had to show hidden accounts for the account I wanted to select (Cap1 Savings) to appear.

[pixel.dick]

After playing around a bit, I added in a couple of accounts, TECO Electric and TECO Gas. The data I previously entered for my Upgrade card did not move with the account name.

[1Email2RuleThemAll]

Solution:

The two lists on Accounts do not need to be in the same order. I believe the issue with the Hidden accounts mismatch is due to your extra columns in the Accounts sheet, Acct. Display Name and Min. Bal.

On Credit Card Settings in cell AA16, the column for Hide should be column 17 (Q). With your added columns, it would be 19 (S). I’m betting yours instead displays 6 (F). To fix this, you would need to change the formula in cell AA9 from:
=MATCH(Z9,INDIRECT("'"&Z$7&"'!$F1:$1"),0) + 5
to
=MATCH(Z9,INDIRECT("'"&Z$7&"'!$H1:$1"),0) + 7

Then, use the fill handle to drag the new formula down to AA17 to update the rest of the formulas. Everything else should stay the same, but the number and column for Hide should update.

Root Cause Analysis:

The standard way to reference another sheet in Tiller is to do so dynamically using VLookups.

=MATCH('Hide',INDIRECT('Accounts'!$A1:$1"),0)

This formula essentially looks at the first row, starting at the first column, and looks for a match to the column we want. It then returns the column index when the first match has been found. For this to work, the column names must be unique. Otherwise, the Vlookup will not be able to reach the second instance.

Unfortunately, the column names on the Accounts Foundations sheet are not unique. There are two ‘Hide’ columns: one for the left side list, and one for the right. The right side is the one that is usually referenced in other sheets (such as this one), and the left side should be ignored.

To account for this issue, sheets that reference the right list of the Accounts sheet must modify the standard formula to instead start on the column right after the left side (By default, E or F). This offsets the ‘true’ index of the column, so we then also add the number of columns to the number returned to get back to that true index value.

=MATCH('Hide',INDIRECT('Accounts'!$F1:$1"),0) + 5

Your column names are unique. However, adding them where you did pushed the non-unique Hide column that belongs to the left list into the “space” typically reserved for the right list. Since the dynamic lookup on my sheet is starting at column F, it is grabbing that first left list instance of Hide.

The formula provided for the solution will shift the lookup over by two columns to start at the first column of the right side list, H.

[1Email2RuleThemAll]

It looks like this is unintended behavior due to the way the Accounts sheet is sorted. The list of Accounts on Accounts appears to be sorted by the AccountID, which I incorrectly assumed would increment upwards and append any new accounts to the bottom. I must have gotten lucky with the three accounts I added during testing getting assigned higher Account IDs. But new accounts can have a “lower” ID than older accounts, causing them to be entered into the middle of the list, not at the bottom. This shifts cards around on my sheet because I’m using a live query to look at the Accounts sheet.

To fix this, I’m going to rework my sheet to get rid of the live query, and instead use dropdowns to select the accounts. You would have to choose each card name from the dropdown instead of it showing up automatically, but it would give better stability and control to the end user. This should be done in the next couple days, if not today.

[1Email2RuleThemAll]

Version 2.0

Hello, I have updated the template to address a few issues that were being caused by the way accounts are added to the Accounts foundations sheet. This change fixes an unforeseen bug, but also gives more control over what accounts are seen.

This is a major revision that fixes an issue with tying the credit cards directly to the Accounts sheet. Instead of pulling back credit card accounts directly, this version pulls them into a list in the hidden area. You then select the credit card accounts you want to set the extra metrics on via a dropdown. The rest of the functionality is the same as version 1.3.
– To update from any previous version, rename your current Credit Card Settings sheet to something else, follow the install instructions above to install the new version, and then delete the old sheet after copying all the data in the green cells over to where they belong.

[1Email2RuleThemAll]

I went ahead and was able to knock out these changes. Please follow the instructions for upgrading to version 2.0. This will fix the issue with accounts changing position.

This will not address your issue of incorrect hidden accounts, however. You will need to make the manual change I outlined in my previous reply in order for that to be fixed, as it is more of a hotfix for a specific situation/setup.

If you have any questions, please reach out!

[pixel.dick]

@1Email2RuleThemAll

●

Thanks! that worked.

[pixel.dick]

To be fair, the three affected accounts are all manual accounts. That may change the way the Unique ID is assigned.
The two TECO accounts were added after the the Upgrade Card account.

[1Email2RuleThemAll]

I’m not sure how Tiller generates the AccountIDs in the background, but you running into it for sure means others will as well. Since the AccountIDs are more like a GIUD than an incrementing numerical value, it’s safest to not count on those ever being in order.

Thanks for bringing this issue to my attention! I had been wanting to make these changes anyway, as it would gives users the option to select the cards they want to add extra metrics to, as opposed to a complete list showing regardless. So if you don’t want a card in the list, it doesn’t have to be.

[pixel.dick]

I like the latest version. With the drop down on the Account field I can organize them as I want. I have my active account in alphabetical order and a blank line separating my inactive accounts.