To get bank transactions from Xero into Excel, run the Account Transactions report in its Bank transactions by date layout and export it. That covers what's already in your books. For lines your bank sent that nobody has reconciled yet, there's a separate CSV export.
Quick answer
| You want | Use | What you get |
|---|---|---|
| Every transaction in a bank account for a period | Account Transactions report, Bank transactions by date format > Export | Excel, PDF or Google Sheets, grouped by bank account |
| Statement lines you haven't reconciled yet | Bank accounts > menu icon > Uncoded statement lines > Export | CSV or PDF, with a column for your comments |
| Totals in and out per account | Bank Summary report > Export | One line per bank account: received, spent, balances |
| The same file every week or month | No schedule option on the report screens | A scheduled email, below |
Export bank transactions with the Account Transactions report
Xero has a ready-made layout of the Account Transactions report for this, called Bank transactions by date: everything coming in and going out, grouped by bank account, with the account each transaction was coded to.
- Go to Reporting, then All reports, and open Account Transactions.
- In the panel on the left, choose Bank transactions by date.
- Set the Date range. To export one bank account, select only that account under Accounts.
- Click Update, then Export, and choose Excel.
You need the administrator, standard + reports or viewer role to run and export reports. If you use this export often, mark the layout as a favourite, or change the report to suit and click Save as, then Custom, so next month is one click.
Columns worth adding
- Related account. The Bank transactions by date layout already shows where each transaction was coded. Add this column if you started from the standard report. It's the other side of the entry (some people call it the split column). It's the column that turns a bank list into something you can pivot by expense type.
- Running Balance. A cumulative balance per bank account. It only works if the Debit and Credit columns are in the report too. If you sort by another column, click Update again to recalculate it.
- Reference and Contact. Reference comes from the payment or the source transaction. When the reference was left blank you may see something else, such as the invoice number. Contact lets you sort and filter by customer or supplier (manual journals leave it empty). For bank accounts, Description already shows the contact's name.
- Revalued column (multicurrency). The report already shows foreign and base currency amounts, converted at the rate used when each transaction was entered. Add the Revalued column if the closing balance needs to match the Balance Sheet.
Things to know about this file
- It's what's in your books, not your bank feed. Statement lines you haven't reconciled or coded yet aren't transactions in Xero, so they don't appear. Reconcile first, or export them separately with the list below.
- One payment can take several rows. On a cash basis, or when an invoice or bill has several lines, Xero splits the payment across separate rows, so one bank payment can take more than one row.
- Some amounts may show as 0.00. When a report with formulas opens in Excel, click Enable Editing and the amounts fill in.
- One organisation at a time. Run the report in each organisation you need.
Export unreconciled statement lines
For the other half, the lines your bank sent that nobody has dealt with yet, Xero has a separate list. It's the one to send a client or colleague with "what are these?" questions, and it needs the administrator role.
- Go to Accounting, then Bank accounts.
- Click the menu icon at the top right and choose Uncoded statement lines.
- Under Bank Account, pick all bank accounts, one account, or Bulk download across all orgs if you have access to several organisations, then tick the organisations and click Done.
- Enter the From and To dates, choose the order, and click Run.
- Click Export and choose CSV or PDF.
The export has a Your Comments column, so whoever knows what a payment was for can write it in and send the file back. With bulk download, the screen only shows lines for the organisation you're in, but the exported file has all of the ones you selected.
To look through every statement line, reconciled or not, open the bank account's Manage Account menu and choose Bank Statements. The Status column says whether each line is Unreconciled, Reconciled or Deleted, and Source says whether it came from the bank feed or a file import, or was created when someone marked a transaction as reconciled by hand. Viewing statements needs the administrator or standard + bank accounts role.
Which one to use
- Your accountant or auditor wants the bank transactions for the year: Account Transactions, Bank transactions by date, with Running Balance added.
- Analysing spending by category: the same report with Related account, then a pivot table on that column.
- Clearing a reconciliation backlog with a client: Uncoded statement lines to CSV, with the comments column.
- The owner wants a quick view of cash in and out: the Bank Summary report, one line per account.
- Loading the books into accounts production or tax software: Accounting settings, then Export accounting data. It exports the general ledger in the import format of the products Xero lists there, such as CaseWare, IRIS or MYOB Accountants Office (administrator role).
Getting bank figures every week or month
Each of these exports is a manual click, and Xero's standard report screens have no schedule option. Reportpost can send the Bank Summary for you: connect Xero (read-only), choose the Bank Summary, pick a period such as last week or last month and a day and time, and add up to 20 recipients. Each send pulls fresh figures and attaches a PDF, Excel or CSV with cash received and spent for each bank and credit card account.
It sends account totals, not the transaction list, so for line-by-line work you still want the Account Transactions export above. The same schedule can add the Profit and Loss, Aged Receivables or an unpaid bills list. You can try the demo with sample data, no signup needed. Plans start at US$12 a month for one organisation, excluding VAT, with a 14-day free trial.
Common questions
Can I export a bank statement from Xero?
Not as a statement. Xero shows the imported statements and their lines under Manage Account, then Bank Statements, but the export routes are the two above: Account Transactions for what's been reconciled or entered, and Uncoded statement lines for what hasn't. For a copy of the statement itself, download it from your online banking.
Why are some transactions missing from my export?
Usually because they're still unreconciled statement lines, which aren't in the books yet. Check Uncoded statement lines for the same dates. Also check the date range, that the right bank accounts are selected under Accounts, and that Show archived is ticked if an old account was archived.
How do I get a running balance in the export?
Add the Running Balance column, and keep the Debit and Credit columns in the report, or the running balance won't calculate. Click Update before exporting if you've re-sorted the report.
Can I export bank transactions for several organisations at once?
For unreconciled lines, yes: choose Bulk download across all orgs in Uncoded statement lines and export. The Account Transactions report runs in one organisation at a time.
Can I export bank transactions to CSV instead of Excel?
The Account Transactions report exports to Excel, PDF or Google Sheets. Save the Excel file as CSV if another tool needs it. Uncoded statement lines export straight to CSV.
Related: exporting Xero reports to Excel, exporting invoices to Excel, exporting expenses, where every other Xero export lives and reconciling the balance sheet.