Bank statement file formats: a complete reference

The consumer and small-business formats

• CSV — plain text, read by everything, carries no types. Dates are ambiguous and amounts may or may not be signed. The universal fallback and the one most likely to be silently misread.
• Excel (.xlsx) — types preserved, good for review, refused by many statement importers. Best when a person is reading, not software.
• OFX — self-describing, signed amounts, unambiguous dates, no bank ID needed. The best default for anything that is not Intuit.
• QBO — OFX plus Intuit’s INTU.BID. QuickBooks only. Goes through the banking feed with duplicate detection.
• QFX — the same for Quicken, with a different bank ID at the same bank.
• QIF — pre-OFX, line-oriented, no identifiers and no date order. Imports almost anywhere; deduplicates nowhere.
• IIF — tab-delimited, double-entry, QuickBooks Desktop only. Writes straight into the register with no review step.
• Tally XML — vouchers in Tally’s own envelope, with the debit written as a negative.

The corporate formats

• CAMT.053 — ISO 20022 XML, bank to corporate, the European direction of travel.
• MT940 — SWIFT, tag-based, compact, still widespread. Its :86: information field is structured differently by every bank.
• BAI2 — the US corporate equivalent, fixed record types with standardised numeric transaction codes.

These arrive through a bank feed rather than a download button, and small-business accounting packages generally do not read them.

What each one requires that the others do not

• QBO and QFX need an Intuit bank ID that is not derivable from the statement and differs between the two products at the same bank.
• IIF needs every transaction to balance to zero across its lines, and QuickBooks will not tell you when it does not.
• Tally XML needs ledger names matching the target company exactly, and inverted signs.
• QIF needs you to know which date order was written, because the file cannot say.
• CSV needs a mapping step in most importers, and an explicit date format in all of them.
• OFX needs nothing beyond being valid, which is why it is the easiest to get right.

Choosing by software

• QuickBooks Online: QBO, or three-column CSV without a bank ID.
• QuickBooks Desktop: QBO through Web Connect, or IIF where no bank ID exists.
• Quicken: QFX, with QIF as the fallback.
• Xero: CSV or OFX. Not QBO.
• Sage: OFX across the range; QIF on older Sage 50; CSV on the online products.
• MYOB: OFX, QIF or CSV — and mind the sign convention on CSV.
• Wave, Zoho Books, FreshBooks: CSV, with OFX accepted by the first two.
• Tally: XML vouchers, and nothing else natively.

The one rule worth remembering

Prefer a format that states its own dates and signs. OFX, QBO, QFX and Tally XML all do; CSV and QIF do not, and every silent misimport in this entire subject traces back to that difference.

Choosing by what you are doing

Software is the usual constraint, but not the only one. Where you have a choice, the job decides.

• Posting to the books, routinely: whichever format your package treats as a bank feed. Duplicate detection is worth more than convenience over a year of imports.
• Reviewing before anything is posted: Excel. Types preserved, filterable, and nothing is committed by opening it.
• Handing data to someone else: CSV, with dates written as YYYY-MM-DD. It survives being opened in software you have not seen.
• Analysis — matching across accounts, totalling by payee, searching a disclosure: Excel or CSV into whatever you analyse with. Accounting formats are worse at this than a spreadsheet.
• Archiving: keep the original PDF. Every format here is derived from it, and only the PDF is the document.

The extension tells you nothing

Every format on this page is a text file with a name. Renaming an OFX to .qbo does not add a bank ID; renaming a CSV to .qif does not restructure it; a .qbo served by a bank portal after the session expired is an HTML error page.

The contents settle it, and they are readable. OFX-family files start with OFXHEADER. QIF starts with an exclamation mark. IIF starts with !TRNS and tabs. Tally XML starts with an XML declaration and an ENVELOPE. Thirty seconds in a text editor answers questions that otherwise become support tickets.