Bank statements

Monzo statement to CSV: turn a statement into a spreadsheet you can trust

Need a Monzo statement as CSV? Find the bank's export, check columns and prove the file with running totals before you analyse it. Worked £ example.

Editorial illustration for Monzo statement to CSV: turn a statement into a spreadsheet you can trust

To get a Monzo statement into CSV, first look in your bank's own screens for an export option that gives a spreadsheet-style file; this is cleaner than converting a PDF. Whichever route you use, prove the file before you analyse it: add up money in and money out and check they reproduce the statement's closing balance.

Which route gives the cleanest file?

RouteProsWatch out for
Bank's own CSV or spreadsheet exportColumns are already separatedCheck the date range you chose
PDF copied into a spreadsheetWorks when no export existsRows may merge or split
PDF converted by a toolQuickRead every row; do not upload statements to unknown sites

Check your own bank's help for what it offers; menus and options change.

How do I prove the file is complete?

  1. Note the opening and closing balance from the statement.
  2. Sum the credits and the debits in your CSV.
  3. Compute opening + credits - debits.
  4. Compare with the closing balance.
  5. If it differs, find the gap before doing anything else.

Worked example in pounds

DateDescriptionAmount (£)
02 NovSalary2,400.00
03 NovRent-950.00
05 NovSupermarket-62.35
09 NovTrain ticket-31.20
12 NovPhone bill-18.00

Say the opening balance was £320.00. Credits are £2,400.00. Debits are 950.00 + 62.35 + 31.20 + 18.00 = £1,061.55. Closing should be 320.00 + 2,400.00 - 1,061.55 = £1,658.45. In a sheet, =SUM(C2:C6) should return 1,338.45, and 320.00 + 1,338.45 = 1,658.45, which agrees. If your sheet shows 0, the cells are text; convert them with =VALUE().

What should I fix before analysing?

  • Make sure dates are real dates, not text, by sorting them and checking the order.
  • Remove any summary rows at the top or bottom.
  • Keep debits negative and credits positive in one column.
  • Trim extra spaces in descriptions with =TRIM(B2).
  • Add a column for category and fill it in by hand or with a lookup.

Totalling by category

Once a Category column is in D, =SUMIFS($C$2:$C$500,$D$2:$D$500,"Rent") gives the total for rent. Check that the category totals plus income equal the sum of the amount column.

A note on SpendixAI

SpendixAI is built around India and rupee statements, so it is not meant for a UK account. For rupee accounts, it takes PDF, CSV or Excel statements and shows rows for review before saving; the proof method here still applies.

Descriptions are where most cleaning time goes. Merchant names often arrive with extra words, reference numbers or location codes. A helper column with =LEFT(B2,FIND(" ",B2&" ")-1) pulls out the first word, which is often enough to group by shop. For anything more subtle, sort by description and fill the category by hand for the repeated names first; fifteen or twenty merchants usually cover most of your rows.

Finally, freeze the header row so it stays visible while you scroll, and add a total row separated by a blank line from the data, not directly under it. A total row directly under the data is the usual reason a later SUM double counts, because new rows get added below it and the formula range shifts.

One more check is worth the minute it takes: count the rows. If the statement lists 38 transactions and your sheet has 36 data rows, two are missing, and no total will be right until you find them. Use =COUNTA(B2:B500) to count the descriptions, then compare with the number you see on the statement, page by page. Matching both the count and the balance gives you real confidence in the file.

Closing checklist

  • Export route chosen, original saved.
  • Balance proof done.
  • Dates and signs cleaned.
  • Categories added and totals cross-checked.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports