[FF]: Draft Organization Stuff To Do

As I mentioned in this post, I think some of the programming for future organization/draft running/scoring stuff should be done collectively. This is intended to be the organization thread. There’s quite a few tasks to be done, improvements to be made, and all that good stuff.

So, there are two main categories. There’s the Draft Assistant project, mainly in Excel macros (Visual Basic and the like), which is a big one but easily breakable into smaller chunks, and there’s several smaller projects that one or more people can take on. If you want to work on something, put your name down on that so we know what still needs to be covered.

Draft Assistant V3 (EricH, coordinator)
[strike]-Rewrite original macros for speed/optimization. (EricH–I’ll be modifying the code to set and use certain variables throughout the program, rather than having to reset 'em all, as the key part of the process)
-Rewrite for dynamic placement of team list. (Note: This ought to be pretty straightforward, so I might get to it when I do the other rewrite. Right now, it’d be a pain in the behind.)[/strike]
-Add a 4th-round option, as that might be useful in the future. (There’s an existing macro or two that can be modified to make this easier.)
-Incorporate list picks.
-Incorporate random picks.
-Incorporate error-checking.
-Automate format changes and error-checking.
-See if the various macros etc. work in Google Docs, or can be rewritten for same.

General Organization
–Work out single scoring sheet with alliances/scores–location, layout, automation level, all the gory details.
–Automatic team list pulling from FRC site.
–Automatic scoring (by team or by player)–I think there’s an existing method, but IIRC it didn’t work properly when I checked it for this year.
–Automatic waiver processing–if anybody wants to be so bold.

The plan on the Draft Assistant is: If someone wants to get a jump on it, there’s a copy in CD-Media; I’ll point you to it if you’ve forgotten (or you could search for me as the uploader). [strike]I’ll use the development version, which is slightly different and do the above-mentioned rewrite there, then publish that (and I’ll make sure to comment a lot within the macros).[/strike] At which point, y’all see the list, y’all have some fun, and if you complete a section that seems to work, drop it on over to me and I’ll republish the whole sheet.

I could work on general organization; I’ll have a lot of free time over the next couple weeks or so.

I have uploaded the rewritten Draft Assistant to http://www.chiefdelphi.com/media/papers/2881, under the “Group Development Copy” paper. (“Clean” setup versions remain available.)

I took care of the first two rewrites and added some comments.

I can help with the general organization also.

I will take a look at this. Google spreadsheets just recently went through an overhaul and some new things were added.

I’ll take this on. Any specific ideas or requests?

Should I set it up so that you can look up points on teams by entering team number and week? i.e. like a get function such as get_points(2495,4) and it spits it right out? Any features you’d like to see?

I’d go with a couple of different data access methods, namely by event, by team, and by team and week/event. There are various reasons to pull each of those.

What I’m currently working on: That random list pick. I think I can get it working in the next couple of weeks.

New projects, under Organization:
–I was just thinking it might be nice to be able to maintain an archive of all the drafts.
–And, for some of the Excel wizards out there: A method for updating an event’s team list on command, highlighting new and removed teams.
–Something I might be able to do in off time would be to make a draft runner coverage calendar. Essentially, who’s likely to be available on what night(s), and who’s actually taking on the drafts, and who’s NOT available. Low priority, mainly because it’s pretty quick to set something like that up.

Question for going forwards: I’ve been thinking of tackling the list picks after I deal with the random list. Would folks prefer to view those in vertical or horizontal form, and would you rather have 'em on the main player list, in a separate tab, or right next to the drafting tier? (BTW, I don’t think it’ll be an issue to take lists in and swap 'em between vertical and horizontal. Excel has a method for going one way, I have a method for going the other way somewhere in an archive if I can find it.)

If a database is created through the draft managing program and uploaded somewhere easily accessible, that would be nice. That could further be combined with the scorer to create a live-updating ranking webpage

Status update: I think I have the random list pick functional. I’ll pull a draft and see what it does before too long (read: sometime next week). If it works as planned, I can do some simplification elsewhere in the code.

OK, by “functional” I mean that it runs around and gets stuck on an empty list. Haven’t tried it with teams or a draft loaded. I’m also trying to get the formatting to play nicely this time around.

First run of the scorer, currently scrapes all the data needed using TBA API and builds it into a single array. I just need to start the actual scoring process. Replace the year and the event code with any valid combo. Please try to break it, and let me know if you find any inconsistencies or errors.

Planned features: Different scoring systems per year, each system is setup in an ini file. You’d be able to choose, for example, to look at the 2012 results with the 2012 FF scoring system or the current (2015?), whatever floats your boat.

Once the event scorer is done, I can create separate ‘functions’ such as pulling up only a specific team, ect. Suggestions welcome.

Because I’m flying by the seat of my pants and learning php as I go (this is my maybe my 3rd php project overall), criticism is welcome from anyone more knowledgeable. Source.

Autodraft looks possible with Gdocs. Will investigate further. Sheets can remain hidden (I think) depending on who you are logged in as.

However if people have a problem with having a google account, that may be a problem.

How is the drafting program going to work? Is there any chance it could output the alliance(s) for a FF team into somewhere I can scrape it from? I’d be willing to work with whoever to make it happen

I’ll see what I can do as far as getting the “backbone” in Excel macros. Found a trick the other day that ought to work wonders as far as automating some of the formatting/positioning. If hidden sheets can be done, it might be a good idea to have a “list input” sheet that will dump to a hidden sheet, if that can also be done.

That would probably depend on exactly how it was set up. As-is, without an online component, I can probably set up a .csv file dump; copy/paste into a Gdoc, or some similar method. With an online component, shouldn’t be too hard to rig one Gdoc to scrape another–bet I could do it with Excel, too, in terms of picking up data and dropping it off in a sheet.

BTW, random team macro testing has been delayed. I’ll try and get to that later this week, then upload that version.

I was considering having each FF team having their own hidden sheet so that they could make edits after without confusing the draft runner.

Yeah, I will look at scraping from on g-doc to another and if that is possible.


So it seems hidden sheets are a no-go. However I did end up getting scraping working, so each team can have their own submission sheet. Currently I can pull from one sheet to another, and it updates pretty quickly, have a look.

Sample submission sheet #1
Sample submission sheet #2
Autodraft Testing


I have it automatically removing already picked teams from the list for each team’s first picks. It’s not pretty work, but it’s functional

Any chance you can translate that into an Excel function, once you get the rest of the picks as well? That’s on the to-do list–matter of fact, it’s in a macro, but the macro never triggers for some reason, so I disabled it.

It’s actually not a macro, it’s a formula. So yes, entirely possible. It’s pretty ugly though. All picks are working now, and it’s fairly easy to add lists and such. Actually, it’s probably easier than making a macro.


I pull all of the picks and put them in one column, then check for a match. If a match, display “PICKED” if no match (essentially an error) display original # located in the dump sheet (sheet that pulls the list from other gdocs)

The MATCH function only works in columns or rows, so I had to just link the data into one column, with plenty of blanks (to deal with different sized tiers with different numbers of players)

So currently an “auto list crosser-offer” is what this is. Which is still super useful, and makes drafting much easier. Especially if teams want to submit through gdocs. It’s also easy to copy/paste lists, so submissions by PM or perhaps a Gform are still very much compatible.

I’m thinking it would be nice to implement a section for the randoms where it checks each team list with your random list. If that team isn’t on the random list (eg not attending the event) the team is removed from the team’s pick list automatically.

Also I may be able to run a duplicate pick checker and such.

Unfortunately, macros aren’t a thing in Google spreadsheets.

Ah. And most of the setup program is done in macros. For Brandon and others: I think I’d best make an addition to the list of stuff to do to the draft setup/running, namely: add an “export” button to send the whole thing to a .csv or .txt for transfer to Gdocs/archiving.

One thing I generally do with the setup/running is to verify each pick. I don’t care how many times you put 254 on your list for L.A., if they aren’t showin’ up, you don’t get 'em! And then when you do make a valid pick, the first check is a verification that they are in fact available. (Actually, on the first draft runner I wrote, at one point I specifically asked folks to put some incorrect teams in among their lists for an actual draft. Those teams didn’t make it into the draft.)

Update on random stuff:

Turns out that the last release–the rewrite–had a couple of bugs, that severely affected the random selections. I’ve got those fixed, but now I’m chasing down a couple of bugs in the random pick code that get the macros stuck in infinite loops, or allow a double pick. I’ll try to get those chased and squashed over the weekend and re-release the sheet. (I’m also cleaning up some unused macros.)

And FINALLY! Ya know, it really helps to reset your search variables if you know you’re about to start another search for some reason.

The random pick functionality is active and ready to go. And no, it won’t dish out the same random pick twice, even if the team is somehow on the random list twice.


And to update the draft assistant list:
Draft Assistant V3 (EricH, coordinator)
-Add a 4th-round option, as that might be useful in the future. (There’s an existing macro or two that can be modified to make this easier.)
-Incorporate list picks.
[strike]-Incorporate random picks.[/strike]
-Incorporate error-checking.
-Automate format changes and error-checking.
-See if the various macros etc. work in Google Docs, or can be rewritten for same.–Brennan already found out that this isn’t an option, so it’ll be a total rewrite if it happens.
[strike]-Automate team removal from the tier lists if picked up. Add to random macro.[/strike]

Next up for me is probably the format changes, while I still remember how to do that/find how to make it work–might be able to tweak some stuff so you don’t have to select the next slot. Also, I seem to have picked up a bug that splits the player list when generating random player lists, so I’ll have to deal with that. Anybody want to take a stab at some of the other parts of the list?

[Edit] OK, I lied. I automated team removal instead. I’ll be able to use the same macro when I do list picking, too. (35 lines of code, and about half of that is either whitespace or comments.)

This question hasn’t been answered yet. I’d like to go ahead and tackle the list picking, but I’d like to know the answer in case something goes haywire and people need to actually look at the lists.

Current Thinking

My current plan is to have the lists horizontal within the tier sheet, on the same row as the player’s slot, and added manually–easier programming is one benefit; another is quick manual takeover. I have a method to allow for vertical lists to come in in mind as well: load into another tab, hit a button, they’re horizontal, transfer over–but I need to dig out the code start point from another project.