Spreadsheets guide

JHSheridan · 05-23-2017, 02:09 AM

I've seen a few threads here in there of people sharing spreadsheets to better organize their OOTP teams, but I'm having trouble finding a primer that explains the ins and outs of this.

What's the easiest way to get started with this? Has anyone done anything more advanced? I'm experienced with python, and have done a bit of mysql work in my day. Are these also an option? Is there a method for rolling exports throughout the season?

I'm not even sure exactly what questions to ask yet, but these came to mind. I'd be grateful for any information you may have, or links to spreadsheets you've made before.

Thanks!

OldFatGuy · 05-23-2017, 03:35 AM

Quote:

Originally Posted by JHSheridan

I've seen a few threads here in there of people sharing spreadsheets to better organize their OOTP teams, but I'm having trouble finding a primer that explains the ins and outs of this.

What's the easiest way to get started with this? Has anyone done anything more advanced? I'm experienced with python, and have done a bit of mysql work in my day. Are these also an option? Is there a method for rolling exports throughout the season?

I'm not even sure exactly what questions to ask yet, but these came to mind. I'd be grateful for any information you may have, or links to spreadsheets you've made before.

Thanks!

I hope you don't mind (and if you do, I sincerely apologize) but I wanted to add my question in here too regarding spreadsheets.

Can anyone tell me how to save a LibreOffice Calc spreadsheet to TEXT? Only thing close I see is an option to save it as a CSV (comma separated value) but OOTP won't read a schedule saved in that format. Anyone know if there's a way to just save a LibreOffice Calc file to text?

https://www.libreoffice.org/

absurddoctor · 05-23-2017, 11:56 AM

Quote:

Originally Posted by OldFatGuy

I hope you don't mind (and if you do, I sincerely apologize) but I wanted to add my question in here too regarding spreadsheets.

Can anyone tell me how to save a LibreOffice Calc spreadsheet to TEXT? Only thing close I see is an option to save it as a CSV (comma separated value) but OOTP won't read a schedule saved in that format. Anyone know if there's a way to just save a LibreOffice Calc file to text?

https://www.libreoffice.org/

Text just means more or less 'non-binary'. A CSV file is text. So isn't an XML file, which it looks like is what the schedule format files are expected to be.

I've never created schedule files, but someone created a little for doing so in Open Office. In this post he explains how to end up with a file that OOTP can read: http://www.ootpdevelopments.com/boar...6&postcount=11

I haven't done this myself, but that might at least point you in the right direction.

OldFatGuy · 05-23-2017, 12:08 PM

Quote:

Originally Posted by absurddoctor

Text just means more or less 'non-binary'. A CSV file is text. So isn't an XML file, which it looks like is what the schedule format files are expected to be.

I've never created schedule files, but someone created a little for doing so in Open Office. In this post he explains how to end up with a file that OOTP can read: http://www.ootpdevelopments.com/boar...6&postcount=11

I haven't done this myself, but that might at least point you in the right direction.

Oh HEY, I THINK THAT MIGHT WORK!!!! THANK YOU!!!!

I haven't tried it yet, but reading it and opening up LibreOffice, I do have all of those selections he mentioned, so hopefully this will work.

But I tried several times just clicking on save and save as Text.csv file, and then manually renamed the extension to lsdl (or whatever). And OOTP would not read it. Kept saying "Syntax error" when I tried several schedules.

I've never made a schedule or even edited one but I really want to try so I won't have to bother those wonderful folks over there. So, I loaded one up in LibreOffice, and felt pretty good about moving things around (moving things around ONLY though) but then I couldn't get the save to work and couldn't find any way to just save the file to plain text.

Hopefully I do now. THANK YOU AGAIN.

NoOne · 05-23-2017, 12:23 PM

csv is text too. export as csv but use the lsdl extension. (turn off auto-extension check box)

the key is the charcters that are included in the text format or in this case, not included, being important.

you'll notice that a " quatation mark sometimes has an orientation (start/end quotes)... the game won't handle something like that in the lsdl file correctly... they should just be simple vertical ' " ' as you likely see here (your browser may be forcing a different text format than mine). openoffice calc likes to export with the start/end quotes by default.

select the correct text formatting - Western Europe iso-8859-1 - and it should be fine... in Calc, you save as CSV, then "keep current format", then the top drop-down box on the next window should allow you to choose this.. defaults to "windowsblah blah" so, you do have to change it.

if not, you can do a "Find and Replace" within the text editor on your .lsdl file... you'll need to copy the inward and outward inflecting quotes and replace each with the correct quotation character (if you can't find any, open a working schedule file and copy one of them). After that, the game should be able to read it.

any other problems will relate to the title of the file and the header at the top of hte schedule file... i'd suggest keeping it simple in the header -- less conflicts possible... if all else fails, open a 'similar' shedule file and copy/paste that header portion. change # of teams or whatever is slightly different after you paste.

refer to manual for finer details on naming of the schedule files and tidbits about hte header portion.

absurddoctor · 05-23-2017, 12:38 PM

If it helps, the page in the manual on the schedule file syntax is at http://manuals.ootpdevelopments.com/...le-file-layout

NoOne · 05-23-2017, 12:55 PM

Tip: if you do something with stats or anything you regularly export from the game to use...

SS are made to automate mostly everything except inputs... if the input comes from an exported file from OotP, everything should be automated in nature.

so, you create an external data link to the exported file -- which resides in the same directory with the same name each time you export from that league -- any time the spread sheet loads or you refresh the external links, all inputs are updated immediately.

Now, you have this exported data residing in a worksheet -- unadalterated, and it must stay that way... So, if you can't work on it directly, you need to link to the individual fields on a different worksheet (name them as you wish to keep track).

need the whole thing copied? even that is simple to do:

this involves doing 1 row or column then highlight and fill the rest -- sometimes 2 rows/columns if you are manipulating the data in some cases.. to form a 'pattern' for the fill funciton to repeat (maybe changing horizontal orientation of the data to a vertical one).

so you do "=!worksheet3.A1" in the first field you want the data to be worked on to be seen... you learn to use b2, but a1 is fine, too. then simply fill accross or down to create the first row/column. it will automatically change variables (A1 becomes b1, c1, etc... if you fill left to right from a1. )

Now that the first row or column is done, you repeat that fill process for the rest of the table.

adding a $ will lock row or column e.g. $a$1 will never change as you fill a row or column. $a1 will lock the column. a$1 locks the row. can use it for worksheet names too.

obviously you won't be doing all of it... but you link to data to work on it, but never actually change the fields on that exported data sheet, except with a newly exported file / updated data link.

i have an ltm spreadsheet... it adds up the subleagues from the exported data during this process. so it looks somethign like "=sum(!batting.C1:C2)" .. i needed an empty row so that i could "fill" easily... then i sum/average/divide etc on that sheet.

because all formulas involve variables that lead to that exported data, everything updates when you update the data links. always use variables in the formula, even if you have to make some "$a$1" constant field instead of literally typing it into the each formula. now if that supposed constant changes, you don't have to re-do the entire sheet.

all inputs should be linked to in this way... so, after you setup a spreadsheet properly, all you ahve to do is update data links and/or change a few input fields and the entire thing should change for you.

i have a recent post explaining how i did my LTM spreadsheet somewhere, welcome to search for it. think it was in "new to the game" forum.

Cobby · 05-23-2017, 11:40 PM

Here's what I do - not saying it's the best or only way, but it works for me. First I create a custom view that contains the data I want to manipulate in a spreadsheet. Then under the Report drop-down I select "write report to disk". This doesn't seem to actually write the report to the disk, as far as I can tell. Instead the report appears in my web browser. I then select and copy this data (ctrl-a, ctrl-c) and paste it into an Excel worksheet. From there I can massage the data to my heart's content using other worksheets and VBA scripts that get their input from the pasted data.

I do this routinely as I play the game - I think of my spreadsheet as my "analytics department" and I use it to evaluate players, create projections, evaluate trades and to help manage my depth charts and lineups. (this latter can be a real time-saver when trying to figure out how to juggle one's lineups and depth charts after an injury)

itsmb8 · 05-24-2017, 12:41 AM

Quote:

Originally Posted by Cobby

Here's what I do - not saying it's the best or only way, but it works for me. First I create a custom view that contains the data I want to manipulate in a spreadsheet. Then under the Report drop-down I select "write report to disk". This doesn't seem to actually write the report to the disk, as far as I can tell. Instead the report appears in my web browser. I then select and copy this data (ctrl-a, ctrl-c) and paste it into an Excel worksheet. From there I can massage the data to my heart's content using other worksheets and VBA scripts that get their input from the pasted data.

I do this routinely as I play the game - I think of my spreadsheet as my "analytics department" and I use it to evaluate players, create projections, evaluate trades and to help manage my depth charts and lineups. (this latter can be a real time-saver when trying to figure out how to juggle one's lineups and depth charts after an injury)

If you dont mind, could you pm me the setup of your spreadsheet and maybe explain it? Im curious how using one could affects how you play the game.

Cobby · 05-24-2017, 05:59 PM

Quote:

Originally Posted by itsmb8

If you dont mind, could you pm me the setup of your spreadsheet and maybe explain it? Im curious how using one could affects how you play the game.

PM Sent.

joefromchicago · 05-25-2017, 09:16 AM

You might want to check out this thread.

05-23-2017, 02:09 AM	#1
JHSheridan Bat Boy Join Date: May 2017 Posts: 17	Spreadsheets guide I've seen a few threads here in there of people sharing spreadsheets to better organize their OOTP teams, but I'm having trouble finding a primer that explains the ins and outs of this. What's the easiest way to get started with this? Has anyone done anything more advanced? I'm experienced with python, and have done a bit of mysql work in my day. Are these also an option? Is there a method for rolling exports throughout the season? I'm not even sure exactly what questions to ask yet, but these came to mind. I'd be grateful for any information you may have, or links to spreadsheets you've made before. Thanks!

05-23-2017, 12:23 PM	#5
NoOne Banned Join Date: Apr 2015 Posts: 7,273 Infractions: 0/1 (3)	csv is text too. export as csv but use the lsdl extension. (turn off auto-extension check box) the key is the charcters that are included in the text format or in this case, not included, being important. you'll notice that a " quatation mark sometimes has an orientation (start/end quotes)... the game won't handle something like that in the lsdl file correctly... they should just be simple vertical ' " ' as you likely see here (your browser may be forcing a different text format than mine). openoffice calc likes to export with the start/end quotes by default. select the correct text formatting - Western Europe iso-8859-1 - and it should be fine... in Calc, you save as CSV, then "keep current format", then the top drop-down box on the next window should allow you to choose this.. defaults to "windowsblah blah" so, you do have to change it. if not, you can do a "Find and Replace" within the text editor on your .lsdl file... you'll need to copy the inward and outward inflecting quotes and replace each with the correct quotation character (if you can't find any, open a working schedule file and copy one of them). After that, the game should be able to read it. any other problems will relate to the title of the file and the header at the top of hte schedule file... i'd suggest keeping it simple in the header -- less conflicts possible... if all else fails, open a 'similar' shedule file and copy/paste that header portion. change # of teams or whatever is slightly different after you paste. refer to manual for finer details on naming of the schedule files and tidbits about hte header portion. Last edited by NoOne; 05-23-2017 at 12:27 PM.

05-25-2017, 09:16 AM	#11
joefromchicago Hall Of Famer Join Date: Jun 2011 Posts: 3,712	You might want to check out this thread. __________________ 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

05-23-2017, 12:38 PM	#6
absurddoctor Minors (Double A) Join Date: Apr 2013 Posts: 102	If it helps, the page in the manual on the schedule file syntax is at http://manuals.ootpdevelopments.com/...le-file-layout

05-23-2017, 12:55 PM	#7
NoOne Banned Join Date: Apr 2015 Posts: 7,273 Infractions: 0/1 (3)	Tip: if you do something with stats or anything you regularly export from the game to use... SS are made to automate mostly everything except inputs... if the input comes from an exported file from OotP, everything should be automated in nature. so, you create an external data link to the exported file -- which resides in the same directory with the same name each time you export from that league -- any time the spread sheet loads or you refresh the external links, all inputs are updated immediately. Now, you have this exported data residing in a worksheet -- unadalterated, and it must stay that way... So, if you can't work on it directly, you need to link to the individual fields on a different worksheet (name them as you wish to keep track). need the whole thing copied? even that is simple to do: this involves doing 1 row or column then highlight and fill the rest -- sometimes 2 rows/columns if you are manipulating the data in some cases.. to form a 'pattern' for the fill funciton to repeat (maybe changing horizontal orientation of the data to a vertical one). so you do "=!worksheet3.A1" in the first field you want the data to be worked on to be seen... you learn to use b2, but a1 is fine, too. then simply fill accross or down to create the first row/column. it will automatically change variables (A1 becomes b1, c1, etc... if you fill left to right from a1. ) Now that the first row or column is done, you repeat that fill process for the rest of the table. adding a $ will lock row or column e.g. $a$1 will never change as you fill a row or column. $a1 will lock the column. a$1 locks the row. can use it for worksheet names too. obviously you won't be doing all of it... but you link to data to work on it, but never actually change the fields on that exported data sheet, except with a newly exported file / updated data link. i have an ltm spreadsheet... it adds up the subleagues from the exported data during this process. so it looks somethign like "=sum(!batting.C1:C2)" .. i needed an empty row so that i could "fill" easily... then i sum/average/divide etc on that sheet. because all formulas involve variables that lead to that exported data, everything updates when you update the data links. always use variables in the formula, even if you have to make some "$a$1" constant field instead of literally typing it into the each formula. now if that supposed constant changes, you don't have to re-do the entire sheet. all inputs should be linked to in this way... so, after you setup a spreadsheet properly, all you ahve to do is update data links and/or change a few input fields and the entire thing should change for you. i have a recent post explaining how i did my LTM spreadsheet somewhere, welcome to search for it. think it was in "new to the game" forum.

05-23-2017, 11:40 PM	#8
Cobby Minors (Triple A) Join Date: May 2016 Posts: 252	Here's what I do - not saying it's the best or only way, but it works for me. First I create a custom view that contains the data I want to manipulate in a spreadsheet. Then under the Report drop-down I select "write report to disk". This doesn't seem to actually write the report to the disk, as far as I can tell. Instead the report appears in my web browser. I then select and copy this data (ctrl-a, ctrl-c) and paste it into an Excel worksheet. From there I can massage the data to my heart's content using other worksheets and VBA scripts that get their input from the pasted data. I do this routinely as I play the game - I think of my spreadsheet as my "analytics department" and I use it to evaluate players, create projections, evaluate trades and to help manage my depth charts and lineups. (this latter can be a real time-saver when trying to figure out how to juggle one's lineups and depth charts after an injury)