Action Backers hey! I want to do a good power ranking for my local Smash Ultimate community. I still don’t know if this video will help me but I’m giving it a try!
For those struggling with the “the PSI interpreter was not able to parse the middle correctly” error. This is solved by pasting all values and not calling to another sheet through a formula. I was calling scores through formulas in a sheet called 'Scores.' Instead, I had to copy my data and paste values only so that it stays statically. Best of luck enjoying this!
can you explain what this means in a little more detail please. I am running into this issue and am new to sheets so any help would be greatly appreciated!
Not sure if this channel still operates. But used this methodology for WNBA and its worked well. Excited to see the different from this season. Freakin Aces were so good no matter the height of my ratings value they were always a few points higher. Hope dude whos made all this, is doin well.
Fantastic video! How are you pulling the historical home/away results for NFL if they are not outlined as such on pro football reference (as they are for NHL)
Hi. When taking the mean of arbitrary rankings I am left with 0.5. This is based on 20 teams. Is there any way to get around this, or is it OK to proceed with the rest of the calculations?
I’m following this guide step by step and building my own NBA model. When I get to the part where I press solve and solver does it’s magic, it says “the PSI interpreter was not able to parse the middle correctly” I don’t know where I’m going wrong. Is there any way we could make contact to go over this?
@@TruthOperator81 Try removing the formulas in your initial power ratings. I had formulas initially and got the same error. Can see how that would confuse a solver. After removing the formulas it was fine.
To be clear, initially i set the power ratings arbitrarily from 15 to -15 simply using above cell -1, as per the video. This is the one part of the vid i would clarify! If these formulas still remain when you run the solver, there is no way the solver will come to a sensible solution. copy the initially ratings and re-paste them as values (or set the initial ratings arbitrarily). I believe the formulas remaining in the cells is probably causing the issue.
An excellent video, made so much sense and easy to follow. I have used it for English Football and can see how it can aid prediction. I guess another step would be to use the solver to workout the ideal Home threshold and Away Threshold. My early attempts have not worked as I am not familiar enough with solver. I will work it out. Excellent tutorial.
I was following along with the video, but ran into an issue. Solver add-on no longer works. Any recommendation for a different add-on? Or a manual formula to enter in? I'm very new to building these sheets. Thanks!
Great Video!!! I use it for my online NHL95 league. I did have some issues with running the solver after the first time. Keeps popping up "Stop...reached maximum time". Not sure what to do.
Great Video!! So I noticed in the video, you had games that hadn't happen but you had forecast, home margin and squared error filled out?.. did I miss something?
Any recommendations why the solver would not be working now. I had created Power Rating system last spring, worked fine. Copied what I had created for new season, now it will not work on either google sheet. Any tips appreciated.
Awesome video! I have no experience with google sheets and you talked me through it perfectly. Thank you. I am having one problem though... When I am adding new scores, and solving for the forecast, the data from all the previous forecasts are changing which is skewing the betting results. Is there a way to lock in the forecasts so that new data doesn't change it?
hello there , great video! i am trying to set this up but now there are 32 teams in the league and the mean i am getting is .5 what do i do about that?
I made this model based on the current season as it stands, and my power rankings dont seem right. it has TB as #24. but they are clearly a top5 team. i made the model step for step to the video. what could i have possibly done wrong? any help would be great
hey really good info i was running this with nfl scores everything was going fine untill i tried to update the scores today and run the solver and got this message "Solver Add-in Invalid index into the collection. Invalid column name inside a list object." any ideas? i tried going back to an earlier version and keep getting the same thing now reallly furstrating
Hey, Great video and Ive followed you step by step but unfortunately when I tried to download the solver add on, Google blocked it. I have changed the security on my account but I still cant add it. Do you have any recommendations for next steps???
Hey Action Backers, really enjoyed the video. One of the most helpful videos I have come across! I just had one question, towards the end of the video, you were explaining how you incorporate power ratings into your model. You said that you take the "Home Advantage" and add it to your xG calculation. How did you calculate the Home advantage for each team? Thanks, and keep up the great content!
I don't incorporate it in newer models, as it is essentially being added in twice (due to a new way I have built my model), but the "home advantage" is what the video is showing you how to calculate. The power ratings are derived from that number.
Does anybody know how to solve the "PSI interpreter problem", I've tried everything, yet I'm still not able to find a solution. It would mean a lot to me if I could find one.
When i paste my scores from pro football reference the scores and teams get an * next to them causing issues when trying to preform functions how do i passte with out the * ?
Anybody get the error "The Objective Cell values do not converge" when running solver? I triple checked it and can't figure out what the issue is. The rankings are very larger numbers
Most power ratings will start with previous season's stats and go from there. To be more accurate, waiting until week 4 (or if you're impatient even after week 1) like you mention would definitely help. The problem is football has such a short season, relative to other leagues so it's very challenging to get enough meaningful data. Great question!
Action Backers I’ve only been looking at building a model over the last day or two, it’s very confusing, I’ve seen people build models using key stats like turnover differential, yards per play, ect and then converting those stats to a line and betting off that. you still have to wait for a few weeks to get the stats to work with but do you think that sort model of model is best for the nfl or a model like you use here?
So I build all of my models using stats, and teach all of my members to do the same (and if you want to learn, check out my site- I teach you to build models without the confusion), but when building a model, you need to have a way of comparing apples to apples, and that is where power ratings come in: Team strength vs the league. The video was just showing a very simple way to get to a power rating, but in order to feel confident actually betting, you would want to build something more robust like you've outlined above, but that factors in strength of each team relative to the league. This was just one small example of that.
It's also worth noting that I do not bet on NFL football at all. It's far too efficient a market and the return isn't worth it, so I focus on other sports, personally. Not to say you can't profit, just saying I personally put my efforts elsewhere
Make your own home and away columns next to the data table and then use a few simple if statements. For example let’s say winners is col A @ is col B(only present if col A team is also road team) and Loser is col C. Then you make two adjacent columns, D Away E Home, for instance. Then in column D you would say if col B = “@“, return team listed in col A, else return team listed in col C. Then for Home teams you would just say if col D = col A, return col C, else return col A. It’s just thinking logically about the information presented to you and manipulating it to suit your purpose
I have followed your steps and I have question regarding square error sum, it should be count just for already played matches or also for whole season (fixtured matches). For me i doesn't make sense to involve not played games. Thanks for your answer.
Im having a problem trying to pull the YTD scores for MLB. Baseball reference doesn't have a table like NBA,NHL, and NFL. Amy suggestions to get the YTD scores for MLB?
You might need to look into building (or having somebody build) a web scraper. Or look for a different website. I don’t have one off the top of my head because I don’t actually use this method when building my models. You could look at fangraphs but they are more game stats than boxscores. Sorry I don’t have a better solution for you. It’s so odd that the sport-reference sites are formatted so differently
hockey reference has the entire season data on one page to pull in, but NBA and NCAA bball do not. Can you combine multiple tables into your YTD sheet?
Yep- so I just copy the month by month in a separate tab and then copy it into the main tab: a minor annoyance but it only took me ~10 minutes to copy all nba months. Haven’t done it for NCAA but I’d imagine it would be similar
Make sure that you’re not trying to run it against any formulas- if you’ve accidentally pasted in a formula that is copying another name or number from a different cell, for instance, you will sometimes get that error. This happened to me when I was trying to pull in team names and scores automatically, rather than copy and pasting from a secondary tab
Hi! I had success with predicting soccer in home leagues for example england. But my predictions are way off in competition like champions league because you have teams from all around Europe. And sometimes you have very strong team against a weak team. but that weak team has better stats than strong team because he is best in his league. So whatever I try too much chances are given to weaker team. How would you predict a match for example between manchester city and sparta praha? I'm talking about probabilities..
You would have to normalize the stats somehow. If they don't play each other often or the leagues are severely mis-matched, this is hard to do, especially for a one- off match. A crude way would be to take the averages of both leagues and create a new average from that. From there you could work out a poisson distribution, but with so few matches against one another, it would likely not be terribly accurate.
That would be a bit different. Would likely have to look at something closer to an ELO rating, similar to chess. Fight sports are challenging because fighters often don't have a lot of data against each other.
I don't have a pc so if possible can you give me a formula like a mathematics system home edge + home ranking - visitors edge + visitors ranking it would be easier for me to understand.
I have a separate spreadsheet where I track all of my bets- date, game, bet type, odds, outcome, net profit. It takes about 10 minutes a day or less depending on number of bets, but then you have it easily available to compare
@@igor6815 its easy to me, because if you breakdown the time into separate scoring columns as you research the game (mainly the numbers), while considering 50 percent of each team's projected performances & the ohter 50 perc. may come from anything (system/consideration-wise) you want=% to win the game/% to score a certain amount of points off.&/or def.), The end! Ps. This is just one of my systems out of thousands that I use for nhl (note: this system concept can/may be used in different-kind of sports as well) Good luck!
NHL is my favourite sport to bet on too, but Zauvijek is correct- of the major North American team- based sports, it does involve the most "luck" - this is based on a bunch of studies done. But this also means it's easier to profit from an underdog winning, as that happens more often (due to the "luck factor") compared to other sports. The fact that there aren't spreads to balance the outcome is also helpful.
Question man. I copied yours, but I get from the solver this...The PSI Interpreter was not able to parse the model correctly. Unexpected end of formula [Computed_Value] at cell _____. I put blank because no matter how many lines there are out picks the visitor cell of the last line. Can't find any help on Google. Thanks for any if you have it.
Nvm. I was trying to have a query in the solver area so it would always pull new data. Once I copy and pasted values only it worked. Guess it couldn't read it because it was a query. Love your content man. Thanks
Im struggling with the formula for "Betting Only Visiting Team" - both wins and losses. Would anyone be able to drop the formula in the comments? forever thankful in advance
I was following along with the video, but ran into an issue. Solver add-on no longer works. Any recommendation for a different add-on? Or a manual formula to enter in? I'm very new to building these sheets. Thanks!
What league or sport do you want to build power ratings for?
Action Backers hey! I want to do a good power ranking for my local Smash Ultimate community. I still don’t know if this video will help me but I’m giving it a try!
Boxing 🥊
Volleyball
College Football and/or Basketball
English Premier League would be interesting.
For those struggling with the “the PSI interpreter was not able to parse the middle correctly” error. This is solved by pasting all values and not calling to another sheet through a formula. I was calling scores through formulas in a sheet called 'Scores.' Instead, I had to copy my data and paste values only so that it stays statically. Best of luck enjoying this!
thank you so much, you saved me a lot of troubles
can you explain what this means in a little more detail please. I am running into this issue and am new to sheets so any help would be greatly appreciated!
Thanks a lot
Could you explain your comment more in depth? I am not sure of what you exactly mean? It would hugely help me.
Not sure if this channel still operates. But used this methodology for WNBA and its worked well. Excited to see the different from this season. Freakin Aces were so good no matter the height of my ratings value they were always a few points higher. Hope dude whos made all this, is doin well.
What solver do you use? When I try to use the solver he used Google blocks me from installing it due to security reasons
Doing this for NBA, thanks a bunch man I got 2 models working for the NBA now.
Fantastic video! How are you pulling the historical home/away results for NFL if they are not outlined as such on pro football reference (as they are for NHL)
Hi. When taking the mean of arbitrary rankings I am left with 0.5. This is based on 20 teams. Is there any way to get around this, or is it OK to proceed with the rest of the calculations?
You would just need to adjust the input for the Solver extensions, otherwise all good.
I’m following this guide step by step and building my own NBA model. When I get to the part where I press solve and solver does it’s magic, it says “the PSI interpreter was not able to parse the middle correctly” I don’t know where I’m going wrong. Is there any way we could make contact to go over this?
Same :/
@@TruthOperator81 Try removing the formulas in your initial power ratings. I had formulas initially and got the same error. Can see how that would confuse a solver. After removing the formulas it was fine.
@@cusematt23 ha i can’t even remember which power rankings sheet this was but i figured it out thank u sir
To be clear, initially i set the power ratings arbitrarily from 15 to -15 simply using above cell -1, as per the video. This is the one part of the vid i would clarify! If these formulas still remain when you run the solver, there is no way the solver will come to a sensible solution. copy the initially ratings and re-paste them as values (or set the initial ratings arbitrarily). I believe the formulas remaining in the cells is probably causing the issue.
Late to comment but yes, I should have clarified that more in the video and thank you for posting a solution!
An excellent video, made so much sense and easy to follow. I have used it for English Football and can see how it can aid prediction. I guess another step would be to use the solver to workout the ideal Home threshold and Away Threshold. My early attempts have not worked as I am not familiar enough with solver. I will work it out.
Excellent tutorial.
Glad it was helpful!
I was following along with the video, but ran into an issue. Solver add-on no longer works. Any recommendation for a different add-on? Or a manual formula to enter in? I'm very new to building these sheets. Thanks!
Great Video!!! I use it for my online NHL95 league. I did have some issues with running the solver after the first time. Keeps popping up "Stop...reached maximum time". Not sure what to do.
A Good idea for the next video is to teach us how we can use the forecast with functions to post them on another excel like for "today bets" thing
Brilliant video. I am going to build an NFL. I hope the formulas and such are the same for that. Outstanding.
THANKS FOR THE VIDEO, HOW DO I INTERGRATE 2019-2020 TO 2020-2021?
HOW DO I UPDATE YTD SCORES AUTOMATICALY?
Great Video!! So I noticed in the video, you had games that hadn't happen but you had forecast, home margin and squared error filled out?.. did I miss something?
What do you do if the list is of winners and losers and not home and away teams? I am having this problem with cfb.
Hey Action Backers - I'm interested in applying this to English premier league - do I need to add anything to factor for the game ending in a draw?
Very interesting video and very well explained! Thanks from Sweden.
Any recommendations why the solver would not be working now. I had created Power Rating system last spring, worked fine. Copied what I had created for new season, now it will not work on either google sheet. Any tips appreciated.
Awesome video! I have no experience with google sheets and you talked me through it perfectly. Thank you. I am having one problem though... When I am adding new scores, and solving for the forecast, the data from all the previous forecasts are changing which is skewing the betting results. Is there a way to lock in the forecasts so that new data doesn't change it?
hello there , great video! i am trying to set this up but now there are 32 teams in the league and the mean i am getting is .5 what do i do about that?
Awesome video and great explanations
Very good video! But i had a problem: When I ran solver it says “Solver found a solution…” But nothing changed
What are you ranking your teams prior to the solver with 32 teams? Middle team .05 instead of zero?
did you find any answer to this?
great job thanks for the tips
Would you recommend adding stats models for better accuracy or the past score models are good enough to be consistently profitable?
I made this model based on the current season as it stands, and my power rankings dont seem right. it has TB as #24. but they are clearly a top5 team. i made the model step for step to the video. what could i have possibly done wrong? any help would be great
Very informative!
hey really good info i was running this with nfl scores everything was going fine untill i tried to update the scores today and run the solver and got this message "Solver Add-in
Invalid index into the collection. Invalid column name inside a list object." any ideas? i tried going back to an earlier version and keep getting the same thing now reallly furstrating
Hey, Great video and Ive followed you step by step but unfortunately when I tried to download the solver add on, Google blocked it. I have changed the security on my account but I still cant add it. Do you have any recommendations for next steps???
How would i go about incorporating this into an already made monte carlo model?
My solver worked the first time, but after updating scores it's saying it a solution cannot be found.
With the addition of the Seattle Kraken, would this still work even though the average is -0.5? Or should I keep the average 0?
Thank you so much for this video!!!
I'm using excel 2019. There is no unique function available, what's the alternative I could do.
There’s definitely a unique function available in excel 2019...
Hey Action Backers, really enjoyed the video. One of the most helpful videos I have come across!
I just had one question, towards the end of the video, you were explaining how you incorporate power ratings into your model. You said that you take the "Home Advantage" and add it to your xG calculation. How did you calculate the Home advantage for each team?
Thanks, and keep up the great content!
I don't incorporate it in newer models, as it is essentially being added in twice (due to a new way I have built my model), but the "home advantage" is what the video is showing you how to calculate. The power ratings are derived from that number.
Does anybody know how to solve the "PSI interpreter problem", I've tried everything, yet I'm still not able to find a solution. It would mean a lot to me if I could find one.
Hi. Great video on creating power ratings using excel. Are there any other categories besides final score that can be used in the same manner? Thanks
Margin of victory can be factored.
When i paste my scores from pro football reference the scores and teams get an * next to them causing issues when trying to preform functions how do i passte with out the * ?
this was super helpful
I cannot get solver to download, anyone know any alternates?
I subbed this guys intresting
Often when running this, I get the error "Problem could not be loaded, no model defined." Any advice?
Baseball doesnt have tables, how do you imports the baseball scores?
same question
Anybody get the error "The Objective Cell values do not converge" when running solver? I triple checked it and can't figure out what the issue is. The rankings are very larger numbers
So if you use a model like this for next nfl season would you have to wait until around week 4 so you can build up stats to get data off?
Most power ratings will start with previous season's stats and go from there. To be more accurate, waiting until week 4 (or if you're impatient even after week 1) like you mention would definitely help. The problem is football has such a short season, relative to other leagues so it's very challenging to get enough meaningful data. Great question!
Action Backers I’ve only been looking at building a model over the last day or two, it’s very confusing, I’ve seen people build models using key stats like turnover differential, yards per play, ect and then converting those stats to a line and betting off that. you still have to wait for a few weeks to get the stats to work with but do you think that sort model of model is best for the nfl or a model like you use here?
So I build all of my models using stats, and teach all of my members to do the same (and if you want to learn, check out my site- I teach you to build models without the confusion), but when building a model, you need to have a way of comparing apples to apples, and that is where power ratings come in: Team strength vs the league. The video was just showing a very simple way to get to a power rating, but in order to feel confident actually betting, you would want to build something more robust like you've outlined above, but that factors in strength of each team relative to the league. This was just one small example of that.
It's also worth noting that I do not bet on NFL football at all. It's far too efficient a market and the return isn't worth it, so I focus on other sports, personally. Not to say you can't profit, just saying I personally put my efforts elsewhere
Action Backers I’ll check out your site, thanks for the help brother
My numbers for this year football are coming out really low. I rarely have a forecasted number over 5. Is that common?
Do power ratings work for Single player sports like Tennis?
NFL doesn't have the schedule sorted by Home/Away but Winners and Losers. When you sort it with @ it messes up all the dates. Any suggestions?
Make your own home and away columns next to the data table and then use a few simple if statements. For example let’s say winners is col A @ is col B(only present if col A team is also road team) and Loser is col C. Then you make two adjacent columns, D Away E Home, for instance. Then in column D you would say if col B = “@“, return team listed in col A, else return team listed in col C.
Then for Home teams you would just say if col D = col A, return col C, else return col A. It’s just thinking logically about the information presented to you and manipulating it to suit your purpose
Ive followed step by step but when i get to the forecast part its giving me a N/A; cant find soultion
I have followed your steps and I have question regarding square error sum, it should be count just for already played matches or also for whole season (fixtured matches). For me i doesn't make sense to involve not played games. Thanks for your answer.
Thanks for sharing and your shout out to Clear Data Sports as being the starting point. I plan to binge watch your series, applying some of your tips.
Who the hell hit dislike? Nice job man!!!
Thank you! Much appreciated !
Im having a problem trying to pull the YTD scores for MLB. Baseball reference doesn't have a table like NBA,NHL, and NFL. Amy suggestions to get the YTD scores for MLB?
You might need to look into building (or having somebody build) a web scraper. Or look for a different website. I don’t have one off the top of my head because I don’t actually use this method when building my models. You could look at fangraphs but they are more game stats than boxscores. Sorry I don’t have a better solution for you. It’s so odd that the sport-reference sites are formatted so differently
hockey reference has the entire season data on one page to pull in, but NBA and NCAA bball do not. Can you combine multiple tables into your YTD sheet?
Yep- so I just copy the month by month in a separate tab and then copy it into the main tab: a minor annoyance but it only took me ~10 minutes to copy all nba months. Haven’t done it for NCAA but I’d imagine it would be similar
@@ActionBackers thanks! That was what I ended up doing and it worked.
Awesome- glad to hear it worked for you!
Great vid.
Thanks!
Have you ever received "the psi interpreter was not able to parse the model correctly" when running the model? Any suggestions?
Make sure that you’re not trying to run it against any formulas- if you’ve accidentally pasted in a formula that is copying another name or number from a different cell, for instance, you will sometimes get that error. This happened to me when I was trying to pull in team names and scores automatically, rather than copy and pasting from a secondary tab
Also on any cells with numbers in them, highlight them and make sure in your formatting it is set to “number” and not “automatic”
@@ActionBackers Thanks. It works
The forecast formula keeps giving me a value of 0.00. I've already triple and quadruple checked my formula. Any idea's why that would be?
NVM I figured it out
Hi! I had success with predicting soccer in home leagues for example england. But my predictions are way off in competition like champions league because you have teams from all around Europe. And sometimes you have very strong team against a weak team. but that weak team has better stats than strong team because he is best in his league. So whatever I try too much chances are given to weaker team. How would you predict a match for example between manchester city and sparta praha? I'm talking about probabilities..
You would have to normalize the stats somehow. If they don't play each other often or the leagues are severely mis-matched, this is hard to do, especially for a one- off match. A crude way would be to take the averages of both leagues and create a new average from that. From there you could work out a poisson distribution, but with so few matches against one another, it would likely not be terribly accurate.
how would you make one for boxing?
That would be a bit different. Would likely have to look at something closer to an ELO rating, similar to chess. Fight sports are challenging because fighters often don't have a lot of data against each other.
@@ActionBackers okay thanks:)
No problem- I might make a video on this in the future!
does the team ranking need to match the league table?
What do you mean? Sorry, not quite following
of course not, that's why you build your own model :)
Great video but still really confusing. I'll have to watch a few times
Fcking AWESOME...thank you!!!
So doing my NHL rankings...how would you parse this truncated year with the previous full season? I did this year all be hand
I don't have a pc so if possible can you give me a formula like a mathematics system home edge + home ranking - visitors edge + visitors ranking it would be easier for me to understand.
How do you recommend tracking the average Vegas odds if just using this to bet
I have a separate spreadsheet where I track all of my bets- date, game, bet type, odds, outcome, net profit. It takes about 10 minutes a day or less depending on number of bets, but then you have it easily available to compare
Or you can use an app like the action network if you don’t want to manually track for yourself
Awesome
I love to wager on NHL too, mainly because it's the most predictable sport!
Hockey is most random sport because it's involves a lot of luck while tennis is a sport that involves most skill and least luck.
@@igor6815 its easy to me, because if you breakdown the time into separate scoring columns as you research the game (mainly the numbers), while considering 50 percent of each team's projected performances & the ohter 50 perc. may come from anything (system/consideration-wise) you want=% to win the game/% to score a certain amount of points off.&/or def.), The end! Ps. This is just one of my systems out of thousands that I use for nhl (note: this system concept can/may be used in different-kind of sports as well) Good luck!
NHL is my favourite sport to bet on too, but Zauvijek is correct- of the major North American team- based sports, it does involve the most "luck" - this is based on a bunch of studies done. But this also means it's easier to profit from an underdog winning, as that happens more often (due to the "luck factor") compared to other sports. The fact that there aren't spreads to balance the outcome is also helpful.
Question man. I copied yours, but I get from the solver this...The PSI Interpreter was not able to parse the model correctly. Unexpected end of formula [Computed_Value] at cell _____. I put blank because no matter how many lines there are out picks the visitor cell of the last line. Can't find any help on Google. Thanks for any if you have it.
Nvm. I was trying to have a query in the solver area so it would always pull new data. Once I copy and pasted values only it worked. Guess it couldn't read it because it was a query. Love your content man. Thanks
Im struggling with the formula for "Betting Only Visiting Team" - both wins and losses. Would anyone be able to drop the formula in the comments? forever thankful in advance
I was following along with the video, but ran into an issue. Solver add-on no longer works. Any recommendation for a different add-on? Or a manual formula to enter in? I'm very new to building these sheets. Thanks!