I have a MySQL database of historic horse racing results. I want to run processes that will make racing selections. They will first need to select and eliminate races on the basis of eligibility criteria. Then they will select horses from the qualifying races on the basis of their previous results. This process will involve searching through the database, matching on the name of the horse, and then performing a calculation based on values for this race and the previous races it ran in. The script needs to produce a report including the results and profit or loss of the system.
The manual describing the database is available at:
[url removed, login to view]
I want to remain flexible on the details of exactly how the calculation is made and so I will need to be able to edit the code myself. The code will be in SQL. I also require assistance with extracting data to Excel and creating a new database that is a fragment of the current one. I have shared the structure of the database here:
It may be possible to provide some sample data but I will need help in providing this.
Skills required: SQL, An interest in horse racing is desirable but not essential.
1. Explain how to produce a fragment of the database (which may be used for development purposes).
2. Output the data in historic_runners in to separate Excel files for each year of the data.
3. Distance and Going Project
a. Select races in a particular date range and of a particular type.
b. Select horses that have performed well in each of their preceding races (either won or finished in the first quarter of the field) and which have only run over a greater distance than the race under consideration, or which have only run over a lesser distance than the race under consideration. (A horse that has run over both a greater and lesser distance is does not qualify on this basis) or which have only run over softer, or only over firmer ground, than the going in the race under consideration (a horse that has run over both softer and firmer ground does not qualify on this basis.)
4. Handicap rating project
a. Filter on type of race in which to bet (handicaps only, not All weather (AW)).
b. Filter on type of race in which to make a calculation (again only handicaps and not AW, also must be within a certain date range, say one year, of the race under consideration.)
c. A table needs to be constructed for a multiplying factor used in the calculation that depends of the length of each form race.
d. Calculate a value for each horse based on this formula:
V = ORf – ORt - (F*D(lengths))
ORf is the Official rating of the form race
ORt is the Official rating of the race under consideration. (“Today’s race.”)
F is the factor that depends on the length of the face.
D is the distance in lengths the horse finishes behind the third place horse (this value will be zero for horses which finished third, and negative for horses which won or finished second.)
The horse with the highest rating for any of its eligible races is the selection for the race.
5. For both systems produce a report of the result of betting on each selected horse, which is displayed in the command window and output to an excel spread sheet.
Please let me know if you have any other questions.