Convert bank statements to OFX files

What is an OFX file?

OFX (Open Financial Exchange) is a standard file format for bank and credit card transactions. Instead of columns that you have to map by hand, an OFX file labels everything: which account it belongs to, the date of each transaction, the amount, the payee, and the closing balance. That is why finance apps can import it without asking you which column is which.

OFX also removes two problems that plague CSV imports. Dates are written as YYYYMMDD, so 03/04 can never be mistaken for 4 March or 3 April, and amounts always use a decimal point with a minus sign for money out. For the background — how OFX relates to QFX and QBO, and where banks offer it — see what an OFX file is.

What's inside the OFX file you download

You get an OFX 1.0.2 file (the SGML version), which is the version most personal finance and accounting apps read. Each statement becomes one statement section in the file:

Plain OFX has no Intuit bank ID, so QuickBooks Desktop won't import it. For QuickBooks Desktop, export the .qbo Web Connect file instead — see bank statements to QuickBooks.

Which apps import OFX?

OFX is supported by a long list of desktop and online apps. A few common ones, and the right file for the ones that need something else:

PDF to OFX: why the balance check matters

Once a transaction is imported, it sits in your register and gets reconciled against. If a converter skipped a line at a page break or read a debit as a credit, you usually find out weeks later when the account won't reconcile — and then you have to hunt for the cause.

Every statement is checked before you export it. The opening balance plus money in, minus money out, must equal the closing balance, and the running balance is checked on every row. Signs that were misread are corrected from the running balance, duplicate rows at page breaks are removed, and any row that still doesn't add up is flagged with its page number. A statement that doesn't balance uses no pages (up to 30 pages a month).

Scans and phone photos go through the same check. See scanned bank statements to Excel for tips on photos.

CSV to OFX and Excel to OFX

Many banks only offer CSV downloads, while your app imports OFX more reliably. Upload the bank's CSV or Excel file and export OFX: columns are recognised automatically, including separate debit and credit columns, decimal commas and day-first or month-first dates, so there is no template to set up.

CSV, Excel, OFX, QIF, CAMT.053 and MT940 files use no pages, because there is nothing to read from images. You still need an active plan or a page pack to export them. If you want to go the other way — open an OFX file in a spreadsheet — upload it and export Excel or CSV.

How duplicates are avoided when you import twice

Each transaction in the file carries its own FITID. Apps that support OFX use it to spot transactions they have already imported: GnuCash, for example, compares transaction IDs, dates and amounts against what is already in the account and skips exact matches. If you export the same statement again, the IDs stay the same, so a second import doesn't double up.

The one case IDs can't solve is overlap with a bank feed. Transactions that arrived through a live connection carry the bank's IDs, not ours, so the app sees them as different. Before importing, check the date of the last transaction the feed brought in, and use the export's date range to include only the period the feed doesn't cover.

Several statements in one OFX file

• Select several statements and export them together; each account gets its own statement section in one file.
• Limit the export to a date range, for example the months before your bank feed started.
• A PDF that contains several accounts or currencies is split into one statement per account before export.
• Accounts are identified by the last four digits of the account number, so the first time you import, your app may ask which account the file belongs to.

Questions