Share your OOTP Spreadsheets!

[OBSL Commish]

https://www.dropbox.com/s/q2q6dsuguj...ater.xlsm?dl=0

This is an Excel macro that will extract ratings from one league and paste them into another league's rosters, provided it finds the same player names in both rosters. I use this macro to update the ratings for the teams in my online league when each new version of OOTP comes out.

The only thing I haven't been able to figure out is how to change the file names in the macro and have it work. As it is, both files have to have the names "obsl_rosters" and "updated_rosters" in order for this to work. The files can be changed to whatever names after the updating is done. If anyone has a clue how to make the changes work, please let me know.

[Athletics]

Originally Posted by NoOne

you should look into setting the data up in a db, then make various report templates that query the data and fill everything in for you. absolutely no effort or work required after you set it up.

Originally Posted by Rizon

The DB way is interesting (MS Access?). Do you have any examples with data so I can see it in action?

Thanks to NoOne and Rizon, I got inspired to sit down for a weekend and code a Python script to read in CSV files, use PyMongo to save the raw data to MongoDB, and then query the raw data from MongoDB to calculate stats.

I wanted an easier way to see wOBA, ISO, and xFIP splits for my entire fictional league. It's not user-friendly at all and runs from the command line.

Also, compare the game save file to the database size.
3.7G braves.lg/
ootp_braves 0.953GB

Source code and example output can be found here. https://github.com/namtsui/ootp

[Kobeck]

Originally Posted by joefromchicago

This is a spreadsheet I've developed to calculate market size. It's applicable to major US cities through 2010 and Canadian cities through 2006 (I need to update that). Files are in OpenOffice format.

Attachment 439568

Attachment 439569

what does the 2 different number ranges represent ?


and thanks for sharing




EDIT - Nevermind I found the how to file....

[BBGiovanni]

For those of you using spreadsheets to "calculate" market size, are you basically using some factor to convert population/raw numbers like radio market population to OOTP market size? Or reverse engineering budgets, or what?

PS: Rizon, were you really Twins34? Curse you

[joefromchicago]

Originally Posted by BBGiovanni

For those of you using spreadsheets to "calculate" market size, are you basically using some factor to convert population/raw numbers like radio market population to OOTP market size? Or reverse engineering budgets, or what?

My numbers are based on US and Canadian census figures, so it's the former. TV/Radio market numbers are fine for modern leagues, but they obviously aren't available for earlier time periods, so that's why I use census figures.

[NoOne]

Originally Posted by Athletics

Thanks to NoOne and Rizon, I got inspired to sit down for a weekend and code a Python script to read in CSV files, use PyMongo to save the raw data to MongoDB, and then query the raw data from MongoDB to calculate stats.

I wanted an easier way to see wOBA, ISO, and xFIP splits for my entire fictional league. It's not user-friendly at all and runs from the command line.

Also, compare the game save file to the database size.
3.7G braves.lg/
ootp_braves 0.953GB

Source code and example output can be found here. https://github.com/namtsui/ootp

i never saw rizon's reply. i don't put in that kind of effort. i am fine with the stats in game.

the only spreadsheet i have made for this game is to track stats related to LTMs. virtually everything is automated excepted adding or reducing # of years to be evaluated. i would have had to look that up. it's been 20 years since i learned this stuff and i don't do anything sophisticated with spreadsheets in my normal life since then. effort would not have matched the return. i just keep two template files - one for 100, the other 200 years.

if i make anything in the future, it will be to test things. e.g. at some point i'd like to do is flesh out which pitch combinations result in lower ERAs than what is projected in the player editor.

once you set up the data (anythign repeatable = can be automated), the spreadsheet does all the hard work, or you can use VB to create your own functions.

you can know every "best" decision within this video game, if someone puts in the time, lol. again, not really worth the effort and probably removes all the fun.

[Athletics]

My Python script reads in raw data from the game's csv files, saves it to a database, reads from the database to calculate some stats for splits, and outputs batter and pitcher csv files.

name,year,team,pos,bats,pa,pa_l,pa_r,woba,woba_l,w oba_r,iso,iso_l,iso_r
Moritz Lingen,2019,POR,3B,R,618.0,201.0,417.0,0.363,0.401 ,0.345,0.257,0.297,0.239
Ian Zilberman,2019,POR,SS,R,572.0,185.0,387.0,0.302,0. 312,0.297,0.111,0.116,0.109

name,year,team,pos,throws,ip,ip_l,ip_r,era,era_l,e ra_r,xfip,xfip_l,xfip_r,k9,k9_l,k9_r,bb9,bb9_l,bb9 _r
Carlos Navarro,2019,POR,P,R,196.0,87.0,108.0,4.684,4.759, 4.667,4.292,4.36,4.247,6.75,6.931,6.667,2.526,3.10 3,2.083
Tom Warner,2019,POR,P,R,138.0,60.0,77.0,4.304,4.35,4.3 25,4.231,4.028,4.401,7.435,7.35,7.597,1.957,0.9,2. 805

Then, I slice and dice the data inside my spreadsheet. Certainly, the game provides a lot of tools to keep track of this information. Using my own notes, I can do things like sort by lineup, mark star players, and research matchups.

I'm still trying to figure out an ideal workflow because the update operation takes 1 minute per team and ~30 minutes to calculate the csv files for the entire league. I plan to update stats every few months during the current season.

I've found that taking the time to sit back and explore the data that the game generates is really immersive and fun! I love the suggestions in this thread and seeing how you all play the game. I have started to incorporate some of these ideas into my workflow. For instance, before, I would never really keep track of the star players of other teams and just focused on building up my own team. Then, I noticed what you all were paying attention to and keeping track of in your spreadsheets. By writing things down and observing the vast landscape, I am better enjoying the dynamic game of baseball.

Also, stats-only is hard, and I will take any edge that I can get.

[NoOne]

a simple front-end like heidisql can import csv they made the script for you. alot prettier and easier to read.

you can make any query/rport you wish with sql. they probably give enough info to extrapolate most statistics out there, if not included in the game.

[r0nster]

Certainly hopes one-day to learn how to use spreadsheet for reports

[darkcloud4579]

This thread is amazing, only found it today. Such good stuff.

My financial calculator for OOTP is called GMExcel.

I created a Google sheet for Tangotiger's Cy Young Calculator, too.