PDFCellsConvert a PDF bank statement to OFX or a Xero CSV
Open the statement in PDFCells and export OFX or the Xero CSV once its balances tie. In Xero, go to the bank account, choose Manage account › Import a statement and pick the file; OFX needs no column mapping.
Steps
- Go to pdfcells.com and choose the PDF, or drop it on the page. Drop several files to open them as a batch. A password-protected PDF asks for its password.
- Each table opens in an editable grid next to the page. Totals that don't add up, balances that don't tie, text in a number column and cells OCR was unsure of are flagged. Fix any cell, merge or split columns, or undo.
- Choose Bank statement (OFX, QBO, Xero CSV), then OFX or Xero CSV. For the Xero CSV, set the date format to match your Xero organisation (DD/MM/YYYY by default, MM/DD/YYYY for a US organisation). Pick the currency for OFX: USD, EUR, GBP, CAD or AUD.
- In Xero: Accounting › Bank accounts, pick the account, then Manage account › Import a statement. Confirm the column mapping once for the CSV and save it.
OFX or Xero CSV?
| OFX (.ofx) | Xero CSV | |
|---|---|---|
| Imports into | Xero, Wave, Quicken, GnuCash and other software that reads OFX 1.x | Xero |
| Column mapping | None | Once, on Xero’s mapping screen |
| Payee | First 32 characters of the cleaned payee; description in the memo, up to 255 characters | Cleaned payee, so Xero bank rules match every visit to the same merchant |
| Duplicates | Stable FITID per transaction | Handled by Xero’s own duplicate check |
| Text | US-ASCII: accents dropped, € written as EUR | UTF-8 |
Credit card statements
A card statement is detected from its account summary (minimum payment due, credit limit, available credit, payment due date and similar). The OFX then uses the credit card message set, purchases are negative and payments positive, and the balance owed is written as a negative ledger balance, as the OFX specification describes. More on card statements.
Before you export
OFX, QBO and Xero files are only offered when PDFCells' own checks say the numbers are consistent: with a balance column, every row's balance must equal the previous balance plus that row's amount; without one, the statement's summary must tie (opening + credits − debits = closing) and the transactions must add up to it. When a check fails, the export panel says why, with the page and row where it can.
Many statements print dates as month and day only (01/15), with the year only in the statement period. For the statement formats (OFX, QBO, Xero CSV and the bank CSV), PDFCells takes each date's year from the printed period, so December dates on a January statement get the earlier year. When the page prints no period, the export panel's Statement year picker asks for the year, and those formats stay off until you pick one rather than guess. In the Excel export these dates stay as printed text.
OFX files declare a checking account (or a credit card account for card statements). Savings and money market accounts aren’t distinguished. One account per export: statements printed in sections (deposits and withdrawals, or payments and purchases, in separate tables) are combined; otherwise PDFCells prefers a table whose running balance it recognises, then the longest. Untick tables you don’t want exported.
Questions
Can I import a PDF bank statement into Xero?
Not directly; Xero imports OFX, QIF and CSV statement files. Convert the PDF with PDFCells to OFX or the Xero CSV, then use Manage account › Import a statement in Xero.
Which OFX version does PDFCells write?
OFX 1.02 (SGML), the version Xero, Quicken, GnuCash and many banks document for statement import.
Why does the Xero CSV have no category?
Xero’s account codes depend on your chart of accounts, so categories stay out of it. Xero bank rules can match on the cleaned payee instead.