-
Notifications
You must be signed in to change notification settings - Fork 109
/
qif-file-format.txt
410 lines (348 loc) · 12.2 KB
/
qif-file-format.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
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 S Asset account (German)
!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
Note that numbers for investment transactions are positive in most
cases. The importation process automatically takes care of negating
the values for Actions that move funds out of an account (for example:
sales, expenses and transfers).
Be aware that GnuCash's file format stores the share quantity and the
total value of the transaction. Prices are not stored.
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 typically occur in either US or European format:
10,000.00 Ten Thousand Dollars (US format)
10.000,00 Ten Thousand Francs (European format)
An apostrophe is also used in some cases:
10'000.00 Ten Thousand Dollars (Quicken 4)
10'000,00 Ten Thousand Francs (unconfirmed)
Within a given QIF file, the usage of a particular 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 European 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 (!).
Investment Actions
------------------
The N line of investment transactions specifies the "action" of the
transaction. Although not a complete list, possible values include
the following:
QIF N Line Notes
============ =====
Aktab Same as ShrsOut.
AktSplit Same as StkSplit.
Aktzu Same as ShrsIn.
Buy Buy shares.
BuyX Buy shares. Used with an L line.
Cash Miscellaneous cash transaction. Used with an L line.
CGMid Mid-term capital gains.
CGMidX Mid-term capital gains. For use with an L line.
CGLong Long-term capital gains.
CGLongX Long-term capital gains. For use with an L line.
CGShort Short-term capital gains.
CGShortX Short-term capital gains. For use with an L line.
ContribX Same as XIn. Used for tax-advantaged accounts.
CvrShrt Buy shares to cover a short sale.
CvrShrtX Buy shares to cover a short sale. Used with an L line.
Div Dividend received.
DivX Dividend received. For use with an L line.
Errinerg Same as Reminder.
Exercise Exercise an option.
ExercisX Exercise an option. For use with an L line.
Expire Mark an option as expired. (Uses D, N, Y & M lines)
Grant Receive a grant of stock options.
Int Same as IntInc.
IntX Same as IntIncX.
IntInc Interest received.
IntIncX Interest received. For use with an L line.
K.gewsp Same as CGShort. (German)
K.gewspX Same as CGShortX. (German)
Kapgew Same as CGLong. Kapitalgewinnsteuer.(German)
KapgewX Same as CGLongX. Kapitalgewinnsteuer. (German)
Kauf Same as Buy. (German)
KaufX Same as BuyX. (German)
MargInt Margin interest paid.
MargIntX Margin interest paid. For use with an L line.
MiscExp Miscellaneous expense.
MiscExpX Miscellaneous expense. For use with an L line.
MiscInc Miscellaneous income.
MiscIncX Miscellaneous income. For use with an L line.
ReinvDiv Reinvested dividend.
ReinvInt Reinvested interest.
ReinvLG Reinvested long-term capital gains.
Reinvkur Same as ReinvLG.
Reinvksp Same as ReinvSh.
ReinvMd Reinvested mid-term capital gains.
ReinvSG Same as ReinvSh.
ReinvSh Reinvested short-term capital gains.
Reinvzin Same as ReinvDiv.
Reminder Reminder. (Uses D, N, C & M lines)
RtrnCap Return of capital.
RtrnCapX Return of capital. For use with an L line.
Sell Sell shares.
SellX Sell shares. For use with an L line.
ShtSell Short sale.
ShrsIn Deposit shares.
ShrsOut Withdraw shares.
StkSplit Stock split.
Verkauf Same as Sell. (German)
VerkaufX Same as SellX. (German)
Vest Mark options as vested. (Uses N, Y, Q, C & M lines)
WithDrwX Same as XOut. Used for tax-advantaged accounts.
XIn Transfer cash from another account.
XOut Transfer cash to another account.
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
Cleared Status line
-------------------
The "C" line of specifies the cleared status. The second character
in the line, if present, may be any of:
* Cleared
c Cleared
X Reconciled
R Reconciled
? Budgeted
! Budgeted
=====================================================================
Sample Files
=====================================================================
Investment Transactions
-----------------------
!Account
NAssets:Investments:Mutual Fund
TInvst
D10/30/2006
Q0.9
T500
PPurchase
NBuyX
L[Assets:Investments:Mutual Fund:Cash]
YFOO
^
D11/28/2006
Q0.897
T100
PSale
NSellX
L[Assets:Investments:Mutual Fund:Cash]
YFOO
^