About AutoCat

AutoCat allows you to automatically categorize transactions and override other columns in the Transactions sheet using a customized rule set.

How to Install AutoCat in your Excel Workbook

  1. Open your Tiller-powered Excel workbook

    1. If you haven’t created a workbook powered by Tiller yet, click here to access our Foundations Guide and get started.

  2. Open the Tiller Money Feeds add-in from the Data ribbon in Excel

  3. Sign in to Tiller if necessary

  4. Click the “Install AutoCat” button at the top of the add-in

How AutoCat Works

AutoCat uses filter and override columns in the rule set to understand how to read your Transactions sheet and apply the rules based on your customized criteria.

AutoCat only makes changes to the Transactions sheet.

At the most basic level AutoCat will categorize transactions based on a set of criteria you customize on the AutoCat sheet (a tab along the bottom). More advanced rules can be created with additional columns to further customize and build a more complex rule set.

Information about advanced rule options is included below.

Creating Simple Rules

⚠️ AutoCat rules for Excel workbooks are built directly in the AutoCat sheet. There are no sidebar UI features to help build rules at this time.

Type in a transaction description keyword into the “Description contains” column on the AutoCat sheet and assign a category in the Category column on the AutoCat sheet.

You can apply other basic criteria that AutoCat should match on like Account Contains or Institution Contains. You can also add amount criteria to the rule.

Read more below about how to format amounts in rules.

Description Contains

Most of the time a simple “Description Contains” filter will work for a basic rule set to capture recurring transactions and those with a description that are always categorized the same.

Choose a Category from the dropdown, enter “Description Contains” keyword(s) into that column in the ruleset and you have a basic rule.

Run AutoCat by clicking the Run AutoCat button in the Excel add-in.

Example rule:

Apply “Yoga” to all transactions where the Description Contains “asheville Commuussq” for example.

This would categorize any transaction that contains that text in the description as “Yoga.”

Rule processing order

The rules are processed in a top down order, so put your most fine grained (specific with more criteria) rules at the top of the sheet and broader (generic less criteria) rules lower down.

You can sort the AutoCat sheet using the built-in filter features in Excel. Be sure to turn the filter on before trying to sort in Excel. The header (blue) row must remain at the top and trying to sort without turning on the filter feature can cause the header row to move out of position 1.

Case & contains sensitivity

The filter criteria (Description Contains) is case insensitive, but it is literally a contains. So if you have a rule that just has the criteria for Description contains as “air” - perhaps for “Allegiant air” any other description that contains the word “air,” including in the middle of a word, would get categorized under that rule. So slightly more detailed/specific is better.

Amount Ranges

Use the amount min and amount max columns to set the minimum and maximum for the amount that the rule should look for. Enter these as absolute values.

If you need an exact match, enter that amount into both the min and max columns. E.g. if you want to set up a rule for your mortgage check that’s consistent every month you can use the Description Contains “Check #” and then use the Amount range $1200 for both the min and max.

If you need AutoCat to interpret the polarity (positive/negative) for the amount use the Amount Polarity column. You can also use other suffixes like "equals" to build more advanced rules. Read more here.

How to run AutoCat

After you have built a rule set you can run AutoCat using the Run AutoCat button near the top of the workbook section in the Tiller Money Feeds add-in sidebar in Excel.

Click the “Run AutoCat” button to run AutoCat against your categorized transactions.

You can run the ruleset against only uncategorized transactions (default) or your entire transactions sheet (all). You can configure this under the Run AutoCat Rules On section under Settings.

If you choose to have it run on all transactions (including those that are already categorized) this will overwrite existing categorizations where a rule matches so use this feature carefully.

How to run AutoCat automatically

You can turn on the AutoCat Runs on fill setting in the add-in and AutoCat will automatically categorize transactions as they're added to your Transactions sheet when you click the “fill” button. You can find the Auto run on fill setting in the Settings section of the add-in.

The automatic processing only works for newly added transactions. It will not automatically categorize transactions that were already in the sheet on the next sheet fill. You can manually run AutoCat to categorize those transactions that already exist in your sheet.

Creating Advanced Rules

AutoCat allows you to build rules to override and automate data customization in other columns in the Transactions sheet beyond just the Category column.

For example, if you wanted to apply a Tag in a Tags column in the Transactions sheet based on content in the Description column (or any other column in the Transactions sheet) you could build a rule to do this.

In the AutoCat rules sheet, each column performs one of two functions:

  • Filter criteria - search this column for matches

  • Override - override this column with my custom content

In our basic rule below the Description Contains column is the "Filter Criteria" and the Category column in the Transactions sheet is the column that will be overridden with the content selected in the Category column on the AutoCat rules sheet.

Filter Criteria

Filter criteria columns in the rules sheet determine if a transaction should be processed. (i.e. does the Transaction meet this criteria?)

A column is recognized as a filter criteria when it has a filter criteria suffix. (e.g. contains, equals, starts with, ends with) The full list of suffix options are below.

Filter criteria are AND-ed together— for a transaction to be processed, it must meet all non-blank rules.

Filter criteria columns must start with the column name to be searched. For example, “Description Contains” will search the column named “Description.” If a column does not exist in the Transactions sheet, the filter criteria is ignored. This is case sensitive. Make sure the first word in the criteria is capitalized. (i.e "Description contains" NOT "description contains")

Any Transactions-sheet column name can be used for a filter criteria. This includes custom columns you add that are not default in the Transactions sheet. Great examples are Tags, Note, or Client.

Filter Criteria Suffixes

For a column to function as a filter criteria, it must end in one of the following keywords:

  • “Equals” - text equals entire rule string exactly

  • “Contains” - text contains rule string

  • “Starts With” - text starts with rule string

  • “Ends With” - text ends with rule string

  • “Max” - value is less than or equal to rule value

  • “Min” - value is greater than or equal to rule value

  • "Polarity" - identifying whether the Amount filter should match on positive or negative

Polarity filter criteria

If you'd like to have one of your AutoCat rules search based on the polarity of the Amount column (positive or negative amounts) you can add an Amount Polarity column to your AutoCat sheet. Otherwise the Amount Min and Amount Max columns that are default columns in the AutoCat sheet are expecting absolute values.

If you want AutoCat to look for negative amounts (expenses or outflows) put the word "Negative" (without quotes) in the Amount Polarity column for that rule. If you want it to look for positive amounts (inflows, income, or refunds) put the word "Positive" (without quotes) in the Amount Polarity column for that rule.

Multiple Match Criteria

AutoCat supports multiple match criteria in text filter criteria fields.

For example, a “Description Contains” rule can be:

"Starbucks","Dunkin","Dutch"

Multiple match criteria are OR-ed. In other words, the rule will be applied if the row matches just one of the keywords in the list for the rule.

Each multiple-match criteria keyword must be wrapped in quotes and separated by commas.

Editing rows/columns

You can delete rows, add, and rearrange columns on the AutoCat sheet. You can also freeze the top row. The header keyword row (row 1) must be at the top.

Override columns

Override columns are any columns in the AutoCat rules sheet that are not recognized as filter criteria (i.e. they do not have a suffix). If a transaction matches on the rule's filter criteria, the values in any override columns for the rule will be applied to those columns in the matching rows on the Transactions sheet.

Any Transactions sheet column name can be used for an override.

For example, if we had a rule with “Description Contains” = “Starbucks” and “Description” = “Starbucks”

The transaction:

12/31/19 "Seattle Starbucks store 1234" $5.00

Would be updated after AutoCat runs with:

12/31/19 "Starbucks" $5.00

This feature is great if you want to clean up your descriptions. You can use a single rule to clean up descriptions and categorize. In the basic rule example above all Starbucks transactions would be categorized as Coffee and the descriptions would be unified to just say "Starbucks" instead of including the extra details about which specific Starbucks store it was.

It's important to note that once a transaction matches on one rule, it won't match on other rules so if you want to clean up or customize other columns you would exclude those entries from multiple match criteria rules.

E.g. if you put Starbucks in a multi-match rule for "coffee" you won't be able to clean the descriptions up for Starbucks.

By default override columns other than Category are not included in the AutoCat ruleset sheet. If you want to build additional override rules for Description, other default Transactions sheet columns, or your own columns (see next section) you need to manually add these columns to the AutoCat ruleset sheet by inserting columns (right click a column to insert).

Overriding custom columns

Custom columns you add to the Transactions sheet can also be used for override rules in the AutoCat sheet.

For example, if you added a column called “Tags” to your transaction sheet you can build an override rule to automatically tag transactions by adding that column and the criteria to the AutoCat sheet.

Troubleshooting

AutoCat failed to run message

AutoCat will not successfully run if you have formulas in cells below row 1 (the header row) in the Transactions sheet. You will see an “AutoCat failed to run” error at the top.

There may be other cases where AutoCat fails to run and you see this message. If you do not have any formulas in any of the cells below row 1 in your Transactions sheet please reach out to our support team using the chat tool in the lower right corner of the Console at https://sheets.tillerhq.com/auth/login

Did this answer your question?