Chief Delphi
Page 1 of 3 1 23 >
Show 100 post(s) from this thread on one page

Chief Delphi (http://www.chiefdelphi.com/forums/index.php)
-   Extra Discussion (http://www.chiefdelphi.com/forums/forumdisplay.php?f=68)
-   -   paper: New Scouting Database from Team 2834 (http://www.chiefdelphi.com/forums/showthread.php?t=70111)

Ed Law 20-11-2008 23:15
paper: New Scouting Database from Team 2834
 
Thread created automatically to discuss a document in CD-Media.

New Scouting Database from Team 2834 by Ed Law

Ed Law 20-11-2008 23:26
Re: paper: New Scouting Database from Team 2834
 
I try to upload the Scouting Database but it says my file is too big. I will try to reduce the size but I will have to delete some of the data.

Ed Law 20-11-2008 23:43
Re: paper: New Scouting Database from Team 2834
 
I split the database into two Excel file. You can stitch it back together if you want or just use part 1 without part 2.

billbo911 21-11-2008 01:37
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 776614)
I split the database into two Excel file. You can stitch it back together if you want or just use part 1 without part 2.
What size would the entire file be if you zipped it first?

R.C. 21-11-2008 01:39
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by billbo911 (Post 776639)
What size would the entire file be if you zipped it first?
If you email it to me I could upload it for you :yikes:

Ed Law 21-11-2008 07:37
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by billbo911 (Post 776639)
What size would the entire file be if you zipped it first?
Thanks for reminding me of that option. It reduced it down to 1.4 Mb. I uploaded the zip file but now I can not delete the two files that were split into part 1 and part 2.

Clinton Bolinger 21-11-2008 10:52
Re: paper: New Scouting Database from Team 2834
 
Very nice database of OPR and CCWM.

Got a few suggestions that might improve your excel file.

In order to make the getpicture sub to work every time a new query is requested, change your getpicture sub to the following functions:

Code:
Function getpicture(teamnum As String) As Boolean
    Dim filen as String
   
    If ActiveSheet.Name = "Query" Then
 
    Else
    GoTo Done
    End If

    Dim AC As Range
    Static P As Shape
    On Error GoTo Done
    Set AC = Application.Caller
    If PicExists(P) Then
    P.Delete
    Else
    'look for a picture already over cell
    For Each P In ActiveSheet.Shapes
    If P.Type = msoLinkedPicture Then
    If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
    If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
    P.Delete
    Exit For
    End If
    End If
    End If
    Next P
    End If
    filen = CurDir + "\" + Format(teamnum) + ".jpg"
    Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.75, AC.Top + 5, 329.25, 247.5)
    getpicture  = True
    Exit Function
Done:
    getpicture  = False
End Function

Function PicExists(P As Shape) As Boolean
    'Return true if P references an existing shape
    Dim ShapeName As String
    On Error GoTo NoPic
    If P Is Nothing Then GoTo NoPic
    ShapeName = P.Name
    PicExists = True
NoPic:
    PicExists = False
End Function
As for calling the function add the following to "I5":

Code:
=getpicture(B2)
Finally to make the query worksheet more user friendly, I would "unlock" cell B2. You can do this by right clicking on B2, Format Cells..., Click Protection, and uncheck Lock. Then you will want to protect the worksheet by going to Tools>Protection>Protect Sheet.. then add a password if you like and uncheck select locked cells. That way the only selectable cell would be "B2" on the Query worksheet.

Hope that helps and Thanks for the information.
-Oris-

Ed Law 21-11-2008 12:56
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Oris (Post 776694)
Very nice database of OPR and CCWM.

Got a few suggestions that might improve your excel file.

In order to make the getpicture sub to work every time a new query is requested, change your getpicture sub to the following functions:

Code:
Function getpicture(teamnum As String) As Boolean
    Dim filen as String
   
    If ActiveSheet.Name = "Query" Then
 
    Else
    GoTo Done
    End If

    Dim AC As Range
    Static P As Shape
    On Error GoTo Done
    Set AC = Application.Caller
    If PicExists(P) Then
    P.Delete
    Else
    'look for a picture already over cell
    For Each P In ActiveSheet.Shapes
    If P.Type = msoLinkedPicture Then
    If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
    If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
    P.Delete
    Exit For
    End If
    End If
    End If
    Next P
    End If
    filen = CurDir + "\" + Format(teamnum) + ".jpg"
    Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.75, AC.Top + 5, 329.25, 247.5)
    getpicture  = True
    Exit Function
Done:
    getpicture  = False
End Function

Function PicExists(P As Shape) As Boolean
    'Return true if P references an existing shape
    Dim ShapeName As String
    On Error GoTo NoPic
    If P Is Nothing Then GoTo NoPic
    ShapeName = P.Name
    PicExists = True
NoPic:
    PicExists = False
End Function
As for calling the function add the following to "I5":

Code:
=getpicture(B2)
Finally to make the query worksheet more user friendly, I would "unlock" cell B2. You can do this by right clicking on B2, Format Cells..., Click Protection, and uncheck Lock. Then you will want to protect the worksheet by going to Tools>Protection>Protect Sheet.. then add a password if you like and uncheck select locked cells. That way the only selectable cell would be "B2" on the Query worksheet.

Hope that helps and Thanks for the information.
-Oris-
Hi Oris,

Wow, thanks for the code. I spent a little bit of time but couldn't figure out how to do it yet. I will try it out this weekend and repost the database.

Does anybody know why I can not delete the two files that I don't need any more now that I am zipping the file to make it smaller?

Ed

Clinton Bolinger 21-11-2008 13:48
Re: paper: New Scouting Database from Team 2834
 
Forgot one thing about protecting the worksheet, you will also have to check the "Edit Objects" box (scroll down 2nd from the bottom). This will allow for the "I8" robot picture to update correctly.

Look forward to seeing the new version.
-Oris-

Ed Law 21-11-2008 19:48
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Oris (Post 776732)
Forgot one thing about protecting the worksheet, you will also have to check the "Edit Objects" box (scroll down 2nd from the bottom). This will allow for the "I8" robot picture to update correctly.

Look forward to seeing the new version.
-Oris-
Hi Oris,

It is working now. Thank you. The picture automatically changes when the user type in a new team number in cell B2. One thing I have to change to get it to work is teamnum as Integer instead of teamnum as String.

The picture is okay if the aspect ratio is 4:3 but it get distorted if it is not. How can I maintain the aspect ratio? I tried to do it but was not successful. Can you take a look at the program and see what I did wrong?

I have decided not to protect the worksheet for now since I am still developing it.

Ed

Clinton Bolinger 24-11-2008 08:43
Re: paper: New Scouting Database from Team 2834
 
After:

Code:
Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.5, AC.Top + 2.5, 329, 250)
Add:

Code:
    P.ScaleHeight 1, True
    P.ScaleWidth 1, True
    P.Height = 250
That should keep the aspect ratio of the pictures.

-Oris-

Ed Law 06-12-2008 19:24
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Oris (Post 777230)
After:

Code:
Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.5, AC.Top + 2.5, 329, 250)
Add:

Code:
    P.ScaleHeight 1, True
    P.ScaleWidth 1, True
    P.Height = 250
That should keep the aspect ratio of the pictures.

-Oris-
Hi Oris,

I tried it and it worked. However when I tried it on Excel 2007, I think it changes it back to its original size and then stretch it to Height = 250 without maintaining the aspect ratio. I tried a few things but none seems to work. Can anybody help? I would like to make this work for everybody as more and more people will be using Excel 2007. Thanks.

Ed

Clinton Bolinger 08-12-2008 08:20
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 780261)
Hi Oris,

I tried it and it worked. However when I tried it on Excel 2007, I think it changes it back to its original size and then stretch it to Height = 250 without maintaining the aspect ratio. I tried a few things but none seems to work. Can anybody help? I would like to make this work for everybody as more and more people will be using Excel 2007. Thanks.

Ed
Try replacing:

Code:
P.ScaleHeight 1, True
P.ScaleWidth 1, True
P.Height = 250
with:

Code:
P.LockAspectRatio = msoTrue
P.Height = 250
I don't have Excel 2007 on my current computer, so I didn't get a chance to test it.

-Oris-

Ed Law 09-12-2008 23:07
Re: paper: New Scouting Database from Team 2834
 
This spreadsheet is working in Excel 2007 now. The change that Oris suggested works. It is now version 4. I still can not delete the old versions.

I will be posting the 2009 Scouting Database during X'mas break so we can see easily which teams are going to which regionals.

Ed

Clinton Bolinger 10-12-2008 08:35
Re: paper: New Scouting Database from Team 2834
 
I got another one for you Ed to fix your opening issues.

Replace:

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

Code:
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-

Ed Law 11-12-2008 16:36
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Oris (Post 781893)
I got another one for you Ed to fix your opening issues.

Replace:

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

Code:
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 08-01-2009 15:59
Re: paper: New Scouting Database from Team 2834
 
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 07-02-2009 22:00
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by cygnus.x1 (Post 796017)
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 05-03-2009 10:20
Re: paper: New Scouting Database from Team 2834
 
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 05-03-2009 11:14
Re: paper: New Scouting Database from Team 2834
 
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 05-03-2009 11:16
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Killraine (Post 832104)
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 05-03-2009 13:04
Re: paper: New Scouting Database from Team 2834
 
Mmk, was planning on integrating the two systems if there wasn't already an integrated one available.

Thanks

Ed Law 05-03-2009 13:22
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Killraine (Post 832104)
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 07-03-2009 23:56
Team 2834 Scouting Database (Week Two results)
 
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 08-03-2009 14:33
Re: paper: New Scouting Database from Team 2834
 
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 09-03-2009 00:22
Re: paper: New Scouting Database from Team 2834
 
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 09-03-2009 01:03
Re: Team 2834 Scouting Database (Week Two results)
 
Quote:
Originally Posted by Ed Law (Post 832959)
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 09-03-2009 01:24
Re: Team 2834 Scouting Database (Week Two results)
 
Quote:
Originally Posted by Vikesrock (Post 833476)
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 09-03-2009 10:54
Re: paper: New Scouting Database from Team 2834
 
Nice work Ed, and thanks for posting this. It will take me a while to absorb all of this.

Vikesrock 09-03-2009 11:11
Re: paper: New Scouting Database from Team 2834
 
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
Code:
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.

Ed Law 09-03-2009 17:07
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Vikesrock (Post 833525)
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
Code:
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 09-03-2009 17:12
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 833707)
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 11-03-2009 00:18
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Vikesrock (Post 833525)
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
Code:
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 11-03-2009 07:49
Re: paper: New Scouting Database from Team 2834
 
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 11-03-2009 12:37
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Phyrxes (Post 834624)
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 11-03-2009 12:43
Team 2834 Scouting Database for Week 3 Regionals
 
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 11-03-2009 12:49
Re: paper: New Scouting Database from Team 2834
 
Thank you the directions make sense, now to find a free wifi hotspot near our next regional.

Killraine 11-03-2009 13:46
Re: paper: New Scouting Database from Team 2834
 
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 11-03-2009 14:30
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Killraine (Post 834730)
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 11-03-2009 14:32
Re: paper: New Scouting Database from Team 2834
 
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 11-03-2009 14:45
Re: paper: New Scouting Database from Team 2834
 
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 11-03-2009 17:18
Re: paper: New Scouting Database from Team 2834
 
Last post of the day, I swear :P

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 11-03-2009 21:51
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Killraine (Post 834824)
Last post of the day, I swear :P

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 12-03-2009 11:46
Re: paper: New Scouting Database from Team 2834
 
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 12-03-2009 12:54
Re: paper: New Scouting Database from Team 2834
 
Well the guys from TBA just posted such a request, hopefully people read it and e-mail them pictures.

Killraine 12-03-2009 12:55
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Phyrxes (Post 835119)
Well the guys from TBA just posted such a request, hopefully people read it and e-mail them pictures.
:P That was me

Could anyone write a program that downloads the picture at http://www.thebluealliance.net/tbatv.../2009/####.jpg (where #### is the team number) and puts it into a folder? You would just have to run the program once and then drop the spreadsheet in there and it would have all the pictures that are currently on TBA. Basically create a matrix of team numbers (already together as Column A on the sheet "2009 Teams") and then use a loop to go through the list and download the picture to a folder on the desktop.

In psuedo-code:
Code:
for(<Every value in the matrix>)
if(http://www.thebluealliance.net/tbatv/pictures/2009/####.jpg exists)
Download the file at the link to "/Desktop/2009 FRC Pictures";
If you wanted to go all the way, you could have it so when the program creates the matrix, it first checks "/Desktop/2009 FRC Pictures" it only adds the teams from the list that you don't already have. Then, if there is a way to include that program in a startup script for this excel document, you would always have an updated set of photos from TBA.

I'm pretty sure you can do all of this in c++ except maybe the part where excel runs the program before opening the spreadsheet. But then again, its not that much of a hassle to click a .exe before opening up the spreadsheet.

Ed Law 12-03-2009 15:44
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Killraine (Post 835120)
:P That was me

Could anyone write a program that downloads the picture at http://www.thebluealliance.net/tbatv.../2009/####.jpg (where #### is the team number) and puts it into a folder? You would just have to run the program once and then drop the spreadsheet in there and it would have all the pictures that are currently on TBA. Basically create a matrix of team numbers (already together as Column A on the sheet "2009 Teams") and then use a loop to go through the list and download the picture to a folder on the desktop.

In psuedo-code:
Code:
for(<Every value in the matrix>)
if(http://www.thebluealliance.net/tbatv/pictures/2009/####.jpg exists)
Download the file at the link to "/Desktop/2009 FRC Pictures";
If you wanted to go all the way, you could have it so when the program creates the matrix, it first checks "/Desktop/2009 FRC Pictures" it only adds the teams from the list that you don't already have. Then, if there is a way to include that program in a startup script for this excel document, you would always have an updated set of photos from TBA.

I'm pretty sure you can do all of this in c++ except maybe the part where excel runs the program before opening the spreadsheet. But then again, its not that much of a hassle to click a .exe before opening up the spreadsheet.
I asked Greg Marra of TBA about a year ago about this. This was his reply.

Quote:
Install the Firefox "Down Them All" plugin. It lets you download all the links on a single page by right clicking then selecting "down them all".

Then you can go here: http://www.thebluealliance.net/tbatv/pictures/2008/ which is a page linking to all of the 2008 team photos.

Just use down them all to grab every single picture in one go
For this year's photo, just substitute 2009 for 2008. This way they can all be in the same folder as the Scouting spreadsheet. "Down Them All" is quite powerful. It will probably give you options not to download a file if it already exists in your folder. Downloading all the pictures every time takes quite a bit of time and is not practical.

Ed Law

Ed Law 12-03-2009 17:20
Re: paper: New Scouting Database from Team 2834
 
I would like to spend some time to clarify regarding the different rankings OPR, DPR, CCWM and PMR (plus/minus ranking). I tried to explain this in another post but I was rushing and didn't explain fully.

If we look at the system of equations to calculate OPR, it is

A * OPR = B(opr)

B(opr) is assembled by adding the scores of every match each team was involved in

and the system of equations to calculate DPR is

A * DPR = B(dpr)

B(dpr) is assembled by adding the scores of the opposing alliance of every match each team was involved in.

The way I solve for CCWM is

A * CCWM = B(wm) = B(opr)-B(dpr)

A is the same matrix for all 3 systems of equations. I put the winning margin into vector B which is the same as vector B for OPR minus vector B for DPR.

The way PMR is calculated is to first calculate OPR and DPR separately. Then PMR = OPR - DPR

I don't remember the proper name of the math theory but since this is a linear system, you can do some kind of superposition such that

A * PWR = A * (OPR-DPR) = A * OPR - A * DPR = B(opr) - B(dpr)

Hence CCWM and PWR are identical numerically. This was confirmed by Jesse Knight. Having said that, once I have OPR and CCWM which is what my spreadsheet calculates, I can get DPR simply by

DPR = OPR - CCWM

Isn't math great? I learned all that and more when I was in college and there is some use for it afterall.

Ed Law

Ed Law 13-03-2009 09:50
Week 3 Regional underway
 
Regionals at Florida, Peachtree and Pittsburg are underway and started to post results. The spreadsheet that I did ahead of time is working. Don't worry about the error message right now, just click "End" to get out of it. Once every team has played one match, you will not see that error message any more. If necessary I will fix that for Week 4. I will continue to monitor until every team at every week 3 regionals has played at least one match.

I hope this will help you with alliance selection tomorrow.

Ed Law

Phyrxes 13-03-2009 09:55
Re: paper: New Scouting Database from Team 2834
 
Aside from that error the spread sheet is working flawlessly on my computer here at school this morning (Using Excel 2003).

Killraine 13-03-2009 10:45
Re: paper: New Scouting Database from Team 2834
 
I don't know why I didn't think to go to http://www.thebluealliance.net/tbatv/pictures/2009/ yesterday. Should have been the first place I checked.

Thanks.

Ed Law 13-03-2009 12:19
Re: paper: New Scouting Database from Team 2834
 
I uploaded a new version "Team_2834 2009_Scouting_Database Week 3c.zip" to clean up some formating issues. I also fixed the error message when not every team has played one match yet. There was also a small bug that I fixed. For example in Pittsburg, I had 38 teams listed but there are only 37 teams. One team must have dropped out after I put the list in a few months ago. This produced an error. The new version will take care of this problem.

When I have time, I will fix the score prediction. Originally this program was written to run only after a regional is completed. Now we want it to run during a regional and some things were not planned. For example, in score prediction, you may notice the numbers predicted are very small when teams have played only a few matches. This is because the matrix A was assembled with all the scheduled matches. However vector B only has scores of finished matches. This is why the answer x is smaller than expected. I need to scale the answer x based on number of matches played and number of matches scheduled.

If anybody sees any problems with the spreadsheet, please let me know.

Ed Law

Ed Law 13-03-2009 13:18
Re: paper: New Scouting Database from Team 2834
 
I fixed the score prediction. It turned out to be easier than I thought. The new version now is "Team_2834 2009_Scouting_Database Week 3d.zip". I didn't do an extensive study yet, but some of the score predictions are amazingly accurate.

Ed Law

Killraine 13-03-2009 13:25
Re: paper: New Scouting Database from Team 2834
 
After updating the spreadsheet, I tried to Query team 330 and nothing came up in that window. It would be nice to be able to still use the Query function while you are at an in-progress competition (Situation: At the regional, we are paired up with team #### and team #### in our next qualification match. We don't have much information on them yet and this is their first regional this year, but we want a general idea what our strategy will be. A quick look-up in the spreadsheet shows us there records and a vague idea what their OPR/CCWM are)

Killraine 13-03-2009 13:27
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 835503)
I fixed the score prediction. It turned out to be easier than I thought. The new version now is "Team_2834 2009_Scouting_Database Week 3d.zip". I didn't do an extensive study yet, but some of the score predictions are amazingly accurate.

Ed Law
I'm not seeing 3d. In fact I'm not seeing 3c anymore either... Anyone else have this problem?

Ed Law 13-03-2009 13:40
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Killraine (Post 835508)
I'm not seeing 3d. In fact I'm not seeing 3c anymore either... Anyone else have this problem?
It is there now. You are too quick for me. Are you at a regional now?

Ed

Ed Law 13-03-2009 14:02
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Killraine (Post 835506)
After updating the spreadsheet, I tried to Query team 330 and nothing came up in that window. It would be nice to be able to still use the Query function while you are at an in-progress competition (Situation: At the regional, we are paired up with team #### and team #### in our next qualification match. We don't have much information on them yet and this is their first regional this year, but we want a general idea what our strategy will be. A quick look-up in the spreadsheet shows us there records and a vague idea what their OPR/CCWM are)
Again the original plan was transfer the data only after a regional is completed, I transfer the data from the sheet from that regional to sheets "WM data" and "OPR data" for use by the Query sheet. If we want to transfer during a regional also, I need to reprogram that part. Right now it only allow one transfer per regional. If you transfer more than one time, it will think that it is a new regional because it looks for blank space and dump the data there.

For now, you have to refer to the sheet for your current regional and find the data there for the teams which is not that hard. Actually I think it is more convenient to have all the teams you are competing with at your regional all on one page than to have to query one team at a time. The Query was meant to be printed out before you get to the regional or to be printed out after your scouts take pictures of all the robots on Thursdays.

Thanks for the suggestions.

Ed Law

Team 1746 13-03-2009 22:19
Re: paper: New Scouting Database from Team 2834
 
Peachtree regional is not updating. All other regional seem to be updating OK.

Error message

run time error 13

type mismatch

Ed Law 14-03-2009 20:14
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Team 1746 (Post 835634)
Peachtree regional is not updating. All other regional seem to be updating OK.

Error message

run time error 13

type mismatch
The reason Peachtree is not updating is because they did not broadcast the ranking information. All the links in the spreadsheet are correct.

Phyrxes 14-03-2009 22:11
Re: paper: New Scouting Database from Team 2834
 
So incase anybody was wondering this spreadsheet doesn't appear to work with OpenOffice.org Calc.

Ed Law 15-03-2009 00:45
Team 2834 Scouting Database (Week 3 Results and Week 4 Regionals Ready)
 
I have uploaded the Week 3 results. Please note that Peachtree Regional did not post the Standings. I calculated the standings based on match results. (You do that by using macro shortcut SHIFT-CRTL-R after you manually refresh the qualifying round data.) However if there are extra matches, it will still go into the standings because I would not know which match does not count. However it will not affect the OPR and CCWM data since all match data are used to calculate them.

I have also put in the links for Week 4 regionals and unless I made a mistake, they should all work. I need to write macros to create these links. It is very tedious to add the links manually and it is possible to make mistakes. Team 2834 will be going to Detroit District Competition. I will be busy this week since we have to work on the robot for 8 hours. I will not have time to change the database. However if someone finds an error in the spreadsheet, I will fix it as soon as I can.

I was looking at the results of the Cass Tech and I was amazed by the selection. It seems that the CCWM correlates extremely well with whiich teams were picked. Many teams that were ranked high were skipped. Except for one team, the teams that were skipped all had negative CCWM. It means either all Alliance Captains have access to this data (which I doubt that many teams actually use these data) or they can tell those teams were not that helpful to their partners in the qualifying round. Here are the numbers.

PHP Code:
Avg Score    OPR    OPR Rank    Avg WM    CCWM    CCWM Rank    Record    Regional Rank    Alliance    Finish
28.6    50.9    1    16.1    50.3    1    (11-0-1)    1 of 40    Alliance Captain  1    Regional Winner
24.6    38.9    3    8.6    27.2    3    (10-2-0)    2 of 40    # 1 pick    Regional Winner
23.4    33.0    4    7.3    17.0    4    (9-2-1)    3 of 40    Alliance Captain  2    Finalist
25.5    42.7    2    12.6    44.3    2    (8-3-1)    4 of 40    Alliance Captain  3    Semi Finalist
16.8    16.6    19    1.1    8.5    8    (7-3-2)    5 of 40    Alliance Captain  4    Semi Finalist
16.9    17.5    15    1.4    11.1    7    (8-4-0)    6 of 40    # 4 pick    Semi Finalist
18.8    17.0    17    2.0    2.7    18    (7-4-1)    7 of 40    Alliance Captain  5    Quarter Finalist
18.4    18.9    13    2.1    6.2    12    (7-4-1)    8 of 40    Alliance Captain  6    Quarter Finalist
16.4    14.2    24    0.3    0.1    21    (7-4-1)    9 of 40    Alliance Captain  7    Quarter Finalist
21.3    29.1    6    2.2    4.7    15    (7-4-1)    10 of 40    # 6 pick    Quarter Finalist
18.2    22.4    11    0.2    4.6    16    (7-4-1)    11 of 40    Alliance Captain  8    Quarter Finalist
21.2    25.9    8    4.1    8.2    9    (7-5-0)    12 of 40    # 7 or 10 pick    Quarter Finalist
17.3    14.7    22    -1.4    -7.8    29    (7-5-0)    13 of 40        
23.0    33.0    5    6.3    13.4    5    (7-5-0)    14 of 40    # 5 or 12 pick    Quarter Finalist
16.6    15.0    21    -0.3    -2.1    23    (7-5-0)    15 of 40    # 13 pick    Semi Finalist
17.3    11.0    31    -0.5    -11.5    30    (7-5-0)    16 of 40        
15.6    14.4    23    0.4    5.5    13    (7-5-0)    17 of 40    # 8 or 9 pick    Quarter Finalist
17.3    13.9    25    -0.5    -7.0    28    (6-5-1)    18 of 40        
17.3    16.3    20    0.0    0.5    20    (5-5-2)    19 of 40    # 12 or 5 pick    Quarter Finalist
15.9    6.4    35    -3.8    -19.2    37    (6-6-0)    20 of 40        
17.4    18.2    14    0.1    4.4    17    (6-6-0)    21 of 40    # 10 or 7 pick    Quarter Finalist
19.8    26.1    7    2.7    12.2    6    (6-6-0)    22 of 40    # 3 or 14 pick    Semi Finalist
16.8    11.6    30    0.8    -4.3    25    (5-5-2)    23 of 40    # 9 or 8 pick    Quarter Finalist
17.8    24.2    10    -0.1    8.1    10    (6-6-0)    24 of 40    # 11 pick    Quarter Finalist
16.6    13.2    27    -1.8    -6.5    27    (6-6-0)    25 of 40        
19.7    22.0    12    2.3    5.1    14    (5-7-0)    26 of 40    # 14 or 3 pick    Semi Finalist
18.4    24.6    9    0.6    6.6    11    (5-7-0)    27 of 40    # 2 or 15 pick    Finalist
16.3    17.1    16    -1.8    0.1    22    (5-7-0)    28 of 40    # 16 pick    Regional Winner
13.9    2.4    39    -2.4    -11.6    31    (5-7-0)    29 of 40        
16.4    13.4    26    -1.1    -3.0    24    (4-7-1)    30 of 40 
Ed Law

Ed Law 21-03-2009 22:35
Team 2834 Scouting Database (Week 4 results)
 
The database has been updated with Week 4 results. There are now results of 1301 teams who have played.

Also the missing data from previous weeks have all been posted and is reflected in the database.

I will add the links for Week 5 regionals next week when I have time. To add links before the events start is very time consuming unless I have a macro to do it in the future. Since the number of downloads is very low each week, I will only do the regionals on request only. PM me if you need it for a particular regional and I will email it to you. Otherwise I will post them after the regional starts on Friday. Thanks.

Ed Law

Phyrxes 21-03-2009 22:43
Re: paper: New Scouting Database from Team 2834
 
I was using this at our event this past weekend and had a couple people as about it, thanks again for writing it.

Ed Law 22-03-2009 14:51
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Phyrxes (Post 838927)
I was using this at our event this past weekend and had a couple people as about it, thanks again for writing it.
I am glad it was useful for you. The latest one is version 4c. All previous versions can be deleted. This one contains all of the latest data and the small bugs that I can find. I will be posting version 5 some time on Friday for Week 5 regionals.

Ed

Ed Law 27-03-2009 12:09
Team 2834 Scouting Database (Week 5)
 
Hi,

Week 5 events have started. A new file is posted with all the links. Some regionals are not posting properly. That is why the spreadsheet may not be working. Give it some time until every team has played once and hopefully they would have corrected it by then.

If you have any problems with any of the links, please let me know.

Ed

Ed Law 28-03-2009 23:12
Team 2834 Scouting Database (Week 5 Results)
 
Week 5 results are out. Rankings for Troy District Event are missing. I did include the Troy event based on rankings my program calculates. However the ranking may be slightly off because my RS numbers do not match the actual RS numbers which takes into account penalties that I do not have info of. Rankings for Connecticut Regional are also missing from the FIRST website but fortunately I have them stored on my spreadsheet as I update the rankings throughout the day.

Ed

MrWibbles 01-04-2009 19:32
Re: paper: New Scouting Database from Team 2834
 
(should I mention this here?)
Hi, first: I love the database! This isn't just an amazing tool for FRC info, it's a great example of how databases in general should look and feel.

Bug report: I can see the picture when the document loads, but it then disappears when the "enable automatic refresh?" pop-up appears, and is replaced with FALSE. The cell stays like that regardless of if I click Enable or Disable.

Ed Law 03-04-2009 01:19
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by MrWibbles (Post 844631)
(should I mention this here?)
Hi, first: I love the database! This isn't just an amazing tool for FRC info, it's a great example of how databases in general should look and feel.

Bug report: I can see the picture when the document loads, but it then disappears when the "enable automatic refresh?" pop-up appears, and is replaced with FALSE. The cell stays like that regardless of if I click Enable or Disable.
Yes, this is the place to report bugs. However that is not a bug. Before I get to that, I would recommend that you select disable automatic refresh.

The picture you see is the picture of our robot that was there when the Excel file was last saved. However when you open it up on your computer, it disappears because you do not have that picture file on your computer. Please read the instruction tab for details.

Basically you have to provide the picture files, name them as xxxx.jpg where xxxx is the team number. For example team 2834 would be 2834.jpg and team 33 would be 33.jpg etc.

Ed

Ed Law 03-04-2009 01:22
Team 2834 Scouting Database (week 6)
 
Hi,

Week 6 is ready and available to use. We will be at the Michigan State Championship. If you use this spreadsheet and have any questions on how to use it etc., please stop by our pit. Unfortunately EMU will not be providing internet access so we have to manually update the scores.

Ed

Vikesrock 03-04-2009 01:30
Re: paper: New Scouting Database from Team 2834
 
Thanks Ed! This is the first week I have a real use for this beyond curiosity/novelty!!

tsiersema 03-04-2009 01:36
Re: paper: New Scouting Database from Team 2834
 
so does anyone know of good scouting software? in need for state tourny this weekend

Fred Sayre 03-04-2009 13:22
Re: paper: New Scouting Database from Team 2834
 
I have read a bit about this method of generating OPR and I was curious about your input for the Seattle Regional. My team, 488 ranks quite low on OPR and average score, but multiple teams of scouts confirmed we were in the top few for average scoring per match including human and robot scoring. I would say 3 of the top 5 scorers are ranked below 10 for opr. There are teams ranked in the top 10 that we did not record scoring a ball during the entire event. Is this biased based on strength of schedule or something like that? I would love to use this tool in ATL, but having my own experience at a regional, I need to know how far to trust this data.

Thanks!

Ed Law 05-04-2009 00:55
Team 2834 Scouting Database (Week 6 Results)
 
Week 6 results have been updated. Troy standings is still not posted. If anyone has the ranking, please let me know.

I will update the spreadsheet again once the Championship divisions have been decided.

Team 2834 will be going to Atlanta. If anyone has any questions on how to use the spreadsheet or just want to talk about scouting, please stop by our pit.

Ed

Ed Law 07-04-2009 00:25
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Fred Sayre (Post 845379)
I have read a bit about this method of generating OPR and I was curious about your input for the Seattle Regional. My team, 488 ranks quite low on OPR and average score, but multiple teams of scouts confirmed we were in the top few for average scoring per match including human and robot scoring. I would say 3 of the top 5 scorers are ranked below 10 for opr. There are teams ranked in the top 10 that we did not record scoring a ball during the entire event. Is this biased based on strength of schedule or something like that? I would love to use this tool in ATL, but having my own experience at a regional, I need to know how far to trust this data.

Thanks!
Sorry it took so long to get back to you. Our team was competing last weekend at the Michigan State Championship. We qualified for Atlanta and I was busy yesterday and today securing money from various sources and arranging bus transportation and hotel at the last minute.

I looked at the Seattle Regional. Your team, 488, is ranked 22 out of 64 teams. Your OPR of 19.7 is ranked 23rd which is quite respectable. Another thing I look at is comparing the average score with OPR. The green color means the OPR is higher than the average score. It means your team is contributing more points to the matches than your alliance partners on average. I don't know if there were any penalties against your alliance for your matches. If there is, it will lower your OPR score.

Team 2660 is ranked 4th with an impressive record of 6 wins and 1 loss. However its OPR is only 12.2 which ranked 40th and they have a negative CCWM. You can PM me and tell me how they actually perform. They picked 2 very good partners though.

Team 1983's record of 3 wins and 4 losses ranked as 33rd. However their OPR is 39.4 and ranked number 1. Their CCWM is 9.4 which is quite high also. With the average score of 19.5 and average CCWM of -0.4, I can tell that this team had a tough schedule or some very weak partners because they scored most of the points and their partners dragged them down. The data seemed to show that they were picked number 1 which means other teams recognize them as a good team with some bad luck. I think the numbers are quite accurate in telling the story.

I would be careful with the data when there are 64 teams and each team only plays 7 matches. There is not enough of interaction between all the teams. I would put a +/- 5 on the OPR data.

That is the problem when there are many teams and some of them are not competitive. At the Michigan State Championship, most of the 64 teams are highly competitive so the data is a lot more accurate.

I will be at Atlanta. If you want to talk a little more about OPR and CCWM, please stop by our pit and ask for me.

Ed

Ed Law 07-04-2009 12:36
Re: paper: New Scouting Database from Team 2834
 
I received some questions through PM and I decided to answer them here so others can benefit from it also.

First of all, these OPR and CCWM should not replace watching matches and gathering match statistics if your team has the human resources to do it reliably.

OPR does not predict what a team (robot and human player) can score. It is the calculated contribution by that team on average to all the matches they were involved in to their alliance partners. A team that has high OPR score means that every time they are on the field, good things happen to that alliance meaning high score. Some of the possibilities are:

1) their robot score a lot of points
2) their human player score a lot of points
3) their presence allow their alliance partners to score a lot of points which they don't normally do as well.
4) they have on average stronger partners and weaker opponents by the luck of the draw than other teams.

A low OPR is just the opposite.

CCWM is the calculated contribution to the winning margins of the matches the team was involved in. About half the teams will have negative CCWMs and if you add up all the CCWMs of all the teams in the event, it will add up to zero. A negative CCWM means the team is a liability to their partners. They may be able to score points, but they get scored on more than they score on others and it may be because of penalties also. A team with negative CCWM should not be picked as alliance partners.

A team that has high CCWM means that every time they are on the field, good things happen to that alliance and in this case it means winning by a big margin. Some of the possibilities are:

1) they score more points on others than others score on them
2) their presence allow their alliance to score more points on others than others score on them. This could be from playing defense or help pin an opposing robot so their alliance partner can score more.
3) they do not incur much penalties.
4) they have on average stronger partners and weaker opponents by the luck of the draw than other teams.

In another scenario, a team with high OPR and negative CCWM could mean that they are a liability to their partners. They probably get scored on a lot more than they score on others but their presence helped their partners to score more than they normally do. To me, it raise a red flag about their game strategy. I hope this helps.

Ed Law

Ed Law 07-04-2009 23:57
Team 2834 Scouting Database 2009 Championship
 
I have updated the database to show which teams are going to Atlanta. When the divisions are annouced, I will create 4 separate tabs for them. There will be links to update results from the internet. I hope there will be internet access there. Otherwise I will have to update it manually again. Actually there are two macros to help you calculate approximate ranking and calculate OPR and CCWM. Read the instructions tab for details.

If you plan to use this at the World Championship and you have any questions, please stop by Team 2834's pit.

Ed

Ed Law 08-04-2009 13:33
Re: paper: New Scouting Database from Team 2834
 
Scouting database updated with Championship divisions. Links are not set up yet to update standings and match results. I also corrected the BAE results with the regional winners and finalists but I still don't have data of the final match.

I will post division strength in a separate post.

Ed

Rob 08-04-2009 14:20
Re: paper: New Scouting Database from Team 2834
 
Hi Ed,

Thanks for the info. The BAE finals match 3 score was 98-55.

Thanks,

Ed Law 08-04-2009 23:48
Re: paper: New Scouting Database from Team 2834
 
Oops, I forgot to change the sort macro in version 2 after I inserted the 4 championship divisions column. The sorting macro is now fixed in version 3. Sorry for the inconvenience.

Ed

Ed Law 17-04-2009 00:16
Re: paper: New Scouting Database from Team 2834
 
Version 4 is out. I put in all the matches and links to usfirst.org. This is a version you can use tomorrow and Saturday morning during the qualifying rounds. I couldn't get internet access today there, so we will have to update the match results by hand.

Good luck tomorrow.

Ed

Ed Law 17-04-2009 01:56
Re: paper: New Scouting Database from Team 2834
 
I just noticed that the Oregon results were wrong. It is too late for me to change it tonight. I will fix it Friday night. The OPR and CCWM are correct, only the alliance selection data was wrong.

That wrong data has been there for a while. If anybody notice anything wrong, please post it here to let me know so I can fix it. Thanks.

Ed

Ed Law 17-04-2009 22:39
Re: paper: New Scouting Database from Team 2834
 
Version 5 is out. I regenerated all the regional data. The problem was some of the regional data that FIRST published had the elimination round schedule all mixed up instead of following the order with quarterfinal, then semifinal then final. My program figure out the selection order based on the elimination round matches and did not anticipate out of order listing and posted some wrong alliance selection info.

I also have updated championship info as of end of Friday. If you want to use this spreadsheet tomorrow, just enter the match results manually and follow the instructions on how to use it. You can also look for me and I will show you how to use it. I will be between the pit and the Curie stand sitting right in front of the Thunder Chickens.

Ed

Ed Law 20-04-2009 13:05
Re: paper: New Scouting Database from Team 2834
 
Version 6 is out. This will be my final version for this year unless somebody notices an error that I need to fix.

The spreadsheet can now handle 5 events per team instead of 4 as quite a few Michigan teams went to 5 events (3 districts, state championship and Atlanta).

The average OPR and CCWM now includes the Atlanta divisions' results also.

There is a long list of improvement I plan to do for next year. Now is a good time to tell me what kind of improvement you would like to see.

Thank you for the encouragement from many of you as I will only continue to do this if people actually find it useful.

For offseason, we plan to go to MARC and Kettering. We will try to apply to go to IRI but it may be tough to get in as a rookie team even though we have a good record this year. I would be happy to add a sheet for IRI if anyone is interested.

Ed

ObedJ 17-02-2010 18:58
Re: paper: New Scouting Database from Team 2834
 
This is Obed, a rookie member of 354, wondering how do you put information the scouting database.

Ed Law 21-02-2010 03:09
Team 2834 Scouting Database for 2010
 
The 2010 Scouting Database from Team 2834 is out. I added links for Week One only. I have to do it manually and it is very time consuming. I wrote a program last summer to add links automatically but it is not working. With the changes in scoring this year, I don't know if they are changing the format. I am going to wait until past Week One before I do the rest. We are going to a Week One competition also so I will not have time to re-publish it on Friday if it does not work. I apologize in advance.

A few words about OPR and CCWM this year. There were some discussions about whether OPR and CCWM have any meaning this year. So far I stayed out of that discussion partly because I was busy working with my team to build our robot. Also I do not want to get into a debate. If you do not think OPR and CCWM is useful this year, don't read on. I am not trying to convince you.

So here is my thought on OPR and CCWM. The number of goals scored this year is meaningless because teams who have a big lead will try to score for their opponents. The winning margin is therefore meaningless also. Unlike last year where the offensive score was based on how many moon rocks and supercells were scored, this year the reported score is not based on goals. The reported score is actually the seeding score. Since teams will try to maximize their seeding score, they will try their best.

The interpretation of OPR this year has to be changed slightly. It is not a measure of how strong their offensive power or how many balls the team can score. It is a measure of how successful the team is to get a high seeding score for their alliance. This is why I think OPR is still meaningful but we have to interpret it differently.

On the other hand, I think CCWM has no meaning this year because of the way the coopertition points are given out. However, my program will still calculate and report CCWM. I do not want to change the program just for this year.

Ed Law 21-02-2010 21:11
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by ObedJ (Post 922398)
This is Obed, a rookie member of 354, wondering how do you put information the scouting database.
I am sorry but you need to clarify what you would like to ask. I am not sure what you mean.

All the data came from usfirst.org. I wrote a number of macros and use Excel functions where convenient to organize the data. You can PM me if you would like more information.

Ed Law 27-02-2010 01:35
Re: paper: New Scouting Database from Team 2834
 
I also updated the regionals and districts which teams are attending. Hope that helps.

Ed Law 14-03-2010 21:22
Re: paper: New Scouting Database from Team 2834
 
OPR results from Week 2 is out. You can download the data from

http://www.chiefdelphi.com/media/papers/2174

If there is any mistake, please let me know.

I need to make some corrections on my recent post on 2/21. The reported score is not the seeding score as I thought before week 1. It is the actual number of goals minus penalties.

I am a little surprised how good the correlation is of OPR to results prediction. At most regionals/districts, the prediction is 70-80% accurate. It also correlates very well with the actual alliance selection. Look at the data and you can be the judge.

Also the correlations are better for Week 2 than Week 1. It probably has to do with the additional 5 seeding points for a win.

Ed Law 15-03-2010 12:15
Re: paper: New Scouting Database from Team 2834
 
Forgot to mention that Wisconsin Regional ranking data is missing. I did include the OPR numbers for that event. If anybody has information on when the data will be posted, please let me know.

Vikesrock 15-03-2010 12:32
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 937319)
Forgot to mention that Wisconsin Regional ranking data is missing. I did include the OPR numbers for that event. If anybody has information on when the data will be posted, please let me know.
The information was up during the event and then disappeared after. The same thing happened for Peachtree both last year and this year.

I have the order, SP and CP points for the event still on my phone, if you want them let me know (PM with your email would probably be best) and I will type them up and send them to you.

EDIT: I didn't realize the Peachtree data had reappeared, last year it never came back up (still missing to this day)

Ed Law 15-03-2010 15:49
Re: paper: New Scouting Database from Team 2834
 
Thanks to "Vikesrock" who sent me the Wisconsin data, I have added the ranking information, Seeding Score and Coopertition Bonus but I am still missing the Hanging Points. He mentioned that the Seeding Score may have a slight error since 2194 is listed with the highest Coop points and 111 got the award. If anyone has more information, please let me know.

cygnus.x1 18-03-2010 00:46
Re: paper: New Scouting Database from Team 2834
 
Thanks for reviving this spreadsheet Ed!

It's working great. We're off to SVR tomorrow.

Joe Ross 22-03-2010 12:43
Re: paper: New Scouting Database from Team 2834
 
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 22-03-2010 22:35
Week 3 data is posted
 
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 23-03-2010 11:51
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Joe Ross (Post 941043)
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 30-03-2010 20:46
Re: paper: New Scouting Database from Team 2834
 
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 30-03-2010 22:25
Re: paper: New Scouting Database from Team 2834
 
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 30-03-2010 22:42
Re: paper: New Scouting Database from Team 2834
 
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 31-03-2010 12:32
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by rulesall2 (Post 946039)
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.

Ed Law 31-03-2010 13:09
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by XaulZan11 (Post 946051)
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.


All times are GMT -5. The time now is 08:42.
Page 1 of 3 1 23 >
Show 100 post(s) from this thread on one page

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © Chief Delphi