Extracting Race Distance from Excel

[laffo16]

Hi guys, just a programmin question. I'm trying to extract the race distance in furlongs but not sure which way to go about it. Lets say here are some example race names as taken from cell A1.

Leic 9th Sep - 14:20 7f Mdn Stks
Bev 9th Sep - 14:30 1m Mdn Stks
Ling 9th Sep - 14:40 7f Nursery
Bev 9th Sep - 14:00 To Be Placed
Leic 9th Sep - 15:50 1m2f Mdn Stks
Crayfd 9th Sep - 14:27 S4 540m
Sund 9th Sep - 17:17 HC 640m

I start off my extractin the text between the 5th and 6th " " spaces.
Check if last character is "m" or "f"
Remove all "m" & "f" characters and check remaining characters are numbers only

Do you think this would be enough to certify that a race distance either exists or doesnt in the race name? any ideas guys. The thing is i dont know if this format of " " spaces is consistent, cus if that changes then this method is out the window.

also the greyhound race "Crayfd 9th Sep - 14:27 S4 540m" could easily be mistaken if the S4 wasnt included.

[sjaak1943]

=ALS(DEEL(A1,VIND.ALLES(":",A1)+6,1)=" ",DEEL(A1,VIND.ALLES(":",A1)+4,2),DEEL(A1,VIND.ALLES(":",A1)+4,4))

ALS=IF
DEEL=PART
VIND.ALLES=FIND EVERYTHING

Hope this helps!

Sjaak

[laffo16]

intresting, thanks for taking the time to reply

[laffo16]

have you used this method in the past sjaak, does it work correctly with all tracks and all the different race name conventions?

i think on "Bev 9th Sep - 14:00 To Be Placed" it would return "To" without a IsNumber parameter in their. I want it to return "" blank if this is the case, so it would return blank on all Place & Greyhound markets.

I still think i'll stick with the Space " " find param unless their is any reason otherwise to use ":".

[laffo16]

does this happen at all does anyone know:
"Leic 9th Sep - 14:20 10f Mdn Stks"
or is the furlong always converted to the mile every 8.

[sjaak1943]

I only used it on "win" horseracing.

[laffo16]

sjaak movin on, do you use any kind of race time estimations with your scripts? without going into ne great lengths of research i composed some data.

name dist class time avg
Bath 5 5 75 15
Bath 5 5 74 14.8
Bath 11 5 161 14.63636364
Bath 13 5 187 14.38461538
Folkestone 9 4 129 14.33333333
Folkestone 12 5 165 13.75
Thirsk 16 4 219 13.6875
Bath 8 6 109 13.625
Bath 8 5 108 13.5
Thirsk 8 4 106 13.25
Bath 5 6 65 13
Kempton 16 4 207 12.9375
Folkestone 7 5 90 12.85714286
Folkestone 7 6 90 12.85714286
Folkestone 7 5 89 12.71428571
Thirsk 7 4 89 12.71428571
Folkestone 6 4 76 12.66666667
Thirsk 6 5 76 12.66666667
Thirsk 6 5 76 12.66666667
Kempton 11 4 138 12.54545455
Kempton 12 1 150 12.5
Kempton 7 3 87 12.42857143
Thirsk 5 5 62 12.4
Folkestone 5 4 61 12.2
Kempton 7 4 85 12.14285714
Kempton 8 2 97 12.125
Kempton 6 1 71 11.83333333

then i just put some tests together (in secs)
this is using an average of 13 secs per furlong.
furl 100% 80% in
5 65 52
6 78 62.4
7 91 72.8
8 104 83.2
12 156 124.8
14 182 145.6
16 208 166.4
18 234 187.2

basicly im trying to have my script jump in at an estimated % during the race. i really dont know how to go about this properly. or how to tell if a race is NH or Flat from the racename and how this migt effect the average time.

[KevinTHFC]

Hi Mate,

I parse the race details from the Racing Post card and pass the race distance string to the following function.

The RP can have has distances such as 1m1f25y. The function takes the string and converts it to decimal furlongs.

Kevin

Code: Select all

Function RaceLength(RaceLengthString As String) As Double

Select Case Len(RaceLengthString)
Case 2
        If (Right(RaceLengthString, 1) = "m") Then
                RaceLength = Val(RaceLengthString) * 8
        Else
                RaceLength = Val(RaceLengthString)
        End If
Case 4, 5
       
        If (Right(RaceLengthString, 1) = "f") Then
                RaceLength = Val(Left(RaceLengthString, 1)) * 8 + Val(Mid(RaceLengthString, 3, 1))
       
        ElseIf (Mid(RaceLengthString, 2, 1) = "f") Then
                RaceLength = Val(Left(RaceLengthString, 1)) + (Val(Mid(RaceLengthString, 3, 3)) / 220)
        Else
                RaceLength = Val(Left(RaceLengthString, 1) * 8) + Val(Mid(RaceLengthString, 3, 1) / 220)
        End If

 Case 6
       
        If (Right(RaceLengthString, 1) = "f") Then
                RaceLength = Val(Left(RaceLengthString, 1)) * 8 + Val(Mid(RaceLengthString, 3, 1))
       
        ElseIf (Mid(RaceLengthString, 2, 1) = "f") Then
                RaceLength = Val(Left(RaceLengthString, 1)) + (Val(Mid(RaceLengthString, 3, 3)) / 220)
       
        ElseIf (Mid(RaceLengthString, 4, 1) = "f") Then
       
                RaceLength = Val(Left(RaceLengthString, 1) * 8) + Val(Mid(RaceLengthString, 3, 1) + Val(Mid(RaceLengthString, 5, 1) / 220))
       
        Else
                RaceLength = Val(Left(RaceLengthString, 1) * 8) + Val(Mid(RaceLengthString, 3, 3) / 220)
        End If
 
 Case 7
        If (Mid(RaceLengthString, 4, 1) = "f") Then
                RaceLength = Val(Left(RaceLengthString, 1)) * 8 + Val(Mid(RaceLengthString, 3, 1)) + Val((Mid(RaceLengthString, 5, 2)) / 220)
       
       
        End If
       
 Case 8
        If (Mid(RaceLengthString, 4, 1) = "f") Then
                RaceLength = Val(Left(RaceLengthString, 1)) * 8 + Val(Mid(RaceLengthString, 3, 1)) + Val((Mid(RaceLengthString, 5, 3)) / 220)
       
       
        End If
End Select


End Function

[sjaak1943]

I'm not going as far as that!

[laffo16]

haha cool thanks kevin. which pages do you scan through to find the right page you need with the track distance info. say eg,

1st page, then get todays
http://www.racingpost.co.uk/horses/a_da ... =2008-9-10

find todays courses, then load
http://www.racingpost.co.uk/horses/a_da ... 10&crs=DON
...&crs=KEM
...&crs=UTT

ahhh i didnt see it before but on the view all markets (card_meeting) bit
http://www.racingpost.co.uk/horses/card ... =2008-9-10
the race distance is at the top right of each race.

is the "&crs=" input always the first 3 chars of the track?

[KevinTHFC]

I have automated the process so that I only have to enter the date for the days cards that I want.

Basically the program will go to http://www.racingpost.co.uk/horses/runners_index.sd?r_date=2008-9-10 and searches for the links to each race_id keeping a record of each unique one found.

I can then construct the address for each page in turn for the days racing using the race_id and date such as http://www.racingpost.co.uk/horses/card.sd?race_id=464591&r_date=2008-9-10

The race distance always comes between the prize money and the going.

Kevin

[knot]

http://www.gruss-software.co.uk/forum/v ... highlight=