How to Compile Odds for Football: A Beginner’s Guide Part VI – The Weighting Game & Asian Handicaps

In today’s part of our odds compiling series, we’ll finally take a look at zeroing in on a closer xG numbers for the upcoming Premier League match in GW 34 between Liverpool and Palace using our 5 season data.

For those only joining us, you can catch up hereAlso please note that this part uses data which is up to date as of 18th April 2017 (Last match was between Middlesbrough & Arsenal).

Weighted Attack and Defence

Alright so, first job for today is to find league averages for our data and weight each team’s attack and defence numbers. For simplicity, I will refer to these in this article as attack and defence ratings, however, we will look at building more detailed ratings later in the series (which will make use of some metrics we calculated in Part V).

In our workbook we need to go to our Shots and Goal Data sheet. We’ll look at our in-season data first. From cells B23:I23 we need to AVERAGE all of our data so it looks as follows:

footdata44

The numbers in row 23 now show how an “average” team would perform in this season’s premier league. We can now use these averages to see how above or below average each team is and use these as weights later on. The formula for the attack and defence ratings is very straight forward. For Arsenal, in cell J2 simply type:

=E2/$E$23

This formula takes Arsenal’s xG Scored (2.039) and divides it by the league average (1.41), and gives a result of 1.44.

For the defence ratings we will use the same calculation in cell K2, but we will use I in our formula rather than E. The result for Arsenal should be 0.92. Copy these two formulas down to all teams and you should have the following:

footdata45.JPG

We also have a similar table on this worksheet for our 5 season data – be sure to repeat the steps outlined above for that table, which when you finish should look like so:

wm27uh

So now we have some overall numbers for our datasets, let’s break it down a step further.

Create a new worksheet and name it Weighted Attack and Defence. On this worksheet we will break up the numbers into home and away statistics for this season’s results and for our 5 season data.

In cells A3:A22, add each team’s name. We will add some headings in cells A2:L2, so we get this outline:

footdata46

The formulas we will use are the exact same we have used on our Shots and Goal Data worksheet, however we only need half of it as we are just looking at the home results. So looking at the formula we used on our Shots and Goal Data worksheet we’ll use the following:

=(AVERAGEIF(Results!$C:$C,$A3,Results!L:L)+AVERAGEIF(Results!$D:$D,’Shots and Goal Data’!$A2,Results!M:M))/2 (We only need the first AVERAGEIF, but be sure to remove the ‘Shots and Goal Data’!).

Since we’re using parts of formulas we have already made, we could type out all of the formulas again, or you could simply copy them from our previous worksheet and remove the parts that refer to away games. When you are finished, the table should look like this:

ad1wwz

From columns M:W we will find each team’s away stats. Once again, we just need the second part of each formula, so in cell M3 to find Arsenal’s Avg Shots For when away, we use

(AVERAGEIF(Results!$D:$D,$A3,Results!$M:$M))

Here is the entire table after completing our away stats:

84tvpn

So now we’ve done it for our 1 season data, let’s do it again for the 5 seasons. Add each team’s name in cells A32:A51. Follow the steps above, except use the sheet reference HistResults rather than Results. When that is finished we have this table:

ejfdss
Recompiling Match Odds – Liverpool vs Crystal Palace

For this section you are free to create a new worksheet or to simply edit the Odds Calculation worksheet.

If you are using the sheet we have already created, select Liverpool as our home team and Crystal Palace as the away side. All the formulas we will use we have gone through before, namely the INDEX function. We will use our five season data for these calculations. For each team we will find Attack Ratings, Defence Ratings, xG For, xG Against. We will use an index function to find the home attack rating for Liverpool on the new worksheet we created (column J), and the away attack rating for Palace (column U), each team’s defence ratings (columns K and V).

When all of our categories have been calculated we should have the following:

yewv5x

Next step is to find an average rating for each team. We will use our AVERAGE formula to get our final rating. The cells shaded green are the inputs for Liverpool, and the cells shaded orange are the inputs for Palace:

nuarhp

Now we can use this average with each teams Overall attack and defence rating on our Shots and Goal data page. Using Liverpool as an example, we can find their xGoals by multiplying their Avg Rating * Liverpool’s Attack * Palace’s Defence, or 1.47*1.41*1.06 = 2.19

Doing the same for Palace, we see that their xGoals for this match is 0.79.

Using these as our inputs for the Poisson Distribution gives the following table:

mrozd6

This gives us the following match odds:

kc65id

Calculating Asian Handicaps

Now that we have our updated match odds, let’s dive deeper into the estimates to pull out some more betting markets. Firstly, we’ll calculate our winning margins. By using the SUM function, add all of the cells from our correct score matrix where the each team wins by exactly 1 goal. Do the same for exactly 2 and 3 goals. Here’s an example of Liverpool winning by exactly 1 goal (summed cells that show 1-0,2-1,3-2,4-3 etc…)

footdata48

Doing this for both teams gives the following percentages:

dceo4q

We can now use that to find the odds for each Asian Handicap line. Great, what is an Asian Handicap?

Asian Handicaps basically eliminate the possibility of a draw. This gives us a binary market, which gives bettors a much better chance to turn a profit, providing other factors like staking plans, bankroll management etc.. As an example, the AH line for our match between Liverpool and Palace is -1.25. This means that Crystal Palace will start the game with a 1.25 goal advantage. If we place a bet on Liverpool at -1.25, our bet would win if Liverpool win by 2 or more goals. If Liverpool win by goal, we would get half of our stake returned and lose half. Here is a chart of AH lines and their results.

asian-handicap-table

**NOTE: in my worksheet I have my % chances in column AJ.

footdata49

So, firstly we need to make a scale from 2 to -2.

daflnh

Here are the calculations for each line:

0: =1/((1-AJ9)/AJ8) or, 1/((1- Draw % / Liverpool %)

-0.25: =1/((1-AJ9/2)/AJ8)

-0.5: =1/(1/AJ8)

-0.75: =1/((1-(AJ21/2))/((AJ8-(AJ21/2)))) where AJ21 is % chance of Liverpool winning by 1

-1: =1/((1-AJ21)/(AJ8-AJ21))

-1.25: =1/((1-(AJ21/2))/(AJ8-AJ21))

-1.5: =1/(1/(AJ8-AJ21))

-1.75: =1/((1-(AJ22/2))/(AJ8-AJ21-(AJ22/2)))  where AJ22 is % chance of Liverpool winning by 2

-2: =1/((1-AJ22)/(AJ8-AJ21-AJ22))

Keeping with Liverpool:

+0.25: =1-1/((1-AJ9/2)/AJ10) where AJ10 = Crystal Palace %

+0.5: =1-1/(1/AJ10)

+0.75: =1-1/((1-(AJ24/2))/((AJ10-(AJ24/2)))) where AJ24 is % chance of Palace winning by 1 goal

+1: =1-1/((1-AJ24)/(AJ10-AJ24))

+1.25: =1-1/((1-(AJ24/2))/(AJ10-AJ24))

+1.5: =1-1/(1/(AJ10-AJ24))

+1.75: =1-1/((1-(AJ25/2))/(AJ10-AJ24-(AJ25/2))) where Aj25 is % chance of Palace winning by 2 goals

+2: =1-1/((1-AJ25)/(AJ10-AJ24-AJ25))

That gives us the range of lines for Liverpool winning by 3 or more goals to losing by 2 or fewer goals:

feyskw

Alright. Horrible formulas. Thankfully to find each line for Palace we simply need to find the inverse of each of our % chances. As we do not calculate a margin, all we need to do is use 1 – Liverpool % chance to find the + And – lines for the away team:

eok4ez

The average odds from Oddsportal for the AH shows that

Liverpool -1.25 goals is at 2.06

Palace +1.25 goals is at 1.84

So definitely in range. With basic data at our disposal, the model does a nice job of finding where the lines should be. Is it perfect? Of course not, but it’s a great starting point.

Alright, I’ll leave it there for today. As usual, any questions feel free to contact me below or @petermckeever

2 thoughts on “How to Compile Odds for Football: A Beginner’s Guide Part VI – The Weighting Game & Asian Handicaps”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s