QIF file format --------------- The QIF is an old and rather broken file format defined by Intuit for exporting Quicken data. It is 'broken' because the format is ambiguous in many places, non-standard between different releases and applications, and even varies subtly from country to country (in particular, the way dates and amounts are represented), and fails to define important data (such as the currency denomination, or the exchange rates when transferring between accounts marked in different currencies). Importing a QIF file can require significant manual intervention by the user in order to get the data straight. Extensions ---------- TEF -- Time and Expense Format (see below) QFX -- also known as 'Web Connect' -- very similar, and is the 'new' standard for on-line bank statement downloads. (??? or is it just 'ofx in a file' ???) Type of account identifiers ---------------------------- !Type:Bank Bank account !Type:Bill ??? (bill presentment ???) !Type:Cash Cash account !Type:CCard Credit Card account !Type:Invoice ??? (invoice presentment ???) !Type:Invst Investment account !Type:Oth A Asset account !Type:Oth L Liability account !Type:Tax ??? !Account Account list or which account applies to following transactions !Type:Cat Category list !Type:Class Class list !Type:Memorized Memorized transaction list Note that !Account is used both to be a header for account information, and to be a header for a list of transactions. Also note that international versions of Quicken and MS Money often translate the Type: tags into the local language. But not always. Account Information Format -------------------------- The below typically follow an !Account identifier, and provide account data. Letter Definition N Name T Type of account D Description L Credit limit (only for credit card accounts) / Statement balance date $ Statement balance amount ^ End of entry Category Information Format --------------------------- N Category name:subcategory name D Description T Tax related if included, not tax related if omitted I Income category E Expense category (if category type is unspecified, assumes expense type) B Budget amount (optional, only appears in a Budget QIF file) R Tax schedule information ^ End of entry Class Information Format ------------------------ N Class name D Description ^ End of entry Memorized Transaction Format ---------------------------- KC Check transaction KD Deposit transaction KP Payment transaction KI Investment transaction KE Electronic payee transaction T Amount C Cleared status P Payee M Memo A Address L Category or Transfer/Class S Category/class in split E Memo in split $ Dollar amount of split 1 Amortization: First payment date 2 Amortization: Total years for loan 3 Amortization: Number of payments already made 4 Amortization: Number of periods per year 5 Amortization: Interest rate 6 Amortization: Current loan balance 7 Amortization: Original loan amount ^ End of entry Note that the K* entries must be the *last* entries in the transaction. All fields are optional. If this is an amortization record, then all seven amortization fields much be present. Investment transaction format ----------------------------- Letter Definition D Date (optional) N Action Y Security I Price Q Quantity (# of shares or split ratio) C Cleared status P first line text for transfers/reminders M Memo O Commission L Account for transfer (category/class or transfer/class) (For MiscIncX or MiscExpX actions, this will be category/class|transfer/class or |transfer/class) T Amount of transaction U Amount of transaction (higher possible value than T) $ Amount transferred ^ End of entry Non-investment transaction format --------------------------------- Letter Definition D Date T Amount U Transaction amount (higher possible value than T) C Cleared status N Number (check or reference number) P Payee/description M Memo A Address (up to 5 lines; 6th line is an optional message) L Category (category/class or transfer/class) S Category in split (category/class or transfer/class) E Memo in split $ Dollar amount of split % Percentage of split if percentages are used F Reimbursable business expense flag X Small Business extensions ^ End of entry Note that S,E and $ lines are repeated as needed for splits. Time and Expense Format ----------------------- The following QIF extension added by Iambic Software to handle time and expense tracking. This is used in particular by handhelds (Palm and WinCE). TEF is claimed to be a superset of the QIF format. TEF Files begin with the header: #TEF VERSION X.YYY Documented below is version 1.01 # Any line beginning with # is a comment and not parsed B City F Reported H Report # J Attendees K Reimbursable R Receipt U Begin Odometer V End Odometer W Private X Exchange Rate Z User 1 Client 2 Project 3 Activity 4 Expense Type 5 Account 6 Vehicle 7 Currency 8 Task 9 (not used) 0 (not used) @ Billing Code ! Tax Amount % Uses Splits ( SalesTaxRate1 ) SalesTaxRate2 = Flat Fee Amount \ Status1 / Status2 & Status3 < Status4 > Status5 ? Keyword: TIME, EXPENSE, CLIENT, PROJECT, ACTIVITY, TYPE, TASK, VEHICLE, PAYEE, CURRENCY. If absent, entry is assumed EXPENSE type as compatible with QIF * Duration hh:mm:ss + Timer On [ Start time ] End Time { TimerLastStoppedAt } (not used) | Notes When importing type CLIENT, PROJECT, ACTIVITY, TYPE, TASK, VEHICLE, PAYEE, CURRENCY the following are used: N Name C Code R Rate L Link W Private ===================================================================== General Notes: Dates: ----- Dates in US QIF files are usually in the format MM/DD/YY, although four-digit years are not uncommon. Dates sometimes occur without the slash separator, or using other separators in place of the slash, commonly '-' and '.'. US Quicken seems to be using the ' to indicate post-2000 two-digit years (such as 01/01'00 for Jan 1 2000). Some banks appear to be using a completely undifferentiated numeric string formateed YYYYMMDD in downloaded QIF files. European QIF files may have dates in the DD/MM/YY format. Monetary Amounts: ----------------- These may occur in either US or Euro format: 10,000.00 Ten Thousand Dollars 10.000,00 Ten Thousand Francs Within a given QIF file, the usage of US or Euro numeric format appears to be consistent within a particular field but may be different from one field to another. For example, the Share Amount field can be in Euro format but the Split Amount in US. No radix-point is required and no limit on decimal places is evident, so it's possible to see the number "1,000" meaning "1 franc per share" "1,000" meaning "one thousand shares" in the same transaction (!). Category/Transfer/Class line: ----------------------------- The "L" line of most transactions specifies the category, transfer account, and class (if any) of the transaction. Square brackets surrounding the contents mean the transaction is a transfer to the named account. A forward slash separates the category/account from the class. So overall, the format is one of the following: LCategory of transaction L[Transfer account] LCategory of transaction/Class of transaction L[Transfer account]/Class of transaction In stock transactions, if the 'N' field (action) is MiscIncX or MiscExpX, there can be *two* account/class pairs on the L line, with the second guaranteed to be a transfer. I believe they are separated by a '|', like so: D01/01/2000 NMiscExpX T1000.00 Lexpense category/expense class|[Transfer account]/transfer class