Testing, Testing!

If you’re like me and you’ve been betting/trading for any length of time, then you’ll have left a large number of “foolproof” systems, strategies, ideas and whimsies behind on the “exchange highway” – with many of them never even having seen the light of day. 

There are essentially two ways to test a strategy or idea, one of which (in my opinion) is far safer, more thorough, less time-consuming and generally more accurate. The other is perhaps more fun.

The fun method is test an idea out by trialling it with small stakes, perhaps even automating it so that you don’t have to slavishly sit at your computer to run the actual trial. This is okay but even with small stakes on a losing, high-turnover strategy, it’s quite easy to trickle away a reasonable portion of your betting bank. Also the question then becomes, how long should I run the trial for? Is one week, two weeks, two months sufficient? What is a reasonable sample? Tricky isn’t it?

The other, and far more practical method is to back-test any new strategy first. Now the lazy and feckless amongst you will probably be groaning right about now. 

“Hurrumph! It’s too difficult and too time-consuming to back-test something. Easier just to try it out.”

Yes, it certainly is easier just to throw a bet down and “see how it goes”, but even if you’re just a serious hobbiest, this is unacceptably dumb. If you’re a professional or semi-professional and working like that, then perhaps you need to find another line of business.

Back-testing is admittedly no substitute for live betting, but it can sort the wheat from the chaff before you even embark down that road and potentially cost yourself money. I have ideas and strategies flying around in my head all of the time. Most of them are completely daft and fanciful, but some appear to have real worth and substance. It’s these ideas that can be dangerous unless treated properly.

“Oh my God! This idea is amazing. I can really see this working.”

Stop right there. Don’t you dare put a penny into the market. BACK-TEST IT! 

In truth back-testing is really not that difficult. I’ll even take you through an example of back-testing to show you just how easy it really is.

First off, pop along to http://www.football-data.co.uk/data.php#download where you can download several season worth of data for all the major European leagues. This is all free-of-charge and is an amazing resource to have at your disposal.

These data files are downloadable as CSV files. What are CSV files, you may be asking? Well, these are text files where the data is separated by commas. The acronym CSV simply stands for Comma Separated Values. Once you click on a file and it downloads, what you need to do is open it using Excel and save it as an Excel workbook. The reason we do this is so that we can perform calculations on the data.

These data files are helpful to us bettors because they contain the average odds for each match, so we can not only see if one of our ideas will win lots of times or not, we can also see, given the average odds, whether the idea will actually make a profit or not. That is an amazing time-saver, isn’t it? No need to keep betting over and over to check an idea. Here we can test against thousands of matches in a few minutes. If the idea makes money after back-testing, well that is the time to start testing it in the live environment!

Okay, so we’ve downloaded our test file and we’ve saved it as an Excel workbook. We open it up and see a lot of data in there. On the same website, there is a key to each of these fields, but they are nearly all easily interpreted anyway.

Now so you can see a practical example of back-testing, I've provided you with a short example, In the link below, I have provided last year’s EPL data results. 

Download the example file here

If you open it up, you will see two sheets. The first one is the “E0” sheet. This is the raw data provided by football-data.co.uk. You will find it good practice to generally avoid adding formulas and calculations directly into the raw data. This is best left untouched and a new sheet used to work out the “winning strategies” (we hope). This way, you can apply your ideas to any set of data without having to try and unpick all the formulas and calculations from the data.

The second sheet is what I’ve created to help us perform our back-testing on the data. Right, so let’s suppose we want to test the idea that there is money to be made by backing Over 2.5 goals in every match where the odds are greater than or equal to 2.00. 

Now this is obviously a rather rudimentary example, but it should still be helpful to see the mechanics of it all. The whole test on last year’s Premier League took me less than five minutes. That's testing 380 matches, and I could very easily expand that out to several seasons or several leagues, allowing me to test my idea against thousands of matches.

In my “Test” sheet, I have written in plain English what I’m testing. This is easy to forget if you open the sheet up after a couple of months, so it’s always good to remind yourself of what you’ve already tested. I’ve also separated-out the odds I’m testing against (C3) so that I can change this and the results will update “on-the-fly”. This gives a bit more flexibility. For example, odds of 2.00 might be a loser, but what about odds of 1.90?

In the raw data sheet (E0), the odds section begins at column X and finishes at column BS. Most of these are match odds, some are Asians and some are the 2.5 goals odds. We’re obviously interested in the 2.5 odds. Columns BI to BM contain “BbOU”, BbMx>2.5”, “BbAv>2.5”, “BbMx<2.5” and “BbAv<2.5”. In order these are:

• The number of bookies used to derive the averages
• Betbrain maximum Over 2.5 goals odds.
• Betbrain average Over 2.5 goals odds.
• Betbrain maximum Under 2.5 goals odds.
• Betbrain average Under 2.5 goals odds.

I think we’re just interested in the averages here, so we want the BbAv>2.5 column (which is the BK column). Right, so in column B of my test sheet, starting at row 13, I have this formula:

=IF(AND(ISNUMBER(E0!B3), E0!BK2>$C$3), E0!BK2, "")

What I’m doing here is checking that the B column in the “E0” sheet contains a date. If it doesn’t, then it must be an empty row and I can ignore it. I do this because the EPL has 380 games per season, but the Championship and below have 552. I want to make my test sheet “data agnostic” if at all possible. 

The second check in this formula is whether the Over 2.5 odds in the BK column of the “E0” sheet are greater than or equal to my stated odds in C3. The little “$” marks around the C3 means it’s static, allowing me to drag this formula all the way down the sheet and it will still refer to C3. Anyway, if both those conditions are satisfied, then I display the actual average odds.

Column C just reports if the bet was a winner or a loser. A “1” is a win and a “0” is a loss. The “Return” showed is my stake (C4) multiplied by the given odds. Of course if you’re betting on the exchange then you will also have to take commission into account (C5), which gives us an actual P/L in column F. The final result is seen in F10.

Unfortunately, as you can see, this particular strategy would not have made me any money in the Premier League for last season. It could be worth checking other leagues and other seasons to see if there is any variation, but on the face of it, there is no edge here.

But hey, I haven’t spent a single penny of my bank finding out the answer. And that makes the last five minutes playing around with the data well worth my time. 

In my next post, I will provide a more complex example, that performs lookups based on the previous match for each team. This also can then be used as a template for a wide variety of backtesting scenarios.