getMarketTradedVolume into Excel question

[doris_day]

I'm trying to get the highest price for each runner where a minimum of say £100 has been traded.
Is that possible via an Excel worksheet which is being populated by BA data ?

[osknows]

The following code will monitor the 'last price matched' and 'Total amount matched'; if total amount matched is £100 or more than the last refresh and the odds are greater then....

Column BH will update with the latest odds
Column BI will update with 'Total amount matched' to compare on the next BA refresh
Column BJ updates with the current variance between previous 'Total amount matched' and current 'Total amount matched'

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Columns.Count = 16 Then

Set rng = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("BH5:BJ55")
        strArray = rng
        BA_Changes = Target
       
    If ThisWorkbook.Sheets(Target.Worksheet.Name).Range("E2") = "In Play" And ThisWorkbook.Sheets(Target.Worksheet.Name).Range("F2") <> "Suspended" Then
       

        For I = 5 To UBound(BA_Changes)
               
                'track highest matched price over £100

               
               
               
                Select Case strArray(I - 4, 1)
                Case "", Is < BA_Changes(I, 15)
                If BA_Changes(I, 15) <> 0 And (BA_Changes(I, 16) - strArray(I - 4, 2)) >= 100 Then
                strArray(I - 4, 1) = BA_Changes(I, 15)
                End If

                End Select
                strArray(I - 4, 3) = BA_Changes(I, 16) - strArray(I - 4, 2)
                strArray(I - 4, 2) = BA_Changes(I, 16)
               

           
               
        Next I

    Else

        For I = 1 To UBound(strArray)

                strArray(I, 1) = ""
                strArray(I, 2) = ""
                strArray(I, 3) = ""
        Next I
       
       
    End If
    ThisWorkbook.Sheets(Target.Worksheet.Name).Range("BH5:BJ55").Value = strArray
End If
Application.EnableEvents = True
End Sub


[osknows]

Should have also said that this is set up to monitor in play currently so change accordingly

[doris_day]

Thanks for that. I think my only problem may be that I'd also like to see the highest price matched prior to monitoring if that's possible as per the Traded and Available table on the site.
This is because I'll only be opening the market about 10 mins prior to the start of the race. (I should have mentioned this at the outset )