AutoCat is a Google Sheets Add-on that allows you to quickly categorize transactions based on a configurable ruleset. It’s great for automatically categorizing recurring transactions, those from the same merchants, and anytime you need to quickly categorize historical data. Turn on the Automatic Run feature to have matching transactions automatically categorized for you as they’re added to your Tiller-powered Google Sheet.
Install and Create the AutoCat Sheet
- Install the Tiller add-on found here.
- Open one of your Tiller spreadsheets.
- Open the Add-ons menu and choose Tiller > Tools > AutoCat > Create
- Click the “Create” button to install the AutoCat sheet.
How to Build an AutoCat Rule
- Select a category.
- Define the criteria that should be evaluated against your transactions.
You can create rules to categorize transactions based on the following criteria:
- Description contains
- Account contains
- Institution contains
- Amount (range) is greater than, less than, equal to, or a range that uses some combination of those
At a minimum you should include a description in order for the rule to find a match.
A transaction must match ALL specified criteria to be categorized by AutoCat. Simply using the “Description contains” criteria is sufficient for catching most of your transactions unless your purchases at a specific merchant (meaning they’d have the same description) fall across multiple categories in which case using the amount criteria may be handy.
Note: If you select a category, but do not add any other criteria to the rule, and run the ruleset, AutoCat will categorize all transactions that do not match the other rules using this category. In essence, this is a “wildcard” feature to categorize all transactions that don’t match on other rules using a catchall category. If this catchall rule is first, all transactions will be categorized this way so if you want to use this feature, make it the last rule in the list.
Setting up Amount Criteria
By default, AutoCat evaluates amounts using absolute values when running the rules against your Transactions sheet, meaning it ignores the sign for the amount (positive or negative i.e. it doesn’t care whether it’s an income or expense transaction).
Ranges should be configured from smallest to largest.
To set up a rule targeting a range of absolute value transaction amounts, you can use the following structure:
- Amounts greater than a certain value use >
- Amounts less than a certain value use <
- Amounts equal to a certain value just enter the value
- For a range that’s less than one value but greater than one value use < - >
To match on amounts less than $30 use “<30” in your rule
To match on amounts greater than $30 use “>30” in your rule
To match on amounts between (and equal to) $10 to $40, use “10 to 40” in your rule
To match on amounts greater than $10 but less than $40 use “>10 <40” in your rule
In most cases absolute values should be sufficient for configuring the amount criteria for your rules.
Overriding Absolute Values
You can override the absolute value default and configure the amounts to pay attention to the sign by adding key phrases to the amount criteria.
Remember that when overriding absolute values and filtering for negative amounts that a lower negative value is numerically greater than a higher negative value. For example -$1 is greater than -$4
For example, if you’d like to set up a rule that categorizes any transactions from Starbucks that are less than $10 but greater than $2 as “coffee” you could use the following amount range “neg $10 to neg $2.”
Use the following key phrases to add sign matching to amount criteria:
For positive values:
inc, income, positive, pos, inflow, inflows, or incoming.
For negative values:
exp, negative, neg, charge, charges, outflow, outflows, outgoing, expense, expenses
Running the AutoCat Ruleset
When you’ve finished building your rule set use the Run AutoCat dropdown at the top of the AutoCat sheet to run the ruleset against your Transactions sheet for all transactions or only the ones that are uncategorized based on how the “Apply to” drop down is configured.
Changing the Apply To Option
Set the “Apply To” drop down to “All” if you want to overwrite your existing categorization data for transactions that match any rules. Set it to “Uncategorized” if you only want it to run on uncategorized transactions.
When you run AutoCat, or the Automatic Run feature runs it when new transactions are added to your sheet, the Categorized Date column on the Transactions sheet will record the categorized date and time for all transactions where AutoCat automatically applied the category.