ANALYSE Football Matches for Win %, Over/Under and Correct Score using EXCEL! | Tutorial
Added 2024-05-07 20:33:00 +0000 UTCG'day lads!
I've put together a model that uses the live stats and the Poisson distribution to predict EPL, LaLiga, Ligue 1, Bundesliga and Seria A games. All in one dashboard!
Note: The 'Normal' uses array functions that are only available to Excel 2019 users and later. However, the 'Legacy' file should work for ALL versions of Excel 💪
Simply select a league in cell A3, select the matchup from the dropdown list in G1 & G3, and the results will populate. To update the data, go into the 'Data' tab and select the "Refresh All" icon. This will refresh the power query's connection to the internet and import up-to-date stats.
Comments
G'day, Will! I've just updated the post with the 2024-25 version that works 👍 This can also be seen in the most recent post on the Patreon 😃
Excel LADZ
2024-08-20 11:57:12 +0000 UTCWill there be a new season update. i refreshed data and it doesn't like it
Will spare
2024-08-20 07:13:55 +0000 UTCHi mate, the correct score probabilities are found my multiplying the probability of Team A scoring x goal, and Team B scoring y goals, for a x - y scoreline. For example, say Team A have a 20% chance of scoring 1 goal in a match (as determined by the POISSON.DIST function). Team B may have a 5% chance of scoring 3 goals in the same match (using POISSON.DIST). The chance of a 3-1 Team B win is 5% * 20% = 0.01 = 1%. Therefore, 3 - 1 scoreline has a 1% chance of occurring. This equation assumes that Team A's goals in a match and Team B's goals in a match are independent; one does not influence the other. To work out the probability of Team A scoring 1 goal given they are expected to score 1.45 goals in a match (xG = 1.45), you can use the POISSON.DIST function. =POISSON.DIST(1, 1.45, FALSE) = 34.01% 👍
Excel LADZ
2024-07-14 07:50:02 +0000 UTCThe correct score should have been provided accurately. Personally, I don't grasp the nuances of your correct scoring method.
spoilbrat42
2024-07-14 07:00:23 +0000 UTCHi lad, yes it is 👍 I highly recommend taking advantage of the community there 🔥
Excel LADZ
2024-05-24 01:45:20 +0000 UTCIs the discord server available only for patreons?
Aylas
2024-05-23 17:22:25 +0000 UTCThanks lad, it's now working again. Yesterday, after I refreshed the data. Each team had "Vs" before their name (e.g. "Vs Liverpool"), so no data was returned. Today, it's back to normal, so I suspect a brief issue with fbref.com caused it.
Iain Moran
2024-05-18 08:06:33 +0000 UTCHi lad, thanks for letting me know. I tested both models and the data was working fine for me. If you're experiencing an error, do you mind sending me a screenshot through Discord? That way the error can be tracked down better 😉 Yes, the video tutorial would be good for everyone I reckon 👍 Obviously though I want to make sure there's no issues with the data 🔥
Excel LADZ
2024-05-18 00:44:22 +0000 UTCThe model has stopped working. I suspect thet the data site has made some changes. Maybe time to make a video showing how to add new leagues and teach us how to correct things when the data changes :)
Iain Moran
2024-05-17 13:29:08 +0000 UTCModel is having some issues...
Kerry Coleman
2024-05-17 03:46:58 +0000 UTCand/or team shots/freekicks/throwins
ryy ryy
2024-05-14 23:21:15 +0000 UTCcould you do player shots/passing? that would be perfect !
ryy ryy
2024-05-14 23:19:53 +0000 UTCIf you could make a video it would be awesome, lad. The five leagues on this sheet, all finish in a couple of weeks, so a step-by-step tutorial on how to add some new ones would be appreciated!
Iain Moran
2024-05-14 19:25:04 +0000 UTCthanks, mister
Victor Saul Parra Rodriguez
2024-05-14 04:19:05 +0000 UTCGreat question lads! It would involve bringing in a new Power Query for whatever league you wanted to add. For example, the MLS. Now, using a combination of formulas (depending on how the MLS stats were brought in with the Power Query), you would create the MLS Table/Standings like I've done in the "Data" worksheet. Then, name the worksheet "mis_table". Add 'MLS' to the competition range, then add "mls" to the competition_code range. This will enable you to reference the MLS Table. Then, all the formulas in the "Dashboard" worksheet should update from there 👍 Perhaps I should do a tutorial video on this 😃
Excel LADZ
2024-05-13 12:02:23 +0000 UTCYes, I would like to know this too.
Iain Moran
2024-05-13 09:12:04 +0000 UTCYes it has been properly fixed lad 😃
Excel LADZ
2024-05-13 00:58:49 +0000 UTChow can i add other ligues? for example mls,mexico,etc
Victor Saul Parra Rodriguez
2024-05-12 03:46:23 +0000 UTCHas the BTTS calculation been created on this model?
Iain Moran
2024-05-11 13:03:31 +0000 UTCHow can we change the leagues for ones that play through the summer?
Iain Moran
2024-05-11 12:56:23 +0000 UTCGreat question lad 🔥 I'll look into that and see if it's an easy feature to add into the model. I'll let everyone know in a post when this change has been made 💪
Excel LADZ
2024-05-09 23:37:49 +0000 UTCHi lad, this file only updates throughout the 2023-24 season. However, once that is finished, I'll make a new sheet connecting the 2024-25 data that everyone can download 👍 I would love to have had the model keep updating season-to-season by itself, but the website for 2025,26,27... seasons haven't been created yet 🤷
Excel LADZ
2024-05-09 23:36:31 +0000 UTChow do i change to my time zone?
Jonny Hoareau
2024-05-08 16:39:53 +0000 UTCwill this automatically update for next season and so on?
Jonny Hoareau
2024-05-08 16:23:57 +0000 UTC
