MrExcel Publishing
Your One Stop for Excel Tips & Solutions

working with live data

Posted by Max on January 18, 2002 10:35 AM

I would like to have excel remember the highest and lowest values of a certain cell.

I am retrieving live data, as that data changes I would like excel to remember the high and the low and contiually change as the highs and the lows change in that particular cell.

For instance. If incoming data initially gave me a reading of 100, but then went to 105 and back to 95 I would like to have 2 cells that show the High and Low of that data. One cell would contain 105 and the other would contain 95. And the live data cell would contain the current value.

The highs and lows would only change if the value of the live data cell goes higher or lower.

Thanks for your help. I know this should not be so difficult.

Posted by Tom Dickinson on January 18, 2002 11:31 AM

In the object sheet (or whatever is is called) for the spreadsheet are using, put in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") > Range("B2") Then
Range("B2") = Range("A1")
End If
If Range("A1") < Range("B1") Then
Range("B1") = Range("A1")
End If
End Sub

This will check A1 and put it into B1 (low), or B2 (high). You will want to initially enter numbers into these cells as the computer thinks of them a zero (for comparison purposes) when they are empty.

Posted by Max on January 19, 2002 9:42 AM

Thanks Tom, I think that is the basic solution for what I need.

Let me explain the exact nature of the incoming data.

The data consists of equity futures prices, the S&P500 and Nasdaq 100 specifically. Now, standard charting packages display the data ranges in specific time periods for the individual symbols. But I am trying to graph the "spread" or the difference between the two symbols. So I've applied the dollar amount to the contracts and then subtracted the nasdaq from the s&p.

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.

Meanwhile, I think I need to study this programming language. Can you recommend some online instruction?

Thanks Max

Posted by Max on January 22, 2002 7:15 AM

Tom, I need a little more help please

Posted by Tom Dickinson on January 22, 2002 9:55 AM

Re: Tom, I need a little more help please

Glad it works. My outbound e-mail doesn't.

For books an courses or whatever on the subject, I don't know. I took one programming course about 20 years ago (punch cards were on the verge of being replaced), back when you had to tell the spreadsheet if you were entering a number or a text into the cell before you entered whatever into the cell. From there I have just picked up a book now and then on the stuff and did some reading. One that I like is put out by Que. It has a listing of all the funtion syntaxes, along with an explanation.

As far as other questions; got any good stock tips?.....

Tom D.

Posted by max on January 22, 2002 1:02 PM

Still doesn't work quite right

Posted by max on January 22, 2002 1:30 PM

Still doesn't work quite right

I don't really trade many stocks. Just futures.

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). Depending on contract volatility I adjust the ratio for which I buy and sell. That spread value is generated by a dollar value, which I derive from the number of contracts traded times the dollar amount of each contract. Because this is not your normal "spread", standard charting packages will only display this value as a "line on close". 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.

If you can help me, I would gladly pay you some sort of fee. I am just starting out, so go easy on me.

I left you my phone # on the email I sent you, feel free to call if you require further information.