I'm new this year as a subscriber so I'm still playing and learning.

Anyway, something I do each year is look back at previous drafts to get an idea as to when runs happen, owners' tendencies, etc.

I was wondering if it would be possible to use previous years drafts to help simulate what the owners would do in the mock drafts. Not sure if it's possible but figured I throw out the suggestion.

If you have previous years drafts, then definitely take a look at this thread: User ADP Thread

Blox,

Last year Stormbringer put together a spreadsheet that allowed you to take the data from DD and generate a Draft Log. It shows the results of each team by round as well as a count of positions taken by round. This will put your historical data in an easy to use format for customizing ADP.

PM me if you'd like me to send you a copy.

Joel

GoBears84,

If you email me a copy, I'll post it here for everybody.

Done.

Strombringer set it up for a 12 team serpentine draft. I brute force modified it for a 10 team serpentine so that both are available. If somebody has the Excel chops to have it user configurable by number of teams and type of draft, go for it...

Joel

Here is the draft log template that was mentioned earlier in this post. I haven't tried it yet, but hope to in a few days here.

Wow, that looks like it must have been a lot of work and will be very useful. Does anyone have the Excel knowledge to do this for a 14 team league? Thanks.

I can brute force it when I have the time later - unless somebody else can find a way to modify it to run semi-automatically.

Joel

How would I input previous years' drafts? The file says to generate a draft summary with DD. Do I need to download older versions of the program and manually re-enter my last two years' drafts? I've used DD for two years but am new to Excel so any help would be great.

I modified the excel sheet to allow 1 to 14 teams and 1 to 25 rounds and also included IDP info. The cells that aren't used will blank out and you can hide those columns/rows or just move past them.

Let me know if this was enough changes for you guys.

draft_log_template Update

Edit: Went back this morning and made the formulas slightly better and adding teams or rounds is a ton easier now.

If you saved your league files after drafting... you can use this years Draft Dominator to open those league files. Click File - Open and browse to the old league files. Then do a File - Export - Player Pool and create a file. Then double click on that file to open it in Excel. Once you open it in excel, you can follow the instructions giving in the spreadsheet. Let us know if you need additional help.

jstout: My new hero!

:sehorn:

Thanks jstout for taking my work and making it even better!!! Maybe if we beg enough we can get Bruce to add this type report to DD eventually. In the meantime, here are permanent links to the formats. The only change I made to jstout's revision was enhancing the instructions, including how to hide/unhide columns and rows. Let me know if any comments or suggestions. Good luck to everyone this year.

[Bruce: Any chance of getting this pinned?)

10 teams, no IDP
12 teams, no IDP
Configurable up to 14 teams/25 rounds, with IDP

will any of these work with a non-serpentine draft?

Not currently - but it's usefullness is counting players taken per round and that won't change regardless of order...

Awesome work guys...

I commish a keeper league and need to publish to the league who is taken when after each draft.... I used to have to manually type every pick into a spreadsheet to send out to the league. UGH!

After having done this for ten years, I am THRILLED to find this application.

My hat is off to you. Thank you!
Steve

Did you know that you could also export the draft summary and the teams from within DD into a .csv file? The template just puts it into another format so that you can get Round by Round summaries.

Joel

I somewhat automated this by going to Data->Get External Data, select the CSV, but don't import the status column.

Once its setup, just hit refresh and it will reload the csv file. You can also set excel to auto refresh up to once a minute.


It would be nice if you could auto export the CSV file from draft dominator after each pick....

I know this thread is almost a year old, but I'm re-evaluating my strategy and considering using a ADP_user file instead of 'User Configurable by Position Rank'.

I used strombringer/jstroud's tool to get round-by-round position counts and cumulative numbers (and even averaged 3 seasons draft data).... but I'm struggling to see how to re-import this info into the DD.

It's my understanding that ADP_user is supposed to look like
RB
RB
RB
...etc

but the draft_log_template gives me data in the form of

...etc

So thru 3 rounds I know 11 WR's will be taken (for example), but I don't know where and that's what ADP_user requires (right?)

Is the general idea to manually build your ADP_user file based on the round-by-round summaries, or is there a more "scientific" automated method?

There are two aspects to setting this up correctly.

One way to use the data is to sort by Team name and then round. Then you see how each team picks. What I've done is taken the data for each owner for the past few years and tried to determine their tendencies. RB, RB, RB or RB, WR, RB, etc. I put that data into a new spreadsheet that shows their typical pick by round.

Then when the draft order comes out, I cut/paste and put them in the correct draft order. I then use that to create my custom ADP.

You can then take that information, plug it into the "data" sheet and determine how many players for each position will be taken in each round.

Using the data that shows number taken per round will help you with the drop-off calculation.

It's not hard, just a lot of cut/pasting. Check this thread on the auto-dropoff calculation.

Is the custom dropoff spreadsheet still available? The link is not found.

I'm having trouble wrapping my head around this concept. I'll have to check my historic data to be sure, but I imagine after the first 3 (or 4) rounds individuals' tendencies are going to fluctuate year to year. I mean that if someone looked at my drafting tendency they'd see RB, RB, WR year after year.. but after that I don't think I keep a consistent pattern.


It doesn't matter much anyway as my league is probably expanding from 10 teams to 12 this year. I plan on multiplying the "user configurable by pos. rank" by 6/5 to ballpark the baseline, but I think regular ADP mimics my leagues picks pretty accurately.

I don't entirely disagree with you. However, what people have observed is that the number of players that fall off the board is reasonably consistant from year to year. Not exact - but close. This is especially important in terms of VBD and "tiers". If your historical data suggests that there is going to be a run on WR's in the 5th round, causing a tremendous drop in VBD - essentially into the next tier -- it would be advantagous for you to wait on a 3rd RB and grab the WR you want now - then grab the RB after the run on WR's.

As indicated in a long post above -- you have to see it in action to appreciate it.

I didn't use DD last year in my drafts, but I want to use the ADP from those drafts this year. Do I just need to input last year's drafts and then save it as a .csv, then use that as my ADP for my upcoming drafts??

You can do that. If you have more data you should incorporate it too. Check out this short thead for some ideas.

I hope everyone's drafts are going well this year. Just wanted to post a minor update to the Excel Draft Log. A configurable, no IDP log is now available. My leagues don't use IDP so this eliminates a few blank columns in the position counts. Nothing significant. Permanent links are below.

Configurable up to 14 teams/25 rounds, no IDP
Configurable up to 14 teams/25 rounds, with IDP

I also see where Bruce has recently added a Draft Board to DD. Very nice. It provides a similar layout to the Draft Log but isn't yet designed to count positions. It is very colorful, I'll say that!!

Good luck to everyone!

I'll add them to my collection of files.

Are you certain you don't want to take a shot at creating configurable custom dropoff sheets?

Instead of hiding the cells you don't want to print... you could highlight the cells you do want to print, and then click File - Print Area - Set Print Area.

For example: highlight the part of the draft board with players filled in, click File - Print Area - Set Print Area, then do a print preview, and just the draft board will show up.

Bruce, good advice if the user only wants the draft board. However, to print the position and round counts hiding the unused columns is necessary. At least I think so. If anyone knows how to print portions of the same worksheet on one page without hiding columns I'd be interested in hearing the technique. I've needed that capability on several occasions!

GoBears84: jstout is the Excel guru. He took my relatively vanilla work and took it to the next level. Don't know if he's still around though. I need training on dropoffs anyway. That's the one part of DD that I don't use, or even fully know how to leverage to any advantage. Yet I suceed in spite of myself. LOL.

I'm still around and just happened to come across this today.

Is this what you wanted added:
Draft Template with Dropoffs

That's why you are an expert and I'm a wannabee

Though it does need to be modified slightly so that it can be used during the draft.