Your Excel skills are top-notch. Tell me, how can I update the matches on the excel on a regularly basis without interfering with the data in the table?
Great 🤓video! Even learned a few new tricks on the way and have a new reference point of fast formula writing. And just love the way DATA is pronouced in your part of the world (I'm Danish). Absolutely going to check out your other content.
Great video and model. I'm trying to re-build it, but is having a small issue with the games drop down on the Dashboard. When I select the drop down I only get the first game for each date. The drop down says (e.g. Jan 13): "Rækkemærkater" (Danish equal to Row Labels) "Alcorcon vs Mirandes" When I see the Games sheet everything looks fine. All games from coloumn A copied into column B. Do you have any idea what the problem can be?
Hi lad, thanks for watching 🔥 My first suggestion would be to ensure that the copied timeline in 10:10 of the video updates the 'games' range when you change the date. You can just switch back and forth between the 'Dashboard' and 'Games' worksheet to doublecheck this 👍 My second suggestion would be that when you select the 'list' in Data Validation, make sure the boxes 'Ignore blank' and 'In-cell drop-down' are both selected. This can be seen at 10:40 of the video. If none of these work lad, it may be due to your version of Excel not supporting dynamic ranges or arrays. It's odd though that Column B of the 'Games' worksheet works yet your Dashboard worksheet dropdown list is not. Let me know if that helps lad 😃
Brilliant idea Mr. Lad! Video already saved in my favorites! :) Once I skimmed through that club ELO API but I didn't pay much attention to it. I'm glad you got this great idea. Thanks! Idea for you (if feasible): Dixon-Coles implementation of Poisson Distribution. Basically the two gentlemen added a parameter to the two independent Poisson Distributions of a matchup with the objective of creating a kind of dependence between the two Poissons and correct the low score probabilities (1-0, 0-0, 0-1). The challenge is calculating that parameter. I've seen it done in code (R or Python) but maybe it is not feasible in Excel... Cheers
I'm glad you liked the video lad! Your comment is very interesting. I have read about certain limitations of the Poisson distribution surrounding the low scores, so I'll definitely do some research surrounding the Dixon-Coles implementation. However, this will most likely take some time, so unfortunately it may not be a video in the short term.
Thank you so much, I won using your technique in data analysis which is the first time I ever won a football bet. Again thank you. I just have one question on how to update the data from the API given. I tried to copy paste but it isn't reflected on the pivot table even after refreshing it
I'm glad you won lad 💪 I would try and keep re-doing the process, trying to 'Refresh All' (found in the Data tab) as soon as the data is copy and pasted into the spreadsheet. Make sure you update the slicer 👍 I hope that helps lad, let me know if you have any other issues 🔥
Thank you for the excel tutorial. But did you have any success actually betting with these predictions? Can you share some experience? How many games did you need to make a more or less consistent net gain?
Hello Lad. Any chance you know anything about VBA code/macros and make downloading the data automatic? so we don't need to copy and paste the data every other day or whatever it is? This would be legendary.
G'day lad, thanks for watching! I usually use a power query on Excel to import data that updates automatically (so I don't need to copy and paste the data every day). An example of this is my NBA video where I import live stats to predict games. The reason I didn't do it for this particular video was because it was mainly geared at beginners who, without prior experience, would find obtaining the live date tedious and difficult. However, future videos on Football ELO Models will definitely feature live data lad 👍
G’day lad, I would firstly find the most reliable site. There would be a million different ways to compile ELO Ratings, so use the most well established ELO website for the NFL. The formula to compile the win percentage based off of the ELO Ratings will differ with each system… While I don’t recommend you get the actual ratings from Wikipedia, it’s good for searching for mathematical formulas to transform the ratings into win probability. ELO Ratings only deal with win/loss scenarios. As a result, any expected goals calculations as well as draw probabilities cannot reliably be calculated. I know all this is probably common sense, but I hope it helps a bit lad 🤞
Hi lad, thanks for this impactful video, however I'm stuck where in the pivot table when I put in = games, it only shows one game not all of them and also on the dashboard my formulae bring errors. Kindly enlighten, what I'm I doing wrong
Hi lad, thanks for watching 🔥 You may not be doing anything wrong... Unfortunately later versions of Excel (e.g. Excel 2016 and later) don't recognise the XLOOKUP function. Is the #name error appearing on your dashboard? The solution for this would be to replace all the XLOOKUP functions in the formulas with a VLOOKUP function. I hope that helps lad 💪
Will try that tomorrow. I couldn't get the list of games to appear when you typed =games around 9 minutes into video. It only shows one match when I do it. Not a list
G'day lad, I'm just using an XLOOKUP function to pull in the data values for each scoreline. If the stats we imported say that a 0-0 draw has a 0.121 likelihood, then I'm just grabbing that and plugging it into the dashboard to make it easy to read and understand 👍
Thanks lad, I appreciate the support. Usually I would definitely use a power query to import the data, which would update automatically in Excel. However, this video was more geared towards beginners, and at first the power query skill is a bit tedious and difficult to complete. Future Football ELO models will absolutely include a power query and automatically updated data lad, stay tuned 👍
G’day lad, unfortunately with the way the data is pulled, there’s no concrete way to backtest the model’s suggestions. However, you could keep a running ‘success rate’ of the model and track its success. Of this was helpful lad, please subscribe!
Hello, great video but I can’t get this to work, when I copy it it doesn’t look like it does in the video, it turns up directly into excel and it won’t transform it to % either, I really don’t know what’s wrong here,
Hi lad, I’ll try and give you a couple broad suggestions in the hope it’ll fix it for you. Wit regards to the percentages, you have to convert them yourself by selecting all the relevant cells, and then clicking on the percentage icon in the home tab. Once all your data is in, go to the Data tab and select ‘Refresh All’. This will make sure your pivot table updates, and you can then use the slicer to file yet games by date. If you’re using an old version of Excel (one that doesn’t support XLOOKUP), you’ll have to write the formulas with VLOOKUP functions 👍 I hope that helps lad
Hi, I'm having hard time seeing the formula using wrote under game_ID and the other formula at the top. I would appreciate if you can write them out for me. Thanks.
Greetings, Excel LADZ. I absolutely love these videos. Fantastic work! I am trying to model a league of my own (purely as a hobby). May I ask if you offer one-to-one Excel tutorials on a commercial basis at all? Thank you.
Thanks for watching lad, I appreciate the support. With regards to your question, in the future I will be making a football video in which multiple parameters (e.g. possession, shots, passing, etc) are used in the model. And then from then, maybe rather than simulating the goals scored in the match, I can use the poisson distribution to solve for each team's chance of winning the match. I hope that helps lad.
@@excel_ladz is there a way you can add an output of correct scores, highest and the second highest values with there corresponding correct score outcome and percentage? for example, probable outcome 3-2 14%..
@@reportspayment Hi lad, I do this at stage 19:30 of the video. Instead of spilling out the highest probable scoreline though, I use conditional formatting to highlight it for me. This is shown in 20:30.
Could you create a rating model for soccer players? It would also be interesting to create a power ranking Sorry if the English was not written correctly. I am Brazilian and still learning
Don't worry lad, your English is good. Perfectly understandable. As to your question, the idea of a rating model for individual soccer players was something I hadn't previously thought of. So that's a great idea I'll definitely look into. In terms of power rankings, that's easy for me to create once I've done the ratings for players. Thanks for the comment lad, great ideas for future videos!
G’day, thanks for watching! You can update the fixtures by deleting the old matches, and replacing them with a new copy and paste from the Club ELO Ratings Website (like we did at the start of the video). From there, use text to columns to clean up the data (again, exactly like the video). Then, click the Refresh All button to refresh all worksheets, and the model should update automatically 🔥
Hi lad, in the near future I’m considering setting up a website/page where all of my Excel models will be available for purchase. Would you be interested in this lad?
Thanks for the video, Im little slow so it took me ages to finally make this, but now im wondering, when we get new upcoming games, how do i add them to this existing model? I tested with adding the same games again to the rawdata and made new pivottable with the new ones included but the new ones i added wont show there? do you know what i mean, I would appreciate it if you can help me.
Mr. Lad! That's amazing video! You help us a lot with your teachings. I'd like to ask about API. Do you know where can I find any API similar to ClubElo to put more championship? (for example: J League, MLS, Australia, Brazil). Thank you!
Hi lad, thanks for the comment 🔥 As of right now, unfortunately I don't know a reliable website where you can find ELO ratings for leagues outside of Europe 😢 I'll be sure to post it in the community tab of the channel when I find one of similar quality to the ClubElo ratings 👍
Hi lad, I’m really glad you enjoyed the video. The what-if analysis function is not really relevant in this video as the win percentages are already calculated for each team. When I use what-if analysis, it’s for a Monte Carlo simulation where I can simulate scenarios on a large scale. Then from there, the probability of each outcome makes itself apparent, e.g. Arsenal beats Man United 3450 out of every 10000 simulations, therefore Arsenal have a 34.50% chance of winning. I use this method when I do not already know the probability distribution for a game. I hope this helps lad.
That's a shame. If I remember correctly, you were having a problem with the Pts Sim? I've got two new methods for you to try and solve your problem lad. Firstly, can I confirm you are using an English version of Excel? There was one subscriber who's issue with the formula was immediately fixed, because he was using the French version of Office, and so the BINOM.INV formula was written differently. Secondly, try breaking up the formula into parts. To simulate the 2 pointers, enter in: 2*BINOM.INV(BINOM.INV(F12,G12,RAND()),H12,RAND()) If this simulation works and changes every time you update the cells, then move onto the 3 pointers and Free Throws until the formula for Pts Sim. is complete. I hope this helps you lad, let me know how it goes.
@@bobnicholson6350 Try entering this: =2*BINOM.INV(BINOM.INV(F13,G13,RAND()),H13,RAND()) for Row 13. This should spill out the simulated points for only 2-point shots for that player in Row 13.
Thanks for watching lad, make sure you're subscribed 👍 . In this video I use the XLOOKUP function to grab the home team. However, only Excel 2021 and the Microsoft 365 versions of Excel have access to this function. If you are using an older model of Excel, you must use VLOOKUP instead of XLOOKUP (it's a relatively easy switch between the two). I hope that helps lad 💪. If not, make sure you comment again and I'll get back to you as soon as possible.
G’day lad, the website has data for all Major Leagues including the EPL, Serie A, Ligue 1, Bundesliga, La Liga and many more competitions. I would try the model on another day, such as before the next EPL competition weekend, to see those games come up. If this helped lad, make sure you subscribe for more 🔥 Follow me on Twitter @excel_ladz 💪
G’day lad, because this is a very simple model I haven’t included the process of a power query. If you’re able to, a power query would update the stats automatically for you. 👍
Hi Which version of excel are you using? There is a problem with the formulas =SI(Games!$D$11=0,"",Games!$A$4:DECALER(Games!$A$4(Games!$A$4,Games!$D$11-1,0)) error : not enough arguments to this function
Hello Lad. I just had a suggestion, there are some markets for betting over and under goals. How can i I do it using this elo rating method. It seems to work I just don't know how to go about it. There could also be a filter to generate games that have goals over or under a certain number
Hi lad, very sorry for the late response. This is the formula I have inputted for cell AT2 in the 'Raw Data' worksheet under the 'Game_ID' header: =IF(ISTEXT(C2)=TRUE,C2&" vs "&D2,"") Try this lad, and let me know how it goes 👍 .
Hi lad, yeah that shouldn’t be the case. The sum of all probabilities for a match’s scoreline should be 100%, so there’s probably an error somewhere in your formulas (if the imported data is correct). Let me know if you have anymore specific questions lad 👍
G'day lad, sorry for the late reply. First of all, make sure your Game_ID formula at 3:21 of the video is correct. Then, at stage 8:15, go into Name Manager in the Formulas tab, and make sure you exactly type in the same formula I do under the name 'games'. If you still have further troubles, try to be as specific as you can when commenting so I can help you fix it lad!
G’day lad, I’m using Excel with a Microsoft 365 subscription (the latest version). The ISTEXT function is available on all Excel versions dating back until 2000, so I assume your version can support it 🔥 Just make sure that your ranges are correct lad, and make note of the specific error your formula is returning (e.g. a #NAME? error) which could help you solve your problem 💪
Thank you for watching lad 🔥 I’ll try and release a video predicting this season’s playoffs using a basic model. If unfortunately I’m unable to release this due to it being too late to publish, I’ll definitely release a video walking my subscribers through how to predict NHL games/seasons/playoffs using Excel in the short term 💪
Thanks for watching lad, but unfortunately the website I've imported the date from does not provide first half / second half data for us to calculate. This is an extremely simple model, a more detailed football model which I'm planning to upload int he short term will go though half analysis.
Thanks for watching lad 🔥. To update the mode for the upcoming games, simply copy and paste in the new raw stats from the website (the text to columns part only takes a couple of seconds). Then, go into the Data tab and hit Refresh All or Refresh Data. If you do these two very quick steps (takes under 30 seconds), then the model will update for that new day 💪 If this helped lad, make sure you’re subscribed 👍
@@excel_ladzm having issues.. I can’t workout a few things. For some reason I keep getting 0 for team names on the dashboard. And I have 0% on the heat map. I’m now at a point where I’m stuck. Can you just add a link under the videos to where we can just download the sheets? Cheers, and I must say; this is the first ever RUclipsr I’ve seen where you don’t need to even ask for subscribers. When I try to save the worksheet, my laptop says some data maybe lost if I save as a CSV file. What shall I do? When I updated the sheet, it seems to only put data in the left hand side of the sheet in A1 only. And back to original format. I think it would be easier if you have a link to download the sheet from. Sorry if I’m being an absolute T@@T.
@@seychelles44 Don't apologise lad! It's my job to answer questions from loyal subscribers 👍 1. If you're getting a 0 for the Team Names in the Dashboard then there's a couple things that could be wrong. Firstly, check that you have access to the XLOOKUP function. Only recent versions of Excel have it. If you don't, change the function to VLOOKUP and adjust accordingly. Secondly, you may simply have just entered the XLOOKUP formula wrong. However, if the XLOOKUP formula (12:00) is correct, that means your Team ID's column has not been formatted like mine. This means the XLOOKUP is searching for the wrong range to return. 2. If you're getting 0% for the heat map, then it's more than likely it's the same issue here. Check if you have XLOOKUP, and then check if your data is formatted EXACTLY like mine in the video. 3. I'd save this file as a regular Excel worksheet. When you update the sheet, do what I do at 1:25 and go to the Text to Columns Wizard. I hope this all helps lad 💪When I become monetised on RUclips, I'm looking to start a membership where I can build up this Excel LADZ community some more. Through this, the link to all Excel LADZ models will be available 👍 Thanks again for being a subscriber 🔥
@@excel_ladz nice one. I’ll check it out and see how it goes in a few days when I get more time. I’ll also look into other ways to find a system that works well with this. Cheers
This is good - is the data on Club Elo actual data rather than expected? One thing I’ve noticed is that the win % it gives can be very different to Poisson for example
Hi lad, I myself have found that the Poisson method is much more accurate than the data this model imports. The ELO data is based on scorelines, and gives no bearing to who was playing in matches and the importance of a match to each club... This means, a Poisson model, factoring player strength in each match, is a much more predicative method 👍 Very soon lad I'll be releasing a video on how to build this model on Excel 💪 Thank you for watching lad!
@@excel_ladz yes agreed - it would be good if you could alter the future model to exploit the other markets which aren’t as liquid and are more exploitable, like win to nil, for example. The 1x2 markets are notoriously tough to beat as the bookies have these models dialled in. I personally have been having more success with bet builders and goalscorer markets. The goalscorer market you can reverse engineer the market odds to give some success.
Most computer models use what's called the 'Monte Carlo Method' to calculate the probabilities. If you have any further questions lad, just ask me about it 👍
@@excel_ladz But not sure what model clubelo is using. Maybe Poisson distribution? But not sure how to get an expected mean solely based on Elo ratings.
Hi lad, I haven't yet combined ELO Ratings with the Poisson Distribution. With Poisson, I normally go a different route and calculate Attack and Defence ratings for each club and then for each matchup calculate xG. An example of this is in the first ever video I made on this channel.
Given two teams, A and B, with Elo ratings Elo_A and Elo_B, the probability of team A to win the match is: 1 / (1 + 10^ ((Elo_B - Elo_A)/400) ). For Team B, just swap Elo_A and Elo_B in the equation. Elo rating doesn't account for draws. Also I don't know how club Elo is calculating the goal difference using Elo ratings.
@@3ecke11er I didn't get your method, can you elaborate? You calculate the goal expectation probability of each team using Poisson, and then you multiply the two Poissons to get the probability of match results (0-0, 1-0, ...), right? At least this is the most common way of using Poisson.
Hi lad, it should still work in Excel 2013. All you have to do is replace every XLOOKUP formula to a VLOOKUP. This is because unfortunately, XLOOKUP is only available in 365 Microsoft subscriptions as well as Excel 2021 and later.
Hi lad, if you already have the decimal values then it just involves changing the format from General to Percentage in the Home tab. However, if you're unable to get any values, double check your XLOOKUP formula; if you don't have access to XLOOKUP use a VLOOKUP formula 🔥Thanks for watching lad!
The data from the source was not that accurate since most of the predictions were wrong , with team statistics why don't you get your own winning,draw, losing chances
G’day lad, I 100% agree with you. There are many flaws with this website’s calculations. Have a look at my NBA video where I walk through how to build a model, completely from scratch, on how to predict basketball games using player stats. Thanks for watching the vid lad, make sure you’re subscribe for more 🔥 Follow Excel LADZ on Twitter to be able to DM me about any sports modelling questions (@excel_ladz) 💪.
Hi lad, unfortunately this website doesn't provide stats for South American football leagues 😔 However, very soon I'll publish a video on 'How to Predict Football Games using the Poisson Distribution' that will be a much more accurate system of predicting matches than this video. Furthermore, you will be able to apply this to South American leagues 👍 Thank you for being a loyal subscriber lad 💪
G'day lads, if you want access to this model (and every other Excel LADZ model), join the Excel LADZ community! Sign up here: www.patreon.com/ExcelLADZ 💪
Hello, I'm using office 2016 where the XLOOKUP function is missing and I used the VLOOKUP function so unfortunately the teams are not displayed in the field after the formula. where could be the problem ?
Hi lad, the VLOOKUP function is slightly different to XLOOKUP so watch you don’t accidentally list the wrong ranges in the formula 👍 With a VLOOKUP in this video, it should be: =VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, FALSE) If you have a more specific issue with the formula (e.g. at one stage of the video) please let me know lad 🔥
@@excel_ladz The problem has been solved, but there is another problem I can't save the file with all the sheets. When I save the file, excel says that I have to save each sheet in the file separately. so I saved each sheet separately and when I open them again, the table with the formulas stops working actually all the formulas are missing. how the hell do I do that thanks for the advice.
Can you create the program with HT/FT and correct score please ??
Your Excel skills are top-notch. Tell me, how can I update the matches on the excel on a regularly basis without interfering with the data in the table?
Great 🤓video! Even learned a few new tricks on the way and have a new reference point of fast formula writing. And just love the way DATA is pronouced in your part of the world (I'm Danish). Absolutely going to check out your other content.
Great video and model. I'm trying to re-build it, but is having a small issue with the games drop down on the Dashboard. When I select the drop down I only get the first game for each date. The drop down says (e.g. Jan 13):
"Rækkemærkater" (Danish equal to Row Labels)
"Alcorcon vs Mirandes"
When I see the Games sheet everything looks fine. All games from coloumn A copied into column B.
Do you have any idea what the problem can be?
Hi lad, thanks for watching 🔥 My first suggestion would be to ensure that the copied timeline in 10:10 of the video updates the 'games' range when you change the date. You can just switch back and forth between the 'Dashboard' and 'Games' worksheet to doublecheck this 👍 My second suggestion would be that when you select the 'list' in Data Validation, make sure the boxes 'Ignore blank' and 'In-cell drop-down' are both selected. This can be seen at 10:40 of the video. If none of these work lad, it may be due to your version of Excel not supporting dynamic ranges or arrays. It's odd though that Column B of the 'Games' worksheet works yet your Dashboard worksheet dropdown list is not. Let me know if that helps lad 😃
Brilliant idea Mr. Lad! Video already saved in my favorites! :) Once I skimmed through that club ELO API but I didn't pay much attention to it. I'm glad you got this great idea. Thanks! Idea for you (if feasible): Dixon-Coles implementation of Poisson Distribution. Basically the two gentlemen added a parameter to the two independent Poisson Distributions of a matchup with the objective of creating a kind of dependence between the two Poissons and correct the low score probabilities (1-0, 0-0, 0-1). The challenge is calculating that parameter. I've seen it done in code (R or Python) but maybe it is not feasible in Excel... Cheers
I'm glad you liked the video lad! Your comment is very interesting. I have read about certain limitations of the Poisson distribution surrounding the low scores, so I'll definitely do some research surrounding the Dixon-Coles implementation. However, this will most likely take some time, so unfortunately it may not be a video in the short term.
Thank you so much, I won using your technique in data analysis which is the first time I ever won a football bet. Again thank you. I just have one question on how to update the data from the API given. I tried to copy paste but it isn't reflected on the pivot table even after refreshing it
I'm glad you won lad 💪 I would try and keep re-doing the process, trying to 'Refresh All' (found in the Data tab) as soon as the data is copy and pasted into the spreadsheet. Make sure you update the slicer 👍 I hope that helps lad, let me know if you have any other issues 🔥
@@excel_ladz It helped a lot thank you.
Please when will you upload the video for Over or Under Basketball total points Prediction?
Thank you for the excel tutorial. But did you have any success actually betting with these predictions? Can you share some experience? How many games did you need to make a more or less consistent net gain?
I think every 2 day repeat process again? for updated data?
Nice video for football since 2017❤
Pls recommend best market ie. over under, win the game and example compare market. Thank you
For sure lad, this is coming soon. Make sure you’re subscribed 💪
Hello Lad. Any chance you know anything about VBA code/macros and make downloading the data automatic? so we don't need to copy and paste the data every other day or whatever it is? This would be legendary.
G'day lad, thanks for watching! I usually use a power query on Excel to import data that updates automatically (so I don't need to copy and paste the data every day). An example of this is my NBA video where I import live stats to predict games. The reason I didn't do it for this particular video was because it was mainly geared at beginners who, without prior experience, would find obtaining the live date tedious and difficult. However, future videos on Football ELO Models will definitely feature live data lad 👍
@@excel_ladz cant wait for that!
Any chance you can do Major League Baseball with that season starting up soon?
Yes lad, that'll be a priority heading into March. 👍
Amazing as always. Kudos lad
any tips on how to make an elo rating system for NFL games, now that the season is about to start?
G’day lad, I would firstly find the most reliable site. There would be a million different ways to compile ELO Ratings, so use the most well established ELO website for the NFL. The formula to compile the win percentage based off of the ELO Ratings will differ with each system… While I don’t recommend you get the actual ratings from Wikipedia, it’s good for searching for mathematical formulas to transform the ratings into win probability. ELO Ratings only deal with win/loss scenarios. As a result, any expected goals calculations as well as draw probabilities cannot reliably be calculated. I know all this is probably common sense, but I hope it helps a bit lad 🤞
Hi lad, thanks for this impactful video, however I'm stuck where in the pivot table when I put in = games, it only shows one game not all of them and also on the dashboard my formulae bring errors. Kindly enlighten, what I'm I doing wrong
Hi lad, thanks for watching 🔥 You may not be doing anything wrong... Unfortunately later versions of Excel (e.g. Excel 2016 and later) don't recognise the XLOOKUP function. Is the #name error appearing on your dashboard? The solution for this would be to replace all the XLOOKUP functions in the formulas with a VLOOKUP function. I hope that helps lad 💪
I'm using excel 2016 can't get this to work. Does that sound right?
I know I don't have xlookup function
All you'll have to do is swap the XLOOKUP function with VLOOKUP 👍
Will try that tomorrow. I couldn't get the list of games to appear when you typed =games around 9 minutes into video. It only shows one match when I do it. Not a list
Good job... pls drop the NBA model with injuries and day's rest included 🙏
Of course lad, I’ll work hard so that the video can be uploaded within the next 7 days👍
hello there save as type which option ?? cause i save it but when i opened it's not saving right
Please Can you help me in the sheet dashboard the last calcul what does it mean ??
G'day lad, I'm just using an XLOOKUP function to pull in the data values for each scoreline. If the stats we imported say that a 0-0 draw has a 0.121 likelihood, then I'm just grabbing that and plugging it into the dashboard to make it easy to read and understand 👍
Thanks Lad, great video, as usual. Would be even better if we could update the data automatically. That would make a brilliant part two :)
Thanks lad, I appreciate the support. Usually I would definitely use a power query to import the data, which would update automatically in Excel. However, this video was more geared towards beginners, and at first the power query skill is a bit tedious and difficult to complete. Future Football ELO models will absolutely include a power query and automatically updated data lad, stay tuned 👍
What do you think about Google sheets
Hi lad, in my opinion Excel is much more powerful, however Google Sheets can provide Google users a very easy introduction to spreadsheets.
Have you/Can you do a backtest video to see the effectiveness of the model suggestions?
G’day lad, unfortunately with the way the data is pulled, there’s no concrete way to backtest the model’s suggestions. However, you could keep a running ‘success rate’ of the model and track its success. Of this was helpful lad, please subscribe!
Hello, great video but I can’t get this to work, when I copy it it doesn’t look like it does in the video, it turns up directly into excel and it won’t transform it to % either, I really don’t know what’s wrong here,
Hi lad, I’ll try and give you a couple broad suggestions in the hope it’ll fix it for you. Wit regards to the percentages, you have to convert them yourself by selecting all the relevant cells, and then clicking on the percentage icon in the home tab. Once all your data is in, go to the Data tab and select ‘Refresh All’. This will make sure your pivot table updates, and you can then use the slicer to file yet games by date. If you’re using an old version of Excel (one that doesn’t support XLOOKUP), you’ll have to write the formulas with VLOOKUP functions 👍 I hope that helps lad
Hi, I'm having hard time seeing the formula using wrote under game_ID and the other formula at the top. I would appreciate if you can write them out for me. Thanks.
game_ID looks like: =IF(ISTEXT(C2)=TRUE,C2&" vs "&D2,"")
Thanks
Hi this is great but can I get the formulas by re-writing it in the comment section
Great stuff as always
=games is not working as yours can you please advise me it only shows one game form the top of the list, and not the whole pivot table list
Hi, I have rebuilt it one to one but I don't know how to update it for today's games? Please answer 👋🏻
Greetings, Excel LADZ. I absolutely love these videos. Fantastic work!
I am trying to model a league of my own (purely as a hobby). May I ask if you offer one-to-one Excel tutorials on a commercial basis at all?
Thank you.
G’day lad, thanks for the support 🔥. You can reach out to me on Twitter. My handle is @excel_ladz
@@excel_ladz Hey, Lad. Thank you very much for getting back to me, I appreciate it.
@excel ladz can you create a video with posession model and poison distribution model combines?
Thanks for watching lad, I appreciate the support. With regards to your question, in the future I will be making a football video in which multiple parameters (e.g. possession, shots, passing, etc) are used in the model. And then from then, maybe rather than simulating the goals scored in the match, I can use the poisson distribution to solve for each team's chance of winning the match. I hope that helps lad.
@@excel_ladz That will be helpful waiting for the video
@@excel_ladz
is there a way you can add an output of correct scores, highest and the second highest values with there corresponding correct score outcome and percentage? for example, probable outcome 3-2 14%..
@@reportspayment Hi lad, I do this at stage 19:30 of the video. Instead of spilling out the highest probable scoreline though, I use conditional formatting to highlight it for me. This is shown in 20:30.
Could you create a rating model for soccer players? It would also be interesting to create a power ranking
Sorry if the English was not written correctly. I am Brazilian and still learning
Don't worry lad, your English is good. Perfectly understandable. As to your question, the idea of a rating model for individual soccer players was something I hadn't previously thought of. So that's a great idea I'll definitely look into.
In terms of power rankings, that's easy for me to create once I've done the ratings for players.
Thanks for the comment lad, great ideas for future videos!
Hello, I have been using this model. How can I update the new fixtures to automatically generate the dashboard predictions
G’day, thanks for watching! You can update the fixtures by deleting the old matches, and replacing them with a new copy and paste from the Club ELO Ratings Website (like we did at the start of the video). From there, use text to columns to clean up the data (again, exactly like the video). Then, click the Refresh All button to refresh all worksheets, and the model should update automatically 🔥
Hello. Is it possible for you to publish this excel you have made here? Thanks.
Hi lad, in the near future I’m considering setting up a website/page where all of my Excel models will be available for purchase. Would you be interested in this lad?
@@excel_ladz Yes sir.
As long as the price is right and there is success, of course I buy.
Thanks for the video, Im little slow so it took me ages to finally make this, but now im wondering, when we get new upcoming games, how do i add them to this existing model? I tested with adding the same games again to the rawdata and made new pivottable with the new ones included but the new ones i added wont show there? do you know what i mean, I would appreciate it if you can help me.
Mr. Lad! That's amazing video! You help us a lot with your teachings.
I'd like to ask about API. Do you know where can I find any API similar to ClubElo to put more championship? (for example: J League, MLS, Australia, Brazil). Thank you!
Hi lad, thanks for the comment 🔥 As of right now, unfortunately I don't know a reliable website where you can find ELO ratings for leagues outside of Europe 😢 I'll be sure to post it in the community tab of the channel when I find one of similar quality to the ClubElo ratings 👍
Great Vid. Really helpful...thankyou!!
Hi Lad Great stuff, how could we add a what if scenario based analysis ?
Or lad how can we get this model to suggest an over/unders game?
Hi lad, I’m really glad you enjoyed the video. The what-if analysis function is not really relevant in this video as the win percentages are already calculated for each team.
When I use what-if analysis, it’s for a Monte Carlo simulation where I can simulate scenarios on a large scale. Then from there, the probability of each outcome makes itself apparent, e.g. Arsenal beats Man United 3450 out of every 10000 simulations, therefore Arsenal have a 34.50% chance of winning. I use this method when I do not already know the probability distribution for a game.
I hope this helps lad.
Hello Lad; great video...easy to follow...by the by got brand new computer (wife) alas the NBA model STILL didn 't work? unbelievable!!!
That's a shame. If I remember correctly, you were having a problem with the Pts Sim? I've got two new methods for you to try and solve your problem lad.
Firstly, can I confirm you are using an English version of Excel? There was one subscriber who's issue with the formula was immediately fixed, because he was using the French version of Office, and so the BINOM.INV formula was written differently.
Secondly, try breaking up the formula into parts. To simulate the 2 pointers, enter in: 2*BINOM.INV(BINOM.INV(F12,G12,RAND()),H12,RAND()) If this simulation works and changes every time you update the cells, then move onto the 3 pointers and Free Throws until the formula for Pts Sim. is complete.
I hope this helps you lad, let me know how it goes.
@@excel_ladz yes US English
Morning: =if (d13>0,iferror(2*binom.inv(binom.inv(f13,g13,Rand()),h13,Rand()) is this correct...my data is on row 13
@@bobnicholson6350 Try entering this: =2*BINOM.INV(BINOM.INV(F13,G13,RAND()),H13,RAND()) for Row 13. This should spill out the simulated points for only 2-point shots for that player in Row 13.
@@excel_ladz Tried that but got #num! error...pasted numbers in different cells as Values and got same result.#NUM!...What to do??!!
Please what 😢 short cut code did you use to load the home win away win draw % at once without stress myself please help 😮
When i do the formula to lookup to return the home team keeps saying formula is wrong any chance typing it here please
Thanks for watching lad, make sure you're subscribed 👍 .
In this video I use the XLOOKUP function to grab the home team. However, only Excel 2021 and the Microsoft 365 versions of Excel have access to this function. If you are using an older model of Excel, you must use VLOOKUP instead of XLOOKUP (it's a relatively easy switch between the two).
I hope that helps lad 💪. If not, make sure you comment again and I'll get back to you as soon as possible.
How do I add other leagues. I got 15 following your video but they were all Spanish. Thanks
G’day lad, the website has data for all Major Leagues including the EPL, Serie A, Ligue 1, Bundesliga, La Liga and many more competitions. I would try the model on another day, such as before the next EPL competition weekend, to see those games come up.
If this helped lad, make sure you subscribe for more 🔥 Follow me on Twitter @excel_ladz 💪
u will be doing a video on automating the sheets soon so for now do I have to do the whole process over again for the new fixtures?
G’day lad, because this is a very simple model I haven’t included the process of a power query. If you’re able to, a power query would update the stats automatically for you. 👍
@@excel_ladz Got it right, now updating takes about a minute using power query....Thanks a lot Lad
@@bonezbooth1845can you send me yours sheet please 🙏
Hi
Which version of excel are you using?
There is a problem with the formulas
=SI(Games!$D$11=0,"",Games!$A$4:DECALER(Games!$A$4(Games!$A$4,Games!$D$11-1,0))
error : not enough arguments to this function
Hello Lad. I just had a suggestion, there are some markets for betting over and under goals. How can i I do it using this elo rating method. It seems to work I just don't know how to go about it.
There could also be a filter to generate games that have goals over or under a certain number
Hi Excel Ladz can't get the Data Id formula to work can you post it?
=IF(AND(ISBLANK(C2)=FALSE, ISBLANK(D2)=FALSE),C2&" vs "&D2,"")
Hi lad, very sorry for the late response. This is the formula I have inputted for cell AT2 in the 'Raw Data' worksheet under the 'Game_ID' header:
=IF(ISTEXT(C2)=TRUE,C2&" vs "&D2,"")
Try this lad, and let me know how it goes 👍 .
i got something weird when importing data, the number is too big for probability. it's over 100%, is that normal ?
Hi lad, yeah that shouldn’t be the case. The sum of all probabilities for a match’s scoreline should be 100%, so there’s probably an error somewhere in your formulas (if the imported data is correct). Let me know if you have anymore specific questions lad 👍
Hi i`m trying to add column B which is league name to GAME ID but i tried and failed, can you please
G'day lad, sorry for the late reply. First of all, make sure your Game_ID formula at 3:21 of the video is correct. Then, at stage 8:15, go into Name Manager in the Formulas tab, and make sure you exactly type in the same formula I do under the name 'games'. If you still have further troubles, try to be as specific as you can when commenting so I can help you fix it lad!
Hi
Which version of excel are you using?
There is a problem with the formulas
=IF(ISTEXT(C2)=TRUE, C2&" vs "&D2,"'")
=IF(SUM(L2:Q2)=0,"",SUM(L2:Q2))
G’day lad, I’m using Excel with a Microsoft 365 subscription (the latest version). The ISTEXT function is available on all Excel versions dating back until 2000, so I assume your version can support it 🔥 Just make sure that your ranges are correct lad, and make note of the specific error your formula is returning (e.g. a #NAME? error) which could help you solve your problem 💪
THANK YOU just done it
Hey! Any chance you can do NHL playoffs? Love the videos 👌🏻
Thank you for watching lad 🔥 I’ll try and release a video predicting this season’s playoffs using a basic model. If unfortunately I’m unable to release this due to it being too late to publish, I’ll definitely release a video walking my subscribers through how to predict NHL games/seasons/playoffs using Excel in the short term 💪
Bu verilerin ilk yarılarının
Verisi varmı sitede varsa. Ht ft analizi için süper olmazmı
Thanks for watching lad, but unfortunately the website I've imported the date from does not provide first half / second half data for us to calculate. This is an extremely simple model, a more detailed football model which I'm planning to upload int he short term will go though half analysis.
That’s wicked. Well done. I’m not sure if I missed it, but can this be made so it’s automated by dragging in the latest stats?
Thanks for watching lad 🔥. To update the mode for the upcoming games, simply copy and paste in the new raw stats from the website (the text to columns part only takes a couple of seconds). Then, go into the Data tab and hit Refresh All or Refresh Data. If you do these two very quick steps (takes under 30 seconds), then the model will update for that new day 💪
If this helped lad, make sure you’re subscribed 👍
@@excel_ladz oh I am subscribed lol 😂
@@excel_ladzm having issues.. I can’t workout a few things. For some reason I keep getting 0 for team names on the dashboard. And I have 0% on the heat map.
I’m now at a point where I’m stuck.
Can you just add a link under the videos to where we can just download the sheets?
Cheers, and I must say; this is the first ever RUclipsr I’ve seen where you don’t need to even ask for subscribers.
When I try to save the worksheet, my laptop says some data maybe lost if I save as a CSV file. What shall I do? When I updated the sheet, it seems to only put data in the left hand side of the sheet in A1 only. And back to original format.
I think it would be easier if you have a link to download the sheet from. Sorry if I’m being an absolute T@@T.
@@seychelles44 Don't apologise lad! It's my job to answer questions from loyal subscribers 👍
1. If you're getting a 0 for the Team Names in the Dashboard then there's a couple things that could be wrong. Firstly, check that you have access to the XLOOKUP function. Only recent versions of Excel have it. If you don't, change the function to VLOOKUP and adjust accordingly. Secondly, you may simply have just entered the XLOOKUP formula wrong. However, if the XLOOKUP formula (12:00) is correct, that means your Team ID's column has not been formatted like mine. This means the XLOOKUP is searching for the wrong range to return.
2. If you're getting 0% for the heat map, then it's more than likely it's the same issue here. Check if you have XLOOKUP, and then check if your data is formatted EXACTLY like mine in the video.
3. I'd save this file as a regular Excel worksheet. When you update the sheet, do what I do at 1:25 and go to the Text to Columns Wizard.
I hope this all helps lad 💪When I become monetised on RUclips, I'm looking to start a membership where I can build up this Excel LADZ community some more. Through this, the link to all Excel LADZ models will be available 👍 Thanks again for being a subscriber 🔥
@@excel_ladz nice one. I’ll check it out and see how it goes in a few days when I get more time. I’ll also look into other ways to find a system that works well with this. Cheers
This is good - is the data on Club Elo actual data rather than expected? One thing I’ve noticed is that the win % it gives can be very different to Poisson for example
Hi lad, I myself have found that the Poisson method is much more accurate than the data this model imports. The ELO data is based on scorelines, and gives no bearing to who was playing in matches and the importance of a match to each club... This means, a Poisson model, factoring player strength in each match, is a much more predicative method 👍 Very soon lad I'll be releasing a video on how to build this model on Excel 💪 Thank you for watching lad!
@@excel_ladz yes agreed - it would be good if you could alter the future model to exploit the other markets which aren’t as liquid and are more exploitable, like win to nil, for example. The 1x2 markets are notoriously tough to beat as the bookies have these models dialled in. I personally have been having more success with bet builders and goalscorer markets. The goalscorer market you can reverse engineer the market odds to give some success.
THIS LAG FOR ME I GO TO TEXT TO COLUMS AND NOTHING
prima tips excel thank you
I wonder how the API in clubelo calculate the probabilities
Most computer models use what's called the 'Monte Carlo Method' to calculate the probabilities. If you have any further questions lad, just ask me about it 👍
@@excel_ladz But not sure what model clubelo is using. Maybe Poisson distribution? But not sure how to get an expected mean solely based on Elo ratings.
Do you know how the potential results are calculated on ELO score? I am using predictions based on xG and the poisson distribution function
Hi lad, I haven't yet combined ELO Ratings with the Poisson Distribution. With Poisson, I normally go a different route and calculate Attack and Defence ratings for each club and then for each matchup calculate xG. An example of this is in the first ever video I made on this channel.
Given two teams, A and B, with Elo ratings Elo_A and Elo_B, the probability of team A to win the match is: 1 / (1 + 10^ ((Elo_B - Elo_A)/400) ).
For Team B, just swap Elo_A and Elo_B in the equation.
Elo rating doesn't account for draws. Also I don't know how club Elo is calculating the goal difference using Elo ratings.
I am using poisson distribution to predict where I generate the xG and than use this in the formular
@@3ecke11er I didn't get your method, can you elaborate? You calculate the goal expectation probability of each team using Poisson, and then you multiply the two Poissons to get the probability of match results (0-0, 1-0, ...), right? At least this is the most common way of using Poisson.
@@-Tharos- This is correct
Is it working on 2013 Excel?
Hi lad, it should still work in Excel 2013. All you have to do is replace every XLOOKUP formula to a VLOOKUP. This is because unfortunately, XLOOKUP is only available in 365 Microsoft subscriptions as well as Excel 2021 and later.
you didn't mention when you insert new data that everything needs to be refreshed to accept the new data!
That’s absolutely correct lad 🔥 I’ll make sure to update the video’s description in case some subscribers can’t work it out 👍
My Name manager is making empty cells 0 instead of blank. can any tell me why?
You need to correct spelling error or check if XLOOKUP addon is in your excel version
How i can have this program?
Hi lad, make sure you subscribe because in the short term I'll set up a website/page in which these models can be purchased for a small fee.
I can't put the numbers in percentage
Hi lad, if you already have the decimal values then it just involves changing the format from General to Percentage in the Home tab. However, if you're unable to get any values, double check your XLOOKUP formula; if you don't have access to XLOOKUP use a VLOOKUP formula 🔥Thanks for watching lad!
Some nice data.
Appreciate it lad. Great data makes life worth living.
ben aynısını yaptım ve (2,5 alt 2,5 üst) karşılıklı gol var yok gibi seenekleride kledim
Thank you👍
very interesting video
I cannot see the formula
=games not working on my PC too
The data from the source was not that accurate since most of the predictions were wrong , with team statistics why don't you get your own winning,draw, losing chances
G’day lad, I 100% agree with you. There are many flaws with this website’s calculations. Have a look at my NBA video where I walk through how to build a model, completely from scratch, on how to predict basketball games using player stats.
Thanks for watching the vid lad, make sure you’re subscribe for more 🔥 Follow Excel LADZ on Twitter to be able to DM me about any sports modelling questions (@excel_ladz) 💪.
I have an issue thanks
Hi lad, I just answered your question on the NFL Touchdowns video. Do you have any more specific questions?
I'll.look sorry lad cheers
Still no joy mate I've took a screenshot cab I send it to u
Esta tabla para sudamerica
Hi lad, unfortunately this website doesn't provide stats for South American football leagues 😔 However, very soon I'll publish a video on 'How to Predict Football Games using the Poisson Distribution' that will be a much more accurate system of predicting matches than this video. Furthermore, you will be able to apply this to South American leagues 👍 Thank you for being a loyal subscriber lad 💪
NHL?
Definitely before the season starts 🔥
@@excel_ladz what league do you really recommend: NHL? Baseball, NBA, Soccer ⚽,? And which markets (MoneyLine, Spread, Totals)?
Aynısını yaptım
Has anyone won money using this system?
Hi lad, not that I know of yet mate. This is a very simple model, make sure you're subscribed for more accurate models I'll be releasing very soon 💪.
G'day lads, if you want access to this model (and every other Excel LADZ model), join the Excel LADZ community! Sign up here: www.patreon.com/ExcelLADZ 💪
You should run a Blackjack simulation in Excel
@@williamjacksquilliam Sounds like an idea... Will take a lot of planning though, so not a video in the short term unfortunately.
Please when will you upload the video for Over or Under Basketball total points Prediction?
@@boldbuzz I'm glad you're eager lad, this will be published very soon in the coming weeks 💪 💪.
=IF(ISTEXT(C2)=TRUE, C2&" vs "&D2,"') THERE IS A PROBLEM WITH THIS FORMULA
this is not working =IF(Games!$D$11=0,"",Games!$A$4:OFFSET(Games!$A$4,Games!$D$11-1,0)
Hello, I'm using office 2016 where the XLOOKUP function is missing and I used the VLOOKUP function so unfortunately the teams are not displayed in the field after the formula. where could be the problem ?
Hi lad, the VLOOKUP function is slightly different to XLOOKUP so watch you don’t accidentally list the wrong ranges in the formula 👍
With a VLOOKUP in this video, it should be:
=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, FALSE)
If you have a more specific issue with the formula (e.g. at one stage of the video) please let me know lad 🔥
@@excel_ladz The problem has been solved, but there is another problem I can't save the file with all the sheets. When I save the file, excel says that I have to save each sheet in the file separately. so I saved each sheet separately and when I open them again, the table with the formulas stops working actually all the formulas are missing. how the hell do I do that thanks for the advice.
@@excel_ladz having the same problem,can you type the formula using vlookup?
@@excel_ladz Hi Unfortunately I'm using excel 2019 and don' have Xlookup, can you provide a workaround for the lookup at 12:20 stage Thanks