So far in this series we have looked at a basic way of estimating xGoals, using a Poisson Distribution, and Bookmaker margin. In Part IV we are going to take a look at using previous results as a way of deriving odds.
To kick things off, Open the workbook we have been using so far and go to our Results worksheet. You’ll notice that in column A we have E0 in each row. In a multi-league model this data would be important, however, as we are only interested in the Premier League, let’s repurpose this column now. In cell A2, type the number 1 and in cell A3 type the number 2. Select both cells and hover your cursor over the bottom-right edge of cell A3. When the black cross appears, double click and Excel will fill in the rest of column A with and increasing number until the end of our data.
If you like, you can rename this column to Index, or Match Number, or leave it as is. I prefer to rename it as it is considered best practice to always indicate what your data is. Do the same with our HistResults sheet, as this will be the sheet we focus on today. When you finish it should look like this:
**Tip: with larger datasets, it is useful to know which column you are dealing with. To save time jumping to the top of our worksheet to double check we are using the correct fields, we can go to our toolbar, select VIEW->FREEZE PANES->FREEZE TOP ROW.
Now no matter where we are on our worksheet, we will always know what each column is for.
Now we have an index which we can use to more easily pull specific results from our data. In the following, I would strongly recommend using the formulas shown as a guide and try to recreate it yourself, as copying and pasting will again result in errors due to ‘ symbols in formulas.
Analysing Head to Head Results
So let’s say we want to see how well teams have done when facing each other over the past 5 seasons. Of course we could go to our HistResults page, filter our home team and away teams, copy and paste the results, sort them by date and then run our analysis. But let’s automate as much as we can to speed up the whole process.
Create a new worksheet and name it Head to Head Results. The first formula we will use will be in cell B102.
This is a bit of a long one, but use the following as a guide and recreate it using some of the tips I mentioned in Part III.
Alright the formula is:
=IFERROR(IF(COUNTIFS(HistResults!$C:$C,’Odds Calculation’!$C$2,HistResults!$D:$D,’Odds Calculation’!$G$2)<ROWS(‘Head to Head’!$AA$102:AA102),””,INDEX(HistResults!$A:$A,SMALL(IF((HistResults!$C:$C=’Odds Calculation’!$C$2)+(HistResults!$D:$D=’Odds Calculation’!$G$2)=2,ROW(HistResults!$A:$A)),ROW(A1)))),100000)
**NOTE: This is an array formula, so to enter it you must press CTRL+SHIFT+ENTER
This should return a value of 355. Hover your cursor over the bottom-right corner of cell B102 and drag down to cell A106. The numbers should look like this:
In cell B107 we will use the same formula, however, we need to change the ‘Odds Calculation’!$C$2 as this references games where the home team played at home. We need the games that the away team played at home so we will change $C$2 to $G$2 (The cell that references our away team on the Odds Calculation sheet).
Here is the away formula for B107:
=IFERROR(IF(COUNTIFS(HistResults!$C:$C,’Odds Calculation’!$G$2,HistResults!$D:$D,’Odds Calculation’!$C$2)<ROWS(‘Head to Head’!$AA$102:AA102),””,INDEX(HistResults!$A:$A,SMALL(IF((HistResults!$C:$C=’Odds Calculation’!$G$2)+(HistResults!$D:$D=’Odds Calculation’!$C$2)=2,ROW(HistResults!$A:$A)),ROW(A1)))),100000)
This leaves us with:
The 1000000 you see are there in case of error. This will help us accurately select the correct order these games were played in. Liverpool have hosted Everton 4 times before the upcoming match and Everton have hosted Liverpool 5 times.
We’ll now use a RANK function in cells A102:A112 to order our games. In A102 enter the following formula:
=RANK(B102,$B$102:$B$112,1). Click and drag down to cell A112. Now we have the following:
The formula for cell C102 is:
And for cell C103:
Lastly, in cells D102:D111 enter the numbers 1 to 10.
Alright, now that is done we can start pulling in results.
At the top of the worksheet, copy the following:
The numbers in the top row are “helpers” that will allow us to find the data we need more easily.
In cell A3 enter the following:
Copy the formula down to cell A12. In cell B3 we will use our first VLOOKUP formula:
Again, copy this down to B12. The sheet should now look like so:
Alright just one last formula to get all of our head to head data for Liverpool v Everton.
In cell C3 enter the following:
IFERROR(VLOOKUP(‘Head to Head’!$B3,HistResults!$A:$BV,C$1,0),””)
Copy it down to cell C12 and across to column R, which finally gives us:
We can now see how well each team has done against one another:
As you can see, I added a 2 new columns at S and T to calculate the Total Goals and whether both teams scored. From this information I was able to extract the data you see below our first table.
The formulas I used for each market were:
RESULTS: =COUNTIFS(D$3:D$12,$D15,$H$3:$H$12,”H”) change “H” to “A” for Away result
AVG GOALS: =(SUMIF($D$3:$D$12,$I15,$F$3:$F$12)+SUMIF($E$3:$E$12,$I15,$G$3:$G$12))/COUNTIF($D$3:$E$12,I15)
In column S, Total Goals were calculated by summing home and away goals.
BOTH TO SCORE: =COUNTIF($T$3:$T$12,1) where 1 is YES and 0 is NO.
In column T, Both to Score was given a 1 or 0 using the following: =IF(AND(F3>0,G3>0),1,0)
OVER/UNDER TOTAL GOALS: In cell Q15 I used the following: =COUNTIF($S$3:$S$12,”>”&$P15)
and in cell R15: =COUNTIF($S$3:$S$12,”<“&$P15)
Past Results for Home and Away Teams
We can do something similar to get home and away results, but instead of getting into further detail with that, I have included a workbook here odds-compiling-tutorial-part-iv
Just a heads up, I have made the rest of the workbook inaccessible, so those already working along with the series, feel free to use it as a guide, but those beginning from scratch won’t get a short cut here.
The home and away result sheets look like this:
We can dive into many different markets with the data we already have such as Win to Nil, Clean Sheet, Halftime Result, Halftime/Fulltime, Win Both Halfs, Double Chance, Draw no Bet etc.. and I will come back to that later in the series, but for now lets take a look at two popular markets: Both to Score, and Over/Under 2.5 Goals.
I have made a new sheet titled Past Results to Odds and made the following tables:
Note that for the team names I use =’Odds Calculation’!C2 and =’Odds Calculation’!G2 to automatically show the selected home and away teams. I am only interested in percentages on this worksheet, so by going to the HomeResults, AwayResults, and Head to Head sheets, we can populate our charts so they will always auto-update. For example, on Past Results to Odds, click on cell B4. Press = then go to HomeResults and select cell N26 and press enter. In Column D, I show the average of columns B and C. In Column G I take the average of columns B, C, and F. Column H averages D and F. This gives us:
So, some notes. I rather use column G to calculate my odds, but I include an alternative average incase of issues with results. This match is a good example. Everton haven’t won a Head to Head match against Liverpool in our dataset. Including that 0% in our average would bring their probability down a lot farther than it should be. Even eyeballing the numbers I have here I would say 8.00 is too high, especially for a team in a derby game.
Ask 5 people what value means and you’ll probably get 5 different answers. When we talk about value we are talking about a situation where the odds we have calculated show an outcome is more likely than the probability set by the bookmaker. Value is a very subjective thing. Are odds of 1.20 considered value, for example? I would argue yes, if we believe an outcome has an even higher chance of occurring. Would I place a wager with odds so short? In football probably not. But that’s more to do with Risk/Reward than value. So how do we calculate value? You could simply look at the odds you have calculated and the odds for the bookmaker. If the bookmaker is offering better odds you may have a value bet. I say may here because it all depends on your odds. Are you confident in your method and the results of those methods? That’s up to yourself to answer.
We can also use Excel to quickly tell us:
I have added 4 more columns. Bookmaker odds are the odds displayed by your bookmaker of choice. Book% shows the % chance with margin removed. Value uses the formula:
If this number is below 0, you may not have a value bet, if it is above, you just might.
I prefer to use xValue, which looks at potential monetary returns using a €1 stake:
This formula gives us our expected returns per bet depending on the size of our stake. So even though it looks as though Both to Score NO and Over 2.5 Goals NO look to have some value, the difference between our % and those being offered by the bookmakers would score too low in our xValue column indicating it is not worth the risk.
Alright, a lot of ground was covered today, if you have any questions please feel free to contact me below or @petermckeever