*Spreadsheets* Do you guys use them? - Page 2

CBeisbol · 12-20-2020, 01:59 PM

Quote:

Originally Posted by Syd Thrift

I got white-out all over my bedsheets! You think your Commodore 64 is really neato! What kind of chip you got in there, a Dorito?

:RockHorns:

But,
They call ME, the king of the spreadsheets, I got them all printed out on my bedsheets.

joefromchicago · 12-21-2020, 11:25 AM

An old thread, but it still might have some useful stuff.

EDIT: I see Cavebutter already linked to this thread. Never mind.

ThePride87 · 12-21-2020, 12:08 PM

Currently working on several worksheets in preparation for a Tigers run.

I sim 30 years ahead into 2050 to ensure all manually placed players are gone and retired...I want a 100% AI generated universe remaining so I know how the game thinks.
Then, season by season, I track team WAR to team wins on a chart. I'm thinking something like 100 years-worth of it....I take the median WAR of each set of wins (for instance, take the median team WAR for all 81-81 teams that existed....that median will represent 81-81 teams). My goal is to prove to myself team WAR and team wins are heavily correlated, which I know they will be. It's to confirm this whole effort is even worth it....if they weren't correlated, WAR would be useless.

I then calculate total $ spent each season from the league on players (player expenses + misc player expenses....if a Chris Davis gets cut, it should still count if the team is paying for him). I divide that number by total WAR accumulated from players that season (add all hitting/pitching WAR from all players that had either 1 PA or 1 BF). That way I know how much 1 WAR costs in the game, which is usually ~$4 Million (which shows why free agency is a bad long-term strategy, you usually pay double-price, and they're usually players who peaked already...on the downswing). In order to make this process easier, I use universal DH, no openers/followers, and no two-way players. It just makes collecting the numbers a lot easier, and for some reason pitcher hitting WAR is wrong in this game....no pitcher gets rewarded for batting well relative to other pitchers, it's always negative.

I also use 100% ratings for this experiment. I need to know what a player's true rating is at every moment. Obviously once I play the actual game, that will go back to Normal.

Then, I record every player who participated in MLB for the season and track their PA/BF, where they played defensively, and whether their rating fluctuated during the season. Any player who only played in one position and never changed ratings-wise, I track their PA/BF and their WAR produced for the year. That will help me know how much WAR/BF or WAR/PA a player at that position and rating produces on average. I then multiply that number by the median amount of PA or BF a starting player at that position produces (i.e. take the median number of the top 30 catcher PA's for a given season). This gives me an idea of how much WAR I can expect out of any player given their current situation, and attach a $-value to them. This will obviously lead to a lot of unused player data, but I need to know when I look at a player's WAR that it wasn't diluted with them playing other positions or changing as a talent.....if I have a 60 OVR 3B, I need to make sure that's all he has been this year, or his WAR may have been altered/tainted.

I'm also tracking age vs productivity to see if I can project prospects' future based on their current age and their potential....but I have a feeling that's going to be near-impossible to really do. Every player is a unique case, it's really hard to tell what they will become knowing the randomness of talent changes/circumstances. The main goal is to know what a player's worth is today with a WAR and $ amount attached to them.

dumbrabbit · 12-21-2020, 04:47 PM

I would pay someone on Patreon to do a youtube series on excel spreadsheets for OOTP, and, or programming python for spreadsheets as well.

luckymann · 12-21-2020, 11:26 PM

The best thing about this thread is that it lets me know that, while I may take my OOTP experience way too seriously with all of the spreadsheets and documentation I keep, at least I'm not the only one...

And it's funny how similar some of the spreadsheets on here, or at least the methodology behind them, are to mine.

For example, my player contracts sheet:

jparker2112 · 12-22-2020, 04:56 PM

Id LOVE to start a fresh world and utilize spreadsheets, but it seems so damn intimidating! I really wouldn't even know how to start and what settings to use for the league(s).

Leo_The_Lip · 12-23-2020, 12:58 PM

Quote:

Originally Posted by BillyBall

Would love to see a spreadsheet built right into OOTP.

This amuses me because back when OOTP, Baseball Mogul and Front Office Football first came out, they were disparaged by game magazine reviewers for being mere 'spreadsheet games.'

CBeisbol · 12-23-2020, 05:09 PM

Quote:

Originally Posted by jparker2112

Id LOVE to start a fresh world and utilize spreadsheets, but it seems so damn intimidating! I really wouldn't even know how to start and what settings to use for the league(s).

Don't start over
Just start using spreadsheets

wallewalls · 12-24-2020, 08:45 AM

I've always wanted to build spreadsheets to the extent that others in this thread have but I usually just end up creating a basic depth chart in the offseason sometimes to evaluate who I have on my roster, in my minor league system that can contribute to the MLB squad in the next season as well as top prospects at each position with their ETA and potential. I admire all of these that have been shared, though.

Syd Thrift · 12-24-2020, 02:56 PM

“Include spreadsheets in the game” is pretty useless advice for three reasons:

1. There are already, basically, spreadsheets all over the game. The lineup and pitching screens, the individual player cards, the team stats screens, etc.

2. Spreadsheets are okay to use I guess when you’ve got one set of data you’re consuming but if you need to programmatically get at the data at all, they’re of very, very limited use. OOTP, like any even somewhat sophisticated data modeling system, uses a relational database to get at everything and the “spreadsheets”, then, are somewhat glorified views (although again, databases tend to be far more powerful).

3. What you’re really asking for are features. Ask for the features instead. Do you want to design your own formulae? That seems like a decent ask (and something only recently implementable). Do you want to be able to control what columns and what order you see on the screen? The game already does that and I’m sure that it’ll get even better in the future. Do you want to hand-enter stats? To each their own I guess but no thanks; speaking as a programmer I’m way too lazy to do that. I will take 4 hours to program something so that I can avoid spending 2 hours doing data entry (who am I kidding? If it’s 2 hours of data entry, it’s probably closer to 40 hours that I’d spend).

Timofmars · 12-24-2020, 11:02 PM

I think you want the AI to be as smart as possible, as much as a programmer can make it, because it can always be artificially handicapped later. For example, we already have GM tendencies in the game so that they favor OBP versus AVG, vets vs prospects, etc. So these preferences are already a kind of inefficiency from some optimal style.There's also manager strategies, which are pretty much random rather than being tailor made for the team. Then there's trade difficulty levels. And the budget differences based on market size. If all this weren't enough, an additional difficulty setting could be introduced.

So I don't think we should worry about the AI becoming too good.

dumbrabbit · 12-28-2020, 09:11 AM

Can someone start a Youtube series explaining how they use spreadsheets for OOTP, and how to use programming as well to come up with spreadsheets too?

JudgeGio · 01-02-2021, 11:11 AM

I export rosters to a .CSV file, open it in Excel and then examine the difference of player experience to age. Sometimes a player will be 23 years old with 15 years professional experience, or a player will be 38 with 2 years experience. In Excel I create a new column with formula= (age - years experience). If it's anything other than a reasonable number like 18-25, I change years of experience value and import the .CSV back into the game. Years of pro experience makes a big difference as far as performance or contract demands.

12-21-2020, 11:25 AM	#22
joefromchicago Hall Of Famer Join Date: Jun 2011 Posts: 3,703	An old thread, but it still might have some useful stuff. EDIT: I see Cavebutter already linked to this thread. Never mind. __________________ American-Ethnic (and Canadian) Namesets Historical Minor League Schedules 1870s City/Team Nickname Randomizers "It's Usually Sunny in Philadelphia" weather mod Negro League Schedules Last edited by joefromchicago; 12-21-2020 at 11:27 AM.

12-21-2020, 12:08 PM	#23
ThePride87 Minors (Double A) Join Date: Feb 2019 Posts: 100	Currently working on several worksheets in preparation for a Tigers run. I sim 30 years ahead into 2050 to ensure all manually placed players are gone and retired...I want a 100% AI generated universe remaining so I know how the game thinks. Then, season by season, I track team WAR to team wins on a chart. I'm thinking something like 100 years-worth of it....I take the median WAR of each set of wins (for instance, take the median team WAR for all 81-81 teams that existed....that median will represent 81-81 teams). My goal is to prove to myself team WAR and team wins are heavily correlated, which I know they will be. It's to confirm this whole effort is even worth it....if they weren't correlated, WAR would be useless. I then calculate total $ spent each season from the league on players (player expenses + misc player expenses....if a Chris Davis gets cut, it should still count if the team is paying for him). I divide that number by total WAR accumulated from players that season (add all hitting/pitching WAR from all players that had either 1 PA or 1 BF). That way I know how much 1 WAR costs in the game, which is usually ~$4 Million (which shows why free agency is a bad long-term strategy, you usually pay double-price, and they're usually players who peaked already...on the downswing). In order to make this process easier, I use universal DH, no openers/followers, and no two-way players. It just makes collecting the numbers a lot easier, and for some reason pitcher hitting WAR is wrong in this game....no pitcher gets rewarded for batting well relative to other pitchers, it's always negative. I also use 100% ratings for this experiment. I need to know what a player's true rating is at every moment. Obviously once I play the actual game, that will go back to Normal. Then, I record every player who participated in MLB for the season and track their PA/BF, where they played defensively, and whether their rating fluctuated during the season. Any player who only played in one position and never changed ratings-wise, I track their PA/BF and their WAR produced for the year. That will help me know how much WAR/BF or WAR/PA a player at that position and rating produces on average. I then multiply that number by the median amount of PA or BF a starting player at that position produces (i.e. take the median number of the top 30 catcher PA's for a given season). This gives me an idea of how much WAR I can expect out of any player given their current situation, and attach a $-value to them. This will obviously lead to a lot of unused player data, but I need to know when I look at a player's WAR that it wasn't diluted with them playing other positions or changing as a talent.....if I have a 60 OVR 3B, I need to make sure that's all he has been this year, or his WAR may have been altered/tainted. I'm also tracking age vs productivity to see if I can project prospects' future based on their current age and their potential....but I have a feeling that's going to be near-impossible to really do. Every player is a unique case, it's really hard to tell what they will become knowing the randomness of talent changes/circumstances. The main goal is to know what a player's worth is today with a WAR and $ amount attached to them. Last edited by ThePride87; 12-21-2020 at 12:11 PM.

12-21-2020, 11:26 PM	#25
luckymann Hall Of Famer Join Date: Nov 2019 Posts: 13,104	The best thing about this thread is that it lets me know that, while I may take my OOTP experience way too seriously with all of the spreadsheets and documentation I keep, at least I'm not the only one... And it's funny how similar some of the spreadsheets on here, or at least the methodology behind them, are to mine. For example, my player contracts sheet: Attached Images __________________ HISTORICAL DO-OVERS A'S RED SOX DODGERS CUSTOM SAVES ECLIPSE LEAGUE MOON SHOT LEAGUE EVERYMAN LEAGUE GULF LEAGUE USBA

12-21-2020, 04:47 PM	#24
dumbrabbit All Star Reserve Join Date: Aug 2012 Posts: 827	I would pay someone on Patreon to do a youtube series on excel spreadsheets for OOTP, and, or programming python for spreadsheets as well.

12-22-2020, 04:56 PM	#26
jparker2112 All Star Reserve Join Date: Nov 2012 Location: Denver CO Posts: 864	Id LOVE to start a fresh world and utilize spreadsheets, but it seems so damn intimidating! I really wouldn't even know how to start and what settings to use for the league(s).

12-24-2020, 08:45 AM	#29
wallewalls All Star Reserve Join Date: Apr 2016 Posts: 774 Infractions: 0/1 (1)	I've always wanted to build spreadsheets to the extent that others in this thread have but I usually just end up creating a basic depth chart in the offseason sometimes to evaluate who I have on my roster, in my minor league system that can contribute to the MLB squad in the next season as well as top prospects at each position with their ETA and potential. I admire all of these that have been shared, though.

12-24-2020, 11:02 PM	#31
Timofmars Minors (Triple A) Join Date: Feb 2018 Posts: 251	I think you want the AI to be as smart as possible, as much as a programmer can make it, because it can always be artificially handicapped later. For example, we already have GM tendencies in the game so that they favor OBP versus AVG, vets vs prospects, etc. So these preferences are already a kind of inefficiency from some optimal style.There's also manager strategies, which are pretty much random rather than being tailor made for the team. Then there's trade difficulty levels. And the budget differences based on market size. If all this weren't enough, an additional difficulty setting could be introduced. So I don't think we should worry about the AI becoming too good.

12-28-2020, 09:11 AM	#32
dumbrabbit All Star Reserve Join Date: Aug 2012 Posts: 827	Can someone start a Youtube series explaining how they use spreadsheets for OOTP, and how to use programming as well to come up with spreadsheets too?

01-02-2021, 11:11 AM	#33
JudgeGio Minors (Double A) Join Date: Aug 2019 Location: New Jersey Posts: 129	I export rosters to a .CSV file, open it in Excel and then examine the difference of player experience to age. Sometimes a player will be 23 years old with 15 years professional experience, or a player will be 38 with 2 years experience. In Excel I create a new column with formula= (age - years experience). If it's anything other than a reasonable number like 18-25, I change years of experience value and import the .CSV back into the game. Years of pro experience makes a big difference as far as performance or contract demands.