Export Bank Transactions from Xero to CSV

To export bank transactions from Xero, run a report and export it to Excel, then save it as CSV. The Bank Statement report gives the lines your bank sent. The Account Transactions report gives the coded transactions in each account. Run both for every bank account and year you plan to keep.

Statement lines or account transactions: which Xero bank export?

Xero holds two lists for each bank account, and the export you need depends on which one you want. Statement lines are what the bank supplied, through a feed or an imported file. Account transactions are what you recorded in Xero against them: spend money, receive money, payments and transfers, each with an account code.

If you are moving to new software and want to upload the bank history there, the statement lines are closer to what a bank file looks like. If you want a record of your bookkeeping, the account transactions carry the codes. When in doubt, export both. They are small files, and you can't run either one once the subscription has ended.

Exporting Xero statement lines through the Bank Statement report

Xero's statement lines come out through the Bank Statement report, which is part of the Bank Reconciliation report pack. Xero's guide to the Bank Reconciliation reports says the Bank Statement report lists a bank account's statement lines for the date range you pick. It shows each line's date, import date, reconciled status and source.

Steps, as Xero describes them:

1. In the Reporting menu, select All reports.
2. Open the Bank Reconciliation report pack.
3. Under Contents, choose the Bank Statement report.
4. Set the pack date range, for example one financial year.
5. Under Bank Account, choose the account.
6. Pick the columns, and choose All statement lines rather than only reconciled ones.
7. Click Update, then Export.

Xero's page on exporting a report lists PDF, Microsoft Excel and Google Sheets as formats. Choose Excel for a file you will reshape. A PDF is fine as a record, but a spreadsheet is the version you can trim and reshape for an import.

Repeat for each bank account. A credit card account in Xero is a bank account for this purpose, so export it the same way.

Exporting Xero account transactions to Excel

Xero's Account Transactions report lists the transactions recorded in the ledger, account by account. Xero's overview of exporting data recommends it as the report for a list of all transactions. If you use more than one currency, it suggests selecting all columns, including the foreign exchange ones.

The steps follow the same pattern as other reports:

1. In the Reporting menu, select All reports.
2. Find and open the Account Transactions report.
3. Set the report options to your bank accounts and the date range you want.
4. Add any extra columns you will need later, such as references or contacts.
5. Click Export and choose Excel.

The same Xero overview lists other files worth saving before you leave. These include the chart of accounts, contacts, invoices and bills, a trial balance and the bank reconciliation report. The last one is your proof of which months were already reconciled. The question of how long to keep exports like these, and in what form, is covered in keeping old accounting data after switching.

How do you turn a Xero report export into a clean CSV?

A Xero report exported to Excel is laid out for reading, not for importing. Turn it into a clean CSV by deleting everything that isn't a transaction row. Then keep one header row and save the sheet as CSV. Most programs then accept it after column mapping.

What to remove:

• The report title, organisation name and date range lines at the top.
• Opening balance and closing balance rows.
• Subtotal and total rows.
• Blank rows between sections.

What to keep, at minimum: date, description or payee, reference, and amount. If the report shows money in and money out as two columns, either keep both or combine them into one signed amount. Use whichever the receiving software asks for. Dates should use one format all the way down.

Check the result before you upload it. The sum of the amounts should equal the change in the account's balance over the period. If it doesn't, a row was cut by mistake.

If the receiving program is QuickBooks, its bank upload takes a 3-column or 4-column CSV. People moving the other way, from QuickBooks Desktop into Xero, face the mirror image of this task. Switching from QuickBooks Desktop to Xero describes it. When some months exist only as PDF bank statements, SimpleBankConvert is being built to turn those into CSV files for Xero or QuickBooks. Accounts are not open yet, and you can join the early access list from the sign-up page.

Deleted and duplicate lines a Xero bank export can hide

A Xero bank export shows the lines that are still there, so deleted statement lines and flagged duplicates can drop out of view. Xero's Bank Reconciliation pack includes a Statement Exceptions report for exactly this. It lists deleted statement lines and lines Xero flagged as possible duplicates, plus lines created by marking a transaction as reconciled.

Run the Statement Exceptions report for the same accounts and dates as your export. Then compare three figures for each account:

1. The total of the exported statement lines.
2. The bank's own statement movement for the period.
3. The total of any deleted or duplicate lines in the exceptions report.

If the first two match, the export is complete. If they differ by the amount of a deleted line, the line was removed in Xero but did happen at the bank. Add it to your file by hand before you import it elsewhere.

Lines coded to the wrong account are a different problem. They export with their wrong code and carry the mistake into the new system. Fix them in Xero first, or note them for correction after the move. The QuickBooks side of that fix is in moving transactions imported to the wrong account in QuickBooks.