Excel LADZ | 2024 MLB Lineup Model
Added 2024-05-29 23:08:59 +0000 UTCG'day, lads!
I've created an MLB Model that uses daily lineups to project a team's xRuns in a matchup. This includes calculating the influence a side's pitcher and batting lineup have on their expected performance. The model (using array formulas) is attached below!
An article & video explaining how I built this model will be released shortly π
HOW TO USE
Attached are 2 files: 'Pitcher Model xRuns' and 'xRuns Simulation Model'.
The first file, 'Pitcher Model xRuns' contains the data to project a team's xRuns in their daily matchup. When downloading this file, you may have to "Connect" to the FanGraphs datasource as "Anonymous" in order to get the stats into Excel through a power query.
On the 'Dashboard' worksheet, select the two teams playing each other using the drop down list in cells C1 and C3. The Starting Pitcher for each team will automatically populate beside the selected team. The rest of the stats will update as well, with the xRuns result being presented in cells O1 and O3.
There is the option to change two more parameters in this model: 'Pitcher Confidence' and 'Pitcher Game %'. The 'Pitcher Confidence' stat is how confident you are in the Starting Pitcher's FIP being completely accurate and reflective of the pitcher's strength; I've left it on 50%. The 'Pitcher Game %' figure is how much of the game you expect the pitcher to pitch for.
NEXT, to find the probabilities for each market (e.g. Moneyline, Over/Under, etc) go into the 'XRuns Simulation Model'. Input the xRuns into cells I1 and I4 for each team. This will run simulations, and you can see the results in the "Betting" worksheet.
There is also a "Live Win Probability" feature to the right in the "Dashboard" worksheet. This will also update the "Betting" worksheet.
I decided to create an extra file for the xRuns Simulations as integrating it into the 'Pitcher Model xRuns' was too slow. Excel was working too hard to bring in the stats for batters & pitchers, that when the simulations were added, the model became way too slow and wasn't functional.
NOTE: Right now, the file is only available for those with Excel 2019 and later. I'm working to convert this into a 'Legacy' file.
Thanks for reading lads, let me know if you have any questions or issues π₯
Comments
Hi, lad! In the next couple of days, I'm working on releasing the NFL Model to YouTube. After this, I can begin building the Legacy model for this file π I'll keep you updated π
Excel LADZ
2024-09-20 12:40:40 +0000 UTCHi guy, when do u think you will publish teh legacy version of Pitcher model ? Bescause i'm using Excel 2021 but some formulas like textebefore are missing of my excel version. Thanks for your amazing job !
Adrien
2024-09-19 07:12:10 +0000 UTCThank you! Iβll try to download again and run through that.
Jack
2024-06-12 11:02:29 +0000 UTCHi lad! When downloading the model initially, there should be a pop window to "connect anonymously" to the FanGraph website. Click "connect" or the equivalent to this, and make sure "Enable is selected" if any yellow bars appear across the top of the screen - this "enables" the Power Query. I'll be releasing a video on how to set it up shortly π
Excel LADZ
2024-06-11 19:41:43 +0000 UTCLoving the model, any eta on when you'll have the new video up? Also another question/request, is there a way to add cells that display the pitchers total innings pitched on the season? or even to display their average innings pitched per start? For the pitcher game% cell I'm basically dividing their innings pitched by their amount of starts on the season, but having to use and outside reference to figure this out which adds up to be a lot of time when trying to gather this data for every starting pitcher on the day.
Nate Contreras
2024-06-11 14:54:28 +0000 UTCAlso for pitcher model x runs, i cannot get it to load power query data. any advice to get it rolling?!
Jack
2024-06-10 18:13:41 +0000 UTCreferring to the 2024 mlb model
Jack
2024-06-10 18:10:20 +0000 UTCDoes this include pitcher data? I didn't see any pitcher info in the power query, where it pulls lineups and starting pitcher to project that against the lineups for opposing teams.
Jack
2024-06-10 18:09:54 +0000 UTCCouple of questions. Is there a timeline on when the new baseball model will be released that you mentioned a couple of weeks ago? Also, the MLB xRuns simulator is not working, will there be a fix for that as well?
Randy Reed
2024-06-09 21:15:36 +0000 UTCOk canβt wait to see the video you make on this Thanks
Bob
2024-06-06 04:00:52 +0000 UTCHi lad π₯ With regards to sports betting there wouldn't be a best win % to play. Rather, you should be seeking an expected positive ROI with each bet placed. Mathematically, if you expect an event to happen 50% of the time, and odds of $4.00 are offered (25% chance), then that has a positive ROI (return on investment) and that's a mathematically good bet. If you're looking to just pick winners, then the team with the highest Win % is always the "expected" winner π I'm going to release a how-to-use video on the MLB Pitcher model in the next few days lad π Using player stats can become really volatile, so it's important to adjust the model and relevant multipliers (pitcher DEF and batter ATT multipliers) when the player data is misleading due to a low sample size or luck πͺ
Excel LADZ
2024-06-06 03:56:27 +0000 UTCSo whatβs is the win % play is best to play .. I been playing 55% to 70% win and have won 1 game out of 20 ?????
Bob
2024-06-06 02:14:50 +0000 UTCHi lad! The Pitcher Model will return xRuns for each side in O1 & O3. This figure is calculated through the teams selected in C1 & C3 as well as the starting pitcher for that match in I1 and I3. The team with the highest xRuns is "expected" to win. The Simulation Model will find the probability of this actually happening (e.g. 60%). In the Simulation Model, just enter the xRuns results generated in the Pitcher file manually into I1 & I4, and the file will run simulations with these numbers automatically. The win probability will show up beside in in K1 & K4, and the probabilities for all the other markets can be found in the "Betting" worksheet π
Excel LADZ
2024-06-04 19:49:18 +0000 UTCthe 2024 MLB Pitcher Model xRuns file is not complete, not all teams or launcher
Victor Saul Parra Rodriguez
2024-06-04 17:54:22 +0000 UTCHi, good morning
Victor Saul Parra Rodriguez
2024-06-04 17:53:39 +0000 UTCI apologize if this question is redundant but is the "game winning prediction" located on I1 and I3 on the pitching model or should we rely on the run simulator to predict winners?
Fabian Timmons
2024-06-03 23:25:53 +0000 UTCThe MLB xRuns Model is not refreshing for me now. I redownloaded the file just to make sure.
Randy Reed
2024-06-02 16:12:21 +0000 UTCHi Nate, yes I would call it an "eye test". The Pitcher Confidence stat contributes to the overall Pitcher effect on his team's DEF Rating. So, a higher Confidence % will mean the pitcher's multiplier is larger. If it's a starting pitcher who's played a lot of games so far in the season, that number can go to 100%. However, for a pitcher who hasn't started a game yet, their FIP might be really lopsided and, left without a Confidence change, will have a massive positive/negative impact on the opposition's xRuns - when they shouldn't. This rating would go to zero for such a pitcher. At 0%, the Pitcher multiplier goes to 1 - meaning absolutely no increase or decrease is applied to his team's Defensive rating, and consequently the xRuns of the opposition π
Excel LADZ
2024-06-01 11:28:01 +0000 UTCso I'm just using average innings pitched per start for the game % option but I'm wondering if you have any suggestions for how to come up with a number for pitcher confidence? is that more of just an eye test if you've been watching the games?
Nate Contreras
2024-05-31 21:45:49 +0000 UTCIt can't come into Excel through a regular power query on my computer unfortunately π’
Excel LADZ
2024-05-31 20:46:29 +0000 UTCI've just uploaded an edited file, that streamlines the power query response process. It should work for all of today's games on Baseball Monster, and perhaps into the future. Of course, I'll keep monitoring the file and update anything if it breaks with the power query π Thank you for your help and attention π
Excel LADZ
2024-05-31 20:45:53 +0000 UTCCouldn't you pull lineups for here: https://www.mlb.com/starting-lineups
Randy Reed
2024-05-31 20:31:08 +0000 UTCI appreciate you for looking over this and going to try to find a solution for this!
Randy Reed
2024-05-31 20:29:49 +0000 UTCYeah I was just looking and the Oakland/Atlanta game has verified lineups but I canβt get either of those teams to show up in the drop downs on the dashboard.
Randy Reed
2024-05-31 20:28:53 +0000 UTCHi lad, I had a look. It seems like Baseball Monster's Power Query is very finicky. Some teams are not being brought in because the way they are being presented in the query are different to how to formulas were set up. This is usually the case when the lineups haven't been confirmed yet. I'll work hard over the next few days to come up with a solution for this π
Excel LADZ
2024-05-31 20:27:04 +0000 UTCI see on the daily lineups tab below the match ups it shows an expected time for updates.. injuries report are not in yet.. thatβs what I thinking !!
Bob
2024-05-31 20:09:26 +0000 UTCSo I ended up redownloading the pitcher model and then refreshing the data and it seemed to populate some of the later games for tonight but not all of them. Will try again later on this evening.
Randy Reed
2024-05-31 20:02:08 +0000 UTCOnce the line up is verified Iβm sure the match up should be a true match up outcome.. I was waiting for more info about this!!!!
Bob
2024-05-31 19:55:56 +0000 UTCActually, that what I was trying to do now, but I can't seem to get any of the games/teams to load after the Miami/Texas game tonight. They will not show up for me. Usually they populate, and you can see the teams are on baseball monster where I think this model is pulling from for the daily games.
Randy Reed
2024-05-31 19:53:33 +0000 UTCI look at matchups before and after they are verified so I can maybe see the difference in the weaker or stronger lineup.
Randy Reed
2024-05-31 19:51:42 +0000 UTCI have a question !! Should I wait for the line up to be verified before I run the games match up ?? Thanks
Bob
2024-05-31 19:14:52 +0000 UTCHey, I'm noticing today that it is not loading the last 7 games it's pulling from baseball monster. Starting at 7:20pm through 10:15pm will not load.
Randy Reed
2024-05-31 19:00:24 +0000 UTCSure, send it to ladz.excel@gmail.com and I'll have a quick look over the next couple of days π
Excel LADZ
2024-05-31 12:08:22 +0000 UTCI switched out the quarries but Iβm having trouble replacing the working players section any way I can email my spread to see where I went wrong
Chi sheffield
2024-05-31 12:02:18 +0000 UTCYes lad π To do that, you would just have to replace the NBA Power Query / stats with WNBA Stats. It's just a matter of replacing everything NBA related with WNBA info, the formulas and model would work the same.
Excel LADZ
2024-05-31 11:59:28 +0000 UTCIs it possible to turn the nba model into a wnba prediction spread?
Chi sheffield
2024-05-31 11:31:38 +0000 UTCWhen do the video drop for the new update??
Bob
2024-05-30 20:18:01 +0000 UTCThat's why your only seeing 8 teams populate when looking at Home and Away teams.
Randy Reed
2024-05-30 20:16:29 +0000 UTCLAD don't play today. I think it only populates daily games once they populate on baseball monster, which would be probably tomorrow morning sometime.
Randy Reed
2024-05-30 20:15:44 +0000 UTCany recommendations for calculating pitcher confidence and game%
Nate Contreras
2024-05-30 19:12:05 +0000 UTCHey LAD in the data validation away and home donβt have of the teams to populate!!!!!
Bob
2024-05-30 18:32:31 +0000 UTCVery nice work!
Bobby
2024-05-30 09:59:36 +0000 UTCNo problem at all! Will try it out today! Thank you!
Randy Reed
2024-05-30 09:07:17 +0000 UTCSorry mate, it seems like I forgot to upload the updated one. I'm sure it is fixed now, sorry for the inconvenience. Please re-download the file and the Seattle match should come up straight away.
Excel LADZ
2024-05-30 04:12:49 +0000 UTCif you go to baseball monster, that game is the very last game for tonight.
Randy Reed
2024-05-30 01:56:48 +0000 UTCIf I try to type in Houston in the away team and Seattle in the home team, it says βThis value doesnβt match the data validation restrictions defined for this cellβ
Randy Reed
2024-05-30 01:56:22 +0000 UTCHi lad, try typing the team name into the Home and Away Team cells. If that doesnβt work, then I recommend joining the Discord so you can send me screenshots so I can have a better look at the problem π
Excel LADZ
2024-05-30 01:45:29 +0000 UTCHummm, I redownloaded the file and I still don't see the Houston/Seattle game for tonight.
Randy Reed
2024-05-30 01:03:42 +0000 UTCHi lad, thatβs all been fixed now. Please re-download the file from the post and you shouldnβt have an issue. All the matchups are generated through the Baseball Monster website and brought in through a power query. Whatever you see there, should be the matchups that come into the model π Apologies for the inconvenience.
Excel LADZ
2024-05-30 00:55:57 +0000 UTCSo right now, In The first file, 'Pitcher Model xRuns', I don't see all the teams listed in the drop down boxes for the home and away teams in the dash board. I tried to look up the 9:40PM game Houston/Seattle and neither team is listed. Any thoughts?
Randy Reed
2024-05-30 00:49:01 +0000 UTCHeck yeah! Will be kicking the tires on this tomorrow! THANK YOU!
Randy Reed
2024-05-30 00:22:51 +0000 UTC
