Use of Spreadsheet to track your team

[Sharpie]

So it appears from reading posts that spreadsheet is a preferred method of tracking your organization.

So how many tabs do you use? Hitting/Pitching/Defense??? Salary? Do you add in vs RH/LH for both Pitching & Hitting?

[Drstrangelove]

I don't regularly track my team, with spreadsheets, but I do use those for analysis from time to time. I'd be interesting in seeing what people are doing and using as well.

[Sharkn20]

Would be nice to learn how to export / use / compare in Excel the team reports...

Any guides somewhere??

Sent from my SM-G960F using Tapatalk

[manooly]

Generate the league almanac, copy it to a flash drive and take it to work with you. That way you can check on your entire league while multi-tasking at work.....

[BIG17EASY]

Originally Posted by manooly

Generate the league almanac, copy it to a flash drive and take it to work with you. That way you can check on your entire league while multi-tasking at work.....

And there goes my productivity at work ...

[Rizon]

Originally Posted by Sharpie

So it appears from reading posts that spreadsheet is a preferred method of tracking your organization.

So how many tabs do you use? Hitting/Pitching/Defense??? Salary? Do you add in vs RH/LH for both Pitching & Hitting?

Everything

[Sharpie]

Is there something I am missing?

Overall Position
vs RH Position
vs LH Position
Defense
Overall Pitcher
vs RH Pitcher
vs LH Pitcher
Potential + Demographics +Salary

[old fat bald guy]

Mine just has key ratings -- current star ratings, stuff-movement-control, contact-gap power-HR power-eye-K avoidance. I add them to get an overall rating. My management style is to emphasize ratings over results, in other words, to trust my scouts. Stats are easily accessible in-game, anyway. For hitters I have the ratings against both left- and right-handed pitching. Minor-leaguers who are on the radar, at least in contention for spring-training invites, are on there too, because looking for changes in current star ratings is one way to track development. (Or not -- sometimes a guy drops from 1.5 stars to 1 because he went 0-for-4 yesterday.)

[Gruber_Tagged_Him]

Originally Posted by manooly

Generate the league almanac, copy it to a flash drive and take it to work with you. That way you can check on your entire league while multi-tasking at work.....

Pretty sure they count my keystrokes at work... Now if they are all in Excel, seems I win....

[Cobby]

Spreadsheets are completely integral to how I play OOTP...

I import all of the players into my spreadsheet and use the ratings to produce a projected runs-created or projected WAR for each player assuming that player played full-time. I make my best attempt at this and try to incorporate both defensive and offensive contribution and to scale everything to the run environment and pitcher usage norms for the era I'm playing in. Then I run a VBA script that estimates development and aging for each player and calculates the projected WAR for the remainder of the player's careers. I end up with two ordered lists - one of the players' current value and one of the players' projected future value. Players in my own organization are highlighted in these two lists. This second list is where I live most of the time when making decisions about trades and contracts - i.e. trade players lower on the list for those higher on the list.

I use this second list for the amateur draft - I just draft the highest remaining player on the list (at least for the first few rounds) regardless of anything else. It's all about accumulating the best talent available that can be later either used or traded.

Then, I have another VBA script that calculates my depth charts and lineups. This is an enormous help in reconfiguring the team after an injury. I always build my teams for flexibility so it's not always obvious what the most effective lineup is - things like: should I put my brilliant defender in center field and have player B at shortstop, or should I put him at shortstop and have player C in center field. And if I put player C in center who now is going to play right? These are not always obvious and my little algorithm just uses a brute force method to find the configuration likely to produce/save the most runs.

I often use this lineup generation to evaluate free agents. I can combine the free agent list and the team list to generate a depth chart and see which free agents would make the team and how the lineup would configure itself. I can compare the run value to the team for each free agent and make decisions based on the cost of the additional runs. Similarly for possible trades - especially at the trade deadline where I'm more concerned with seeing how the trade target would contribute immediately rather than contribute to building for the future.

Then I have another VBA script that I run before spring training. This helps me find prospects that are good candidates for learning or developing new positions. Given the basic fielding ratings and the current positional ratings I can positions to develop - and also factor in the quality of the prospect so I don't give the valuable positional experience to prospects unlikely to ever contribute at the major league level.

I have another set of spreadsheets that I use just to help me get to know the top players in my league. This requires some effort for me since I always play fictional and I don't play out individual games. So I need a way to get to know the players.

Every year I run my depth chart algorithm on the entire league to generate a sort of all-star team. I keep a list of these for each year and I can see how the top players change through time and get to know their names. I do the same thing, but with the algorithm set for +4 years. That allows me to track top prospects.

I also keep track of the leader boards year to year. In the game you can see the current leader boards, but I like to see the evolution of the leader boards, so I keep them in a spreadsheet and highlight changes from year to year. This also helps me get to know the players in my universe.

I also have a thing called "players of renown" where I track all of the players in my league and highlight potential hall-of-famers. I do this by taking their current career stats and doing a projection based on their age of how their final stats might end up. I try to identify potential hall-of-famers early - say while they are in their early to mid-20's. It's fun to see which ones make it and which ones have their promising early careers eventually fizzle.

I also do a complete list of all players both current and retired listed in order of hall-of-fame worthiness. I have my own quirky little algorithm for calculating this but it does seem to work pretty well. For active players I can watch them climb on this list from promising rookie to all-time-great or somewhere in-between.

I also have a spreadsheet with career stats of real-life players. Then for each of my fictional players I run a similarity check to see which real-life player most most comparable. Once again, it's my own quirky algorithm, based on rate stats of various kinds, longevity and overall quality (things like ERA+ and OPS+). It's not perfect, but it I'm pleased with it and it is a huge help for getting a handle on my fictional players

So yes. I have indeed gone overboard with the spreadsheets...

[jparker2112]

id also LOVE a tutorial on what to include and how to really utilize it. As of now, i do not see a reason to do it....teach me as to why there is a need please

[jparker2112]

Originally Posted by Cobby

Spreadsheets are completely integral to how I play OOTP...

I import all of the players into my spreadsheet and use the ratings to produce a projected runs-created or projected WAR for each player assuming that player played full-time. I make my best attempt at this and try to incorporate both defensive and offensive contribution and to scale everything to the run environment and pitcher usage norms for the era I'm playing in. Then I run a VBA script that estimates development and aging for each player and calculates the projected WAR for the remainder of the player's careers. I end up with two ordered lists - one of the players' current value and one of the players' projected future value. Players in my own organization are highlighted in these two lists. This second list is where I live most of the time when making decisions about trades and contracts - i.e. trade players lower on the list for those higher on the list.

I use this second list for the amateur draft - I just draft the highest remaining player on the list (at least for the first few rounds) regardless of anything else. It's all about accumulating the best talent available that can be later either used or traded.

Then, I have another VBA script that calculates my depth charts and lineups. This is an enormous help in reconfiguring the team after an injury. I always build my teams for flexibility so it's not always obvious what the most effective lineup is - things like: should I put my brilliant defender in center field and have player B at shortstop, or should I put him at shortstop and have player C in center field. And if I put player C in center who now is going to play right? These are not always obvious and my little algorithm just uses a brute force method to find the configuration likely to produce/save the most runs.

I often use this lineup generation to evaluate free agents. I can combine the free agent list and the team list to generate a depth chart and see which free agents would make the team and how the lineup would configure itself. I can compare the run value to the team for each free agent and make decisions based on the cost of the additional runs. Similarly for possible trades - especially at the trade deadline where I'm more concerned with seeing how the trade target would contribute immediately rather than contribute to building for the future.

Then I have another VBA script that I run before spring training. This helps me find prospects that are good candidates for learning or developing new positions. Given the basic fielding ratings and the current positional ratings I can positions to develop - and also factor in the quality of the prospect so I don't give the valuable positional experience to prospects unlikely to ever contribute at the major league level.

I have another set of spreadsheets that I use just to help me get to know the top players in my league. This requires some effort for me since I always play fictional and I don't play out individual games. So I need a way to get to know the players.

Every year I run my depth chart algorithm on the entire league to generate a sort of all-star team. I keep a list of these for each year and I can see how the top players change through time and get to know their names. I do the same thing, but with the algorithm set for +4 years. That allows me to track top prospects.

I also keep track of the leader boards year to year. In the game you can see the current leader boards, but I like to see the evolution of the leader boards, so I keep them in a spreadsheet and highlight changes from year to year. This also helps me get to know the players in my universe.

I also have a thing called "players of renown" where I track all of the players in my league and highlight potential hall-of-famers. I do this by taking their current career stats and doing a projection based on their age of how their final stats might end up. I try to identify potential hall-of-famers early - say while they are in their early to mid-20's. It's fun to see which ones make it and which ones have their promising early careers eventually fizzle.

I also do a complete list of all players both current and retired listed in order of hall-of-fame worthiness. I have my own quirky little algorithm for calculating this but it does seem to work pretty well. For active players I can watch them climb on this list from promising rookie to all-time-great or somewhere in-between.

I also have a spreadsheet with career stats of real-life players. Then for each of my fictional players I run a similarity check to see which real-life player most most comparable. Once again, it's my own quirky algorithm, based on rate stats of various kinds, longevity and overall quality (things like ERA+ and OPS+). It's not perfect, but it I'm pleased with it and it is a huge help for getting a handle on my fictional players

So yes. I have indeed gone overboard with the spreadsheets...

Hol-lee crap! i love the idea of all of that but havent a clue how to do it...and what is a VBA? im excel dumb and technology slow...so speak slow and use simple word if you dont mind . haha!

Do you happen to have a blank..uh...report? at the ready to possibly send to us non tech savvy dudes?

[Cobby]

Oh, and I forgot to mention:


I finally learned how to keep track of my fictional players, but I was having trouble getting a feeling for the teams.

So I started a spreadsheet to keep track of Bill James' "dynasty points" (google it) for each franchise. Then I basically negated everything in his method for calculating dynasty points to come up with my own measure that I call "futility points".

I then subtract futility points from dynasty points. That gives me a measure I call "franchise success" which I then plot over time.

It helps me keep track of the teams in my universe - which ones are doing well overall, which ones are the perennial losers and which ones always seem to be in the middle of the pack. It also helps me decide which team to next take control of and try to build up when it comes time to switch. And it's also interesting to see how long it takes for the AI to ruin one of my former teams...

[Cobby]

Originally Posted by jparker2112

Hol-lee crap! i love the idea of all of that but havent a clue how to do it...and what is a VBA? im excel dumb and technology slow...so speak slow and use simple word if you dont mind . haha!

Do you happen to have a blank..uh...report? at the ready to possibly send to us non tech savvy dudes?

VBA stands for Visual Basic for Applications. In this case it's just a way to create a program for manipulating the data in a spreadsheet.

I can post screenshots from the spreadsheets if you are interested (let me know), but there are a number of problems in making the spreadsheet itself available:

First, it's about the most opposite of user friendly as you can get, so it would take quite a learning curve for anyone else to use.

Also, it requires a high end computer - I once tried to run it on a mid-range laptop while my main laptop was being repaired and found it to be completely unusable. The spreadsheet took forever to calculate and the VBA code took seemingly more than forever to run on that laptop.

And finally, if you have a large league - or even just a modern MLB setup with full minors - then you get the dreaded page selectors because the number of players in your universe will be greater than 4095. When you reach that point, exporting the data to the spreadsheet becomes difficult (or impossible?) (I periodically complain to the developers about this and Markus has told me that they will fix this at some point, but it appears to be a very low priority for them) (although I haven't tried the recent patch yet - hope springs eternal). But in the meantime this will only work for smaller leagues where the number of players stays under 4095.

[Hageneezz]

Originally Posted by Gruber_Tagged_Him

Pretty sure they count my keystrokes at work... Now if they are all in Excel, seems I win....

You work at ze Gestapo?

Verstuurd vanaf mijn WAS-LX1A met Tapatalk

[Tzar]

Originally Posted by Cobby

VBA stands for Visual Basic for Applications. In this case it's just a way to create a program for manipulating the data in a spreadsheet.

I can post screenshots from the spreadsheets if you are interested (let me know), but there are a number of problems in making the spreadsheet itself available:

First, it's about the most opposite of user friendly as you can get, so it would take quite a learning curve for anyone else to use.

Also, it requires a high end computer - I once tried to run it on a mid-range laptop while my main laptop was being repaired and found it to be completely unusable. The spreadsheet took forever to calculate and the VBA code took seemingly more than forever to run on that laptop.

And finally, if you have a large league - or even just a modern MLB setup with full minors - then you get the dreaded page selectors because the number of players in your universe will be greater than 4095. When you reach that point, exporting the data to the spreadsheet becomes difficult (or impossible?) (I periodically complain to the developers about this and Markus has told me that they will fix this at some point, but it appears to be a very low priority for them) (although I haven't tried the recent patch yet - hope springs eternal). But in the meantime this will only work for smaller leagues where the number of players stays under 4095.

Have you thought about using Power BI to do this. I use Power BI at work. It's from Microsoft and as such integrates and easily read Excel, Access or SQL files that OOTP can export. And best of all, it's FREE! Well, technically it's not entirely free if you want to start publishing online a lot of reports but for personal use the desktop version which is free does the job.

Being a dedicated data intelligence tool, I think it's easier and more powerful to use than the limitations Excel and VBA have for manipulating data.

I am going to fiddle with it and see what I can get. But your post above gives great suggestions of analysis points.

[jparker2112]

Originally Posted by Cobby

VBA stands for Visual Basic for Applications. In this case it's just a way to create a program for manipulating the data in a spreadsheet.

I can post screenshots from the spreadsheets if you are interested (let me know), but there are a number of problems in making the spreadsheet itself available:

First, it's about the most opposite of user friendly as you can get, so it would take quite a learning curve for anyone else to use.

Also, it requires a high end computer - I once tried to run it on a mid-range laptop while my main laptop was being repaired and found it to be completely unusable. The spreadsheet took forever to calculate and the VBA code took seemingly more than forever to run on that laptop.

And finally, if you have a large league - or even just a modern MLB setup with full minors - then you get the dreaded page selectors because the number of players in your universe will be greater than 4095. When you reach that point, exporting the data to the spreadsheet becomes difficult (or impossible?) (I periodically complain to the developers about this and Markus has told me that they will fix this at some point, but it appears to be a very low priority for them) (although I haven't tried the recent patch yet - hope springs eternal). But in the meantime this will only work for smaller leagues where the number of players stays under 4095.

Gotcha....thanks for the explanation and i think I will pass on destroying my laptop with a spreadsheet i need to go to MIT to understand . I like the theory and all, but i spend enough time as it is on OOTP so i think id need a divorce attorney before spreadsheet help if i unloaded more time to this. thanks a bunch for the info!!!!

[stevem810]

Definitely admire those that can manage the data so effectively and efficiently. No doubt there is an edge to be gained. I will stay 'old school', looking at the data, and making my projections sans mathematics. Good exercise for my brain.

[NoOne]

i use them to organize the creation of a fictional league. re-structure, adjust mil systems etc etc..

i also use them to calculate some long-term league total modifiers.

as far as evaluation, i rely on in-game stuff. a lot of the fancy stuff isn't so good once you limit the sample to 1 player with a limited career. by the time it's reliable, the guy is likely in decline and that knowledge no longer applies to him very well. my eye is just as good, less work and more reactive/sensitive to context.

i'd suggest focusing on real rating and not overall ratings. Even contact is actually 3 ratings factored together. Knowing how 2 quite different hitters can result int eh same contact value is important to understand and easily highlighted with a spreadsheet to hasten that learning curve.

if i wanted to mine the data, i'd be looking for obvious tiers or steps where the players are noticeably better as opposed to a steady incline. just with my eye, i'd wager that sort of context exists with power and 'contact' -- of which power is a portion of, too, but in addition to that with babip. i think avoid k's is more of a straight line, which is the 3rd component of contact.
.
*can't see the babip rating, but it can be deduced by the other 2 variables and the resulting contact rating.

[Cobby]

Originally Posted by Tzar

Have you thought about using Power BI to do this. I use Power BI at work. It's from Microsoft and as such integrates and easily read Excel, Access or SQL files that OOTP can export. And best of all, it's FREE! Well, technically it's not entirely free if you want to start publishing online a lot of reports but for personal use the desktop version which is free does the job.

Being a dedicated data intelligence tool, I think it's easier and more powerful to use than the limitations Excel and VBA have for manipulating data.

I am going to fiddle with it and see what I can get. But your post above gives great suggestions of analysis points.

I've never used it, but it seems like it might be fun to play around with...