gschwendt
11-05-2012, 01:58 PM
I've put together a spreadsheet on Google Docs that I use to track my roster (particularly for the next season) and my recruiting.
This should be readable for everyone, but let me know if it's not.
https://docs.google.com/spreadsheet/ccc?key=0AvtJqcTghgb0dGVJNWVma0dKZjY4cUxPbk9mU0JrO Wc
The latest revisions are mostly only on S4-Roster, S4-Depth, and S3-Recruits. This was all used during the 3rd season so it allowed me to plan for the next season to determine where I would have holes not only next year but for the future. It also lets me plan out my redshirts (with red text), player cuts (with a strikethrough), and position changes. We're finishing up Season 3 now so since it's more or less finalized, I wanted to share it with everyone to get any kind of feedback you guys wanted to share, even if just to say I have too much time on my hands.
On the roster tab (S4-Roster), The first 5 columns are self-explanatory, Name, Position, Year, OVR, and Tendency. The PosYr column is a concatenation of Pos & Year in order to build the roster breakdown that you see to the right (I'll get to that in a minute). The PosSort is so that I can sort it by a normal roster (QB, HB, etc.) and the YrSort is essentially the same, just sorting by SrRS, Sr, JrRS, etc. PosYr, PosSort, & YrSort are calculated columns, thus they are greyed; PosYr as explained is a concatenation, whereas PosSort and YrSort are lookups from the Resources tab (last one). The ones that have their Year highlighted means that they were a new recruit for that season (Freshman & JUCOs).
As for the Roster breakdown on the right, it's actually a nice little calculated section I put together that tracks the yearly breakdown for each position as well as the two highest rated OVR players at each position. The positional breakdown is actually pulling unique values from the Pos column on the roster so if you don't care to track FBs, it won't list them or if you only care to track WRs (not arrangement specific), you can list them all as WRs and see the breakdown that way.
On the depth tab (S4-Depth), this is just a visual break down of a 2-deep depth chart. The Positions are manually entered as are the Names but based on the name, it will do a lookup of the Year and OVR. This page will help you determine how "old" your team is based on contributors. If you have a really senior-heavy team, then this will tell you that you need to make sure you're ready for the future. It also highlights those Seniors as well as Freshman to point out those players.
On the recruits tab (S3-Recruits), this is a fairly straight-forward recruits tracker. The first column is the order they appear (the 99s are just a buffer), the OVR is their post-scouting OVR, their Scout column is a percentage of the next two columns, the first is how many attributes you have scouted, the second is how many attributes they have (it's a lookup from Resources). Next is their original OVR, so you can see how they finished versus where they started using the Diff column (simple subtraction from OVR), and then finally are the remainder of the manual columns, Prestige, Position (default), possible Position Changes (particularly for ATHs & Right vs Left positions), Interest level, when they visit, whether you've offered a Scholarship, and then Competition (particularly fellow User teams). The final is just a Notes column, sometimes I'll highlight most important traits for an ATH or what traits I haven't scouted, or whatever.
You'll also see previous years which will also give you an idea of how this thing started and how it got to where it is. As well is a S5 set for the next season we're getting ready to start.
Finally, there is the Training Results and the Resources tabs. The Resources tab is just simply used for other pages to lookup information such as PosSort, number of ratings to scout for each position, and so on.
On the Training Results, it's also a little creation I put together for me to be able to see how each player I've had over the years has progressed. The first four columns are pulled from the Resources page, which is just a list of every seasons roster... the lookup will only pull unique values so there will only be one record for each player. The next columns is a breakdown of each player for that year (2013, 2014, 2015, 2016, etc.) including their year and their OVR for that year. Then finally is a Progression column which shows how much they've grown over the years (min - max) and their MaxOVR which is the highest point they reached. It's not a very friendly sorting on the default spreadsheet page but if you look at it in List View you can sort it by year, position, etc. quite easily.
You can make a copy to your own Google Docs by doing File > Make a Copy. Once you have it in your own, you'll have to update named ranges, calculations, etc. but in about 10 minutes worth of work you should be able to have your own version up & running. For the roster, I just do a download from the OD site, then concatenate the player names into one column.
Hopefully this will end up helping someone else out and will be valuable to someone else. Obviously if you share your own version with anyone else I would appreciate some kind of credit as I have spent way too much time working on this.
This should be readable for everyone, but let me know if it's not.
https://docs.google.com/spreadsheet/ccc?key=0AvtJqcTghgb0dGVJNWVma0dKZjY4cUxPbk9mU0JrO Wc
The latest revisions are mostly only on S4-Roster, S4-Depth, and S3-Recruits. This was all used during the 3rd season so it allowed me to plan for the next season to determine where I would have holes not only next year but for the future. It also lets me plan out my redshirts (with red text), player cuts (with a strikethrough), and position changes. We're finishing up Season 3 now so since it's more or less finalized, I wanted to share it with everyone to get any kind of feedback you guys wanted to share, even if just to say I have too much time on my hands.
On the roster tab (S4-Roster), The first 5 columns are self-explanatory, Name, Position, Year, OVR, and Tendency. The PosYr column is a concatenation of Pos & Year in order to build the roster breakdown that you see to the right (I'll get to that in a minute). The PosSort is so that I can sort it by a normal roster (QB, HB, etc.) and the YrSort is essentially the same, just sorting by SrRS, Sr, JrRS, etc. PosYr, PosSort, & YrSort are calculated columns, thus they are greyed; PosYr as explained is a concatenation, whereas PosSort and YrSort are lookups from the Resources tab (last one). The ones that have their Year highlighted means that they were a new recruit for that season (Freshman & JUCOs).
As for the Roster breakdown on the right, it's actually a nice little calculated section I put together that tracks the yearly breakdown for each position as well as the two highest rated OVR players at each position. The positional breakdown is actually pulling unique values from the Pos column on the roster so if you don't care to track FBs, it won't list them or if you only care to track WRs (not arrangement specific), you can list them all as WRs and see the breakdown that way.
On the depth tab (S4-Depth), this is just a visual break down of a 2-deep depth chart. The Positions are manually entered as are the Names but based on the name, it will do a lookup of the Year and OVR. This page will help you determine how "old" your team is based on contributors. If you have a really senior-heavy team, then this will tell you that you need to make sure you're ready for the future. It also highlights those Seniors as well as Freshman to point out those players.
On the recruits tab (S3-Recruits), this is a fairly straight-forward recruits tracker. The first column is the order they appear (the 99s are just a buffer), the OVR is their post-scouting OVR, their Scout column is a percentage of the next two columns, the first is how many attributes you have scouted, the second is how many attributes they have (it's a lookup from Resources). Next is their original OVR, so you can see how they finished versus where they started using the Diff column (simple subtraction from OVR), and then finally are the remainder of the manual columns, Prestige, Position (default), possible Position Changes (particularly for ATHs & Right vs Left positions), Interest level, when they visit, whether you've offered a Scholarship, and then Competition (particularly fellow User teams). The final is just a Notes column, sometimes I'll highlight most important traits for an ATH or what traits I haven't scouted, or whatever.
You'll also see previous years which will also give you an idea of how this thing started and how it got to where it is. As well is a S5 set for the next season we're getting ready to start.
Finally, there is the Training Results and the Resources tabs. The Resources tab is just simply used for other pages to lookup information such as PosSort, number of ratings to scout for each position, and so on.
On the Training Results, it's also a little creation I put together for me to be able to see how each player I've had over the years has progressed. The first four columns are pulled from the Resources page, which is just a list of every seasons roster... the lookup will only pull unique values so there will only be one record for each player. The next columns is a breakdown of each player for that year (2013, 2014, 2015, 2016, etc.) including their year and their OVR for that year. Then finally is a Progression column which shows how much they've grown over the years (min - max) and their MaxOVR which is the highest point they reached. It's not a very friendly sorting on the default spreadsheet page but if you look at it in List View you can sort it by year, position, etc. quite easily.
You can make a copy to your own Google Docs by doing File > Make a Copy. Once you have it in your own, you'll have to update named ranges, calculations, etc. but in about 10 minutes worth of work you should be able to have your own version up & running. For the roster, I just do a download from the OD site, then concatenate the player names into one column.
Hopefully this will end up helping someone else out and will be valuable to someone else. Obviously if you share your own version with anyone else I would appreciate some kind of credit as I have spent way too much time working on this.