QBO vs OFX vs QFX vs QIF: which one to use

Four file extensions, three of which are the same format underneath, and one accounting package that refuses two of them. If you have ever exported a bank statement and had the import screen reject it, this is the page that explains why, and which file to ask for next time.

The short answer

If your software is not in the table, look for the word "Web Connect" (that means QBO or QFX) or "OFX" in its import help. If it lists neither, it wants CSV.

What each format is

QIF

Quicken Interchange Format is the oldest of the four, a plain-text file from Quicken's first years. Each transaction is a few lines: a date, an amount, a payee, a memo, a caret to end the record. QIF has no account identifier, no bank identifier, and no unique transaction ID. A file imported twice produces every transaction twice, and there is nothing in the file to stop that. Its dates are also written in whatever form the writer chose, so a 03/04 line can be March 4 or April 3 depending on who made the file. Quicken for Windows stopped accepting QIF for bank and card accounts years ago; Quicken for Mac, Xero, GnuCash and a handful of older tools still read it.

OFX

Open Financial Exchange is the format the others are built on. It was published in the late 1990s by a group of financial software companies and is still the way most banks describe a download. An OFX file has a header, then a block that names the bank and account, then one record per transaction with a type, a posting date, an amount and a unique ID called the FITID. That ID is the important part: software that imports OFX uses it to skip transactions it has already seen, so importing the same month twice does not double your ledger. Version 1 files look like SGML (tags without closing tags); version 2 files are XML. If you are not sure what a package wants and it is not a QuickBooks or Quicken product, OFX is the safe first attempt.

QFX

QFX is OFX with Intuit-specific tags added for Quicken. The file is otherwise identical. Quicken calls this "Web Connect", and it is the format banks provide when you click "download for Quicken". The extra tags identify the financial institution, and Quicken checks them against its own list before it will import the file (more on that below).

QBO

QBO is the QuickBooks flavor of the same thing: OFX plus the Intuit tags, with the extension changed so QuickBooks recognizes it. QuickBooks Desktop takes bank transactions in no other file format. QuickBooks Online accepts QBO too, along with CSV and, per Intuit's own upload page, OFX and QFX. A QBO and a QFX file for the same account are nearly byte-for-byte the same; the software just refuses the wrong extension.

The INTU.BID quirk

The Intuit tags in QFX and QBO include a field called INTU.BID, a numeric ID for the bank that issued the file. Quicken and QuickBooks check this ID against Intuit's list of participating institutions. If the number is missing or is not on the list, the import fails, usually with a message that says the file cannot be read, which sends people looking for corruption that is not there.

This is why a hand-written or generic OFX file renamed to .qbo often fails, and why converters have to do something about it. The usual approach, and ours, is to write a valid INTU.BID for a bank that is on the list, matched to the bank on the statement where possible. The practical consequence: when you import, QuickBooks may display a different institution name than the one printed on your statement. That is cosmetic. The transactions, dates and amounts are yours; the label comes from Intuit's list. If it bothers you, you can rename the account inside QuickBooks after the import.

Dates and signs

Two conventions cause most of the remaining import trouble.

Dates. OFX, QFX and QBO write dates as YYYYMMDD, sometimes with a time and a timezone appended. There is no ambiguity. CSV and QIF write dates however the author chose, so a US statement converted by a tool set to day-first ordering will move every transaction from the 1st to the 12th of the month into a different month. Before importing a CSV, look at a date after the 12th and confirm the software read it the way you expect.

Signs. In the OFX family, money leaving the account is negative and money arriving is positive, and that holds for credit cards too: a purchase is negative, a payment to the card is positive. Some CSV imports want that single signed column; QuickBooks Online also accepts a four-column layout with separate debit and credit columns, both positive. Mixing the two, or importing a statement where the bank printed withdrawals as positive numbers, is the most common reason a month imports with every sign backwards. Our exports use the OFX convention throughout, and the conversion result shows the sign of every row against the statement's running balance, so a flipped column shows up before you import. What that check does and does not catch is on the reconciliation page.

How to import into QuickBooks Online

The screens move around between versions, but the sequence has been stable for years.

1. Open the bank transactions area (Transactions, then Bank transactions, or Banking on older layouts).
2. Choose the option to upload transactions from a file rather than connect an account.
3. Select the QBO file. If you are importing CSV instead, you will be asked to map columns; date format and the single-column versus two-column amount layout are the two choices that matter.
4. Pick the QuickBooks account the transactions belong to. Make sure the bank account already exists in your chart of accounts.
5. Confirm. The rows land in the review list, not in the register, and you categorize or match them from there.

Duplicates are skipped by FITID when the file is QBO. With CSV there is no ID, so QuickBooks matches on date and amount and will ask about anything that looks like a repeat.

How to import into Xero

1. Open the bank account from the Accounting menu.
2. From the account's manage menu, choose Import a statement.
3. Upload the OFX file. For CSV, Xero shows a column-mapping step; assign the date, amount and description columns and confirm the date format.
4. Review the imported lines. They appear as statement lines waiting to be reconciled, not as posted transactions.
5. Reconcile as usual.

Xero also uses the FITID to avoid importing the same line twice from OFX, and it flags CSV rows that look like duplicates of lines already imported.

When CSV is the right answer

CSV is the one format everything reads, which makes it tempting to use everywhere. It is the right choice when the destination is a spreadsheet, when the software has no OFX import, or when you want to edit rows before they go in. It is the wrong choice when a Web Connect format exists for your software, because you give up duplicate protection, unambiguous dates and a fixed sign convention, and you have to map columns every time.

Our converter produces all eight files from one conversion, so you do not need to decide before uploading; the formats page lists what each contains.

Related

• What a reconciliation check really catches
• Bank statement PDF to Excel: 4 methods
• Export formats