How I Updated the Excel LADZ 2023 MLB Model

G’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