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

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!

1 Like

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

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.

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

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

@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

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:

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

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.

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