UK bank statements into Excel, Xero or Sage
UK statements are made to be read, not imported: day-first dates that often skip the year, separate columns for money in and money out, and payment codes such as DD, SO and FPI inside the description. Many banks keep six or seven years of PDFs online, but Lloyds, Nationwide and Santander export only a 12-month midata file. Pick your bank, or upload a statement.
- Currency
- Pounds sterling (GBP)
- Date order
- Day first, often '15 Mar'
- Money columns
- Money out, money in, balance
- Common imports
- Xero, QuickBooks, Sage, FreeAgent
Private workspace. HTTPS upload. No credit card needed.
Banks
UK banks and what their statements do
High street banks and building societies 10
-
Barclays
An 'At a glance' summary sits beside the transactions, and DR marks an overdrawn balance.
-
HSBC UK
Six years of statement PDFs; codes such as DD, SO and TRF can sit on a line of their own.
-
Lloyds Bank
Seven years online and at least ten in the app, while Midata covers 12 months.
-
Halifax
The app reaches back at least ten years, further than Online Banking's seven.
-
NatWest
Transactions export to Excel, CSV or OFX; statements are PDFs, chosen a year at a time.
-
Royal Bank of Scotland
Card payments abroad can add N-S TRN FEE lines, which convert as rows of their own.
-
Santander UK
Dates appear as DD/MM/YY or DD/MM/YYYY, and statements sit in the app's Mailbox.
-
Nationwide
36 months of PDF statements in the Internet Bank, plus a 12-month midata file.
-
TSB
Statements land in the Digital Inbox, with codes such as B/O, DD and FPI in the narrative.
-
Metro Bank
Every statement since the account opened can be downloaded, so long catch-ups are possible.
App banks and multi-currency accounts 4
-
Monzo
A single signed amount column, and Pots have statements of their own.
-
Starling Bank
App statements carry a 'True Copy' stamp; custom date ranges come as transaction summaries instead.
-
Revolut
One statement per currency, with each exchange shown as two linked rows.
-
Wise
A statement covers up to 365 days, and amounts can carry a currency code, as in -12.50 GBP.
Accounts held abroad 1
-
Standard Chartered
Usually statements from overseas accounts; eStatements in India, the UAE and Singapore arrive password protected.
Statement layout
Reading a UK bank statement
Day first, and often no year
Barclays, Halifax and RBS print dates like '15 Mar' and leave the year to the statement period, while Santander and TSB use DD/MM/YY. The year is filled in from the period, so a December to January statement keeps January's direct debits in the right year.
Money out, money in and a running balance
Most high street statements give payments and receipts their own columns and print a balance after each line, with DR beside a balance that is overdrawn. Monzo uses one signed column instead. Either way, each row comes out with a single signed amount.
Codes worth keeping
DD, SO, BGC, FPI, TFR and POS say what kind of payment a line was. They stay in the description, so you can filter direct debits or card spending in Excel, or base bank rules on them.
Output and review
What you download, and what's checked first
Each statement comes back as dated rows with a description, a signed amount and the balance: Excel, CSV, JSON, or a CSV laid out for Xero or QuickBooks. The rows are added from the opening balance and compared with the closing one. Signed in, upload a year of statements for one account and missing, duplicate or overlapping months are flagged before anything reaches your books.
Xero, QuickBooks, Sage and FreeAgent
-
Xero
Import the Xero CSV into the matching bank account through the statement import, and only for months the bank feed hasn't already brought in.
-
QuickBooks Online
The QuickBooks CSV uses the three-column Date, Description and Amount layout, so there is one amount to map.
-
Sage Accounting
Sage's CSV import reads Date, Description and Amount, the same three columns, so start from the QuickBooks CSV.
-
FreeAgent
FreeAgent accepts CSV, OFX and QIF statements. Its CSV wants three columns, date, amount and description, with no header row, so reorder the plain CSV first.
FAQ