Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 1st 04, 06:15 PM
Post Tenebras Lux
 
Posts: n/a
Default Data Table - Does it work with DDE links and stock tickers?

I'm posting this is General and in Excel Functions, because I don't know
where it will get looked at. Apologies.

I have a spreadsheet that is set up to pull up historical stock prices via
dde links that is driven by the stock's ticker - e.g. MSFT

Once the prices are pulled into the spreadsheet, it calculates some values -
to keep it simple, let's say it calculates a 5d, 20d and 50d moving averages.

I want to create a data table that will use all the tickers I want to look
at in the column on the left, and the three moving average periods in the
row, looking something like this:
Moving Average Period (days)
A1 5 20 50
Stock MSFT
Ticker IBM
HP

A1 is the cell reference to the formula that calculates the moving average.
Another cell, let's say B1 contains the current ticker, MSFT, and that is the
column reference for the stock tickers in the table, and, let's say C1
contains the current moving average period, 5 (days).

When I build the data table, even though I can see the spreadsheet going
through the motions of "calculating" for each row and column in the data
table, only the current ticker is used by the data table. The row header
(moving average period) works fine.

But I end up with a data table that has the first row (in this example,
because it is the same as the current value in the Ticker cell B1) of data
correct, but just repeated all the way down for each ticker. That is, the
remaining tickers show the MSFT calculations in their rows.

I've got a reasonable amount of experience, and I know (and have checked)
that my data table settings are fine.

Is it possible to do this? Because the calculations done on the stock
prices are really not this simple, and involve alot of data through the DDE,
I don't want to have to build a repeated calculation model for each ticker
(because we're talking alot more than three).

Hope this explains what I'm trying to do. I wondered if it was the DDE link
that causes the problem (works fine for the current link, and I can see it
"resetting" through every iteration of the data tabel calculation). I also
thought it may be that data tables will only accept numerical inputs.

Any suggestions would be hugely appreciated.

Thanks!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 03:45 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017