Quicken Interchange Format (QIF) is an open specification for reading and writing financial data to media (i.e. files).
Background Although still widely used, QIF is a format 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. Most personal money management software, such as Microsoft Money, GnuCash and Quicken's low end products (e.g. Quicken Personal and Quicken Personal Plus),In fact, QIF files are the only format files that Quicken Personal and Quicken Personal Plus can import. 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. The Australian version of Quicken still allows the importing of QIF files for these account types. However, unlike the American version, it is not possible to export data to QIF or any other file type for any account type. The QIF format does not allow a user to mark the currency in which a transaction was completed. In some cases this may cause problems for users who do use multiple currencies when they export or import into another software package.
Quicken's proposed replacement for the QIF format has been the proprietary 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.)
Data format A QIF file typically has the following structure:
Each record ends with a ^ (caret). All the data in the file is stored in ASCII 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.
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 & Savings 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 !AccountNAccount NameTAccount TypeDAccount Description Account list or which account follows !Type:CatNCategory NameDCategory description 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. For investment transactions, this amount is always a positive number, even for Buy and CvrShrt transactions. All T-1,234.50USeems identical to T field (amount of item.) Both T and U are present in QIF files exported from Quicken 2015.AllU-1,234.50 M Memo—any text you want to record about the item. All C Cleared status. Values are blank (unreconciled/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. (40 characters maximum) Banking, Splits LFuel:car F Flag this transaction as a reimbursable business expense. Banking F??? S Split category. Same format as L (Categorization) field. (40 characters maximum) 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). For investment transactions, this amount is always a positive number, even for Sell and ShtSell transactions. 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.00BBudgeted amount - may be repeated many times for monthly budgets.CategoriesB85.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
Investment Actions
Investment Action DescriptionBuyBuy a security with cash in the accountBuyXBuy a security with cash transferred from another accountCashContribXCash transferred into an account from another account. (The other side of WithdrwX.)WithdrwXCash transferred out of an account into another account. (The other side of ContribX.)ExercisXExercise stock options with cash transferred from another accountExerciseExercise stock optionsExpireExpiration of stock optionsGrantGrant of stock optionsVestVesting of stock optionsSellSell a security with proceeds received in the accountSellXSell a security and transfer the proceeds to another accountShtSellSell a security in a short sale transaction and deposit sale proceeds in the accountCvrShrtBuy back a security sold short to close the position with cash in the accountCGLongLong-term capital gains distribution received in the accountCGLongXLong-term capital gains distribution transferred to another accountCGMidMedium-term capital gains distribution received in the accountCGMidXMedium-term capital gains distribution transferred to another accountCGShortShort-term capital gains distribution received in the accountCGShortXShort-term capital gains transferred to another accountDivDividend received in the accountDivXDividend transferred to another accountIntIncInterest Income received in the accountIntIncXInterest Income transferred to another accountReinvDivDividend reinvested in additional shares of the securityReinvIntInterest Income reinvested in additional shares of the securityReinvLgLong-term capital gains reinvested in additional shares of the securityReinvMdMedium-term capital gains reinvested in additional shares of the securityReinvShShort-term capital gains reinvested in additional shares of the securityRepriceReprice employee stock optionsXInCash transferred into the accountXOutCash transferred out of the accountMiscExpMiscellaneous expenseMiscExpXMiscellaneous expense covered by another accountMiscIncMiscellaneous income, optionally associated with a securityMiscIncXMiscellaneous income, optionally associated with a security, transferred to another accountMargIntInterest paid on a margin loan received in the accountMargIntXInterest paid on a margin loan transferred from another accountRtrnCapReturn of capital received in the accountRtrnCapXReturn of capital transferred to another accountStkSplitChange in the number of shares as a result of a stock split.ShrsOutRemoval of shares from an accountShrsInAddition of shares into an account
Notes The S, E, $, and % fields are repeated for each split of this transaction. For investment action codes that end in X, the Category field indicates the other account used to transfer cash from or to. 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 workarounds for QuickBooks: exporting to QIF QuickBooks doesn't allow exporting to QIF. Only the Intuit Interchange Format (IIF) is supported. An IIF to QIF converter will not solve the problem either, as journal entries can't be exported in IIF format, only lists are exportable. A viable way to overcome this problem is to set up a journal report, to show all journal entries. Print the report using 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 need to be reorganized to generate the appropriate format for the macro to work, such as separating cheque accounts from term deposits, etc. The above referenced Excel macro supports split transactions.
See references for further examples of reporting 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