|
PureBytes Links
Trading Reference Links
|
I have now found what I believe to be the original message and a follow-up
message:
>Resent-Date: Fri, 15 Aug 1997 19:01:11 -0600
>X-Sender: teosb@xxxxxxxxxxxxxx
>Date: Sat, 16 Aug 1997 08:46:17 +0800
>To: metastock-list@xxxxxxxxxxxxx
>From: Teo Soon Bock <teosb@xxxxxxxxxxxxxx>
>Subject: Portfolio Management using Excel and MS 6.0
>Resent-Message-ID: <"Cx6a_.0.H84.GlFzp"@mail.equis.com>
>Resent-From: metastock-list@xxxxxxxxxxxxx
>X-Mailing-List: <metastock-list@xxxxxxxxxxxxx> archive/latest/3865
>X-Loop: metastock-list@xxxxxxxxxxxxx
>Resent-Sender: metastock-list-request@xxxxxxxxxxxxx
>
>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
>
>
>
>
At 22:57 17/08/97 +1200, you wrote:
>>At 08:46 16/08/97 +0800, Teo Soon Bock wrote:
>>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:-
>>
>Teo, good work
>
>Your system can be further automated by using the VLOOKUP function in your
>main portfolio spreadsheet file to automatically search for and load
updated
>prices as follows:
>
>=VLOOKUP(NOW(),PRICES.xls!A3:B1000,2)
>
>PRICES.xls is the price file linked to Metastock.
>
>A3:B1000 is the range for a particular stock - a named range of the ticker
>code could be used here.
>
>
>
>Steve Latta
>
>
>
> -----Original Message-----
> From: owner-metastock@xxxxxxxxxxxxx
> [mailto:owner-metastock@xxxxxxxxxxxxx]On Behalf Of CMA
> Sent: Saturday, 6 March 1999 04:57
> To: Metastock Digest
> Subject: Excel and MetaStock
>
>
> Last year someone posted the methodology on how to set up an Excel
> spreadsheet to maintain daily closing prices from the MetaStock data
> files. I think it also involved macros (Excel and other macros).
> Would appreciate it if the poster could repost that explanation.
> _________________________________________________________
> DO YOU YAHOO!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
Ian Rawnsley
Auckland
New Zealand
|