|
Posted by Andrew on March 7, 2007, 8:00 am
Please log in for more thread options
Howard Kaikow wrote:
>> There are no "tags" involved.
>>
>> But since there is no single CSV "format", you do have to know
>> what data is contained in what "columns" when you get the data
>> into Excel. In my experience, this is a trivial problem.
>
> Every value in a QIF file is tagged.
> Is there an official spec for the QIF format?
Collected over the years:
QUICKEN INTERCHANGE FORMAT (QIF)
Overview
You can export transactions from a Quicken account register to a
specially formatted text file and import data to a Quicken register
from the same type of text file. This text file MUST be formatted
as a Quicken Interchange Format (QIF) file. For Quicken to
translate data from a text file into the Quicken register as
transactions, the data must be in the QIF format.
Procedure
Basic Procedures:
* Each transaction must end with a * symbol
* Each item in the transaction must appear on a separate line
* When Quicken exports an account register, it adds a line at the
top of the file that identifies the type of account. Here are
the header lines Quicken adds to the exported files:
!Type:Bank
!Type:Cash
!Type:CCard
!Type:Invst
!Type:Oth A
!Type:Oth L
* You can add a line to a file to be imported into Quicken account
to force Quicken to import all transfers, regardless of whether
Ignore Transfers is selected when the file is imported. Use a
test editor or word processor to put the following line right
after the header line at the top of the file:
!Option:AllXfr
Items for non-investment accounts
Each item in a bank, cash, credit card, other liability, or other
asset account must begin with a letter that indicates the field in
the Quicken register. The non-split items can be in any sequence:
D Date
T Amount
C Cleared status
N Number(check or reference number)
P Payee
A Address (up to five lines; the sixth line is an optional
message)
L Category (Category/Subcategory/Transfer/Class
S Category in split (Category/Transfer/Class)
E Memo in split
$ Dollar amount of split
^ End of the entry
Items for investment accounts
D Date
N Action
Y Security
I Price
Q Quantity (Number of shares or split ratio)
C Cleared Status
P. Text in the first line for transfers and reminders
M Memo
O Commission
L Account for the transfer
$ Amount transferred
^ End of the entry
Repeat the S, E, and $ lines as many times as needed for additional
items in a split. If an item is omitted from the transaction in the
QIF file, Quicken treats it as a blank item.
Examples of QIF files
Transaction Item Comment (not in file)
!Type:Bank Header line
D7/8/93 Date
T-1000.0 Transaction amount
CX Status in Cleared column
N255 Check Number
PFranks Plumbing Payee
AFranks Plumbing Address (first line)
A1234 Main St. Address (second line)
ASan Jose, CA 95123 Address (third line)
LHome Maint Category/Subcategory/Transfer/Class
^ End of the transaction
D7/8/93 Date
T-75.46 Transaction amount
CX Status in Cleared column
N256 Check Number
PWalts Drug Payee
LSupplies First Category/Subcategory/Class in
the split
EOffices Supplies First memo in the split
$-51.00 First amount in the split
SMedicine Second Category/Subcategory/Class in the
split
EPrescription Drugs Second memo in the split
$-24.46 Second amount in the split
^ End of the transaction
Transaction Item Comment (not in file)
!Type:Bank Header line
D8/25/93 Date
NShrsIn Action (optional)
Yibm4 Security
I11.260 Price
Q88.81 Quantity
CX Cleared Status
T1,000.00 Amount
MOpening Balance Memo
^ End of the transaction
D8/25/93 Date
NBuyX Action
Yibm4 Security
I11.030 Price
Q9.066 Quantity
T100.00 Amount
MEst. price as of 8/25/93
Memo
L[CHECKING] Account for transfer
$100.00 Amount transferred
^ End of the transaction
At Intuit, we highly recommend that you backup your Quicken data
regularly to protect against loss due to unexpected power failures,
diskette damage or other mishaps. If you need further assistance,
you may contact Intuit Technical Support at (415) 858-6050, Monday
through Friday between 5 am and 5 pm Pacific time.
--- Another entry of this info ---
Export headers for QIF files
Export headers in QIF files divide separate groups of items such
as accounts or transactions. They can also signify options. Export
headers follow the general format:
!<Header name>:<Export type>
Exportable accounts
The table below lists the types of accounts that can be exported
and their export headers:
Export Header Type of account
!Type:Bank Bank account
!Type:Cash Cash account
!Type:CCard Credit card account
!Type:Invst Investment account
!Type:Oth A Asset account
!Type:Oth L Liability account
!Type:Invoice Invoice account (business subtype of Oth A)
!Type:Tax Tax account (business subtype of Oth L)
!Type:Bill Bill account (business subtype of Oth L)
An account header is followed by transaction records if there are
any within the selected date range.
Exportable lists
The table below lists the types of lists that can be exported and
their export headers:
Export Header Type of list
!Type:Class Class list
!Type:Cat Category list
!Type:Memorized Memorized transactions list
!Type:Security Securities list
!Type:Prices Security Prices list
!Type:Budget Budgets list
!Type:Invitem Invoice items list
!Type:Template Business templates list
A list header is followed by records of the selected list type.
Options headers
Below is the option you can use, and its action:
Export Header Meaning
!Option:AllXfr Forces transfers to be imported, even if Ignore
Transfers is set.
Account headers
Export Header Meaning
!Option:AutoSwitch Start of the Accounts list
!Account Beginning of accounts list records
!Clear:AutoSwitch End of the Accounts list
!Account is followed by either a list of account records for the
Accounts list or a single account record for the selected account
transactions.
Copyright (c) 1999 Intuit, Inc.
Identifiers for non-investment accounts
Use these letters to identify specific items in a non-investment
430 No such article
Howard Kaikow wrote:
>> There are no "tags" involved.
>>
>> But since there is no single CSV "format", you do have to know
>> what data is contained in what "columns" when you get the data
>> into Excel. In my experience, this is a trivial problem.
>
> Every value in a QIF file is tagged.
> Is there an official spec for the QIF format?
Collected over the years:
QUICKEN INTERCHANGE FORMAT (QIF)
Overview
You can export transactions from a Quicken account register to a
specially formatted text file and import data to a Quicken register
from the same type of text file. This text file MUST be formatted
as a Quicken Interchange Format (QIF) file. For Quicken to
translate data from a text file into the Quicken register as
transactions, the data must be in the QIF format.
Procedure
Basic Procedures:
* Each transaction must end with a * symbol
* Each item in the transaction must appear on a separate line
* When Quicken exports an account register, it adds a line at the
top of the file that identifies the type of account. Here are
the header lines Quicken adds to the exported files:
!Type:Bank
!Type:Cash
!Type:CCard
!Type:Invst
!Type:Oth A
!Type:Oth L
* You can add a line to a file to be imported into Quicken account
to force Quicken to import all transfers, regardless of whether
Ignore Transfers is selected when the file is imported. Use a
test editor or word processor to put the following line right
after the header line at the top of the file:
!Option:AllXfr
Items for non-investment accounts
Each item in a bank, cash, credit card, other liability, or other
asset account must begin with a letter that indicates the field in
the Quicken register. The non-split items can be in any sequence:
D Date
T Amount
C Cleared status
N Number(check or reference number)
P Payee
A Address (up to five lines; the sixth line is an optional
message)
L Category (Category/Subcategory/Transfer/Class
S Category in split (Category/Transfer/Class)
E Memo in split
$ Dollar amount of split
^ End of the entry
Items for investment accounts
D Date
N Action
Y Security
I Price
Q Quantity (Number of shares or split ratio)
C Cleared Status
P. Text in the first line for transfers and reminders
M Memo
O Commission
L Account for the transfer
$ Amount transferred
^ End of the entry
Repeat the S, E, and $ lines as many times as needed for additional
items in a split. If an item is omitted from the transaction in the
QIF file, Quicken treats it as a blank item.
Examples of QIF files
Transaction Item Comment (not in file)
!Type:Bank Header line
D7/8/93 Date
T-1000.0 Transaction amount
CX Status in Cleared column
N255 Check Number
PFranks Plumbing Payee
AFranks Plumbing Address (first line)
A1234 Main St. Address (second line)
ASan Jose, CA 95123 Address (third line)
LHome Maint Category/Subcategory/Transfer/Class
^ End of the transaction
D7/8/93 Date
T-75.46 Transaction amount
CX Status in Cleared column
N256 Check Number
PWalts Drug Payee
LSupplies First Category/Subcategory/Class in
the split
EOffices Supplies First memo in the split
$-51.00 First amount in the split
SMedicine Second Category/Subcategory/Class in the
split
EPrescription Drugs Second memo in the split
$-24.46 Second amount in the split
^ End of the transaction
Transaction Item Comment (not in file)
!Type:Bank Header line
D8/25/93 Date
NShrsIn Action (optional)
Yibm4 Security
I11.260 Price
Q88.81 Quantity
CX Cleared Status
T1,000.00 Amount
MOpening Balance Memo
^ End of the transaction
D8/25/93 Date
NBuyX Action
Yibm4 Security
I11.030 Price
Q9.066 Quantity
T100.00 Amount
MEst. price as of 8/25/93
Memo
L[CHECKING] Account for transfer
$100.00 Amount transferred
^ End of the transaction
At Intuit, we highly recommend that you backup your Quicken data
regularly to protect against loss due to unexpected power failures,
diskette damage or other mishaps. If you need further assistance,
you may contact Intuit Technical Support at (415) 858-6050, Monday
through Friday between 5 am and 5 pm Pacific time.
--- Another entry of this info ---
Export headers for QIF files
Export headers in QIF files divide separate groups of items such
as accounts or transactions. They can also signify options. Export
headers follow the general format:
!<Header name>:<Export type>
Exportable accounts
The table below lists the types of accounts that can be exported
and their export headers:
Export Header Type of account
!Type:Bank Bank account
!Type:Cash Cash account
!Type:CCard Credit card account
!Type:Invst Investment account
!Type:Oth A Asset account
!Type:Oth L Liability account
!Type:Invoice Invoice account (business subtype of Oth A)
!Type:Tax Tax account (business subtype of Oth L)
!Type:Bill Bill account (business subtype of Oth L)
An account header is followed by transaction records if there are
any within the selected date range.
Exportable lists
The table below lists the types of lists that can be exported and
their export headers:
Export Header Type of list
!Type:Class Class list
!Type:Cat Category list
!Type:Memorized Memorized transactions list
!Type:Security Securities list
!Type:Prices Security Prices list
!Type:Budget Budgets list
!Type:Invitem Invoice items list
!Type:Template Business templates list
A list header is followed by records of the selected list type.
Options headers
Below is the option you can use, and its action:
Export Header Meaning
!Option:AllXfr Forces transfers to be imported, even if Ignore
Transfers is set.
Account headers
Export Header Meaning
!Option:AutoSwitch Start of the Accounts list
!Account Beginning of accounts list records
!Clear:AutoSwitch End of the Accounts list
!Account is followed by either a list of account records for the
Accounts list or a single account record for the selected account
transactions.
Copyright (c) 1999 Intuit, Inc.
Identifiers for non-investment accounts
Use these letters to identify specific items in a non-investment
account transaction. Each line in the transaction must begin with
one of these letters:
Letter What it means
D Date
T Amount of transaction
U Amount of transaction (higher possible value than T)
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
% Percentage of split if percentages are used
F Reimbursable business expense flag
^ End of entry
Repeat the S, E, % and $ lines as many times as necessary for
additional items in a split. If an item is omitted from the
transaction in the QIF file, Quicken treats it as a blank item.
Copyright (c) 1999 Intuit, Inc.
Identifiers for investment accounts
Use these letters to identify specific items in an investment
account transaction. Each line in the transaction must begin with
one of these letters:
Letter What it means
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/class followed by
|transfer/class of the transaction
For MiscInc or MiscExp actions:Category/class of the transaction
For all other actions:Transfer/class of the transactions
T Amount of transaction
U Amount of transaction (higher possible value than T)
$ Amount transferred
^ End of entry
If an item is omitted from the transaction in the QIF file,
Quicken treats it as a blank item.
Copyright (c) 1999 Intuit, Inc.
Identifiers for memorized transaction items
Use these letters to identify specific items in a memorized
transaction. Each line in the transaction must begin with one of
these letters or combination of letters:
Memorized transaction identifiers
The table below lists the identifiers for Memorized Transaction
items and what they mean:
Letter What it means
K Transaction type
KI Memorized investment transaction
KE Memorized regular electronic payment transaction
KC Memorized regular write checks transaction
KP Memorized regular payment transaction
KD Memorized regular deposit transaction
Q Quantity (number of new shares for a split)
R Quantity (number of old shares for a split)
You can also use the identifiers for investment and non-investment
transactions, as necessary. Electronic payments, write checks,
payments, and deposits are regular transaction types.
Memorized loan payment also supports the following identifiers:
Letter What it means
1 First payment date
2 Total years of the loan
3 Number of payments made
4 Payment periods per year
5 Loan rate
6 Current balance of the loan
7 Original balance of the loan
Copyright (c) 1999 Intuit, Inc.
Identifiers for business transaction items (Home & Business)
Use these letters to identify specific business-related items in a
non-investment account transaction. Each line in the transaction
must begin with one of these letter combinations:
Letter What it means
XI Invoice type
XE Payment due date
XU Number of payments
XD Date for a payment
XY Payment amount
XC Sales tax Category
XR Sales tax rate
XT Sales Tax amount
XP PO number
XA Shipping/Vendor address (up to 5 lines)
XM Customer message
XK Default split class
XN Name of invoice item
X$ Price per item of a split
X# Number of items in a split
XS Split item description
XFT Taxable split item flag
^ End of entry
Repeat the XS, XK, X Ft, X# and X$ lines as many times as
necessary for additional items in a split. If an item is omitted
from the transaction in the QIF file, Quicken treats it as a blank
item.
Copyright (c) 1999 Intuit, Inc.
--
-------------------------------------------------------------
Regards -
- Andrew
|
|
Posted by Howard Kaikow on March 7, 2007, 2:43 pm
Please log in for more thread options
Thanx.
That may describe the needed tags.
|
|
Posted by Howard Kaikow on March 8, 2007, 12:49 am
Please log in for more thread options >
> Thanx.
>
> That may describe the needed tags.
Alas, tho the Quicken menu provides for Import of QIF, this is not permitte
dfor QIF files.
Will Investment accounts import OFX/QFX files.
|
|
Posted by John Pollard on March 7, 2007, 9:06 am
Please log in for more thread options Howard Kaikow wrote:
>> There are no "tags" involved.
>>
>> But since there is no single CSV "format", you do have
>> to know what data is contained in what "columns" when
>> you get the data into Excel. In my experience, this is
>> a trivial problem.
> Every value in a QIF file is tagged.
It's not just that technicially there are no "tags" in a QIF
file - there are "field identifiers", which I believe long
predate the term "tag".
It's that those field identifiers are totally invisible to the
"xl2qif" user; not only do you not need to know what identifer
is used for what field; it would not help you if you did know,
because "xl2qif" makes only the Quicken "field names" visible
... not the QIF file "field identifiers".
"Field identifiers" are not involved in an "xl2qif" conversion
(from the user's viewpoint); it's all handled behind the scenes.
All the user has to know is what basic data is contained in the
non-qif file columns in Excel: which column has the "transaction
date", which column has the "payee", which column has the
"amount" ... and so forth. You tell "xl2qif" which column
contains which Quicken field.
> Is there an official spec for the QIF format?
I guess you could say it's "official"; in any event, it's been
posted in this group several times.
--
John Pollard
First initial underscore Last name at mchsi dot com
Please reply to newsgroup
|
| Similar Threads | Posted | | Converting 2007 file to 2008 | October 5, 2007, 8:00 pm |
| After converting to Quicken 2008, all I can say is NEVER AGAIN! | May 12, 2008, 3:22 pm |
| Considering switch to Mac--any advice on converting PC to Apple with Quicken | November 17, 2007, 9:49 am |
| Quicken and Fidelity accounts | April 14, 2007, 1:10 am |
| Quicken Downloads From ScotTrade or Fidelity | May 17, 2006, 3:26 pm |
| Quicken 2006 with Fidelity as Core Account | September 2, 2006, 10:26 am |
| Fidelity Money Market not showing up in Quicken | January 4, 2007, 3:00 am |
| Converting IRA to Roth IRA | January 2, 2007, 5:47 pm |
| Converting Vanguard Shares | May 10, 2008, 9:11 am |
| Re: Converting from Citi to Chase? | July 11, 2008, 6:44 pm |
|
|
Yahoo! 
Google 
Windows Live 
del.icio.us 
digg 
Netscape 
XML Sitemap