How do you analyze bank statements?

The process below is the same whether you are doing it in a spreadsheet or with software. The order matters more than the tooling: each step assumes the previous one is correct, and doing them out of sequence is how people end up confidently wrong.

1. Gather every statement

Every account, for the whole period, with no gaps. This includes the savings account you never touch and the credit card you pay off in full, because both carry transactions that change the picture. Download from your bank in whatever format it offers, preferring CSV or Excel over PDF where you have the choice, because tabular exports skip an entire class of extraction problem.

2. Extract into a table

You want four columns at minimum: date, description, signed amount and account. Descriptions arrive as payment-processor strings, often truncated and full of reference numbers. Keep the raw string. Do not tidy it as you go, because the raw text is what your categorization rules will match against, and rewriting it breaks reproducibility.

3. Reconcile before you do anything else

Opening balance, plus every credit, minus every debit, equals closing balance. This has to hold for every statement individually. When it does, you know no row was dropped, duplicated or misread. When it does not, the difference itself is a clue: a difference equal to one transaction means a row was missed, a difference that is exactly double a transaction means one was counted twice, and a difference that is small and odd usually means a digit was misread by OCR.

4. Normalize

Standardize dates into one format. Settle the sign convention so every outflow is negative and every inflow positive, regardless of how each bank presented it. Then identify internal transfers: a debit in one account matched by a credit of the same amount in another within a couple of days. Flag both sides. They are not income and not expenditure, and leaving them in is the fastest way to produce a nonsense analysis.

5. Categorize with rules, not by hand

A rule maps a pattern in the description to a category: any description containing a specific supermarket name becomes Groceries, anything containing a payroll reference becomes Salary. The value of a rule is that it applies retroactively and prospectively, so correcting one transaction fixes every past and future occurrence at once. Tagging rows individually feels faster for the first fifty transactions and is far slower by the five hundredth.

6. Work only the exceptions

After the rules run, you are left with a residue: new merchants, one-off payments, ambiguous descriptions. This is the only part that genuinely needs your attention. Categorize each one, and let each decision become a new rule so the residue shrinks every month.

7. Interpret

Now read the numbers. Monthly income and how much it varies. Fixed commitments as a share of income. Discretionary spending by category. Recurring charges, including the ones you forgot. Months where net cash flow was negative, and what drove them. The point of the first six steps is that you can trust these answers.

A reconciliation failure that mattered

A scanned statement extracted cleanly except for one line where the OCR read 1,380.00 as 1,880.00. The transaction list looked entirely plausible: right merchant, right date, believable amount. The only thing that caught it was the closing balance being out by exactly 500.00. Without the balance check the year's expense total would have been wrong by that amount and nothing on screen would have hinted at it.

A transfer that looked like income

A sole trader moved 4,000 from a savings buffer into the business account each January to cover the quarterly tax bill. Analyzed naively, January showed a 4,000 income spike and the savings account showed a 4,000 expense. The apparent effect was a profitable January and an unexplained cost elsewhere. Flagging both sides as a transfer removed the distortion and the real pattern, a predictable January cash squeeze, became visible.

What twelve months of grouping revealed

Grouping a year of transactions by merchant surfaced three active streaming subscriptions where the household believed it had one, two overlapping cloud storage plans, and an insurance add-on that had been renewing since a policy that had already been cancelled. None of these were individually large. Together they were a meaningful annual figure, and none of them were visible in any single month.

• Trustworthy totals. Reconciliation means the numbers are provably complete rather than probably complete.
• Reproducible categories. Rules give the same answer every time and can be explained to whoever asks.
• Effort that shrinks. Each exception you resolve becomes a rule, so month two is faster than month one.
• Audit-ready output. Every category total traces back to specific rows, which is what an accountant or a lender actually wants to see.
• Works across accounts. Once transfers are flagged, several accounts can be analyzed as one financial picture.

• Starting with categorization. Categorizing before reconciling means carefully sorting data that may be incomplete.
• Editing descriptions while extracting. Tidying the raw text as you go breaks rule matching and makes the work unrepeatable.
• Leaving transfers in. The single biggest source of inflated income and expense figures.
• Using different category names over time. Fuel, Petrol and Gas as three separate categories makes a year-on-year comparison meaningless. Settle the list first.
• Excluding the credit card. If most discretionary spending happens on a card and the current account only shows the monthly payment, the analysis sees one large opaque number instead of the actual spending.
• Treating refunds as income. A refund reduces spending in its original category. Booked as income it distorts both figures.
• Stopping at the summary. The category chart is the headline. The value is in the outliers underneath it.