Categorise your personal finances quickly with an iterative macro in Alteryx

The list of transactions that I download from my bank contains a description field that I can use to categorise my spending and help my budgeting. When I first began this categorising task using only excel I became frustrated with having to categorise new items each month that were only subtly different from items I had already categorised. With Alteryx I built an iterative macro that made categorising similar items easy.Here are some subtly different items that have appeared in my statement:

• CO-OP GROUP 310334 ST LEONARDS O GB
• CO-OP GROUP 180281 ST PETERS GB
• LONDON & SOUTH EAS TONBRIDGE GB
• LONDON & SOUTH EAS BROADSTAIRS GB
• STARBUCKS LONDON GB
• STARBUCKS WATERLOO EAST GB

The problem, as you can see, is that a lot of retailers include the store location in the description. When I uploaded my last 12 months of transactions I found I had visited one supermarket chain in 12 different locations. There is no clear rule regarding how many words I could keep or exclude, so instead I decided to search for substrings within the strings.What I wanted to do was input a table of strings to search with categories eg.

SearchString	Category
CO-OP	Supermarket
LONDON & SOUTH EAS	Train
STARBUCKS	Eating out

I wanted this table to be joined onto the bank statement without multiplying the rows. Eg. If I put in a search string Water - Utilities I do not want STARBUCKS WATERLOO to be categorised twice. When I add new data each month I want to be quickly see which descriptions are not being categorised and be able to quickly add in new search strings and categories.  With this is mind, here is the workflow I created:The black and yellow icon is the search string iterative macro that I created just for this workflow:The first challenge was to create a feedback loop that contained the descriptions and the strings. An iterative macro can only have one iteration input and one iteration output. So the descriptions and search strings had to be appended together to enter the macro then immediately split using the summarise tools. The unmatched descriptions and the unused search strings are then appended together to feed back into the next iteration.The second challenge was getting the macro to quit when there no search strings left. When I run the workflow each month I expect there to be a few transaction descriptions that do not match any of the string searches. But to speed the macro up I did not want to have to wait for an arbitrary number of iterations to be reached (eg. 1000 when there are only 60 search strings). The solution I found was to add a filter length([SearchString])>0  which is labelled above as 'True when unmatched string remains'.To have a closer look, feel free to download the macro from the Alteryx Gallery here.Dan Watt