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.

• CSV ("Comma Separated Values") is an ordinary spreadsheet format, which many people will recognize if you're familiar with Microsoft Excel. It's a plain text file with each row representing one transaction, and the first row representing the column labels.
  
  However, there is no agreed-upon standard for banking transactions, so each bank may format it in its own way. The "Date" column might be "Transaction Date" or "Posting Date", for example. Another column might be "Amount", "Transaction Amount", "Credit", "Credit Amount", or "Deposit". It may be the second column or the sixth. Charges or debits may be negative numbers, or may be positive numbers in a separate "Debits" column.
  
  If you want to use csv files to transfer transaction data to your program, you will probably have to clean up and modify the csv file first to conform to a predictable pattern.
  
  
• QIF ("Quicken Interchange Format") is a rather simple ASCII text format with name-value pairs that follow a consistent scheme. Although it was originally developed by Intuit, it's an open standard. Several Quicken competitors have also adopted it (and continue to use it), though Quicken dropped support for qif in 2005.
  
  
• OFX ("Open Financial Exchange") is another text format, and utilizes a scheme similar to XML, with "tags" in angle brackets followed by their values. It's extra complexity (a broader range of tags than qif) can facilitate some additional features such as automatic account association and automatic balancing. A lot of Quicken's competitors support ofx, but Quicken does not.
  
  
  
  Quicken will not import QFX without a license compliance check.
• QFX ("Quicken Financial Exchange") is the same as ofx but with the addition of a few Quicken-specific tags for license control. This is the only input format that Quicken now supports.
  
  Intuit probably recognized online updating was a lucrative opportunity to lock customers into a closed ecosystem and turn them into cash cows, paying for Quicken over and over again, ad infinitum. While Intuit might keep adding new features to Quicken every year, they would still have to convince the customer it was worth paying for the upgrade. In contrast, if they could arbitrarily disable the import feature every few years, customers would be forced to keep buying new versions just to retain the same functionality they already had. Quicken might see this as a win, since they wouldn't have to keep marketing to current customers to keep getting money from them.
  
  The modification to ofx facilitates Quicken's ability to kill the import facility when it wants. Before a qfx file can be imported, Quicken requires an internet connection so it can verify license compliance. This is a form of pre-planned obsolescence that forces users to rebuy Quicken at least every three years.
  
  In a classic case of "double-dipping", Intuit can also extract license fees from the banks. Remember, banks can just as easily use the otherwise identical ofx format without paying license fees, but that does no good if the Quicken program is blocked from accepting ofx. By requiring the .qfx extension and license control information, Quicken can force banks to pay up or the bank won't be able to support its customers who use Quicken.
  
  

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.

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.


The web portals of banking and credit card websites may offer to export transaction data in one or more of these formats.

• CSV: Many portals may offer downloads in csv format because it's compatible with Excel and familiar to people. But if you want to import it into your financial program you may have to manually clean up the file in Excel first.
  
  
• QFX: With Quicken the de facto leader in financial management software, the majority of banks and credit card companies will feel they have to support qfx exports, despite any licensing fees, because they don't want to cut off their customers who use Quicken.
  
  
• OFX: Popular with many non-Quicken programs, a lot of web portals will support ofx exports. Since it's functionally identical to qfx, there's probably little reason a website shouldn't offer ofx if it also offers qfx.
  
  
• QIF: Intuit dropped support for qif in 2005, and with qfx forcing Quicken customers to upgrade every three years, over time fewer and fewer bank customers were using the older qif-compatible versions of Quicken. Eventually banks and credit card companies stopped supporting it.
  
  Most web portals today don't offer qif exports. Yet, several Quicken competitors still support qif, and if you still have a pre-2005 version of Quicken it will still import qif files. But you need to be able to get a file into qif format in the first place. The rest of the information below is about how to do that.

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.


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.