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.
Max
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.
708-771-2133
Posted by Tom Dickinson on January 23, 2002 7:38 PM
By saying live data, I assume that you have macros running that are constantly changing the data. You can easily put in a macro that runs every time there is a change to your spreadsheet, but I'm not sure what effect that will have on whatever it is that is supplying your live data. If you want to try, then go to the macro page for the sheet (do not use the separate macro page). the program would look something like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("Q100") > Range("R100") Then
Range("R100") = Range("Q100")
End If
If Range("Q100") < Range("S100") Then
Range("S100") = Range("Q100")
End If
End Sub
You may be able to put this in the macro that is supplying you the live data (in which case don't use the first and last lines).
One caution: you will need soemthing in S100 to start with as a blank will have a value of zero.
Hope this helps.
