|
PureBytes Links
Trading Reference Links
|
After getting some tips and advice from the Metastock mailing list, I have managed to use Excel to link up to the Metastock database for portfolio management. The steps are detailed below:-
PORTFOLIO MANAGEMENT USING MICROSOFT EXCEL, LINKED TO METASTOCK DATA BASE
by Mr Teo Soon Bock email: teosb@xxxxxxxxxxxxxx
1. Action to be taken on the selected counters in MetaStock 6.0
(a) Open MetaStock 6.0 and click on Indicator Builder;
(b) Create a New indicator named CLOSE, and under Formula, just type the word CLOSE. Click on OK to save it. The creation of this CLOSE indicator needs to be done once only, and can be applied to all counters.
(c) Open the chart for your selected portfolio counter, eg DBS LAND.
(d) Drag and drop the CLOSE indicator from your QuickList on to the chart.
(e) As this CLOSE indicator is not used for normal chart analysis, you can hide it by making it the same colour as the background (right-click on the CLOSE indicator, select CLOSE properties, and under Color, choose white if your chart background is also white).
(f) Repeat (c) to (e) for other selected counters for your portfolio. You may want to test with a few counters first.
2. How to link your Excel spreadsheets to the selected counters
(a) Open Microsoft Excel, and click on New workbook. In cell A1, type in the name of the counter selected for your portfolio, eg DBS LAND.
(b) Switch to MetaStock 6.0,and select the chart for DBS LAND. Click on the CLOSE indicator. (If your chart has many indicators drawn on it, you can use the TAB key to help you select the CLOSE indicator.) Once the CLOSE indicator is selected, click on Edit, followed by Copy.
(c) Switch back to Excel, and click on cell A2, followed by Edit, Paste Special, Paste Link as Csv, and OK. You will get a 2-column table with Date and CLOSE as the header. The dates will be imported as raw numbers, and can be formatted appropriately using the Format, Cells, Date command. The maximum number of records is 1,000 in Excel for Win95; the minimum number of records will depend on the Load Options (File, Open, Options) dialog box in MetaStock. You can now save the file using the counter name, eg DBSLAND.xls
(d) Repeat steps (a) to (c) for the other selected counters in your portfolio.
(e) The next step is to build up your main portfolio spreadsheet file, and link it to the individual Excel files for your portfolio. The rows in this spreadsheet will be the counter names, while the columns could have headings like Date Bought, No. of Shares, Price Bought, Total Cost; Updated Date and Price; Profit/Loss; Date Sold, No. of Shares, Price Sold, etc.
(f) The figures for the Updated Date and Price columns can be linked from the individual Excel files for your portfolio by highlighting the latest date and price in the individual Excel file for your portfolio, and clicking on Edit, Copy, then switching to your main portfolio spreadsheet and in the appropriate cell for that counter, click on Edit, Paste Special, Paste Link. This should be repeated for all the counters in your portfolio. You can save your main portfolio spreadsheet with an appropriate name, eg PORTFOLIO.xls
3. How to keep your portfolio files together so that all the individual portfolios can be updated at the same time
(a) This is desirable so that when you open just one Excel file, your entire portfolio can be updated at the same time.
(b) Open all the Excel files for your portfolio, with your main portfolio spreadsheet as the most recent file to be opened.
(c) Click on File, Save Workspace, and save your file in the XLStart subdirectory of your Excel program, eg C:\MSOffice\Excel\XLStart\port1.xlw
(d) In this way, whenever you start your Excel program, your portfolio workspace file will be started automatically, and when it prompts you whether to re-establish links, just click on Yes, and your portfolio will be updated.
Note: As stated in para 2(c), the maximum number of records in each of your Excel portfolio file may be 1,000 (about 4 years). However, for new IPOs, the number of records that is copied over will probably be less than 1,000. Nevertheless, it is important to block off space for 1,000 records so as to allow for automatic linkage as more records are added. The extra records will be marked with #N/A (not available). It will also be necessary to copy and paste link the latest prices from these files into your main portfolio spreadsheet file.
/tsb:15 Aug 97
|