home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Share Gallery 1
/
share_gal_1.zip
/
share_gal_1
/
BF
/
BF017.ZIP
/
PFROIAUX.EXE
/
IMPORT.TXT
< prev
next >
Wrap
Text File
|
1992-03-11
|
17KB
|
389 lines
Data Imports from PRODIGY, QUICKEN and Others
----------------------------------------------
PFROI can import data from "quote/comma" delimited files into
transaction and price history records. The required data format
is covered in the user manual under the "Import" command (Alt+I)
for each of these record types.
PFROI also can directly import price record text files from
PRODIGY's "Quote Track" and can furthermore import portfolio data
from text files generated by QUICKEN and other investment
management programs. This latter ability enables users to
transfer their investment records from these less comprehensive
programs to PFROI and CAPTOOL. These imports are accomplished by
using an import "translate" file (has the file extension ".XLT")
which tells PFROI where to find each data element in the source
text file. Following import you may need to edit the data to
delete extraneous records or fill in empty data fields.
PRODIGY Price Imports
---------------------
To import prices from the PRODIGY service perform the following
steps:
1) First run PRODIGY and "jump" to the "QUOTE TRACK" service.
There, using the Prodigy commands and following the Prodigy
prompts you specify the ticker symbols of the securities for
which you want quotes. Prodigy stores these selections so that in
the future you will not have to re-specify them. When the quotes
appear on your screen page down to the bottom of the list and
then execute the Prodigy "Print" command. Specify that output
be sent to a disk text file (default name: QUOTES.TXT). The
resulting file on your disk should have contents similar to
the following:
PRODIGY (R) interactive personal service 09/14/1991 12:44 PM
QUOTE TRACKsm
Stock Last Change/ Open/ High Low Volume/
Bid Asked Date
----------------------------------------------------------------------------
ASTA 28 3/4 -2 30 1/2 30 3/4 28 1/4 1267600
BHI 24 1/4 -1 25 1/4 25 1/4 24 469200
BORL 49 1/4 -1 50 1/2 51 1/8 49 306500
CHPS 9 + 1/8 8 7/8 9 8 5/8 53100
CTUS 17 + 3/8 16 5/8 17 16 1/2 284600
You may wish to repeat the quote retrieval process for several
groups of ticker symbols because PRODIGY limits the number of
ticker symbols per group. You may append the quotes to the
existing QUOTES.TXT file. When through retrieving quotes, exit
PRODIGY back to DOS.
2) Now Start PFROI or CAPTOOL. Enter the PFROI Auxillary menu
and select "Price History" records.
3) When the Price History records appear on screen, move the
cursor down to the last record.
4) Execute Alt+I, the "Import" command. You will be prompted
for the name of an "Import" file. Respond as follows:
File : C:\PRODIGY\QUOTES.TXT/PRODIGY<Enter>
This tells PFROI or CAPTOOL to import the file "QUOTES.TXT"
that you just generated with Prodigy. The "/PRODIGY"
specifies that the file "PRODIGY.XLT" should be used to
translate the data from QUOTES.TXT to the internal data
format used by PFROI and CAPTOOL.
Upon hitting the "Enter" key you should see your quotes
appear. Review them for accuracy.
IMPORTANT!: If the quotes do not appear on screen or appear to be
in error this may be due to Prodigy having changed the format of
their "QUOTES.TXT" files. You may have to modify the "translate"
file "PRODIGY.XLT" (using your word processing software) to
accomodate the new format. "PRODIGY.XLT" is a short text file
containing the following information:
**PRICES**
"PRODIGY",7
0,1,0,64/100,32,42,52,9
The first line merely identifies this as a translate file for
price data. The "PRODIGY" in the second line is a "Key Word" for
which PFROI/CAPTOOL searches as it reads "QUOTES.TXT". The "7"
specifies that PFROI/CAPTOOL start importing prices beginning
with the 7th line following the "Key Word".
The third line provides the information used to do the actual
data translation. PFROI/CAPTOOL price records have 8 data
elements as follows:
1 : Date. Prodigy has no separate date for each quote so
the .XLT file has a "0" entry for this field.
2 : Symbol. The Prodigy symbol entry starts in screen
column number 1, so the .XLT file has a "1".
3 : Type field. Prodigy doesnt specify a type, so the .XLT
entry is "0".
4 : Volume. The Prodigy entry for volume begins on the 64th
screen column, so the .XLT entry is "64". Furthermore
because PFROI/CAPTOOL report volume in hundreds the
"64" is followed by "/100" to indicate the volume is to
be divided by 100.
5,6,7,8 : Open, High, Low, Last. the Prodigy screen posi-
tion for these quotes start at columns 32, 42, 52 and
9, hence the corresponding entries in the .XLT file.
HINT: If you obtain Prodigy quotes over a weekend, Prodigy uses
the current day's date, even though the quotes were Friday's
quotes. Some users may wish to edit the date in the QUOTES.TXT
file before importing to reflect the correct quote date.
MUTUAL FUNDS - Prodigy uses a different quote format for mutual
funds requiring a different "XLT" file named "MUTFUNDS" as
follows:
**PRICES**
"PRODIGY",7
,1,,,,,,15
In reponsed to the import prompt type:
C:\PRODIGY\QUOTES.TXT/MUTFUNDS.XLT<Enter>
QUICKEN Portfolio Imports
-------------------------
To transfer your portfolio data from Quicken to PFROI or CAPTOOL
perform the following steps:
1) Run Quicken. Select Quicken's "Reports" option. Select "Investment
Reports" from the resulting sub-menu. Select "Investment
Transactions" from the resulting sub-sub-menu. Edit the Quicken
report start date to correspond to the date of the very first
transaction in your Quicken portfolio. Select the "Don't Subtotal"
option and hit the "Enter" key until the transaction report appears
on the screen. Hit the "F8" key to execute the "Print" command. At
the "Print" menu select option #4 to print to "Disk (ASCII).
Specify 80 column wide output and (IMPORTANT!) make sure the report
date range extends over more than one calendar year (e.g. 12/01/91-
06/01/92) so as to force Quicken to include the year in its
transaction dates. For the filename, type in "QKNTRANS.TXT" to
create a disk file of the same name which appears similar as follows
when viewed with a text editor:
INVESTMENT TRANSACTIONS REPORT
1/22/89 Through 9/22/91
portfolio
9/22/91
Date Action Secur Categ Price Shares Commssn Cash
-------- ------- --------- ------ ---------- ---------- ---------- ----------
BALANCE 1/21/89 0.00
1/22/89 XIn -Cash- [portfo 5,000.00
1/28/89 Buy xxx corp 20.375 196.319 -4,000.00
2/ 3/89 IntInc -Cash- Investment Interest Inc 140.00
2/ 8/89 MiscExp -Cash- -15.35
3/ 8/89 RtrnCap xxx corp 100.25
7/ 8/89 Div xxx corp Dividend 135.75
7/10/89 Buy yyy 10 100 -1,000.00
7/12/89 XIn -Cash- [portfo 4,500.00
7/14/89 CGLong xxx corp Long Term Cap Gain Dist 230.00
7/26/89 CGShort xxx corp Short Term Cap Gain Dist 35.50
8/20/89 StkSpli xxx corp 2:1
8/22/89 BuyX zzz 11 200 -2,200.00
10/ 5/89 ReinvDi yyy 15 2.456 -36.84
9/19/90 ShrsIn Xyz corp 10 100 -1,000.00
9/21/90 ShrsIn abc corp 25 100 -2,500.00
9/22/90 Buy Xyz corp 11 150 -1,650.00
9/22/90 XIn -Cash- [portfo 2,000.00
12/24/90 XIn -Cash- [portfo 1,000.00
----------
TOTAL 1/22/89 - 9/22/91 6,476.15
BALANCE 9/22/91 6,476.15
(Note: The two right-most fields in this example have been
deleted to avoid "wrap-around" on 80 column wide printers.)
2) Escape back to the Quicken Investment Report menu. Now select
"Portfolio Values" from the menu. Select a valuation date and
generate a valuation report. (Suggested dates: Year-end for
every year since portfolio started). Once the valuation
report is on screen, execute the "F8" to print to a text file
as was done for the transactions. Repeat the process for
each valuation date you wish to transfer. Since Quicken
overwrites rather than append to print files you must specify
a new filename for each valuation. Use filenames such as
"QKNVAL1.TXT", "QKNVAL2.TXT", etc. Quicken valuations should
appear as follows:
PORTFOLIO VALUE REPORT
As of 9/24/91
Security Shares Curr Price Cost Basis Gain/Loss Balance
-------------------- ---------- ---------- ---------- ---------- -----------
abc corp 100.00 33 2,500.00 800.00 3,300.00
Xyz corp 200.00 12 1/2 2,150.00 350.00 2,500.00
-Cash- 1,675.00 1 1,675.00 0.00 1,675.00
---------- ---------- -----------
Total Investments 6,325.00 1,150.00 7,475.00
3) Exit Quicken to DOS and start PFROI or CAPTOOL. Go to the
PFROI main menu and execute the "Load" command. Specify an
unused filename so as to create new portfolio file.
4) Enter the "Transactions" records screen. It should be empty
of data since this is a new portfolio. Execute the "Alt+I"
Import command. Respond to the file prompt as follows:
File: C:\QKN\QKNTRANS.TXT/QUICKEN<Enter>
This tells PFROI to import data from the QKNTRANS.TXT file
that you just generated with Quicken. The "/QUICKEN"
specifies that the file "QUICKEN.XLT" should be used to
translate the data from QKNTRANS.TXT to the internal data
format used for PFROI transactions. Upon hitting the "Enter"
key you should see your quotes appear.
Carefully review the transactions for accuracy. You should
note that the ticker symbols were taken from the first seven
characters of the Quicken description field. This is necessary
because Quicken does not display ticker symbols on their
transaction reports. You can easily change these symbols to
their correct value later using the PFROI "Change Symbol"
utility (See #7 below). Make note of the current symbols
that will need changing before proceeding.
Look for for "split" type transactions. You must edit these
to enter the correct values in the "Quantity" field because
Quicken uses a different notation for splits. Note also that
tax lot fields are also blank because Quicken does not specify
tax lot methodology in their transaction report. You will
need to assign tax lot numbers if you are using other than
average cost bases.
5) Escape PFROI transactions and go to the PFROI valuation
records. Execute the Alt+I (Import) command. Respond to the
file prompt as follows:
File: QKNVAL1.TXT/QUICKEN<Enter>
Repeat the process until all the Quicken valuation files you
created have been imported.
Execute the "Alt+M" Multi-Create command and specify the first
valuation date as the start date. This causes PFROI to re-
compute the cost bases and positions for each one of the
valuations. Lookout for any valuation marked with an
asterisk. This indicates a price is missing. In this case,
execute "Alt+R" and key in the missing prices.
6) Execute Alt+F, and select the "saVe" option to save your new
portfolio file.
7) Escape PFROI valuations and select the Utilities option on the
main PFROI menu. Select the "Change Security Symbol" option.
Specify the portfolio file just created as the target
portfolio file. Change the symbols for all the securities
that did not have the correct ticker symbols upon conversion.
IMPORTANT!: If the transactions or valuations do not appear on
screen or appear to be in error this may be due to a change in
the Quicken reporting format. You may have to modify the
"translate" file "QUICKEN.XLT" (using your word processing
software) to accomodate the new format. "QUICKEN.XLT" is a text
file containing the following information:
**TRANSACTIONS**
"TRANSACTIONS",7
1,10,18,0,18,47,68,59,0,0,37
{TAC translations}
SHRSIN=RCV
SHRSOUT=DLV
BUY=BUY
BUYX=BYD
CGLONG=CGD
CGLONGX=CGW
CGSHORT=SGD
CGSHORTX=SGW
DIV=DV+
DIVX=DVW
INTINC=IN+
MARGINT=IN-
MISCEXP=EXP
RTRNCAP=ROC
STKSPLIT=SP+
REINVDIV=DRI
REINVLG=CGR
REINVSH=SGR
SELL=SLL
SELLX=SLW
XIN=DPF
XOUT=WDF
-Cash-=**CASH***
*******
**VALUATIONS**
"VALUE",7
0,1,1,22,32,0,67,43
-Cash-=**CASH***
There are two parts to the QUICKEN.XLT file. The first pertains
to transactions and is identified by the "**TRANSACTIONS**" nota-
tion. The second part pertains to valuations and is identified
by the "**VALUATIONS**" notation. The second line of each
section contains a "Key Word" followed by a comma and a number.
PFROI searches for the key word as it reads the import file and
commences importing the specified number of lines following the
occurence of the key word.
The third line, e.g.: "1,10,18,0,18,47,68,59,0,0,37", provides the
information used to do the actual data translation.
The transaction import specifications have 11 fields as follows:
1: Date. The Quicken date starts in column #1, so there is a
"1" here.
2: TAC. The Quicken transaction code begins in the 10th screen
column so there is a "10" here.
3: Symbol. Quicken does not report a symbol, so the first word
of the "Security" field is used instead. This field starts
at the 18th column so an "18" is used.
4: Lot Number. Quicken doesnt report tax lot numbers so this
field is specified with a zero.
5: Description. This is column #18 in the Quicken report.
6: Quantity. Quicken column 47.
7: Amount. Quicken column 68.
8: Com/Fee. Quicken column 59.
9: Est. Tax. Not avail. on Quicken report.
10: X-Rate. Not avail. on Quicken report.
11: Price. Quicken column 37.
The field translation specifications are followed by "Equates"
which are used to translate Quicken transaction codes to PFROI
TAC codes. Symbol equates may also be included here as is
done here for "-CASH-" to translate symbols.
Other Portfolio Managers
------------------------
Users who wish to import data from other price sources and/or
portfolio managers may construct their own ".XLT" files using
the prior examples for guidances. When importing merely specify
the file name following the "/":
File: datasrc.TXT/myxlate.XLT
where "myxlate.XLT" is the translate file and "datasrc.TXT" is
the text file containing the data to be imported.
Schwab Link
-----------
Professional portfolio managers who use Schwab Link's "Account Access
Link" may import transaction and composite files downloaded from
Schwab into CAPTOOL Global Investor during batch valuations thereby
eliminating most manual data entry. (Note: Schwab limits this service
to investment professionals, annual charges are approx. $1000/year)