Excel LADZ
patreon
How I Updated the Excel LADZ 2023 MLB Model
🕑 Added 2024-04-04 10:49:32 +0000 UTCG’day lads! An essential part of sports modelling is being able to update a model to reflect current data. In this article, I’ll run you through how I updated the ‘Excel LADZ - 2023 MLB Model’ to be ready for the 2024 season.
JOIN the best Excel Sports Modelling Community by becoming a member of the Excel LADZ Patreon! Receive downloadable access to every model (including the Excel LADZ MLB Model) as well as technical posts, like this, which will improve your understanding of sports modelling. You also have the ability to directly message me lads, so I can help you with any questions/problems!
Download ‘Excel LADZ - 2024 MLB Model’: https://www.patreon.com/posts/excel-ladz-2024-101481659
Importing 2024 MLB Stats
My first step was importing the 2024 ‘Team Standard Batting’ and ‘Team Fielding’ datasets from the website Baseball Reference (links at the bottom of the article). In Excel, it is possible to connect your file with an updating dataset on the internet. This connection is called a ‘Power Query’.
Instead of having to copy and paste new data into your model each day, a Power Query connects Excel with the website url. Once the worksheet is refreshed by selecting the “Refresh All” icon within the ‘Data’ tab, the connection is updated - bringing in live and up-to-date stats from the website’s dataset.
If you’re unsure on how to set up a power query - look it up on YouTube! It’s very straightforward for Windows users, but it’s definitely possible for 99% of Excel users. For lads on a Mac, have a look at the ‘Excel Power Query for Mac’ tutorial I put up on YouTube in 2023.
Integrating 2023 MLB Stats
Now that I’ve got the 2024 MLB power query connected within my file, I can remove the 2023 MLB power query. However, instead of removing the stats completely, I’m only going to remove the power query by copying and pasting the values over the top of the existing dataset.
This is so I can use a proportion of 2023’s data in determining the strength of 2024 teams at the start of the season. Obviously, a team’s true ability cannot be effectively assessed after 1 game; or even 10 for that matter. The more data, the better!
So, to come up with the ATT & DEF Ratings for every MLB Team I used a “sliding scale” with a cutoff of 30 games. For example, after 1 game in season 2024, a team’s ATT & DEF Ratings are 1/30th of their 2024 performance, and 29/30ths of their 2023 performance.
20 games into the 2024 season, the ATT & DEF Ratings will be based off of 20/30ths of their 2024 performance, and 10/30ths of their 2023 performance. This continues until after a team plays 30 games in the season, and their ATT & DEF Ratings are completely based on their performance so far that year - the 2023 data is completely forgotten.
Using the Model
Now that the model is built, all I need to do is update the model’s stats each day. Go into the ‘Data’ tab, and select the “Refresh All” icon. That’s all you need to do!
Thanks for reading lads, JOIN the Patreon today!
Baseball Reference Team Standard Batting: https://www.baseball-reference.com/leagues/majors/2024.shtml#all_teams_standard_batting
Baseball Reference Standard Fielding: https://www.baseball-reference.com/leagues/majors/2024-standard-fielding.shtml
Comments
Ben
Out of curiosity, can you use this model to predict pitchers strikeouts / hits / walks / outs?
Victor Saul Parra Rodriguez
Is the file updated automatically at the end of the day's games?
Kevin Spiro
I just have a question on one of the data sets and what is it Alphabetical Rank Team 2024 Last 3 Last 1 Home Away 2023 24 1 Seattle 0.75 1 0 0.75 -- 0.14 22 1 Pittsburgh 0.75 0.67 2 -- 0.75 0.08
James
I was using Python, so yes. But, panda was having problem. I need to get better with Python. R? I might try that in the future.
704 G
James, are you scraping the data with numpy? I was successful a few times but have not tried since. Have you used R? I am taking a Course on R now to really get in the weeds with these statistics.
James
LAD, that is an excellent idea. I had been thinking that DEF average will not properly account for an ACE versus a #4 pitcher in a rotation. Starting down the path of individual players, and how they contribute to a teams DEF or ATT starts with pitcher, but then you can expand that more and more. Might not be worth the effort, but that was where my mind was going, when I was planning on doing all of this in Python.
Excel LADZ
Hi lad! Yes, that’s definitely going to be my next addition to the model. I’m planning on making a team’s pitcher a multiplier to the team’s existing ATT & DEF strength. For example, a relatively strong pitcher might increase the DEF rating of their own team.
704 G
Is there a way to include pitching on this model?
Excel LADZ
Hi lad, that's awesome 🔥 A rough estimate is in the mid 60-s in terms of how often the favourite determined by the model wins. I built the model using the research done in this paper: https://legacy.baseballprospectus.com/images/analytica/rpi_model.pdf
Koby Stone
hi, i am interested in signing up for access to the MLB model, but before I do, can you tell me a rough estimate of the model's accuracy %?
