Main   Back to Tutorials Index 
Using Quicken QIF Files
If you use a personal finance program to track a savings, checking or credit card account, you probably already know that you can often download transaction data from your financial institution's website and import the transactions into your program. There are four major formats for these transfer files: CSV, QIF, OFX and QFX.

TRANSFER FILE FORMATS
WHICH FORMATS DO THE PROGRAMS SUPPORT?
Quicken's principal competitor used to be Microsoft Money. When Microsoft discontinued the program, they released a final "Sunset" version which eliminated the registration and online product authentication parts of the program. Anyone can now install it for free. Money supports qif and ofx imports.

Quicken versions prior to 2005 support qif imports. Later Quicken versions only support qfx.


Quicken 2004 imports QIF transactions.

Quicken 2006 does not support QIF.

Note: Quicken has gradually added copious bells and whistles to the program, tracking much more than just bank accounts and credit cards. Quicken can track every aspect of your financial health, from stocks and bonds to real estate and business income. They'll even track your net worth – if you're willing to give Intuit every bit of your complete financial profile. If you're okay with that, a current Quicken version may be attractive to you, and this whole discussion is moot.

If all you want, however, is a program to help manage simple bank and credit card accounts, an older Quicken version will be much more straightforward and easier to use.


A Google search will turn up a wide variety of freeware programs that also support csv, qif, and ofx imports. Note these Quicken competitors can also use qfx imports and simply ignore the superfluous license control tags. So if your program only imports ofx and your web portal only exports qfx, you should be able to get by with simply renaming the download file's .qfx extension to .ofx.


WHICH FORMATS DO THE WEB PORTALS SUPPORT?
The web portals of banking and credit card websites may offer to export transaction data in one or more of these formats.
CONVERTING QFX TRANSACTIONS TO QIF

Drop your downloaded csv/qfx/ofx files into the same folder as the .cmd scripts.
To help convert ofx, qfx, and csv files to qif format, I have created a couple small scripts and bundled them in a downloadable zipfile. Download qfx-to-qif.zip, extract the "QFX-to-QIF" folder from the zipfile, and place it on your desktop or someplace readily accessible.

To convert qfx/ofx to qif:
  1. Download your bank or credit card transaction history from your financial institution's web portal in qfx or ofx format. (Note some companies may refer to qfx as "Quicken 2010" format.)

    Remember, qfx and ofx are essentially identical. The only differences are some license control tags which qif is going to discard anyway, so the same procedure can be used to convert both qfx and ofx files.

  2. Add the downloaded files to the "QFX-to-QIF" folder.

  3. Double-click the "CONVERT OFX-QFX FILES TO QIF.cmd" file to convert all the qfx and ofx files in the folder to qif files.
Import the qif files into the corresponding accounts in your personal finance program.


CONVERTING CSV TRANSACTIONS TO QIF
To convert csv to qif, you'll first need to download and modify the csv file. The major problem with csv files is that there is no agreed upon standard. However, by judiciously modifying the csv file to conform to a unified standard, a script can be written to that standard that will convert the csv file to qif format.
  1. Download your bank or credit card transaction history from your financial institution's web portal in csv format.

  2. Modify your csv files as follows:

    • use the following column names: Date, Amount, Cleared, ChkNum, Payee, Category, Memo

    • The qif format spec is pretty spartan and doesn't allow for much more than that, so if your csv contains extra columns they should be dropped because they won't be imported anyway.

    • The conversion script will read only the first seven columns, so while it's recommended to delete extraneous columns, if you do keep any at least move them to the right of column 7.

    • Labels of any extraneous columns must not contain any of the protected names (e.g., "Posting Date" or "Debit Amount" will conflict with "Date" or "Amount").

    • If the csv file contains separate "Credit" and "Debit" columns, they should be combined into a single "Amount" column, with positive numbers denoting credits and negative numbers denoting debits.

    • Amount entries should be formatted as numbers without commas or '$' prefix.

    • Date entries should be formatted as mm/dd/yyyy.

    • Payee, Category, or Memo entries may be edited in the csv lines if you wish. Categorizing transactions can be done in Quicken, but it may be easier to do in Excel while still in csv format.

  3. After saving the modified csv file, open it in Notepad (or any other text editor) and rescan to make sure there are no quotation marks. Quotation marks are often a sign there is a comma in an entry somewhere. But the csv spec reserves commas as field delimiters, so extra commas can confuse the conversion script.

  4. Add the modified csv files to the "QFX-to-QIF" folder.

  5. Double-click the "CONVERT CSV FILES TO QIF.cmd" file to convert csv files to qif.

  6. Edit each qif so the first line is either "!Type:Bank" or "!Type:CCard" (without quotes). The csv files contain no information about whether the transactions correspond to a bank account or credit card account, so the script can't determine which to insert. Before importing into Quicken you must edit the first line of the converted qif file to indicate the Type of account.
Import the qif files into the corresponding accounts in your personal finance program.





Back to Top
last revised: 03/04/2022

Valid HTML5 author: Dan Goodell