Back Or Lay Picks 30th-31st May

Off to the continent for all of today's selections...

MOLDE
NORRKOPING
CAGLIARI
FIORENTINA
ROMA
ROSENBORG


These picks should be backed if decimal odds at KO are 2.00 or over.
They should be laid if their odds are at 1.90 or under.

Racing Database Redesign - Part 14 - Manual Error Checking

I thought I'd take a moment to discuss the importance of periodic checking of your database to ensure that your automated systems are doing what you expect them to do. This is all the more critical when your database pulls in data from a third party to which you have no control over - as is the case for my horse racing database.

I've already mentioned a case in point where a couple of data files from Betfair had empty MENUHINT hint fields. This resulted in importation of data failing because the BFShortName for the venue could not be ascertained.

In that instance, I was able to programmatically check for that missing data and substitute a dummy value to enable the data importation process to continue. I now still have to periodically run the following SQL query to identify any events where the BFShortName is unknown. I can then go in and manually create it.

SELECT * FROM 'event' WHERE 'bfshortname' = 'Unknown';

This weekend, and purely by chance, I discovered another issue which wasn't trapped by the error checking in my script.

The TimeForm site does not seem to use the same values for eventid and selectionid for it's historical results pages as those that are contained within the data files downloaded from Betfair. For some reason, I had always assumed they did. I was in the process of confirming that when I discovered that the race data from the data file released on 22 May 2015, i.e. races held on the 21st, hadn't been placed into the EVENTDETAILS table. All the race data like MinIP odds had all been calulated correctly and entered into the EVENT table, but the data for each individual runner had not been entered into the EVENTDETAILS table.

This had not been trapped by the error checking in my script, yet clearly there was an issue. Take a look at the image below which shows the data file in question as loaded into a spreadsheet:


The highlighted rows illustrate the problem. That data shouldn't be in the file. I've yet to ascertain why the error checking in my script didn't catch it but now I know it is there I can search for it and remove it before the data is sent to the corresponding tables.

This is the issue with third party data. It can be changed without notice or can contain errors the nature of which are impossible to predict. Once identified, code can be put in place to trap those conditions but one can never be certain that some unforeseen condition isn't getting through.

So, like the case of the missing BFShortName above, I will have to periodically check that every race listed in the EVENT table actually has some corresponding runners listed in the EVENTDETAILS table by running the following query:

SELECT * FROM 'event' WHERE 'eventid' NOT IN (SELECT 'eventid' FROM 'eventdetail');

It is manual checking like this that needs to be done periodically to maintain confidence in the integrity not of the database design but of the data itself. The trouble is, if you don't know what to look for, how do you query your database to find it?

That's a difficult question to answer, but hopefully the intimate knowledge that you have with your database design and the data it is meant to contain will guide you in the process.

Back Or Lay Picks 23rd-25th May

Not many selections this weekend as expected. With most of these at very short odds it will not need many to come in to produce a decent return.

PARIS ST-G
BARCELONA
REAL MADRD
LILLESTROM


These picks should be backed if decimal odds at KO are 2.00 or over.
They should be laid if their odds are at 1.90 or under.

Chat Room Requests

There's been a chat room on the Bet Your Life Sports Trading site since Sept 2007... at least until Feb 2014 when, due to lack of interest, I decided to pull the plug. Since the turn of the year I've had a number of enquiries about it and thought I'd pose the question:

If I brought the chat room facility back, would you actually use it?

I'm well aware that there are now many chat rooms available for traders, primarily provided by suppliers in order to support their products and services. That was never the intent of the original chat room, nor would it be if it was resurrected. I am not peddling any products or services so the chat room would be an independent facility where traders can hang out. Neither would I intend spending much of my time maintaining it.

If that sounds like something you might use then please leave a comment below indicating your interest. If there's enough numbers I'll look to setting one up again.

Back Or Lay Picks 19th-21st May

It has been something of a disappointing start to the last quarter of the year having gained only 0.28pts since the start of May. A case of keeping the faith until things pick up again... which they will. In the meantime, it is off to Sweden and Finland for the midweek selections this time around.

NORRKOPING
SJK


These picks should be backed if decimal odds at KO are 2.00 or over.
They should be laid if their odds are at 1.90 or under.

Back Or Lay Picks 16th-17th May

I seem to be taking one step backward for every step forward at the moment. Better than going backwards all the time I suppose...

MONACO
ROSENBORG
NAPOLI


These picks should be backed if decimal odds at KO are 2.00 or over.
They should be laid if their odds are at 1.90 or under.

Back Or Lay Picks 8th-11th May

A handful of picks for this weekend as the main Euro leagues draw to a close and I head North to Scandinavia...

PARIS ST-G
ALLOA
SANDEFJORD
RAYO V.
HJK HELSNK


These picks should be backed if decimal odds at KO are 2.00 or over.
They should be laid if their odds are at 1.90 or under.

Racing Database Redesign - Part 13 - Automated Data Import

At the bottom of this post you'll find a video demonstrating the automated data download and importation process in action. I don't go into great detail in that video so it is worthwhile flagging up a few pointers so if you are trying to do something similar you'll know what to look out for.

Obviously some of the information may not be directly related to your situation if you are not using MySQL and PHP. Although I have no intention of posting lots of PHP script code hopefully you will be able to glean something from it in anycase.

Firstly, let me describe the process that the automation script follows:

1. Assuming no dates have been specified, the script heads off to  https://promo.betfair.com/betfairsp/prices and downloads the results files for the UK Win and Place markets based on the current system date, i.e. the files that contain the previous day's racing. Although there are files available for racing in other countries and the database can handle it, I just want to concentrate on UK racing.
2. It imports that data into temporary tables - one for the win data and the other for the place data.
3. It then analyses those tables on a race-by-race basis and, using internal MySQL commands, it splits that data and inserts it into the corresponding tables. It also calculates other information from that same data.
4. Once it has gone through all the races, it empties the aforementioned temporary tables, produces a summary of what it has done by writing everything to a log file then quits.

If the script was provided with a date range, steps 1-2 are repeated for each and every day within that range.

The script makes use of the PHP 'cURL' library to download each datafile, stick it into a variable then save the variable contents to a file on my server. It then uses the MySQL 'LOAD DATA INFILE' to quickly insert the contents of that file into temporary holding tables from where the data is then analysed and manipulated using internal MySQL commands.

While it is perfectly feasible to use PHP to manipulate and modify the data on a line be line basis before using the MySQL 'INSERT' command to insert the data into the corresponding tables, the method I'm using is significantly faster. As you'll see from the video below it takes 1-2 seconds for each day's worth of data. If you are not using MySQL, I strongly advise you investigate what bulk importation funtionality your software supports.


Finally, let me flag up a few things that you might want to consider:

1. Although the data files holding the results for the day are generally available by 10am the following morning, this is not always the case. Sometimes there is a delay so you might want to put off the download process until later that evening.
2. Very occasionally, Betfair have issues and the files aren't uploaded for a number of days so you may want to set up a mechanism that records that fact so that you can go back at some point to correct it either manually or automatically. I find a text based log file suits my fine, though you could have errors written to a database table instead.
3. Once you've downloaded the datafiles, there's no need to keep them after the data is imported into your database so you could set up an automated delete mechanism once the database is up-to-date.
4. If you configure the script to run automatically without any user input, you might want to consider having the log file emailed to you if you are away.

Now that everything is up and running, it is time to concentrate on the interface so I'll draw this series to a close. At some later stage I'll come back to introduce the interface but that is likely to be a while.

In the meantime, I hope you have found some of my ramblings of use and if you have any questions, just leave a comment below.

Back Or Lay Picks - 9 Month Summary Aug 2014 - Apr 2015

There's no midweek selections this time around so I'll take the opportunity to summarise this year's performance to the end of April 2015:

Bet TypeNo. Of BetsPts P&L
Lays28736.3
Backs55-14.9
No Bets18NA
Totals36021.3

An overall ROI of 5.9% is not to be sneezed at though the back bets continue to drag things down as they have all season - disappointingly so. Still, very happy with an ROI of 12.6% for the lays.

Selections will start to dwindle now that the main European leagues are drawing to a close. Picks will continue though during the summer as I take in the main Scandinavian leagues of Finland, Norway and Sweden.

Back Or Lay Picks 1st-3rd May

After a complete washout midweek there's a fair few selections this weekend that will hopefully redeem the situation.

CELTIC
DERBY
MIDDLESBRO
WATFORD
MILTON K.
FORFAR
BOSTON
WOLFSBURG
VALENCIA
LILLE


These picks should be backed if decimal odds at KO are 2.00 or over.
They should be laid if their odds are at 1.90 or under.