|
PureBytes Links
Trading Reference Links
|
The way to have this update-process automaticaly done requires for you to
have an OLE-link capable object(chart or indicator) to be present. In Metastock
this can be easy established by creating seperate Charts for each security.
Follow and execute these steps below, here I am using a single daily
closing price as object, for a simplified use of the Win 95's OLE program.
1. First make a new indicator Close Only :
-Start Metatsock and click the button for the Indicator Builder
-In Indicator Builder create a custom indicator named "Close Only"
(without the quotations) and in the formula field type CLOSE and click OK
2. To create a Close Only Template :
-Start the Win95-Explorer and create a new folder named OLE(which folder
will hold your Template and Charts used for this OLE) below your working
folder(that is holding your metastock files dat/dop/master/emaster etc.)
-Then switch back to Metatstock
-Open the by you desired security using Smart Charts as type
-Delete all other charts and all inner windows and all indicators that are open
in the current screen(=layout) except for the base securities' Price indicator
(the bar, line, sticks)
-Drag the newly created Close Only indicator down from the IB-Quick List
(from the small window in the middle at the top) and release it to have the newly
created indicator displayed in its own inner-window
-Now SAVE AS the current screen (with Template as the file type) using the
CloseOnly name(without a space) as the Template's name("CloseOnly.mwt")
-Close down Metatsock + Win95-Explorer
3. To create the separate Charts used for OLE :
-Start Metatsock(fresh again) and click New|Chart or click Open
-Click Apply Template (this action is always required prior to selecting a security)
and scroll to the OLE folder to apply the newly created CloseOnly Template
-On Opening of this New Chart the above mentioned Template's layout containing
the Price and the Close Only indicators will be displayed
-Now SAVE AS the current screen (with Chart as the file type) using the security's
name as the Charts' pointer name("SecurityX.mwc") to the newly created
OLE-folder
-Close Metastock
4. To create the OLE link from Metastock to an Excel spreadsheet :
-Start Metatsock(fresh again) and click Open
-Open the required security in the newly created OLE-folder
-Right-click to Select and click Copy to have the security's CloseOnly indicator
copied to the Clipboard
-Start Excel and check that the first cell at the top-left is been selected(=black
line bordered rectangle)
-Select the required cells by placing the mouse-pointer at the right corner of the
selected rectangle and click and press down the Left-mouse button and whilst
at the same time holding the mouse-button down, drag down this first column(A)
and release button until you have reached record row #999 and all of the
selected cells will be colered black(Note that this selection made, has to be
done in one(1) straight firm move down the column, eg a one single selection
has been made)
-Now let the mouse-pointer float on this blackened selection and Right-click to
choose Paste Special
-In the Paste Special's Dialog Window click the Paste Link radio-button and
choose CSV as file-type
-With plenty of system memory on board it will not take that long before the
Special Linked data is calculated and displayed (as the cell's contents), and
that the Link has been made
-Close and Save As the Excel file to the OLE folder(with standard XLS as file type)
with the security's name as the pointer name
-Each time now, that you Open this XLS-file again, automaticaly the Excel program
will have you prompted if you would to update the Link. Within the Excel program's
options (Tools|Options|Calculations or Edit|Link|Manual) you can pre-set this to
"manual" as well, but then you will have to click Edit|Link|Update Now to update
once the spreadsheet's above Linked cell selection entirely
A.
Note here that the more history is stored in your 'original' Metastock files, eg the
files the Chart uses as its base, the longer the column contents(displayed cells), the
longer it will take to calculate and also the more memory is being used, so you
will have to keep this 'history' as short as what can be possible for any fast results.
B.
Note here too that you can then apply the special instructions (mailed in a previous
mail to the List) to have the Linked cells' contents SPLIT UP over more cells in the
spreadsheet(s), so as to enable you to make calculations in Excel, eg using Excel's
cell linking(referencing) and formula language(the tiny editor) capabilities and/or
apply any of the other Excel program's features.
C.
Note here also that the above applies for MS6.x and Excel8.0(OfficePro97).
D.
To reverse this OLE linkage back into Metatsock, do not forget to create an
empty Inner Window first, prior to creating the Link.
In MS click Window|New Inner Window and then Right-click in this Inner Window and
choose Paste Special|Paste Link (with CSV as file type). See MS-Help or MS-Manual
or Equis' Customer|Support website for more detailed instructions.
Regards,
Ton Maas
ms-irb@xxxxxx
----- Original Message -----
From: Lionel and Gail Issen <lissen@xxxxxxxxxxxxxxxx>
To: <metastock@xxxxxxxxxxxxx>
Sent: vrijdag 5 maart 1999 4:22
Subject: Re: Excel and MS
>Tom:
>
>This should work.
>
> What I am looking for is a more automated process. It may reqire the use
>of macros and/or some other utility like automate or a similar program.
>
>Lionel Issen
>-----Original Message-----
>From: Tom Strickland <tstrickland@xxxxxxxxxxxxx>
>To: metastock@xxxxxxxxxxxxx <metastock@xxxxxxxxxxxxx>
>Date: Thursday, March 04, 1999 9:05 PM
>Subject: Re: Excel and MS
>
>
>>Lionel,
>>
>>As I understand your desire, it's to take data from a MetaStock file and
>>use it to update an Excel file. Here's how I'd do it:
>>
>>Open the Downloader and then open the data file in question. Highlight the
>>data of interest and then hit the Copy button. Go to the appropriate Excel
>>file and Paste the data in the desired location. It's as simple as that if
>>I understand your question correctly.
>>
>>Let me know if this does what you want.
>>
>>Tom Strickland
>>
>>
>>
>>At 04:54 PM 3/4/99 -0600, you wrote:
>>>Last year someone posted a technique for updating Excel files from MS
>>>files. That is only the update data was appended to the excel file.
>>>
>>>I thought that I had saved this file, but I cannot find it. If anyone
>has
>>>this information, or knows how to do it, please post or email me
>directly.
>>>
>>>Thanks
>>>
>>>Lionel Issen
>>>
>>>
>>>
>>
>
>
|