Convert bank statements to QIF for Quicken and other money apps

What is a QIF file?

QIF (Quicken Interchange Format) is a plain-text format Intuit created for Quicken. Each transaction is a short block of lines, every line starts with a one-letter code, and a caret (^) ends the transaction. Banks have largely replaced it with OFX-based downloads, but many personal finance apps still import it because it is simple and it can carry a category with every transaction.

This is what the QIF file you download contains:

Which apps still import QIF?

• Quicken for Windows. Use File → File Import → QIF File and choose the account from the drop-down. Older versions refused QIF imports into bank and credit card accounts, but Quicken says current versions can import QIF into any account type.
• Quicken for Mac. QIF is used for starting a new data file from another program, not for adding statements to accounts you already have. Check Quicken's help for your version before relying on it.
• GnuCash. File → Import → Import QIF walks you through choosing the date format and matching QIF categories to your GnuCash accounts.
• HomeBank and MoneyMoney. Both import QIF as well as OFX and CSV.
• Many other desktop budgeting apps — check the import menu for QIF.

If your app also imports OFX, prefer it: OFX gives every transaction a unique ID and an unambiguous date. See bank statements to OFX.

QIF date format: the one thing to check

QIF has no fixed date format. The same line can mean 3 April or 4 March depending on which program wrote it, and some programs write two-digit years with an apostrophe, like 4/ 3'26. Importing with the wrong assumption silently moves transactions to the wrong month — or rejects every day above 12.

The QIF you download always uses MM/DD/YYYY with a four-digit year, the convention Quicken uses. In GnuCash, choose m-d-y when the import asks; the GnuCash manual covers this step. Amounts use a decimal point and no thousands separators, whatever the statement's own number format was. After any import, look at one transaction dated after the 12th of a month to confirm it landed on the right day.

Categories go into the file

Unlike OFX, QIF has a category line, so your categorizing work travels with the transactions. The category on each row is the one you see in the review: a default category in your language, one you renamed, or one set by your own rule. Your app reads these names on import — GnuCash, for example, asks you to match each QIF category to one of its accounts — so it pays to use the same category names in both places.

If you change a category during review, you can turn it into a rule ("description contains X → category") so the next statement comes out right. More on that in categorize bank transactions.

Checked before it reaches your register

A QIF file has no balances and no transaction IDs, so the app you import into can't tell whether a line is missing or appears twice. That makes the check before export important. The opening balance plus money in, minus money out, must equal the closing balance, the running balance is checked on every row, and rows that don't add up are flagged with their page number. A statement that doesn't balance uses no pages (up to 30 pages a month).

Because there are no IDs, importing the same QIF twice usually creates duplicates. Export each period once, or use the export's date range to cover only the months you haven't imported yet.

Credit cards, CSV files and several statements

• Credit card statements export as !Type:CCard. Purchases, fees and interest are negative; payments and refunds are positive. See credit card statements to Excel for how card statements are read.
• CSV or Excel to QIF: upload your bank's export and download QIF. These files use no pages, though you need a plan or page pack to export.
• Several statements: select them and export one QIF, optionally limited to a date range. Each statement gets its own account-type header.
• Not sure QIF is right? Excel or CSV are better for spreadsheets, and QuickBooks has its own import files.

Questions