Data Imports from PRODIGY, QUICKEN, SCHWAB 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 record type. 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 portfolio 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 a "translate" file (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, use the Prodigy commands and follow the Prodigy prompts to 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 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,15,15 In reponsed to the import prompt type: C:\PRODIGY\QUOTES.TXT/MUTFUNDS.XLT 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 that the date range for the report spans more than one calendar year (e.g. 12/01/91 to 06/01/92) so as to force Quicken to include the year portion on the 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 ---------- TOTAL 1/22/89 - 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. The Quicken Valuation output 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.XLT 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.XLT 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 report- ing 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**" notation. 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 col 47. 7: Amount. Quicken col 68. 8: Com/Fee. Quicken col 59. 9: Est. Tax. Not avail. in Quicken. 10: X-Rate. Not avail. in Quicken. 11: Price. Quicken col 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. Schwab Account Access Link -------------------------- Charles Schwab & Co. provides a service called "Schwab Link" to facilite computerized trading by professional money managers and financial advisors (contact C. Schwab Financial Advisor Service 1-800-648-6021 for details). Schwab Link enables the user to download Transaction and Position record files which may then be imported into CAPTOOL Global Investor during batch valuations, reducing data input to a minimum. Schwab Link data files are downloaded using the "Account Access Link" (see appendix of Schwab manual). Two comma-delimited files are used for import. Transaction files use the file extension ".TRN" and have the following format: Acct # Date TAC T Symbol Amount Csh/Mg Quantity Commis Cost/u Accr. int Broker -------- ------ --- -- --------- ------------ ------ ----------- -------- ---------- ---------- -------- 15958386,012292,dep,ca,client , 339170.17,cash , , , , , 15958386,012292,buy,cs,npr , 12024.25,cash , 500.000, 148.75, 22.375, ,schwab 15958386,012292,sel,cs,gfy , 22711.50,cash , 1560.000, 298.50, 14.750, ,schwab 15958386,012292,buy,mf,fstux , 94356.52,cash , 9046.271, 637.15, 10.360, ,schwab 14161818,012292,wdl,ca,client , 866.91,margin, , , , , 14161818,012292,int,ca,client , 152.15,margin, , , , , ;No balance in account 14161818,012292,buy,cs,leo , 3325.00,margin, 300.000, 62.50, 10.875, ,schwab 14161818,012292,sel,cs,gfz , 4678.13,margin, 325.000, 75.80, 14.625, ,schwab 14161818,012292,buy,cs,vcd , 84126.25,margin, 4000.000, 626.25, 20.875, ,schwab Where "T"=Type: ca=client acct, cs=common stk, mf=mutual fund Note that these records contain data for more than one account. During import, CAPTOOL uses the account number to select the correct records for each portfolio. These account numbers must be entered in your client group records in order for the import to work (Important!). Position record are contained in "Composite Statement" files with the file extension of ".CMP" which have the following record format: C L T M Symbol S Quantity Description Price/U Valuation - - --------- - ------------- -------------------------- --------- ------------- cs,C,BPL ,L, 1000.0000,"BUCKEYE PARTNERS UTS L P", 26.8750, 26875.00 , ,BPL , , ,"UNIT LTD PARTNERSHIP INT", , cs,C,CMO ,L, 950.0000,"CAPSTEAD MTG CORP ", 31.6250, 30043.75 cs,C,CEQ ,L, 10526.0000,"CENTENNIAL GROUP INC ", .2188, 2303.09 cs,M,CG ,L, 400.0000,"COLUMBIA GAS SYSTEM INC ", 18.5000, 7400.00 cs,M,LENS ,L, 1.0000,"CONCORD CAMERA CORP ", 8.5000, 8.50 cs,M,DQE ,L, 200.0000,"D Q E ", 28.0000, 5600.00 cs,M,LEO ,L, 300.0000,"DREYFUS STRATEGIC MUNS ", 10.8750, 3262.50 cs,C,264901109,L, 45.0000,"DUNDEE BANCP CL A SB VTG", 2.5900, 116.55 , ,264901109, , ,"CLASS A SUB VTG ", , The file SCHWAB.XLT is required to "translate" these files during import: **TRANSACTIONS** "",0 2,3,5,0,0,8,6,9 {TAC translations} buy=BUY sel=SLL dep=DPF wdl=WDF div=DV+ int=IN+ cdt=DPF dbt=WDF MMDDYY=MM/DD/YY ACCTFLD=1 COMMADELIM ******* **PRICES** "",0 0,3,1,0,0,12,12,12 ******* **POSITIONS** "",0 1,3,6,5,7,8 COMMADELIM ******* **MSTRLIST** "",0 3,6,0,1,0,0,0,7 COMMADELIM ******* There are several things to note about this "translate" file. First, it may be used for importing from ".TRN" files into CAPTOOL portfolio Transaction records, Price History records, and Master List records. The "Positions" translate specifications are used to price portfolio "Position" records. Second, all but the "Prices" specification are "Comma Delimited" imports as indicated by the notation "COMMADELIM" in the specification. This means, for example, that a "3" in the import specification string specifies importing from the third field of the import file rather than from the third column position when a fixed field position format is used as for Prodigy or Quicken imports. Third, a date transformation is specified by the "MMDDYY=MM/DD/YY" notation in the trans-action specification. Lastly, the notation "ACCTFLD=1" tells CAPTOOL to exclude from import all transaction whose account number in field #1 does not match the account number of the currently loaded portfolio. You MUST use the same account number in the Captool Client Group records for a portfolio as is used by Schwab for that account, otherwise transactions will not import. Single Portfolio Imports ------------------------ The Schwab ".TRN" files may be separately imported into each portfolio by executing the "Import" command (Alt+I) on CAPTOOL's PFROI transaction screen and responding to the prompt as follows: File: C:\SLINK\CS013192.TRN/SCHWAB where "SLINK" is the directory containing the transaction file CS013192.TRN downloaded using Schwabs Account Access Link. The Schwab ".CMP" file may likewise be separately imported into CAPTOOL master security list (MSTRLIST.STD) by executing the import command (Alt+I) while on the master security screen: File: C:\SLINK\CS013192.CMP/SCHWAB CAPTOOL will import only those records for which there is not already an entry in the master list. The Schwab ".CMP" file may also be used to price an existing position screen by executing the "Alt+Z" command (Alt+I command already taken) while on the position screen. Schwab Batch Imports -------------------- The most efficient way to import Schwab link data is during CAPTOOL batch valuations. This is done by placing the notation "I+" in the "Other Pf Specs" field on the PFROI "Configuration Menu" to indicate that you want to import while you are performing batch valuations (or individual portfolio valuations for that matter). Subsequently when you execute a "Create" command (Alt+C), after you specify a "Close Date" you will be prompted as follows to specify "transaction" and "composite" import files: Transaction Import File: C:\SLINK\CS013192.TRN/SCHWAB Composite Import File: C:\SLINK\CS013192.CMP/SCHWAB If you specify valid existing filenames CAPTOOL will 1) import and sort appropriate transactions as each portfolio is batched, 2) update the master list from the composite import file and 3) price the batch position screen from the composite import file. After reviewing the Batch position records for accuracy, execute the batch update command (Alt+U) to complete the update process. Caution! -------- Users who import from Schwab Link should be alert for a number of problems which could arise and should review their data accordingly as follows: 1) CAPTOOL requires that the Account Number on the CAPTOOL Client Group records match the account numbers used by Schwab. Non-matching account numbers will result in failure to import. 2) Duplicate transactions. CAPTOOL does not check for duplicate transactions during the import process. Thus, if you import the same file more than once, you will get duplicate transactions in your portfolio. Protect against this by removing an import file from its source directory (SLINK) after it has been imported. Also, back up your portfolio files before importing so that it is easy to recover in case you make a mistake. 3) The range of transaction codes used by Schwab and by CAPTOOL do not correlate on a one to one basis. In particular CAPTOOL has a much wider range of TAC codes. After importing you may need to modify some TAC codes to get correct treatment in CAPTOOL. You should also verify that quantities and amounts have the correct signs. It is prudent to print and carefully transaction reports for each portfolio for the applicable period following imports. This can be accomplished from the PFROI reports menu by selecting a transaction report and specifying "*.*" for the portfolio mask so as to print reports for all portfolios. IMPORTING FROM CYBER-SCAN CREATED DTN FILES ------------------------------------------- For those who wish to obtain near real-time quotes via a satellite feed DTN provides a cost-effective alternative. CYBER-SCAN, Inc. (Rt. 4, Box 247, Buffalo, MN 55313, Tele: 612-682-4150) provides software which can dump DTN quote data onto ASCII disk files which can be imported into CAPTOOL as follows: 1) Run Cyber-Scan QUOTE COMMANDER program. 2) Select "Automatic Dump" option: a) Set Time, or hit key twice for immediate dump of data. b) Select "ASCII dump type. Data will be placed in an ASCII file named mo-dy-yr.ASC where "mo-dy-yr" is the current day's date. 3) Exit QUOTE COMMANDER when dump is finished. 4) Run CAPTOOL, enter CAPTOOL "Price History" Records: a) Select "Portfolio Management" on main CAPTOOL menu. b) Select "Auxiliary Records" on PFROI menu. c) Select "Price History" on Auxiliary menu. 5) On CAPTOOL "Price History" screen: a) Execute "Alt+I" (import) command. b) At prompt for filename type in: File: C:\DTN\mo-dy-yr.ASC/CYBERSTK.XLT where "mo-dy-yr" is the current day's date and CYBERSTK.XLT is the "translate" file for stocks. Use CYBERFND.XLT for funds and CYBERBND.XLT for bonds. CYBERSTK.XLT, CYBERFND.XLT & CYBERBND.XLT contents: **PRICES** {stocks} "",0 0,1,0,49,0,11,20,29 **PRICES** {funds} "",0 0,1,0,0,0,11,11,11 **PRICES** {bonds} "",0 0,1,0,0,0,29,29,29 IMPORTING FROM RJT SYSTEMS "QUOTE EXPRESS" FILES ------------------------------------------------ If you have DTN daily quote files captured using "Quote Express" from RJT Systems, Inc. (1049 SW Baseline #200, Hillsboro, OR 97123, Ph:1-800-729-5037) you may import them by creating an "XLT" file (see QEXPRESS.XLT) with contents as follows: **PRICES** "",0 2,1,0,7,3,4,5,6 COMMADELIM YYMMDD=MM/DD/YY You may dump Quote Express data to an importable text file and import that file into CAPTOOL as follows: 1) From the main Quote Express screen, select the "Download" option, using the "Current List", "Defaults Only" sub-options. The price data will be sent to a comma delimited ASCII text file named "OUTPUT". 2) Exit Quote Express and start CAPTOOL. Enter the CAPTOOL "Price History" records (from Time Trends menu or PFROI "Auxiliary" sub-menu). Execute the "Import" command (Alt+I), responding to the file prompt as follows: Import File : C:\QE\OUTPUT/QEXPRESS.XLT IMPORTING FROM WORDEN ASCII PRICE FILES --------------------------------------- Worden Ascii price files appear as follows, with one stock per file: MRK,880609,56.8750,55.7500,56.1250,16808 880610,56.6250,55.5000,55.7500,8391 880613,55.5000,54.6250,55.0000,5595 Note that the file does not include the symbol on each line of data and thus requires conversion to add this item so the file appears as follows: MRK,880609,56.8750,55.7500,56.1250,16808 MRK,880610,56.6250,55.5000,55.7500,8391 MRK,880613,55.5000,54.6250,55.0000,5595 The utility program WORDENX.EXE will perform this function. Once converted, import the resulting modified file into the CAPTOOL Price History records using the WORDEN.XLT translate file: Import File: C:\W_ASCII/WORDEN.XLT where "W_ASCII" is the converted Worden ASCII file. Note: For WORDENX.EXE conversion to work properly, the ticker symbol must appear as the first symbol in the first line of the WORDEN file. If this ticker is missing, add it with your text editor program before performing the conversion. IMPORTING FROM META-STOCK ASCII FILES ------------------------------------- META-STOCK ASCII files are identical to the Worden file described above, except that you must edit the file to add the ticker symbol to the beginning of the first line prior to using WORDENX.EXE to convert the file. Import in the same fashion as in the Worden example above. Other Imports ------------- 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.