Sarnoff

My method to create my ADP_USER file

6 posts in this topic

I've been trying to figure out how to best use the ADP_USER option and have been tweaking my system for the last few years. I think I've hit upon a method to better estimate my league's tendencies, this year there were several patches during the draft where everything followed exactly to plan. Only occasionally would I have to search for a different player in the pool than DD thought my opponents would take, and most of the time it was within a few projected points (within the same tier), so it didn't matter much anyway. I'm in the 9th year of a 12-team league and have 8 years of old draft data to go through on many of the teams. We've turned over a few owners and my adjustments to that system are noted below. Our current rosters have 9 starters and 7 bench positions, we start 1 QB, 2 RB, 2 WR, 1 RB/WR Flex (no TE in the flex), 1 TE, 1 K, 1 DST. Basically, I use an Excel spreadsheet to track selections by owners, then I work out each owner's average tendencies. I feed that into ADP_USER to come up with a very specific list. I thought I'd share my method for those that may wish to try it, or for anyone who has suggestions or input. It may be data-entry intensive to start, but once it gets going, each year you only have to update with the previous season's draft. Each owner has a separate section that looks like this:

Yr  Name  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 1611  Bob   RB RB QB WR RB WR RB DT WR PK QB WR RB TE RB WR10  Bob   RB WR QB RB RB TE RB WR WR QB WR WR PK RB WR DT09  Bob   RB WR QB TE WR RB RB DT QB PK RB WR RB RB WR RB....etc

Which lists years and what position was drafted in each round of the draft. I do this for every owner. Next I convert this into a grid where each position is broken out by number of selections, so I know where each guy likes to take his QB1, his QB2, etc.:

Yr  Name  QB1 QB2 QB3 QB4 RB1 RB2 RB3 RB4 RB5 RB6 WR1 WR2 WR3 WR4 WR5 WR6 TE1 TE2 PK1 PK2 DT1 DT211  Bob   3   11  *   **  1   2   5   7   13  15  4   6   9   12  16  *   14  *   10  *   8   *10  Bob   3   10  *   **  1   4   5   7   14  *   2   8   9   11  12  15  6   *   13  *   16  *09  Bob   3   9   *   **  1   6   7   11  13  14  2   5   12  15  *   **  4   *   10  *   8   *...etc

Which reads as "In 2011, Bob took his QB1 in round 3, his QB2 in round 11. He's never taken 3 QBs. He took RBs in 1 & 2, then again in 5 and in 7." And so on. You'll notice that in some years Bob didn't always take the same number of positions, so I've left those with asterisks for now, but those will change soon. Next, I weight the years. I like to use all the data but weight recent years with higher importance. I'll give full value (100%) to the two most recent drafts, half-value to the next two, and descend value from there. Let's say these are the only three years I have data for, I'll give full consideration to 2011 & 2010 and half as much to 2009's values. A little math tells me my weight multipliers are 0.4 each for 2011 and 2010, and 0.2 for 2009 (you'll know you've got it right when the total is 1.0). What I intend to do is multiply the rounds by the weighting, and then get a total. But those missing picks in some years will screw me up a bit, so I add 5 rounds each, starting with the last draft round +5, for "missing" selections. So each of the single asterisks will get a 21 (16 round draft +5), and if Bob didn't draft two players, the second missing one is given 26, the third 31, etc. My data now looks like this:

Yr  Name  QB1 QB2 QB3 QB4 RB1 RB2 RB3 RB4 RB5 RB6 WR1 WR2 WR3 WR4 WR5 WR6 TE1 TE2 PK1 PK2 DT1 DT211  Bob   3   11  21  26  1   2   5   7   13  15  4   6   9   12  16  21  14  21  10  21  8   2110  Bob   3   10  21  26  1   4   5   7   14  21  2   8   9   11  12  15  6   21  13  21  16  2109  Bob   3   9   21  26  1   6   7   11  13  14  2   5   12  15  21  26  4   21  10  21  8   21

Now I can multiply each value by the appropriate multiplier, and put those numbers at the bottom of each column. So, for example, at the bottom of the QB2 column I'd put the sum of (11*.4)+(10*.4)+(9*.2), which is 10.2. That means, after weighting, Bob selects his QB2 in round 10.2 (not exactly the same as drafting at pick 10.02, but rather, 2 tenths of the way through round 10. It doesn't matter that this isn't an integer number). For DT1, it'd be (8*.4)+(16*.4)+(8*.2)=11.2. So, on average, we'd say Bob drafts his first defense at round 11.2. His DT2 would average out to round 21, which is beyond the scope of the draft (16 rounds), so we'd see he rarely, if ever, drafts one (I'm keeping the DT2 column just because other owners might draft DT2s, or Bob may have in the past outside the scope of the example here, and keeping the column for future reference makes it easier). Taking a look at WR6, we see Bob only drafted one once, in 2010. That's where our +5 adjustments come in, we'd weight average those picks out to (21*.4)+(15*.4)+(26*.2)=19.6, which is also outside the scope of our 16 rounds. So usually he doesn't pick one here, either. Here's the grid with an "average" at the bottom of each column of where each position is drafted:

Yr  Name  QB1 QB2 QB3 QB4 RB1 RB2 RB3 RB4 RB5 RB6  WR1 WR2 WR3 WR4  WR5  WR6  TE1 TE2 PK1  PK2 DT1  DT211  Bob   3   11  21  26  1   2   5   7   13  15   4   6   9   12   16   21   14  21  10   21  8    2110  Bob   3   10  21  26  1   4   5   7   14  21   2   8   9   11   12   15   6   21  13   21  16   2109  Bob   3   9   21  26  1   6   7   11  13  14   2   5   12  15   21   26   4   21  10   21  8    21AVERAGE   3  10.2 21  26  1  3.6 5.4 7.8 13.4 17.2 2.8 6.6 9.6 12.2 15.4 19.6 8.8 21  11.2 21  11.2 21

Obviously, this is very easy to do in Excel. You figure out the formula once, then copy it to all the other cells. IF you have a team with a shorter history, you have to change the weightings, otherwise, this can work for all your teams. NOW, all I have to do is sort the averages by position, another task done easily with Excel. Just by eyeballing we can see that Bob will take an RB first, and he's more likely to take a WR in the second (average spot 2.8, he's gone WR in the 4th, 2nd, and 2nd in the last 3 years) than go QB (always in the third, average spot 3.0). Sorting by averages from lowest to highest, we get this pattern: Bob's Average: RB1 WR1 QB1 RB2 RB3 WR2 RB4 TE1 WR3 QB2 PK1 DT1 WR4 RB5 WR5 RB6 (WR6 QB3 QB4). Those last 3 spots are 17th, 18th, and 19th, so we discard those since they are outside of the scope of the 16 round draft. We just want the first 16 positions. Note RB6 falls into the data here, even though it's average is round 17.2, since it's still more likely Bob will take a 6th RB at the end of the draft than anything else. Now, after doing this for all the other teams in the draft, you'll get an average for each person. INCLUDE YOURSELF when doing this, your draft tendencies are just as important as everyone else's to have. Then, take the owners and put them in DRAFT ORDER on a new excel sheet. If Bob is drafting second, list him second: ALICE BOB RB1 WR1 QB1 RB2 RB3 WR2 RB4 TE1 WR3 QB2 PK1 DT1 WR4 RB5 WR5 RB6 CARL DAVE ...etc and fill in the rest of the owner's tendencies as well. For simplicity, strip out the numerals and your columns in Excel might look like this:

ALICE  RB RB WR QB TE WR RB RB QB DT PK WR QB WR WR RBBOB    RB WR QB RB RB WR RB TE WR QB PK DT WR RB WR RBCARL   RB QB RB WR TE RB QB PK RB WR WR DT QB WR RB RBDAVE   RB RB WR RB QB WR WR WR TE PK DT WR WR RB RB WR..etc

Now, just copy DOWN each column and paste into a new Excel file, and you'll have your ADP_USER file. If your draft snakes, create a "mirror image" for the even number rounds that lists the same info in reverse order (ending with Dave, Carl Bob, Alice). Your first 4 down would be: RB RB RB RB ...and coming back up, at 2.09-2.12, you'd have RB QB WR RB ...and reading back down for the third round, you'd have in cell A24-A27: WR QB RB WR Save this as a .csv file and you're done! It sounds like a lot of work, but it's very easy to keep going after setting it up just once. The more you know about Excel, the easier it is to automate many of the tasks. If your league uses keepers, ADD THOSE IN! Let's say Bob kept a RB, WR, and a Tight End. Very unlike him! But you have to account for that in your ADP_USER file, so just slide TE1 into position and refigure his tendencies: RB WR TE QB RB RB WR RB WR QB PK DT WR RB WR RB. Play around with weighting past years as you feel is appropriate. Perhaps you know and owner has changed his philosophy recently. Or there's been some turnover. But the more past data you have, the more accurate this can be.

Edited by Sarnoff

Share this post


Link to post
Share on other sites

I've been trying to figure out how to best use the ADP_USER option and have been tweaking my system for the last few years. I think I've hit upon a method to better estimate my league's tendencies, this year there were several patches during the draft where everything followed exactly to plan. Only occasionally would I have to search for a different player in the pool than DD thought my opponents would take, and most of the time it was within a few projected points (within the same tier), so it didn't matter much anyway.I'm in the 9th year of a 12-team league and have 8 years of old draft data to go through on many of the teams. We've turned over a few owners and my adjustments to that system are noted below. Our current rosters have 9 starters and 7 bench positions, we start 1 QB, 2 RB, 2 WR, 1 RB/WR Flex (no TE in the flex), 1 TE, 1 K, 1 DST.Basically, I use an Excel spreadsheet to track selections by owners, then I work out each owner's average tendencies. I feed that into ADP_USER to come up with a very specific list.I thought I'd share my method for those that may wish to try it, or for anyone who has suggestions or input. It may be data-entry intensive to start, but once it gets going, each year you only have to update with the previous season's draft.Each owner has a separate section that looks like this:

Yr  Name  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 1611  Bob   RB RB QB WR RB WR RB DT WR PK QB WR RB TE RB WR10  Bob   RB WR QB RB RB TE RB WR WR QB WR WR PK RB WR DT09  Bob   RB WR QB TE WR RB RB DT QB PK RB WR RB RB WR RB....etc
Which lists years and what position was drafted in each round of the draft. I do this for every owner. Next I convert this into a grid where each position is broken out by number of selections, so I know where each guy likes to take his QB1, his QB2, etc.:
Yr  Name  QB1 QB2 QB3 QB4 RB1 RB2 RB3 RB4 RB5 RB6 WR1 WR2 WR3 WR4 WR5 WR6 TE1 TE2 PK1 PK2 DT1 DT211  Bob   3   11  *   **  1   2   5   7   13  15  4   6   9   12  16  *   14  *   10  *   8   *10  Bob   3   10  *   **  1   4   5   7   14  *   2   8   9   11  12  15  6   *   13  *   16  *09  Bob   3   9   *   **  1   6   7   11  13  14  2   5   12  15  *   **  4   *   10  *   8   *...etc
Which reads as "In 2011, Bob took his QB1 in round 3, his QB2 in round 11. He's never taken 3 QBs. He took RBs in 1 & 2, then again in 5 and in 7." And so on. You'll notice that in some years Bob didn't always take the same number of positions, so I've left those with asterisks for now, but those will change soon.Next, I weight the years. I like to use all the data but weight recent years with higher importance. I'll give full value (100%) to the two most recent drafts, half-value to the next two, and descend value from there. Let's say these are the only three years I have data for, I'll give full consideration to 2011 & 2010 and half as much to 2009's values. A little math tells me my weight multipliers are 0.4 each for 2011 and 2010, and 0.2 for 2009 (you'll know you've got it right when the total is 1.0). What I intend to do is multiply the rounds by the weighting, and then get a total. But those missing picks in some years will screw me up a bit, so I add 5 rounds each, starting with the last draft round +5, for "missing" selections. So each of the single asterisks will get a 21 (16 round draft +5), and if Bob didn't draft two players, the second missing one is given 26, the third 31, etc.My data now looks like this:
Yr  Name  QB1 QB2 QB3 QB4 RB1 RB2 RB3 RB4 RB5 RB6 WR1 WR2 WR3 WR4 WR5 WR6 TE1 TE2 PK1 PK2 DT1 DT211  Bob   3   11  21  26  1   2   5   7   13  15  4   6   9   12  16  21  14  21  10  21  8   2110  Bob   3   10  21  26  1   4   5   7   14  21  2   8   9   11  12  15  6   21  13  21  16  2109  Bob   3   9   21  26  1   6   7   11  13  14  2   5   12  15  21  26  4   21  10  21  8   21
Now I can multiply each value by the appropriate multiplier, and put those numbers at the bottom of each column. So, for example, at the bottom of the QB2 column I'd put the sum of (11*.4)+(10*.4)+(9*.2), which is 10.2. That means, after weighting, Bob selects his QB2 in round 10.2 (not exactly the same as drafting at pick 10.02, but rather, 2 tenths of the way through round 10. It doesn't matter that this isn't an integer number). For DT1, it'd be (8*.4)+(16*.4)+(8*.2)=11.2. So, on average, we'd say Bob drafts his first defense at round 11.2. His DT2 would average out to round 21, which is beyond the scope of the draft (16 rounds), so we'd see he rarely, if ever, drafts one (I'm keeping the DT2 column just because other owners might draft DT2s, or Bob may have in the past outside the scope of the example here, and keeping the column for future reference makes it easier). Taking a look at WR6, we see Bob only drafted one once, in 2010. That's where our +5 adjustments come in, we'd weight average those picks out to (21*.4)+(15*.4)+(26*.2)=19.6, which is also outside the scope of our 16 rounds. So usually he doesn't pick one here, either.Here's the grid with an "average" at the bottom of each column of where each position is drafted:
Yr  Name  QB1 QB2 QB3 QB4 RB1 RB2 RB3 RB4 RB5 RB6  WR1 WR2 WR3 WR4  WR5  WR6  TE1 TE2 PK1  PK2 DT1  DT211  Bob   3   11  21  26  1   2   5   7   13  15   4   6   9   12   16   21   14  21  10   21  8    2110  Bob   3   10  21  26  1   4   5   7   14  21   2   8   9   11   12   15   6   21  13   21  16   2109  Bob   3   9   21  26  1   6   7   11  13  14   2   5   12  15   21   26   4   21  10   21  8    21AVERAGE   3  10.2 21  26  1  3.6 5.4 7.8 13.4 17.2 2.8 6.6 9.6 12.2 15.4 19.6 8.8 21  11.2 21  11.2 21
Obviously, this is very easy to do in Excel. You figure out the formula once, then copy it to all the other cells. IF you have a team with a shorter history, you have to change the weightings, otherwise, this can work for all your teams.NOW, all I have to do is sort the averages by position, another task done easily with Excel. Just by eyeballing we can see that Bob will take an RB first, and he's more likely to take a WR in the second (average spot 2.8, he's gone WR in the 4th, 2nd, and 2nd in the last 3 years) than go QB (always in the third, average spot 3.0). Sorting by averages from lowest to highest, we get this pattern:Bob's Average: RB1 WR1 QB1 RB2 RB3 WR2 RB4 TE1 WR3 QB2 PK1 DT1 WR4 RB5 WR5 RB6 (WR6 QB3 QB4).Those last 3 spots are 17th, 18th, and 19th, so we discard those since they are outside of the scope of the 16 round draft. We just want the first 16 positions. Note RB6 falls into the data here, even though it's average is round 17.2, since it's still more likely Bob will take a 6th RB at the end of the draft than anything else.Now, after doing this for all the other teams in the draft, you'll get an average for each person. INCLUDE YOURSELF when doing this, your draft tendencies are just as important as everyone else's to have.Then, take the owners and put them in DRAFT ORDER on a new excel sheet. If Bob is drafting second, list him second:ALICEBOB RB1 WR1 QB1 RB2 RB3 WR2 RB4 TE1 WR3 QB2 PK1 DT1 WR4 RB5 WR5 RB6CARLDAVE...etcand fill in the rest of the owner's tendencies as well. For simplicity, strip out the numerals and your columns in Excel might look like this:
ALICE  RB RB WR QB TE WR RB RB QB DT PK WR QB WR WR RBBOB    RB WR QB RB RB WR RB TE WR QB PK DT WR RB WR RBCARL   RB QB RB WR TE RB QB PK RB WR WR DT QB WR RB RBDAVE   RB RB WR RB QB WR WR WR TE PK DT WR WR RB RB WR..etc
Now, just copy DOWN each column and paste into a new Excel file, and you'll have your ADP_USER file. If your draft snakes, create a "mirror image" for the even number rounds that lists the same info in reverse order (ending with Dave, Carl Bob, Alice).Your first 4 down would be:RBRBRBRB...and coming back up, at 2.09-2.12, you'd haveRBQBWRRB...and reading back down for the third round, you'd have in cell A24-A27:WRQB RBWRSave this as a .csv file and you're done!It sounds like a lot of work, but it's very easy to keep going after setting it up just once. The more you know about Excel, the easier it is to automate many of the tasks. If your league uses keepers, ADD THOSE IN! Let's say Bob kept a RB, WR, and a Tight End. Very unlike him! But you have to account for that in your ADP_USER file, so just slide TE1 into position and refigure his tendencies: RB WR TE QB RB RB WR RB WR QB PK DT WR RB WR RB.Play around with weighting past years as you feel is appropriate. Perhaps you know and owner has changed his philosophy recently. Or there's been some turnover. But the more past data you have, the more accurate this can be.
Sarnoff,Thanks for the very detailed analysis. I do something very similar. I like what you did with the +5 adjustments. I always struggled with what to do with the extra RB or WR that's only been picked in two years. I also do a weighted analysis, but this year I also looked at a weighted analysis of the last 3 years (40%,33%,27%) and an equal distribution (34%,33%,33%). I decide which weighting to use based on how they drafted compared to their predictions. Since you have the predicted draft, you can compare it to the actual draft when completed. I do it for each player. If RB2 was expected to go 5 and he went 6, that's a -1. I look at the top 7-8 picks and they should total up to ~0. If they do, then I weight things evenly. If they don't, I'll skew the weightings more toward the recent picks.I do this for each owner. In my EXCEL sheet I have a a column that shows the appropriate picks for each slot. For example, in a 12 team league, with 3rd round flip, the owner with the 8th pick will draft:01.0802.0503.0504.0505.0806.0407.0808.0509.0810.0511.0812.0513.0814.05You can paste that into the column next to the positional rankings for each owner, then sort on that list to put the entire draft in order. If you use a draft log template, you can also take the new draft order, enter in into the template to calculate the appropriate baselines... Edited by GoBears84

Share this post


Link to post
Share on other sites

How do you suggest handling changes in league membership -- e.g. one past member is replaced with a new member whose tendencies are unknown?

Share this post


Link to post
Share on other sites

How do you suggest handling changes in league membership -- e.g. one past member is replaced with a new member whose tendencies are unknown?

For the first year I just use the same tendencies as the previous owner. It's a good a start as anything else I can think of...

Share this post


Link to post
Share on other sites

Thank you for this, I am looking forward to giving it a go. This will be my first time using DD and am going to attempt inputting my leagues picks as they happen as we use ESPN to draft and hopefully DD giving me a recommendation of who to select as the draft progresses. Not sure if this is totally crazy and would love input if anyone has ever done it before. Perhaps this is a topic for another post...

I did have a question though. How would this be affected if our league expanded from an eight person league in 2012 to a 10 person league last year in 2013? I have record beginning in 2011 (eight person league as well). I didn't want my data to be skewed because the pool of available players in a eight man league in the 4th round might differ for that in a ten man league. For example, RBs might go quicker in a ten man because there are fewer remaining to choose from in later rounds. Thoughts? Thanks for your feedback.

Share this post


Link to post
Share on other sites

Most teams will draft to a strategy - first 5 picks typically include 2 RB's and 2 WR's and depending on projections 3-5 QB's in the first 5 rounds and 3-4 TE's. To me, the baseline is the key, not the exact order they are picked.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.