How to Analyze Bank Statement CSVs: Spending and Subscriptions

Most banking apps show a pie chart of this month's spending and not much else. The CSV download is where the real answers are: how much you actually spend in a normal month, which subscriptions you forgot about, whether eating out has doubled since last year. The file itself is simple — a date, a description, an amount — but every bank formats it a little differently, and a few conventions will quietly give you the wrong total if you don't catch them first. This guide covers getting the file, the traps, cleaning up merchant names, categorizing, and the questions worth asking.

Getting the CSV

Nearly every bank and card issuer offers a transaction download on the website (sometimes not in the app). Look for Download, Export or Statements & documents near your transaction list, pick a date range, and choose CSV — some banks also offer Excel, OFX or QFX, which are formats for accounting software rather than spreadsheets. If the download is limited to a few months at a time, grab several ranges and stack them into one sheet.

Download every account you want to analyze: checking, savings and each credit card. Spending is split across them, and a single account rarely tells the whole story.

What's in the file

Columns vary by institution, but you'll almost always see some version of:

Trap 1: sign conventions

This is the one to check first, because it flips every total.

• One signed Amount column. Many checking accounts export money out as negative and money in as positive. Many credit card issuers do the opposite — purchases are positive (they increase what you owe) and payments or refunds are negative. There is no standard; open the file and look at a purchase you recognize.
• Separate Debit and Credit columns. Money out is in one column, money in is in the other, and the unused column is blank. To get a single amount, compute =Credit − Debit (treating blanks as zero) so spending is negative, or the reverse if you prefer spending as positive.

When you combine a checking account and a credit card, normalize the sign first so that spending has the same sign in both. Otherwise card purchases cancel out checking purchases and your monthly spend looks tiny.

Trap 2: transfers and card payments count twice

When you pay your credit card from checking, the checking file shows a payment out and the card file shows a payment in. Neither is spending — the spending already happened as individual card purchases. The same goes for moving money to savings or between your own accounts.

If you leave these in, a month where you paid off a large card balance looks like a month of heavy spending. Tag them as Transfer (search descriptions for words like "payment", "transfer" or your card's name) and exclude that category from every spending total.

Trap 3: pending and duplicate rows

If you downloaded overlapping date ranges, the overlap appears twice. Some banks also include pending transactions that later post with a slightly different amount (restaurant tips, hotel holds). Before summing, check for exact duplicates on date + description + amount — the approach in finding duplicates in Excel works here.

Cleaning up merchant names

Raw descriptions look like SQ *BLUE BOTTLE COFFEE 0423 OAKLAND CA or AMZN Mktp US*2K4L81. The same merchant shows up under a dozen variants: different store numbers, city names, processor prefixes, reference codes. Grouping by the raw description gives a hundred rows of one coffee shop.

The practical approach:

1. Add a Merchant column.
2. Strip the noise you can recognize: payment-processor prefixes, trailing reference numbers, city and state.
3. For the rest, map by keyword — if the description contains "BLUE BOTTLE", the merchant is Blue Bottle Coffee. A small two-column lookup table (keyword → merchant) plus a formula, or a handful of find-and-replace passes, covers most of a year's statement.

You don't need every row perfect. Clean the merchants that appear most often and the long tail stops mattering.

Categorizing

Bank-supplied categories are a start but often too broad or wrong. A dozen categories of your own is usually enough: Housing, Groceries, Eating out, Transport, Utilities, Subscriptions, Shopping, Health, Travel, Income, Transfer, Other. Map each cleaned merchant to one category with the same keyword-lookup approach, and review the Other bucket until it's small.

The guide to cleaning messy Excel data covers the text functions (TRIM, SEARCH, SUBSTITUTE) that make the mapping formulas short.

Finding recurring subscriptions

A subscription is a merchant that charges a similar amount at a regular interval. To find them:

• Group by cleaned merchant and count the months in which it appears.
• Keep merchants that appear in most months (or once a year for annual plans) with an amount that barely changes.
• Sort by amount, then multiply annual plans down to a monthly figure so you can compare them.

This is where a lot of forgotten charges turn up: free trials that converted, a second streaming service, an app you stopped opening. The price change check is useful too — the same merchant charging more this year than last.

The questions worth asking

1. Total spending by month, excluding transfers and income. The baseline number.
2. Spending by category by month — a stacked or line chart makes drift obvious.
3. Top merchants by total spend over the year.
4. Recurring subscriptions and their monthly cost.
5. Average spend per week on eating out or groceries, this year vs last.
6. Largest single transactions — for catching one-offs that distort a month.
7. Income vs spending by month — your actual savings rate.
8. Spending by day of week — occasionally surprising.

Asking in plain English

Everything above is filter-and-group work, which is exactly what plain-English questions are good at. Upload your statement CSVs to ChatExcel — each file or sheet is read — and start with a question that sets the ground rules:

• "Purchases are positive and payments are negative in this card file; treat anything with 'PAYMENT' in the description as a transfer."
• "What did I spend each month this year, excluding transfers? Chart it."
• "List merchants that charged me in at least 10 of the last 12 months, with the typical amount."

Answers are computed from every row, and you can ask "what's the Excel formula for that?" to rebuild any number in your own budget workbook. For more on working with raw CSVs, see analyzing a CSV with AI.

A sanity check

Pick one month and compare your computed spending with the statement for that month. They won't match exactly — statements include payments and fees — but spending plus payments and transfers should reconcile to the change in balance. If it's far off, a sign is flipped somewhere.