QIF to OFX Converter: Old Register Files into OFX

A QIF to OFX converter turns an old register export into an Open Financial Exchange statement. It keeps the dates, amounts, payees and memos, and it has to supply what QIF never stored: an institution, an account number, a currency and a unique ID for each line. Check those made-up values before the first import.

What does a QIF register file lack that OFX needs?

A QIF register file lacks everything that ties transactions to a bank. It has no institution name, no account number, no currency and no per-line ID. OFX requires all of these, so converting is not a matter of renaming. The converter writes a new file around your old lines.

QIF stores each transaction as a few lines of text, each starting with a letter: D for the date, T for the amount, P for the payee, M for the memo. A caret on its own line ends the record. The only account information is a header such as !Type:Bank.

OFX is a structured statement format. The FDX OFX Work Group page describes it as an open standard for exchanging financial data, with version 1 files written in SGML and version 2 files in XML. Either way, an OFX statement wraps its transactions inside blocks that name the bank, the account and the period covered.

Dates also need work. A QIF date can be 4/9'24 or 04/09/2024, and the file does not say which number is the month. OFX dates are always written year first, as in 20240409, so the converter has to choose an order and you have to confirm it.

Bank ID, account number and FITID values a QIF to OFX converter supplies

A QIF to OFX converter supplies the bank ID and account number from what you type in, and it builds a FITID, the transaction ID, for every line. The FITID matters most. Programs use it to skip lines they already have, so it has to be stable and unique.

Expect the converter to ask for these values, or to fill them with placeholders:

• Bank ID or routing number. Use the real routing number if the account is a US bank account. A placeholder works for some programs and fails in others.
• Account number. Use the real number, or at least the last four digits, so you can tell files apart later.
• Account type. Checking, savings or credit card. Take it from the QIF header: Bank or CCard.
• Currency. Normally USD for US accounts.
• FITID. One per transaction. A pattern like the date plus a counter, such as 20240409-002, gives the same ID if you convert the file again.

Categories and splits do not survive. OFX is a bank statement format, so it has no field for a category. The L and S lines in the QIF are dropped.

Which programs want an OFX file instead of QIF?

Programs that import bank statements but do not read QIF want an OFX file. QuickBooks Online is one: Intuit's guide to uploading transactions manually into QuickBooks Online lists CSV, TXT, QBO and OFX files for the upload, and QIF is not among them.

Many budgeting apps and some accounting tools also read OFX but not QIF. If your target program offers a QIF import, use it and skip the conversion. Each conversion adds a chance for a date or a sign to change.

If you are heading the other way, from an OFX download into an older program that only reads QIF, the steps reverse. Converting OFX to QIF covers what gets lost in that direction. And if the data never left a spreadsheet, turning an Excel sheet into a QFX file may be a shorter route than building a QIF first.

Renaming .qif to .ofx and other QIF to OFX shortcuts that fail

Renaming a .qif file to .ofx is the shortcut that fails most often. The contents stay QIF, so the program cannot read them and reports an error or imports nothing. Two other shortcuts fail more quietly: converting a multi-account QIF in one go, and accepting random transaction IDs.

A multi-account QIF has several !Account sections. An OFX statement describes one account. If the converter does not split the file, all lines may land in one account. Split the QIF first, one section per file.

Random IDs cause trouble later. If the converter makes new IDs every time it runs, converting the same QIF twice gives two files that look different. Import both and every line appears twice.

When the bank still exists and still offers downloads, a real export from the bank beats any conversion. For an example of finding that download, see downloading a QBO file from Capital One.

Six checks on the OFX output before the first import

Six checks on the converted OFX catch most problems before they reach your books. Open the file in a text editor, look at the header, the account block, three dates, three amounts, the IDs and the line count. Then import it into a spare account first and compare the total with the original.

Run through this checklist:

1. Header. The file starts with an OFX header, not with !Type:. If you still see QIF lines, the conversion did not run.
2. Account block. The account number and type match the account you mean to import into.
3. Dates. Three DTPOSTED values, from the start, middle and end of the file, match the QIF dates in the right order.
4. Signs. Withdrawals are negative in TRNAMT and deposits positive.
5. IDs. Every FITID is different. Search the file for one ID and confirm it appears once.
6. Count. The number of transaction blocks equals the number of ^ lines in the QIF.

After those six, import into a new, empty account in the target program. Compare the total and the number of lines with the QIF. Only then import into the real account.

If you later need the data as a spreadsheet again, a QBO or OFX file can go back to CSV. Converting QBO to CSV shows how. SimpleBankConvert, still in early access, works from PDF bank statements rather than QIF files, and it is being built to produce CSV or QBO for QuickBooks. The SimpleBankConvert homepage shows what it covers.