1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. There seems to be a problem with some alerts not being emailed to members. I have told the hosts and they are investigating.
    Dismiss Notice
Welcome to the UK Betting Forum. Please consider registering, it is free!

Help required

Discussion in 'Spreadsheets' started by Matt Jarvis, Sep 24, 2017.

  1. Matt Jarvis

    Matt Jarvis Foal

    Joined:
    Mar 16, 2014
    Messages:
    4
    Likes Received:
    6
    Hi all,

    Just looking for some help/advice on a project I'm working on involving the Winning Distance markets.

    I've got individual spreadsheets for each track for every race they've held since the start of 2010 to the end of 2016.
    In this spreadsheet, it has the following info (Date , Distance , Going , RCode , Type , Class , Name , Winning Distance).
    What I want to do is to create something whereby I will enter the course name, going for the day, Distance, Class, RCode & Type and it will bring me back the average winning distance for that set criteria. I would also like it to highlight any anomalies where the winning distance exceeds a maximum tolerance.

    I've attached an example spreadsheet containing the data for Plumpton.

    Any help would be greatly appreciated.

    Thanks

    Matt
     

    Attached Files:

    dicko14 and markfinn like this.
  2. dicko14

    dicko14 Mare

    Joined:
    Dec 30, 2012
    Messages:
    4,591
    Likes Received:
    2,823
    Matt you will always have problems whilst using the distance criteria as the race course executives keep moving the railing to facilitate better ground etc so the official distance doesn't match the actual race distance.
    Try reading @TheBluesBrother posts on the subject. It's quite scary how far they are out with race distances.
     
    Matt Jarvis likes this.
  3. TheBluesBrother

    TheBluesBrother Gelding

    Joined:
    Feb 9, 2013
    Messages:
    1,457
    Likes Received:
    3,140
    @Matt Jarvis

    Since December 2015 I have been incorporating rail movements into my speed figures.

    The problem you face with your data, is that the actual race distances can be different to what are advertised, after rail movements, at racecourses like Market Rasen and Fakenham for example,
    the race distances on the day can be actual up to 1f further than advertised, one race at Fakenham was actually +257yds.

    In the past two years all the NH and flat racecourses have been re-measured, this will certainly affect your data.

    Looking at your Plumpton data, I took a look at last Sunday's fixture.

    http://www.britishhorseracing.com/racing/results/fixture-results/#!/2017/1331

    Going/Track
    Good,Good to Firm in Places-Watered (GoingStick: 8.2 Hurdle 8.5 Chase on Sunday at 08:15)

    Rails: Common bends with rails from innermost line Hurdle IN 5yds, Chase OUT 5yds. Approx effect on distances.

    Race 1 -60yds (add 4.0s)
    Races 2,4&7 -45yds (add 3.0s)
    Race 3 +90yds (subtract 6.0s)
    Race 5 -75yds (Add 5.0s)
    Race 6 +60yds. (subtract 4.0s)

    A mixed bag here, short and plus race distances.

    So in conclusion, using actual advertised race distances and beaten lengths as variables would not be linear, they have nothing in common.

    Mike.
     
    Last edited: Sep 26, 2017
    Matt Jarvis likes this.
  4. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,917
    Likes Received:
    13,852
    Hi @Matt Jarvis

    Have you any experience with using VBA with excel?
    You will be able to do what you want very easily with a bit of VBA code.

    AR
     
    Matt Jarvis likes this.
  5. Matt Jarvis

    Matt Jarvis Foal

    Joined:
    Mar 16, 2014
    Messages:
    4
    Likes Received:
    6
    Hi Ark,

    I have some but very limited.

    As for the other posts, thank you very much.
     
  6. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,917
    Likes Received:
    13,852
    I'll post up something later that will give you guide as to what you could do.
     
    dicko14 likes this.
  7. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,917
    Likes Received:
    13,852
    @Matt Jarvis
    So generally for this sort of thing you will want to first select the sheet you are interested in and then sort it on some criteria.

    Code:
    Sheets("Plumpton").Select
    Range("A2").Select
    So the above code will select a sheet called Plumpton and will then select cell A2.
    Code:
     Columns("A:H").Select
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
            "B2:B10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
            "D2:D10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
            "F2:F10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
            "C2:C10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Plumpton").Sort
            .SetRange Range("A1:H10000")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    The above code will sort the data in columns A to H first on Distance, then RCode, then Class and Finally Going. After sorting the sheet will look like this
    upload_2017-9-30_20-15-57.png

    Obviously you can change the sort preferences to suit your needs.
    You will note that the range of cells I am selecting for the sort goes upto row 10,000 but there are only 736 rows in the sheet you posted. This is a quick and dirty way to allow you to add new data without having to change the code.
     
    dicko14 and markfinn like this.
  8. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,917
    Likes Received:
    13,852
    So once the sheet is sorted to your needs you can the loop round the cells to do the sums you need.

    Lets say we want to find the average winning distance for Class 4 races at 2miles run on Good to Firm going.

    The following code will search through the sheet until it either finds the first race at 16F or comes to the end of the data and finds an empty cell

    Code:
    Range("A2").Select
        Do Until ActiveCell.Offset(0, 1).Value = 16 Or ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
        Loop
    ActiveCell.Offset(0, 1).Value = 16 means from the current selected cell look in the one to the right for a value of 16, ie 2miles.

    So effectively if the value in row B is not equal to 16 then we do ActiveCell.Offset(1, 0).Select which means move the cursor down one row.

    At the end of the loop we will now be here on the sheet.
    Capture.JPG

    Now we need to find Class 4 Races run on GF

    Code:
    NumberofRaces = 0
      TotalWinningDist = 0
      Do While ActiveCell.Offset(0, 1).Value = 16
    
      If ActiveCell.Offset(0, 5).Value = 4 And ActiveCell.Offset(0, 2).Value = "GF" Then
    
      NumberofRaces = NumberofRaces + 1
      TotalWinningDist = TotalWinningDist + ActiveCell.Offset(0, 7).Value
    
      End If
      ActiveCell.Offset(1, 0).Select
    
      Loop
    
    So the While Loop will continue to run until the race distance is no longer 16F.
    Inside the loop we check for Class 4 races on GF, if the race doesn't match we move on to the next 16F race.

    Once we find a qualifying race we can count the number of qualifying races and sum the winning distances.
     
    Last edited: Sep 30, 2017
    dicko14 and markfinn like this.
  9. ArkRoyal

    ArkRoyal Administrator

    Joined:
    Dec 28, 2012
    Messages:
    12,917
    Likes Received:
    13,852
    Then we can display the result, or write it out to the sheet...

    Code:
    If NumberofRaces > 0 Then
            MsgBox "Average Winning Distance = " & Round(TotalWinningDist / NumberofRaces, 2)
        Else
            MsgBox "No Qualifying Races"
    
        End If
    So in total the code looks like this

    Code:
    Sub Avg_WinningDist()
    
        Dim NumberofRaces As Byte
        Dim TotalWinningDist As Double
    
    
        Sheets("Plumpton").Select
        Range("A2").Select
        Columns("A:H").Select
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
                                                                       "B2:B10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                                                                  xlSortNormal
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
                                                                       "D2:D10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                                                                  xlSortNormal
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
                                                                       "F2:F10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                                                                  xlSortNormal
        ActiveWorkbook.Worksheets("Plumpton").Sort.SortFields.Add Key:=Range( _
                                                                       "C2:C10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                                                                  xlSortNormal
        With ActiveWorkbook.Worksheets("Plumpton").Sort
            .SetRange Range("A1:H10000")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
        Range("A2").Select
        Do Until ActiveCell.Offset(0, 1).Value = 16 Or ActiveCell.Offset.Value = ""
            ActiveCell.Offset(1, 0).Select
        Loop
        NumberofRaces = 0
        TotalWinningDist = 0
        Do While ActiveCell.Offset(0, 1).Value = 16
    
            If ActiveCell.Offset(0, 5).Value = 4 And ActiveCell.Offset(0, 2).Value = "GF" Then
    
                NumberofRaces = NumberofRaces + 1
                TotalWinningDist = TotalWinningDist + ActiveCell.Offset(0, 7).Value
    
            End If
            ActiveCell.Offset(1, 0).Select
    
        Loop
        If NumberofRaces > 0 Then
            MsgBox "Average Winning Distance = " & Round(TotalWinningDist / NumberofRaces, 2)
        Else
            MsgBox "No Qualifying Races"
    
        End If
    
    
    End Sub
    You can copy and paste the code above and run it on your Sheet, have a play and see how you get on and feel free to ask any questions.
    Note that the code presented isn't necessarily the best way to do what you want but is an attempt to show you how you can start to do what you want in small steps.
     
    Last edited: Sep 30, 2017
    dicko14 likes this.

Share This Page