|
PureBytes Links
Trading Reference Links
|
A rather "clunky" of doing it was posted in 1997 (copied from my previous
e-mail software):
>Resent-Date: Thu, 7 Aug 1997 13:56:46 -0600
>To: bmccall@xxxxxxxxx
>From: "Harley Meyer" <Harley.D.Meyer-2@xxxxxxxxxx>
>Subject: RE: New User Question re. MS to Excell
>Reply-To: Harley D Meyer <Harley.D.Meyer-2@xxxxxxxxxx>
>CC: metastock-list@xxxxxxxxxxxxx
>Date: Thu, 7 Aug 1997 14:23:07
>X-Tick-Nemesis: Chairface Chippendale
>Resent-Message-ID: <"DCg7R2.0.115.vXYwp"@mail.equis.com>
>Resent-From: metastock-list@xxxxxxxxxxxxx
>X-Mailing-List: <metastock-list@xxxxxxxxxxxxx> archive/latest/3697
>X-Loop: metastock-list@xxxxxxxxxxxxx
>Resent-Sender: metastock-list-request@xxxxxxxxxxxxx
>
>Been there done that. It's a trick. The stocks you want in Excel move to a
>seperate folder. Make a custom indicator called the close of the close.
Drag
>that into MS. Change the line to be as thick as possible. Close and save as
a
>smart chart. Reopen but this time now go to options and select load 1 day
>display one day.
>
>Now select the 1 day price bar. Use the tab to move to the close (custom
>indicator). You have to watch the status bar at the bottom. Once selected
go to
>the menu to copy. Then go to Excel to paste special. Then link. then CSV.
Done.
>
> hope you get the point. Then don't forget to add the new subdirectory to
the
>downloader.
>
>
>On Thu, 07 Aug 1997 15:09:26 +0500,
>bmccall@xxxxxxxxx wrote...
>>I asked this question on CIS and didn't get a reply so I hope those
>>members on this group who sucribe to both aren't put out.
>>I have a program in Excell that needs the last closing price of the same
>>stocks I use in MS. I know how to cut and paste but I would like to link
>>the last closing price in MS to Excell. I am not lookink for the entire
>>price history just the last closing price.
>>Help
>>Thanks in advance.
>>Dr.M.
>>
>>
>
>Harley Meyer
>meyer093@xxxxxxxxxx
There is another way of doing it without moving the data to another
sub-directory. This involves having your default Smart Chart having the
Close (as mentioned above) in a separate window. This data can then be OLEd
across to your excel spreadsheet. With this method, you obtain for each
Ticker Code 2 columns of data being the most recent 1,000 days of Date and
the Close. You can then use the last row of data which will be the most
recent Close, or in the case of those Ticker Codes where there is less than
1,000 rows of data, you can use the excel code eg:
=VLOOKUP(NOW(),'C:\EXCEL\[PORTFOL.XLS]Prices'!A3:B1002,2)
Where:
"Porfol.xls" is the name of your Spread Sheet
"Prices" is the name of the Work Sheet
!A3:B1002,2 is the data from Columns A and B (A being the Date Column and B
the Close Column)
>From time to time, the OLE causes a bit of a problem as mentioned in recent
posts. In those instances, you may have to delete the Smart Chart in the
MSSMART sub-directory to get it working again.
As an explanation for the reason why it is exactly 1,000 rows of data, here
is a post from Equis dated February 1997:
>To get your Excel spread sheet to update simply load less data into your
>chart than what was originally pasted into Excel. Why? Excel
>cannot change the size of the data array (try it, Excel will not let you
>change the amount of cells or edit the Data directly). So, if you
>originally loaded all of the periods that a security had into you MetaStock
>chart (we will say that you had exactly 1000 periods) and you
>paste link those 1000 periods into Excel, Excel will make rows to hold
>those 1000 data points. You add data to your security so it now has
>1001 data periods. You open the Chart in MetaStock with 1001 periods
>loaded. Excel does not have space in its Data Array to display
>the 1001 periods because it can't add another row. If you only load 1000
>periods instead of 1001, Excel will update by dropping the oldest
>date (because that date is no longer loaded in the MetaStock Chart) from
>the top of the spreadsheet and the new data will appear at the
>bottom of the spreadsheet.
>
>The OLE II link between MetaStock and Excel (or anything else), is using
>the loaded Chart data. That means that the amount of loaded
>periods is going to effect the link.
>
>Also, the reason the dates come up as numbers is because of the OLE
>subsystem and its interaction with Excel. I did extensive research into
>this. MetaStock itself has no control over this situation. We also have
>no control over the above situation.
>
>Equis Support
> -----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
|