One page for the whole landscape: what each format is, what software reads it, what it requires that the others do not, how to recognise it from its first line, and where each one fails.
The consumer and small-business formats
These are the formats you download from online banking or feed into accounting and personal finance software. They divide into three groups: plain tables (CSV and Excel), the OFX family (OFX, QBO and QFX), and formats belonging to one vendor (QIF, IIF and Tally XML).
| Format | What it is | Read by | Main weakness |
|---|---|---|---|
| CSV | Plain text table, no types | Almost everything | Dates and signs ambiguous; no transaction IDs |
| Excel (.xlsx) | Typed workbook | People; Sage and a few importers | Refused by most statement importers |
| OFX | Tagged statement with signed amounts and IDs | Xero, Sage, MYOB, Wave, Zoho Books, QuickBooks Online | Two dialects; some readers take only one |
| QBO | OFX plus Intuit’s bank ID | QuickBooks Online and Desktop | Needs the bank’s QuickBooks ID |
| QFX | OFX plus Intuit’s bank ID | Quicken; also Xero, Wave, MYOB desktop | Needs the bank’s Quicken ID, usually a different number |
| QIF | Line-per-field text, pre-OFX | Quicken, Xero, MYOB, Sage Accounting, Zoho Books | No IDs, no stated date order |
| IIF | Tab-delimited double entry | QuickBooks Desktop only | Writes straight to the register |
| Tally XML | Vouchers in Tally’s envelope | TallyPrime and Tally.ERP 9 | Ledgers must pre-exist; inverted signs |
- CSV: the universal fallback and the one most likely to be silently misread, because the reader decides what each value means.
- Excel: types preserved, good for review, and the right thing to hand a person, not an importer.
- OFX: self-describing, signed amounts, unambiguous dates, transaction IDs, no bank ID needed. The best default for anything that is not Intuit.
- QBO and QFX: OFX with Intuit’s INTU.BID. They go through the banking feed with duplicate detection, at the price of a number you have to find.
- QIF: imports almost anywhere and deduplicates nowhere.
- IIF: the most expressive of the set, with splits, classes and both sides of each entry, and the least forgiving.
- Tally XML: the only way to bring a PDF-sourced statement into Tally, as Receipt and Payment vouchers.
Since which format you need depends on software you may not have chosen, being able to produce eight of these from a single conversion means the decision is reversible.
The corporate formats
| Format | Standard | Syntax | Notes |
|---|---|---|---|
| CAMT.053 | ISO 20022 | XML | End-of-day statement; the direction of travel worldwide |
| CAMT.052 / .054 | ISO 20022 | XML | Intraday report and debit or credit notifications; not statements |
| MT940 | SWIFT MT | Tagged lines | Still widespread; :86: detail varies by bank; coexistence planned to end in 2028 |
| MT942 | SWIFT MT | Tagged lines | Interim intraday report |
| BAI2 / BTRS | BAI, now ASC X9 | Numbered comma records | US corporate reporting; standardised type codes and control totals |
These arrive through a bank feed, a host-to-host link or a corporate portal rather than a download button, and small-business accounting packages generally do not read them. The exceptions worth knowing are Zoho Books, which lists CAMT.053, CAMT.054 and MT940, and TallyPrime’s Bank Statement import, which takes MT940 for some banks.
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, account names that match the company file exactly, and US-style MM/DD/YYYY dates.
- Tally XML needs ledger names matching the target company exactly, ledgers that already exist, debits written as negatives, and dates as YYYYMMDD.
- QIF needs you to know which date order was written, because the file cannot say, and a !Type header matching the account.
- CSV needs a mapping step in most importers, an explicit date format in all of them, and often a specific column layout, such as QuickBooks Online’s three or four columns.
- OFX needs nothing beyond being valid and in the dialect the reader expects, which is why it is the easiest to get right.
Choosing by software
| Software | Use | Also accepted |
|---|---|---|
| QuickBooks Online | QBO | QFX, OFX, CSV; PDF or image statements through Upload from file |
| QuickBooks Desktop | QBO via Web Connect | IIF where there is no bank ID or Web Connect is unavailable |
| Quicken | QFX | QIF, with account-type limits that vary by version |
| Xero | OFX | CSV, QIF, QFX, QBO; PDF for selected US and Canadian banks |
| Sage Accounting | OFX | QIF, CSV, Excel |
| Sage 50 (UK) | OFX | CSV, QIF, QBO, QFX, XLS through Bank Feeds |
| MYOB Business | OFX | QIF |
| MYOB AccountRight desktop | OFX | QFX, QIF, OFC |
| Wave | OFX | QBO, QFX, CSV |
| Zoho Books | OFX | CSV, TSV, QIF, CAMT.053, CAMT.054, MT940; PDF for listed banks |
| FreshBooks | CSV | Manual import is CSV |
| TallyPrime | XML vouchers (Alt+O → Transactions) | Bank’s own CSV, Excel or MT940 through Bank Statement import |
Recognising a file from its first line
Every format on this page is a text file with a name, apart from Excel. 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 in any plain text editor.
| First line looks like | Format |
|---|---|
| OFXHEADER:100 | OFX 1.x, or QBO/QFX if an INTU.BID line follows |
| <?xml ...?> then <?OFX OFXHEADER="200" ...?> | OFX 2.x |
| !Type:Bank (or CCard, Cash, Oth A ...) | QIF |
| !TRNS followed by tab-separated column names | IIF |
| <ENVELOPE> | Tally XML |
| <Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053..."> | CAMT.053 |
| :20: on an early line, often after a {1: header block | MT940 |
| 01, followed by comma-separated values ending in / | BAI2 |
| Date,Description,Amount or similar | CSV |
| <!DOCTYPE html> or <html> | A failed download, whatever the extension says |
| PK (binary) | Excel .xlsx, which is a zip package |
Thirty seconds in a text editor answers questions that otherwise become support tickets.
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.
- Catching up on old periods: the OFX family if your software takes it, because transaction IDs make a mistaken overlap harmless.
- 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, such as matching across accounts, totalling by payee or 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 mistakes that cross every format
- Trusting the extension. The first line tells you what a file is; the name tells you what someone hoped it was.
- Overlapping a bank feed. A file and a live connection covering the same dates are two sources with two sets of identifiers, and both land.
- Opening and re-saving in Excel before importing. Long references lose digits, leading zeros vanish and dates are reinterpreted, then the damage is written back.
- Importing a file that has not been checked. A dropped or doubled row imports exactly as cleanly as a correct one; only the arithmetic against the statement’s own balances catches it.
The one rule worth remembering
Prefer a format that states its own dates and signs. OFX, QBO, QFX, Tally XML, CAMT.053 and BAI2 all do; CSV and QIF do not, and nearly every silent misimport in this subject traces back to that difference. Second to that, prefer a format that carries transaction IDs, because that is what turns an accidental re-import from a clean-up job into a non-event.