Categorising a year of spending from your bank statements

You want one number per category for last year. What you have is twelve statement periods, and somewhere around eighteen hundred rows once they are all in the same place. Categorising eighteen hundred rows one at a time is a weekend, and by row four hundred you will be assigning categories inconsistently to the same merchant without noticing.

The work is not eighteen hundred decisions. It is roughly a hundred decisions about merchants, applied to eighteen hundred rows by a lookup, plus one structural decision at the start that determines whether the totals mean anything at all.

Sequence matters more than technique

Convert, verify, strip transfers, categorise, then pivot. Doing those out of order costs the most time of anything in this exercise.

Categorising before verifying means recategorising after you find the conversion dropped rows at a page boundary. Pivoting before stripping transfers produces a picture that looks finished and is wrong by thousands. Neither error announces itself: a pivot table with transfers in it still balances, still totals, and still looks like an answer.

Start by getting every period into one sheet with five columns: date, description, amount, balance, and a column naming the source statement. That last column is what lets you trace any suspicious figure back to a specific page later, and it is easier to add during the conversion than to reconstruct afterwards. If you hold more than one account, every account belongs in the same workbook with an account column rather than in parallel files.

Then verify before touching anything else. Each period's opening balance plus its movements must equal its printed closing balance, and each period's closing balance must equal the next period's opening balance. A break in that chain means rows are missing or duplicated, and every category total below will be wrong by an amount you cannot see. Checking a converted year end to end takes minutes and it is the only point in this process where an error is cheap to fix.

Transfers are not spending

This is the structural decision, and it is where most personal spending analyses go wrong.

Take a year with total debits of 48,210.60. Inside that figure are three things that are not spending: 6,000.00 moved to savings, 9,340.25 in credit card payments, and 4,800.00 transferred to a joint account. Those come to 20,140.25, which leaves 28,070.35 of actual spending out of the current account.

The card payments are the subtle one. If you also convert the card statements and categorise those transactions, and you leave the card payments in as an expense, you have counted the same spending twice: once when the card paid the merchant and once when you paid the card. Suppose the card statements show 9,120.40 of spending across the same twelve months. The correct total is 28,070.35 plus 9,120.40, which is 37,190.75. The wrong total, with the card payments left in, is 46,531.00, and it is wrong by more than a fifth.

Categorise merchants, not transactions

Reduce each description to a short merchant key first. The raw description on a card transaction carries a processor prefix, a reference number, sometimes a location field and sometimes the transaction date, all of which change between charges from the same merchant. Uppercase the description, strip the processor prefixes, cut the string at the first run of four or more digits, and trim what is left to a fixed width chosen so the merchant name survives and the branch or terminal identifier that follows it does not. That is the same normalisation used to find recurring charges across a year, and building it once serves both jobs.

Now pivot on the merchant key with a count of rows and a sum of amount, and sort by total spend descending rather than by row count. Ranking by count puts the coffee shop at the top; ranking by total puts the things that actually moved your money at the top, so work down the total column and stop when the amounts stop mattering.

In the year above, thirty-eight merchant keys accounted for 1,180 of the 1,842 rows and 21,480.00 of the 28,070.35 in spending. Assigning a category to those thirty-eight is one short sitting, and it categorises roughly two thirds of the rows and three quarters of the money. The remaining 662 rows carry 6,590.35 between them.

The lookup is the asset

Put the assignments in their own sheet, two columns: merchant key and category. Reference it from the transactions sheet with XLOOKUP or VLOOKUP. Do not type categories directly into the transactions sheet.

The reason is next month. When you paste in a new statement period, the lookup categorises every merchant it already knows and leaves only genuinely new ones blank. You can delete the transactions sheet entirely and re-export it from the statements without losing a single assignment. Get this the wrong way round and you repeat the entire exercise every time.

Working the long tail

The 662 remaining rows are where people either give up or waste a day. Neither is necessary. Sort the unassigned rows by absolute amount, largest first, and work down until the remaining uncategorised total is small enough that it cannot move any category's answer.

Set that threshold explicitly before you start. If you are looking at categories in the thousands, an uncategorised residue of a few hundred changes nothing, and stopping there is a decision rather than an abandonment. In the year above, working down the tail until 412.80 remains unassigned leaves under one and a half per cent of spending in the Uncategorised bucket, and the bucket stays visible in the pivot so you always know how much precision you traded away.

Two cases in the tail deserve a rule rather than a judgment call each time.

• Merchants selling across categories. A supermarket that also sells fuel, or a general retailer covering groceries and household goods. Assign the whole key to its dominant category. If the secondary category is one you actually care about, split by hand only the rows above a threshold, rather than trying to split every row from that merchant.
• Refunds and reversals. A refund is a credit, and if you build the pivot on debits only it never nets off, so the category is overstated by the full amount of a returned purchase. Assign refunds the same category as the original purchase and let them net within it. Only genuine income belongs on the credit side.

Build the pivot with months as columns

Rows are categories, values are the sum of amount, and columns are the month. The month breakdown does more than show seasonality.

A category with a figure in one month and nothing in the other eleven is almost always one of two things: an annual renewal, or a miscategorised transfer. Both are worth opening. A category that jumps in a single month by several times its usual level is either a real event you remember or a decimal that landed in the wrong place, and the source statement column tells you which page to check.

Reading across the months is also the fastest way to catch a lookup that broke partway through the year. If a merchant changed its descriptor in June, the key changed with it, and the category total drops to zero from June onward while an unfamiliar key appears in the tail with six months of charges.

The checks that have to pass

Run the checks in this order. The category totals plus the Uncategorised bucket must sum to exactly 28,070.35. Total debits minus the three transfer lines must produce the same 28,070.35. And the credits side must reconcile too: total credits minus transfers in should equal the income you know you received, and if it does not, something you classified as income is a transfer from your own savings coming back.

Watch for category names with a trailing space. A pivot treats "Groceries" and "Groceries " as two categories, splits the total between them, and still sums to the right grand total, so the reconciliation passes while the individual figure is wrong. Read the pivot's category list before you read its numbers.

What the totals do and do not prove

Two different checks sit behind the figures in that table, and they are not equally strong. The weaker is a totals check: a period's opening balance plus its movements equals the printed closing balance, which is the same shape as the reconciliation from 48,210.60 down to 28,070.35. It tests a set of rows against one published figure, so any pair of errors that cancels passes it. Lose a 96.40 row at a page boundary and repeat another 96.40 further down, and the year still reconciles to 28,070.35 while one category is understated by 96.40 and another overstated by exactly as much.

The stronger is the row by row check you ran at the start: each row's printed balance plus the next row's amount equals the next row's printed balance. A 184.20 read as 84.20 breaks that equation on the row after it and leaves a rebuilt balance 100.00 adrift until something cancels it, so a wrong digit cannot pass silently into a category total. It catches the dropped-and-duplicated pair too, because the loss and the repeat each break a row of their own and the rebuild is wrong for every row between them. On a statement that prints no running balance the row by row check is unavailable entirely, and the totals check is all there is.

Neither check reads a date, a description or a category, which is most of what a categorisation depends on. A transaction dated 3 January that actually fell on 30 December reconciles perfectly and lands in the wrong year, which matters if the point of the exercise is a calendar year comparison. A description mangled badly enough to produce a merchant key that matches nothing will sit in the tail as a stranger, uncategorised and correct in every arithmetic sense. A row for 0.00 moves no balance and is tested by nothing at all, and a balance column misread in a way that agrees with the misread amount beside it reconciles against itself.

So run the arithmetic checks to establish that the year is complete and the magnitudes are right, then read the largest rows against their source statements, working down until the rows still unread are too small to move any category's figure. The arithmetic covers the amounts, your reading covers the dates and descriptions it cannot see, and between them you have looked at the rows that carry the answer rather than at all eighteen hundred.