Tiller AI AutoCat

Google Sheets Show & Tell
1

I was getting annoyed manually categorizing everything in Tiller, or creating AutoCat rules, so I wrote some code to use Open AI (and previously categorized transactions) to automatically set a Category and clean up the Description field to be cleaner and more consistent with how I’ve named things perviously.

I’m now using this entirely instead of AutoCat and there are probably 1 or 2 transactions in 100 I need to manually adjust.

This requires getting an Open AI developer account and installing some Apps Script so requires a bit of technical know-how, but it’s not too bad. Cost is about a $.02 a day for me in OpenAI API calls (although will be transaction volume dependent).

More info and installation instructions are here: GitHub - sjogreen/tiller_ai_autocat: Apps Script code to use Open AI to automatically categorize financial transactions (designed to work with Tiller Finance Feeds and Google Sheets).

Please give me examples of things that don’t work properly if you try this out.

2

Impressive! Our team tried a quick session on something similar in chatGPT and only a few were successful. Thanks for sharing.

3

By request on another thread, here’s a video of this in action:

Video

Here’s what this shows:

  • Starts with the Tiller Sample Data for builders sheet
  • Cuts out a few descriptions and categories as provided.
  • Runs the AI AutoCat script (I cut out some of the video time as it’s kind of slow – why I run it automatically at midnight).
  • You’ll see the AI Autocat script starts to fill in descriptions and categories based on how similar transactions were categorized historically.
  • At the end of the video I paste back in what I cut out and toggle back and forth between the originals and what my code generated. You’ll see only one category is different (an amazon transaction) – everything else is done automatically.

I’ve been testing this for a while now and have completely moved over to it from Tiller’s AutoCat. It costs me about .01/day in Open AI fees.

I would love for Tiller to incorporate something like this in the extension so it’s just available to everyone. In the meanwhile, I’m going to try to package this as an Add On so it’s easier to install. Will work on that next.

1 Like
4

Would love for you guys to take this code and incorporate it in the extension – there are lots of ways it could be improved, but for a few days of work I’m pretty pleased with how it works. I don’t use the built in AutoCat at all anymore, as this both learns from prior manual categorization and novel categories in a way that rules can’t do (and are cumbersome to set up).

1 Like
5

Awesome idea and something I’m really interested in, but I couldn’t get it to work with either my data or the template builders sample data. Below is the log data from the sample data.

Dec 28, 2023, 5:36:39 PM Info Found 29 transactions to categorize
Dec 28, 2023, 5:36:39 PM Info Looking for historical similar transactions…
Dec 28, 2023, 5:36:39 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “amazon” OR lower(C) contains “amazon”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:39 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “new york times” OR lower(C) contains “new york times”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:39 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “lifewise” OR lower(C) contains “lifewise”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:40 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “lioness life coach” OR lower(C) contains “lioness life coach”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:40 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “appletv” OR lower(C) contains “appletv”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:40 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “flying fish” OR lower(C) contains “flying fish”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:40 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “aaa” OR lower(C) contains “aaa”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:41 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “albertsons” OR lower(C) contains “albertsons”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:41 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “amazon” OR lower(C) contains “amazon”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:41 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “roto rooter” OR lower(C) contains “roto rooter”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:41 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “capitol hill apartments” OR lower(C) contains “capitol hill apartments”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:42 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “shell” OR lower(C) contains “shell”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:42 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “seattle sehawks” OR lower(C) contains “seattle sehawks”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:42 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “new york times” OR lower(C) contains “new york times”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:43 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “the paramount” OR lower(C) contains “the paramount”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:43 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “new yorker” OR lower(C) contains “new yorker”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:43 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “albertsons” OR lower(C) contains “albertsons”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:43 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “centurylink” OR lower(C) contains “centurylink”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:44 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “credit card payment” OR lower(C) contains “credit card payment”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:44 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “amazon” OR lower(C) contains “amazon”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:44 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “ups” OR lower(C) contains “ups”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:44 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “monsoon” OR lower(C) contains “monsoon”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:45 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “feathered friends” OR lower(C) contains “feathered friends”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:45 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “credit card payment” OR lower(C) contains “credit card payment”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:46 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “nordstrom” OR lower(C) contains “nordstrom”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:46 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “safeway” OR lower(C) contains “safeway”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:46 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “7 limbs yoga” OR lower(C) contains “7 limbs yoga”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:46 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “starbucks” OR lower(C) contains “starbucks”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:47 PM Info Looking for previous transactions with query: SELECT C, D, N WHERE D is not null AND (lower(N) contains “the paramount” OR lower(C) contains “the paramount”) ORDER BY B DESC LIMIT 3
Dec 28, 2023, 5:36:47 PM Info Processing this set of transactions and similar transactions with Open AI:
Dec 28, 2023, 5:36:47 PM Info Logging output too large. Truncating output. [{previous_transactions=[{updated_description=Amazon, original_description=Amazon, category=Clothes/Gear}, {original_description=Amazon, category=Clothes/Gear, updated_description=Amazon}, {original_description=Amazon, category=Clothes/Gear, updated_description=Amazon}], transaction_id=bd5bcg136c4127c85egdca8f, original_description=Amazon xxx-xxx-7407}, {original_description=New York Times, previous_transactions=[{updated_description=New York Times, category=Subscriptions, original_description=New York Times xxx-xxx-1396}, {updated_description=New York Times, original_description=New York Times, category=Subscriptions}, {category=Subscriptions, original_description=New York Times, updated_description=New York Times}], transaction_id=ega1dfba03feg54a18ecc1ge}, {previous_transactions=[{category=Insurance, updated_description=Lifewise, original_description=Lifewise}, {category=Insurance, original_description=Lifewise, updated_description=Lifewise}, {original_description=Lifewise xxx-xxx-2198, updated_description=Lifewise, category=Insurance}], original_description=Lifewise, transaction_id=aadacfe8dffgd8c35e1cc6c6}, {previous_transactions=[{updated_description=Lioness Life Coach, original_description=Lioness Life Coach xxx-xxx-4239, category=Guidance}, {updated_description=Lioness Life Coach, original_description=Lioness Life Coach, category=Guidance}, {updated_description=Lioness Life Coach, original_description=Lioness Life Coach, category=Guidance}], transaction_id=a0a51dg18ccf2cc9d6cdb7ed, original_description=Lioness Life Coach xxx-xxx-9990}, {previous_transactions=[{category=Streaming, original_description=AppleTv xxx-xxx-8979, updated_description=AppleTv}, {category=Streaming, updated_description=AppleTv, original_description=AppleTv xxx-xxx-1418}, {category=Streaming, original_description=AppleTv xxx-xxx-6703, updated_description=AppleTv}], transaction_id=gd3gda4gacc8cb1eca26ccfg, original_description=AppleTv}, {transaction_id=a6cbab70b9437ad1ab11f8fe, original_description=Flying Fish, previous_transactions=[{category=Restaurants, original_description=Flying Fish xxx-xxx-4455, updated_description=Flying Fish}, {original_description=Flying Fish xxx-xxx-4482, updated_description=Flying Fish, category=Restaurants}, {category=Restaurants, updated_description=Flying Fish, original_description=Flying Fish xxx-xxx-3974}]}, {transaction_id=d2fdc55cgec8f1adcffb247d, previous_transactions=[{original_description=AAA, category=Fees/Repairs/Maint., updated_description=AAA}, {original_description=AAA xxx-xxx-1408, category=Fees/Repairs/Maint., updated_description=AAA}, {updated_description=AAA, category=Fees/Repairs/Maint., original_description=AAA xxx-xxx-3033}], original_description=AAA xxx-xxx-3232}, {previous_transactions=[{original_description=Albertsons, updated_description=Albertsons, category=Groceries}, {original_description=Albertsons, category=Groceries, updated_description=Albertsons}, {original_description=Albertsons, category=Groceries, updated_description=Albertsons}], original_description=Albertsons xxx-xxx-2112, transaction_id=0c9de311bb4c7baedf7cbf6a}, {transaction_id=dbfbfecacb20gfdfg55b9cbd, original_description=Amazon xxx-xxx-7000, previous_transactions=[{original_description=Amazon, updated_description=Amazon, category=Clothes/Gear}, {category=Clothes/Gear, original_description=Amazon, updated_description=Amazon}, {updated_description=Amazon, category=Clothes/Gear, original_description=Amazon}]}, {previous_transactions=[{original_description=Roto Rooter, category=Household, updated_description=Roto Rooter}, {category=Household, updated_description=Roto Rooter, original_description=Roto Rooter}, {original_description=Roto Rooter xxx-xxx-5797, updated_description=Roto Rooter, category=Household}], transaction_id=0dd7bag642ebe3g3d9dc029d, original_description=Roto Rooter xxx-xxx-4766}, {previous_transactions=[{updated_description=Capitol Hill Apartments, original_description=Capitol Hill Apartments xxx-xxx-7055, category=Rent}, {updated_description=Capitol Hill Apartments, category=Rent, original_description=Capitol Hill Apartments xxx-xxx-6387}, {category=Rent, original_description=Capitol Hill Apartments xxx-xxx-6945, updated_description=Capitol Hill Apartments}], original_description=Capitol Hill Apartments, transaction_id=ffbgb806ebgc5gfd56f3acbd}, {previous_transactions=[{original_description=Shell xxx-xxx-9954, updated_description=Shell, category=Gas}, {category=Gas, original_description=Shell, updated_description=Shell}, {original_description=Shell xxx-xxx-6952, updated_description=Shell, category=Gas}], original_description=Shell, transaction_id=f51ga8dd08ef0d0842fb2c8b}, {previous_transactions=[{category=Fun, updated_description=Seattle Sehawks, original_description=Seattle Sehawks}, {updated_description=Seattle Sehawks, category=Fun, original_description=Seattle Sehawks xxx-xxx-6128}, {category=Fun, updated_description=Seattle Sehawks, original_description=Seattle Sehawks xxx-xxx-4231}], original_description=Seattle Sehawks, transaction_id=3cdg1gf93ba8a5gcf423a151}, {original_description=New York Times xxx-xxx-7399, previous_transactions=[{updated_description=New York Times, category=Subscriptions, original_description=New York Times xxx-xxx-1396}, {original_description=New York Times, category=Subscriptions, updated_description=New York Times}, {updated_description=New York Times, original_description=New York Times, category=Subscriptions}], transaction_id=de33381040e2aaf58627eace}, {original_description=The Paramount, transaction_id=6b2gag25bdaabedb0gfff0bd, previous_transactions=[{updated_description=The Paramount, category=Fun, original_description=The Paramount}, {original_description=The Paramount, category=Fun, updated_description=The Paramount}, {updated_description=The Paramount, category=Fun, original_description=The Paramount}]}, {transaction_id=9f9906cba0bfe3e7gefg6f6d, original_description=New Yorker, previous_transactions=[{category=Subscriptions, updated_description=New Yorker, original_description=New Yorker xxx-xxx-9557}, {updated_description=New Yorker, category=Subscriptions, original_description=New Yorker}, {updated_description=New Yorker, category=Subscriptions, original_description=New Yorker}]}, {transaction_id=01ed0aag3cdcgddc27346f97, original_description=Albertsons xxx-xxx-3899, previous_transactions=[{original_description=Albertsons, updated_description=Albertsons, category=Groceries}, {category=Groceries, updated_description=Albertsons, original_description=Albertsons}, {original_description=Albertsons, updated_description=Albertsons, category=Groceries}]}, {transaction_id=f432bf8fg7ecfc138de39bc5, original_description=CenturyLink, previous_transactions=[{updated_description=CenturyLink, category=Internet, original_description=CenturyLink}, {updated_description=CenturyLink, original_description=CenturyLink xxx-xxx-8153, category=Internet}, {original_description=CenturyLink, updated_description=CenturyLink, category=Internet}]}, {transaction_id=fcca7c2cee97deabg945bbaf, previous_transactions=[{original_description=Credit Card Payment Received xxx-xxx-7443, updated_description=Credit Card Payment Received, category=Transfer}, {updated_description=Credit Card Payment, category=Transfer, original_description=Credit Card Payment xxx-xxx-3138}, {original_description=Credit Card Payment Received, updated_description=Credit Card Payment Received, category=Transfer}], original_description=Credit Card Payment Received}, {original_description=Amazon xxx-xxx-2078, previous_transactions=[{category=Clothes/Gear, original_description=Amazon, updated_description=Amazon}, {updated_description=Amazon, category=Clothes/Gear, original_description=Amazon}, {category=Clothes/Gear, original_description=Amazon, updated_description=Amazon}], transaction_id=9286gb680b10aeedfcfcgdbb}, {previous_transactions=[{original_description=UPS xxx-xxx-4554, category=Misc, updated_description=UPS}, {original_description=UPS xxx-xxx-9271, catego
Dec 28, 2023, 5:36:48 PM Error TypeError: Cannot read properties of undefined (reading ‘0’)
at lookupDescAndCategory(ai_autocat:321:76)
at categorizeUncategorizedTransactions(ai_autocat:50:29)

6

Sorry about that – I need to add some more robust error checking, but this error log indicates something didn’t come back from Open AI properly. Usually this is a problem with your API Key. Did you get one and update that constant in the script?

Not sure how technical you are, but if you add some Logger.log messages around line 321 to log the actual response from Open AI, that will help you get to the bottom of what’s going on. Or if you give me a day or two I’ll try to update the script to print out better error messages.

7

Actually, take that back, let me make an update right now – give me 10 minutes.

8

Okay, I just posted an updated version on github: GitHub - sjogreen/tiller_ai_autocat: Apps Script code to use Open AI to automatically categorize financial transactions (designed to work with Tiller Finance Feeds and Google Sheets)

This should at least let you know if Open AI is returning an error message, and what it is. As I said I suspect you aren’t passing a valid API key.

1 Like
9

Is the API key supposed to begin with ‘sk-’?

10

Yep.

If you run this from the Apps Script code view what error messages print in the logs? It now should log the error.

11

I’m getting the following error: (Also, I notice you’re using a beta AI, rather than GPT-4, which would require modifying once they change or delete AI sets.)

image
image1426×301 37.5 KB

Could it be something with the range?
image
image
image1094×429 31.6 KB

12

Ok, got it to work with the sample data. You were right…GPT account issue.

When I try with my actual data, I get the exact same error as yossiea.

13

Yeah that’s definitely the problem – although it’s not obvious to me looking at the code why that isn’t working for you. Can you send me the headers row only of your transaction sheet so I can try to reproduce it?

In the meanwhile, if you just hardcode lastColLetter (it appears in two places) to the letter of your last Transactions sheet column I think it will fix this.

I’d love to fix this more generically though so that first row of your transactions sheet would be greatly appreciated (including any blank or extra rows)! Have you made any other changes to the code? It looks like somehow the passed in header row is undefined – I’m just a little stumped why it’s finding the other header letters correctly but crapping out here.

14

Here’s my header row, some of the columns are not text, but an arrayformula, which might be an issue??

Date	Description	Category	Amount	Hours to Earn	Links	Account	Tags	Account #	Institution	Month	Week	Transaction ID	Check Number	Full Description	Categorized Date	Metadata	Date Added	Type	Group	Note	Year	Merchant Name	Category Hint	Expenses	Income	Transfer	AutoCat Rule ID	AI AutoCat
15

I’m an idiot – I see the problem I think – I’m not accounting for column letters more than Z.

If you don’t mind, hard code lastColLetter to your last column and lets see if we can get the openAI part working for you; I will work on a fix to support more columns.

16

I changed the variable and the call to the variable to AD and it worked.
Although it did “fail” on Open AI’s side:

Error from Open AI: Sorry! We've encountered an issue with repetitive patterns in your prompt. Please try again with a different prompt.

most of those were Amazon purchases and I should really delete those. I’ll try again with some transactions where I remove the cat and see what happens.
I would love the script to know if I’m highlighting rows, and only process those rows as opposed to the whole page.

17

Interesting, I’ve never seen that one before – yes maybe call it separately on more diverse blocks of transactions.

In any case, I updated the code here: GitHub - sjogreen/tiller_ai_autocat: Apps Script code to use Open AI to automatically categorize financial transactions (designed to work with Tiller Finance Feeds and Google Sheets) to fix the “greater than Z” column issue. Thanks for the report!

PS: This is using some features only available in the gpt-4 preview. Once that’s out of beta I can update it to use gpt-4.

18

And yes, calling it with highlighted rows would be a good improvement, thanks for the suggestion.

19

Ok, I concur…too many columns is definitely the problem. I modified the function that returns column letters and got it to go above Z, but then it popped up another range error further down. Coming back to the forum, I noticed that you had modified the code so I copied in your edits instead of my hack job and it seems to be working!

20

@cps Just wanted to let you know that I installed and having running. Took me a brief moment to realize I have to pay for ChatGPT API usage but was fairly smooth sailing based on your instructions. I tried to remove the categorization from 15 items and AI Autocat was able to decifer about 12. Looking forward to how it performs with continual use. Overall feedback is great so far.

1 Like