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> </DIV>
<DIV><FONT
size=2>----------------------------------begin----------------------------------------------</FONT></DIV>
<DIV><FONT color=#000000 size=2></FONT> </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 "copy" 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
"conversion"-button to go to "convert"-dialog<BR>-before
actual conversion, to go to its "options"-dialog<BR>-on the
"destination" tab - traverse folders must be marked/applied<BR>-click
ok and ok again on the "conversion options" tab to get
you<BR> back to the "convert-dialog":
<BR>General: folder to convert to can be just the "parent" 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> </DIV>
<DIV><FONT
size=2>----------------------------------------end---------------------------------------------------
<BR></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2><B>-----Oorspronkelijk bericht-----</B><BR><B>Van:
</B>Teo Soon Bock <<A
href="mailto:teosb@xxxxxxxxxxxxxx">teosb@xxxxxxxxxxxxxx</A>><BR><B>Aan:
</B>metastock@xxxxxxxxxxxxx <<A
href="mailto:metastock@xxxxxxxxxxxxx">metastock@xxxxxxxxxxxxx</A>><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><< QUOTE
>><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 "comma
separated values.") 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><< UNQUOTE
>><BR><BR>At 10:10 PM 5/7/98 +0200, skystar wrote:
<BR>>>>><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 <<<A
href="mailto:meyer@xxxxxxxxxxx">mailto:meyer@xxxxxxxxxxx</A>><A
href="mailto:meyer@xxxxxxxxxxx">meyer@xxxxxxxxxxx</A>><BR><B>To:
</B><<A
href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx">mailto:metastock@xxxxxxxxxxxxxxxxxxxx</A>><A
href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx">metastock@xxxxxxxxxxxxxxxxxxxx</A>
<<<A
href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx">mailto:metastock@xxxxxxxxxxxxxxxxxxxx</A>><A
href="mailto:metastock@xxxxxxxxxxxxxxxxxxxx">metastock@xxxxxxxxxxxxxxxxxxxx</A>><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 "paste special" "paste link"
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> <<A
href="mailto:skystar@xxxxxxxxxx">mailto:skystar@xxxxxxxxxx</A>><A
href="mailto:skystar@xxxxxxxxxx">skystar@xxxxxxxxxx</A><?/smaller>
<BR></BLOCKQUOTE></BLOCKQUOTE><BR></BLOCKQUOTE><BR></BLOCKQUOTE><<<<<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
|