working with live data

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.

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
maxrose@earthlink.net

Tom, I need a little more help please

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.

Still doesn't work quite right

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.

Thanks

Max