Jump to content

TSB Roster Spreadsheet


Bodom

Recommended Posts

  • Replies 56
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

Actually haven't had the chance to work on it the past couple of days. Work and family stuff taking up all my time...imagine that. Ideally, I'd like to get a copy of the playbooks from each rom, but I might just go with the original for now. Hopefully finally post this thing over the weekend.

Link to comment
Share on other sites

Just sent over some stuff to Bodom... here's what I did at more of a "top-level" aspect:

- Automated Sim Stats for QBs via VLOOKUP - this can be carried over to other positions as well (I did only QBs) and there may be ways to factor in multiple stats to the sim ratings

- Used VLOOKUP to determine whether QBs have WHITE, BLACK, or SIHLOUETTE player portraits. This can also be carried over to other positions. Seeing as Excel is a bitch with images and you'd need a plugin installed to even support images in cells it isn't practical. At least this should help with major errors that you can't determine via looking at the player face hex code. This can be applied for all positions as well. It also can be broken down even further (i.e. Black afro, white moustache, black afro and goautee, etc.) as all the descriptions are linked to specific cells in Excel. Someone would just need to go through the pics and write descriptions about each one in the appropriate cell - Grunt work in case anyone here wants to volunteer :? .

- Tweaked the playbook stuff. Created the formatting to be read by TSBtool for playbooks (Bills only) and divided run/pass plays. Awaiting bodom's feedback to see if this is a good direction to go in as we could use formulas to determine the number of playaction plays a team is running, how many routes are run on average for each pass play, how many plays are run by QB1, RB1, RB2, etc. Lots of stuff.

Link to comment
Share on other sites

Just sent over some stuff to Bodom... here's what I did at more of a "top-level" aspect:

- Automated Sim Stats for QBs via VLOOKUP - this can be carried over to other positions as well (I did only QBs) and there may be ways to factor in multiple stats to the sim ratings.

I like this idea. At the very least, this can be done for Kicker, Punters and most of the offensive position. Like you said, Team sim value has a bit to do with this as well. I wonder if we can factor that in, too? This will save A LOT of time. I may have to take a further look into this, sim a few seasons, and see what attributes contribute to high stats for each position...

- Used VLOOKUP to determine whether QBs have WHITE, BLACK, or SIHLOUETTE player portraits. This can also be carried over to other positions. Seeing as Excel is a bitch with images and you'd need a plugin installed to even support images in cells it isn't practical. At least this should help with major errors that you can't determine via looking at the player face hex code. This can be applied for all positions as well. It also can be broken down even further (i.e. Black afro, white moustache, black afro and goautee, etc.) as all the descriptions are linked to specific cells in Excel. Someone would just need to go through the pics and write descriptions about each one in the appropriate cell - Grunt work in case anyone here wants to volunteer :? .

This is definitely useful. Although I'm hoping to take an extensive look, after the draft, at each player so there won't be much need to go and edit anyone.

- Tweaked the playbook stuff. Created the formatting to be read by TSBtool for playbooks (Bills only) and divided run/pass plays. Awaiting bodom's feedback to see if this is a good direction to go in as we could use formulas to determine the number of playaction plays a team is running, how many routes are run on average for each pass play, how many plays are run by QB1, RB1, RB2, etc. Lots of stuff.

Still the trickiest aspect of the whole thing as many of the newer roms have their own playbooks. I don't think many people use the original playbook anymore. Basically, people would have to go into this tab, insert their playbook, then put in all the necessary info and I doubt anyone is willing to do that....

Great ideas and feedback, Jeid! Will get back to you over email and iron all this out!

Link to comment
Share on other sites

  • 2 weeks later...
  • 2 weeks later...
BTW, Bodom, did you see these two spreadsheets that Jstout and Buck posted? They might help add to the spreadsheet you're working on.

http://www.tecmobowl.org/forum//viewtopic.php?f=5&t=11924

I haven't, but that's some good stuff. With permission, I will add these into the spreadsheet and give full credit. I just need about 2 hours of uninterrupted time to sit down and finish this up, unfortunately I never get that anymore.

Link to comment
Share on other sites

I have attached a *rough draft* of this spreadsheet in case anyone is editing for the upcoming season and to also maybe get some feedback on what else could be added/edited to make this better. There's still a few things I need to add in that I haven't yet that Jeid sent over regarding playbooks. There was also a few things I saw on sim stats that might be a better fit. There also certain "sorting instructions" that need to be followed when using this that are kind of half assed written on each page for now.

Anyways, here's the draft copy, feedback is welcome.

Side note: I'm trying to figure out a way for someone to post their TSBTool file into a spreadsheet and have the data get split out by cell. If anyone knows how to do this, let me know....

Side note2: Rosters are from an older version.

[credit]

Link to comment
Share on other sites

I'm really digging on it, dude. Other than some of those things that you haven't spoke about but wish to implement, I'm all for this spreadsheet. It's awesome. I'll probably give it a little visual love when you're done with it, mmhmmm, and send it over your way if you're into that sort of thing. ;-):lol:

Link to comment
Share on other sites

I'm really digging on it, dude. Other than some of those things that you haven't spoke about but wish to implement, I'm all for this spreadsheet. It's awesome. I'll probably give it a little visual love when you're done with it, mmhmmm, and send it over your way if you're into that sort of thing. ;-):lol:

::thumbsup::

Link to comment
Share on other sites

bodom, pretty good spreadsheet, if not a wee-bit of a cluster-fuck. is that as elegant as can be? I will look closer.

I recommend taking a look at the interpolating 'defensive sim' example page I posted in the above link.

it works very simple -

1. input individual player SACKS

2. input individual player INTS

INT and SACK 'SIM values' are automatically calculated and scaled 0-255.

I might take a look at embedding it into your spreadsheet later on tonight. First I gotta figure out what the hell is going on in your spreadsheet and what links to what.

Link to comment
Share on other sites

Side note: I'm trying to figure out a way for someone to post their TSBTool file into a spreadsheet and have the data get split out by cell. If anyone knows how to do this, let me know....

I've done something like this before but it can get very messy very fast due to the limited capabilities of excel. I will send you what I have this weekend IF I can find it (was years ago), but essentially this is what you do:

1.) make a macro to do comma delimited text to columns

2.) add to the macro code to find/replace/delete all unneeded characters (i.e. # for jersey number, [] for sim data)

3.) Link the macro to a button and place the button on the desired spreadsheet - you can do all this manually but it's quicker to make a macro in the long run.

4.) Take the data and use "=CellID" functions in a new spreadsheet to clean up and style the raw data from the spreadsheet you imported it to like the QB, RB, etc tabs you have now - name these "IQB, IRB, etc"

5.) This step seems pointless but it's important - copy the imported data spreadsheets and make new spreadsheets named "EQB, ERB, etc", but change all the "=CellID" formulas to read from the IQB, IRB, etc data sheets and not the raw data sheet

6.) create a macro which basically copies whatever is on the IQB, IRB, etc data sheets and place a button for it on the EQB, ERB, etc data sheets. Name it "bring back original imported rosters" or something similar. You can combine it with the first macro if you like (I kept them separate in case I was working on something and didn't want it to be overwritten)

7.) The exported data sheets are the data you can edit but beware - anytime you edit something it removes the "=CellID" formula linking it to the corresponding imported data sheet. But that's why you made all those macros... once you make all the edits you desire and grab the data in TSBtool format, you just save if you want and close the spreadsheet.

8.) When you open the spreadsheet again the formulas you deleted in exported data will be removed, but hit the macro and "voila!" - they're all back. So just import again and you're good to go. If you want to skip macros just go the first cell in the exported data sheet and copy the "=CellID" from the corresponding imported data sheet and use the fill function, but this can be tedious if you have stuff broken down by position.

I hope this helped!! All of these macros are easy to make and you can find examples on google. Please let me know if you have any questions either here or via e-mail.

Great job Bodom, looking forward to using this for my rom this year :lol:

Link to comment
Share on other sites

bodom, pretty good spreadsheet, if not a wee-bit of a cluster-fuck. is that as elegant as can be? I will look closer.

I recommend taking a look at the interpolating 'defensive sim' example page I posted in the above link.

it works very simple -

1. input individual player SACKS

2. input individual player INTS

INT and SACK 'SIM values' are automatically calculated and scaled 0-255.

I might take a look at embedding it into your spreadsheet later on tonight. First I gotta figure out what the hell is going on in your spreadsheet and what links to what.

This would be an awesome edition... who uses the interpolation in excel anyways? :lol: do you think it could be used for offensive sim as well? maybe a formula could be made for more complex stats like PS, MS, and RP and then the value from that is used? I honestly haven't used it since college so I'd need a major refresher... help Buck!

Link to comment
Share on other sites

I currently have sheets for all sim stats. I use Buck's defensive sim calc for defense. I base Passing off of PC, Rushing off MS. Then you have the three Receiving numbers. The first one is receptions so I base it off of actual receptions - highest player gets 15 and the rest scales down. The second number is yards. Since 1500 is usally around the max I just divide yards by 100 and use that. The third number is targets, with the 5 starters needing to total 32. I made a sheet similar to the defensive calc for this. For backups I plug in their TA to the player they would replace. For kickers I use KA with slight tweaks for AKB, punters is KA.

For team sim stats I made a sheet that uses PFR's Simple Rating System (based on point differential and SOS), then tweaked based on number of Wins and playoff performance.

I can post the sheets if anyone is interested.

Link to comment
Share on other sites

I currently have sheets for all sim stats. I use Buck's defensive sim calc for defense. I base Passing off of PC, Rushing off MS. Then you have the three Receiving numbers. The first one is receptions so I base it off of actual receptions - highest player gets 15 and the rest scales down. The second number is yards. Since 1500 is usally around the max I just divide yards by 100 and use that. The third number is targets, with the 5 starters needing to total 32. I made a sheet similar to the defensive calc for this. For backups I plug in their TA to the player they would replace. For kickers I use KA with slight tweaks for AKB, punters is KA.

For team sim stats I made a sheet that uses PFR's Simple Rating System (based on point differential and SOS), then tweaked based on number of Wins and playoff performance.

I can post the sheets if anyone is interested.

This sounds pretty efficient - let's add it in!

Link to comment
Share on other sites

That sounds like a pretty awesome addition Bo. Bodom, were you guys able to get together and get this in your excel workbook? I'd like to break ground on my rom fairly soon using this awesome device :roll:

Not yet. The last version I know of is what I posted a few days ago. If anyone was able to make any additions or had any other ideas, post it up!!

Link to comment
Share on other sites

Okay, here's my sim rating sheets. Here's the team sim rating sheet broken down by column: first column is what I call Win Rating. For non-playoff teams this is how many games they actually won. Luckily no non-playoff team had more than 9 wins. As it turns out, the four teams who lost in the wildcard round had 10 or 11 wins, so this is their WRat. Cards, Ravens & Cowboys won one playoff game so they get 12. Chargers had 13 wins and a bye so they get 13. Vikings/Jets 14, Colts 15, Saints 16. Next two columns are Offensive Simple Rating System (SRS) and Defensive SRS from PFR. Raw O and Raw D are the SRS scores put into a 1-15 scale. Then for the non-playoff teams, if the Total Raw score was double their wins or less, I made their sim total double their wins. If it was more, it's double +1. Ex: Rams won 1 game, Raw O was 1, Raw D was 4, so I made their sim O 1 and sim D 2. When I got up to 10 wins (the playoff teams) doubling the number of wins became untenable, so I just switched to adding 10. Pats got +11 because their SRS was so high.

Second page of that file is pass sim and rush sim. It's just based on PC and MS, and pretty self-evident.

The second file is all receiving ratings. The last column is rec sim, and is based on receptions. The second rec sim rating determines yards, so like i said, i just divided yards by 100 and rounded to the nearest number. the second sheet in the file is the last rec sim rating, which determines targets. This works like the defensive ratings, except instead of 255 the total of the 5 starters should equal 32 (from what i understand. I know in the hex it's not equal intervals for whatever reason, but hopefully overlooking this doesn't skew the stats too much). Anyway, it's just a sheet that makes the total add up to 32, it's pretty easy to figure out. Due to rounding, sometimes it is 31 or 33, so you just have to check each team after you plug the numbers in (or make a sheet like buck's defensive calc, i'm not that good with excel).

Link to comment
Share on other sites

I think that trying to SIM-rate all NFL players together is tough.

Maybe better to SIM-rate individuals relative to their team.

[attachment=0]scaling example.JPG[/attachment]

but, this is just to show how you can automatically scale things in excel.

basically, it's just making a percentage and multiplying by the max number possible, this is not "interpolation"....

the column "insert catches" could be any number you want to use to determine a SIM stat (like some previous calculation or total or whatever, here it's "catches")

the code line in cell D2 just shows what's actually written in cell C2.

the code beneath 301 and 32 just shows what's written in cell B13 and C13.

(BO does a version of this in his REC spreadsheet.)

Link to comment
Share on other sites

I think that trying to SIM-rate all NFL players together is tough.

Maybe better to SIM-rate individuals relative to their team.

[attachment=0]scaling example.JPG[/attachment]

but, this is just to show how you can automatically scale things in excel.

basically, it's just making a percentage and multiplying by the max number possible, this is not "interpolation"....

the column "insert catches" could be any number you want to use to determine a SIM stat (like some previous calculation or total or whatever, here it's "catches")

the code line in cell D2 just shows what's actually written in cell C2.

the code beneath 301 and 32 just shows what's written in cell B13 and C13.

(BO does a version of this in his REC spreadsheet.)

Hey, I just realized that your formula does the exact same thing as mine. Due to rounding, the total is not always what you want it to be. I thought your formula somehow adjusted for that and that's why you did it that way. but it doesn't. For example, if you want your total to be 45, and you end up with nine at 4.9 and one at 0.9, when those numbers are rounded you get nine at 5 and one at 1, which totals 46. Is there any way to automatically correct for this?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...