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 *here. *Also 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:

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:

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:

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:

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:

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:

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:

**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:

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:

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:

This gives us the following match odds:

**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…)

Doing this for both teams gives the following percentages:

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 **1 **goal, we would get half of our stake returned and lose half. Here is a chart of AH lines and their results.

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

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

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:

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:

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*

Can you send the content in excel to my e-mail? The address is caerrr@gmail.com, if you could, it would be very useful.

Thank you

LikeLike

Unfortunately not. But if you have any specific questions please ask.

LikeLike

hello Mr Peter McKeever i would like to ask can you guide me to compile also the odds for correct score and any other specials markets such as team total goals? HTFT? please and thank you

LikeLike