Categorise expenses in a spreadsheet

Once a day, the rows on the 'Expenses' tab of your spreadsheet that do not have a category yet are read, and the AI files each one under one of the categories you list in plain language, your own chart of accounts. The category is written into a Category column on the same row (added at the end of the tab the first time), so each expense is filed once and your other columns are never changed.

An answer that is not one of your categories is never written: an expense that fits none of them is marked Uncategorised, so you can see it and file it by hand. Rows that already have a category, including ones you corrected yourself, are left alone, and a row with no description waits until it has one. At most 200 expenses are filed per run, and the run's message says how many are still waiting. If an expense cannot be filed, the run is marked as failed with the row numbers, and it is tried again the next day.

You need a Google Sheets connection and an Anthropic connection, a spreadsheet with an 'Expenses' tab holding Date, Description and Amount columns, the link to that spreadsheet, and your list of categories.

Run every day.Run every day.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set categories to blank.Set categories to blank.

Set expensesTab to Expenses.Set expensesTab to Expenses.

Set maxPerRun to 200.Set maxPerRun to 200.

Replace "Replace with , in categories and save it as categoryText.

Split categoryText by , and save it as categoryList.Split categoryText by , and save it as categoryList.

Make categoryText lowercase and save it as lowerCategories.Make categoryText lowercase and save it as lowerCategories.

Split lowerCategories by , and save it as categoryKeys.Split lowerCategories by , and save it as categoryKeys.

Google SheetsRead the rows of spreadsheetUrl. Save it as sheet.Read the rows of spreadsheetUrl. Save it as sheet.

If sheet.headers doesn't contain DescriptionIf sheet.headers doesn't contain Description

Yes

Stop and report a problem: The '@expensesTab' tab has no column headed Description. Its first row must hold the column names Date, Description and Amount, with one expense on each row below. If your expenses are on another tab, put that tab's name in 'expensesTab'.Stop and report a problem: The '@expensesTab' tab has no column headed Description. Its first row must hold the column names Date, Description and Amount, with one expense on each row below. If your expenses are on another tab, put that tab's name in 'expensesTab'.

No

No steps

Keep each item in sheet.headers where item in lowercase is category and save it as categoryHeaders.Keep each item in sheet.headers where item in lowercase is category and save it as categoryHeaders.

If categoryHeaders isn't empty and sheet.headers doesn't contain CategoryIf categoryHeaders isn't empty and sheet.headers doesn't contain Category

Yes

Stop and report a problem: Rename the column headed '@categoryHeaders[0]' on the '@expensesTab' tab to Category, with a capital C, then run it again. That is the column the categories are written into.Stop and report a problem: Rename the column headed '@categoryHeaders[0]' on the '@expensesTab' tab to Category, with a capital C, then run it again. That is the column the categories are written into.

No

No steps

Keep each item in sheet.rows where item.Category is blank and item.Description isn't blank and save it as newExpenses.Keep each item in sheet.rows where item.Category is blank and item.Description isn't blank and save it as newExpenses.

Count the items in newExpenses and save it as newCount.Count the items in newExpenses and save it as newCount.

Take the first maxPerRun items of newExpenses and save it as batch.Take the first maxPerRun items of newExpenses and save it as batch.

Set filedCount to 0.Set filedCount to 0.

Set uncategorisedCount to 0.Set uncategorisedCount to 0.

Set failedRows to an empty list.Set failedRows to an empty list.

Set lastError to blank.Set lastError to blank.

For each expense in batch:For each expense in batch:

If a step below fails, save the error as fileErrorIf a step below fails, save the error as fileError

Try

Run an AI agent: Expense: expense.Description Amount: expense.Amount Date: expense.Date and save it as verdict.Run an AI agent: Expense: expense.Description Amount: expense.Amount Date: expense.Date and save it as verdict.

Make verdict.output.category lowercase and save it as answerKey.Make verdict.output.category lowercase and save it as answerKey.

Find the position of answerKey in categoryKeys and save it as position.Find the position of answerKey in categoryKeys and save it as position.

Set category to Uncategorised.Set category to Uncategorised.

If position is greater than or equal to 0 and answerKey isn't blankIf position is greater than or equal to 0 and answerKey isn't blank

Yes

Change category to categoryList[@position].Change category to categoryList[@position].

No

No steps

Google SheetsSet Category: category on row expense.row_number of expensesTab in spreadsheetUrl.Set Category: category on row expense.row_number of expensesTab in spreadsheetUrl.

Add 1 to filedCount.Add 1 to filedCount.

If category is UncategorisedIf category is Uncategorised

Yes

Add 1 to uncategorisedCount.Add 1 to uncategorisedCount.

No

No steps

On error

Add expense.row_number to the end of failedRows and save it as failedRows.Add expense.row_number to the end of failedRows and save it as failedRows.

Change lastError to fileError.message.Change lastError to fileError.message.

If the length of failedRows is greater than or equal to 5If the length of failedRows is greater than or equal to 5

Yes

Stop repeating.Stop repeating.

No

No steps

If failedRows isn't emptyIf failedRows isn't empty

Yes

Join failedRows with , and save it as failedList.Join failedRows with , and save it as failedList.

Stop and report a problem: New expenses filed: filedCount. Could not file the ones on these rows of the '@expensesTab' tab: failedList (the error was: lastError). Any expense still without a category is tried again on the next run.Stop and report a problem: New expenses filed: filedCount. Could not file the ones on these rows of the '@expensesTab' tab: failedList (the error was: lastError). Any expense still without a category is tried again on the next run.

No

No steps

If newCount is greater than maxPerRunIf newCount is greater than maxPerRun

Yes

Set leftCount to newCount minus maxPerRun.Set leftCount to newCount minus maxPerRun.

Finish the run: New expenses filed on the '@expensesTab' tab: filedCount, of which uncategorisedCount marked Uncategorised. leftCount more are waiting: at most maxPerRun are filed per run, so the rest follow on the next runs.Finish the run: New expenses filed on the '@expensesTab' tab: filedCount, of which uncategorisedCount marked Uncategorised. leftCount more are waiting: at most maxPerRun are filed per run, so the rest follow on the next runs.

No

No steps

If filedCount is 0If filedCount is 0

Yes

Finish the run: No new expenses to file: every row with a description on the '@expensesTab' tab already has a category.Finish the run: No new expenses to file: every row with a description on the '@expensesTab' tab already has a category.

No

No steps

Finish the run: New expenses filed on the '@expensesTab' tab: filedCount, of which uncategorisedCount marked Uncategorised because none of your categories fit.Finish the run: New expenses filed on the '@expensesTab' tab: filedCount, of which uncategorisedCount marked Uncategorised because none of your categories fit.