Credit Card Settings Sheet for Google Sheets

[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.

[YouBet96]

V2.0 works well. Thanks for the fixes because I had noticed the same issue with the AccountID. Two of my accounts were not pulling over in the previous version and now they are all there.

[bentyre1]

V2 works well for me, this is where I jumped into using this template, however one small thing that I found was the data validation in column M for Auto pay didn’t have the advanced option of Arrow selected. It was instead on Plain text.

I was just going over things like this with my wife this weekend and was wishing there was something like this. Thank you @1Email2RuleThemAll I’m looking forward to the more solutions it sounds like you are working on.

[1Email2RuleThemAll]

Thanks for pointing that out! I fixed that in the template.

In all my personal sheets, I use the plain-text option for dropdowns. I think it just looks cleaner. I like having blank rows fully blank, and the arrows extending into them just bug me. I completely overlooked that column when making the community template version.

[bentyre1]

Ahh, makes sense, I see that you can just use something like the spacebar and then select them. I guess I’m just accustomed to seeing the dropdown arrow so I know that there is validation in that cell.

[1Email2RuleThemAll]

Version 2.1

Adds support for accounts that do not come through on the Accounts sheet as having a type of “Credit”. The accounts that could be selected were originally 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.

[alwaysunnyaz]

Howdy! I am new to the Tiller Community and just passed my first 30 days, Woot woot! I have been manually adding transactions and tracking my account balances and very happy there is something like Tiller that exists!

My situation is that I use the excel version of Tiller as it’s more familiar to me. I do pay off my credit card balances off every month and need a way to track my statement balances that I know will be due in the current month. Do you happen to know if a solution like the one you created is available for the excel folks? I searched to forums and didn’t find anything and do not want to modify your work.

I appreciate your time and look forward in hearing from you.

Cheers!

[1Email2RuleThemAll]

I apologize, I did not see this comment for some reason! Unfortunately, I do not know of any solutions that are Excel-based. If you would like to rework the sheet into Excel for your own purposes, I do not have a problem with that! I wish I could help out, but I do not have access to Excel and have not developed in it in quite some time. After working with Sheets, I couldn’t turn back