Rank: Newbie
Groups: Registered, Registered Users
Joined: 5/4/2006(UTC)
Posts: 8
Location: Nyon, Switzerland
|
Hallo
First time I'm in the Metastock Forum. I'm trying to set up a "risk spreadsheet" on Excel by applying the OLE link feature. The only data I'm interested in for time being is the closing prices (for serveral stocks I have in the portfolio). I would like to see the Excel sheet being updated automatically after eod data download with the Downloader. I followed procudure described in the handout with copy - past special Csv etc. but new data does not come automatically to the exel sheet although I pushed the "refresh Data" button in MSP (which updates the chart in MSP only). How to procede in order to have a "life" spreadsheet or asked differently, what is the best way to set up to portfolio risk management spreadsheet based on eod data downlaoded via the "downloader". Maybe there is even a feature in MSP that I didn't find yet.
Many thanks for your help!
Stefan
|
|
|
|
|
|
Rank: Advanced Member
Groups: Registered, Registered Users
Joined: 3/19/2005(UTC)
Posts: 2,995
Was thanked: 14 time(s) in 10 post(s)
|
Hi Ladole-
Welcome to the forum!
It sounds like you are following the basic instructions. If you deviate at all, I have found that it will crash. Here are my notes on OLE. I don't use this feature daily, but my sample worksheets work and update OK. I use MetaStock Pro 9.2 for QuoteCenter and Excel 2003 SP1.
Getting started quickly:
1. Open Excel and clear the Office Clipboard: EDIT | OFFICE CLIPBOARD | CLEAR ALL
1. Open MetaStock and display the desired chart (SmartCharts or Charts are OK)
2. Right-click on the price plot or indicator plot and choose COPY from the shortcut menu
4. Change back to Excel, then position the cursor at the desired location within the spreadsheet
5. Right-click on the cell and choose PASTE SPECIAL from the shortcut menu
6. Choose the PASTE LINK radio button; CSV ("comma separated values") as the file type; Click OK.
7. Repeat as desired for further indicators; Right-click on subsequest date/time columns and select HIDE
8. You don't have to have all the applications open in order for the OLE links to remain up-to-date
Notes:
1. Format your Excel worksheet:
e.g., DATE for first column of data, TIME for second column of data, NUMBER for remaining columns of data
Indicator format requires 3 columns: Date, Time, Indicator Value
Price plot format requires 8 columns: Date, Time, Open, High, Low, Close, Volume, OI
2. Ensure that your data area has room to expand as data is updated
The required data range will depend on the number of data periods loaded by MetaStock
3. Use WINDOW | FREEZE PANE to facilitate readibility
4. [color=red:4eb41a9ffa]Data link will automatically update unless you set it to update manually: EDIT | LINKS | UPDATE MANUAL[/color]
Make sure you are set to AUTO UPDATE the links from within Excel (see #4 above). You can test EOD data updates by restricting your loaded data periods on the x-axis toolbar. Start with data that goes up to a month ago. Establish your link. Then change the loaded periods to something more current. The Excel data should update. Mine does. I don't even need to hit the refresh button in MS as the online help mentions.
|
|
|
|
|
|
Rank: Newbie
Groups: Registered, Registered Users
Joined: 5/4/2006(UTC)
Posts: 8
Location: Nyon, Switzerland
|
Thank you very much for the detailed reply. It is basically what I did. Though, I tried it again and it does not work. If I change the amount of the data loaded by manipulating the x-axes properties (say from April 9th to May 9th) nothing changes in the Excel sheet apart from some blinking figures during two second or so that tell me that some kind of link must be established, although the update does not work. Please be sure that I set the Excel sheet to "automatic link".
If you have any further idea how I could make this feature working properly, I would very much appreciate, although you problaby said already all you can say about this issue .. .
Again thank you very much for your help
PS: work with windows xp and office 2000 and MSP 9.1. Just received CD to upgrade to 9.2. I'll install it and try again. One never knows .. .
|
|
|
|
|
|
Rank: Advanced Member
Groups: Registered, Registered Users
Joined: 3/19/2005(UTC)
Posts: 2,995
Was thanked: 14 time(s) in 10 post(s)
|
Sorry it still doesn’t work. The only thing else I can think of is to try manually updating the link. You might also make sure that this inability to update is not something peculiar to one specific security. Try to plot an indicator or another price line. Make sure you are using Paste Special as a link and not plain Paste. If none of this resolves your trouble, you might try Equis Support via email or phone. If you figure it out let me know… I’ll add it to the list of resources.
|
|
|
|
|
|
Rank: Newbie
Groups: Registered, Registered Users
Joined: 5/4/2006(UTC)
Posts: 8
Location: Nyon, Switzerland
|
Thanks again ! Tried it several times and looks like something is finally "moving" since I upgraded from 9.1 to 9.2 but not sure if this is the reason. But since I installed 9.2 I have a problem with the downloader that for any particular reason doesn't seem to recognize most of the symbols anymore. I have to resolve first of all this and come back on the OLE issule later. I'll keep you updated.
Thanks and have a nice day !
|
|
|
|
|
|
Rank: Newbie
Groups: Registered, Registered Users
Joined: 5/4/2006(UTC)
Posts: 8
Location: Nyon, Switzerland
|
ladole wrote:Hallo First time I'm in the Metastock Forum. I'm trying to set up a "risk spreadsheet" on Excel by applying the OLE link feature. The only data I'm interested in for time being is the closing prices (for serveral stocks I have in the portfolio). I would like to see the Excel sheet being updated automatically after eod data download with the Downloader. I followed procudure described in the handout with copy - past special Csv etc. but new data does not come automatically to the exel sheet although I pushed the "refresh Data" button in MSP (which updates the chart in MSP only). How to procede in order to have a "life" spreadsheet or asked differently, what is the best way to set up to portfolio risk management spreadsheet based on eod data downlaoded via the "downloader". Maybe there is even a feature in MSP that I didn't find yet. Many thanks for your help! Stefan
|
|
|
|
|
|
| Users browsing this topic |
|
Guest (Hidden)
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
