How to move a cell value using an RTD formula (pulling in live updating data) when it changes?

New Contributor


I am using Interactive Brokers TWS trading platform and they supply a demo excel file that has RTD formulas to pull in a list of available live data trading prices such as Bid, Ask, Last, etc.


The RTD connection does not allow you to pull in historical data for the trading prices mentioned above.


What I am want to do is the following, if possible:


The Last price traded for the MES Futures contract is pulled into cell F3 by using the following formula:


RTD Connecxtion.png


I am currently connected to the TWS trading platform and the value in cell F3 is 4761.25

As soon as the markets reopen this value will change continuously.


I want to be able to capture the price of 4671.25 that is pulled into cell F3 and move it down by one cell as it changes - for whatever number of cells I decide to capture.


For example, if I decide to capture the Last 20 prices, then I want to populate the range F3 to F22.

Once I can move the changing values into the cells below, I can then use conditional formatting to change the cell colors based on the criteria I use, which is my objective of the exercise.


Thank You.



1 Reply