Anyone want a horse racing database ?

[Datapunter]

Working on a project whereby i'll be providing a horse racing database. Intention is to have it available in multiple fileformats like csv,xml,xls and also direct in DB format like MySQL. ( There will be a price to pay eventually but way too early to say anything on that except that i'm hoping to keep it as low as possible ) At the moment i'm looking for a couple of Beta-testers. Would like at least one person who knows his way around databases, but not an expert, just an average database user. And one person who might be interested in using a non-DB format like excel. Most important really would be that you have some purpose for wanting a horse racing database / data. Tell me that and i can try out different fileformats and filestructures to see what fits best.

[BillyHills]

Re: Anyone want a horse racing database ? I would be interested in the Excel format. I compile my own ratings on hurdlers and at the moment do it on the Racing Post website but dont have a database at home so if the Post ever goes i'm in trouble. I like working in excel and havnt had much experience in using database software. If thats any use give us a shout.

[King Theoden]

Re: Anyone want a horse racing database ? I can use Mysql. I would like to get know British racing and racing betting better. Things I'm interested in are for example compile some sort of speedratings, seeking what things people undervalue and overvalue odds-wise etc. I can give you also feedback to further develop your database.

[Datapunter]

Re: Anyone want a horse racing database ? BH, how would you do that if you had the data in excel only ? Obvious problem would be how far back in time do you need to go, and what file format would be suitable for that. Assuming you don't want to start using an actual database which would reallly be the way to go. You can have a sheet with the cards/results for the day for example. But if you need to get for example the last 6 results of a horse in order to compile a rating then it will get quite difficult to do that in excel. You would basically need an additional sheet per horse with it's history or something to that effect. So to get practical: how are your ratings compiled and how could that be done inside excel ?

[Datapunter]

Re: Anyone want a horse racing database ? King Theoden, lovely, let's try out some things. For starters can you see if you can map out the structure of the database for the key entities. I won't say anymore as i want to see if you'll come up with the same structure as me. Then find out what data you need to compile speed ratings and i'll see if i can get my hands on it. I should have a beta-version for MySQL ready sometime next weekend and then we'll have a chat and get down to some practicalities. :ok

[BillyHills]

Re: Anyone want a horse racing database ? Not sure Data how I could do it to be honest. I work my ratings out by giving the winner a rating and working backwards with how far beaten and what weight carried. In the post you can give the winner a figure and it automatically calculates the rest of the field. I could do this manually but would obviously take much longer. I save the ratings for each horse and then when I come to a new race it puts the highest rating adjusted to todays weights in for me. To be honest I was just thinking of a way of doing it without being dependant on the Post, maybe keep a full list of my horses and then recall the ratings when needed for each one, even if it meant working them out manually. Maybe its a non starter and a database is what i'm after. I only do novice hurdles so there are only a couple of races a day at the moment.

[Datapunter]

Re: Anyone want a horse racing database ? Sounds like you want a database that is kept up-to-date with as little effort as possible. With the update process you can include a script that generates/updates a custom table if you want. In this step you can already do all sorts of calculations. Then you need another script that generates an output from the database into .csv which you can then use with excel. Actually any calculation done can be in the DB or in excel, that is basically a matter of choice. So even if you want minimal DB you can use that to produce basic excel sheets and then use those as the basis for calculations.

I work my ratings out by giving the winner a rating and working backwards with how far beaten and what weight carried. In the post you can give the winner a figure and it automatically calculates the rest of the field. I could do this manually but would obviously take much longer. I save the ratings for each horse and then when I come to a new race it puts the highest rating adjusted to todays weights in for me.

Walk me throught the steps here. ( just pretend i know nothing of racing which would be a fair assumption ;) )

[King Theoden]

Re: Anyone want a horse racing database ? Datapunter, in case you are waiting I'll try to do basic structure by friday afternoon. I think I'll need some time to think about this so neither of us doesn't have to do any unnecessary work.

[evergreenfc]

Re: Anyone want a horse racing database ? I'd love a database that would have the following data parameters. Date, Course, Racetime,Distance,Going,Ran,Class,Class(Top BHB),Racetype(Hcp,Mdn,Sell)etc,Age,Draw,Position,DistW/L, Horse,Weight,Rating1,Rating2 I would also like to import results and cards directly from the sporting Life or Racing Post or any other reliable source, and generate the top 9 Handicap performances from the horses last 3 runs,with the option to sort on any field. I was compiling the form in an excel spreadsheet, but it was taking so long to get the results into it that there wasn't enough time to rate any races!! Davey H

[Datapunter]

Re: Anyone want a horse racing database ? King Theoden, take your time mate, having a think is exactly what i'm after. everfreenfc, Class,Class(Top BHB), where do i find each one ? and more important what's the difference ? Rating1,Rating2 which ones wuld these be ? the top 9 Handicap performances from the horses last 3 runs you mean the top 9 horses ? a little confused here ?

I was compiling the form in an excel spreadsheet, but it was taking so long to get the results into it that there wasn't enough time to rate any races!!

:lol i bet there's a whole bunch of people going " O yeah, been there :wall "

[evergreenfc]

Re: Anyone want a horse racing database ? Class / Class BHB I back mainly in all age handicap races, and occasionly in the higher class non handicaps. Class refers to the class of the race eg: 1,2,3,4,5,6,7,G1,G2,G3 BHB class refers to the top rated horse in the race. For example, a handicap race may be framed as 0-85, but if the top rated horse is only rated 79 then any ratings formed by the framed ratings will be 6lbs out. For non handicap races I use the following "marks" for the classes taken from me Racing System Builder. G1=120,G2=115,G3=110,CL1=105,CL2=100,CL3=95,CL4=84,V=CL5=75, CL6=65,CL7=55 rating1 was referring to my own ratings generated from the Class side of the race, rating2 is generated from the BHB mark of the race. I only take into account the last 3 runs of the seasons, so when I am rating the days racing I like to have the top 9 performance ratings,in order,displayed for that race,as each horse diplayed underneath with the last 3 Crse,Dist,going,rating1,rating2. Don't want much do I!!:lol I do not weight adjust anything as in my opinion they have little effect on races, and it is the class of the races contested that makes the difference. In America the weight carried isn't even considered and more importance is placed on the class of the race and the type of "trip" the horse encountered in the race.Each to their own Hope that clarifies things a little bit. Davey H

[King Theoden]

Re: Anyone want a horse racing database ? Hi I thought basic table structure for results database could look like this: Races-table(primary key RaceId): RaceId(int), RaceCourse(Varchar), RaceDate(date), RaceTime(varchar), RaceName(Varchar), RaceNumber(int), RaceConditions(varchar), RaceType(Varchar),RaceClass(int), RaceGoing(varchar), RaceDirection(varchar),RaceDistance(int), RaceFences(number of fences)(int), RaceRunners(number of runners)(int), RaceFinishers(number of finishers)(int), RacePrice(int), WinningTimeInSeconds(decimal) Horses-table(primary key horseId, RaceId) HorseId(int), RaceId(int), HorseName(varchar), HorseDOB(date), HorseForm(varchar), HorseGender(varchar), HorseAge(int), HorseOrigin(country)(Varchar), HorseStallNumber(int), HorseRating(official rating)(int), CreatedRating(own or for example Racing Post's rating)(int),HorseRatingDifference(compared to best rating + or -)(int) HorseHandicapWeight(int),HorseMorningPrice(decimal), HorseStartingPrice(decimal),HorsePriceBetfair(decimal), HorsePlacePrice(decimal), HorsePlacePriceBF(decimal), HorseTrainer(varchar),HorseJockey(varchar), HorseOwner(varchar), HorsePosition(finishing position)(int), HorseWonBy(lenghts)(decimal), HorseLostBy(lenghts)(decimal),HorseFinishingTimeSeconds(decimal), HorseLastRacedInDays(int), NumberOfRacesIn30Days(int), NumberOfRacesin60Days(int) That's what I thought of for starters.

[Datapunter]

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 ???

[Datapunter]

Re: Anyone want a horse racing database ? Structure i got so far, ( just main Key's without data attributes ) Table Horse, Table Jockey, Table Trainer, All 3 having a single primary key. Table Race, Primary key RACE_ID Field: RACEDATE Field: RACETIME Field: RACEORDER Field: COURSE Fields: title,wintime,etc... - raceorder is used in cases where the time is not known - the combination of Date+Time/Order+Course is in itself unique, the RACE_ID is a surrgate key to facilitate the joining of related data - field COURSE actually is also an ID linking to a course table but not strickly required as long as the ID or name of the course is unique Table RaceRunner primary key (RACE_ID,HORSE_ID) Key: HORSE_ID Key: JOCKEY_ID Key: TRAINER_ID Field: Draw Field: Position Fields: weight,official_rating,etc... - as the combination of RACE_ID and HORSE_ID is unique there really isn't much to gain from creating a surrogate RUNNER_ID, althou i may change my mind as i go along ;) Table Race_derived Primary key RACE_ID Field: numberOfRunners Field: places etc... - holds derived data usefull to have readily available, The tables RACE,RACERUNNER,HORSE,JOCKEY,TRAINER are required for all users as they contain the core data. The table Race_derived will be used by many but some will prefer to create and use their own custom version. The table itself is not strickly required and it is up to the individual user if they use it or not. Obviously my point of view is slightly different from yours as i look at the design for a group of different individual users.

[King Theoden]

Re: Anyone want a horse racing database ? By Race Number I mean what is the number of the race of current meeting date. I think it's something I'm keen to know because I would like to see if there's effect on the days earlier results to odds on later races. For example, if Ruby Walsh wins race number 1 and 2 will odds be slashed on the horses he later rides on that day. On that duplicate matter, I don't really think it's an issue. It can be done in both ways. "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 ???" Yes, in fact a field horsesFinaltime would do the same if it's calculated straight from the results(lenghts).

[King Theoden]

Re: Anyone want a horse racing database ?

Structure i got so far, ( just main Key's without data attributes ) Table Horse, Table Jockey, Table Trainer, All 3 having a single primary key. Table Race, Primary key RACE_ID Field: RACEDATE Field: RACETIME Field: RACEORDER Field: COURSE Fields: title,wintime,etc... - raceorder is used in cases where the time is not known - the combination of Date+Time/Order+Course is in itself unique, the RACE_ID is a surrgate key to facilitate the joining of related data - field COURSE actually is also an ID linking to a course table but not strickly required as long as the ID or name of the course is unique Table RaceRunner primary key (RACE_ID,HORSE_ID) Key: HORSE_ID Key: JOCKEY_ID Key: TRAINER_ID Field: Draw Field: Position Fields: weight,official_rating,etc... - as the combination of RACE_ID and HORSE_ID is unique there really isn't much to gain from creating a surrogate RUNNER_ID, althou i may change my mind as i go along ;) Table Race_derived Primary key RACE_ID Field: numberOfRunners Field: places etc... - holds derived data usefull to have readily available, The tables RACE,RACERUNNER,HORSE,JOCKEY,TRAINER are required for all users as they contain the core data. The table Race_derived will be used by many but some will prefer to create and use their own custom version. The table itself is not strickly required and it is up to the individual user if they use it or not. Obviously my point of view is slightly different from yours as i look at the design for a group of different individual users.

I think it actually might be better to have separate tables for trainers etc. It may even be better idea that I leave table structing to you, two cooks don't make good soup ;). To me the most important thing for using a racing database would be that it's automated to get results and odds from the races and maybe the day's racing cards. So I might be better suited to trying find the questions and answers from the database than trying to construct it. Trying the database and finding some valuable info that might be missing from it might be more beneficious to both of us. Would that suit better to you?

[Datapunter]

Re: Anyone want a horse racing database ? Just looking for feedback, you've made me realise i'm probably under-estimating the importance of the derived data like Age, RaceRunners, NumberOfRacesIn30Days. I should give that some more attention as most people will want a lot of that readily available. Now about the update procedure, attached is a dataset for this month with the races of the past few days. Work in progress mind you. ( The dataset also contains the races in .csv , excell, xml if anyone wants to have a look ) The procedure is like this: One time only: 1) you create a MySQL database, name it anything you want. 2) extract the .zip file and rename the .bat file edit it and put you username and databasename inside the .bat file you will run that to update the database. 3) create any script you want to run right after an update, you can add that to the .bat file so it runs each time you update With each update: 1) download the file, as attached here, and extract each time you download the "daily" version it will contain the latest info 2) run the .bat file which will run all the scripts and update the database Is that a practical method ? I would also love to have someone try it out with Access or another database application rather than MySQL as the .sql files should be standard sql and therefore suitable for most database applications. ( just modify the create script accordingly ! )

conv_1097.zip

[evergreenfc]

Re: Anyone want a horse racing database ? You'vre lost me already there. As I know sweet Felicity Arfwright about Databases I take it the zip file can't be used in Access? Just a thought you might want a field for AW and TURF if, like me you don't think form carries across then it would be handy just to have the option to display form from one surface,or both. Davey H

[Datapunter]

Re: Anyone want a horse racing database ? Unfortunately you not knowing Access is something i can do Felicity Arfwright about :lol . I had a quick look but i can't make heads or tails of Access, all those menu's to me is like working with a straightjacket on. Inside the set there's a file called racing_create_database.sql but written for MySQL so needs adjusting for Access. That creates the tables. I'm sure in Access you can run a script somehow. Otherwise you can view the file and see the table structure. Then you run the script run_update_scripts.sql which in turn runs the other .sql files and they enter the data. Those scripts i used standard SQL so they should work on just about any database application.

[sumuwin]

Re: Anyone want a horse racing database ? Hi Datapunter I can take a look at it in Access. I already have several large Access databases and have been intending to migrate to mySQL or SQLServer for a while - just haven't found the time. I may also be able to provide some data that might be of use - pm me if you want to discuss. all the best sumuwin

[Datapunter]

Re: Anyone want a horse racing database ? Well just need someone to put the create script into the right syntax and then run the .sql files, however that works in Access. Then tell me if there actually is a problem somewhere/somehow.

[Datapunter]

Re: Anyone want a horse racing database ? Apparently Access can't run SQL scripts, stupid program :lol Anyhow, i've now created a .CSV file per table that can be imported. ( field names on first row, field separator comma, text separator double quote ) Just don't know how to actually create the tables, guess that will have to be done manually, structure can be seen in the racing_create_database.sq file. Anyone using a database other than MySQL or Access ?

conv_1100.zip

[sumuwin]

Re: Anyone want a horse racing database ? With some modification you can use vbscript to create the database and to provide the updates. The update code could be automated from your SQL files but it's probably easier to create csv files for the user to import. Here's some sample code - copy into a text editor (e.g. notepad) and save the file with extension vbs. Double click the file to run.

To create database C:\sumuwin.mdb
 
strFileName = "C:\sumuwin"
Set objCatalog = CreateObject("ADOX.Catalog")
objCatalog.Create "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & strFileName & ".Mdb" & ";"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & strFileName & ".Mdb" & ";"
 
objConnection.Execute "Create Table Horse(" & _
"HorseID long not null," & _
"Name varchar(25) null," & _
"Registration_country varchar(3) null,[DateofBirth] datetime null," & _
"Sex varchar(25) null , Breeding varchar(255) null, Primary Key (HorseID))"
objConnection.Execute "Create Table jockey(" & _
"JockeyID counter," & _
"lastName varchar(50) default null," & _
"firstname varchar(50) default null,firstinitial char(5) default null," & _
"Title varchar(15) default null , Gender byte default null)"
 
objConnection.Close
Set objCatalog = Nothing
Set objConnection  = Nothing
 
MsgBox strfilename & " has been created."
 

To update Horse details
 
strFileName = "C:\sumuwin"
 
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" & strFileName & ".Mdb" & ";"
 
mySQL = "delete * from horse where HORSEID IN " _
& "(89083,89244,91618,94637,97245,98569,98580,100039)"
objConnection.Execute mySQL
REM create a string containing all the horse fields then create array from the string
REM loop through the array and insert the values
mystr = ""
mystr = mystr & "(89083,'Kaiser Willie','IRE');"
mystr = mystr & "(89244,'Protiva','GBR');"
mystr = mystr & "(91618,'Yellow Printer','GBR');"
mystr = mystr & "(94637,'Winrob','GBR');"
mystr = mystr & "(96063,'Young Star Gazer','GBR');"
mystr = mystr & "(97245,'Niceonefrankie','GBR');"
mystr = mystr & "(98569,'Caucus','GBR');"
mystr = mystr & "(98580,'Top Spin','IRE');"
mystr = mystr & "(100039,'Futurist','GBR')"
myarray = Split(mystr, ";")
For i = 0 To UBound(myarray)
myvalues = myarray(i)
mySQL="Insert into horse (HORSEID,name,registration_country) " _
& " values " & myvalues & ""
objConnection.Execute mySQL
Next
 
objConnection.Close
Set objConnection = Nothing
 
MsgBox "Horses Updated."
 

all the best sumuwin

[Datapunter]

Re: Anyone want a horse racing database ? Thanks for that sumuwin, could use a .vbs script to create the database tables, then import data using .csv files, should be practical enough.

[Datapunter]

Re: Anyone want a horse racing database ? Next step, data. For the UK/IRE only, (US and AUS is slightly different) Race: Race date / course / time Title Wintime race type (maiden/stakes/etc( race age group race distance race "Class" total prize money winner prize money going (simple single word) going (descriptive) race Handicap (true/false) Nr of runners Nr of places (standard calculation) Runners: Entry Draw Position Distance (possibly numeric related to wintime) Horse, name/country / SP Horse age (counted from jan 1st) weight: pounds weight: stone headgear official rating Jockey: name Jockey: allowance Trainer: name Possibly forecast prices ? Possibly owner name ? Possibly horse breeder/pedigree ? From this list would there be anything you'd say is missing and really should be included ?

[princecapri]

Re: Anyone want a horse racing database ? Hey Datapunter, In order to automate rating and selection process (yes, I am lazy :)), I need a database with the following items: Date, Race Type, Course, Class, Going, Class, Total Runners, Runner 1 name, Runner 2 name.........., Runner n name (in csv format). Ideally, it would be good if it goes far back, but an year or two would suffice. It is taking me way to long to manually prepare this list! Aside from this, I have extensive experience in Mumps (cache...dBase management language), and a fairly decent experience in Excel (though don't quite know all the tricks and such). So I could volunteer my time as a beta-tester if you require. But any info on the above request would be great. Thanks :)

[Datapunter]

Re: Anyone want a horse racing database ? Hi princecapri, in a database format it's all split over tables so you can extract it just the way you need it. But for a CSV format would it not make more sense to list the horses in a column rather than a row ? Date, Race Type, Course, Class, Going, Class, Total Runners, Runner 1 name Date, Race Type, Course, Class, Going, Class, Total Runners, Runner 2 name Date, Race Type, Course, Class, Going, Class, Total Runners, Runner 3 name

[princecapri]

Re: Anyone want a horse racing database ? Datapunter, It will actually (which could assist in incorporating other fields that I haven't thought of, like trainer or jockey). Its just that the way I am extracting data at present, I have names of all horses in the same line. That's not to say your method will not work. As a matter of fact, as mentioned, it will be better (as it assists the inclusion of more details).

[d4v3]

Re: Anyone want a horse racing database ? This is quite interesting, I'm in the process of building my own database to automate my system selections. It's too late now to read the thread properly but I'll subscribe & take a look another day. Hopefully I can help with Access.

[Datapunter]

Re: Anyone want a horse racing database ? Update time.... - have now added a lot of detailled fields, still some to do and details to check but getting there - the .sql files are now specific to MySQL so other database users will have to import the tables from the .csv files, there's one per table - no more excel file for now, few problems to sort with that, but the overall .csv file can be read into excel 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 )

conv_1238.zip