106
Introduction
In this article I’ll describe a simple football betting modelFor this model we’ll use Poisson distribution, a discrete probability distribution “that expresses the probability of a given number of events occurring in a fixed interval of time or space if these events occur with a known constant mean rate and independently of the time since the last event”.
A football match is played in a fixed interval of time, and the number of goals in football is a discrete variable. To be honest, the last part of the definition of Poisson distribution - if these events occur with a known constant mean rate and independently of the time since the last event – doesn’t hold in football. That is because we cannot assume that mean rate of the number of goals remains constant. But this is a simple strategy so for now, we’ll ignore the last assumption. Furthermore, Poisson distribution is used by bookies to calculate betting odds for different markets. Therefore, we can use Poisson distribution to model the number of goals and the result of football matches.
Getting data
To build any model, not only a one on sports betting, you need data. There are many services that provide free football statistics. I used Football-Data, a free football betting portal providing historical results & odds. The website contains historical data from many European leagues starting from the 1994/1995 season. Besides the Big Five (Premier League, Bundesliga, La Liga, Serie A and Ligue 1), historical results and odds of the main domestic football leagues of the Netherlands, Scotland, Belgium, Portugal, and Turkey can also be found in their datasets.
I’ll explain how to build a basic betting model based on the 2018/2019 season of Ligue 2, the second division of French football. First, go to https://www.football-data.co.uk/downloadm.php and download data with the title of “Season 2018/2019”. Of course, you can obtain the data from any resource you find useful. And you can use any football league data of any season you wish, say, English Premier League of the 2021/2022 season. But to follow the process of building a betting model I advise to use same league data as mine.
Open the downloaded file. It should look like this.
As you see, there are many columns, i.e., variables, which we don’t need for this model. At the moment we only need historical results, i.e., HomeTeam, AwayTeam, FTHG, and FTAG. The last two variables mean Full Time Home Team Goals and Full Time Away Team Goals respectively.
Calculating some metrics
Create an Excel file and paste these four columns’ data with all the rows – there should be 380 matches in total – into a sheet in the cell A3.
Write “Team” into the cell F2 and insert team names in the cells below. In the next columns of the second row, we’ll calculate several metrics, namely how many matches each team played, and how many goals they scored; from these two metrics we’ll compute average goals for each team. In a similar manner, we’ll calculate how many goals each team conceded and on average how many goals they concede per game. Ensure that your sheet is similar to this one:
Now we are ready to calculate these metrics. Games Played is self-explanatory – it describes how many games each team had as a home team. Put the following formula into the cell G3:
=COUNTIF($A:$A,F3)
What this formula does is to look at the column A which contains the name of home team for each match, and count the occurrences for each team. You should see 18 in that cell which means that Ajaccio played 18 games as a home team in that season. Now drag the formula down to the least team, Chateauroux. Since all teams participated as the home team in 18 matches, you’ll see 18 in Games Played. Now write the following formula into the cell H3 to compute scored goals for home teams:
=SUMIF($A:$A,F3,$C:$C)
Sumif function is used to sum the values in a range that meet criteria that we specify. The first argument in the function is range, i.e., we want our functions to be executed in the entire column of A which, as already mentioned above, contains home teams’ names. The third argument, sum_range refers to the actual cells to be added according to the second argument, criteria, which is F3 or the name of the home team in our case. Drag the formula down to the last team. “Average goals for” variable is just the second column divided by the first column; so you have to put the following formula into I3
And drag it down as well. Now, we are going to calculate total and average conceded goals for each team. Put the following formula into the cell J3:
=SUMIF($A:$A,F3,$D:$D)
You can figure out yourself what this formula does by looking at the respective columns. To get average conceded goals per match the formula should be inserted into K3:
After all these actions your table should look like this:
We are going to do the exactly same calculation for Away as well. Put the following formulas into the respective cells:
=COUNTIF($B:$B,M3) ---> N3
=SUMIF($B:$B,M3,$D:$D) ---> O3
=O3/N3 ---> P3
=SUMIF($B:$B,M3,$C:$C) ---> Q3
=Q3/N3 ---> R3
After dragging all the formulas down, your Excel sheet will be like:
While each team’s metrics are important to know, we should also calculate the same for the entire league. Write “Total” and “Average” into F24 and F25 respectively. Total will display the sum of the corresponding columns; for example, into G24, you should insert “=SUM(G3:G22)”, into H24 “=SUM(H3:H22)” and so on. “=AVERAGE(G3:G22)” and “=AVERAGE(H3:H22) should be in G25 and H25 respectively. Don’t forget to do the same calculations for Away results as well. Once you are done, this is what your Excel sheet will look like:
I wrote a short ebook on building a football betting model which describes everything from getting data and creating a statistical model based on Poisson distribution. This is the same distribution bookies use to calculate odds on different markets. The book comes with the template Excel file which you can use to learn and apply in your own betting.
And it only costs $5.
To buy it please go to the following link:
https://fmiren.gumroad.com/l/eixemq?_gl=1*10v50y5*_ga*MjA2MjM3MTI2Ni4xNzAyOTg0NDAx*_ga_6LJN6D94N6*MTcwMzAwNDc0Ny4yLjEuMTcwMzAwNTc5Ny4wLjAuMA.
