Bill Payment Tracker: Account Column Needed + Case-sensitive Match

Google Sheets
1

The Bill History Query section of the Bill Payment Tracker could really use an Account column to show where the historical bills are paid (from which account). Is there an easy way to add that on the right? (I looked at the Dev Notes but couldn’t easily figure this out.)

Also, the tool description says the text entered in the Description field in the bill tracker setup should match the Description field of the Transactions sheet. This works, but the match is case-sensitive, which is unfortunate given the number of linked transactions that get pulled in with an ALL CAPS description field.

Aside from this, I’m finding this sheet very useful for tracking our many recurring payments – everything from periodic bills to Amazon autoships to Apple app subscription renewal dates!

2 Likes
2

Interesting point, @mnathans. I’m tagging the builder, @jono…

3

I’d to this to see if there is a way have it so that you can link a certain description on the transaction page, but have a simpler version of it on the description of the bill tracker

For instance I have a bill “Rent” and I am not able to pull the last transaction cause it shows up as something like “ConRealtyWEB payment x35841234” or something of that sort.

4

Hi @mnathans ,
I’m glad to hear the sheet is very useful for you.

Here’s how you can add an Account column in the Bill History Query.

  1. In the hidden part of the sheet, add the word “Account” to U9. This will locate your Account column in the Transactions sheet.

  2. Fill down V8 and W8 to V9 and W9 so the Column and Range for the Account column will be listed.

  3. Adjust the formula in F6 to include the new Account column, which will be referenced as INDIRECT(W9). You will also need to adjust the LABEL by creating 3 space and 4 space empty labels, since the Amount field covers 3 columns and you want the Account column after that. This is just a weird Google Sheets hack. Here’s the new formula:

=IF(I4="",IFERROR(1/0),iferror(IFNA(QUERY({INDIRECT(W6),INDIRECT(W7),INDIRECT(W8),INDIRECT(W9)},"SELECT Col1,Col2,' ',' ',-1*Col3,' ',' ',Col4 WHERE Col2 CONTAINS '"&I4&"' ORDER BY Col1 DESC LIMIT 15 LABEL ' ' '',' ' '',-1*Col3 '',' ' '', ' ' '' "),"None")))

I don’t think the number of spaces are showing correctly in the formula above when it is copied and pasted. And the number of spaces matters.

Here’s the formula with S’s for each space, just as a example. This won’t work with the S’s in place.
=IF(I4="",IFERROR(1/0),iferror(IFNA(QUERY({INDIRECT(W6),INDIRECT(W7),INDIRECT(W8),INDIRECT(W9)},"SELECT Col1,Col2,'S','SS',-1*Col3,'SSS','SSSS',Col4 WHERE Col2 CONTAINS '"&I4&"' ORDER BY Col1 DESC LIMIT 15 LABEL 'S' '','SS' '',-1*Col3 '','SSS' '', 'SSSS' '' "),"None")))

  1. Then add an Accounts label to M5.

This worked for me. Let me know if it works for you.

I’m not clear on the case-sensitive issue you mentioned. Could you provide a bit more detail. It seems case-sensitive matching will prevent unwanted ALL CAPS matching.

Jon

1 Like
5

Jon, thank you, that worked beautifully. (I think there is an extra close paren at the end of your code example.) Great idea to insert the Ss. Now I know why I’m not a spreadsheet hacker. :grinning:

As for the case sensitivity of the Description match, it might be that my preferred behavior is different from yours. If I insert the string “Verizon” in the description in a row in the Bill Setup section, I’d like that to match all transactions that include Verizon and VERIZON (I have both, since the banks are inconsistent when pushing the transaction data out.) I know I can normalize the description using AutoCat but that’s an extra step.

The other small issue with the description match is that I have to keep the descriptions short in the tracker to make sure they will match all pertinent line items in my Transactions sheet. For example, “Amazon” will match transactions Amazon.com, Amazon Prime, Amazon Purchase etc, but if I use something longer and more descriptive, it won’t match all of those.

By the way, it’s really useful to be able to see the account in the bill history. Many of my bills are paid by different methods: some with a credit card and some from checking. I suggest updating the tool to reflect this change!

Mark

6

@mnathans .
I’m glad those changes worked for you. I’ll discuss with some others whether we update the template like this for everyone.

You are correct. There was an extra close paren in the code examples. I’ve updated the post above to correct that.

To make the Bill History Query match both Verizon and VERIZON, you can change the WHERE clause in the formula in F6 from:
WHERE Col2 CONTAINS '"&I4&"'
to:
WHERE LOWER(Col2) CONTAINS LOWER("""&I4&""")

I did a quick test and it worked for me.

Best,
Jon

7

Hi @jfournier520 ,
You might take a look at AutoCat v2. This tool allows you to rename Descriptions using the Advanced Rule Processing.

More info here:

8

Hi @jono that link sadly isn’t working and bring me to a page that doesn’t exist.

9

Sorry @jfournier520 ,
Try this link instead. Watch the video and see the Advanced Rule Builder part where you can normalize and update the Description column:

I’ve updated the link in the post above as well.

10

Jon, I changed the Where clause it did adjust the bill tracker match to make it case-insensitive. I also took a look at the new AutoCat, which I was not aware of, and it is more useful tha the older version. Thanks for your quick responses!

Mark

1 Like
11

This is amazing! Even though much of my bills are on auto, I am so grateful for this bill tracker so I can be sure I’ve got enough money in my bank accounts ahead of time. Brilliant.
Speaking of, I saw this formula to add accounts is made for the Bill History query. Possible to add the account column to the Upcoming Bill Payments section? It’s SUPER helpful to just at a glance see the upcoming payments planned for my business account vs my personal account.

12

Hi @syada ,
I’m glad to hear you think this sheet is amazing and that it helps you manage your money.

I took a quick look at how you might add an Accounts to the Upcoming Bill Payments sections. But there are a few issues in doing this.

Accounts were able to be added to the Bill History Query section because we can look at the Transactions sheet and find out which Account the bill was paid from. The same bill can be paid from any account and that account can change from bill to bill.

With the Upcoming Bill Payments section, the sheet doesn’t know which account the future bill will be paid from.

There are probably some solutions to look at the last account the bill was paid from,

Another option would be to allow an Account to be set for each Bill in the Bill Setup section.

I haven’t fully tested this, so you might want to make and save a copy of your sheet before trying this.

But you could try adding a column to the right of Column C. Add Account in the new Column in row 5 and 23. Then load account names in the Bill Setup section to match each Bill.

Finally, use a formula in the Upcoming Bill Payments section that lookups the Account name based on the Description. =VLOOKUP(C6,C$24:D,2,FALSE) for cell D6 and fill it down. This looks at the Description of the Upcoming Bill Payment and then finds the Account it was linked to in the Bill Setup.

Again, I haven’t tested this so there is a chance it could break some other parts of the sheet. But something like this could work.

Jon

13

@jono

Along the same lines of this post, I’d also like to add my own custom columns to the bill tracker. The account column is a great idea, I would use this as well. I will attempt the steps to add this that you laid out above. In addition to this I would like to add another custom column to track whether the bill is paid automatically or if it is a manual payment I need to do. Also, I think it would be good to have a general notes column to keep notes to myself about that bill, or even a website column to take me directly to the website where that bill is paid for example. None the less, I can think of many columns I would personally want just for myself, maybe others would not think they are needed, but that’s fine. If I just want to add blank columns with no formulas, just manual entry info, is this easy to do so?

14

Hi @Lieder ,
I think for the most part you can add your own custom columns to the bill tracker.

It sort of depends on which section of the sheet you want to ad the columns to.

If you are adding them to the Bill Setup section, in green, I don’t think there would be any issue.

The Upcoming Bill Payment section is generated by formulas in A5 and D5. Since this section is dynamically generated by those formulas and will change, I wouldn’t advise adding any data there. Unless you do it via changes in the formulas.

The Bill History Query section is also generated by a formula in F6. That data is also dynamically generated so any changes would have to be made in the formula.

I’d likely make a copy of the original Bill Payment Tracker sheet and then make the changes you want to the copy. If something breaks or doesn’t look right, you can revert back to the original.

Lastly, some Tiller sheets (from Tiller and the Community) can handle new columns just fine. Sheets that receive data like the Transactions sheet and the Balance History can have columns added for your custom data.

Other sheets run the risk of breaking if you start adding columns. (This especially can happen if there are QUERY() functions that use Column letters in the SELECT formulas, as they won’t get updated when a column is added.)

Does that help?
Jon