Real-Time Data

Hi,

I have a problem. I'm using a real-time data provider for stock data. That is not the problem. The excel sheet effectively can give a time to one cell and a price quote to another cell. This updates in real time. What I would like to do is produce and a sheet of price data throughout the day as follows:

Time High Low
9:00 1523 1517
9:05 1527 1520
9:10 1525 1523

So between 9:00 and 9:05 the high and low would update depending on the real time quote for that time period. The sheet would update for each 5 minute period. My problem is how to achieve this. I do have VBA experience so if that is the way to go it would be ok. I managed to get a high and a low for a time period only by changing the calculation mode to iterative and limiting to 1 iteration.

Can anyone think of how to solve my problem? I can explain more if things aren't clear.

I'm not quite sure where your real time data is input into excel, but I am going to make some assumptions real quick...

#1) The Real Time Data is cummulative and in a list
#2) The List is Column A: Time Column B: Quote
#3) The Most Rows you deal with in a day is 1000 (you can adjust this as necessary)
#4) You have Your Time for the intervals in column E
E2=9:00
E3=9:05

These Array formula should work.. Remember to Enter with Ctrl-Shift-Enter


={LARGE(($A$1:$A$1000>$E2)*($A$1:$A$1000<=$E3)*($B$1:$B$1000),1)}
={SMALL(($A$1:$A$1000>$E2)*($A$1:$A$1000<=$E3)*($B$1:$B$1000),1)}

Not sure if that is what you are looking to do or not, but maybe it will set you on the right path...

Hope that helps...

John

Thanks for the reply john, I'll try to digest your reply in the morning and see if it does the trick. Regards