Charting real-time DDE stock prices

**shawnh** · 02-16-2010, 08:37 PM

Hi all, I have a DDE spreadsheet that receives live stock prices coming into it. I can type a stock symbol (let's say MSFT) into cell A1 and the current stock price will automatically display in cell T1, and it will then update live in realtime... maybe once every 2 or 3 seconds or so.
What I'd like to be able to do is to plot a chart of these live prices on MSFT coming into cell T1. I'm thinking however, that if I somehow write the value of T1 to some row each, say, 5 seconds, that pretty soon I'm going to have thousands and thousands of price quotes and it won't belong before I bog down my whole spreadsheet. Ideally (if possible), I'd like to only chart, say, the last few thousand price quotes and then every new price quote coming in would get tacked onto the end, while dropping off the oldest price quote from the beginning... so I'm never charting more than a few thousand price quotes at any one time.

Has anybody ever wrestled with a problem like this?

Thanks!
Shawn

**shawnh** · 02-21-2010, 07:46 PM

Any takers for this tricky one? Anybody? :-)

Thanks
Shawn

**teylyn** · 02-21-2010, 08:16 PM

Conceptually speaking: You need a macro that will copy the current state of the cell and append it into the first empty cell of a given column.
Then you need to define a dynamic range that always grabs the last x cells of the column. This dynamic range must then be defined for your data series.

With the VBA to copy the data I can't help, unfortunately.

The definition for the dynamic range can be something like

=INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A)-500):INDEX(Sheet1!$A:$A,COUNT(Sheet1!$A:$A))

This is for 500 data points.

**nsns** · 02-24-2010, 01:42 AM

Can you help me with that sheet, I am tring to have real time Exel sheet for my stocks but I couldn't do it
I will be very please to you if you can help me with this matar

thanks

nsns

**teylyn** · 02-24-2010, 03:38 AM

nsns,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.