Predict the Outcome of Football Matches Using this Model
Vložit
- čas přidán 13. 02. 2022
- You can predict the outcome of football matches using this prediction model. The model uses previous goal scoring data and a method called Poisson distribution to determine the probability of certain outcomes including the number of goals and overall result of the game. This model automatically determines the implied odds which you can use to assess if there is an opportunity for value.
This video walks you step by step through the process of creating this model, but if you find it confusing or want to save yourself time I have created and uploaded models for the Premier League, Bundesliga, La Liga, Serie A and Ligue 1 available for you to download...
👉️ My Patreon: / kiemillett
Patreon's get 1 trading video & 1 P&L breakdown video each month as well as many other perks!
► Twitter: / kiemillett
Music from Pixabay.com
Disclaimer: This video is made for informational purposes and is NOT financial advice
Hi everyone! Models for the 2022/23 Premier League, Bundesliga, La Liga, Serie A and Ligue 1 seasons are available for Patreon's www.patreon.com/KieMillett
Hello! Congrats for your videos. I would like to know, the above excel models, in which subscription they are available? Basic or advanced supporter?
Soo if i be member of the 11 euro i gonna have all the leauges?
How can I download it?
You have single-handedly become the most useful person on CZcams. As an old fart, I have never had the opportunity to learn this stuff, and seriously it is gold dust. I would pay just to learn about macros and excel. Being able to use the information to make a few quid is a massive, unexpected bonus. I have subbed & will contribute. Many thanks Kie.
I'd imagine there are lots of training videos on CZcams on Excel and creating macros.
Once you get into it, it's not actually that difficult.
@ronnie has this been profitable for you over the months?
Wow this is impressive stuff and I have been looking for this type of video for ages now and finally found it...You earned urself a new subscriber
Thanks Ivano, glad you enjoyed it!
Good explanation, however poisson distribution tends to favour lower scores, its a known flaw in the distribution and hence why there is another distribution called zero inflated poisson.
This was excellent! I just adjusted it so that it would be applicable to the NFL and now I have my very own model to assist making decisions :) Thank you for taking the time to make this video.
do you mind sharing the model?
Great Video very helpful, thank you so much for sharing.
You can use "=IF(ISNUMBER(SEARCH("-", cell)), LEFT(cell, SEARCH("-", cell)-1), LEFT(cell, SEARCH("+", cell)-1))" in your sheet to retain the numbers before xG, xC and xPTS and clear the rest!
😁
Brilliant job Kie.
Thanks Paul, I'm glad you enjoyed!
Excellent. Will definitely need to view multiple times to benefit from the info. Need to work on my excel skills for sure. Big Up Yourself!!!! Thanks
Thanks for watching Darren, working on your Excel skills is a decision you will not regret!
Thank you brother for your help it really means a lot to us
Fair play to creating your own analysis
That complex formula at 13:18 is contained within Excel (I'm using LibreOffice Calc and it's in there too) it would read "=Poisson.Dist(D44, $B$44, 0)", much simpler, you might need to change the cell numbers if your sheet isn't exactly the same as the video. You can use the same format for the home goals, again, adjusting the cell references as appropriate.
Yep! That does seem a lot more simpler than what I did here
@@KieMillett Yeah. I ended up applying Poisson Distribution to the table in this section of the video and got the same outcomes predicted. =POISSON.DIST($I7,$B$63,FALSE)*POISSON.DIST(J$6,$B$62,FALSE) I moved my table up next to the correct score table, so my cells are different, but it works for every model I made so far using your template.
Brilliant, I love this Mr.Millett
Thanks George!
Gracias por el viseo!... Saludos desde Perú!
Another cracking video Kie so much useful information I am going to try and setup a spreadsheet now.
I'm glad you enjoyed it Lenny, good luck making the spreadsheet!
Looks great thanks for the tips! I was wondering though if you need the goals scored in the league for the ratings? Surely that just pulls down on the good teams and makes a bad team look better since the average is higher? Good video though
Sorry in advance for the long post. I did this for my Mathematics dissertation in 2016, to give the pros and cons of using this model. The model is decently accurate but it depends on many factors. First it depends on the data you use whether the information would be relevant to the current matches so that means how long back shall you go my advice 2 seasons. Next the model over and under estimates goals means that teams who scored lots in the data will continue to score loads of goals and pick up plenty of point and vica versa for low scoring teams. This means in the seasons Man City and Liverpool finished with 98-100 points would have given great return but seasons where the title was won with low 80 points would have given poor returns.
what if insteed of this we do it with Poisson Distribution, what is your opinion on that? What you worked with Poisson Distribution?
Hi, do you mind giving me the dissertation name? I'm trying to get into this subject
Great stuff! I already have something similar that first assesses the optimal distribution (including poisson) for a given data range. Your workbook is much more manageable and user friendly. To anyone viewing the video and reading this, a month's patreon subscription for this workbook alone would be an absolute bargain of an investment for your betting and trading.
Thank you for the kind words Ged!
I Subbed! Thanks for this, it's been good fun setting it up after never doing excel before! One question - Liverpool v Wolves tomorrow night. Based on last 5 games, Wolves are predicted to score zero goals, because Liverpool haven't conceded at home during that time. A value of zero really messes up the data in a lot of the cells - is there anyway for the sheet to cope with this?
Hello, Kie! Brilliant Video! Firstly, I will become a member just to thank the help here. Do you think that the Poisson distribution will be useful to model yellow card bets ? Thanks again for the help! Greetings from Brazil.
Poisson model - gets you a prediction - but doesn't necessarily identify value - if it only were that simple!
It is not a model it is a calculation, a model is dynamic.
I know how to create a macro to take out those annoying "+numbers". Use "left" function. State cell and number of digits you want. Make a column of edited numbers u want, Copy pate them (text only" into new column. You can then make a macro to do these processes for each table, I would make a lil button at the top of each. (Edit shape into a button I find easiest)
Hey is there something I have missed can't seem to find where the average league goals conceded both home and away is used.
=INDEX(W3:W22,MATCH(A2,O3:O22,0))
Much easier than the IFS function.
Copy cell F44, then select the rest of the table and right-click, click Paste Special... then click Formulas, then OK. It saves having to repeatedly type in the formulas to multiply everything together and preserves your colourful formatting.
It doest work
I predicted 3-1 celtic yesterday and 1-1 pool , it was just instinct 👍. Ill try and understand ur formula. Could u give certain matches and give us an idea if a few possible scores? Thanks
Easier functions, work with other leagues' team names:
=INDEX(W3:W22;MATCH(A2;O3:O22;0))
=INDEX(AJ3:AJ22;MATCH(C2;AA3:AA22;0))
=INDEX(AI3:AI22;MATCH(C2;AA3:AA22;0))
=INDEX(X3:X22;MATCH(A2;O3:O22;0))
Thank you so much after searchin for an hour thats much easier than the ifs bloody statement
You are Really good at this obviously. It would be Really cool to see a model for props ( all from player props to card/cornor markets) Any chance You Will do something like that in the future?
Loved the video and became a free paetron member. However, just to clarify, in order to get the excel spreadsheets, you need to get a paid subscription, correct?
I did everything like you explained but excel keeps giving me errors like #NUM and #N/A. Is it because it's still early in the new season or what could it be?
Please i couldn't find the models on your site to download, can you assist me in doing that
Thanks so much! I have been trying to use my own model with lots of stats, this is way better and I can use it to compare!
Took my some time to pause the video every time and try to redo it, esspecialy because I did in on Google Spreadsheets, which meant I couldnt use commas but ; in formulas which took a lot of time to find out..
Anyways, massive thanks!
Thanks Manuel, glad it has been useful for you!
People get too bogged down with stats and place far to much emphasis on them when in reality they are pretty pointless and don't really mean anything
Every football match is a 90 minute random event with multiple variables.
Even the selection of a referee can alter the final outcome of a game
89th min of a game whilst at 0-0 one ref could wave play on for a foul on the edge of the box and the game ends 0-0
Another ref could see it as a foul and give the freekick on the edge of the box and they score and win 1-0
2 completely different outcomes dependant on 1 humans interpretation of a foul/no foul
Chelsea hammer Southampton 0-6, the next game Southampton beat Arsenal 1-0 and then the next game Arsenal win 2-4 at Chelsea
Chelsea lost to a team who lost to a team who Chelsea beat 6-0
Arsenal lost 3 in a row v Palace, Brighton and Southampton scoring 1 goal in those 3 games
Their next 2 games they beat Chelsea and man utd scoring 7 in the 2 games
Its all random and what the 2 team s do on the day is all that matter.
Past stats have zero relevance to future events
At the end of the day, results make stats, stats don't make results
@@SL0409 why dont u stop being a hater and give us a link to ur channel where u can show us how u go on about doing ur own predictions....this model is being trusted by serious traders and bettors for years...i personally being using this model for 5 years and there has been a lot of improvements with my bets...
the model alone is not enough , u also need to know ur football as well... and yeees the guy even mentioned that all models are wrong @ the beginning of the video....the combination of ur football knowledge and the model will do u wonders, believe me.....
THIS KIND OF NEGATIVITY IS NOT WHAT WE NEED!
Have you got a model for corners? I'm guessing this could easily be transferrable?
Hey am not good at excel but I know R.
Football data is hard to find in CSV format. Any sources you know of?
Hi Kie, absolutely brilliant video. Mind you I couldn't understand a word of it LOL as it is way above my knowledge of excel which is next to nothing! I know you said that no model was perfect but I just wondered if you'd used this at any time and how successful it was? Cheers
Hi, I use these models everyweek as a guide when I'm trading. I haven't gone to the depths of tracking to see how accurate they are (in all honesty I'm not stats based enough to know how to determine the accuracy of a model). Anecdotally I can say the model is good enough for a rough guide, but insufficient to be used in isolation to determine your trading decisions.
@@KieMillett okay thanks very much for your reply Kie
Hi Kie, thanks for the content!
Since the new season is about to start, do you think the model could be useful after how much of games?
5
Bonjour comment allez-vous quel est la formule sur excel utiliser vous pour predire htft car actuellement je fait un tableau de prediction de tout les ligue Je vous remercie. Dimitri
Can l do the stasstics with phone kindly advice or l mast have laptop to do model predictions actions.
Nice ❤
How would it be to put the ifs formula since my keyboard is Spanish? Could you guide me?
Are you aware of any sites that display Leagues like understat does? I want to incorporate different leagues but need to find them organised into alphabetical order…Thanks ❤
Bravo dude, bravo....one question, now burnley, norwich and watford have been relegated can I just replace the teams in the formula or do I need to redo the whole formula so as to keep them in alphabetical order ? Or can I just swap burnley for fulham for example despite the fact burnley are the 3rd team alphabetically and fulham aren't ?
Hi Michaels, When I have updated my model, I found the easiest way was to redo the entire formula so that the teams appear in alphabetical order. This is slightly time consuming but I've yet to think of a more optimal way of doing it.
Hello Kie. please i want you to help me out. am been stock along the line. the formular for average home team gpg scored at home down to average home team gpg conceded at home is not working on my sheet. i have tried to do and undo it many time but no way. kindly help me out. ie B37:B40
Also the poisson formula is built into excel. So in cell E44 you could have typed =POISSON.DIST(D44,$B$44,0)
good
does the sum of draw home or away win have to add up to 100%
Thank you greatly. Can you get one for VFL for me?
Great video, why does the table need to be in alphabetical order?
Tengo un problema, con la funcio IFS, cuando trato de hacer lo mismo Error
hey bro, sucha a níce video. You have source code on your Patreon ?
this is a good video, only that after this video you never showed how you make your picks from the model, I believe there are some who would like to see that especially those who never used the model
Hello, where can I get this spreadsheet?
Maybe I'm doing something wrong, but at 8:08 I can't select W3. It gives a error "There's a problem with this formula."
the calculator can be used for any league mike
what is manual formula for average home team gpg scored at home
Bottom line , do u try to predict a score before kick off ? And if so , do u put cover bets down to protect your original stake.
I have no idea what this formula you talk about is all about even though I listened to the whole thing 😁
Hi does anybody know the stats like footystats but for hockey? Thank you in advance.
I have put the whole formula in the "Avg home team gpg scored at home" cell and I am getting the following error. #N/A. I have double checked it and it is correctly entered. I am using an up to date Office 365. Anyone have any suggestions.
good
Thanks!
I subbed
Thank you very much Malcolm!
Have you looked at changing the data for second half score lines? For the start of the season how many games would you want to see before you start to use this data?
I haven't used second half data tbf. In terms of how many games before it starts working, that really is the million dollar question. There is obviously no perfect answer, the more data you get the better of course but the older the data becomes the less relevant it is (teams changing form etc.). I tend to create multiple of the same spreadsheet and use different numbers of games for each one to give me an overall better understanding.
Fantastic video - thanks for the super clear explanation! For this 2022-23 season do you think it would be useful to also incorporate last season's EPL data, and if so, how to incorporate it in the model? Thanks again !!
Generally speaking I wouldn't advice using data from the previous season.
como seria para poner formula ifs?
Will you be doing a model for 22/23 season Kie?
Available on my Patreon to download
Please can you help to know how to read match football ⚽
Btw. You can quickly delete the Plus and Minus symbols from the xG data @ 19:20 by using the "Text to Columns" option in the data tab
Hi, I did try this, however as you have to do it twice (once for the '+' and once for the '-') I found that I could do it the way I did it in the video slightly faster
@@KieMillett Try the find function (ctrl+f) first select column then find and select replace '+' with nothing
Hey, please can you share the excel sheet?
will there be one for 2023/2024
I KEEP ON GETTING ''IFs expects all arguments after position 0 to be in pairs'' while filling out the average away.... just like at 9;32 of the video. help?
Hi, were you able to get this issue resolved?
This only works in Excel versions newer than Excel 2016 as the IFS function doesn't exist in earlier versions of Excel
But you can make it work with nested IF statements should you need to get it working
Thanks for pointing this out, I didn't know this about the IFS function
Could you have not just done a vlookup for each team in the table, instead of having to have the super long formula clicking on each team?
Merhaba ben Türkiye den öncelikle başarılar dilerim güzel bir algoritma ile hazırlanmış bir dosya olduğunu her halinden belli peki bu dosyayı bende kullanmak istesem nasıl temin edebilirim şimdiden teşekürler
What do the implied odds mean? Any help would be appreciated
The implied odds are like the odds you would see on a betting website they are the match odds
The link is not working. I tried to follow you to make my excel model but the explanation is too fast.
The link is working fine for me. You can slow the video down through CZcams and rewind where necessary.
What are the predictions in real game Is this successful?
Definitely not
Please post odds
I need some one from out the country to work with fro betting games and I am ready to do everything
We can help you get this model. Let us know if you are interested
wow it is grat
you could learn how to write all these formulas for excel or you could just as easily build a time machine that lets you transport from the past back to the future...
Brother, with Index and Match you save yourself the uncomfortable yes formula, which extracts the average number of goals from you.
I wasn't aware of this, would save me a lot of time
@@KieMillett as an example, for the Avg Home Team gpg scored cell the formula would be =INDEX($W$3:$W$22,MATCH($A$2,$O$3:$O$22,0)) . Great video by the way!
Pervious data is not going to help you in anyway 2bh as you see many underdogs beat favorites all the time in all types of sports I made a ton of money that way as the odds on favorites are really low so can lay them with low liability and cash out after 1 goal with a decent profit even with a low stake as liability is low
Hi. Please can you help me, am tired of losing a game
Great video. I think that your “ifs” formula can be replaced using a simple lookup formula e.g. in B37 type “=XLOOKUP(A2,O2:O21,W2:W21) but I may be wrong.
Unfortunately, I am not the greatest with Excel. Things like this I should learn to save me time in the future 😊
What is a specific site using?
Brilliant! for those using an excel version without XLOOKUP functionality, use LOOKUP.
@@forty4096 VLOOKUP(A2,O3:X22,9,FALSE) definitely works. The 9 represents the 9th column of the array.
Bro I was done that formula and it disappeared so then I saw this and you just saved me 2 hrs of my life
great video Kie, thanks! I set up a predictor for all leagues from understat. Are there other websites that cover other leagues? All the UK leagues would be great....anyone know?
Also, I have a fast way to remove the XG + and - numbers. highlight the columns, ctrl+H (find and replace) in the find box write -* and leave the replace box blank. then press replace all. change to +* and replace all. job done. :)
I'm creating a football betting community and I'm looking for football fans who are good at predicting and enjoy betting on football. Let me know if you are interested. :)
Keep up the good work, best of luck with your bets.
You got discord?
Where is the excel bro?
Replace your IFS formulas with a Vlookup
I'm not familiar with V Look up, this is something I must check out at some point
can you please share the excel sheet again?
If you are still in need for this model we can share it with you
Getting an #N/A error on :
1. Avg home team gpg scored at home
=IFS(A2="Arsenal",W3,A2="Aston Villa",W4,A2="Brentford",W5,A2="Brighton",W6,A2="Burnley",W7,A2="Chelsea",W8,A2="Crystal Palace",W9,A2="Everton",W10,A2="Leeds",W11,A2="Leicester",W12,A2="Liverpool",W13,A2="Manchester City",W14,A2="Manchester United",W15,A2="Newcastle United",W16,A2="Norwich",W17,A2="Southampton",W18,A2="Tottenham",W19,A2="Watford",W20,A2="West Ham",W21,A2="Wolveshampton Wanderers",W22)
2. Avg away team conceded per game away
=IFS(C2="Arsenal",AJ3,C2="Aston Villa",AJ4,C2="Brentford",AJ5,C2="Brighton",AJ6,C2="Burnley",AJ7,C2="Chelsea",AJ8,C2="Crystal Palace",AJ9,C2="Everton",AJ10,C2="Leeds",AJ11,C2="Leicester",AJ12,C2="Liverpool",AJ13,C2="Manchester City",AJ14,C2="Manchester United",AJ15,C2="Newcastle United",AJ16,C2="Norwich",AJ17,C2="Southampton",AJ18,C2="Tottenham",AJ19,C2="Watford",AJ20,C2="West Ham",AJ21,C2="Wolveshampton Wanderers",AJ22)
please help.
Hi Erick,
I've had a quick look at your calculation, one thing I did see is that you have put "Wolveshampton" instead of "Wolverhampton". The syntax of the equation looks correct to me, without seeing your spreadsheet I can't confirm if there are any other errors in terms of cell references
Kie
@@KieMillett Thank you much. I corrected and Its working perfectly. 🤜
Can someone download this worksheet for me please
Spreadsheet is available on my Patreon, obviously not everyone can afford to support the channel or would want to. This is why I gave step by step instructions on how to make this spreadsheet yourself in the video!
CAN YOU DO NBA PROBABILITY USING THIS MODEL?🙂🙂
NBA is something that I have never looked at.
@@KieMillett I experimented using this method but I couldn't get it 😆 . Can you do the NBA probability?❤️
can you upload it to google sheets?
It's the exact same
If you are still looking for this we can share it with you
Hello boss
ITS A LONG STORY :) HOW CAN I GET THE FORMULA WITH ALL LEAGUES ?
Sorry, but I don't know a way to view all leagues in this way. Tbh I would focus on a smaller number of leagues to begin with.
@@KieMillett i try to understand, if i join on a membership i will have those 5 leagues ?
You said we can download it, but I can't find the free spreadsheet. Why is it 15$ per month? How many people have you scam with this trick?
I said you can download it if you support me on Patreon. If you don't want to support that is absolutely fine, however you will then after follow the tutorial in the video.
It gave me error at the ifs function
Hi, were you able to get this resolved?
Bro just use a vlookup
Hello,nice ideas ,...
Do you have a betting site we can subscribe and do payments as you give us teams
Hi do have idea some betting legit site? Pls. Can i join. Thank you 😊 keep safe
This jumping jack gives nothing for free
Text me for today's correct score
A workbook that costs £9. If it works why sell it? Use it alone in silence!!!!!
You are too fast in your presentation