over/under goals markets

Betfair Historical Data has been, for some time now, openly available for free but it seems that no one is prepared to share just how you open the damned stuff!!!
There are plenty of sites out there willing to share their python knowledge but stop short of a full explanation – very frustrating.
At this point I have to give credit to “Trading The Market” guys who have finally given me the Eureka Moment!
If you follow the steps below you will soon have all the research information to back test your systems.
To begin with you do need to have a betfair account and to access which data you need you will need to be logged in and at the following page
https://historicdata.betfair.com/#/home

For the purposes of this post I will be using the basic plan which costs nothing and has a data frequency of 1 minute. If you need more frequent data logs then you will have to purchase your data by the month but there are free plans available for limited months mainly in 2020.
In this post I will be analysing the data from the North London Derby – Arsenal v Tottenham on the 1st October 2022
The easy way to handle this data is to “purchase” a month at a time so I will “Buy” the Soccer data from OCT 2022
To do this simply select the plan, sport, and month you need in the drop down boxes and click “Add to Purchases” and then the “purchase” as in the screen shot below

You will then be asked to confirm your purchase – Check that you haven’t included the wrong plan by mistake as the fee will be taken straight from your betfair balance. If everything is ok click confirm and you will see the following screen.

As you can see from the screen shot there are over 250000 markets! for our research we only need 3 or 4 of these so we need to find them.
I want to know how the under/over Markets behaved when each goal went in during the match. To do this click on the “My Data” Tab as shown below and you will see your most recent purchases as well as plans you have bought before.

We now need to populate the down load fields with the information that we need so if we look at the match stats we can see that there were 4 goals scored in total.

I want to see the following market odds
O/U 1.5
O/U 2.5
O/U 3.5
O/U 4.5
O/U 5.5
O/U 6.5
and the Match.
To do this we need to populate the download files panel as follows:

In the Plan Box (which is at the top but hidden in this screen shot) enter Basic
In the Sport Box enter Soccer
In the From and Upto Boxes enter 1 Oct 2022
Leave the event ID Blank
In the Event Name box enter Arsenal
In the Market Type box select the markets you need and in this case its Match_Odds
Over_Under_25
Over_Under_35
Over_Under_45
Over_Under_55
Over_Under_65
In the Country Box select GB (There is no UK)
and in the File Type Box select M
Click the Add to Downloads button
You now should have 12 files ready to down load.
Click the Yellow Download button

You will find this file in your downloads folder as a Data “tar” file

Double click the file to un pack it in your unzipping program

Keep double clicking until you get to the actual data files

You should now see your 6 “bz2” files
To make them easy to find Create a new folder on your desktop and name it ARS v TOT

You can now drag and drop the files into this folder from your unpacking software

We now need to convert these files into a readable format and Betfair provide one such program easily available from their website.

You will find this here

https://www.betfairhistoricdata.co.uk/

The first file we want to see is the Match Odds and this will usually be the largest file as most volume would have passed through that market. In this case we are looking at File 1.203213491

These are not very user friendly file names so I am going to rename them. to do this we simply select a file from the list using the betfair Historical Data Processor as below and note the file number and which market it contains. In this case File 1.203213498 is the O/U 6.5 goals Market. We dont want to do anything else yet. We are just identifying the files for our convenience

We can then repeat the process with all the files noting the file numbers to the markets and then rename all the files as below

We are now going to convert these files into meaningful excel ranges where we can look at them in more detail. To do this we again select a file from the folder using the Historical Data Processor and click the download settings button as shown in the screenshot below

This will open the settings dialog box as shown below

We now need to populate our spreadsheet with any relevant information we will need by clicking the drop down box arrow and selecting the fields by ticking the relevant tick boxes (dont worry if you put too much info into the spreadsheet as we can delete it later)

Once you have selected what you need close the drop down box by clicking the “up” arrow and because we are just interested in the in play data slide the “preplay” button to off as shown below and click save

We are now ready to download the file as a CSV file into Excel we do this by simply clicking the “Download CSV” button as shown and let the wonders of better brains than our own do its stuff.

This will put a zipped file into your downloads folder with the original file number

If we double click this file it will unpack and be available as a csv file which we can open with excel.

It may be a good idea to move this file to a new folder on your desktop and rename it as I have done here

If we open this in excel this is what we get.

We can now start to manipulate the data to best suit our needs. We can start by removing some of the columns we dont need. I have removed the following columns to be left with the following spreadsheet.
Market ID
Selection ID

The next task is to make the published time more reader friendly. as you can see this is total jibberish, it means nothing. If you select cell A2, in the address window you will see that the full date and time is shown

So that column A shows the time in hrs and minutes we need to select hh:mm from the custom cell formats as shown below.

When we click ok then cell A2 will show the actual time of the published data for that row

To convert all the row simply select Column A and repeat

When you click ok your spreadsheet should look like this

you will notice that the times are repeated 3 times this is because there are 3 separate outcomes within this betfair market – Arsenal to win The Draw and Tottenham to win.
If we now draw our attention to Column F this is the last traded price column and we could do with tidying up this so that all the figures are shown to 2 decimal places. To do this simply select Column F and using the cell number format dropdown box, select “Number” as shown below.

Your spreadsheet should now look something like this

A bit more tidying up by changing the column headers and this is what you should be looking at.

This is your basic data spreadsheet for the entire match for the Match Odds Market You can now save it into a folder and repeat the process with the other 5 markets

In my next post I will show you how to manipulate the data so that we can see what happened in each market as each goal went in

Please like and subscribe to my blog so that I can get a feel for what content can be most useful to you.

Understanding the initial direction of the Over or Under goals markets on a betting exchange when the game goes in-play and the different speeds that the market moves is key to judging your risk when trading. Grasping this and understanding what happens when a goal is scored is half the battle to making profit in these markets.
As a game goes in-play the probability of a goal being scored begins to diminish when you are trading the Over goals market which means that the odds will increase as the minutes tick away. Depending on which Over market you are attempting to trade, when a goal does go in, the odds will take a very steep drop to a level where the probability of the market being fulfilled will reflect in the odds depending on what time the goal was scored. For instance If you had laid the Over 2.5 goals and the first goal went in after 5 minutes then there are 85 more minutes of the game left for the remaining 2 goals to be scored and the probability of this happening will reflect as very high and the odds will plummet to a point which would put you into a loss position in the market. If, however, the first goal wasnt scored until the 85th minute then the probability of 2 more goals being scored in the final 5 minutes is very low and the movement in the odds would be slight, if at all.
To demonstrate this visually I am going to use a smart bit of software called BetAngel and a feature in the program called “Soccer Mystic”. This will “predict” the odds during a predetermined game if or when goals are scored. There is a game tonight in the English Championship between Barnsley and Swansea City and for no other reason that it is first alphabetically will serve my purpose admirably.
At the time of posting this the predicted lay odds on the Over 2.5 goals market is 1.91. While this is just a predicted odds level at kick-off the market can be different depending on factors such as team sheet announcements, but for the purposes of this it will suit. If we say that we have a lay bet of £5.00 at odds of 1.91 this would give us a liability (total ammount we can lose) of £4.55 at kick-off. We can use “Soccer Mystic” to see our profit/loss should a goal be scored in the fifth minute. The screen shot below shows the predicted odds if no goal is scored in minute 5 and the predicted odds if there was a fifth minute goal

Barnsley v Swansea predicted odds after 5 minutes

As you can see we can determine our risk if there was a goal in the 5th minute and this would be a loss of £2.08 if we traded out. In the next screen shot Soccer Mystic also gives a visual representation of how the market “recovers” as the match progresses and can give us an indication as to when we could trade out for no loss if while watching the match we ascertain that the first goal was a fluke i.e. own goal or a lucky fumble in the box and decide to let the bet run to our predetermined exit point.

The predicted odds are shown by the red line in the graph and as you can see after the first goal going in at the 5th minute the odds plunged to 1.34 but then as the game progresses and no other goal is scored the market recovers slowly to rise after about 35 minutes in-play time to a break even point where we can exit the trade for a no loss/profit. In the graph the horizontal blue line is the “profit” line and when we are laying the market when the red line is above the blue a profitable trade is possible.
As with all things betting or trading research is the key and you may have noticed that I mentioned a predetermined exit point. After doing some really basic research on this game tonight I came to a decision, provisionally, that the first goal probably, using previous games as a guide that perhaps, barring fluke goals, the first goal might be some time after the 17th minute. Using this as our control point we can set out exit point at 15 minutes win or lose

In the screen shot above I have pointed the cursor at 15 minutes and in the ringed box shows us that the predicted odds after 15 minutes when the first goal was scored after 5 mins would be about 1.49. This would leave us in a losing position but if we were right and the next goal went in at minute 17 then our loss would be greater. If of course no goal was scored and we trade out at 15 minutes Soccer Mystic can show us our predicted profit as shown below.

I am sure you are thinking “what if I want to back over 2.5 goals ” then the opposite applies and if you had backed this market at the odds shown and a quick goal was scored then the odds would move dramatically in your favour to enable you to trade out at a profit. as shown below.

Barnsley v Swansea back bet 5 minute goal

We would exit this trade at this point for a £1.93 profit and this would be great but again when we go into this trade we need to pick an exit point and having done the same research we think that the first goal will be at about 17 minutes how far past this point do we go if no goals are scored. If we pick our exit point at say 25 minutes and no goals are scored Soccer Mystic can show us what sort of loss we would expect to take as demonstrated below

Barnsley v Swansea 25 minute goal on a back bet

As you can see an overall loss of £1.95 would be our exit loss.
To summarise if we are laying an “Over” market the odds rise steadily in our favour and continue to provide the potential profit but as soon as a goal is scored our profit potential vanishes to be replaced with the very great risk of a losing trade, on the flip side, if we back the “over” market then our position is of a losing trade until a goal goes in and the earlier the better.

Just for fun there are 7 championship matches tonight and the average time for a first goal this season in the championship league is 32 minutes the following 7 screen shots show what you could expect to win if you laid the Over 2.5 goals market and each game remained goalless until this point.