Is there a way to calculate the runs per inning that are simulated? I’d like to be able to see what % each each has for whether a run was scored or not
Doing research currently but do you know if it's possible to automate ESPN daily stats updates into an excel file? I go through every morning and out of the 50 or so batters, update their last 7 hit/hr stats, august hit/hr stats, if they got a hit last game and total games they got a hit out of games played that month. About a tedious hour thing everyday just seeing if you had any insight, thanks!
Definitely lad 👍I love play-by-play models, as they are grounded in real life. In my opinion (and I'm sure yours too), the more realistic the model, the better it is. It'll take some time before I release a play-by-play MLB model onto Excel LADZ, but I hope to be able to do this by the end of the 2023 MLB Season (in time for the finals) 💪
Hi lad, I've gotten these values using a Power Query on Excel. Basically, a Power Query allows me to import live data from the internet. In this MLB Excel Model, I've pulled live data from the Baseball Reference and TeamRankings websites (both links in the description). Then from this Power Query data, I've just copied the list of teams. Alternatively, you could just manually write the teams down 👍
@@excel_ladz what is the alphabetical order for though what purpose does it serve and how do i do it like can i just follow yours for order and type the number in or do i have to use a special function
Yes, absolutely lad 🔥 I would just copy and paste that into Excel and use a weighted percentage between 23 and 24 data. For example, 100/0 after 0 games in 24, 50/50 after 20 and 0/100 after 40 games. 👍
Thanks for watching lad 🔥In the Team Rankings website, select the 'MLB' header. Then, on the left sidebar, open up 'Stats'. Then select 'Team Stats'. Within 'Team Stats', open up the row 'Other'. Within 'Other', you'll find the link to "Extra Innings Runs Per Game" and "Opponent Extra Innings Runs Per Game". You can use these stats to find the R/Innings and RA/Innings. See if that helps lad, let me know if it works for you 👍
@@excel_ladzI’m wondering how the “extra inning runs” from team rankings will help us to find the r/inning and the ra/inning I hope you answer and can help me out
Can somebody explain why the formula for C doesn’t work on google sheets? I copied it exactly but I’m getting an error message that says #Value!- “A matching value could not be found. To get the values for the entire range use the arrayformula function” any feedback would be appreciated 👍🏽
G'day lad, I could perhaps release a video on this before the EPL season starts 🔥In saying that, a basic Football Corner Kicks model would be quite easy to build. It would just involve treating corners for like 'Goals For' and corners against like 'Goals Against', and then using the Poisson method to distribute each team's Expected Corners. Then, you could come up with accurate Corners Over/Under figures 👍
Can't seem to get the R/9 and RA/9 columns to populate anything other than an #N/A error at the moment - copied your setup on the data imports and formulas to the letter...
So ive managed to add pitching matchups and imported pitching data but i run into this problem where some pitchers have a 3rd name and therefore an extra column in their data and it completely breaks the model, as in the lookup returns no data for those guys and then the whole data table gets messed up. Anyone come up with a solution for this? Ive tried adding wildcards to the vlookup logic but nothing seems to work. Im running out of ideas. Maybe build an if/then into the lookup? Idk, if anyone has a fix for this please advise.
Good video. But estimating the probability in this way will not be accurate, since the starting lineup of the teams must be taken into account. The whole secret lies in estimating the expected runs for teams as accurately as possible.
hello great video but I have a question about how I can replace SEQUENCE(20) because in Excel2010 this formula does not exist. And as you use it to calculate the "probality distribution" I'm stuck on this part.thanks for advance
Hi lad, there’s only a slight change needed to be done to the spreadsheet 🔥 To replace the SEQUENCE Function, just type out the numbers 0-20 👍 If you also don’t have access to XLOOKUP, then the corresponding probability for 0 goals would have to start at 0%, then the 1 goal probability would be the previously 0 goal probability… This is because VLOOKUP return the lowest value instead of the highest one which XLOOKUP does. I hope that helps a bit lad 💪
@@excel_ladz the VLOOKUP returns the 1st value in the table, of course if it is sorted in ascending order it will start with 0 but if it is sorted in descending order it will be the highest value 😉
@@tomkmb4120 yeah it is it took me awhile to find them until i realized they are in this comment section i dont understand the alphabetical numbers though
When I'm trying to load more than the standard batting table from the powerquery - it's just saying they're 'loading' perpetually on the web view. They never show up in the excel Navigator box, only the team standard batting table does - is this the site trying to limit scraping?
had the same error, if you scroll down to the bottom of baseball reference website, you will see a link to pitching and fielding url. click that and use that to import stats instead
Also having an issue with the formula to grab the information from the teamrankings tables, because they're using diff naming conventions - I know he's tried to get around that with the sequence(30) but for some reason it just returns all 0's for me...all data types are correct in the imported tables. This is what I get - i.imgur.com/B56NML9.png My setup is identical to his in terms of cell placements etc.
G’day lad, SEQUENCE may not work on your version of Excel - it’s an array function. It doesn’t matter though, all you need are the numbers listed - doesn’t matter how you get them 😃
@@excel_ladz Cheers for the response - yeah look at my setup though, sequence should work fine as I've got the numbers there but for some reason it just returns all 0's - I've ensure the cells are formatted as numbers i.imgur.com/YB5Egwk.png i.imgur.com/gTikIkH.png
this is a great video. i don't have excel but i am building along in google sheets. when i go to enter the equation to solve for C, it returns "Error A matching value could not be found. To get the values for the entire range use the ARRAYFORMULA function." Anyone know how to troubleshoot this? also that long eqaution for C sequence (20)... part of this video is kinda confusing and i'd be curious to learn how that all works rather than just copy your work. might make for an interesting video, but cool video and i followed you.
@@LettyMMA yea but i don't remember how lol. i think i ended up using a poisson distribution formula i found online instead of the equation in the video. might not be as accurate but it seems to work out fairly well. i dont remember exactly how i did it but i think if found answers by watching youtube videos on "poisson distribution probabilities with google sheets." if you have any questions i might be able to help but its been a few weeks and i really dont remeber.
because its not gonna be very accurate when pitcher data isnt incorporated, theres gonna be a big swing in runs scored vs someone like corbin burnes vs someones 5th starter, so its taking into account season long stats but for single game bets its not gonna show that variance
G’day lads, download the Excel LADZ MLB Model: www.patreon.com/ExcelLADZ
Great video, thanks for sharing! Love to see more sports from you. 🎉
Been waiting for this!
Great work, as always!
Any way I could incorporate pitcher stats such as era into this to make it more accurate? Love the videos!!
Great vid, would love to see a NRL model!
I'd love to make that lad, hopefully before the finals! 🔥
👌Very interesting. As always!
Great Video Man! Any Plans in the future of releasing a NFL or UFC model?
Definitely NFL, but still need to do a bit more research for the UFC Model 👍
EXCELlent
I simp for excel ladz
Bro same 😩
Is there a way to calculate the runs per inning that are simulated?
I’d like to be able to see what % each each has for whether a run was scored or not
Doing research currently but do you know if it's possible to automate ESPN daily stats updates into an excel file? I go through every morning and out of the 50 or so batters, update their last 7 hit/hr stats, august hit/hr stats, if they got a hit last game and total games they got a hit out of games played that month. About a tedious hour thing everyday just seeing if you had any insight, thanks!
Where do you find the source for the two tables of data at the 1:00 mark?
Any plans for making play by play simulation for mlb?
Definitely lad 👍I love play-by-play models, as they are grounded in real life. In my opinion (and I'm sure yours too), the more realistic the model, the better it is. It'll take some time before I release a play-by-play MLB model onto Excel LADZ, but I hope to be able to do this by the end of the 2023 MLB Season (in time for the finals) 💪
Thanks for this new tool, is not clear for me on Alphabetical data column, where come from these?
Hi lad, I've gotten these values using a Power Query on Excel. Basically, a Power Query allows me to import live data from the internet. In this MLB Excel Model, I've pulled live data from the Baseball Reference and TeamRankings websites (both links in the description). Then from this Power Query data, I've just copied the list of teams. Alternatively, you could just manually write the teams down 👍
@@excel_ladz what is the alphabetical order for though what purpose does it serve and how do i do it like can i just follow yours for order and type the number in or do i have to use a special function
Amazing - are you going to have a live excel sheet going for the 2024 season? And if so, can you please share link how to join.
Thanks & great content
G'day lad, I suggest you become an Official Excel LAD here: www.patreon.com/ExcelLADZ
🔥
could I add 2023 data for r/g and ra/g to the model with the present 2024 data to make it more accurate?
Yes, absolutely lad 🔥 I would just copy and paste that into Excel and use a weighted percentage between 23 and 24 data. For example, 100/0 after 0 games in 24, 50/50 after 20 and 0/100 after 40 games. 👍
Do you have one for the NFL?
Not right now lad, but it’ll definitely be released before the season starts in September 💪
It's good tutorial but where can I find the values R/Innings and RA/Innings I don't see those numbers in those tables?
Thanks for watching lad 🔥In the Team Rankings website, select the 'MLB' header. Then, on the left sidebar, open up 'Stats'. Then select 'Team Stats'. Within 'Team Stats', open up the row 'Other'. Within 'Other', you'll find the link to "Extra Innings Runs Per Game" and "Opponent Extra Innings Runs Per Game". You can use these stats to find the R/Innings and RA/Innings. See if that helps lad, let me know if it works for you 👍
@@excel_ladz Thanks for the quick response have found it with your instructions ;)
@@davidjobe9876Hey were you able to figure out the formula for Runs/Per Inning and Runs/Allowed per inning??
@@excel_ladzI’m wondering how the “extra inning runs” from team rankings will help us to find the r/inning and the ra/inning I hope you answer and can help me out
Would really love to see the formula you put in to ensure it will be live and refreshable correct numbers
Much appreciated on the video. Is there any way you can adjust for pitching matchups?
That'll be the next update/video 🔥 Thanks for watching lad!
@@excel_ladz Thanks! Looking forward to it. Cheers
would you be able to explain the formula for r/innings? i can't seem to get it. Thanks in advance! This Part is throwing off the whole model!
Numerous people have asked about the runs /inning stats. He's hoping people will give up trying and buy it from his Patreon.
Can somebody explain why the formula for C doesn’t work on google sheets? I copied it exactly but I’m getting an error message that says #Value!- “A matching value could not be found. To get the values for the entire range use the arrayformula function” any feedback would be appreciated 👍🏽
Can you build for football corner kicks🥺
G'day lad, I could perhaps release a video on this before the EPL season starts 🔥In saying that, a basic Football Corner Kicks model would be quite easy to build. It would just involve treating corners for like 'Goals For' and corners against like 'Goals Against', and then using the Poisson method to distribute each team's Expected Corners. Then, you could come up with accurate Corners Over/Under figures 👍
What's a football corner kick??
@@excel_ladz What's EPL ??
It's possible to do for soccer/football
Can't seem to get the R/9 and RA/9 columns to populate anything other than an #N/A error at the moment - copied your setup on the data imports and formulas to the letter...
So ive managed to add pitching matchups and imported pitching data but i run into this problem where some pitchers have a 3rd name and therefore an extra column in their data and it completely breaks the model, as in the lookup returns no data for those guys and then the whole data table gets messed up. Anyone come up with a solution for this? Ive tried adding wildcards to the vlookup logic but nothing seems to work. Im running out of ideas. Maybe build an if/then into the lookup? Idk, if anyone has a fix for this please advise.
I've come to a standstill using Google Sheets. I'm stuck on the WhatIf Analysis. Anybody have any ideas?
Good video. But estimating the probability in this way will not be accurate, since the starting lineup of the teams must be taken into account. The whole secret lies in estimating the expected runs for teams as accurately as possible.
Great content, just commenting for the algorithm. Thanks Mate 🤝
Haha great idea lad 🔥 Glad you enjoyed it 👍
Hi Excel Ladz, great video just got a question on how do you do the alphabetical formula in the power query page for columns BB and BL, Thanks.
Thank you for this video. Can you tell me How do you create the numbers in the Alphabetical column?
How did you import the stats?
Can you do a MLB player props Excel spreadsheet
Hi lad, great idea 👍 I’ll look to do this in the short term 🔥
hello great video but I have a question about how I can replace SEQUENCE(20) because in Excel2010 this formula does not exist. And as you use it to calculate the "probality distribution" I'm stuck on this part.thanks for advance
Hi lad, there’s only a slight change needed to be done to the spreadsheet 🔥 To replace the SEQUENCE Function, just type out the numbers 0-20 👍 If you also don’t have access to XLOOKUP, then the corresponding probability for 0 goals would have to start at 0%, then the 1 goal probability would be the previously 0 goal probability… This is because VLOOKUP return the lowest value instead of the highest one which XLOOKUP does. I hope that helps a bit lad 💪
@@excel_ladz the VLOOKUP returns the 1st value in the table, of course if it is sorted in ascending order it will start with 0 but if it is sorted in descending order it will be the highest value 😉
Which are those two tables you're pulling from the teamranking site please mate?
Nevermind, it must be runs per game and opponent runs per game right?
@@tomkmb4120 yeah it is it took me awhile to find them until i realized they are in this comment section i dont understand the alphabetical numbers though
When I'm trying to load more than the standard batting table from the powerquery - it's just saying they're 'loading' perpetually on the web view. They never show up in the excel Navigator box, only the team standard batting table does - is this the site trying to limit scraping?
had the same error, if you scroll down to the bottom of baseball reference website, you will see a link to pitching and fielding url. click that and use that to import stats instead
@@joshbull2064 Thanks bro, good looking out - appreciate the help!
Also having an issue with the formula to grab the information from the teamrankings tables, because they're using diff naming conventions - I know he's tried to get around that with the sequence(30) but for some reason it just returns all 0's for me...all data types are correct in the imported tables. This is what I get - i.imgur.com/B56NML9.png
My setup is identical to his in terms of cell placements etc.
G’day lad, SEQUENCE may not work on your version of Excel - it’s an array function. It doesn’t matter though, all you need are the numbers listed - doesn’t matter how you get them 😃
@@excel_ladz Cheers for the response - yeah look at my setup though, sequence should work fine as I've got the numbers there but for some reason it just returns all 0's - I've ensure the cells are formatted as numbers i.imgur.com/YB5Egwk.png
i.imgur.com/gTikIkH.png
Does anyone know how he got the "R/Innings" and "RA/Innings" metrics?
TeamRankings, "MLB Team Extra Inning Runs per Game" and the opposite for the other collum
this is a great video. i don't have excel but i am building along in google sheets. when i go to enter the equation to solve for C, it returns "Error
A matching value could not be found. To get the values for the entire range use the ARRAYFORMULA function." Anyone know how to troubleshoot this? also that long eqaution for C sequence (20)... part of this video is kinda confusing and i'd be curious to learn how that all works rather than just copy your work. might make for an interesting video, but cool video and i followed you.
did you find a fix?
@@Cstanford03 yes sir, but thank you
@@vclkufxxbgfdfehow did u fix it? I’m getting the same error message on google sheets. Did u have to use the arrayformula?
@@LettyMMA yea but i don't remember how lol. i think i ended up using a poisson distribution formula i found online instead of the equation in the video. might not be as accurate but it seems to work out fairly well. i dont remember exactly how i did it but i think if found answers by watching youtube videos on "poisson distribution probabilities with google sheets." if you have any questions i might be able to help but its been a few weeks and i really dont remeber.
@@vclkufxxbgfdfe I appreciate ur reply bro. I’m gonna google that and see what I can come up with, as I am still stuck at that step 😂
too much for me
why would anyone put anything that works on youtube ? it would be stupid to the max
because its not gonna be very accurate when pitcher data isnt incorporated, theres gonna be a big swing in runs scored vs someone like corbin burnes vs someones 5th starter, so its taking into account season long stats but for single game bets its not gonna show that variance
👎