ANALYSE Football Matches for Win %, Over/Under and Correct Score using EXCEL! | Tutorial
Вставка
- Опубліковано 8 лют 2025
- Download: / analyse-football-10383...
Need a spreadsheet built? Visit: www.excelladz.com
EPL Football Reference Stats: fbref.com/en/c...
LaLiga Football Reference Stats: fbref.com/en/c...
Ligue 1 Football Reference Stats: fbref.com/en/c...
Bundesliga Football Reference Stats: fbref.com/en/c...
Serie A Football Reference Stats: fbref.com/en/c...
Need a spreadsheet built? Visit: www.excelladz.com
My apologies lads, there's a minor error in the video from 27:20 to 27:50. To calculate the probability of both teams scoring in cell I14, the formula is: =SUM(D23:M32). The formula for cell I15, the probability that both teams DO NOT score, is: =1-SUM(D23:M32). Thanks for watching 🔥
Such a good video! A cool thing to add would be when picking the 2 teams for the poisson it could pickup the league and after that do an automatic Monte Carlo simulation to see the probability of where each team could finish! Now I have no idea how that could be done but that could make this sheet even better imo
Thanks for watching lad 💪 Yes, that definitely can be done! The only problem is that the number of simulations required to get that result (simulating every remaining game of the year, for 1,000 plus season to get an idea of the distribution) would slow the file down and greatly decrease its functionality. To get this result, it’s best creating a dedicated file for just that. I’ve actually done this for the EPL, check that video out lad!
@@excel_ladz Yeah I’ve already watched that video. It was great. To not slow the file down isn’t there a different way to do the simulations without having to do the 1.000 simulations under each other? I mean like a formula or a mathematical equation or something like that that could make it smoother?
@@Rasmus98 Hi, mate! Sorry, I only saw this comment now. I suppose you could build a formula to approximate a team's finish based on their points and their position on the ladder. It would be very speculative though, and would have to be built based on historical data separate from the team in question. Monte Carlo simulations are really the only proper way to do it, as it takes into account the difficulty of a team's upcoming games as well 👍
@@excel_ladz thanks for your response
Once again, he delivers. Thank you as always.
what a beauty ❤ i love this channel!
in theory it sounds good, but the problem is in reality.At the end of the season, when many games have been accumulated, the results can be better although an essential factor comes into play here: whether the game is still important or not.
but if you calculate XG after the first 10 games,and you calculate once more after 30 games the Xg is it is very different.What this means?it means that from stage 10 to stage 30 (or 26 or 32, it is not very important) you bet wrong. You bet on some scores based on an Xg that had no accuracy.
Hey brother, I am still your Chinese fan, I have a suggestion: Can you please add the selection of Asian tennis disc to the Excel sheet of this video? (e.g. : the home team conceded 0.75 goals 1 goals 2 goals and the visiting team conceded 0.75 goals 1 goals 2 goals) or the exact number of goals! (I know you work hard) is a little advice, see if you have similar ideas, you are really great!❤
One doubt, when calculating BTTS the formula should be 1 - sum of the probabilities of all correct scores with zero considering home and away (0-0; 0-1; 0-2; 0-3; ...0-x ; 1-0; 2-0; 3-0; ...x-0) instead of just adding correct scores 0-0, 1-0 and 0-1. Am I right?
Oh my goodness, you’re right! I can’t believe I overlooked this 😢 I’ll look to edit this in the model 👍 Maybe I might re-upload the video. Thanks for letting me know lad, I appreciate it.
great video how do you get your raw data in like it is
Power Query from the website ‘Football Reference’. Link should be in the description 👍
I’m curious how good your models are. Anyplace we can see what results you’ve had?
I doubt that the profit is very high unless there is huge luck involved. The biggest problem with these models is that they use league average, not accounting for home/away splits. Also not accounting for whoever plays in the game (some players have a lot more impact on the score than others) and as mentioned in the comments, some games have more value for a team than others. Think about already relegated teams or teams that use bench players as they have more games because of cup duties.
These are great to help you get started in excel and to understand averages and numbers but to actually put money on these outcomes is real gambling imo.
How to calculate the actual home advantage rather using arbitrary number ?
will this work as well in Apple Numbers?
Can u show us how u use your power query ?
I would like to know if there is an option in Excel to make a probability matrix for a team to finish in a respective position in the league or at least a table of expected points.
Hi lad, there definitely is! You would just simulate every game in a season, then simulate that season 1,000 or so times to see how every team did. I did this in the following video: ua-cam.com/video/0tzP_joPkIo/v-deo.html
@@excel_ladzThank you!
Hello Ladz, if we already have the Xgoals fo each team how can we apply the home advantage coeeficient to adjust the xG ?
G'day, lad! If your total home advantage was 10%, then you would multiply the Home xG by SQRT(1+10%), and the Away xG by 1/SQRT(1+10%). This would ensure you get a total xG advantage of 10% (or whatever your advantage figure is) for your home team 🔥
Can you make a video about power query
I can make work home XG calculation work fine but when I drag the formula down (14:00 mins) and change to "away_xg" I get an error? Any suggestions? Thanks
What version of Excel do you use?
Hello! Tell me in what form I will get access to match analysis? Will it be Google Sheets or something else?
G’day lad! By becoming a member of the Excel LADZ Patreon, you’ll be able to download the Excel file exactly as I’ve built here 👍 This has a Power Query connected to it, so the stats update automatically whenever the data is refreshed 😃
@luxuryway6227 He has never tested any of his model with upcoming fixtures. Wont waste money any of his models
can you explain how i convert this in asian handicap lines e.g. -0,25 and + 0,25 asian handicap. Would be great
Bro, would this be also be applicable to NRL/Rugby league?....
Hey lad =games formula for your other tutorial refused to work on my PC
How do you become a member
G’day, mate! Check out the first link in the description of the video 🔥 By becoming a member, you can download any model you like and ask me any questions about Excel and sports modelling 😃
How can i reach you
You said with live an updating data, will this update for the next season?
Yep! Once the season starts, the data will be replaced with the 24/25 connections and the model will run like normal 👍
@@excel_ladz so, once we have the sheet it will work automatically?
@@seychelles44 That's right 👍 As soon as I build the 2024/25 season sheet, it will work automatically for the rest of the season. You can also download the finished 23/24 version, and replace the stats yourself 😃
Can you do how to project minutes in the NBA?
Good idea lad! Thanks for watching 💪
Can you do a statistics on who likes to go to school and who doesn't like to go to school
does the all the data updates automatically, second question do you sell these Models or no? because i want to buy if its so.
Hello, lad! Yes, all the models are able to be downloaded, including this one, by becoming a member of the Excel LADZ Patreon here: www.patreon.com/ExcelLADZ
The data in these models are connected to the internet via a Power Query, so the data updates automatically once selecting the "Refresh All" icon in the 'Data' tab. I hope to see you in there, lad 😃
@excel_ladz ok, thanks for your reply
Bu dosyayı nasıl indirebiliriz?
Can you do this for American football 🏈 with player stats and with the schedule just like this one here and weather ☁️ in all city's and player injuries 🧑🦽and lineups that would be great 👍