Quicken Interchange Format

Quicken Interchange Format (QIF) is an open specification for reading and writing financial data to media (i.e. files). A QIF file typically has the following structure:

!Type:type identifier string [single character line code] Literal String Data ... ^ [single character line code] Literal String Data ... ^

Each record is ended with a ^ (caret). All the data in the file is stored in ASCII strings, and the file could be edited in any text editor

QIF is older than Open Financial Exchange (OFX). The inability to reconcile imported transactions against the current account information is one of the primary shortcomings of QIF. It is commonly supported by financial institutions to supply downloadable information to account holders. Most personal money management software (Microsoft Money, Intuit's Quicken versions earlier than 2006) can read QIF files to import information. However, Quicken has dropped support of QIF for online banking accounts as of version 2006 [http://web.intuit.com/personal/quicken/qif/] . However, as of release R4, Quicken 2006 still supports importing and exporting transactions in QIF format for asset, liability, cash, small business payable, and invoice accounts [http://web.intuit.com/personal/quicken/qif/qif_faqs.html] . Banks that support integrated online banking (i.e. as part of Money or Quicken) usually use OFX instead of QIF.

Some banks are objecting to this change, because Quicken (Intuit) charges licensing fees to use OFX. Other banks are passing on these fees, by charging customers for downloading OFX files. (QIF is an open format, free for anyone to use. Banks do not normally charge for downloading QIF files.) Microsoft Money imports either QIF or OFX format files, and Microsoft does not charge banks any licensing fees to use OFX for Money.

Details

*Header line: the first line in the file must be a header line, to identify the type of data contained. Valid values for accounts are:
**!Type:Bank
**!Type:Invst
**!Type:Cash
**!Type:Oth A
**!Type:CCard
**!Type:Oth LThere are also values for QIF files of internal Quicken information:
**!Account Account list or which account follows
**!Type:Cat Category list
**!Type:Class Class list
**!Type:Memorized Memorized transaction list A header line is not followed by a separator line; it is immediately followed by the first field of a detail item.
*Detail Item: consists of several fields, each on a separate line. Each line begins with a single character identifying code in the first column, followed by the literal data for that field. The detail item is terminated by a separator line. The fields can be in any order (except that within split transactions, the category, amount, and memo must be adjacent). The fields are:
**standard (non-split) detail item:
***D12/25/2006 Date. Month-day-year order. Leading zeroes on month and day can be skipped. Year can be either 4 digits or 2 digits or '6 (=2006).
***N1001 Number of the check. Can also be "Deposit", "Transfer", "Print", "ATM", "EFT".
***PStandard Oil, Inc. Payee. Or a description for deposits, transfers, etc.
***A101 Main St. Address of Payee. Up to 5 address lines are allowed. A 6th address line is a message that prints on the check. 1st line is normally the same as the Payee line -- the name of the Payee.
***Mgasoline for my car Memo -- any text you want to record about the item.
***CR Cleared status. Values are blank (not cleared), "c" (pending clearance) or "R" (cleared and reconciled).
***LFuel:car Category or Transfer and (optionally) Class. The literal values are those defined in the Quicken Category list. SubCategories can be indicated by a colon (":") followed by the subcategory literal. If the Quicken file uses Classes, this can be indicated by a slash ("/") followed by the class literal.
***F??? Flag this transaction as a reimbursable business expense.
***T-1,234.50 Amount of the item. For payments, a leading minus sign is required. For deposits, either no sign or a leading plus sign is accepted. Do not include currency symbols ($, £, ¥, etc.). Comma separators between thousands are allowed.
**split detail item:
***D, T, N, A, M, C, and L fields as above.
***Sgas from Esso Split category. Same format as L field.
***Ework trips Split memo -- any text to go with this split item.
***$1,000.50 Amount for this split of the item. Same format as T field.
***%50 Percent. Optional -- used if splits are done by percentage.
***The S, E, $, and % fields are repeated for each split of this transaction.
**Investment item:
***D, C, P, M, and T fields as above.
***L field as above, or for MiscIncX or MiscExpX actions, Category/classtransfer/class.
***NBuy Action.
***YIDS Federal Income Security name.
***I5.125 Price.
***Q4,896.201 Quantity of shares (or split ratio, if used instead).
***O??? Commission
***$25,000.00 Amount transferred. All the fields in detail items are optional -- if not included, that field will be left blank in the imported transaction. Also, Quicken seems to do little error checking on these items; if the same field is included twice in the detail item, the second one will just overwrite the first one.

*Separator line: this ends a detail item, and separates it from the next detail item. The last detail item in the file should end with a separator line. The format is a single caret (^) in the first column, and the rest of the line blank.

Export Hacks for QuickBooks: exporting to QIF

QuickBooks doesn't allow exporting to QIF. Only the IIF format is supported. An IIF to QIF converter will not solve the problem either, as the journal entries can't be exported in IIF format, only the lists are exportable. A passable hack to overcome this problem is to set up the journal report, so that it shows all journal entries. Print the report but use the print to file option. Set the file type to Excel before printing. Rename the extension of the resulting file from prn to csv. Use this [http://xl2qif.chez-alice.fr/xl2qif_en.php XL2QIF Excel macro] to convert to QIF. The Excel file may well need massaging to get the results in the appropriate format to suit the macro, such as separating out cheque accounts from term deposits, etc. The Excel macro supports split transactions.

This site demonstrates further examples of [http://accountant.intuit.com/practice_resources/articles/technology/article.aspx?file=rs_usingexcelpivotchartswithquickbooks exporting to Excel]

Import Hacks for Quicken 2005+: importing from QIF

While Intuit officially only supports QIF importing on asset, liability, cash, small business payable, and invoice accounts, Quicken will still allow you to import transactions using QIF into any account type. Two methods are explained below:

Indirect (Temporary Cash Account) Method

Note: If you really need to get data from a QIF file into an account that does not supported QIF imports (e.g. Quicken 2005 and later), you can can import from the QIF file into a (temporary) Cash account. Make sure the first line in the QIF file says "!Type:Cash" for importing it into a Quicken Cash account. (QIF files can be edited in any text editor.) After importing the transactions, you can copy/paste them into a register of your choice. Tested in 2006 version. Note that you can not copy cash transactions into brokerage accounts in Quicken 2007.

Direct Method

To import transactions into brokerage accounts (or any account), a little text editing is necessary. Ensure to prefix your transaction data with the top 5 lines in the example below - the example is for an investment transaction:

!AccountNJoint Brokerage AccountTInvst^!Type:InvstD12/21' 7NBuyYIBMT11010.00I110.10Q100MPurchase of 100 shares of IBM stock on 12/21/2007 at $110.10 per share^

Line 2 (N) in the example must contain the exact name of the account you are intending to import into. When importing the file, the account you choose in the QIF-import-dialog box is irrelevant (you can even choose "all accounts") - once you begin the import process, the application will prompt to confirm importing to the intended account.

The example above was tested in Quicken 2007 and Quicken 2008.

External links

* [http://web.intuit.com/support/quicken/docs/d_qif.html Official Specification]
* [http://www-128.ibm.com/developerworks/linux/library/l-sed3.html How to parse online bank QIF with sed]
* [http://www.w3.org/2000/10/swap/pim/qif2n3.py Python script to convert QIF to RDF] . Look for "def extract(path)". Script provided by [http://www.w3.org/2000/10/swap/ SWAP] .
* [http://search.cpan.org/~mmcgillis/Finance-QIF-2.06/lib/Finance/QIF.pm Perl module to parse and create QIF files.]
* [http://svn.gnucash.org/trac/browser/gnucash/branches/1.8/src/import-export/qif-import/file-format.txt GnuCash QIF format notes]