11-20-2008 11:26 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I try to upload the Scouting Database but it says my file is too big. I will try to reduce the size but I will have to delete some of the data.

11-20-2008 11:43 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I split the database into two Excel file. You can stitch it back together if you want or just use part 1 without part 2.

11-21-2008 01:37 AM

billbo911

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law 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.

11-21-2008 01:39 AM

R.C.

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by billbo911 What size would the entire file be if you zipped it first?

11-21-2008 07:37 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by billbo911 What size would the entire file be if you zipped it first?

11-21-2008 10:52 AM

Clinton Bolinger

Re: paper: New Scouting Database from Team 2834

Very nice database of OPR and CCWM.

Got a few suggestions that might improve your excel file.

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

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

=getpicture(B2)

11-21-2008 12:56 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by 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: Code: Function getpicture(teamnum As String) As Boolean Dim filen as String If ActiveSheet.Name = "Query" Then Else GoTo Done End If Dim AC As Range Static P As Shape On Error GoTo Done Set AC = Application.Caller If PicExists(P) Then P.Delete Else 'look for a picture already over cell For Each P In ActiveSheet.Shapes If P.Type = msoLinkedPicture Then If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then P.Delete Exit For End If End If End If Next P End If filen = CurDir + "" + Format(teamnum) + ".jpg" Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.75, AC.Top + 5, 329.25, 247.5) getpicture = True Exit Function Done: getpicture = False End Function Function PicExists(P As Shape) As Boolean 'Return true if P references an existing shape Dim ShapeName As String On Error GoTo NoPic If P Is Nothing Then GoTo NoPic ShapeName = P.Name PicExists = True NoPic: PicExists = False End Function As for calling the function add the following to "I5": Code: =getpicture(B2) Finally to make the query worksheet more user friendly, I would "unlock" cell B2. You can do this by right clicking on B2, Format Cells..., Click Protection, and uncheck Lock. Then you will want to protect the worksheet by going to Tools>Protection>Protect Sheet.. then add a password if you like and uncheck select locked cells. That way the only selectable cell would be "B2" on the Query worksheet. Hope that helps and Thanks for the information. -Oris-

11-21-2008 01:48 PM

Clinton Bolinger

Re: paper: New Scouting Database from Team 2834

Forgot one thing about protecting the worksheet, you will also have to check the "Edit Objects" box (scroll down 2nd from the bottom). This will allow for the "I8" robot picture to update correctly.

Look forward to seeing the new version.
-Oris-

11-21-2008 07:48 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by 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-

11-24-2008 08:43 AM

Clinton Bolinger

Re: paper: New Scouting Database from Team 2834

After:

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

    P.ScaleHeight 1, True
    P.ScaleWidth 1, True
    P.Height = 250

12-06-2008 07:24 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Oris After: Code: Set P = ActiveSheet.Shapes.AddPicture(filen, True, True, AC.Left + 2.5, AC.Top + 2.5, 329, 250) Add: Code: P.ScaleHeight 1, True P.ScaleWidth 1, True P.Height = 250 That should keep the aspect ratio of the pictures. -Oris-

12-08-2008 08:20 AM

Clinton Bolinger

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law 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

P.ScaleHeight 1, True
P.ScaleWidth 1, True
P.Height = 250

P.LockAspectRatio = msoTrue
P.Height = 250

12-09-2008 11:07 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

This spreadsheet is working in Excel 2007 now. The change that Oris suggested works. It is now version 4. I still can not delete the old versions.

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

Ed

12-10-2008 08:35 AM

Clinton Bolinger

Re: paper: New Scouting Database from Team 2834

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

Replace:

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

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

12-11-2008 04:36 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Oris I got another one for you Ed to fix your opening issues. Replace: Code: filen = CurDir + "" + Format(teamnum) + ".jpg" With: Code: Dim sCurDir As String sCurDir = Mid(ThisWorkbook.FullName, 1, Len(ThisWorkbook.FullName) - Len(ThisWorkbook.Name)) filen = sCurDir + "" + Format(teamnum) + ".jpg" That way the team pictures will load if you open the file by double clicking, open recent, or file open. Hope this helps, -Oris-

01-08-2009 03:59 PM

cygnus.x1

Re: paper: New Scouting Database from Team 2834

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

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

02-07-2009 10:00 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by cygnus.x1 Where do the pictures come from? The spreadsheet works great otherwise! I guess I need to find my own team robot photos or did you have a zip file of them as well?

03-05-2009 10:20 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Hi,

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

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

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

Ed Law

03-05-2009 11:14 AM

Killraine

Re: paper: New Scouting Database from Team 2834

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

03-05-2009 11:16 AM

Clinton Bolinger

Re: paper: New Scouting Database from Team 2834

Quote:

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

03-05-2009 01:04 PM

Killraine

Re: paper: New Scouting Database from Team 2834

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

Thanks

03-05-2009 01:22 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

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

03-07-2009 11:56 PM

Ed Law

Team 2834 Scouting Database (Week Two results)

Hi,

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

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

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

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

Ed

03-08-2009 02:33 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Hi,

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

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

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

What do you think?

Ed

03-09-2009 12:22 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Hi,

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

Ed

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

03-09-2009 01:03 AM

Vikesrock

Re: Team 2834 Scouting Database (Week Two results)

Quote:

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

03-09-2009 01:24 AM

Ed Law

Re: Team 2834 Scouting Database (Week Two results)

Quote:

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

03-09-2009 10:54 AM

IKE

Re: paper: New Scouting Database from Team 2834

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

03-09-2009 11:11 AM

Vikesrock

Re: paper: New Scouting Database from Team 2834

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

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

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

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

03-09-2009 05:07 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Vikesrock I'm not all that great in Excel either, but I believe I have found a way to work around this issue. For each table you want to grab you need to create a .icy file. This is a text file that describes the query. To find out how to format the file you can create a query to a valid page and then save the query, the only part you need to change is the event code in the URL. Here's an example .icy file to get the Qualifications table from the DC regional Code: WEB 1 http://www2.usfirst.org/2009comp/events/DC/matchresults.html Selection=3 Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False The way to use these files is a bit of a hack, but is the only thing I've gotten to work. According to the help files you can just click open and select the .icy and an import wizard will pop up letting you insert the data into the current sheet, but it just opens in a new workbook for me. The workaround I found is to create the query with a valid page (any page), then locate it under Data->Connections (it helps to label the connections). Once you find the Connection click Properties -> Definition -> Browse and select the appropriate .icy file. I tested this by setting one for the DC regional with my computer unplugged from the internet and then plugged back in and refreshed the data and the correct data appeared, so I think it should work.

03-09-2009 05:12 PM

Vikesrock

Re: paper: New Scouting Database from Team 2834

Quote:

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

03-11-2009 12:18 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Vikesrock I'm not all that great in Excel either, but I believe I have found a way to work around this issue. For each table you want to grab you need to create a .icy file. This is a text file that describes the query. To find out how to format the file you can create a query to a valid page and then save the query, the only part you need to change is the event code in the URL. Here's an example .icy file to get the Qualifications table from the DC regional Code: WEB 1 http://www2.usfirst.org/2009comp/events/DC/matchresults.html Selection=3 Formatting=None PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False The way to use these files is a bit of a hack, but is the only thing I've gotten to work. According to the help files you can just click open and select the .icy and an import wizard will pop up letting you insert the data into the current sheet, but it just opens in a new workbook for me. The workaround I found is to create the query with a valid page (any page), then locate it under Data->Connections (it helps to label the connections). Once you find the Connection click Properties -> Definition -> Browse and select the appropriate .icy file. I tested this by setting one for the DC regional with my computer unplugged from the internet and then plugged back in and refreshed the data and the correct data appeared, so I think it should work.

03-11-2009 07:49 AM

Phyrxes

Re: paper: New Scouting Database from Team 2834

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

03-11-2009 12:37 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

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

03-11-2009 12:43 PM

Ed Law

Team 2834 Scouting Database for Week 3 Regionals

Hi,

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

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

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

Ed Law

03-11-2009 12:49 PM

Phyrxes

Re: paper: New Scouting Database from Team 2834

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

03-11-2009 01:46 PM

Killraine

Re: paper: New Scouting Database from Team 2834

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

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

03-11-2009 02:30 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Killraine I am currently compiling a pre-competition scouting database for Chesapeake getting information from a number of sources. After taking info for each team at Chesapeake, I noticed an irregularity with CCWM world rankings. There are a number of teams with the World Rank of 329. It appears that any team that hasn't competed yet will get this rank, but there are a number of teams who have competed that all share this rank. Actually it looks like any team that has a CCWM that is >= 0 has a CCWM world rank of 329. I'm not sure how to upload the file in a post, so if you would like to see it, I can email it to you.

03-11-2009 02:32 PM

Killraine

Re: paper: New Scouting Database from Team 2834

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

03-11-2009 02:45 PM

Killraine

Re: paper: New Scouting Database from Team 2834

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

03-11-2009 05:18 PM

Killraine

Re: paper: New Scouting Database from Team 2834

Last post of the day, I swear

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

03-11-2009 09:51 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

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

03-12-2009 11:46 AM

Killraine

Re: paper: New Scouting Database from Team 2834

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

03-12-2009 12:54 PM

Phyrxes

Re: paper: New Scouting Database from Team 2834

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

03-12-2009 12:55 PM

Killraine

Re: paper: New Scouting Database from Team 2834

Quote:

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

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";

03-12-2009 03:44 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Killraine That was me Could anyone write a program that downloads the picture at http://www.thebluealliance.net/tbatv.../2009/####.jpg (where #### is the team number) and puts it into a folder? You would just have to run the program once and then drop the spreadsheet in there and it would have all the pictures that are currently on TBA. Basically create a matrix of team numbers (already together as Column A on the sheet "2009 Teams") and then use a loop to go through the list and download the picture to a folder on the desktop. In psuedo-code: Code: for(<Every value in the matrix>) if(http://www.thebluealliance.net/tbatv/pictures/2009/####.jpg exists) Download the file at the link to "/Desktop/2009 FRC Pictures"; If you wanted to go all the way, you could have it so when the program creates the matrix, it first checks "/Desktop/2009 FRC Pictures" it only adds the teams from the list that you don't already have. Then, if there is a way to include that program in a startup script for this excel document, you would always have an updated set of photos from TBA. I'm pretty sure you can do all of this in c++ except maybe the part where excel runs the program before opening the spreadsheet. But then again, its not that much of a hassle to click a .exe before opening up the spreadsheet.

03-12-2009 05:20 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I would like to spend some time to clarify regarding the different rankings OPR, DPR, CCWM and PMR (plus/minus ranking). I tried to explain this in another post but I was rushing and didn't explain fully.

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

A * OPR = B(opr)

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

and the system of equations to calculate DPR is

A * DPR = B(dpr)

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

The way I solve for CCWM is

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

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

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

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

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

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

DPR = OPR - CCWM

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

Ed Law

03-13-2009 09:50 AM

Ed Law

Week 3 Regional underway

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

I hope this will help you with alliance selection tomorrow.

Ed Law

03-13-2009 09:55 AM

Phyrxes

Re: paper: New Scouting Database from Team 2834

Aside from that error the spread sheet is working flawlessly on my computer here at school this morning (Using Excel 2003).

03-13-2009 10:45 AM

Killraine

Re: paper: New Scouting Database from Team 2834

I don't know why I didn't think to go to http://www.thebluealliance.net/tbatv/pictures/2009/ yesterday. Should have been the first place I checked.

Thanks.

03-13-2009 12:19 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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

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

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

Ed Law

03-13-2009 01:18 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I fixed the score prediction. It turned out to be easier than I thought. The new version now is "Team_2834 2009_Scouting_Database Week 3d.zip". I didn't do an extensive study yet, but some of the score predictions are amazingly accurate.

Ed Law

03-13-2009 01:25 PM

Killraine

Re: paper: New Scouting Database from Team 2834

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

03-13-2009 01:27 PM

Killraine

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by 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

03-13-2009 01:40 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Killraine I'm not seeing 3d. In fact I'm not seeing 3c anymore either... Anyone else have this problem?

03-13-2009 02:02 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Killraine 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)

03-13-2009 10:19 PM

Team 1746

Re: paper: New Scouting Database from Team 2834

Peachtree regional is not updating. All other regional seem to be updating OK.

Error message

run time error 13

type mismatch

03-14-2009 08:14 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Team 1746 Peachtree regional is not updating. All other regional seem to be updating OK. Error message run time error 13 type mismatch

03-14-2009 10:11 PM

Phyrxes

Re: paper: New Scouting Database from Team 2834

So incase anybody was wondering this spreadsheet doesn't appear to work with OpenOffice.org Calc.

03-15-2009 12:45 AM

Ed Law

Team 2834 Scouting Database (Week 3 Results and Week 4 Regionals Ready)

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

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

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

03-21-2009 10:35 PM

Ed Law

Team 2834 Scouting Database (Week 4 results)

The database has been updated with Week 4 results. There are now results of 1301 teams who have played.

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

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

Ed Law

03-21-2009 10:43 PM

Phyrxes

Re: paper: New Scouting Database from Team 2834

I was using this at our event this past weekend and had a couple people as about it, thanks again for writing it.

03-22-2009 02:51 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Phyrxes I was using this at our event this past weekend and had a couple people as about it, thanks again for writing it.

03-27-2009 12:09 PM

Ed Law

Team 2834 Scouting Database (Week 5)

Hi,

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

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

Ed

03-28-2009 11:12 PM

Ed Law

Team 2834 Scouting Database (Week 5 Results)

Week 5 results are out. Rankings for Troy District Event are missing. I did include the Troy event based on rankings my program calculates. However the ranking may be slightly off because my RS numbers do not match the actual RS numbers which takes into account penalties that I do not have info of. Rankings for Connecticut Regional are also missing from the FIRST website but fortunately I have them stored on my spreadsheet as I update the rankings throughout the day.

Ed

04-01-2009 07:32 PM

MrWibbles

Re: paper: New Scouting Database from Team 2834

(should I mention this here?)
Hi, first: I love the database! This isn't just an amazing tool for FRC info, it's a great example of how databases in general should look and feel.

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

04-03-2009 01:19 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by MrWibbles (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.

04-03-2009 01:22 AM

Ed Law

Team 2834 Scouting Database (week 6)

Hi,

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

Ed

04-03-2009 01:30 AM

Vikesrock

Re: paper: New Scouting Database from Team 2834

Thanks Ed! This is the first week I have a real use for this beyond curiosity/novelty!!

04-03-2009 01:36 AM

tsiersema

Re: paper: New Scouting Database from Team 2834

so does anyone know of good scouting software? in need for state tourny this weekend

04-03-2009 01:22 PM

Fred Sayre

Re: paper: New Scouting Database from Team 2834

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

Thanks!

04-05-2009 12:55 AM

Ed Law

Team 2834 Scouting Database (Week 6 Results)

Week 6 results have been updated. Troy standings is still not posted. If anyone has the ranking, please let me know.

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

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

Ed

04-07-2009 12:25 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Fred Sayre 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!

04-07-2009 12:36 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I received some questions through PM and I decided to answer them here so others can benefit from it also.

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

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

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

A low OPR is just the opposite.

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

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

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

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

Ed Law

04-07-2009 11:57 PM

Ed Law

Team 2834 Scouting Database 2009 Championship

I have updated the database to show which teams are going to Atlanta. When the divisions are annouced, I will create 4 separate tabs for them. There will be links to update results from the internet. I hope there will be internet access there. Otherwise I will have to update it manually again. Actually there are two macros to help you calculate approximate ranking and calculate OPR and CCWM. Read the instructions tab for details.

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

Ed

04-08-2009 01:33 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Scouting database updated with Championship divisions. Links are not set up yet to update standings and match results. I also corrected the BAE results with the regional winners and finalists but I still don't have data of the final match.

I will post division strength in a separate post.

Ed

04-08-2009 02:20 PM

Rob

Re: paper: New Scouting Database from Team 2834

Hi Ed,

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

Thanks,

04-08-2009 11:48 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Oops, I forgot to change the sort macro in version 2 after I inserted the 4 championship divisions column. The sorting macro is now fixed in version 3. Sorry for the inconvenience.

Ed

04-17-2009 12:16 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Version 4 is out. I put in all the matches and links to usfirst.org. This is a version you can use tomorrow and Saturday morning during the qualifying rounds. I couldn't get internet access today there, so we will have to update the match results by hand.

Good luck tomorrow.

Ed

04-17-2009 01:56 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

I just noticed that the Oregon results were wrong. It is too late for me to change it tonight. I will fix it Friday night. The OPR and CCWM are correct, only the alliance selection data was wrong.

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

Ed

04-17-2009 10:39 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Version 5 is out. I regenerated all the regional data. The problem was some of the regional data that FIRST published had the elimination round schedule all mixed up instead of following the order with quarterfinal, then semifinal then final. My program figure out the selection order based on the elimination round matches and did not anticipate out of order listing and posted some wrong alliance selection info.

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

Ed

04-20-2009 01:05 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Version 6 is out. This will be my final version for this year unless somebody notices an error that I need to fix.

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

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

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

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

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

Ed

02-17-2010 06:58 PM

ObedJ

Re: paper: New Scouting Database from Team 2834

This is Obed, a rookie member of 354, wondering how do you put information the scouting database.

02-21-2010 03:09 AM

Ed Law

Team 2834 Scouting Database for 2010

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

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

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

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

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

02-21-2010 09:11 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by ObedJ This is Obed, a rookie member of 354, wondering how do you put information the scouting database.

02-27-2010 01:35 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

I also updated the regionals and districts which teams are attending. Hope that helps.

03-14-2010 09:22 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

OPR results from Week 2 is out. You can download the data from

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

If there is any mistake, please let me know.

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

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

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

03-15-2010 12:15 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Forgot to mention that Wisconsin Regional ranking data is missing. I did include the OPR numbers for that event. If anybody has information on when the data will be posted, please let me know.

03-15-2010 12:32 PM

Vikesrock

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law 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.

03-15-2010 03:49 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Thanks to "Vikesrock" who sent me the Wisconsin data, I have added the ranking information, Seeding Score and Coopertition Bonus but I am still missing the Hanging Points. He mentioned that the Seeding Score may have a slight error since 2194 is listed with the highest Coop points and 111 got the award. If anyone has more information, please let me know.

03-18-2010 12:46 AM

cygnus.x1

Re: paper: New Scouting Database from Team 2834

Thanks for reviving this spreadsheet Ed!

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

03-22-2010 12:43 PM

Joe Ross

Re: paper: New Scouting Database from Team 2834

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

03-22-2010 10:35 PM

Ed Law

Week 3 data is posted

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

03-23-2010 11:51 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

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

03-30-2010 08:46 PM

Joe Ross

Re: paper: New Scouting Database from Team 2834

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

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

03-30-2010 10:25 PM

rulesall2

Re: paper: New Scouting Database from Team 2834

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

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

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

03-30-2010 10:42 PM

XaulZan11

Re: paper: New Scouting Database from Team 2834

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

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

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

03-31-2010 12:32 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by rulesall2 There is a mistake in the WPI regional. During the regional the last qualification match was not counted when the data was saved into FMS so the version that is out on the FIRST site isn't the final version that was used for alliance selections, which were re-done after the error was realized. The match can be found here: http://www.thebluealliance.net/tbatv/match/2010wor_qm58 That would bump 2791 to 7th seed 5th alliance captain at WPI. And 190 would also be bumped up to 6th seed 4th alliance captain. This would mean 2104 would not be the #5 Alliance captain, but instead was the 8th alliance captain. Below is the list of the alliance selection of WPI with teams in order of captain, 1st pick, 2nd pick. WPI 2010. 1 230 20 228 W 2 1735 2370 663 F 3 3280 348 157 SF 4 190 2523 839 SF 5 2791 195 1124 QF 6 2877 172 3205 QF 7 358 716 2621 QF 8 2104 1995 3125 QF

03-31-2010 01:09 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by XaulZan11 Thanks for putting this together again. It's a huge help for the Championship. I came across two errors, however, regarding Wisconsin and Midwest. For Wisconsin, it says 1732 was picked 4th or 13th; we were actually picked 3rd. For Midwest, it says we were first pick, but we were actually AC1. A quick glance finds some more errors with those regionals. I didn't check other regionals, but I would suggest you check all the regionals using the information from this thread: http://www.chiefdelphi.com/forums/sh...ce+selectio n

03-31-2010 01:11 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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

04-06-2010 10:40 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-07-2010 12:59 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-08-2010 06:52 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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

04-12-2010 11:43 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-13-2010 09:56 PM

cygnus.x1

Re: paper: New Scouting Database from Team 2834

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!

04-15-2010 11:02 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-16-2010 09:34 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I updated the Championship data with Friday results. Good luck to all tomorrow!

04-17-2010 08:12 AM

Jacob Plicque

Re: paper: New Scouting Database from Team 2834

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.

04-17-2010 11:13 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I updated the final results of each division and recalculated the OPRs.

04-19-2010 09:26 AM

Nathan Streeter

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law I updated the final results of each division and recalculated the OPRs.

04-19-2010 09:37 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Nathan Streeter 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!

04-20-2010 12:34 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

08-30-2010 07:55 PM

quinxorin

Re: paper: New Scouting Database from Team 2834

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.

08-30-2010 10:36 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by quinxorin 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.

08-31-2010 12:03 AM

quinxorin

Re: paper: New Scouting Database from Team 2834

It did.

03-08-2011 07:31 AM

Nathan Streeter

Re: paper: New Scouting Database from Team 2834

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! :-)

03-08-2011 04:57 PM

cje36

Re: paper: New Scouting Database from Team 2834

ok my email is cje36f@gmail.com
send me a message with the scouting database spread sheet please!

03-17-2011 01:33 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

03-17-2011 02:13 PM

Nathan Streeter

Re: paper: New Scouting Database from Team 2834

No problem at all - thanks a bunch for doing... it's really useful!

03-18-2011 01:34 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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)

03-22-2011 08:37 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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)

03-23-2011 12:33 AM

Jacob Plicque

Re: paper: New Scouting Database from Team 2834

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

03-23-2011 11:47 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Jacob Plicque 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

03-24-2011 12:17 AM

Ed Law

Re: paper: Scouting Database from Team 2834

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)

03-26-2011 11:59 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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)

03-27-2011 06:38 PM

Jacob Plicque

Regional Rank: New Scouting Database from Team 2834

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

03-27-2011 07:27 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

03-27-2011 11:02 PM

Jacob Plicque

Regional Ranking: New Scouting Database from Team 2834

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.

03-28-2011 12:46 AM

bobosalad

Re: paper: New Scouting Database from Team 2834

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.

03-28-2011 01:05 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

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"

03-31-2011 08:17 PM

shocker4256

Re: paper: New Scouting Database from Team 2834

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

03-31-2011 11:53 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by shocker4256 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

04-01-2011 12:18 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-01-2011 01:34 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-04-2011 12:25 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-04-2011 12:29 AM

Vikesrock

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law North Star finals match results incomplete

04-04-2011 12:55 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Vikesrock 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.

04-04-2011 01:05 AM

RMiller

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law 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.

04-13-2011 09:17 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-13-2011 09:45 PM

Travis Hoffman

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law 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.

04-14-2011 10:28 AM

techvikesmom

Re: paper: New Scouting Database from Team 2834

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!!

04-14-2011 10:38 AM

Vikesrock

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by techvikesmom 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!!

04-14-2011 09:02 PM

techvikesmom

Re: paper: New Scouting Database from Team 2834

thank you for the links!!!

04-18-2011 08:53 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

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)

04-18-2011 11:19 AM

Travis Hoffman

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law 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)

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

04-18-2011 11:39 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Travis Hoffman 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?

    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

04-18-2011 03:50 PM

Travis Hoffman

Re: paper: New Scouting Database from Team 2834

VBA fun

04-18-2011 05:17 PM

The Lucas

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Travis Hoffman Code: 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?

04-18-2011 10:46 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Travis Hoffman Code: 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?

04-18-2011 11:52 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Travis Hoffman 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?

04-20-2011 11:18 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-21-2011 08:53 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I did not realize they added some teams. Version 2b reflects the changes.

04-29-2011 08:16 PM

PSHRobotics

Re: paper: New Scouting Database from Team 2834

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:

04-29-2011 08:19 PM

Vikesrock

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by PSHRobotics 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.

04-29-2011 10:06 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

I did realize the links were wrong. I will be posting the updated links in a few minutes.

04-29-2011 10:20 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

04-29-2011 11:52 PM

Vikesrock

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Ed Law Version 3 is out. It contains data through Friday matches at Championship.

05-01-2011 06:24 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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.

05-02-2011 05:23 PM

Ed Law

Re: paper: New Scouting Database from Team 2834

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

07-30-2011 12:40 AM

Ed Law

Re: paper: New Scouting Database from Team 2834

Quote:

Originally Posted by Travis Hoffman 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?