paper: New Scouting Database from Team 2834

CD-Media: Papers
, , ,
1

Thread created automatically to discuss a document in CD-Media.

New Scouting Database from Team 2834
by: Ed Law

This is a new scouting database with a new measure to rank teams, in addition to Offensive Power Rating (OPR), that takes into account both offense and defense.

I developed this new scouting database to help with rating teams. This does not replace pit scouting and watching matches but it helps small teams that does not have a lot of resources to do extensive scouting.

I included a presentation to explain the new measure which I call Calculated Contribution to Winning Margin (CCWM). Please note that the presentation has been updated in 2010 (4th file down) in 2012 (8th file down) and in 2014.

Ed Law
FRC Team 2834 Coach

Team_2834 Scouting_Database presentation.pdf (812 KB)
Team_2834 2008_Scouting_Database v5.zip (1.37 MB)
Team_2834 2009_Scouting_Database Championship v6.zip (2.3 MB)
Team 2834 Scouting Database Presentation 2010.pdf (312 KB)
Team_2834 2010_Scouting_Database Championship v6.zip (3.15 MB)
Team_2834 2011_Scouting_Database Championship v4.zip (4.23 MB)
Team_2834 2011_Scouting_Database Championship v4b.zip (3.17 MB)
Team 2834 Scouting Presentation 2012.pdf (741 KB)
Team_2834 2012_Scouting_Database Championship v4.xlsm (7.82 MB)
Team_2834 2012_Scouting_Database Championship v4.zip (5.43 MB)
Team_2834 2012_Scouting_Database Championship v5.xlsm (7.71 MB)
Team_2834 2013_Scouting_Database Championship v6.xlsm (7.66 MB)
Team 2834 Scouting Database Presentation 2014.pdf (1.79 MB)
Team_2834 2014_Scouting_Database Championship v2.zip (9.46 MB)
Team_2834 2015_Scouting_Database Championship v2.xlsm (12.2 MB)

2

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.

1 Like
3

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.

4

What size would the entire file be if you zipped it first?

5

If you email it to me I could upload it for you :yikes:

6

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.

7

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-

1 Like
8

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

9

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-

10

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

11

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-

12

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

13

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-

14

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

15

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-

16

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

17

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?

18

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.

19

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

20

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?