High/Low in a continuously updated spreadsheet

[frdfrd]

I have a spreadsheet using the RTD function to update stock prices and option prices and I want the high/low for the day to be a field that queues off this RTD function. How can I do this. Example stock price is 10.00 then goes to 15.00 then goes to 20, then to 7.50. High for the day would be 20, low for the day 7.50 and these would be too separate fields queing of the RTD function thatis continuously updating price from a broker? Thanks

 

[offthelip]

You will probably find that the RTD function does't trigger the workhseet change event when you get an update, the way round this is to trigger a calculation so that the worksheet recalculates when the price is updated. Since you only want to update the High when the price is above the last high, I suggest you put that calculation on the workhseet and use it as the trigger.
So Assuming the price which updates is in A2 and that you want the High in B2, and Low in C2, put this equation in D2:

=A2>B2

then in the code forthe worksheet put this code:

Private Sub Worksheet_Calculate()
trigger = Range("D2")
If trigger Then
Application.EnableEvents = False
Range("B2").Value = Range("A2")
Application.EnableEvents = True
End If
End Sub

this does the High , the low can be done in exactly the same way

 

[frdfrd]

You will probably find that the RTD function does't trigger the workhseet change event when you get an update, the way round this is to trigger a calculation so that the worksheet recalculates when the price is updated. Since you only want to update the High when the price is above the last high, I suggest you put that calculation on the workhseet and use it as the trigger.
So Assuming the price which updates is in A2 and that you want the High in B2, and Low in C2, put this equation in D2:

=A2>B2

then in the code forthe worksheet put this code:

Private Sub Worksheet_Calculate()
trigger = Range("D2")
If trigger Then
Application.EnableEvents = False
Range("B2").Value = Range("A2")
Application.EnableEvents = True
End If
End Sub

this does the High , the low can be done in exactly the same way

Thanks very much. Will test on Monday when the market is open and data is being updated. Not real strong with VBA so may have a return question. Thanks again

 

[frdfrd]

You will probably find that the RTD function does't trigger the workhseet change event when you get an update, the way round this is to trigger a calculation so that the worksheet recalculates when the price is updated. Since you only want to update the High when the price is above the last high, I suggest you put that calculation on the workhseet and use it as the trigger.
So Assuming the price which updates is in A2 and that you want the High in B2, and Low in C2, put this equation in D2:

=A2>B2

then in the code forthe worksheet put this code:

Private Sub Worksheet_Calculate()
trigger = Range("D2")
If trigger Then
Application.EnableEvents = False
Range("B2").Value = Range("A2")
Application.EnableEvents = True
End If
End Sub

this does the High , the low can be done in exactly the same way

Probably user error but that did not work. I am not real strong at all with VBA code but I believe I did it correct.
The field that is updated every 3 seconds is cell K2. My high result is L2, low result is M2. Cell for > is N2 so in N2 I have K2>L2.
Here is my VBA Code:
Private Sub Worksheet_Calculate()
trigger = Range("N2")
If trigger Then
Application.EnableEvents = False
Range("L2").Value = Range("K2")
Application.EnableEvents = True
End If
End Sub

Thanks!

 

[offthelip]

It would appear that the updates in K2 are not triggering the worksheet calculate event. You could try putting the same code into the worksheet change event and see if that works.
If you can't get the update to trigger some sort of worksheet event, then it is going to be very difficult to do what you want.

 

[frdfrd]

It would appear that the updates in K2 are not triggering the worksheet calculate event. You could try putting the same code into the worksheet change event and see if that works.
If you can't get the update to trigger some sort of worksheet event, then it is going to be very difficult to do what you want.

That does not appear to work either. I was really hoping to get a solution. Anyone else out there have any thoughts? Thanks!

 

[frdfrd]

That does not appear to work either. I was really hoping to get a solution. Anyone else out there have any thoughts? Thanks!

So sorry, actually it did work. How do I code it for 4 different cells?

 

[offthelip]

How you do it depends a bit on where the cells are, Are they contiguous L3, K3 , L4, K4 etc and are they all maximums or are some minimums, If they are all together then it is easy to do them in a loop. If they are spread out but regular then a loop might work, if they are randomly spaced then the best way is just duplicate the code four times changing the cell references.
Can you post the code that worked here , so that i can update it.

 

[frdfrd]

Private Sub Worksheet_Calculate()
trigger = Range("N3")
If trigger Then
Application.EnableEvents = False
Range("L3").Value = Range("K3")
Application.EnableEvents = True
End If
End Sub

Thanks very much. The code above is what worked.
I have K4, K5, and K6 to compare as well.
K3 I still need the low which will be in M3.
High - in L3 (works from above) , L4, L5, L6
Low - in M3, M4, M5, M6.
I have the compare for high in N3, N4, N5, N6 which is =K3>L3
I have to compare for the low in O3, O4, O5, O6 which is =K3<M3.
If you can get me a loop for 2 instances of the high low. I can figure out the syntax from there most likely.
Thanks so much!

 

[offthelip]

That is excellent, the layout of the cells couldn't be better , ideal for doing two loops. Try this code:

Private Sub Worksheet_Calculate()
triggers = Range("N3:O6")
Kvalues = Range("K3:K6")
For i = 1 To 4
 For j = 1 To 2
   If triggers(i, j) Then
   Application.EnableEvents = False
   Range(Cells(i + 2, 11 + j), Cells(i + 2, 11 + j)).Value = Kvalues(i, 1)
   Application.EnableEvents = True
  End If
 Next j
Next i
End Sub

Note I have change the mode of addressing the output in columns L and M , this is because using " Cells(i+2),Cells(11+j) " allows me to add the two indices to constants to cover the changing rows and columns . also the reason for loading all the values in column K into a varaint array is because this is faster than doing it one cell at a time. This is important when you are dealing with realtime updates
Also when you post code can you please use the VBA fomatting which highlights the code as per my posts. you can do this by clicking on the icon which looks like a cloud with VBA under , then paste your code