Min/Max odds matched

[clinton]

Hello
I have a question: if I want to know in a certain moment which was the minimum and the maximum odds matched for a detrminate horse, is it possible? I can see looking at the ladder, but is it possible make tre process automatic using excel?

[MiniBlueDragon]

Could you cannibalise something like this in the Worksheet_Change Sub but adapt it for price?

Code: Select all

'Check current Bank and if it's higher than existing Max Bank, update Max Bank
If Worksheets("Gruss").Range("I2").Value > Worksheets("Gruss").Range("U1").Value Then
    Worksheets("Gruss").Range("U1").Value = Worksheets("Gruss").Range("I2").Value
End If

Set yourself a "min" and "max" cell for your chosen runner and then use something like the above but for the prices. For example if you have column Y as "Max" and Z as "Min":

Code: Select all

'Check favourite price and record any new high
If Worksheets("Gruss").Range("F5").Value > Worksheets("Gruss").Range("Y5").Value Then
    Worksheets("Gruss").Range("Y5").Value = Worksheets("Gruss").Range("F5").Value
End If

'Check favourite price and record any new low
If Worksheets("Gruss").Range("F5").Value < Worksheets("Gruss").Range("Z5").Value Then
    Worksheets("Gruss").Range("Z5").Value = Worksheets("Gruss").Range("F5").Value
End If

Note: None of this is tested.

[clinton]

Ty you are very kind, but my knowledge is too low to test your code.

[MiniBlueDragon]

I've created a spreadsheet. Testing now and then I'll upload it for you.

[clinton]

I cant believe you are so kind....
1000 times thank you...

[MiniBlueDragon]

Here ya go.

Currently it only monitors the favourite but you can easily change the row to be monitored in the VBA code.

Who knows maybe one of the "proper" coders on here can help improve it to monitor all runners in a race (which I think might be beyond my coding ability)

https://www.dropbox.com/s/1q3b9xhw42gb0 ... 20fav.xlsm

[clinton]

Ty very much... If you realize how monitoring all the runners let me know... Ty again

[clinton]

I tested it doesn't work... anyway never mind, ty for your time again

[MiniBlueDragon]

Did you make sure the permissions on the file were "enabled"?

Works fine here.

[clinton]

sry if i am so stupid, but how can enable permission? with BA oe with excel?

[MiniBlueDragon]

Normally when you open a macro spreadsheet Ecel prompts to ask if you want to Enable it. If it doesn't prompt then sometimes it's automatically blocked it and you might need to add a "trusted" location into Excel via File > Options > Trust Center > Trust Center Settings > Trusted Locations > Add New Location

[clinton]

ok I'll check thx

[clinton]

No macro are activated but it doesn't work. May be the problem is that my excel is the italian version...

[Captain Sensible]

Here's some code I use for inplay odds, it should stick starting odds in AH , Lowest in AI and highest in AJ. Takes the price from last matched price. If you just want to monitor all in play and pre off just remove the part "Cells(2, 5) = "In Play" And" Remember these things will only log min and max odds from when you enter the market, if you want everything I think you'll need to use the COM functions as you have access to all the API calls such as traded volume etc


Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

For Each cell In Target
iRow = cell.Row
iCol = cell.Column


' Update Max and Min Back Values
If Cells(2, 5) = "In Play" And iCol = 15 And iRow > 4 And iRow < 46 Then

Application.EnableEvents = False
If IsEmpty(Range("AJ" & iRow).Value) Then Range("AJ" & iRow).Value = 0
If IsEmpty(Range("AI" & iRow).Value) Then Range("AI" & iRow).Value = 1001
If IsEmpty(Range("AH" & iRow).Value) Then Range("AH" & iRow).Value = Range("O" & iRow).Value

If Not IsEmpty(cell.Value) Then
Range("AG" & iRow).Value = Range("O" & iRow).Value
If cell.Value < Range("AI" & iRow).Value And cell.Value > 1 Then Range("AI" & iRow).Value = cell.Value
If cell.Value > Range("AJ" & iRow).Value And cell.Value < 1001 Then Range("AJ" & iRow).Value = cell.Value
End If

Application.EnableEvents = True
End If



Next cell

End Sub

[Captain Sensible]

Here's the actual code it was written by a poster called phrenetic

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

For Each cell In Target
  iRow = cell.Row
  iCol = cell.Column

  ' Update Max and Min Back Values
   If iCol = 6 And iRow > 4 And iRow < 46 Then
    Application.EnableEvents = False
    If IsEmpty(Range("AJ" & iRow).Value) Then Range("AJ" & iRow).Value = 0
    If IsEmpty(Range("AI" & iRow).Value) Then Range("AI" & iRow).Value = 1001
    If Not IsEmpty(cell.Value) Then
      If cell.Value < Range("AI" & iRow).Value And cell.Value > 1 Then Range("AI" & iRow).Value = cell.Value
      If cell.Value > Range("AJ" & iRow).Value And cell.Value < 1001 Then Range("AJ" & iRow).Value = cell.Value
    End If
    Application.EnableEvents = True
  End If
 
  ' Update Max and Min Lay Values
  If iCol = 8 And iRow > 4 And iRow < 46 Then
    Application.EnableEvents = False
    If IsEmpty(Range("AL" & iRow).Value) Then Range("AL" & iRow).Value = 0
    If IsEmpty(Range("AK" & iRow).Value) Then Range("AK" & iRow).Value = 1001
    If Not IsEmpty(cell.Value) Then
      If cell.Value < Range("AK" & iRow).Value And cell.Value > 1 Then Range("AK" & iRow).Value = cell.Value
      If cell.Value > Range("AL" & iRow).Value And cell.Value < 1001 Then Range("AL" & iRow).Value = cell.Value
    End If
    Application.EnableEvents = True
  End If
Next cell
End Sub