View Full Version : paper: New Scouting Database from Team 2834
Thread created automatically to discuss a document in CD-Media.
New Scouting Database from Team 2834 (http://www.chiefdelphi.com/media/papers/2174?) by Ed Law
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.
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
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?
What size would the entire file be if you zipped it first?
If you email (rcthekid1323@gmail.com)it to me I could upload it for you :yikes:
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
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:
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":
=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-
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:
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":
=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
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-
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
After:
Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.5, AC.Top + 2.5, 329, 250)
Add:
P.ScaleHeight 1, True
P.ScaleWidth 1, True
P.Height = 250
That should keep the aspect ratio of the pictures.
-Oris-
After:
Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.5, AC.Top + 2.5, 329, 250)
Add:
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
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:
P.ScaleHeight 1, True
P.ScaleWidth 1, True
P.Height = 250
with:
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-
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
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-
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
08-01-2009, 15:59
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?
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.
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
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
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/showthread.php?t=75272
-Oris-
Killraine
05-03-2009, 13:04
Mmk, was planning on integrating the two systems if there wasn't already an integrated one available.
Thanks
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
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
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
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
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, 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
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
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'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
09-03-2009, 17:12
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
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
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?
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
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
Thank you the directions make sense, now to find a free wifi hotspot near our next regional.
Killraine
11-03-2009, 13:46
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.
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
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
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
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.
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
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.
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
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/pictures/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:
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.
:P That was me
Could anyone write a program that downloads the picture at http://www.thebluealliance.net/tbatv/pictures/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:
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.
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
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
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
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
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.
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
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
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
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?
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
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
Peachtree regional is not updating. All other regional seem to be updating OK.
Error message
run time error 13
type mismatch
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.
So incase anybody was wondering this spreadsheet doesn't appear to work with OpenOffice.org Calc.
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.
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
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
I was using this at our event this past weekend and had a couple people as about it, thanks again for writing it.
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
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
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
(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.
(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
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
Thanks Ed! This is the first week I have a real use for this beyond curiosity/novelty!!
tsiersema
03-04-2009, 01:36
so does anyone know of good scouting software? in need for state tourny this weekend
Fred Sayre
03-04-2009, 13:22
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!
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
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
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
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
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
Hi Ed,
Thanks for the info. The BAE finals match 3 score was 98-55.
Thanks,
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
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
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
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
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
This is Obed, a rookie member of 354, wondering how do you put information the scouting database.
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.
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.
I also updated the regionals and districts which teams are attending. Hope that helps.
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.
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
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)
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
Thanks for reviving this spreadsheet Ed!
It's working great. We're off to SVR tomorrow.
Joe Ross
22-03-2010, 12:43
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?
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.
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
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
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
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/showthread.php?t=83814&highlight=alliance+selection
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.
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/showthread.php?t=83814&highlight=alliance+selection
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.
Week 4 data is published. Colorado data is missing from FIRST.
Week 5 data is published. I will publish the data again after championship division data is available. I will be in Atlanta and can only update these data at night at the hotel since there is no internet access at the competition.
1) Colorado data is now included.
2) Wisconsin data was republished by FIRST and is included.
3) For Midwest, I only manually fixed the data for Team 1732. Some other team's data may still be wrong.
4) For WPI, I tried to fix the data by manually adding the seeding points for the 6 teams from the last match. However, the order is still not right because there must have been some penalties that affected the points. I decided to leave it as is until FIRST fix the data.
5) I added a macro to replace one regional's data when there is a change instead of redoing all the regionals in the central database. This will make it easier for me if FIRST publish corrected data.
6) Israel data is not in the central database. The sheet for that regional is still available.
If you are aware of any corrected data that is republished, please let me know. If you want good data in this database, you need to work with your regional director and FIRST to get the data corrected. A lot of teams and TBA depend on these data.
There was a small error in Midwest data. When I switched the ranking of 1732 and 16 manually, I forgot to switch their OPR, CCWM etc. It is now corrected in version 2. This is one of the reasons why I don't like to change the data manually. There is always a chance of human error introduced.
Championship division data is now included. I will be posting divisonal strength tonight.
It seems that since I last posted, some teams dropped out and some teams were added. I will update it Thursday morning after the match schedule comes out and at the end of each day.
How many teams going to Atlanta will actually be using this database and update it in real time? If you do, please stop by our pit and say hi. I would love to talk to you and get some feedback.
cygnus.x1
13-04-2010, 21:56
The SpaceCookies (FRC1868) will be in Atlanta and I'll be using it with the team. Not sure if we'll have real-time updating or not. Depends on the network connectivity there.
Thanks for updating the spreadsheet!
I have updated the Championship data with Thursday results for all divisions. I have also set up all the links including the elimination rounds. So if you have access to the internet at the Championship, you can just refresh the data and everything will be updated automatically. If you have any questions or need anything, please PM me or stop by our pit.
I updated the Championship data with Friday results. Good luck to all tomorrow!
Jacob Plicque
17-04-2010, 08:12
Thanks Ed for all the work that you have done on the scouting database. It is being used by Team 86 in Archimedes. I enjoyed meeting you in person on Friday.:)
I updated the final results of each division and recalculated the OPRs.
Nathan Streeter
19-04-2010, 09:26
I updated the final results of each division and recalculated the OPRs.
Excellent, thank you! It's been really cool to use this spreadsheet for a statistical analysis of teams - our scouting team even used it for "pre-scouting" the Archimedes teams. Do you intend to update the spreadsheet by adding the championship data to the list of event ratings in the query sheet? Thank you very much for sharing all this information!
Excellent, thank you! It's been really cool to use this spreadsheet for a statistical analysis of teams - our scouting team even used it for "pre-scouting" the Archimedes teams. Do you intend to update the spreadsheet by adding the championship data to the list of event ratings in the query sheet? Thank you very much for sharing all this information!
Hi Nathan,
Yes I do plan to include the championship division data to the Query sheet like last year. It is on my list of things to do.
I am also thinking about the possibility of publishing a true world ranking taking into account all the district/regional/championship data so all interactions will be taken into account. Then there is no argument about strong or weak events or whether the world ranking should be based on average, most recent or weighted average. Do you think there is interest in such data? If there is, then I will look into whether my program can handle solving such a hugh system of equationos.
Ed
Version 5 is out. This will be my final version for this year unless somebody notices an error that I need to fix.
The OPR and CCWM now includes the Atlanta divisions' results also.
This version also fixed errors from version 4. This year I am including the Michigan State Championship and Atlanta data to calculate the OPR and CCWM rankings.
quinxorin
30-08-2010, 19:55
Question: why is the OPR rank for teams listed in Worldrank (and on the query page) different from the rank on the OPR Results page? For example, OPR Results gives 67 a rank of 2, but the query and Worldrank gives them a rank of 4.
Question: why is the OPR rank for teams listed in Worldrank (and on the query page) different from the rank on the OPR Results page? For example, OPR Results gives 67 a rank of 2, but the query and Worldrank gives them a rank of 4.
Good observation! It was not a mistake.
On the sheet "Query", it gives the true World Ranking. If you go to cell M32, you will see that it is reading data from sheet "Worldrank". I felt that when somebody does a query of a particular team, they are probably more interested in the true world ranking, how they compare with other teams.
On the sheet "OPR results", it is still using the regional by regional calculation of OPR and then using either highest, most recent, simple average or weighed average OPR depending on the value used in cell G2. A blank value in G2 means using weighted average at week 6. My rationale in keeping those options is if somebody goes to that sheet that has OPR numbers of each regional, they are probably more interested in comparing between different regionals. To get the true world ranking of all the teams on one page, one can go to sheet "Worldrank".
I hope I answered your question.
quinxorin
31-08-2010, 00:03
It did.
Nathan Streeter
08-03-2011, 07:31
Any possibility that 2834 will be posting a 2011 spreadsheet weekly? I certainly would be interested in using it to better understand how teams are performing across all of FIRST! :-)
ok my email is cje36f@gmail.com
send me a message with the scouting database spread sheet please!
Sorry it took so long before posting this. We had back-to-back week one and week two events and we had to maintain the First in Michigan website updating the results, I barely have any time left.
I tried to publish the database in Excel 2003-2007 format but there was an error and it would not convert. Besides the file was too big. The 2010 format takes up less space and I also zipped it. If you are using 2003-2007 Excel, you can download a utility from Microsoft to read this. If you have any trouble, please post here. I also posted the World ranking.
If you find any error, please let me know.
Nathan Streeter
17-03-2011, 14:13
No problem at all - thanks a bunch for doing... it's really useful!
For those who didn't want to spend the time to download, unzip and open the file, here is the top ten true world OPR ranking at the end of week 2. It takes into account all qualifying round matches of every regional and district. There are enough teams that have played two events already so there are enough interactions that make the true world OPR different from the OPR calculated from just one event.
Rank Team Wins Losses Ties Played QS RS MP Avg Scr OPR OPR Rnk Avg WM CCWM CCWM Rnk Record
1114 16 1 0 16 30 33.81 128 30.52 66.11 1 18.98 51.58 3 (16-1-0)
111 16 0 0 16 32 30.94 132 31.54 62.79 2 21.23 51.31 4 (16-0-0)
148 15 1 0 15 28 19.73 135 28.24 60.02 3 21.33 53.60 1 (15-1-0)
33 18 1 0 18 34 21.94 114 24.31 57.20 4 16.80 53.39 2 (18-1-0)
987 18 4 0 18 28 37.11 135 24.17 53.28 5 8.85 14.83 73 (18-4-0)
217 18 3 0 18 30 25.28 122 26.72 47.13 6 17.22 38.71 9 (18-3-0)
179 16 0 0 16 32 21.25 112 24.52 46.61 7 17.44 49.83 5 (16-0-0)
1676 15 1 0 15 28 23.87 124 25.84 45.53 8 17.47 41.46 6 (15-1-0)
254 18 2 0 18 32 29.00 135 25.37 45.13 9 14.33 33.08 12 (18-2-0)
1126 17 4 0 17 26 38.71 122 23.43 44.87 10 7.57 23.08 34 (17-4-0)
Week 3 results are included.
In order to maximize interactions between teams in the calculations of World Ranking, the elimination rounds were included. I think this approach is valid since some teams attend more events than others anyway. The more matches we take into account, the more accurate the results will be.
For individual districts and regionals, only qualifying matches were used to calculate OPR and CCWM in order not to skew the results. This is consistent with the belief of the CD community.
The following is the top ten World Ranking.
Team Wins Losses Ties Played QS RS MP Avg Score OPR OPR Rank Avg WM CCWM CCWM Rank Record
1114 16 1 0 16 30 33.81 128 30.52 65.94 1 18.98 51.80 5 (16-1-0)
111 16 0 0 16 32 30.94 132 31.54 64.43 2 21.23 52.91 2 (16-0-0)
148 15 1 0 15 28 19.73 135 28.24 60.03 3 21.33 51.88 4 (15-1-0)
2054 18 2 0 18 32 28.56 135 28.37 58.08 4 17.65 62.77 1 (18-2-0)
33 18 1 0 18 34 21.94 114 24.31 55.68 5 16.80 52.59 3 (18-1-0)
217 37 5 0 37 64 27.65 122 27.75 53.21 6 17.84 46.54 8 (37-5-0)
987 18 4 0 18 28 37.11 135 24.17 52.81 7 8.85 14.26 120 (18-4-0)
1678 16 1 0 16 30 15.81 99 21.19 49.37 8 15.63 44.03 9 (16-1-0)
456 17 2 0 17 30 29.00 121 24.98 46.84 9 14.31 37.17 18 (17-2-0)
179 16 0 0 16 32 21.25 112 24.52 46.74 10 17.44 49.61 6 (16-0-0)
Jacob Plicque
23-03-2011, 00:33
Ed,
There is something wrong with the world rank sheet. It shows Team 86 with 16 wins and 6 losses. We only have 9W-6L with the playoff games. Our only competition was the Florida Regional.
Jacob
:confused:
Ed,
There is something wrong with the world rank sheet. It shows Team 86 with 16 wins and 6 losses. We only have 9W-6L with the playoff games. Our only competition was the Florida Regional.
Jacob
:confused:
Jacob,
Thanks for catching the mistake. All the data of the World Ranking is correct except for the column Wins (column C). The program overwrote that column with the number of matches played (column F) by mistake which is why the two columns had the same value. I have posted version 3 to correct the mistake.
Here is the corrected Top 10. Again, only the column Wins posted before was wrong.
Team Wins Losses Ties Played QS RS MP Avg Score OPR OPR Rank Avg WM CCWM CCWM Rank Record
1114 15 1 0 16 30 33.81 128 30.52 65.94 1 18.98 51.80 5 (15-1-0)
111 16 0 0 16 32 30.94 132 31.54 64.43 2 21.23 52.91 2 (16-0-0)
148 14 1 0 15 28 19.73 135 28.24 60.03 3 21.33 51.88 4 (14-1-0)
2054 16 2 0 18 32 28.56 135 28.37 58.08 4 17.65 62.77 1 (16-2-0)
33 17 1 0 18 34 21.94 114 24.31 55.68 5 16.80 52.59 3 (17-1-0)
217 32 5 0 37 64 27.65 122 27.75 53.21 6 17.84 46.54 8 (32-5-0)
987 14 4 0 18 28 37.11 135 24.17 52.81 7 8.85 14.26 120 (14-4-0)
1678 15 1 0 16 30 15.81 99 21.19 49.37 8 15.63 44.03 9 (15-1-0)
456 15 2 0 17 30 29.00 121 24.98 46.84 9 14.31 37.17 18 (15-2-0)
179 16 0 0 16 32 21.25 112 24.52 46.74 10 17.44 49.61 6 (16-0-0)
The scouting database has been updated to include Week 4 results.
The top 10 true world ranking is as follows.
Team Wins Losses Ties Played QS RS MP Avg Score OPR OPR Rank Avg WM CCWM CCWM Rank Record
1114 32 1 0 33 64 37.55 150 33.53 72.40 1 20.88 59.30 1 (32-1-0)
111 31 2 0 33 62 39.64 135 32.62 63.83 2 18.98 47.91 7 (31-2-0)
148 14 1 0 15 28 19.73 135 28.24 59.68 3 21.33 53.66 4 (14-1-0)
2054 32 5 0 37 64 27.89 135 27.10 57.15 4 16.95 56.67 2 (32-5-0)
33 17 1 0 18 34 21.94 114 24.31 56.22 5 16.80 54.01 3 (17-1-0)
987 14 4 0 18 28 37.11 135 24.17 52.28 6 8.85 15.55 124 (14-4-0)
217 32 5 0 37 64 27.65 122 27.75 51.70 7 17.84 45.36 9 (32-5-0)
2363 15 1 0 16 30 26.81 116 25.19 51.07 8 14.65 41.86 13 (15-1-0)
2056 29 6 0 35 58 27.06 150 29.26 50.51 9 18.95 44.76 10 (29-6-0)
1678 15 1 0 16 30 15.81 99 21.19 49.45 10 15.63 44.26 11 (15-1-0)
1114 continues to increase their OPR and CCWM and is now ranked #1 in both. Their MP is an amazing 150 from a match this weekend.
Jacob Plicque
27-03-2011, 18:38
Ed,
Your data is also useful in determining the strongest regional. Using the average OPR of the top 24 robots a strength rating is created as follows:
1 Midwest, 28.02
2 Oregon, 26.98
3 WashingtonDC, 26.12
4 LosAngeles, 25.09, 17.41, Average
5 AnnArbor, 23.28, 4.76, Std Dev
6 KansasCity, 22.58, 31.71, +3 Std Dev
7 WestMichigan, 21.40, 3.12, -3 Std Dev
8 FingerLakes, 20.5
9 NewJersey, 20.48
10 Wisconsin, 20.15
11 SanDiego, 20.07
12 BAE, 19.9
13 Chesapeake 19.05
14 Niles 19.00
15 Pittsburgh 18.16
16 Sacramento 17.92
17 Waterloo 17.78
18 Alamo 17.8
19 Palmetto 17.73
20 LoneStar 17.19
21 Detroit 16.68
22 Boilermaker 16.61
23 Florida 16.25
24 Oklahoma 15.8
25 NewYorkCity 15.37
26 WPI 15.21
27 LongIsland 15.18
28 Bayou 14.9
29 St.Louis 13.97
30 Kettering 13.6
31 SeattleCascade 12.52
32 LakeSuperior 12.4
33 Peachtree 12.1
34 Waterford 11.86
35 SeattleOlympic 10.8
36 Israel 10.4
37 TraverseCity 9.97
38 Arizona 9.06
:D
Jacob,
I have seen studies done with several years of data that shows the average OPR increasing as time goes on so I would be careful in saying one regional/district is stronger than another if they are not in the same week.
Take our team as an example. Our week 1 event OPR was 5.9 because the robot was not working properly. Our week 2 event OPR was 32.5 because the robot was working and we also got the minibot to work half way through the event.
For teams that do not have a practice robot to practice after ship date, their OPR usually improves as drivers get more experience. There can be many other reasons teams improve their OPR from one event to the next. Most teams plateau out after their 3rd event. Of course some teams' OPR goes down as their robot wore out and start to break down.
One difference between powerhouse teams and teams like ours is they start their season with very high OPR probably because they have less things to "debug". For us we still have a lot of things to learn. We have a lot of "first" this year. First time using banebot motor, first time using gyro, first time using potentiometer, first time using Mecanum wheels and programming it, first time programming a field oriented drive, first time designing and building a 4 bar link arm, first time trying a magnetic encoder, first time trying a compass sensor. We are very lucky to have Team 33 helping us but when you are doing so many things for the first time, bad things are going to happen.
Jacob Plicque
27-03-2011, 23:02
Ed,
I agree that my method does not reflect improvement from Regional to Regional. However, 69.5% of the teams only attend one regional, 28.9% of the teams attend two regionals, and 1.6% attend three or more regionals. The quick and dirty list does show dominate regionals that are ~3 times stronger than others. The use of the world rank data would probably produce a fairly accurate strength estimate. While the correlation is unproven, I'll bet the the winners of stronger regionals are dominating in the FIRST Championships.
:cool:
bobosalad
28-03-2011, 00:46
can you email me at tomasicenogle@gmail.com with the definitions of every part? EX: RS OPR ect... can i just get a list of everything? Thank you.
QS and RS definition can be found in Section 5 of the manuals.
5.3.3 Qualification Score (QS)
Qualification points are awarded to each TEAM at the completion of each qualification match and are dependant on the final score:
Each TEAM on the winning ALLIANCE will receive two (2) qualification points.
Each TEAM on the losing ALLIANCE will receive zero (0) qualification points.
In the event of a tied score, all six TEAMS will receive one (1) qualification point.
The total number of qualification points earned by a TEAM throughout their qualification matches will be their qualification score.
5.3.4 Ranking Score (RS)
Each TEAM on the winning ALLIANCE will receive a number of ranking points equal to the unpenalized score (the score without any assessed penalties) of the losing ALLIANCE.
Each TEAM on the losing ALLIANCE will receive a number of ranking points equal to their final score (with any assessed penalties).
In the case of a tie, all participating TEAMS will receive a number of ranking points equal to their ALLIANCE score (with any assessed penalties).
The total number of ranking points earned by a TEAM throughout their qualification matches, divided by the number of matches played (excluding any SURROGATE matches), then truncated to two decimal places, will be their ranking score.
Note: because your ranking score is derived directly from the match scores of the losing ALLIANCES in the matches you play, it is in your best interest to support your opponents and win by helping each ALLIANCE score as many points as possible.
OPR stands for Offensive Power Rating. CCWM stands for Calculated Contribution to Winning Margin. The explanation can be found in this white paper. http://www.chiefdelphi.com/media/papers/2174
Look for the file "Team 2834 Scouting Database Presentation 2010.pdf"
shocker4256
31-03-2011, 20:17
Is there any reason why I can't seem to populate the CT match list correctly? I get times and team numbers and scores for Qualifications but they seem random....
edit: It also seems like the Qualification data that it is pulling is going where the elimination data normally is
Is there any reason why I can't seem to populate the CT match list correctly? I get times and team numbers and scores for Qualifications but they seem random....
edit: It also seems like the Qualification data that it is pulling is going where the elimination data normally is
The links for future events are still pointing to last year's events. I cannot setup the new links ahead of time when the web pages did not exist until Thursday or Friday. This was possible before in Excel 2003, but this does not seem to be possible with Excel 2007 and 2010. This will be my summer project if I have time to automate this as much as possible so I can set them up quickly. Doing it manually is quite tedious when there are so many events.
People who knows Excel well enough are able to change the links themselves to point to the current data.
Based on requests, I have set up the links for Connecticut, Smoky Mountain and Troy for people to use. It is in version 5b. Good luck at the competition this weekend.
Keep in mind that the OPR numbers will appear very low until more matches are completed. But you can kind of scale it up based on number of matches played.
Here is Version 5c that takes care of the low predicted OPR issue but it will require mroe matches played before it will work. Otherwise the matrix is ill conditioned and you will get an error message.
Edit: It works for all 3 locations now.
Week 5 results are out. It is in version 5e.
Caution: There are some discrepancies that has not been resolved in the data from usfirst.org website.
Silicon Valley finals match score not posted
North Star finals match results incomplete
Troy match 78 results not included in QS numbers
When resolved, they will be updated in future posting.
I will be busy at Michigan State Championship and will not be able to setup links for other regionals for Week 6. I will post results of Week 6 after this weekend.
Vikesrock
04-04-2011, 00:29
North Star finals match results incomplete
Oddly enough they are. Match 2 was actually a 0 to 0 win for the Blue Alliance. Blue ended with a score of 0 after penalties, but Red had a DQ so they lost, forcing a third and deciding match.
Oddly enough they are. Match 2 was actually a 0 to 0 win for the Blue Alliance. Blue ended with a score of 0 after penalties, but Red had a DQ so they lost, forcing a third and deciding match.
Thanks for letting me know. That was strange. I can't tell and my spreadsheet can't tell who won that match when it is 0-0. I thought that was a tie and required a 4th match that was not posted. I knew the outcome from the Awards page.
Thanks for letting me know. That was strange. I can't tell and my spreadsheet can't tell who won that match when it is 0-0. I thought that was a tie and required a 4th match that was not posted. I knew the outcome from the Awards page.
Can you manually edit the spreadsheet to have something like 0.000001 as a score? I am not familiar with the spreadsheet and do not have Excel installed on this computer otherwise I would check.
Week 6 results are out. All previous missing and wrong data from FIRST have been corrected and incorporated in this version. Keep in mind that the True World Ranking is from the whole season's performance and not an indication of strength going into Championship. The latest OPR may be a better indication of that.
I will be posting another file once the division data is out. I will also add the web links to the divisions on Thursday so teams can update the OPR numbers during the competition.
Let me know if you have any questions.
Travis Hoffman
13-04-2011, 21:45
Week 6 results are out. All previous missing and wrong data from FIRST have been corrected and incorporated in this version. Keep in mind that the True World Ranking is from the whole season's performance and not an indication of strength going into Championship. The latest OPR may be a better indication of that.
I will be posting another file once the division data is out. I will also add the web links to the divisions on Thursday so teams can update the OPR numbers during the competition.
Let me know if you have any questions.
Like! Ahead of Saturday, too.
techvikesmom
14-04-2011, 10:28
Where can I find your spreadsheet?? I have found in the previous weeks and I would love to find it today!! Thanks for the all the great input!!
Vikesrock
14-04-2011, 10:38
Where can I find your spreadsheet?? I have found in the previous weeks and I would love to find it today!! Thanks for the all the great input!!
If you go the very first post in this thread and click the link you will be brought to a page with a number of different versions of the database on it. The latest one is "Team 2834 2011_Scouting_Database week 6 "
Here is another copy of the link in the first post:
http://www.chiefdelphi.com/media/papers/2174?
Here is a direct download link for the latest version:
http://www.chiefdelphi.com/media/papers/download/3056
techvikesmom
14-04-2011, 21:02
thank you for the links!!!
The divisions data is now included. I fixed the calculation for weighted average for 2011. You can select the option you prefer to rank teams.
Today is tax return due date. I looked at the number of downloads for the presentation file that explains the scouting database. Guess what the number is. (1040)
Travis Hoffman
18-04-2011, 11:19
The divisions data is now included. I fixed the calculation for weighted average for 2011. You can select the option you prefer to rank teams.
Today is tax return due date. I looked at the number of downloads for the presentation file that explains the scouting database. Guess what the number is. (1040)
Thanks, Ed!
Can you please explain in more detail the calculation on the WM Results tab, column G, "Wt. Avg. CCWM", when calculated in "default" mode? How is that number obtained? How is the weighting done?
Here are your Top 25, sorted by that metric:
Robowranglers 148 1 68.0 Newton
Beach Bots 330 2 59.3 Archimedes
OP Robotics 2056 3 58.4 Curie
Simbotics 1114 4 57.4 Galileo
HIGHROLLERS 987 5 56.5 Curie
WildStang 111 6 53.8 Galileo
Swartdogs 525 7 52.7 Newton
Triple Helix 2363 8 52.1 Curie
Spartonics 1503 9 51.8 Newton
ThunderChickens 217 10 51.5 Newton
Cheesy Poofs 254 11 51.2 Galileo
Citrus Circuits 1678 12 50.9 Curie
The Children of the Swamp 179 13 49.8 Newton
Team Titanium 1986 14 49.0 Curie
Cyber Blue 234 15 47.2 Newton
Duluth East Daredevils 2512 16 46.9 Archimedes
Team Driven 1730 17 46.8 Newton
Delphi E.L.I.T.E. 48 18 46.4 Galileo
Miracle Workerz 365 19 45.9 Curie
Robonauts 118 20 45.7 Archimedes
Kinetic Knights 781 21 45.5 Archimedes
North STARS 1771 22 45.0 Galileo
Team Tators 2122 23 44.3 Newton
Iron Lions 967 24 43.6 Galileo
WiredCats 2415 25 43.3 Newton
Thanks, Ed!
Can you please explain in more detail the calculation on the WM Results tab, column G, "Wt. Avg. CCWM", when calculated in "default" mode? How is that number obtained? How is the weighting done?
Here is the quick answer directly from the macro.
Case 2 To 7
Range("G1").Select
ActiveCell.Cells = "Wt.Avg" + cword
For i = 1 To numrow
rscale = 0
rvalue = 0
For j = 1 To numcol
If (a(i, j) <> "") Then
If (a(i, j) <> 0) Then
rscale = rscale + 1 / (mcal - week(j))
rvalue = rvalue + a(i, j) / (mcal - week(j))
End If
End If
Next j
If (rscale <> 0) Then
rrvalue(i) = rvalue / rscale
Else
rrvalue(i) = -99
End If
Next i
When using default, mcal=7 means the weighed average is projected to Week 7. Basically the idea is earlier weeks has less weight than later weeks based on number of weeks to the projected week.
To translate this to English for the exact formula, it will have to wait until I have time after work.
Travis Hoffman
18-04-2011, 15:50
VBA fun
Thanks, Ed. I thought it was a "what have you done for me lately" kind of deal.
Another question, one you've been asked before, likely. When I attempt to open your spreadsheet in Excel 2003 (I have the Office 2007 compatibility pack), it always seems to freeze up.
Is there a way to get your spreadsheet to work as an Excel 2003 xls, or perhaps is there a way to re-save it in another 2007 format such that it can be opened in 2003?
The Lucas
18-04-2011, 17:17
VBA fun
Thanks, Ed. I thought it was a "what have you done for me lately" kind of deal.
Another question, one you've been asked before, likely. When I attempt to open your spreadsheet in Excel 2003 (I have the Office 2007 compatibility pack), it always seems to freeze up.
Is there a way to get your spreadsheet to work as an Excel 2003 xls, or perhaps is there a way to re-save it in another 2007 format such that it can be opened in 2003?
I assume it is a space issue since Ed already has to zip it for CD to accept it and Excel 2003 format would be significantly larger. It would be nice if someone could figure out what part of the spreadsheet crashes the 2003 converter (I suspect the OPR Results sheet with all its buttons and some of the colors don't exist in Excel 2003 :rolleyes: ).
I have had success opening the spreadsheet in a new Windows version of OpenOffice. I was then able to copy (to Excel 2003 format) the WorldRank sheet and all its precious data :)
VBA fun
Thanks, Ed. I thought it was a "what have you done for me lately" kind of deal.
Another question, one you've been asked before, likely. When I attempt to open your spreadsheet in Excel 2003 (I have the Office 2007 compatibility pack), it always seems to freeze up.
Is there a way to get your spreadsheet to work as an Excel 2003 xls, or perhaps is there a way to re-save it in another 2007 format such that it can be opened in 2003?
I tried many different ways to save it to Excel 2003 format but it crashed every time no matter what I did. I tried taking out all the colors and macros that I can find but it still crashed.
Yes it was a space issue. The file got too big with more teams and more events. I also added the World ranking sheet.
Does anybody know what the file size limit is?
Thanks, Ed!
Can you please explain in more detail the calculation on the WM Results tab, column G, "Wt. Avg. CCWM", when calculated in "default" mode? How is that number obtained? How is the weighting done?
The weighted average is calculated this way. This year there are 6 weeks of competition. By default, the weighted average is projected to the week after which is 7.
The OPR from each event is scaled by multiplying the reciprocal of the number of weeks between the event and week 7. For example if the event was in week 3, then the OPR (x) will multiply by 1/4. Let's say this team also attended a Week 5 event, that OPR (y) will multiply by 1/2. The weighted average will be (x/4+y/2)/(1/4+1/2). In this case, the week 5 event will have double the weight than the week 3 event since x is divided by 4 and y is only divided by 2.
I hope this makes sense. There is nothing special about this way of assigning weight. There can be many other ways to do it. The idea is the events closer to week 7 should have a higher weight than events that are earlier. I did it this way out of simplicity and convenience. You can use a quadratic function or exponential function if you wish.
Championship version 2 is out. Good news, all 4 divisions are now linked to FIRST website. You can just refresh the pages when you are at championship and everything should work. Remember each team has to play at least 3 matches before the OPR/CCWM can be calculated. And the numbers will start to stabilize as more matches are played.
I finally figured out that in Excel 2007/2010, I can still put in the link to web pages that I know the URL but the page does not exist yet. However I still need to figure out how to automate it like in Excel 2003. Microsoft changed the way how it is done and the old macro does not work anymore. This will be one of my off-season project.
By the way, I will be in St. Louis on Friday and Saturday without my team. I am the proud father of a Dean's List finalist who will be there with me as part of the 6 finalist from Michigan. I will be updating the OPR results regularly on my computer. If anybody plans to use this spreadsheet at the Championship and would like help, PM me and I will give you my cell phone number. My version of the spreadsheet has some goodies that may help your scouting team. I really have nothing to do the whole weekend there. I would be glad to help out any team if needed.
I did not realize they added some teams. Version 2b reflects the changes.
PSHRobotics
29-04-2011, 20:16
Thank you so much for the work you have put in!
Although it is not that big of a deal for me at the moment, I do want to let you know for other people and for the future. When I use ctrl-shift-p to refresh, I get an error that says:
Run-time error '1004':
Application-defined or object-defined error
If you need any help debugging I'd be happy to help, although my experience with complex excel functions and visual basic is very limited.
Edit: I did change the shortcut to ctrl-u and the same problem occurred.
Vikesrock
29-04-2011, 20:19
Thank you so much for the work you have put in!
Although it is not that big of a deal for me at the moment, I do want to let you know for other people and for the future. When I use ctrl-shift-p to refresh, I get an error that says:
If you need any help debugging I'd be happy to help, although my experience with complex excel functions and visual basic is very limited.
Edit: I did change the shortcut to ctrl-u and the same problem occurred.
The links for the division data are wrong. If Ed doesn't see this and post a fixed version, I can post one when I get to my hotel tonight, probably around 11.
I did realize the links were wrong. I will be posting the updated links in a few minutes.
Version 3 is out. It contains data through Friday matches at Championship.
I put in the wrong links because the links were put in before the URL were available. For regionals/districts, event codes were used in the URL. I forgot that for Championship, the 4 divisions use the full name in the URL instead of the abbreviation code that is used on frclinks. Sorry about that. If anybody find anything else that is wrong, please let me know.
Vikesrock
29-04-2011, 23:52
Version 3 is out. It contains data through Friday matches at Championship.
Thanks Ed!
Version 4 is out. This should be the final version for this year unless somebody finds a mistake. I updated the world's ranking also. 1114 is still #1 in OPR world ranking and #2 in CCWM world ranking. 148 is #2 in OPR world ranking and #1 in CCWM world ranking.
Guess who has the highest OPR out of all 4 divisions during qualifying matches (63.4). (To see answer, highlight space to the right of Answer)
Answer: 217
Guess who has the Most Win during the whole season (71)
Answer: 217
Guess who has the Highest Win to Loss difference (55)
Answer: 217
Awesome offensive machine
Thanks, Ed. I thought it was a "what have you done for me lately" kind of deal.
Another question, one you've been asked before, likely. When I attempt to open your spreadsheet in Excel 2003 (I have the Office 2007 compatibility pack), it always seems to freeze up.
Is there a way to get your spreadsheet to work as an Excel 2003 xls, or perhaps is there a way to re-save it in another 2007 format such that it can be opened in 2003?
I finally was able to save it in 2003 format. It is version 4b. After I zipped the file, the file size is actually smaller than the zipped 2007 version. Quite surprising.
The 2012 Scouting Database spreadsheet is ready. It is at http://www.chiefdelphi.com/media/papers/2174
I created links of standing and match results for all the events already so all you have to do is refresh it at the event. I will test it to make sure they work during the Week 1 events. This is the first year we are not going to a Week 1 event so I will have more time to check the results and post them immediately afterwards.
I know there are discussions of OPR before or after penalties. I will try to address them at a later time.
Week 1 results are posted. It took a little longer to get this done because FIRST changed the format of the team standing page and I have to modify quite a bit in the macro.
I also fixed all the links for all future events. They should be working now.
Good News. I was able to get the OPR of Hybrid, Bridge, Teleop and Coopertition separately. It is much easier than I thought. It took about 20 lines of code and 20 minutes. I put the OPR results in each event. I put it all the way to the right so it does not mess up the columns of other data.
Would somebody please compare my numbers with numbers published by others to see if it is consistent?
To get the True World OPR for each individual component will take more work. I will add it in the next few weeks.
Week 2 results are out. New true world ranking is posted. The highest OPR is now 36.71 from Team 2056 folowed closely by Team 67 with OPR of 36.16
All the links of week 2 events that I put in last week worked. Just use Cntl-Shirt-P to run the macro and you will get the overall OPR as well as OPR for hybrid, bridge, teleop and coopertition during the event.
Mike Starke
17-03-2012, 23:40
Hey Ed!
First off, thank you SO MUCH for doing this!
Secondly, would it be at all possible to have the file available as a ".xls"? My Mac doesn't seem to like the .xlsx
Thanks! If it works better for you to email it to me, shoot me a DM!
Thanks!
Hey Ed!
First off, thank you SO MUCH for doing this!
Secondly, would it be at all possible to have the file available as a ".xls"? My Mac doesn't seem to like the .xlsx
Thanks! If it works better for you to email it to me, shoot me a DM!
Thanks!
Please PM me your email address. You have no contact info on CD.
This is an amazing spreadsheet. As someone who used to write Lotus 1-2-3 macros back in the day, I am in awe.
I was able to run the macros to update the week 3 regionals successfully. How do I get that data merged into the OPR and WM tabs?
Thanks...
This is an amazing spreadsheet. As someone who used to write Lotus 1-2-3 macros back in the day, I am in awe.
I was able to run the macros to update the week 3 regionals successfully. How do I get that data merged into the OPR and WM tabs?
Thanks...
I am glad it worked out for you. Every week after the events are over, I update the OPR numbers, check to make sure all results FIRST posted are correct, and then I transfer that week's data to the OPR and WM tabs. I guess I am performing the Quality Control function and make sure there is only one set of data in the CD community instead of everybody running them and potentially have slightly different numbers.
Week 3 data were already posted yesterday. There is a separate post to discuss each week's data. See http://www.chiefdelphi.com/forums/showthread.php?threadid=104743
marccenter
19-03-2012, 13:12
Dr Ed Law,
Thanks for revising the database to add points for teams that elected to gather coopertition points for their alliance. At the Michigan Waterford District tournament we found ourselves often in a position that we were the most consistent bridge climbing team and other opposing alliance often met with us to discuss going together on the white bridge to gain this valuable seeding points. We could have climbed the alliance bridge for 10 points but elected to go the other route. Should the coopertition points really be 10 points instead of the 5 shown in your database? We still climbed a bridge and balanced?
Dr Ed Law,
Thanks for revising the database to add points for teams that elected to gather coopertition points for their alliance. At the Michigan Waterford District tournament we found ourselves often in a position that we were the most consistent bridge climbing team and other opposing alliance often met with us to discuss going together on the white bridge to gain this valuable seeding points. We could have climbed the alliance bridge for 10 points but elected to go the other route. Should the coopertition points really be 10 points instead of the 5 shown in your database? We still climbed a bridge and balanced?
We are giving 5 bridge points for every 1 coop point. However when you balance on the coop bridge in each match, you get 2 coop points which means you are getting 10 bridge points. If you had gone to the alliance bridge by yourselves or balance with a partner, it is also 10 points extra, so it is the same. If you feel that you want to look at it differently by sorting using more than 5 bridge points for every 1 coop point, you can change the number in cell AF1 to whatever you like.
Hey Ed!
First off, thank you SO MUCH for doing this!
Secondly, would it be at all possible to have the file available as a ".xls"? My Mac doesn't seem to like the .xlsx
Thanks! If it works better for you to email it to me, shoot me a DM!
Thanks!
Mike, it is your lucky day. Excel finally cooperated and let me write it out in .xls format.
Ether, now you can look at the data using Excel 2003.
Mike, it is your lucky day. Excel finally cooperated and let me write it out in .xls format.
Ether, now you can look at the data using Excel 2003.
Thanks Ed !
The database has been updated with Divisions information and links were set up for use at Championship to easily update the OPR etc. if you have access to the internet.
Good luck to all at the Championship. Our team is in Newton division. Stop by to say hi. I would love to hear from you and how you use this database for your team.
Jacob Plicque
23-04-2012, 12:35
Ed,
Thanks for the update. Your efforts make it a lot easier for Team 86 and surely others to find the diamond in the rough.
In my previous email, the playoffs were correctly predicted by the eliminations about 70% of the time. Have you considered calculating the playoff rounds with the elims to raise the accuracy of the predictor?
See you in St Louis.
Jacob:cool:
Mentor Team Resistance
Newton Division
Ed,
Thanks for the update. Your efforts make it a lot easier for Team 86 and surely others to find the diamond in the rough.
In my previous email, the playoffs were correctly predicted by the eliminations about 70% of the time. Have you considered calculating the playoff rounds with the elims to raise the accuracy of the predictor?
See you in St Louis.
Jacob:cool:
Mentor Team Resistance
Newton Division
We are in Newton Division too. I will look forward to seeing you. Perhaps we can share our scouting data.
Championship data version 2 is out with Thursday's results. I also fixed some of the link problems that I can find.
Hi Ed,
We seem to be having a problem with updating Thursday's database for Friday's results. Crtl+Shift+P updates the ranking and official FIRST data, but the OPR data doesn't appear to change. Are we missing something? Thanks
Championship data Version 3 including Thursday and Friday results are uploaded. I fixed one problem with the OPR data calculation. However there is something very strange going on with the links. It keeps changing the link to KC, NH, TN and STX. If the Ctrl Shift P does not work, you will need to manually copy and paste into the spreadsheet.
A lot of these latest problem with the spreadsheet is because the spreadsheet was developed not expecting any regional to have more than 97 teams. Since we have 100 teams in each division, there was not enough room to put the standing data between 1 and 100. There are also more matches than I have allocated room for. I have to add rows and there were a lot of changes in the macros and formulas that I sometimes missed.
After the championship is over, I will need to clean this up and probably delete and redo all the links once and for all.
faust1706
18-03-2013, 00:12
wow. that is truly an amazing work of excel. Thanks for posting this, cant even begin to imagine how it was made!
A note to passers-by, this is the 2012 thread. (Took me a minute.) 2013 is here: http://www.chiefdelphi.com/forums/showthread.php?t=115101
wow. that is truly an amazing work of excel. Thanks for posting this, cant even begin to imagine how it was made!
Thanks. It is through a combination of Excel formulas and VBA. Very little is done by hand.
This was the original thread from some years ago. I still put the weekly data here to make it easier for people to find it. Each week I create a new thread to let people know new data is available.
Citrus Dad
19-05-2013, 14:47
There appears to be an error in the OPR calculation for the 2013 Championships. The sum of the individual OPRs diverges significantly from the total OPR, whereas the differences in the regionals are much smaller. I suggest going back and checking the calculations in that version.
There appears to be an error in the OPR calculation for the 2013 Championships. The sum of the individual OPRs diverges significantly from the total OPR, whereas the differences in the regionals are much smaller. I suggest going back and checking the calculations in that version.
redirect here:
http://www.chiefdelphi.com/forums/showthread.php?p=1276030#post1276030
The database is ready for 2014. Everything should work for calculating the OPR/CCWM during the competition. I cannot test it since the links are not up yet. It will also calculate the sub-OPR for Assist, Autonomous, Truss & Catch and Teleop. I will check it on Friday to make sure it works. If there are any problems, I will post any correction ASAP.
marccenter
28-02-2014, 22:35
Ed,
Thanks for doing this. It didn't seem to work for me as expected. Will you be posting a new version, soon.
The CTRl-SHift-P did provide some data but ended with an error code (not expected). The subsequent Ctrl_Shift-O
did not populate data either (also debug message).
It's easy to fix - pop into the debugger and do a find for 5000; change all occurrences of 5000 to 6000, and the spreadsheet goes back to working it's wonders.
Cheers,
Jeremy
Jacob Plicque
01-03-2014, 19:26
Ed
I am getting a runtime "13" error. :rolleyes: After fixing the 5000 with a 6000 for some of the regionals
It's easy to fix - pop into the debugger and do a find for 5000; change all occurrences of 5000 to 6000, and the spreadsheet goes back to working it's wonders.
Cheers,
Jeremy
I published a new one to fix the 5000 limit and a few minor errors. It is hard to anticipate everything and with no way to test before publishing. 6 years ago when I wrote this, 5000 seems to be a very big number as the teams were only in 2000 range. I have completely forgotten about it.
Banderoonies
10-03-2014, 20:12
On behalf of team 195, I want to thank you for the excellent work you do in putting this data together.
olapmonkey
11-03-2014, 01:15
Ed
I am getting a runtime "13" error. :rolleyes: After fixing the 5000 with a 6000 for some of the regionals
I too am getting errors on many of the tabs when I try the ctrl-shift-p combination. Additionally, it is unclear to me on how the WM data and OPR data tabs update. If I try ctrl-shift-P they error as well and currently they are only showing partial information. Any ideas?
Thanks for putting this together Ed. It looks to be quite useful.
I too am getting errors on many of the tabs when I try the ctrl-shift-p combination. Additionally, it is unclear to me on how the WM data and OPR data tabs update. If I try ctrl-shift-P they error as well and currently they are only showing partial information. Any ideas?
Thanks for putting this together Ed. It looks to be quite useful.
Can you give me one example of which one does not work? All week 2 events work for me. There was one event in Week 1 that did not work because of something I did.
Make sure you do not have other spreadsheets open that have macros, especially older versions of the scouting spreadsheets.
What version of Excel are you using and what kind of computers are you using?
theawesome1730
16-03-2014, 23:08
On a Mac running office 2011 what would be Ctrl+Shift+P? I would think Cmd+Shift+P but that opens a new project
On a Mac running office 2011 what would be Ctrl+Shift+P? I would think Cmd+Shift+P but that opens a new project
Sorry I have no experience with Mac. The shortcut is just for convenience on a PC. You can open up the macro and manually run the macro in Module 2 called "RefreshData". It is on the top of all the macros in Module 2.
Is there anyone out there in the CD community who has managed to figure out a way to convert Ed's xlsm spreadsheet so that it can be opened with Excel 2000 ?
First, AWESOME spreadsheet!
Can you give me one example of which one does not work? ........What version of Excel are you using and what kind of computers are you using?
I am using Windows 7 Home Premium and Excel 2013.
I think the tabs people are having problems with are the combined data pages, all of the competition tabs are updated (minus the alliance pick/tournament results "Column O" of course). The "WM data", "WM results", "OPR data", "OPR results", "Adjusted OPR results", and "Worldrank" tabs do not seem to have been updated with the week 4 results. Using CTRL+SHIFT+P on these pages results in "Microsoft Visual Basic Run-time error '1004': Application-defined or object-defined error". Going to debug, the following line in Module 2 (Code) is highlighted: Selection.QueryTable.Refresh BackgroundQuery:=False
Also, column B, the "Location" data is blank for all but Team 1 in each of those tabs.
Truthfully I don't know if you wanted us to use CTRL+SHIFT+P to update those tabs, but it looks like the last time they were updated was week 3.
I think the easiest thing to do on each tab would be to put a small "radio button" or two at the top of the page that would run the expected macro or macros for each tab. Also, if you want the macro to not show the screen updating while it is running, which should make it faster as well, set "Application.ScreenUpdating = False" at the beginning and "= True" just before the end.
Citrus Dad
28-03-2014, 12:54
Ed
Thanks for doing this. I've used it extensively for pre-scouting coming events. A few questions/comments:
- I noticed a few cases where the OPR for specific segments (i.e., Assists & TeleOp give out-of range results). For example 2156 shows Assists = -520.5 and TeleOp = + 417.9. The net is -103.4 for that aspect. The Auto = 74.2 and the Truss & Catch = 42.4, so the overall net gets down to +14.0. Another team has a similar result. I don't remember seeing such oddities before. Is this a bug in your results, or an artifact of the analysis?
- Which brings me to the second point. Since this is a linear regression should be able to produce two fundamental statistics that tell about goodness of fit. (There's other stats that also can tell us about potential bias as well, but those are more difficult in a spreadsheet.) First is to compute the standard error around each estimate so we can see the probability that the parameter estimates are statistically significant. The second is the r-squared that tells about the overall goodness of fit. Can you produce either or both of these in future versions?
- And one last observation. For the statistically minded, this looks like a random effects model. (http://faculty.ucr.edu/~hanneman/linear_models/c4.html). Probably too difficult to implement in a spreadsheet, but there's some important differences in the statistical properties.
Ctrl-Shift-P is only to be used for event tabs. It refreshes data from US FIRST website and calculate OPR and CCWM. It is not a update anything you want macro. If you use it on the "OPR results" tab, some data in column B will be erased because that is what I need to do at the beginning of the macro.
If you find that the data in "OPR results" does not have week 4 data, you must be using version 3.0 which only includes data up to week 3. I publish a new file every week. The spreadsheet does not update itself by running a macro.
It is a great idea to put buttons on each page so only those macros will run. However I don't like to do that because when I copy the whole page of data, those buttons keep pasting over and over on top of the buttons that is already there.
As for not showing the screen update while it is running, I didn't know you can do that. Thanks for pointing it out. I learn something new today. However it is a personal preference. I like to see it updating while it is running. That way I know it is doing something and not just got hung up in an infinite loop. You can modify my spreadsheet to suit your preference. It is open source.
First, AWESOME spreadsheet!
I am using Windows 7 Home Premium and Excel 2013.
I think the tabs people are having problems with are the combined data pages, all of the competition tabs are updated (minus the alliance pick/tournament results "Column O" of course). The "WM data", "WM results", "OPR data", "OPR results", "Adjusted OPR results", and "Worldrank" tabs do not seem to have been updated with the week 4 results. Using CTRL+SHIFT+P on these pages results in "Microsoft Visual Basic Run-time error '1004': Application-defined or object-defined error". Going to debug, the following line in Module 2 (Code) is highlighted: Selection.QueryTable.Refresh BackgroundQuery:=False
Also, column B, the "Location" data is blank for all but Team 1 in each of those tabs.
Truthfully I don't know if you wanted us to use CTRL+SHIFT+P to update those tabs, but it looks like the last time they were updated was week 3.
I think the easiest thing to do on each tab would be to put a small "radio button" or two at the top of the page that would run the expected macro or macros for each tab. Also, if you want the macro to not show the screen updating while it is running, which should make it faster as well, set "Application.ScreenUpdating = False" at the beginning and "= True" just before the end.
Ed
Thanks for doing this. I've used it extensively for pre-scouting coming events. A few questions/comments:
- I noticed a few cases where the OPR for specific segments (i.e., Assists & TeleOp give out-of range results). For example 2156 shows Assists = -520.5 and TeleOp = + 417.9. The net is -103.4 for that aspect. The Auto = 74.2 and the Truss & Catch = 42.4, so the overall net gets down to +14.0. Another team has a similar result. I don't remember seeing such oddities before. Is this a bug in your results, or an artifact of the analysis?
- Which brings me to the second point. Since this is a linear regression should be able to produce two fundamental statistics that tell about goodness of fit. (There's other stats that also can tell us about potential bias as well, but those are more difficult in a spreadsheet.) First is to compute the standard error around each estimate so we can see the probability that the parameter estimates are statistically significant. The second is the r-squared that tells about the overall goodness of fit. Can you produce either or both of these in future versions?
- And one last observation. For the statistically minded, this looks like a random effects model. (http://faculty.ucr.edu/~hanneman/linear_models/c4.html). Probably too difficult to implement in a spreadsheet, but there's some important differences in the statistical properties.
Thanks for pointing that out. Others have pointed it out also recently. I need to look into it. This happens last year when I did it the old way. Every time a team plays a surrogate match, things can get mess up. I added a shift so the total of the sub OPR score will add up to OPR. It seems to work in all the events from last year. However this year there are instances when it will happen. I am not sure if I can fix it quickly as my team is competing week 4, 5 and 6. And also 7 if we make it to Michigan State Championship. However I know the program is okay for most of the other teams since it does the shift independently of each other. I will try it without the shift and see if it is the shift that mess it up.
(Update)
I looked into the macro and found the problem. I came up with the shifting instead of scaling over the summer. Unfortunately I used the wrong version to start this year's spreadsheet which is using the old way. I updated the macro and reran the data of all the 5 weeks of events. The problem should not come up any more. Sorry about the confusion.
Since this is a linear regression should be able to produce two fundamental statistics that tell about goodness of fit. (There's other stats that also can tell us about potential bias as well, but those are more difficult in a spreadsheet.) First is to compute the standard error around each estimate so we can see the probability that the parameter estimates are statistically significant. The second is the r-squared that tells about the overall goodness of fit.
Since the assumption of normal distribution is almost certainly false, I think a cumulative histogram of unpenalized score Ax-b residuals (http://www.chiefdelphi.com/media/papers/download/3958) may provide better insight concerning how well the data match the OPR model.
However, if someone would be willing to extract from Ed's spreadsheet his carefully crafted unpenalized alliance scores and team composition into a simple CSV or whitespace-delimited file and post it here, I will compute the parameter variances.
Required fields in each record:
r1 r2 r3 b1 b2 b3 rsu bsu
... where rsu & bsu are the unpenalized alliance scores
Joe Ross
31-03-2014, 18:45
Required fields in each record:
r1 r2 r3 b1 b2 b3 rsu bsu
... where rsu & bsu are the unpenalized alliance scores
Isn't this available with a little math from the twitter data? I don't think it's collated all in one place in Ed's spreadsheet.
Isn't this available with a little math from the twitter data?
Yes, I could do this easily with just the Twitter data.
But the Twitter data is frustratingly incomplete, and contains errors and redundancies.
Ed starts with the official Match Results and Team Standings data, then integrates the Twitter data with loving care and attention to detail. Bless you Ed. I don't have the stamina or patience to do that.
Isn't this available with a little math from the twitter data? I don't think it's collated all in one place in Ed's spreadsheet.
For events that have complete twitter data, I paste them starting in cell A401. The unpenalized scores are in column O and P starting from row 154.
There is a new presentation to explain how to calculate OPR, sub-OPR and CCWM in more details. It is what I presented today at the Michigan State Championship seminars.
Navid Shafa
15-04-2014, 06:55
There is a new presentation to explain how to calculate OPR, sub-OPR and CCWM in more details. It is what I presented today at the Michigan State Championship seminars.
Is there video of this and/or presentation notes available?
Is there video of this and/or presentation notes available?
No, there is no video, only the powerpoint slides that was used in the presentation. It can be found in the same white paper where I put all the scouting data.
http://www.chiefdelphi.com/media/papers/2174?
nuclearnerd
20-04-2014, 21:48
Hey Ed, I don't know if you can do anything about it, but the latest versions of your scouting database crashes Excel for Mac 2011. The weird thing is the 2013 databases, and the earlier versions of the 2014 database (V6 maybe? Can't check) open fine. But V7.0, V7.1 and 2014 CMP V1 hang on opening.
Maybe you have an idea about what changed? I have a windows PC with excel viewer that lets me see the data at least, but unless I can get it open on OS X again, I can't edit (or filter or search) anything :(
Hey Ed, I don't know if you can do anything about it, but the latest versions of your scouting database crashes Excel for Mac 2011. The weird thing is the 2013 databases, and the earlier versions of the 2014 database (V6 maybe? Can't check) open fine. But V7.0, V7.1 and 2014 CMP V1 hang on opening.
Maybe you have an idea about what changed? I have a windows PC with excel viewer that lets me see the data at least, but unless I can get it open on OS X again, I can't edit (or filter or search) anything :(
Hi,
I did not do anything different in the spreadsheet except the files get bigger and bigger with more weeks of data. I think started from v6.0 or v7.0, I started zipping the file first before I upload. Did you unzip the file? I don't have a Mac so I don't know if you can unzip. I uploaded a Championship v1 without zipping for you. Try to download that one and see if it works. Let me know if you are able to unzip. If not, I will not zip it in the future when I upload.
nuclearnerd
21-04-2014, 21:12
I uploaded a Championship v1 without zipping for you. Try to download that one and see if it works. Let me know if you are able to unzip. If not, I will not zip it in the future when I upload.
The file unzips fine it seems. Unfortunately the unzipped file crashes as well - file starts to open, asks to enable macros, throws up a progress bar, and then stalls. I wish I had more to give you. Is there anyone else on CD using office for Mac?
The file unzips fine it seems. Unfortunately the unzipped file crashes as well - file starts to open, asks to enable macros, throws up a progress bar, and then stalls. I wish I had more to give you. Is there anyone else on CD using office for Mac?
No issues for me.
nuclearnerd
21-04-2014, 23:08
No issues for me.
Huh, weird. I have no problem loading the 1114 database.
Can you do me a favor Ed / Gregor if you have time? Export a version in excel 97 "xls" format (instead of xlsm)?
Huh, weird. I have no problem loading the 1114 database.
Can you do me a favor Ed / Gregor if you have time? Export a version in excel 97 "xls" format (instead of xlsm)?
Huh.
Trying to do that crashes Excel for me, but not with any other spreadsheet.
eddie12390
21-04-2014, 23:56
Huh.
Trying to do that crashes Excel for me, but not with any other spreadsheet.
I just tried to do it and had the exact same issue. About 75% of the way through saving the file it crashed inexplicably.
(Excel 2013)
Huh, weird. I have no problem loading the 1114 database.
Can you do me a favor Ed / Gregor if you have time? Export a version in excel 97 "xls" format (instead of xlsm)?
Welcome to the club.
http://www.chiefdelphi.com/forums/showpost.php?p=1364691&postcount=3
http://www.chiefdelphi.com/forums/showpost.php?p=1364708&postcount=7
http://www.chiefdelphi.com/forums/showpost.php?p=1364713&postcount=8
http://www.chiefdelphi.com/forums/showpost.php?p=1364715&postcount=9
http://www.chiefdelphi.com/forums/showpost.php?p=1364692&postcount=195
Dragonking
25-02-2015, 16:14
Will you be posting a 2015 version?
Will you be posting a 2015 version?
Yes I am working on it. A lot of data is missing so far plus we need to wait for some events to finish on Sunday.
A lot of data is missing so far...
Let's hope it's not missing permanently...
http://www.chiefdelphi.com/forums/showthread.php?p=1451114#post1451114
Yes I am working on it.
Ed, what version of Excel will you be using for the database this year?
nuclearnerd
08-03-2015, 22:44
Ed, what version of Excel will you be using for the database this year?
Is there much programmed in VB? If it's mostly formulas, maybe you could consider moving the spreadsheet to Google Sheets?
Is there much programmed in VB? If it's mostly formulas, maybe you could consider moving the spreadsheet to Google Sheets?
I think VBA is used to import and parse the data from the web, and to do the linear algebra heavy lifting. Probably other stuff too. Once you've gone to the trouble of learning VBA, it's a whole lot easier to use than formulas in many cases.
I was able to get the latest version imported into Excel2000, but haven't run it through the wringer yet to see if everything works.
allen.mays
15-03-2015, 17:32
I can update data and OPR for each region using the keys you specify in the instructions. However, do I have to enter the complete world WM and OPR data manually? Using the refresh commands (Ctrl+Shift+ P or O) only calls an error and asks to debug. If I try to refresh OPR on the Worldrank sheet, the calculations run on and on, adding tens of thousands of rows to the sheet. Thanks for the help and all of the time you have put in to this!
I update the world ranking and each week's results. Individual users do not need to worry about that.
RomeroFRC5012
15-03-2015, 23:25
Hey Ed i like hwat you're doing and was wondering when a new thread with updated rankings will be out? if there is any thing i can do to help?
Thank you
evanperryg
05-04-2015, 19:08
The database is incorrect for Midwest regional elims.
-Currently says 2338, 1756, and 171 were semifinalists. We were finalists.
-Currently says 4143, 2062, and 2830 were semifinalists. They won.
TBA shows this as well, but the FRC event page is up to date.
Steve2062
05-04-2015, 22:16
The database is incorrect for Midwest regional elims.
-Currently says 2338, 1756, and 171 were semifinalists. We were finalists.
-Currently says 4143, 2062, and 2830 were semifinalists. They won.
TBA shows this as well, but the FRC event page is up to date.
The FRC events page doesn't show the score of the final match (Finals 3) for me, but does say that the Red alliance won 2-1. The final score of Finals Match 3 was 172-166 for those that didn't watch.
The database is incorrect for Midwest regional elims.
-Currently says 2338, 1756, and 171 were semifinalists. We were finalists.
-Currently says 4143, 2062, and 2830 were semifinalists. They won.
TBA shows this as well, but the FRC event page is up to date.
Thank you for bringing it to my attention. My program determines which alliance is winner and which alliance is finalist. When there is a tie after the first two matches in the finals and the third match has no scores, my program would think that they are all semifinalist. Probably why TBA says the same thing. I have manually put in the score 172-166. It will be reflected next week when I publish after Week 7 events.
olapmonkey
02-03-2016, 21:23
Thank you for bringing it to my attention. My program determines which alliance is winner and which alliance is finalist. When there is a tie after the first two matches in the finals and the third match has no scores, my program would think that they are all semifinalist. Probably why TBA says the same thing. I have manually put in the score 172-166. It will be reflected next week when I publish after Week 7 events.
Any updates planned for 2016?
Any updates planned for 2016?
Ed retired and moved to the San Fran Bay area. The last I heard, he had passed the baton for update of his widely used and loved spreadsheet.
olapmonkey
06-03-2016, 16:33
Ed retired and moved to the San Fran Bay area. The last I heard, he had passed the baton for update of his widely used and loved spreadsheet.
Do you know where the baton got passed?
Do you know where the baton got passed?
This is all I know:
http://www.chiefdelphi.com/forums/showpost.php?p=1477037&postcount=6
Reach out to Team 2834 Leadership and ask.
timuraydin
06-03-2016, 17:31
Baton passed to me by Ed. I have updated Scouting Database file based on this year's game and will start publishing it this week?
Thanks,
Timur Aydin
Team 2834 Mentor
timuraydin
08-03-2016, 07:53
2016 scouting database is posted to
http://www.chiefdelphi.com/media/papers/3242
Caleb Sykes
08-03-2016, 10:54
2016 scouting database is posted to
http://www.chiefdelphi.com/media/papers/3242
Awesome, thank you very much for continuing with this.
vBulletin® v3.6.4, Copyright ©2000-2017, Jelsoft Enterprises Ltd.