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:
At the start of each "account register," one of the following identifiers is used to identify the account "type:"
!Type:Bank
!Type:Invst
!Type:Cash
!Type:Oth A
!Type:CCard
!Type:Oth L
Each transaction item appears on a separate line.
Transactions in all accounts other than Invst (investment) accounts have fields from the following selection:
D
Date
T
Amount
C
Cleared status
N
Number (check or reference)
P
Payee/description
M
Memo
A
Address (up to 5 lines; 6th line is an optional message)
L
Category/class or transfer/class
S
Category in split (category/class or transfer/class)
E
Memo in split
$
Dollar amount of split
^
End of entry
Repeat the S, E, and $ lines as many times as necessary to represent additional items in a split transaction.
Transactions in Invst accounts has fields from the following selection:
D
Date (optional)
N
Action
Y
Security
I
Price
Q
Quantity ( of shares or split ratio)
C
Cleared status
P
1st line text for transfers/reminders
M
Memo
O
Commission
L
For MiscIncX or MiscExpX actions, Category/classtransfer/class or transfer/class
For all other actions, Category/class or transfer/class
$
Amount transferred
End of entry (required)
Each transaction ends with the field/symbol.
 | 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:
It does not guarantee uniqueness of transactions. There is no "transaction key," thus if you import a file twice, you will get the transactions booked twice.
It uses the assumption that a transaction is associated with a "balance sheet" (asset/liability) account and an "income statement" (income/expense) account. (Possibly multiple of the latter.)
In effect, everything looks like a cash expenditure/receipt. Transfers and adjustments must be fiddled into groups of "cash" transactions.
A new and better transaction format should include things such as the following:
Unique ID.
Every transaction needs a permanently unique "transaction identifier." This means that if data is copied from one system to another twice, this can be detected. A sensible scheme would be to construct the unique identifier using:
An identification of the source system.
QSW might be my name for the "Quicken System at Work."
A unique identifier within the source system. 12F26A78 might be the MD5 checksum of the Quicken transaction; one would then assign that as the "transaction identifier."
This would go together to generate a unique key of QSW-12F26A78.
CREATED-IN: must identify uniquely the software system "instance" used to originally generate the transaction, such as payroll@@example.org or test.gl@@example.org.
This field is arguably unnecessary if the source system is identified within the transaction ID.
CREATED-ON: identifies the date on which the transaction was originally created in the source system.
EFFECTIVE-DATE: 1997/01/01 Effective date of the transaction.
Transaction "line items" would include:
ACCOUNT: CASH or ACCOUNT: Salary Income or some such thing. Unlike the "QIF" format, income/expense accounts are not made inherently distinct from asset/liability accounts.
A RECONCILED: payroll@@example.org 1997/01/01 item that could exist for each line itme would indicate that the line item was marked as reconciled on the "payroll" system on the given date.
Note that this means that in order to have all of a transaction marked as reconciled, one would have to do a reconciliation of every account involved. One would not normally do this with income or expense accounts; normally one only reconciles the balance sheet accounts like chequing accounts and credit cards, and not income or expense accounts. Not to say that it's impossible... A large company might well do reconciliations of some expense accounts to ensure that particular kinds of frauds are not being perpetrated...
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 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
PAW/et Peachtree Extension Tool - A proprietary Peachtree-to-MS-Access data conversion tool
QBooks Office - Transactions (Seems fairly useful)