paper: New Scouting Database from Team 2834

[Ed Law]

Originally Posted by Oris

I got another one for you Ed to fix your opening issues.

Replace:

filen = CurDir + "\" + Format(teamnum) + ".jpg"

With:

Dim sCurDir As String
sCurDir = Mid(ThisWorkbook.FullName, 1, Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name))
filen = sCurDir + "\" + Format(teamnum) + ".jpg"

That way the team pictures will load if you open the file by double clicking, open recent, or file open.

Hope this helps,
-Oris-

Thanks Oris. Version 5 has this included. I also put in the protection as you suggested to help user avoid accidentally modifying fomulas in other cells. There is no password so users can unprotect it if they want to.

Ed

[cygnus.x1]

Where do the pictures come from? The spreadsheet works great otherwise!

I guess I need to find my own team robot photos or did you have a zip file of them as well?

[Ed Law]

Originally Posted by cygnus.x1

Where do the pictures come from? The spreadsheet works great otherwise!

I guess I need to find my own team robot photos or did you have a zip file of them as well?

Sorry it took so long to reply. I have been busy with the robot. I download pictures from The Blue Alliance using downthemall with Firefox. You can take your own pictures and put them in the same directory as the scouting database.

[Ed Law]

Hi,

Here is the week 1 data. Please go to http://www.chiefdelphi.com/media/papers/2174?

Sorry it took so long. I had it done on Monday but was waiting for missing data from FIRST. As of this morning, there are still no match results data from Buckeye Regional and match 65, 66 and 67 results from Oklahoma Regional is still missing which will affect the OPR and CCWM results a bit.

Good luck to teams going to Week 2 events. May the traction force be with you!

Ed Law

[Killraine]

So this scouting database will only be useful in looking at teams that have been to past competitions? Is there anyway for us to get scouting information for the competition we are currently at?

[Clinton Bolinger]

Originally Posted by Killraine

So this scouting database will only be useful in looking at teams that have been to past competitions? Is there anyway for us to get scouting information for the competition we are currently at?

12 Scouters and 12 Clipboards.

or you can try this:

http://www.chiefdelphi.com/forums/sh...ad.php?t=75272

-Oris-

[Killraine]

Mmk, was planning on integrating the two systems if there wasn't already an integrated one available.

Thanks

[Ed Law]

Originally Posted by Killraine

So this scouting database will only be useful in looking at teams that have been to past competitions? Is there anyway for us to get scouting information for the competition we are currently at?

For week one, I am only posting static past results. I have a version that can also automatically get updated data from FIRST website so you can get OPR or CCWM data during a regional. It will also predict results of remaining matches. The problem is it is slightly harder to use as the user need to press a few buttons to get external data refreshed etc. I am still trying to simplify or streamline the process to make it more user friendly.

Ed

[Ed Law]

Hi,

The missing data from Week 1 is still missing. For Week 2, only New York is missing some data. I am not going to wait this time. If I get any of these results, I will add it next time in Week 3.

Here is the week 2 data. Please go to http://www.chiefdelphi.com/media/papers/2174?

Starting from Week 3, you will be able to update this during the competition. That means you can calculate OPR and CCWM as match data becomes available. Unfortunately, I am not able to setup the link before the competition. I have to wait for them to post some data before I can set it up. I will do it Friday morning so check back on CD to download it during the competition. You just have to press one button called "Refresh Data" or Ctrl-Shift-P to run the macro.

If you have any questions, comments or suggestions how to improve this, just let me know.

Ed

[Ed Law]

Hi,

I forgot to mention that the match prediction is available. Keep in mind that it will not be accurate until you have a larger sample of matches.

I did a study of the prediction of match results using OPR and CCWM. Through the first two weeks including those events that have complete data published, in the elimination round, the prediction using CCWM is 59.5% and using OPR is slightly better at 63.6%. Last year, the accuracy was better.

The way I use the prediction is knowing whether the odds are with you or against you. If it shows a close match, you may want your best payload specialist out there. If it shows the odds are against you big time, then you may want to change your strategy with your alliance partners and do something more risky since you have nothing to lose.

What do you think?

Ed

[Ed Law]

Hi,

I am sorry but I have to temporarily remove the week 2 data file. There is a bug in the program. The week 1 data file is correct. I changed the program quite a bit this weekend to try to improve it. I must have introduced a bug in the program. Sorry for the inconvenience. I will put it back up as soon as I find the bug.

Ed

Edit: I found it. It was a small bug. It only took a few minutes to find it. I will rerun the data and post it as soon as possible. Sorry to those who downloaded the data. I hope you see this and download it again.

[Vikesrock]

Originally Posted by Ed Law

Starting from Week 3, you will be able to update this during the competition. That means you can calculate OPR and CCWM as match data becomes available. Unfortunately, I am not able to setup the link before the competition. I have to wait for them to post some data before I can set it up. I will do it Friday morning so check back on CD to download it during the competition. You just have to press one button called "Refresh Data" or Ctrl-Shift-P to run the macro.

If you have any questions, comments or suggestions how to improve this, just let me know.

Ed

Ed, what data do you have to wait until Friday morning for? The match results pages themselves do not exist until the first match is entered by the FMS system but the URL is known ahead of time. I don't know what you need, so I don't know if that helps you or not.

[Ed Law]

Originally Posted by Vikesrock

Ed, what data do you have to wait until Friday morning for? The match results pages themselves do not exist until the first match is entered by the FMS system but the URL is known ahead of time. I don't know what you need, so I don't know if that helps you or not.

Hi,

Yes, I know the URL ahead of time. My problem is in Excel, when I do a Import External Data and Edit Query and change the URL at that time, it will say "The webpage cannot be found" because it is not created yet. However, I need that to be there in order to select the correct table of the webpage to import into my spreadsheet. Hence I have to wait. Do you know of a way to get around this? I am not very good in Excel and I am just learning a little every day.

Regards,

Ed

[IKE]

Nice work Ed, and thanks for posting this. It will take me a while to absorb all of this.

[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.