paper: New Scouting Database from Team 2834

[Ed Law]

Originally Posted by Vikesrock

I'm not all that great in Excel either, but I believe I have found a way to work around this issue.

For each table you want to grab you need to create a .icy file. This is a text file that describes the query. To find out how to format the file you can create a query to a valid page and then save the query, the only part you need to change is the event code in the URL.

Here's an example .icy file to get the Qualifications table from the DC regional

WEB
1
http://www2.usfirst.org/2009comp/events/DC/matchresults.html

Selection=3
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

The way to use these files is a bit of a hack, but is the only thing I've gotten to work. According to the help files you can just click open and select the .icy and an import wizard will pop up letting you insert the data into the current sheet, but it just opens in a new workbook for me.

The workaround I found is to create the query with a valid page (any page), then locate it under Data->Connections (it helps to label the connections). Once you find the Connection click Properties -> Definition -> Browse and select the appropriate .icy file.

I tested this by setting one for the DC regional with my computer unplugged from the internet and then plugged back in and refreshed the data and the correct data appeared, so I think it should work.

Thanks for the suggestion. I will look into it. What version of Excel are you using? I am using Excel 2002 but have access to a computer that has the latest version. I will try to make it work for the older version first and then test it with the latest version.

Ed

[Vikesrock]

Originally Posted by Ed Law

Thanks for the suggestion. I will look into it. What version of Excel are you using? I am using Excel 2002 but have access to a computer that has the latest version. I will try to make it work for the older version first and then test it with the latest version.

Ed

I have Excel 2007

[Ed Law]

Originally Posted by Vikesrock

I'm not all that great in Excel either, but I believe I have found a way to work around this issue.

For each table you want to grab you need to create a .icy file. This is a text file that describes the query. To find out how to format the file you can create a query to a valid page and then save the query, the only part you need to change is the event code in the URL.

Here's an example .icy file to get the Qualifications table from the DC regional

WEB
1
http://www2.usfirst.org/2009comp/events/DC/matchresults.html

Selection=3
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

The way to use these files is a bit of a hack, but is the only thing I've gotten to work. According to the help files you can just click open and select the .icy and an import wizard will pop up letting you insert the data into the current sheet, but it just opens in a new workbook for me.

The workaround I found is to create the query with a valid page (any page), then locate it under Data->Connections (it helps to label the connections). Once you find the Connection click Properties -> Definition -> Browse and select the appropriate .icy file.

I tested this by setting one for the DC regional with my computer unplugged from the internet and then plugged back in and refreshed the data and the correct data appeared, so I think it should work.

I tested it and it worked on pre-Excel2007. Thanks for the tip. I will get the week 3 regionals data ready and post it tomorrow. I will test it on Excel 2007 when I have time but I think it will work too.

To use it, all you have to do is go to the tab of the regional you are attending or interested in and press CTRL-SHIFT-P while you have internet access and let Excel do the rest for you. If you don't have internet access at the regional, you will have to wait until you can get internet access Friday night. Then on Saturday, you just have to update the results of other matches and the rankings. Then press CTRL-SHIFT-O to calculate OPR and CCWM.

I will try this out Friday morning to make sure they all work for Week 3 regionals. If there is a problem, I will fix it immediately. So if you encounter a problem, let me know and check back here for updates if needed. Good luck!

Ed Law

[Phyrxes]

This spreadsheet is a great resource but unfortunately at our next event we won't have internet access, is there any "easy" way to manually input data at an event?

[Ed Law]

Originally Posted by Phyrxes

This spreadsheet is a great resource but unfortunately at our next event we won't have internet access, is there any "easy" way to manually input data at an event?

I added another macro to calculate the ranking from match data so you only have to enter match results after you download team and match data the first time. Look at the Instructions inside the Excel file for when there is no internet access at the regional. I hope this helps a little.

Ed Law

[Ed Law]

Hi,

I have updated the Scouting database. It should be ready to be used for Week 3 regionals. Please read the instructions how to use it inside the Excel file. I added a macro to help ease the pain of manually entering data if there is no internet access.

If there are any questions or problems with using it, please let me know.

Good luck to all teams going to Week 3 events. Have fun!

Ed Law

[Phyrxes]

Thank you the directions make sense, now to find a free wifi hotspot near our next regional.

[Killraine]

I am currently compiling a pre-competition scouting database for Chesapeake getting information from a number of sources. After taking info for each team at Chesapeake, I noticed an irregularity with CCWM world rankings. There are a number of teams with the World Rank of 329. It appears that any team that hasn't competed yet will get this rank, but there are a number of teams who have competed that all share this rank. Actually it looks like any team that has a CCWM that is >= 0 has a CCWM world rank of 329.

I'm not sure how to upload the file in a post, so if you would like to see it, I can email it to you.

[Ed Law]

Originally Posted by Killraine

I am currently compiling a pre-competition scouting database for Chesapeake getting information from a number of sources. After taking info for each team at Chesapeake, I noticed an irregularity with CCWM world rankings. There are a number of teams with the World Rank of 329. It appears that any team that hasn't competed yet will get this rank, but there are a number of teams who have competed that all share this rank. Actually it looks like any team that has a CCWM that is >= 0 has a CCWM world rank of 329.

I'm not sure how to upload the file in a post, so if you would like to see it, I can email it to you.

You are absolutely right. The reason was any team who has not attended a regional will get a CCWM of 0.0. Teams that have a negative CCWM and registered for another regional will also have a CCWM of 0.0 because I was listing the best CCWM. It was an oversight on my part. I changed the formula so it is now showing the average CCWM but ignoring the 0.0 in calculating the average.

Ed Law

[Killraine]

As I am working here, I am planning on adding links to each team's thebluealliance.net page to make it easy to find pictures and other info on the teams. I saw your spreadsheet has links to all of the teams' official FIRST pages, but frankly those pages have little useful information for scouting. I'm not an expert at macros so I will be copying and pasting the TBA links manually for the Chesapeake teams in my database, but you may know a way to have excel retrieve all of that from the TBA website.

[Killraine]

Another thought, if anyone has compiled a folder with team pictures for use with this database, can we post that up here? Would be really nice to be able to unzip a folder of pictures and just throw the current spreadsheet in there.

[Killraine]

Last post of the day, I swear

I was looking at the Buckeye regional data (or rather looking at the big old hole where the data should be) and realized that NO data comes up for teams at the Buckeye regional when you query. I know there isn't the match score information to calculate OPR and CCWM, but the data sheet does have each team's regional record and rank. This data could at least be transferred over in the query, despite the other information being unavailable.

[Ed Law]

Originally Posted by Killraine

Last post of the day, I swear

I was looking at the Buckeye regional data (or rather looking at the big old hole where the data should be) and realized that NO data comes up for teams at the Buckeye regional when you query. I know there isn't the match score information to calculate OPR and CCWM, but the data sheet does have each team's regional record and rank. This data could at least be transferred over in the query, despite the other information being unavailable.

That's a good idea. I was waiting for them to post the scores before I add them but I will post the final results and alliance selections now so other teams can see the results. The file is called "Team_2834 2009_Scouting_Database Week 3b.zip".

Also, read about the instructions I added on downloading pictures. Right now, only a small number of teams have pictures in TBA. It is better not to automatically grab them from TBA each time there is a query especially when internet access is not available in every regional. When that becomes the case in the future, I will do what you suggest. In the mean time, we should just manually download and team scouts can take pictures at the regionals.

Ed Law

[Killraine]

Yeah, I was mostly wondering if anyone who is using this resource has compiled a pretty good folder of pictures on their own. I guess a better request would be for anyone with pictures to upload them to TBA to help out everyone in the whole FIRST scouting community.

[Phyrxes]

Well the guys from TBA just posted such a request, hopefully people read it and e-mail them pictures.