Christopher B. Browne's Home Page
cbbrowne@acm.org

12. Data Formats

12.1. Sample QIF file

Here's a sample QIF (Quicken Interchange Format) file for reference purposes... (Parenthesized Text) at the ends of lines represents a running commentary...

!Type:Bank (Header line) D6/12/95 (Date of 1st transaction) T-1,000.00 (Amount of 1st transaction) N***** (Check number) PFranks Plumbing (Payee) AFranks Plumbing (Address 1st line) A2567 Fresno Street (Address 2nd line) ASanta Barbara, CA 90111 Address (3rd line) LHome Maint (Category/Transfer/Class) ^ (End of 1st transaction) D6/15/95 (Date 2nd transaction) T-75.46 (Amount 2nd transaction) CX (Status in Cleared column) N256 (Number) PWalts Drugs (Payee) LSupplies (Category/class) SSupplies (First category in split) EOffice supplies (First memo in split) $-36.00 (First amount in split) SGarden (Second memo in split) $-39.46 (Second amount in split) ^ (Ends 2nd transaction)

Further details on the structure:

Warning

The complaint against Intuit I described here is now no longer a fair one; they have, in the Quicken 99 release, included a fuller set of documentation of fields recently added to QIF files. At some point I should more fully describe these additional "sections." Real Soon Now.

The documentation that Intuit provides for QIF files is incomplete, as it excludes documentation on the following additional "sections" that one may encounter in a QIF file.

12.1.1. Category List

This looks like the following:

!Type:Cat NBonus DBonus Income T R7360 I ^ NDiv Income DDividend Income T R4576 I ^ NGift Received DGift Received I ^ NInterest Inc DInterest Income T R4592 I ^ NInvest Inc DInvestment Income T I ^ NMusic I ^ NOther Inc DOther Income T R4112 I ^ NSalary DSalary Income T R7360 I ^ N_DivInc DDividend T R4576 I ^ N_DivIncTaxFree DTax-Free Dividend T I ^ N_IntInc DInvestment Interest Inc T R4592 I ^ N_IntIncTaxFree DTax-Free Inv Interest Inc T I ^ N_LT CapGnDst DLong Term Cap Gain Dist T R7808 I ^ N_RlzdGain DRealized Gain/Loss T I ^ N_ST CapGnDst DShort Term Cap Gain Dist T R4576 I ^ N_UnrlzdGain DUnrealized Gain/Loss I ^ NAA Credit Union E ^ NACM MC E ^ NAMEX E ^ NATM E ^ NAuto DAutomobile Expenses E ^

It appears that these fields have the following meanings:

  • N

    Category name

  • D

    Verbose Category name

  • T

    Is this category relevant to taxes?

  • I

    Income account?

  • E

    Expense account?

  • R

    Appears to be an indicator of a "Tax Account," or something of the sort.

12.1.2. Account List

This looks like the following:

!Option:AutoSwitch !Account NAA Federal Credit Union TBank ^ NChecking TBank ^ N1st USA TCCard ^ NAAdvantage VISA TCCard ^ NMedical FlexSpend TOth A ^ NSabre Group 401(k) TPort ^

This appears to involve two field types:

  • N

    Account name

  • T

    Account type

12.1.3. Memorized Transactions

This looks like the following:

!Type:Memorized KP T-63.90 PLinux Journal LComputing ^

Which looks like a similar form to ordinary transactions; there appears to be a new field, "K" which either has value KP , used when transactions are payments, and KD , used when transactions are deposits.

See also other sites with relevant documentation:

12.2. Open Accounting Interchange Format

The QIF format has some downsides, including:

A new and better transaction format should include things such as the following:

The prefixes are here being spelled out in fairly long form; this arguably reflects a degree of inefficiency. I don't care. Assuming 10 bytes wasted per field, and ten fields per transaction, then in order to waste 1MB of disk space, one has to have 10,000 transactions, which is rather a lot of data. If the information is dropped into a database that uses any sort of fixed field/block sizes, any wastage in this regard will rapidly become unnoticeable.

Moreover, if interchange files are compressed for transmission, most of the inefficiencies will disappear anyways.

Note

Note that the Tag Types Utilities can be used to manipulate data files in this very format.

The author of the utilities has created a financial transaction type that seems to bear marked resemblance to what I have described above.

12.3. Other Financial Software Data Conversion Tools

Google

If this was useful, let others know by an Affero rating

Contact me at cbbrowne@acm.org