Predict the Outcome of Football Matches Using this Model

i'm going to show you step by step how to create a football model where you can select any two teams and the spreadsheet automatically displays the predicted result and the odds for each outcome it also shows the predicted odds for the under and over goals markets plus the correct score market you can compare the model's odds with the odds on betfair to see if there's any opportunities for value the model works using previous goal scoring data i'm going to show you how you can configure the spreadsheet to give you results based on xg before we go through the step-by-step guide for creating this football model there are a couple of things we need to go over firstly all models are wrong but some are useful is a quote that is normally attributed to george box and well it applies to you guess it all models including this one right here which i'm going to show you in this video essentially all models fall short of explaining all the complexities of anything that you're trying to model so there are no models which can accurately predict a future reality however that doesn't mean that models and predictions are never useful because the model is giving you a prediction and it is based off real-life data it's just important to know that reality has too many variables to factor into the model but the model is there to just give you an idea of how the game is likely to play based off the previous results of the two teams that doesn't just mean you can simply look what the model says go on to bedford if there's a discrepancy then it has to be value that isn't the case i was just making that class i don't want anyone to be just using this model like it is gospel and using it on every single market you know football is just too highly variable this is why this model or any piece of information as a better or a trader should only be treated as a single piece of information when you put all the pieces together you get an idea of if there is an opportunity in the market or not briefly the model works on something called plasson distribution which in this instance is being used to estimate the number of goals in a football game the model uses some relatively complicated mass but i'm not going to bore you with the details of what each of these things mean because to create this model you don't need to be a mathematician i'm going to tell you what you need to write in each cell of excel in order to create this model if you are interested in pattern distribution and how it works there are tons of videos on youtube if you're interested in it like me but i presume most people aren't so i'm not going to bore you with the details also it is worth mentioning that i haven't created the idea of using plus on distribution to model football matches people have been doing this since the 1970s and 1980s so i'm not taking credit for this method but my spreadsheet i think is pretty cool where you can just put teams in and it works out automatically this spreadsheet is a little bit complex and if you want to save the time and the effort of copying this video and copying it into excel i've uploaded all these models onto my patreon which you can download yourself so that is individual spreadsheets for the 2021 2022 season for the premier league la liga bundesliga french league and syria the link to my patreon is in the description box below and it means a lot to me for all the people currently supporting the channel without further ado let's go into the step-by-step guide on how to make this model the first thing we need when we're creating this model is data itself that we're going to use i personally use a site called understat. com that is because it has both goal scoring data and xg data xg tables but you can use any site that you want you have to just make sure that it can be separated between home and away and also organized so that it can be in alphabetical order for the league that's very important so alphabetical order and separation between home and away understand as well allows you to set the start date and the end date for the table so if you don't want to use the entire data for the league so far just like the last five games for example that is something you can do here by selecting the day and then you get the table if you're interested in knowing what's the perfect number of games to use for this model well there is no perfect number that's the way it is a general rule of thumb is that the the older the data goes it becomes less and less relevant however the less games that you use the less data you've got there so your model becomes less accurate it's a balancing game it can be useful to create multiple different models of various different game amounts and get a broader idea of how the game might panic once you've got the data and i've organized it into alphabetical order and separated it into home matches first select all the data and copy it into excel it might be easier to copy into the same cells that i do so that will get easier later on when i say press equals divided by b43 for example so i copied the home data into cell n2 i'm just formatting the data here initially i'm going to delete the extruded stats i'll show you how to deal with them later on and i'm going to do the same thing by copying in the away table here and i'm just going to wave all these tables so i know which one is which and i don't get confused later on the next thing i need to do is add two columns to these tables a goals per game scored and a goals per game conceded to work out the goals of game score do equals and then select the goals divided by the games played for my spreadsheet that's t3 divided by p3 and for goals per game succeeded it's the same thing but goals against divided by matches instead of goals scored which for me is u3 divided by p3 and you can simply drag those down i'll double click the corner of the cell to fill that in for the rest of the table once you've done that for the home you can simply copy it over and paste it for the away table and all the data will transfer properly though okay next thing we need to do is sort out sort of the template of how the spreadsheet's going to work how you want your model to visually work is up to you but you can copy what i'm doing if you want it to look exactly the same as me the important thing is where you put the formulas but we'll get to that in a minute i'm not going to describe exactly what i'm doing though if you really are that interested in having exactly the same format as me you can pause the video and copy into your excel all we want is a home team section away team section and a projected home team goals and a projected away team's goal section of course we want to make a little table that has the home win weigh-win and draw probabilities and we want two rows in that section one for the chance and one for the employed odds next thing we need to make is an under goals market table i make this for up to under 4. 5 goals you can make it up to under 5.

5 goals or higher if you want to i personally don't see the need of that i don't use any strategies that go that high and again we want percentage chance and implied odds for this one once we've formatted that table we can simply copy it and paste it and change it to say over goal markets the last thing we want to make is a correct score table and as you can see you just want to draw in here all the scores that are available in the betfair correct score market so the highest score is free free and then after that it's any other home win any other way you win and draw there's no point adding like four one or four four because that isn't a market that's available on betfair once you've got your way out for how you want your model to work the next thing to do is add any coloring typically for some reason i have the home team in green the away team in red and the draw in a yellow color obviously you can choose your own colors and in the under and over goal markets i make each other line a different color just to separate it out a little bit animate the headings gray and they make the background of the table blue again personal preference here once we're happy with how we want this model to look we need to go back to the table we've got our data we want to have a row at the bottom of the table for the average what we want to do here is have an average goals per game scored for the league and average goals per game conceded for the league both for the home and the away so as you can see i'm putting the equation in for the home there and then once that's done i simply copy it and paste it in the exact same position underneath the away table and then what we want to do is scroll down a little bit underneath where you want your model to be displayed and this is where we put some data in which will calculate your model results at the end so we want to create a table of these six things obviously you can read them on the screen i'm not going to reread them so you just want to write them in and then we can enter in the equations for each one of these six things which will help determine the overall predicted chance of this game okay total average home goals per game scored you can simply do equals and then it simply equals and then select the cell we've calculated the average home goals per game w24 for me and then it's the exact same for the hawaii goals per game obviously in the away table and these bottom four boxes this is where the spreadsheet gets a little bit complicated we have to enter quite a big formula here so again if you're not willing to enter this formula i do have these spreadsheets available to download on my patreon but yeah let's get into these complex formulas what we want to do is write equals ifs so that's ifs open bracket and then we want to put a2 a2 is simply where you're going to enter which home team you want to be selected for the game and then you want to put equals again and then in quotation marks you want to put your first team alphabetically for that league that you're using so for the premier league that's arsenal and then close the speech marks then put a comma and then we want to select arsenal's average goals per game scored at home so for me they sell w3 that is quite complex you don't need to understand exactly what's going on if you're really interested you can research about the ibs equation in excel but i'm not going to go into that in detail here all you need to do is simply copy the format that i'm using here we need to repeat this same equation for all 20 teams in the league so as you can see it goes comma a2 equals speech marks aston villa comma then the average goals per game for aston villa then comma a2 equals and it goes so on as you can see here until we've got all 20 teams for the league which is the premier league that's what that looks like so if you just want to pause the video and copy that into yourself for real free i'll be here waiting for when you come back because we got another three of these to write in the next free sell so for the average away team goals per game conceded away we just want to copy the cell from above into this out and we just need to change all those average goals per game scored sells into the average goals per game conceded for the away team so from my spreadsheet that is cells aj so as you can see i'm replacing all the w's with ajs the next thing because this cell is related to the away team all the a2s also have to be changed to whichever cell you have your away team selected as so in my spreadsheet this is selected as cell a2 changing all those values now and again after i've changed them feel free to pause the video and copy this into your respective cell in your spreadsheet for the average away team goals per game scored this is identical to the cell above the average awaiting goals per game conceded away we just need to change those aj values into whichever cell you have selected for the average goals per game scored away so for me that is cell a i if you're using the same form i'm using it'll be cell ai for you and the final one is the average home team goals per game conceded at home so this will be the same as the average home team goals per game scored at home so we've got the a2 back in there all we need to do is change that w cell and in my spreadsheet that's being changed to an x filling out those four cells it's the most time consuming part of the spreadsheet so if you've got this far you're doing very well and if we go into cells a2 and c2 if we put two teams in that league though these six cells that we've just created will be able to be filled with data so we have another four boxes to create a four by two table here we want in these four boxes is home attack away defense away attack home defense in the home attack box we want to put the average home teams goals per game scored at home so in my case they'll be 37 divided by the average home goals per game scored for the league which for me is cell b35 away defense is away team goals per game conceded away divided by the average home goals per game scored which is b38 divided by b35 and away attack is b39 divided by b36 which is average away teams goals per game scored divided by the total average away goals per game scored and finally for home defense b40 divided by b36 which is the average home teams goals per game conceded at home divided by the average away goals per game scored for the entire league that's confusing to understand just copy themselves it will all make sense at the end and once you've got this in there once you won't need to think about it again just doing a little bit of formatting though next we want to create a three by two table containing these things projected home goals projected away goals and total goals in the projected homegold box we want to put in the following equation e35 times e36 times b35 that is a home attack times by the away defense times by the total average home goals per game scored for the projected away goals we want to do e37 times e38 times 36 which is the away attack times by the home defense times by the total average goals per game scored across the entire league for the projected total goals it's a simple one here you simply add the projected home goals with the projected away goals the next thing we want to do is create this table that i'm creating now and this is where we're going to use the poisson distribution to calculate the probability of the home team and the away team scoring x number of goals i typically go all the way up to eight goals you can go higher or you can go fewer i wouldn't recommend going too much fewer than seven i'm just doing some formatting of the table here again i put all the home results as green all the away results as red and all the drawers as yellow so i don't know if you can see what's happening with this table here you can read off the top row which is home goals and you can read off the far left hand column which is the away goals and then you can find the probability of a result occurring so for example if you want to do one at home you go to the two at the top and down to the one and that give you the probability of the game finishing two one to the home team but we've not got this table filled out yet so let's fill it out right now this is where we get to this complicated equation from earlier in the video no need to type the equation in again don't worry about what the each variable means just simply write the equation in the equation is almost too confusing to read out loud it's probably easier if you just pause the video copy what i've got on screen now into the same respective box on your spreadsheet make sure it's exactly the same including those dollar symbols in there and once you've entered it you want to come to the top here in this drop down menu change it from number to percentage and then you can simply drag down to the bottom of the table and it will save you type in that same equation now eight times you have to do a similar thing for the home team now again simply pause the video copy the equation into your spreadsheet change the cell type to percentage and then drag the data across for all eight possibilities now we've worked out the probability of the home team and the away team scoring anywhere from zero to eight goals so for example for this game that i'm showing right here the home team of a 23. 98 chance of scoring two goals and the away team of a 0. 51 chance of scoring four goals so now we need to work out the actual probability of a result occurring so say for example what's the probability of the home teams going once and then also the away teams going once at the same time to the game finishing 1-1 well to do that and to do anything like that in probability we need to find the outcome of two events occurring you multiply the two probabilities together which is what i'm doing on the spreadsheet right now so to calculate the probability of nil nil i do e44 times f43 i'm just changing the format to three decimal places and into percentages and then as you can see i'm doing this for each individual score now by multiplying the probability of the two scores together and we get a complete table showing the probability of every single outcome occurring in this football match all the way from zero zero to eight eight i don't think there's ever been an eight eight in football sure we're not so now we need to scroll back to the top of the spreadsheet we created the template remember that it feels like a long time ago now doesn't it and then in the projected home goals box to simply do equals scroll down where we have projected home goals and click that cell right there and repeat the same thing for projected away goals i change the format to these to two decimal places don't have to do that it won't make any difference i just like the look of it so next we need to work out the probability of a home win so to do that we click on this box here for home win and type in the equation equals sum open bracket and we want to select all the cells which are green or all the cells which your home wins and for the draw we do the same thing you know equal sum and add all the individual draws together work with the total probability of the game finishing the draw and for the away we do the same thing again equal sum and then select all the cells where the away team win and then to work out the implied odds we simply do equals one divided by the chance and then we can change the formatting again there to two decimal places and there are the odds they are the employed odds of each team winning based off the previous goal data now let's look at the under and over goal market for the under 9.

5 goals market you simply for the implied chance there's only one way the game can finish with under 0. 5 goals that's a nil nil so we simply just do equals and then select the box which is nil nil for under 1. 5 goals markets we need to then press equals sum and select all the outcomes where the game finishes in under 1.

5 goals which would be nil nil or one nil to the home team or the away team and then you repeat this procedure for all the under the goal market so the under 2. 5 3. 5 and 4.