Thanks to the help I received, I was able to jerry-rig a system that at least looks like it works (no way to see if it properly updates until tuesday.) I felt the need to go via Excel because I am comfortable manipulating data there, and I feel the formula for determining the index will undergo numerous modifications in the future.Here is what I did:
Project: to create a sector index against which to measure a stock from that sector.
Create Linked Spreadsheets in Excel
Set up Charts for Each Security to be in the Index in MetaStock and one for the Comparison Security. I did this in a special folder for this Project.
In MetaStock, Click on Price Plot and Copy
In Excel, Set up a Spreadsheet Labeled with that Securities' Symbol
Paste Special, Csf, Paste Link
Repeat for Each Security in Index and for the Comparison Security.
Perform Calculations to Create Index Data
Open a New Worksheet
Columns: Date [] Final Data [] Close Security XXX (one column for each security in the index)
Paste Special, Paste Link the Closing data from the Individual Securities Spreadsheets into the appropriate Close Security XXX column being sure that data is aligned properly by date. (I had a very hard time doing this as, for some reason I was not allowed to copy portions of data from within the array. I went into an endless circle telling me that I could not if I did so I gave up. I solved the problem by copying the close data by clicking on the column heading and using Edit, Copy, and in Excel, Edit, Paste Special, Csf, Paste Link. It was then necessary to insert spaces as necessary to insure that all columns ended on the same date.)
In the Final Data Column perform the required calculation to arrive at the Data to be used for the index; for example: (A/an+B/bn+C/cn)/x.
(I am sure there must be a better way to do this next, but it was the only way I could get it to work.) Copy and Paste Special, Paste Link the Final Data Column into the blank column to the right of the Comparison Security's Excel datasheet, being sure to insert it in the proper cell so that dates align. Copy the entire column by clicking on the column head, Copy.
In Metastock, on the main window of the Comparison Security, Right Click, Paste, Paste Special, Paste Link.
WISH'N ‘N HOPE'N: The result will be a Security Chart with an Index in the background which updates automatically with the new data downloaded daily. |
