Enter Timestamp for each time cell value changes

[michaelyeehaw]

I have dynamic low of day (LOD) stock prices in column A and last stock prices (LSP) in column B. The data is pulled from an external web service and never hard coded. I need to create 11 additional columns of data based on these two values:

Column C would display the times the LSP fell below LOD plus .01
Column D would display the times the LSP fell below LOD plus .02
Column E would display the times the LSP fell below LOD plus .03
Column F would display the times the LSP fell below LOD plus .04
and so on

For example: The LOD in A5 is 10.5.

C5 would display the timestamps for when the LSP fell below 10.51
D5 would display the timestamps for when the LSP fell below 10.52
E5 would display the timestamps for when the LSP fell below 10.53
F5 would display the timestamps for when the LSP fell below 10.54
...
M5 would display the timestamps for when the price fell below 10.61

If the LOD fell to 10.49, all the data would shift right one column, with C5 the only cell to display the new timestamp as the LSP fell below 10.50 and M5 now displays the timestamps for when the LSP fell below 10.60 as the data had been shifted right one column (previous data in M5 is now gone).

If the LOD suddenly changes from 10.50 to something like 10.45 at 10:30:29 AM, the data shifts right 5 rows such that rows I to M disappear to make way for the five fresh new sets of data in rows C to G, each containing the one timestamp 10:30:29 AM.

Upward movements of the LSP wont necessitate a timestamp. Only negative movements of the LSP below LOD plus .11 will necessitate a new timestamp, each separated by a semicolon (, in the rows represented by the prices the LSP decreased below.

Note: if the LOD is 10.50, a sudden move in the LSP from 10.55 to 10.53 would only one time stamp for Columns G and F as the LSP decreased below LOD plus .05 and LOD plus .04.

 

[Yongle]

Thanks
How frequently is the data auto-refreshed ?
How many times in a day might a monitored cell change its value ?
(just trying to evaluate the size of the beast that you are about to create )

 

[michaelyeehaw]

two dynamic columns with about 500 rows, represented by a stock ticker's current price and LOD value. theoretically, each cell in the two columns could change hundreds of times per minute. realistically, the prices would be changing much less, especially the LOD, which only changes when the current price hits a new low

 

[michaelyeehaw]

two dynamic columns with about 500 rows, represented by a stock ticker's current price and LOD value. theoretically, each cell in the two columns could change hundreds of times per minute. realistically, the prices would be changing much less, especially the LOD, which only changes when the current price hits a new low

auto refresh is instant as there are constant value updates throughout the worksheet