Quicken Interchange Format
Encyclopedia
Quicken Interchange Format (QIF) is an open specification for reading and writing financial data to media (i.e. file
s).
(OFX). The inability to reconcile imported transactions against the current account information is one of the primary shortcomings of QIF. Most personal money management software, such as Microsoft Money
, GnuCash
and Quicken's low end products (e.g. Quicken Personal and Quicken Personal Plus), can read QIF files to import information. Intuit's Quicken
used to be able to import QIF, too, but with its 2006 version it dropped that support for several important account types, including checking, savings, and credit card accounts.
Quicken's proposed replacement for the QIF format has been the Quicken Web Connect (QFX)
format.
It is commonly supported by financial institutions to supply downloadable information to account holders, especially by banks that support integration of Money or Quicken with their online banking. Not everybody, however, was, or is, happy with this replacement. Some banks dislike it because Quicken (Intuit) charges licensing fees to use QFX. Other banks pass the fees on by charging customers for downloading QFX files. Because Microsoft Money imports either QIF or OFX format files, and Microsoft does not charge banks any licensing fees to use OFX for Money, banks do not normally charge for downloading QIF and OFX files. (QIF and OFX are open formats, free for anyone to use.)
Each record is ended with a
strings, and the file could be edited in any text editor
.
simple example
!Type:Bank
D03/03/10
T-379.00
PCITY OF SPRINGFIELD
^
D03/04/10
T-20.28
PYOUR LOCAL SUPERMARKET
^
D03/03/10
T-421.35
PSPRINGFIELD WATER UTILITY
^
etc.
There are also values for QIF files of internal Quicken information:
A header line is not followed by a separator line; it is immediately followed by the first field of a detail item.
Standard detail item
Notes
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.
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 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 exporting to Excel
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, Quicken 2008, and Quicken 2010.
If the transactions are being imported into an existing account, make sure to reconcile the account in the old data file before creating the QIF export file. Before accepting an import, validate any 'Match' transactions and change to 'New' where there isn't really a match. Quicken can match on amounts even when the dates are significantly different.
When editing the QIF file, check for any transaction Category (the field starting with 'L') for an account name contained in brackets, such as [Checking Account]. The brackets reference another quicken account, and if left in place will post a transaction in that account in addition to the account being imported to, with potentially troublesome results. Avoid this by removing the text including the brackets and replacing with another category if desired. The only exception to this is an opening balance transaction, identified by 'Opening Balance' in the 'P' field (POpening Balance). In this case, the brackets need to be left in place, and the account name between the brackets must exactly match the account name in the 'N' field.
The above two paragraphs are based on exporting and importing in Quicken 2010.
Computer file
A computer file is a block of arbitrary information, or resource for storing information, which is available to a computer program and is usually based on some kind of durable storage. A file is durable in the sense that it remains available for programs to use after the current program has finished...
s).
Background
Although still widely used, QIF is an older format than Open Financial ExchangeOpen Financial Exchange
Open Financial Exchange is a data-stream format for exchanging financial information that evolved from Microsoft's Open Financial Connectivity and Intuit's Open Exchange file formats.-History:...
(OFX). The inability to reconcile imported transactions against the current account information is one of the primary shortcomings of QIF. Most personal money management software, such as Microsoft Money
Microsoft Money
Microsoft Money was Microsoft's personal finance software. It was designed for computers using the Microsoft Windows operating system, though versions for Windows Mobile were also available .The last version, released in August 2007, was called Microsoft Money Plus and came...
, GnuCash
GnuCash
GnuCash is a free and open source accounting software program that implements a double-entry bookkeeping system. It was initially aimed at developing capabilities similar to Intuit, Inc.'s Quicken application, but also has features for small business accounting...
and Quicken's low end products (e.g. Quicken Personal and Quicken Personal Plus), can read QIF files to import information. Intuit's Quicken
Quicken
Quicken is a personal finance management tool developed by Intuit, Inc.Quicken runs on Windows and Macintosh systems. Previous versions ran on DOS. An online version is also available. The last version of Quicken for Macintosh computers was Quicken Essentials for Mac released in February 2010...
used to be able to import QIF, too, but with its 2006 version it dropped that support for several important account types, including checking, savings, and credit card accounts.
Quicken's proposed replacement for the QIF format has been the Quicken Web Connect (QFX)
QFX (file format)
QFX is Intuit's proprietary version of the standard OFX financial interchange file format. QFX is used in Intuit's "Web Connect" and "Direct Connect" features to transmit and receive financial information over the internet....
format.
It is commonly supported by financial institutions to supply downloadable information to account holders, especially by banks that support integration of Money or Quicken with their online banking. Not everybody, however, was, or is, happy with this replacement. Some banks dislike it because Quicken (Intuit) charges licensing fees to use QFX. Other banks pass the fees on by charging customers for downloading QFX files. Because Microsoft Money imports either QIF or OFX format files, and Microsoft does not charge banks any licensing fees to use OFX for Money, banks do not normally charge for downloading QIF and OFX files. (QIF and OFX are open formats, free for anyone to use.)
Data Format
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 ASCIIASCII
The American Standard Code for Information Interchange is a character-encoding scheme based on the ordering of the English alphabet. ASCII codes represent text in computers, communications equipment, and other devices that use text...
strings, and the file could be edited in any text editor
Text editor
A text editor is a type of program used for editing plain text files.Text editors are often provided with operating systems or software development packages, and can be used to change configuration files and programming language source code....
.
simple example
!Type:Bank
D03/03/10
T-379.00
PCITY OF SPRINGFIELD
^
D03/04/10
T-20.28
PYOUR LOCAL SUPERMARKET
^
D03/03/10
T-421.35
PSPRINGFIELD WATER UTILITY
^
etc.
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:| Code | Description |
|---|---|
| !Type:Cash | Cash Flow: Cash Account |
| !Type:Bank | Cash Flow: Checking Account |
| !Type:CCard | Cash Flow: Credit Card Account |
| !Type:Invst | Investing: Investment Account |
| !Type:Oth A | Property & Debt: Asset |
| !Type:Oth L | Property & Debt: Liability |
| !Type:Invoice | Invoice (Quicken for Business only) |
There are also values for QIF files of internal Quicken information:
| Code | Description |
|---|---|
| !Account NAccount Name TAccount Type |
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 Items
The Detail section consists of several Detail Items, 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). If a single transaction block contains several detail items with same code, the last row is used for import.Standard detail item
| Code | Description | Used In | Example |
|---|---|---|---|
| D | Date. Leading zeroes on month and day can be skipped. Year can be either 4 digits or 2 digits or '6 (=2006). | All | |
| T | 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. | All | T-1,234.50 |
| M | Memo—any text you want to record about the item. | All | |
| C | Cleared status. Values are blank (not cleared), "*" or "c" (cleared) and "X" or "R" (reconciled). | All | CR |
| N | Number of the check. Can also be "Deposit", "Transfer", "Print", "ATM", "EFT". | Banking, Splits | N1001 |
| P | Payee. Or a description for deposits, transfers, etc. | PStandard Oil, Inc. | |
| A | 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. | Banking, Splits | A101 Main St. |
| L | 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. For Investments, MiscIncX or MiscExpX actions, Category/class or transfer/class. | Banking, Splits | LFuel:car |
| F | Flag this transaction as a reimbursable business expense. | Banking | F??? |
| S | Split category. Same format as L (Categorization) field. | Splits | Sgas from Esso |
| E | Split memo—any text to go with this split item. | Splits | Ework trips |
| $ | Amount for this split of the item. Same format as T field. | Splits | $1,000.50 |
| % | Percent. Optional—used if splits are done by percentage. | Splits | %50 |
| N | Investment Action (Buy, Sell, etc.). | Investment | NBuy |
| Y | Security name. | Investment | |
| I | Price. | Investment | I5.125 |
| Q | Quantity of shares (or split ratio, if Action is StkSplit). | Investment | Q4,896.201 |
| O | Commission cost (generally found in stock trades) | Investment | O14.95 |
| $ | Amount transferred, if cash is moved between accounts | Investment | $25,000.00 |
| X | Extended data for Quicken Business. Followed by a second character subcode (see below) followed by content data. | Invoices | XI3 |
| XA | Ship-to address | Invoices | XAATTN: Receiving |
| XI | Invoice transaction type: 1 for invoice, 3 for payment | Invoices | XI1 |
| XE | Invoice due date | Invoices | XE6/17' 2 |
| XC | Tax account | Invoices | XC[*Sales Tax*] |
| XR | Tax rate | Invoices | XR7.70 |
| XT | Tax amount | Invoices | XT15.40 |
| XS | Line item description | Invoices | XSRed shoes |
| XN | Line item category name | Invoices | XNSHOES |
| X# | Line item quantity | Invoices | X#1 |
| X$ | Line item price per unit (multiply by X# for line item amount) | Invoices | X$150.00 |
| XF | Line item taxable flag | Invoices | XFT |
Notes
- The S, E, $, and % fields are repeated for each split of this transaction.
- Investment Actions track common investment activities such as Dividends, Reinvestment of income, and Capital Gains (CG). Codes that end in X indicate that the transaction was generated by the account, but the cash is transferred to a different account (in which case the Category field is the destination account name). Action codes include: CGLong (Capital Gains Long Term), CGLongX, CGMid, CGMidX, CGShort, CGShortX, Div, DivX, IntInc, IntIncX, MargInt, MargIntX, RtnCap, RtnCapX, XIn, XOut, Added, Removed, StkSplit
- If the line immediately following an XS record does not begin with ^ or X, that is considered a continuation of the XS record.
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.
Record End
The separator line signals the end of the current transaction. The last detail item in the file should end with a separator line.| Code | Description |
|---|---|
| ^ | A single caret in the first column, and the rest of the line blank. |
Export Hacks for QuickBooks: exporting to QIF
QuickBooksQuickBooks
QuickBooks is a line of business accounting software developed and marketed by Intuit.-History:Intuit was founded in 1983 by Scott Cook and Tom Proulx in Mountain View, California, USA...
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 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 exporting to Excel
Import Workarounds 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 support 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 by copying-and-pasting the top four lines from the example below - the example is for an investment transaction:!Account
NJoint Brokerage Account
TInvst
^
!Type:Invst
D12/21' 7
NBuy
YIBM
T11010.00
I110.10
Q100
MPurchase of 100 shares of IBM stock on 21 December 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, Quicken 2008, and Quicken 2010.
If the transactions are being imported into an existing account, make sure to reconcile the account in the old data file before creating the QIF export file. Before accepting an import, validate any 'Match' transactions and change to 'New' where there isn't really a match. Quicken can match on amounts even when the dates are significantly different.
When editing the QIF file, check for any transaction Category (the field starting with 'L') for an account name contained in brackets, such as [Checking Account]. The brackets reference another quicken account, and if left in place will post a transaction in that account in addition to the account being imported to, with potentially troublesome results. Avoid this by removing the text including the brackets and replacing with another category if desired. The only exception to this is an opening balance transaction, identified by 'Opening Balance' in the 'P' field (POpening Balance). In this case, the brackets need to be left in place, and the account name between the brackets must exactly match the account name in the 'N' field.
The above two paragraphs are based on exporting and importing in Quicken 2010.
External links
- Official Specification
- How to parse online bank QIF with sed
- Python script to convert QIF to RDF. Look for def extract(path). Script provided by SWAP.
- Perl module to parse and create QIF files.
- GnuCash QIF format notes
- .NET QIF API