Tom, or anyone ... managing live data
Posted by maxrose on January 23, 2002 6:31 AM
I'm still not getting the cells to retain highs and lows in the time period.
I trade a spread between the mini ES (s&p) futures contract and the mini NQ (nasdaq). What I need is the Open (easily attainable) High, Low (those are the two that present me with a problem) and the Close (again easy).
What I would like to do is this:
1.Take the last trade data of each of the contracts and apply my ratio formula, currently that formula is (value of the ES contract *150)-(value of the NQ contract*40) that formula gives me the "spread value".
I would like to generate candlestick bars, in several time frames, that would show me the open, high, low and close of that value. Ideally this would happen on a separate worksheet.
So the High and Low values need to change as the "last value" increases or decreases above or below the high or low value already there. Then I would like the next bar to start at the prescribed time interval, e.g. 15 min, 30 min, or 60 min.
The data is collected for 15 minute time frames, we call this Open, High, Low and Close. These timeframes are contiuous throughout the day.
The Open and Close values are accurate, but the High and Low for the "spread" is inaccurate, because the High and Low for each symbol might be reached at a different point in time.
That is why I need to monitor the changing field, which is the "Close" field and use those values to place the High and Low. Then those values need to stay (so that I can chart those values) in place.
The whole process starts again in the next 15 minute period.
So, the changing field(T100) consists of this formula
"(F100*150)-(M100*40)" that takes the last trade in the s&p contract multiplied by its dollar value and subtracts the dollar value of the nasdaq contract. This value fluxuates for 15 minutes. It represents the last traded value of the spread.
Q100 is the opening value
R100 should be the High point that T100 achieved (this should always be >= Q100)
S100 should be the Low point that T100 achieved (this should always be <= Q100)
Thanks for your help Tom, feel free to email me if you have any other questions.