paper: New Scouting Database from Team 2834

[Joe Ross]

I'm having trouble updating the data for Silicon Valley. I change to the tab called SiliconValley and do ctrl-shift-p and I get Run-time error '1004'. If I hit debug, it's failing on the first querytable refresh. I notice that if I'm on one of the regionals with existing data, I see the external data toolbar pop up, but I don't see that on the later regionals. Is there something more I need to do?

[Ed Law]

I updated the database to include week 3 data. All the week 3 regional and district data are in each tab. However, due to the unfortunate circumstances at the Israel Regional, I did not include those data in the master database. The data is not very meaninful when each team played only 3 matches. Hence when you use the query tab, the Israel Regional data will not show up.

[Ed Law]

Originally Posted by Joe Ross

I'm having trouble updating the data for Silicon Valley. I change to the tab called SiliconValley and do ctrl-shift-p and I get Run-time error '1004'. If I hit debug, it's failing on the first querytable refresh. I notice that if I'm on one of the regionals with existing data, I see the external data toolbar pop up, but I don't see that on the later regionals. Is there something more I need to do?

Last year, I entered the link manually after it becomes available which is usually a few days before each week's competition. There is no data but the page would be setup so my link will be valid. At the end of the season, I wrote a macro to automatically set it up for all the weeks and it worked. Unfortunately FIRST changed something this year and it is no longer working. Also I was forced to migrate to Excel 2007 and it does not look like they support the .iqy files any more. So now I am back to manually setting up the link after the competitions are over.

If anybody is interested in using this spreadsheet during the competition which has internet access, you need to set up the link. The easiest way is this. Use a browser to navigate to the page where they show ranking. Copy the URL. Go to the tab of the regional you want to use.

In Excel 2003, select cell A1, go to Data, Import External Data and then New Web Query. Paste the URL data into the box that says Address. Then select the table of the page that shows the ranking only. You will see a check mark. Then click import. Then go to cell A101 and A251 and do the same thing for qualifying and elimination match results. That's it.

In Excel 2007, select Data tab and click From Web. The rest are the same.

If you need more help, please PM me. I will walk you through over the phone.

Sorry I don't know enough to setup the link ahead of time for you when the page does not exist. If somebody knows how to overcome this, please let me know.

[Joe Ross]

Thanks, I think I figured it out. A few other things that (temporarily) got me. I had to set the properties of the web query to overwrite, or else it would insert new columns and throw everything off. I also had to change the last range in RefreshData to A251 instead of A201.

Once I did those, I was able to calculate the OPR and CCWM. Thanks!

[rulesall2]

There is a mistake in the WPI regional. During the regional the last qualification match was not counted when the data was saved into FMS so the version that is out on the FIRST site isn't the final version that was used for alliance selections, which were re-done after the error was realized. The match can be found here: http://www.thebluealliance.net/tbatv/match/2010wor_qm58

That would bump 2791 to 7th seed 5th alliance captain at WPI. And 190 would also be bumped up to 6th seed 4th alliance captain. This would mean 2104 would not be the #5 Alliance captain, but instead was the 8th alliance captain. Below is the list of the alliance selection of WPI with teams in order of captain, 1st pick, 2nd pick.
WPI 2010.

1 230 20 228 W
2 1735 2370 663 F
3 3280 348 157 SF
4 190 2523 839 SF
5 2791 195 1124 QF
6 2877 172 3205 QF
7 358 716 2621 QF
8 2104 1995 3125 QF

[XaulZan11]

Thanks for putting this together again. It's a huge help for the Championship.

I came across two errors, however, regarding Wisconsin and Midwest. For Wisconsin, it says 1732 was picked 4th or 13th; we were actually picked 3rd. For Midwest, it says we were first pick, but we were actually AC1. A quick glance finds some more errors with those regionals.

I didn't check other regionals, but I would suggest you check all the regionals using the information from this thread: http://www.chiefdelphi.com/forums/sh...ce+selectio n

[Ed Law]

Originally Posted by XaulZan11

Thanks for putting this together again. It's a huge help for the Championship.

I came across two errors, however, regarding Wisconsin and Midwest. For Wisconsin, it says 1732 was picked 4th or 13th; we were actually picked 3rd. For Midwest, it says we were first pick, but we were actually AC1. A quick glance finds some more errors with those regionals.

I didn't check other regionals, but I would suggest you check all the regionals using the information from this thread: http://www.chiefdelphi.com/forums/sh...ce+selectio n

My program automatically calculates the Alliance captain and picking order based on the ranking. Unfortunately, the Wisconsin ranking list is missing. My data came from "Vikesrock" but there may be small errors in it. The FIRST Midwest data shows 1732 ranked number 2 which is why my program thought you were first pick instead of AC1. I will fix these problems when I publish the final data before championship.

[Ed Law]

Week 4 data is published. Colorado data is missing from FIRST.

[Ed Law]

Week 5 data is published. I will publish the data again after championship division data is available. I will be in Atlanta and can only update these data at night at the hotel since there is no internet access at the competition.

1) Colorado data is now included.
2) Wisconsin data was republished by FIRST and is included.
3) For Midwest, I only manually fixed the data for Team 1732. Some other team's data may still be wrong.
4) For WPI, I tried to fix the data by manually adding the seeding points for the 6 teams from the last match. However, the order is still not right because there must have been some penalties that affected the points. I decided to leave it as is until FIRST fix the data.
5) I added a macro to replace one regional's data when there is a change instead of redoing all the regionals in the central database. This will make it easier for me if FIRST publish corrected data.
6) Israel data is not in the central database. The sheet for that regional is still available.

If you are aware of any corrected data that is republished, please let me know. If you want good data in this database, you need to work with your regional director and FIRST to get the data corrected. A lot of teams and TBA depend on these data.

[Ed Law]

There was a small error in Midwest data. When I switched the ranking of 1732 and 16 manually, I forgot to switch their OPR, CCWM etc. It is now corrected in version 2. This is one of the reasons why I don't like to change the data manually. There is always a chance of human error introduced.

[Ed Law]

Championship division data is now included. I will be posting divisonal strength tonight.

[Ed Law]

It seems that since I last posted, some teams dropped out and some teams were added. I will update it Thursday morning after the match schedule comes out and at the end of each day.

How many teams going to Atlanta will actually be using this database and update it in real time? If you do, please stop by our pit and say hi. I would love to talk to you and get some feedback.

[cygnus.x1]

The SpaceCookies (FRC1868) will be in Atlanta and I'll be using it with the team. Not sure if we'll have real-time updating or not. Depends on the network connectivity there.

Thanks for updating the spreadsheet!

[Ed Law]

Originally Posted by rulesall2

There is a mistake in the WPI regional. During the regional the last qualification match was not counted when the data was saved into FMS so the version that is out on the FIRST site isn't the final version that was used for alliance selections, which were re-done after the error was realized. The match can be found here: http://www.thebluealliance.net/tbatv/match/2010wor_qm58

That would bump 2791 to 7th seed 5th alliance captain at WPI. And 190 would also be bumped up to 6th seed 4th alliance captain. This would mean 2104 would not be the #5 Alliance captain, but instead was the 8th alliance captain. Below is the list of the alliance selection of WPI with teams in order of captain, 1st pick, 2nd pick.
WPI 2010.

1 230 20 228 W
2 1735 2370 663 F
3 3280 348 157 SF
4 190 2523 839 SF
5 2791 195 1124 QF
6 2877 172 3205 QF
7 358 716 2621 QF
8 2104 1995 3125 QF

Thank you for pointing that out. I will make these changes when I publish after all district/regionals are done. It takes an hour to repopulate the data into the central database sheets WM data and OPR data. I can't change that every time there is one mistake.