Anyone want a horse racing database ?

[Fredcoteri]

Re: Anyone want a horse racing database ? Have system around your methods. Would be interested as I have different slant.

[Datapunter]

Re: Anyone want a horse racing database ? That's a little cryptic fred :lol The purpose of this thread is that i provide some early samples to those who might be interested in a horse racing database so that you can provide me with some early feedback. So the question is what would make a database like this practical and useful to you ?

[philipos]

Re: Anyone want a horse racing database ? Hi Datapunter, I just wanted to check if this thread is still active. I'd like to help develop and use a racing database of the type you have all been discussing here. Also would live to devise an effective way to keep it updated. Please let me know if this is still active.

[Datapunter]

Re: Anyone want a horse racing database ? Sure is, just hit a few problems called bugs that take a while to solve, last sample 4 posts back is pretty representative and about 90% complete.

[kmabet]

Re: Anyone want a horse racing database ?

Sure is, just hit a few problems called bugs that take a while to solve, last sample 4 posts back is pretty representative and about 90% complete.

There's no content in that sample link mate? Just to let you know that's all.

[Datapunter]

Re: Anyone want a horse racing database ?

Update time.... Attached is the first week of 2007 for anyone who wants an early look. ( can't seem to attach anything bigger, good sign means loads of data :lol )

Post nr. 31 , Just checked, looks pretty filled to me ???

[kmabet]

Re: Anyone want a horse racing database ? When I open or Save it mate, there's nothing in the file! Can't be so much info if the size is only in kb's?

[Datapunter]

Re: Anyone want a horse racing database ? Unzips to 2Mb, save it then maybe try a different program to unzip ? ( unzips into a directory that starts with SportsBetting/... etc )

[philipos]

Re: Anyone want a horse racing database ? Hi Datapunter, I was looking at your database, and trying to figure it out. What field are the results contained in (i.e., which horse come 1st, 2nd, 3rd)? Also, any plans to put past performance data in there? Thanks,

[evergreenfc]

Re: Anyone want a horse racing database ? Somebody was kind enough to write this code for getting results from the sporting life to use in the database. It gets data for 1 race at a time and then you have to edit it by changing the race number. Is it possible to make it grab all the races somehow, maybe by entering the start and finish racenumber? Sub ReadSLTable() 'Freeze screen while processing Application.ScreenUpdating = False 'create variables to store values 'these names could be anything you choose Dim myURL As String Dim myrow As Integer Dim ie As Object Dim doc As Object Dim mytable As MSHTML.HTMLTable Dim mytablecell As MSHTML.HTMLTableCell Dim Pos As String Dim Draw As String Dim Btn As String Dim Horse As String Dim Wgt As String Dim Jockey As String Dim Trainer As String Dim Age As String Dim SP As String Dim Comments As String 'start row myrow = 1 'open internet explorer Set ie = CreateObject("InternetExplorer.Application") 'go to result page for RaceID 356424 'try using another raceid changing the number below e.g. 357103 or 356410 myURL = "http://horses.sportinglife.com/Full_Results/0,12493,356424,00.html" ie.navigate myURL 'Wait for the page to load Do While ie.busy: DoEvents: Loop Do While ie.ReadyState 4: DoEvents: Loop Set doc = ie.document 'Find "racecard_hdr" DIV element in page Dim RaceHeaderDIV As MSHTML.HTMLDivElement Set RaceHeaderDIV = doc.getElementById("racecard_hdr") 'replace carriage returns/linefeeds with ";" to allow easy parsing raceheader = Replace(RaceHeaderDIV.innerText, Chr(13) & Chr(10), ";") 'Find "race_wintime_detail" SPAN element in page Set winSpan = doc.getElementsByTagName("Span") For Each winSpan In doc.all Select Case winSpan.className Case "race_wintime_detail" wintime = winSpan.innerText Case Else End Select Next 'Add wintime to raceheader raceheader = raceheader & ";" & wintime 'Split raceheader into cells using ";" as delimiter Dim myarray As Variant myarray = Split(raceheader, ";") 'read from start to end of array 'write contents into separate cells For x = LBound(myarray) To UBound(myarray) Cells(myrow, x + 1) = Trim(myarray(x)) Next x 'leave blank row between raceheader and resulst myrow = myrow + 2 'Now get results 'Results info is in table 6 Set mytable = doc.all.tags("table").Item(6) 'Add Column Headings in excel sheet "Results" With ActiveWorkbook.Sheets("Results") .Cells(myrow, 1) = "Pos" .Cells(myrow, 2) = "Draw" .Cells(myrow, 3) = "Btn" .Cells(myrow, 4) = "Horse" .Cells(myrow, 5) = "Wgt" .Cells(myrow, 6) = "Jockey" .Cells(myrow, 7) = "Trainer" .Cells(myrow, 8) = "Age" .Cells(myrow, 9) = "SP" .Cells(myrow, 10) = "Comments" 'Format Heading row in bold .Range(Cells(myrow, 1), Cells(myrow, 10)).Font.Bold = True End With 'Read through the table starting at first result skipping a row each time (Step 2) ' i.e rows 1,3,5 etc 'the first row in the table is 0 'i represents the row number in the table For i = 1 To mytable.Rows.Length Step 2 'increment row number for output, putting each record in new row myrow = myrow + 1 'Refer to individual table cell and get the innertext 'First column is 0 'Continue if an error occurs On Error Resume Next Pos = mytable.Rows(i).Cells(0).innerText Draw = mytable.Rows(i).Cells(1).innerText Btn = mytable.Rows(i).Cells(2).innerText Horse = mytable.Rows(i).Cells(3).innerText 'Add ' before Wgt to avoid it being read as date by excel Wgt = "'" & mytable.Rows(i).Cells(4).innerText Jockey = mytable.Rows(i).Cells(5).innerText Trainer = mytable.Rows(i).Cells(6).innerText Age = mytable.Rows(i).Cells(7).innerText 'Add ' before SP to avoid it being read as date by excel SP = "'" & mytable.Rows(i).Cells(8).innerText 'Race comments is the row beneath the horse result so use i+1 Comments = mytable.Rows(i + 1).Cells(1).innerText 'Write results to Excel sheet "Results" With ActiveWorkbook.Sheets("results") .Cells(myrow, 1) = Pos .Cells(myrow, 2) = Draw .Cells(myrow, 3) = Btn .Cells(myrow, 4) = Horse .Cells(myrow, 5) = Wgt .Cells(myrow, 6) = Jockey .Cells(myrow, 7) = Trainer .Cells(myrow, 8) = Age .Cells(myrow, 9) = SP .Cells(myrow, 10) = Comments End With 'if non runner then skip back 1 row before going forward 2 If Pos = "NR" Then i = i - 1 End If 'Clear variables Pos = "" Draw = "" Btn = "" Horse = "" Wgt = "" Jockey = "" Trainer = "" Age = "" SP = "" Comments = "" 'move to next row 'if non runner then i=i-1 Next i 'clean up objects from memory and quit internet explorer Set mytable = Nothing Set mytablecell = Nothing ie.Quit Set ie = Nothing 'Unfreeze excel screen Application.ScreenUpdating = True End Sub evergreenfc

[slackbetter]

Re: Anyone want a horse racing database ?

I take it by Horse-table you mean the runners in a race, then that would basically be it, i just got seperate tables for horse,jockey and trainer. What do you understand with RaceNumber ? ---------------------------------------------------------------------- Ah, the subject of duplicate data... There are a number of fields that for me don't belong there. Strickly speaking a database should hold unique information and it should hold that information only once. Therefore fields like Age, RaceRunners, NumberOfRacesIn30Days, etc... should not be stored in the core DB as that info is already inherently available in the DB, you just need to retrieve it. On the other hand, having that info prepared and readily available can be very usefull from a human user point of view. My personal choice is to have that info available but in a seperate table that can be joined should someone want to do that. But it is something where people will have different opinions on. On that subject, just so i'm completely clear about it, these 3 fields: HorseWonBy(lenghts)(decimal), HorseLostBy(lenghts)(decimal), HorseFinishingTimeSeconds(decimal) could they not be a single field strictly speaking, a single value relative to the WinningTimeInSeconds field can generate those 3 values can't it ???

Hi Datapunter, I've found that the best way to do this is to completey de-normalise the tables. Normalisation is great in theory, but if you plan on running systems tests against all this data you just end up slowing everything down with countless db queries. Every time you tweak a parameter your re-calculating everything. What I ended up doing was a table for races and a table for runners and thats it. One priamry key for the race which is a foreign key in the runners table. My runners table also has loads of denormalised indicators like days since last run etc that could be computed on the fly via queries but I though that was a waste of time. I pull everything into the DB then run a few stored procs to update the indicators. And don't worry, the data isn't too big, 20k races is about 100 meg, very manageable. Re finishing times : They are very subjective. I found it better to keep beaten lengths, I keep these cumulatively so horse 1 gets a zero, horse 2 gets a 1 for one length behind, horse 3 gets 4 if he was 4 lengths behind the winner (3 lengths behind second) etc.. I do this because I don't have finish times for all horses, but you can roughly calc these from beaten lengths. Also good to put loads of checking logic in your programs, the data on the web is pretty dirty with loads of 'irregularities' HTH !

[losing_run_2]

Re: Anyone want a horse racing database ?

Somebody was kind enough to write this code for getting results from the sporting life to use in the database. It gets data for 1 race at a time and then you have to edit it by changing the race number. Is it possible to make it grab all the races somehow, maybe by entering the start and finish racenumber?] re above program I have a scripts that will read yesterdays results and todays runners if anybody is interested, it will also load the last x months worth of results (1 month takes about 4 hours) it is based round a free scripting program, that you will need to install, and internet explorer. it reads from the racing post website i wrote these to help in the creation of my racing database which is ongoing- download results feed into database, download runners feed into database press button and out comes the winners!!! unfortunately a long way to go yet lol

[d4v3]

Re: Anyone want a horse racing database ? Hi Losing Run, I'm interested in your scripts, what language are they written in, and what formatis the output? Are you willing to distribute them? Kind regards dave

[evergreenfc]

Re: Anyone want a horse racing database ? Hello Losing Run, thought this thread was dormant, I would be interested in your scripts and would like a copy if possible. Perhaps you could get in contact via email if you wish? many thanks Davey H

[robert99]

Re: Anyone want a horse racing database ? Hi Losing Run, Me too. Thanks

[thecanaries99]

Re: Anyone want a horse racing database ? Im interested too losing run can it get results as far back as 2005? Thanks

[gamblermax]

Re: Anyone want a horse racing database ? Losing run can you add me to your growing list thanks GM:ok

[Datapunter]

Re: Anyone want a horse racing database ? Apologies for not replying sooner, somehow the email notification of new posts doesn't seem to work. This project for me is on-going but another big programming job has come first. Will be back with a follow up in a few months. Thanks for the feedback slackbetter, will take it on board.

[sumuwin]

Re: Anyone want a horse racing database ? HI LR I'd like to see your script as well - I may be able to speed it up. It shouldn't take 4hrs to get a month's results. all the best sumuwin

[Tull]

Re: Anyone want a horse racing database ? Plenty of talk on here but being a bit of a computer novice I wondered if anyone could advise me? I use patternform to select my horses but to do each race does anyone know if I could speed this process up? I'm mainly after the fastest horse using their ratings over the lifetime, 24m, 12m, 6m & 1m. I then do the same but this time take the top 3 from each timeframe but on the going. I then add up the number of time each horse is mentioned those over 3 make the shortlist. I then apply cousre distance and class to the process the one with the best outcome is a possible selection. Takes ages to complete manually but would be brilliant if a database could do it for me or if there was a way of getting data off patternform. Anyone have anythoughts?

[jimbo45]

Re: Anyone want a horse racing database ? Hi punter and others Here's some GOOD back data (FREE) it's in CSV format so you'll need to get it into EXCEL. Strip off the bits you don't need and then you can massage it and get into Access DB (or MySQL etc) . Great for Back testing -- for example percentage of winners when selecing ist 3 favourites in races say of 1Mile or longer and odds of ist favourite > evens. Creating these types of queries are simple once you've got the data Here it is http://82.153.163.104/Archive_UKHorseRacing/CSVResults/default.asp Cheers jimbo

[slackbetter]

Re: Anyone want a horse racing database ? Hi Jimbo, Thanks for the link ! Glad I spent all that time writing a downloader now .... ;-)

[losing_run_2]

Re: Anyone want a horse racing database ? Hi all who requested scripts i have updated, as the copied script did not save all the code, the ('less than sign'

 
; read the results from the racing post results page 
 
[COLOR=red]; the below 2 lines need to start with the less than sign after #include[/COLOR]
[COLOR=#ff0000][/COLOR]

#include [COLOR=red]put less than sign here[/COLOR]IE.au3>
#include [COLOR=red]put less than sign here[/COLOR]Date.au3>
;/*******************************************************************************\
; Enter start date below
 $sNewDate =   "2008/03/31"
;/*******************************************************************************\ 
 $sracemonth = ""
 $sRaceYear = ""
#region ---Au3Recorder generated code Start ---
Opt("WinWaitDelay",100)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)
do
$sText1 = ""
$sText = ""
$sRaceYear =  stringleft($snewdate,4)
$iRaceNumber = 0
if $sracemonth  _DateToMonth(Number(stringmid($sNewDate,6,2))) then 
 $sracemonth = _DateToMonth(Number(stringmid($sNewDate,6,2)))
DirCreate ("C:\racing\web txt\" & $sracemonth & "-" & $sRaceYear  )
endif
$dRaceDate1 = StringReplace($sNewDate, "/", "-")
 
DirCreate ("C:\racing\web txt\" & $sracemonth & "-" & $sRaceYear & "\" & $dRaceDate1 )
$Web_address = "[URL]http://www.racingpost.com/horses2/results/home.sd?r_date[/URL]=" & $dRaceDate1
ShellExecute ("iexplore.exe", "about:blank")
WinWait ("Blank Page")
$oIE = _IEAttach ("about:blank", "url")
_IELoadWait ($oIE)
_IENavigate ($oIE, $Web_address)
;$Web_address = "[URL]http://www.racingpost.com/horses2/results/home.sd?r_date[/URL]=" & $dRaceDate1
;$oIE = _IECreate ($Web_address)
sleep (20)
$oLinks = _IELinkGetCollection ($oIE)
$iNumLinks = @extended
if $inumlinks > 0 Then
 $inum = 0
 $sText1 = $olinks(0).href
 For $oLink In $oLinks
  if $inum >= 5 then
   $sText2 = $oLink.href
   if $sText2 = $sText1 Then
    $istrcount = StringInStr($stext2, "race_id",2)
    if  $istrcount > 0 then
     $oIE_1 = _IECreate ($sText2)
     _IELoadWait ($oIE_1)
     $iRaceNumber = $inum
     $sText = _IEBodyReadText ($oIE_1)
 
;/*******************************************************************************\     
; Enter a new path below for the saved files
$file = FileOpen("C:\racing\web txt\" & $dRaceDate1 & "-R-" & $iRaceNumber & ".txt", 9)
;/*******************************************************************************\
; Check if file opened for writing OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
FileWrite($file, $sText)
FileClose($file)
     _IEQuit ($oIE_1)
     sleep(50)
     $sText1 = "done"
    endif    
   else 
    $sText1 = $sText2
   EndIf 
  endif
 $inum = $inum +1
 Next
endif
sleep (50)
_IEQuit ($oIE)
;sleep (2000)
;/*******************************************************************************\     
; Enter a new path below for the saved files
FileMove("C:\racing\web txt\*.txt", "C:\racing\web txt\" & $sracemonth & "-" & $sRaceYear & "\" & $dRaceDate1 , 9)
;/*******************************************************************************\
sleep (50)
$sNewDate = _DateAdd( 'd',-1, $sNewDate)
$PID = ProcessExists("iexplore.exe") ; Will return the PID or 0 if the process isn't found.
If $PID Then ProcessClose($PID)
 
;/*******************************************************************************\
; Enter end date below
until $sNewDate = "2007/12/31"
;/*******************************************************************************\
exit
#region --- Internal functions Au3Recorder Start ---
Func _WinWaitActivate($title,$text,$timeout=0)
 WinWait($title,$text,$timeout)
 If Not WinActive($title,$text) Then WinActivate($title,$text)
 WinWaitActive($title,$text,$timeout)
EndFunc
#endregion --- Internal functions Au3Recorder End ---
#endregion --- Au3Recorder generated code End ---

[losing_run_2]

Re: Anyone want a horse racing database ? sorry to be ignorant Tull but what is patternform ?

[slackbetter]

Re: Anyone want a horse racing database ?

sorry to be ignorant Tull but what is patternform ?

LR2 : http://www.patternform.co.uk/ And to answer Tulls question, yes this could be automated, almost anything web-based can be automated, but its a non-trivial piece of work.

[sumuwin]

Re: Anyone want a horse racing database ? Hi Tull I don't use PatternForm but a few years ago I wrote some code in Excel (2003) that would open PatternForm in an browser in Excel and with a few clicks, copy the data e.g. Lifetime, Course, Going etc into separate sheets depending on what you selected. If you want to send me a sample of what you do for one race I'll see if I can adapt this to do what you need. (If anyone wants a copy of the spreadsheet just pm me.) atb sumuwin

[TGM]

Re: Anyone want a horse racing database ? Thanks for that Losing Run. When I get it to work that will be just what I need. It creates the folders on my C drive, opens IE but doesn't navigate to the website. Any ideas? FIXED IT: Replaced WinWait ("Blank Page") with WinWait ("about:blank") Cheers, Scott

[slackbetter]

Re: Anyone want a horse racing database ?

Hi punter and others Here's some GOOD back data (FREE) it's in CSV format so you'll need to get it into EXCEL. Strip off the bits you don't need and then you can massage it and get into Access DB (or MySQL etc) . Great for Back testing -- for example percentage of winners when selecing ist 3 favourites in races say of 1Mile or longer and odds of ist favourite > evens. Creating these types of queries are simple once you've got the data Here it is http://82.153.163.104/Archive_UKHorseRacing/CSVResults/default.asp Cheers jimbo

A little disappointed that these CSV's don't include the official class. If they have the rest of the info they obviously have the official class and would have been trivial to include in the files. They have 90 fields, whats one more ? Also a pain that they can't maintain a standard file format or naming system... Oh well, what did I expect for free ? :D Back to using my own downloader...

[losing_run_2]

Re: Anyone want a horse racing database ? hi Slackbetter what sort of downloader are u using wish to share ?

[slackbetter]

Re: Anyone want a horse racing database ?

hi Slackbetter what sort of downloader are u using wish to share ?

I'll answer that in two parts... 1. My downloader is some fairly trivial perl scripts. They use 'wget' on linux to do the download. The more difficult part is actually parsing that HTML file once its downloaded, I have been doing this in perl as well, but moving everything over to java now. I might release something when converted to java, but will be a while.... 2. My main concern with sharing is that I have 30 second delays in my downloads, so its very slow but also it doesn't hammer the website too much. If distributed, I imagine the delays would be removed and it will hammer the website. Maybe I'm worried over nothing (I imagine these sites get hit pretty hard particularly on big race days anyway). Having said all that, I wonder if a better approach to sharing the scripts is actually to share the data ? Surely its better for someone to put together data for say start of 2005 - end of 2009 and any systems testers can use that for back testing ? This is why I was hoping the ukhorseracing.co.uk data would be good (but it isn't, its missing some key data)