I finished this on Sunday on the way back from St. Louis. I was going to fix the Event results before I pubilsh this. My spreadsheet macro does not handle elimination round substitutions so it is not giving credit to some teams who sub in and advanced. I don’t think I will have time to fix the program until summer although it should not take more than a few hours of uninterrupted time.
Team 2590 was the #2 pick at MAR champs, not #15. 869 was #15.
Team 1114 was the #2 pick at Archimedes, not #15. 4334 was #15.
Team 1218 was the #6 pick at Archimedes, not #11. 2851 was #11.
Team 357 was the #3 pick at MAR champs, not #14. 816 was #14.
None of these were errors. It was exactly how the program should have worked. Let me explain.
Since FIRST does not publish the alliance selection order, there were two ways to get this information. When Team 1114 first published their Scouting Database back in 2008, Karthik used to contact people from around the country who were at the other regionals and get the allaiance selection information from them. It was full of errors. Then he came up with the idea to have CD users post the information on CD and the first person to post it will get a Team 1114 T-shirt. When there is an error, others will point out and correct it. It is much better but there were still errors and you never know when it is correct and no more correction will be posted. This year CD users continue to do it but I still see people posting but not following the format and just list the teams in each alliance without paying attention to the order of Team Captain, 1st pick and 2nd pick.
I decided not to use data that is not 100% accurate. I wrote a program to automatically figure out who the alliance captains are and the other 1st and 2nd round picks. In the cases where it is definite, the program will call it out, e.g. # 3 pick. In other cases when there is no way to tell which team in an alliance was picked first or second, the program will list the higher ranked one as # 3 or 14 pick, and the lower ranked team as # 14 or 3 pick.
When FIRST publish the selection order, then I don’t need to do this any more. You may notice that for Michigan events, all the teams have a definite selection order. This is because the FTA of all the events email me the Alliance Selection Report that the FMS can write out so I can do the rankings for all the teams under the Michigan point system. When other regional FTAs are willing to post that file in CD, then there will be no more guessing. We have tried to ask FIRST to publish this information but they don’t think it is important.
Thanks for all of this information Ed! We have used it all season as baseline information and it has really helped us prepare for matches before our scouting data is available.
I have posted the Excel 2003 version of the data for those who are still holding out and did not upgrade to Excel 2007 or 2010. Excel has been very nice to me this morning. It normally would crash when I try to write it out in Excel 2003 format. I don’t know if the macros will work or not. I only tested one of them and it was working, but I know some of the VBA stuff may not be compatible.
You can find the 2003 version in the same place at http://www.chiefdelphi.com/media/papers/2174
I finally had a chance to fix all the issues with teams substituting in elimination round. The macro will check for team substitution and indicate during what match the substitution took place and which alliance it was. However it will not work if there is more than one substitution for the same alliance. Also there is a scenario that there is no way to tell there was a substitution. When a team accepted an invitation to be part of an alliance but never played in any of the elimination round matches because a substitute was called in for the first match in quarterfinals, there is no way to tell that this happened based on what FIRST publishes on their website.
I also make an enhancement that if the FTA provided me with the Alliance Selection report, the macro will read that picking order instead of trying to figure out based on alliance teams. In that case, you will not see something like “#3 or 14 pick” when it cannot be determined for sure.
