[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Excel and MetaStock



PureBytes Links

Trading Reference Links

<x-html><!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML><HEAD>
<META content=text/html;charset=Windows-1252 http-equiv=Content-Type><!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<STYLE></STYLE>

<META content='"MSHTML 5.00.0910.1309"' name=GENERATOR></HEAD>
<BODY bgColor=#ffffff>
<DIV>Repost of old mail (in HTML format, can use a browser to read 
it).<BR><BR>Regards,<BR>Ton Maas<BR><A 
href="mailto:ms-irb@xxxxxx";>ms-irb@xxxxxx</A><BR><BR>========================================<BR><BR><BR></DIV>
<DIV>
<DIV><FONT color=#000000 size=2>Yes!</FONT></DIV>
<DIV><FONT color=#000000 size=2></FONT><FONT size=2>Below is my reply to a 
simular question(send on a couple of days ago).</FONT></DIV>
<DIV><FONT size=2>Thanks for yours and Harley's input into this 
Excel-Portfolio-subject.</FONT></DIV>
<DIV><FONT size=2></FONT><FONT color=#000000 size=2>Regards,</FONT></DIV>
<DIV><FONT color=#000000 size=2></FONT><FONT size=2>Ton Maas</FONT></DIV>
<DIV><FONT size=2><A 
href="mailto:Ms-IRB@xxxxxxxxx";>Ms-IRB@xxxxxxxxx</A></FONT></DIV>
<DIV><FONT size=2></FONT>&nbsp;</DIV>
<DIV><FONT 
size=2>----------------------------------begin----------------------------------------------</FONT></DIV>
<DIV><FONT color=#000000 size=2></FONT>&nbsp;</DIV>
<DIV><FONT size=2>p 36-dl manual-create folders<BR>p 69-dl manual-traverse 
folders<BR><BR>In win95-explorer, just make as many folders as you like, 
then<BR>go to the dl, and &quot;copy&quot; the securities you need from one 
folder<BR>to another<BR><BR>You can keep multiple copies too, and they too will 
be updated,<BR>for this make sure in dl:<BR>-to press 
&quot;conversion&quot;-button to go to &quot;convert&quot;-dialog<BR>-before 
actual conversion, to go to its &quot;options&quot;-dialog<BR>-on the 
&quot;destination&quot; tab - traverse folders must be marked/applied<BR>-click 
ok and ok again on the &quot;conversion options&quot; tab to get 
you<BR>&nbsp;&nbsp; back to the &quot;convert-dialog&quot;:&nbsp;&nbsp; 
<BR>General: folder to convert to can be just the &quot;parent&quot; folder, so 
as the<BR>destination-folder: no need to type in the kiddies names(the sub+sub 
subs)</FONT></DIV>
<DIV><FONT size=2></FONT>&nbsp;</DIV>
<DIV><FONT 
size=2>----------------------------------------end--------------------------------------------------- 
<BR></FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=Arial size=2><B>-----Oorspronkelijk bericht-----</B><BR><B>Van: 
</B>Teo Soon Bock &lt;<A 
href="mailto:teosb@xxxxxxxxxxxxxx";>teosb@xxxxxxxxxxxxxx</A>&gt;<BR><B>Aan: 
</B>metastock@xxxxxxxxxxxxx &lt;<A 
href="mailto:metastock@xxxxxxxxxxxxx";>metastock@xxxxxxxxxxxxx</A>&gt;<BR><B>Datum: 
</B>vrijdag 8 mei 1998 3:41<BR><B>Onderwerp: </B>Re: Portfolio Manager - Excel 
(Step-by-Step)<BR><BR></DIV></FONT>To Harley: If you take the securities in your 
portfolio and copy them to a new sub-directory, would this sub-directory be 
automatically updated whenever you download fresh data into your main data 
directory?<BR><BR><BR>I wrote the following last year based on Singapore stocks, 
but it could certainly be used for other markets as well:-<BR><BR>&lt;&lt; QUOTE 
&gt;&gt;<BR>PORTFOLIO MANAGEMENT USING MICROSOFT EXCEL, LINKED<BR>TO METASTOCK 
DATA BASE<BR><BR>For those of you who are using MetaStock 6.5 charting software 
to do technical analysis of stocks and shares, it is possible to link the 
MetaStock data base to Microsoft Excel for Windows 95 in order to have automatic 
updating of your portfolio whenever the data base is updated by downloading the 
latest data from your data vendor. <BR><BR>MetaStock 6.5 is able to create links 
with other Windows 95 programs, eg Microsoft Excel, by using OLE (object linking 
and embedding). The portfolio management spreadsheet uses OLE for automatic 
updating of the profit/loss columns from the MetaStock charts.<BR><BR>The 
following are the detailed steps for linking your selected MetaStock charts to 
your portfolio management spreadsheet in Microsoft Excel for Windows 
95:-<BR><BR>1. Action to be taken on the selected counters in <BR>MetaStock 
6.5<BR><BR>(a) Open MetaStock 6.5 and click on Indicator Builder;<BR><BR>(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.<BR><BR>(c) Open the chart for 
your selected portfolio counter, eg DBS LAND.<BR><BR>(d) Drag and drop the CLOSE 
indicator from your QuickList on to the chart.<BR><BR>(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).<BR><BR>(f) Repeat (c) to (e) for other selected counters for your 
portfolio. You may want to test with a few counters first.<BR><BR><BR>2. How to 
link your Excel spreadsheets to the selected counters<BR><BR>(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.<BR><BR>(b) Switch to MetaStock 6.5,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.<BR><BR>(c) Switch back to Excel, and click on cell A2, followed by 
Edit, Paste Special, Paste Link as Csv, and OK. (Csv stands for &quot;comma 
separated values.&quot;) 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<BR><BR>As stated above, the 
maximum number of records in each of your Excel portfolio file may be 1,000 
records (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).<BR><BR>(d) Repeat steps (a) to (c) for the other selected counters 
in your portfolio.<BR><BR>(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. You can 
save your main portfolio spreadsheet with an appropriate name, eg 
PORTFOLIO.xls<BR><BR>(f) The figures for the Updated Date and Price columns in 
your main portfolio spreadsheet can be updated automatically from the individual 
Excel files for the portfolio by using the VLOOKUP function in Excel. For 
example, for the Updated Date cell for DBS LAND, just type the 
following:-<BR><BR>=VLOOKUP(NOW(),[DBSLAND.xls]Sheet1!A2:B1002,1)<BR><BR>and for 
the updated Price cell for DBS LAND, type the 
following:-<BR><BR>=VLOOKUP(NOW(),[DBSLAND.xls]Sheet1!A2:B1002,2)<BR><BR><BR>The 
above should be repeated for all the counters in your portfolio, with the file 
name for the counter amended accordingly. It will even work for new IPOs where 
the number of records is less than 1,000 records.<BR><BR><BR>3. How to keep your 
portfolio files together so that all the individual portfolios can be updated at 
the same time<BR><BR>(a) This is desirable so that when you open just one Excel 
file, your entire portfolio can be updated at the same time.<BR><BR>(b) Open all 
the Excel files for your portfolio, with your main portfolio spreadsheet as the 
most recent file to be opened.<BR><BR>(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<BR><BR>(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.<BR><BR><BR>. . . . .<BR>&lt;&lt; UNQUOTE 
&gt;&gt;<BR><BR>At 10:10 PM 5/7/98 +0200, skystar wrote: 
<BR>&gt;&gt;&gt;&gt;<BR>
<BLOCKQUOTE><?smaller>Thank you Harley for your response to my question. Being 
  a novice I need more info. Please run me step by step once I have opened the 
  single bar in a new sub directory. Thank you.<BR>I see Roland is also looking 
  for help on this subject.<BR>Regards - Charles<?/smaller> <BR>
  <BLOCKQUOTE><B><?fontfamily><?param Arial><?smaller>-----Original 
    Message-----<BR>From:<?/smaller><?/fontfamily> </B><?fontfamily><?param Arial><?smaller>Harley Meyer &lt;&lt;<A 
    href="mailto:meyer@xxxxxxxxxxx";>mailto:meyer@xxxxxxxxxxx</A>&gt;<A 
    href="mailto:meyer@xxxxxxxxxxx";>meyer@xxxxxxxxxxx</A>&gt;<BR><B>To: 
    </B>&lt;<A 
    href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx";>mailto:metastock@xxxxxxxxxxxxxxxxxxxx</A>&gt;<A 
    href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx";>metastock@xxxxxxxxxxxxxxxxxxxx</A> 
    &lt;&lt;<A 
    href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx";>mailto:metastock@xxxxxxxxxxxxxxxxxxxx</A>&gt;<A 
    href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx";>metastock@xxxxxxxxxxxxxxxxxxxx</A>&gt;<BR><B>Date: 
    </B>04 May 1998 04:25<BR><B>Subject: </B>Re: Portfolio Manager - 
    Excel<BR><BR><?/smaller><?/fontfamily>In a nutshell. Take your securities in 
    your portfolio and copy them to a new sub directory. Now open them up but 
    select the option for days loaded to be one day. <BR><BR>OLE this single bar 
    into Excel. Save as a smart chart. <BR><BR>Now in Excel take the info you 
    want and link it to it's final resting place. <BR><BR>Harley 
    <BR><BR><BR>skystar wrote: <BR>
    <BLOCKQUOTE><BR>
      <BLOCKQUOTE><B><?fontfamily><?param Arial><?smaller>--<?/smaller><?/fontfamily></B> 
        <BR><?fontfamily><?param Arial><?smaller><?/smaller><?/fontfamily><?smaller>I 
        would greatly appreciate it if someone/s could help me to construct a 
        porfolio manager in Excel that will update after doing an end of day 
        share price download on Metastock. I have tried copying a line graph in 
        Metastock and doing a &quot;paste special&quot; &quot;paste link&quot; 
        in Excel and I get the entire share price history. What I want is only 
        the end of last day's trade date, volume, high, low and close to be 
        updated in the previous day's date, volume, high, low and close cells in 
        Excel.Thanks - CharlesUsing Metastock 6.5<?/smaller><?smaller> &lt;<A 
        href="mailto:skystar@xxxxxxxxxx";>mailto:skystar@xxxxxxxxxx</A>&gt;<A 
        href="mailto:skystar@xxxxxxxxxx";>skystar@xxxxxxxxxx</A><?/smaller> 
      <BR></BLOCKQUOTE></BLOCKQUOTE><BR></BLOCKQUOTE><BR></BLOCKQUOTE>&lt;&lt;&lt;&lt;<BR></DIV></BODY></HTML>
</x-html>From ???@??? Sun Mar 07 16:12:24 1999
Received: from listserv.equis.com (204.246.137.2)
	by mail05.rapidsite.net (RS ver 1.0.2) with SMTP id 2579
	for <neal@xxxxxxxxxxxxx>; Fri,  5 Mar 1999 19:17:07 -0500 (EST)
Received: (from majordom@xxxxxxxxx)
	by listserv.equis.com (8.8.7/8.8.7) id HAA17744
	for metastock-outgoing; Sat, 6 Mar 1999 07:55:53 -0700
X-Authentication-Warning: listserv.equis.com: majordom set sender to owner-metastock@xxxxxxxxxxxxx using -f
Received: from freeze.metastock.com (freeze.metastock.com [204.246.137.5])
	by listserv.equis.com (8.8.7/8.8.7) with ESMTP id HAA17740
	for <metastock@xxxxxxxxxxxxxxxxxx>; Sat, 6 Mar 1999 07:55:49 -0700
Received: from hme0.mailrouter02.sprint.ca (hme0.mailrouter02.sprint.ca [207.107.250.60])
	by freeze.metastock.com (8.8.5/8.8.5) with ESMTP id QAA24595
	for <metastock@xxxxxxxxxxxxx>; Fri, 5 Mar 1999 16:53:21 -0700 (MST)
Received: from walter (spc-isp-mtl-58-6-748.sprint.ca [149.99.145.241])
	by hme0.mailrouter02.sprint.ca (8.8.8/8.8.8) with SMTP id SAA27877
	for <metastock@xxxxxxxxxxxxx>; Fri, 5 Mar 1999 18:41:38 -0500 (EST)
Message-ID: <001001be6762$89f763a0$f1916395@xxxxxx>
From: "Walter Lake" <wlake@xxxxxxxxx>
To: "Metastock bulletin board" <metastock@xxxxxxxxxxxxx>
Subject: Excel
Date: Fri, 5 Mar 1999 18:47:31 -0500
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Sender: owner-metastock@xxxxxxxxxxxxx
Precedence: bulk
Reply-To: metastock@xxxxxxxxxxxxx
X-Loop-Detect: 1
X-UIDL: 5698e0378d80d77c640a512630efa414.40

Hi Ton

Do you or anyone else have an "Excel" file of previous Excel messages to the
bulletin board.

Best regards

Walter