Great video tutorial. I just wished near the end where you added Away Rating, Home Rating, and Diff to the Master Schedule, that you stress that the purpose of those VLookups is to extract the "Round 2 Adjusted Winning Percentage" values from the Team Averages Sheet. It actually took me an extra 10 min to figure out that the "Power Ratings" is actually equal to the Round 2 Win % located in column 9 of Sheet 2. Since the topic of the video is "Power Ratings", it is good to emphasize that it is quantified by this column. Of course, if you do many iterations of adjustments, the Power Rating is equal to the final adjusted Win %.
Kudos for the video content! Sorry for butting in, I would love your thoughts. Have you thought about - Seyli Nansabella Bulldozer (Sure I saw it on Google)? It is a smashing exclusive guide for getting a soccer betting tipsters secrets without the headache. Ive heard some awesome things about it and my work buddy at very last got amazing results with it.
Wish I could actually understand it 😵🥴 seems super useful and full of info. I have 0 experience with....macros...excel... Do you have a video you recommend watching ( macros /excel " for dummies") something to start. Thanks
I am really having issues trying to get the multiple rounds to normalize. Actually they're drastically going more negative with each subsequent round. I've ensured my code matches yours exactly. What could be wrong?
How about producing user-friendly software to sell to newbies like me who want to use ELO for college basketball. I think there would be a market for that. Rick
Where is the best place to get Excel basics to even begin knowing what you're doing? Also, which sports betting books do you recommend? Thanks for the content and I hope we can get back to the grind soon!
In your examples of predicting using the linear regression model at the end of the video, I'm slightly unclear on who you're specifying as home and away team. For example, (at 40:00) you get your regression intercept and coefficient and then make a prediction of Packers vs Bears. In that prediction you plug in (PackersAdjWin% - BearsAdjWin%) as 'x' in your regression equation, and then get the Bears % chance of winning that matchup. I just want to clarify you are saying that plugging (AwayAdjWin%-HomeAdjWin%) into this particular regression equation gives you the expected Home Team Win% for that matchup (the Bears?). Thanks!
Seems go be decent information, but the books already take this into consideration when making the lines... A teams win / loss margin is deceptive as they could blow one team out and win others by 1 point, not to mention the game they were up a ton then sand bagged rest of game with back ups.
Yes, but I highly recommend still doing this - it helps tremendously just by having your own point of reference to compare to the books and not having a bias when viewing the lines. In the NFL the lines are very sharp (mostly) so yeah it's really hard to find value there, but in softer markets, just by modeling, it can be a huge advantage. Sooner or later, you will have to create your own power rankings to be long term successful, there is no guarantee for winning, however, there is a guarantee for losing if you don't do it.
Awesome video. I am having problem whereby my wins and losses are off. I wrote the vba code exactly as you compiled in the video, but after it loops through, the wins and losses are all wrong (I verified with the pro-football-reference standings). I rechecked the code again and again, and cannot find any errors in code. I compiled the standings via power pivot, and formatted just like your data. Any help on this issue would be great. It would be hard to move forward without correcting the input wins/losses. Thanks
Going back to this video and of course, really loving the content. Like some, I ran more than the two adjustments (have run 5 now) and the 49ers round 5 win % is now at 104.72% where it started at 78.9%. Seems like this is from too many adjustments...the question is, what are you looking at to determine how many adjustments are enough? My round 1 average adjustments were -0.23%, where now round 5 is -3.15%.
I am getting the error of having all the team names be separate so the cardinals show up in team average tab 15+ times with only either a win or loss, but not adding these up. I watched it and checked my code twice but I cant figure out why it wont consolidate them.
I had this exact same problem. I figured out that the actual "Raw_Calculate" code was working it just wasn't recognizing that it needs to re-count the teams in the "Team_Lookup" (function) code at at the bottom. I figured out I had accidentally typed in "team_peram" in the first line of the function not "team_param". I don't know if that's what yours did too but in the "Team_Lookup" code on the bottom is causing the error because the actual Raw Calculate code is working. I know you commented on this two years ago but I just watched this video last week and had the exact same problem and you're the only one who asked. It took me hours this week to figure this out. So, even if you figured this out a long time ago hopefully this helps somebody else watching this video in the future.
I tried to replicate this in football, what you guys call Soccer. In argentine's first league, away win percentage is greater than home win percentage. So, when I make the simulation, home team goes 35% and ELO 74.43%. Away team goes 64.69% and ELO 25.57%. What am I doing wrong? I followed every step
Great video but I'm struggling to understand the first round win % adjustments; can anyone explain why we are considering the average of the opposing teams win % differential to the league average when we adjust for home/away? Seems to be unnecessary and unrelated to home/away advantages? Cheers!
I have a debug - it says object required on following line - end_line = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row Hope you can help me :) Thanks in advance
I followed the code up to 17:15 but when I run it, the teams show up as an unique row for each result rather than 32 rows with each team's total wins and losses.
I'm following this exactly but using college basketball's 2021 season. I get adj win % over 100% after just the second round of adjustments. Response below for this suggests this happens when you've done too many rounds, but I'm getting this after just 2 rounds? For example, Kansas goes from raw 85% win % to 106. The increase makes sense since they have a tough schedule, but it should not go above 100% Something's not right.
Can anyone help? I followed along as best I could but on the adjustment I’m still getting a “Sub or Function not defined error” on my “team_lookup” even though it all looks to line up. Anyone know how to fix this?
I'm getting a compile error with the 2nd to last "Next start_line" in Adjustment Round 1, the error says, "Next without For" my code is identical to yours, I've checked it several different times, any suggestions on what to do?
Really enjoy the videos but I’m kind of new to this, why does my computer give me the error that “the macro may not be available in this workbook or all macros may be disabled”
Carefully watched and followed along but have applied to soccer (Italian Serie A) and it doesn't take draws in consideration so results have little sense. Have tried to come up with some coding but can't think of anything logical (I'm beginner level). Could you help somehow 🙏?
@@williamleiss4 We haven't tried all the way through the video to see if it messes with the other formulas, but calculating Win % using ties is relatively simple. Winning Percentage = (2 × Wins + Ties) / (2 × Total Games Played) × 100
My W% adjustments become larger with each round, which does not seem correct. If i adjust several times, i start to get negative win %s and some that are over 100%. Any thoughts on what i am missing?
I know I am late to the party but am hoping for a reply. How would you go about calculating these things live during a season. For instance after 6 weeks of college football how can I get it to calculate from the amount of games played instead of through the entire season?
Not quite sure how it could be done outside of VBA - I am guessing you could do a bunch of columns that only insert a number if the team on the schedule shows up. The adjustment is based off the mean of the opponent's combined win percentage minus the league average(.500)
@@williamleiss4 I think part of the problem is that I can't replicate your win % from the raw results data as I'm not sure how you have handled ties. I think what you are saying is that it's the sum total of all the win % of the teams played relative to the 50% expectation? I can get close but not spot on and it's the OCD in me that wants to get to your figures so I know I understand the concept. Do you still have the files to upload as this would be an immense help? Thanks for all the great vids dude!
@@stuarthutchinson2477 It seems the ties were [inadvertently] added into the L column totals due to the way the score logic is written out. If home_score > away_score then W to home else L to away. And vice-versa with away_score > home_score. We made this adjustment while converting to Python. But ended up with another issue with total number of games. The provided (and real) 2019 schedule has 267 total games. In the videos, it shows only a total of 261. We addressed this in the Part 2 of the series and asked what the discrepancy was. So even if you were to get the ties thing figured out, the numbers still won't match 100%. Which is a bummer... unless we can figure out where those extra 6 games came from.
New to this. Any idea why I’m getting an “Object required” error when running the Macro? Has gone through video twice and can’t find what I’m messing up. Thanks in advance!
My only concern about these models is that they are obsolete for the beginning of the season right? I only bet on soccer, I'm trying to build my own model, do you recommend any content for me to learn more about it? I have a model already built in that I'd like to discuss with someone more experienced there is any way to contact you or join with you for 10 min on a discord? Thank you, Jorge
Use the previous season(s) with his model to calculate a preseason power rating for the current/upcoming season. This is exactly what we recently had to do for high school football since only 6 years ago the state converted from a 4A system to a 6A system. This, in our opinion, threw out any previous 4A power ratings a team would have had. Back to the drawing board so to speak. Also, 538 uses a formula to regress end of season ELOs to the mean to calculate preseason ratings. They keep 2/3rds of the end of season ELOs and add 1/3rd of the average (mean) team. In his percentage system (vs the number system), that average team is 50% (vs 1505). So say a team in William's system ends the season at 60%. 2/3rd of that is 40%. And 1/3rd of 50% would be 17%. So the preseason rating would be 57%. This is done to somewhat account for changes in roster via trades and things like that. Disclaimer: We used quick math here to calculate the % rollback. So it may be incorrect. But you get the idea, hopefully. The 538 system is described quite well on their website. But this model by William makes it much easier to start everyone out with the same power rating, then use the adjustment rounds to somewhat simulate the lack of previous years' data. That in itself helps to determine more accurate preseason power ratings than starting off everybody at 50% (1500). Using the typical ELO formula found in the 1500 system isn't conducive to doing this without creating a very convoluted and confusing solver. Once we get a final win percentage in William's system, it's extremely easy to plug that number into the traditional ELO system formula to calculate a numbers-based rating. The added bonus with William's is that it gives us SOS. Another not-so-easy thing to do using the traditional ELO formula.
Hi, When I put my data into excel it did not have my teams lined up like yours did you manually go through and rearrange the teams so all the away teams are on one side and home on opposite?
I used an excel formula to arrange the schedule from pro football reference, since they have the winner on the left and loser on the right, so if there is an @ symbol in there it means the away team won.
Can I apply this to an EPL Soccer season, or will I get half way through the tutorials and realise its so NFL specific that I have to create a time machine to go back to regain the lost man hours :>) ?
I am new to all of this coding, and I copied the first round of coding (where we got the records of each team) exactly, and I still get returned with errors. Any idea on how to fix this?
@@williamleiss4 To be sure, I copied and pasted the code that you linked in the next video, and am getting returned with an “object required” error still
I'm new to sports betting if possible can give me a math formulas like wins/loses = win percentage it would help me out greatly I'm not good with spreadsheet.
Wether your into sports stats or not, this is by far the best introductory lecture on rating systems. Invaluable resource
I followed your steps but with NBA it was just as much fun as betting. Thank you! I'm looking forward to more challenging ones
Great video tutorial. I just wished near the end where you added Away Rating, Home Rating, and Diff to the Master Schedule, that you stress that the purpose of those VLookups is to extract the "Round 2 Adjusted Winning Percentage" values from the Team Averages Sheet. It actually took me an extra 10 min to figure out that the "Power Ratings" is actually equal to the Round 2 Win % located in column 9 of Sheet 2. Since the topic of the video is "Power Ratings", it is good to emphasize that it is quantified by this column. Of course, if you do many iterations of adjustments, the Power Rating is equal to the final adjusted Win %.
Hey mate, awesome video! Is there any chance you could please share the template for the excel spreadsheet you're using?
As a complete newbie to macros, I had to thumbs up this video because of just how much I don't understand it! Hahahaaa!
Awesome video! Can't wait for the next, I really like what you are doing!
Kudos for the video content! Sorry for butting in, I would love your thoughts. Have you thought about - Seyli Nansabella Bulldozer (Sure I saw it on Google)? It is a smashing exclusive guide for getting a soccer betting tipsters secrets without the headache. Ive heard some awesome things about it and my work buddy at very last got amazing results with it.
Wish I could actually understand it 😵🥴 seems super useful and full of info.
I have 0 experience with....macros...excel... Do you have a video you recommend watching ( macros /excel " for dummies") something to start. Thanks
Great video! Using your setup for NHL!
Great video! Looking forward to the next.
I am really having issues trying to get the multiple rounds to normalize. Actually they're drastically going more negative with each subsequent round. I've ensured my code matches yours exactly. What could be wrong?
How about producing user-friendly software to sell to newbies like me who want to use ELO for college basketball. I think there would be a market for that. Rick
Where is the best place to get Excel basics to even begin knowing what you're doing? Also, which sports betting books do you recommend? Thanks for the content and I hope we can get back to the grind soon!
There are good excel tutorial videos on TH-cam. I use 5dimes to bet with online
I want to make one as an experiment because I want to see if there is a pattern on the GTA 5 horses, lol. Just bet on the highest elo score
In your examples of predicting using the linear regression model at the end of the video, I'm slightly unclear on who you're specifying as home and away team. For example, (at 40:00) you get your regression intercept and coefficient and then make a prediction of Packers vs Bears. In that prediction you plug in (PackersAdjWin% - BearsAdjWin%) as 'x' in your regression equation, and then get the Bears % chance of winning that matchup. I just want to clarify you are saying that plugging (AwayAdjWin%-HomeAdjWin%) into this particular regression equation gives you the expected Home Team Win% for that matchup (the Bears?). Thanks!
Correct
Seems go be decent information, but the books already take this into consideration when making the lines... A teams win / loss margin is deceptive as they could blow one team out and win others by 1 point, not to mention the game they were up a ton then sand bagged rest of game with back ups.
Yes, but I highly recommend still doing this - it helps tremendously just by having your own point of reference to compare to the books and not having a bias when viewing the lines. In the NFL the lines are very sharp (mostly) so yeah it's really hard to find value there, but in softer markets, just by modeling, it can be a huge advantage.
Sooner or later, you will have to create your own power rankings to be long term successful, there is no guarantee for winning, however, there is a guarantee for losing if you don't do it.
You know how many times i said F4 during this video LOL
No snap or clap? Aside from that great video!
Awesome video. I am having problem whereby my wins and losses are off. I wrote the vba code exactly as you compiled in the video, but after it loops through, the wins and losses are all wrong (I verified with the pro-football-reference standings). I rechecked the code again and again, and cannot find any errors in code. I compiled the standings via power pivot, and formatted just like your data. Any help on this issue would be great. It would be hard to move forward without correcting the input wins/losses. Thanks
Please put a link to where I can get this table
How would you incorporate Ties into this model? I tried by using an IF statement however for some reason doing this messes up my loss totals.
Going back to this video and of course, really loving the content. Like some, I ran more than the two adjustments (have run 5 now) and the 49ers round 5 win % is now at 104.72% where it started at 78.9%. Seems like this is from too many adjustments...the question is, what are you looking at to determine how many adjustments are enough? My round 1 average adjustments were -0.23%, where now round 5 is -3.15%.
Hate to bump this but getting into modeling and I keep getting an overflow code. What do I do?!
I am getting the error of having all the team names be separate so the cardinals show up in team average tab 15+ times with only either a win or loss, but not adding these up. I watched it and checked my code twice but I cant figure out why it wont consolidate them.
I'm having the exact same problem. Did you even figure it out?
I had this exact same problem. I figured out that the actual "Raw_Calculate" code was working it just wasn't recognizing that it needs to re-count the teams in the "Team_Lookup" (function) code at at the bottom. I figured out I had accidentally typed in "team_peram" in the first line of the function not "team_param". I don't know if that's what yours did too but in the "Team_Lookup" code on the bottom is causing the error because the actual Raw Calculate code is working.
I know you commented on this two years ago but I just watched this video last week and had the exact same problem and you're the only one who asked. It took me hours this week to figure this out. So, even if you figured this out a long time ago hopefully this helps somebody else watching this video in the future.
I tried to replicate this in football, what you guys call Soccer. In argentine's first league, away win percentage is greater than home win percentage. So, when I make the simulation, home team goes 35% and ELO 74.43%. Away team goes 64.69% and ELO 25.57%. What am I doing wrong? I followed every step
Great video but I'm struggling to understand the first round win % adjustments; can anyone explain why we are considering the average of the opposing teams win % differential to the league average when we adjust for home/away? Seems to be unnecessary and unrelated to home/away advantages? Cheers!
Realised its to adjust for vsing the 'average'' team. Thanks again.
I have a debug - it says object required on following line
- end_line = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Hope you can help me :) Thanks in advance
If youre still searching for the answer its "L" not 1 I made the same mistake
I followed the code up to 17:15 but when I run it, the teams show up as an unique row for each result rather than 32 rows with each team's total wins and losses.
I keep getting the sub or function not defined error on the adjustment round 1 macro and can’t figure out why. It looks identical to yours
Enjoyed it, thank you.
I'm following this exactly but using college basketball's 2021 season. I get adj win % over 100% after just the second round of adjustments. Response below for this suggests this happens when you've done too many rounds, but I'm getting this after just 2 rounds? For example, Kansas goes from raw 85% win % to 106. The increase makes sense since they have a tough schedule, but it should not go above 100% Something's not right.
Adjustment1 take raw league averages but Adjustment 2 take adjustment1 league averages , correct ?
Can anyone help? I followed along as best I could but on the adjustment I’m still getting a “Sub or Function not defined error” on my “team_lookup” even though it all looks to line up. Anyone know how to fix this?
Were you ever able to fix this? I am having the same issue.
He has END rather than END IF. Took me an hour to trap.
So, I did more research on Elo ratings and am watching this video for the 2nd time. You *DID* have a snap in the beginning. My bad.
I'm getting a compile error with the 2nd to last "Next start_line" in Adjustment Round 1, the error says, "Next without For" my code is identical to yours, I've checked it several different times, any suggestions on what to do?
I got it to work! My error of course
@@sambahou6253 Bit late but how did you solve it? I have the same error
Ah man I appreciate what you're doing here but like... do you have a link to the file somewhere?
i know this video is 3years old but no one seemed to notice the typo at 22:06 where you added hom_adv instead of home_adv
doesn't visual basic have the idiom x += 1 so you don't have to type out x = x + 1 where x is a long ass thing?
can you link the file for the macro to copy and paste?
Ok but if it’s not sports related how do you count it for more than 2 teams
Really enjoy the videos but I’m kind of new to this, why does my computer give me the error that “the macro may not be available in this workbook or all macros may be disabled”
Is there any way I could do this in Google Sheets?
No, unfortunately
I CLEARLY need to go to school to learn some excel stuff. Lost me when he started typing all that jazz on the right side. 😳
Carefully watched and followed along but have applied to soccer (Italian Serie A) and it doesn't take draws in consideration so results have little sense. Have tried to come up with some coding but can't think of anything logical (I'm beginner level). Could you help somehow 🙏?
You can do ELO with ties, you can search google for it. A bit too complex for a TH-cam comment
@@williamleiss4 We haven't tried all the way through the video to see if it messes with the other formulas, but calculating Win % using ties is relatively simple.
Winning Percentage = (2 × Wins + Ties) / (2 × Total Games Played) × 100
My W% adjustments become larger with each round, which does not seem correct. If i adjust several times, i start to get negative win %s and some that are over 100%.
Any thoughts on what i am missing?
From what I can recall this is the natural result of adjusting too many times, I think
@@williamleiss4 In video you say that with each round numbers should stabilize
I know I am late to the party but am hoping for a reply. How would you go about calculating these things live during a season. For instance after 6 weeks of college football how can I get it to calculate from the amount of games played instead of through the entire season?
Can you explain the logic behind the Round 1 Adj. column? I'm trying to replicate this using formulae rather than VBA and can't work it out. Thanks.
Not quite sure how it could be done outside of VBA - I am guessing you could do a bunch of columns that only insert a number if the team on the schedule shows up. The adjustment is based off the mean of the opponent's combined win percentage minus the league average(.500)
@@williamleiss4 I think part of the problem is that I can't replicate your win % from the raw results data as I'm not sure how you have handled ties. I think what you are saying is that it's the sum total of all the win % of the teams played relative to the 50% expectation? I can get close but not spot on and it's the OCD in me that wants to get to your figures so I know I understand the concept. Do you still have the files to upload as this would be an immense help? Thanks for all the great vids dude!
@@stuarthutchinson2477 It seems the ties were [inadvertently] added into the L column totals due to the way the score logic is written out. If home_score > away_score then W to home else L to away. And vice-versa with away_score > home_score.
We made this adjustment while converting to Python. But ended up with another issue with total number of games. The provided (and real) 2019 schedule has 267 total games. In the videos, it shows only a total of 261. We addressed this in the Part 2 of the series and asked what the discrepancy was. So even if you were to get the ties thing figured out, the numbers still won't match 100%. Which is a bummer... unless we can figure out where those extra 6 games came from.
New to this. Any idea why I’m getting an “Object required” error when running the Macro? Has gone through video twice and can’t find what I’m messing up. Thanks in advance!
Same problem here. Did you fix it?
For me it was misstyped Sheets instead of Sheet
Hey, any idea as to why all of my RD 1 adjustment %'s are coming out as 50%?
Thank you!
how to download the Excel file?
My only concern about these models is that they are obsolete for the beginning of the season right?
I only bet on soccer, I'm trying to build my own model, do you recommend any content for me to learn more about it? I have a model already built in that I'd like to discuss with someone more experienced there is any way to contact you or join with you for 10 min on a discord?
Thank you, Jorge
That's why you have to find a way to generate preseason ratings to fill in the gaps until enough games have been played.
@@williamleiss4 Do you bet on Soccer? OR is it possible to talk with you for 10 minutes in voice?
@@jorgesa4244 No soccer
Use the previous season(s) with his model to calculate a preseason power rating for the current/upcoming season. This is exactly what we recently had to do for high school football since only 6 years ago the state converted from a 4A system to a 6A system. This, in our opinion, threw out any previous 4A power ratings a team would have had. Back to the drawing board so to speak.
Also, 538 uses a formula to regress end of season ELOs to the mean to calculate preseason ratings. They keep 2/3rds of the end of season ELOs and add 1/3rd of the average (mean) team. In his percentage system (vs the number system), that average team is 50% (vs 1505). So say a team in William's system ends the season at 60%. 2/3rd of that is 40%. And 1/3rd of 50% would be 17%. So the preseason rating would be 57%. This is done to somewhat account for changes in roster via trades and things like that. Disclaimer: We used quick math here to calculate the % rollback. So it may be incorrect. But you get the idea, hopefully.
The 538 system is described quite well on their website. But this model by William makes it much easier to start everyone out with the same power rating, then use the adjustment rounds to somewhat simulate the lack of previous years' data. That in itself helps to determine more accurate preseason power ratings than starting off everybody at 50% (1500). Using the typical ELO formula found in the 1500 system isn't conducive to doing this without creating a very convoluted and confusing solver.
Once we get a final win percentage in William's system, it's extremely easy to plug that number into the traditional ELO system formula to calculate a numbers-based rating. The added bonus with William's is that it gives us SOS. Another not-so-easy thing to do using the traditional ELO formula.
Hi, When I put my data into excel it did not have my teams lined up like yours did you manually go through and rearrange the teams so all the away teams are on one side and home on opposite?
I used an excel formula to arrange the schedule from pro football reference, since they have the winner on the left and loser on the right, so if there is an @ symbol in there it means the away team won.
Ok does it matter to have them like yours? Or as long as I have the codes and games I’ll be ok
@@christopherchambers1622 If you are copying my code directly it would need to be structured like mine
Ok thanks for such quick replies and great content. I have learned a lot in the past week since watching your page.
Can I apply this to an EPL Soccer season, or will I get half way through the tutorials and realise its so NFL specific that I have to create a time machine to go back to regain the lost man hours :>) ?
Wanna know the same
Is there a possibility I could have the code file I’m having some issues with getting the code to work
It might be in the video description of subsequent videos in this series, otherwise I no longer have it
I am new to all of this coding, and I copied the first round of coding (where we got the records of each team) exactly, and I still get returned with errors. Any idea on how to fix this?
If you do it exactly like it is in the video there will be no errors.
@@williamleiss4 To be sure, I copied and pasted the code that you linked in the next video, and am getting returned with an “object required” error still
@@mileskee6120 Object required means whatever data you are trying to pass to a variable or function is blank.
what if the match gets drawn?
I'm new to sports betting if possible can give me a math formulas like wins/loses = win percentage it would help me out greatly I'm not good with spreadsheet.