paper: New Scouting Database from Team 2834

[BigBen]

First, AWESOME spreadsheet!

Originally Posted by Ed Law

Can you give me one example of which one does not work? ........What version of Excel are you using and what kind of computers are you using?

I am using Windows 7 Home Premium and Excel 2013.

I think the tabs people are having problems with are the combined data pages, all of the competition tabs are updated (minus the alliance pick/tournament results "Column O" of course). The "WM data", "WM results", "OPR data", "OPR results", "Adjusted OPR results", and "Worldrank" tabs do not seem to have been updated with the week 4 results. Using CTRL+SHIFT+P on these pages results in "Microsoft Visual Basic Run-time error '1004': Application-defined or object-defined error". Going to debug, the following line in Module 2 (Code) is highlighted: Selection.QueryTable.Refresh BackgroundQuery:=False

Also, column B, the "Location" data is blank for all but Team 1 in each of those tabs.

Truthfully I don't know if you wanted us to use CTRL+SHIFT+P to update those tabs, but it looks like the last time they were updated was week 3.

I think the easiest thing to do on each tab would be to put a small "radio button" or two at the top of the page that would run the expected macro or macros for each tab. Also, if you want the macro to not show the screen updating while it is running, which should make it faster as well, set "Application.ScreenUpdating = False" at the beginning and "= True" just before the end.

[Citrus Dad]

Ed
Thanks for doing this. I've used it extensively for pre-scouting coming events. A few questions/comments:

- I noticed a few cases where the OPR for specific segments (i.e., Assists & TeleOp give out-of range results). For example 2156 shows Assists = -520.5 and TeleOp = + 417.9. The net is -103.4 for that aspect. The Auto = 74.2 and the Truss & Catch = 42.4, so the overall net gets down to +14.0. Another team has a similar result. I don't remember seeing such oddities before. Is this a bug in your results, or an artifact of the analysis?

- Which brings me to the second point. Since this is a linear regression should be able to produce two fundamental statistics that tell about goodness of fit. (There's other stats that also can tell us about potential bias as well, but those are more difficult in a spreadsheet.) First is to compute the standard error around each estimate so we can see the probability that the parameter estimates are statistically significant. The second is the r-squared that tells about the overall goodness of fit. Can you produce either or both of these in future versions?

- And one last observation. For the statistically minded, this looks like a random effects model. (http://faculty.ucr.edu/~hanneman/linear_models/c4.html). Probably too difficult to implement in a spreadsheet, but there's some important differences in the statistical properties.

[Ed Law]

Ctrl-Shift-P is only to be used for event tabs. It refreshes data from US FIRST website and calculate OPR and CCWM. It is not a update anything you want macro. If you use it on the "OPR results" tab, some data in column B will be erased because that is what I need to do at the beginning of the macro.

If you find that the data in "OPR results" does not have week 4 data, you must be using version 3.0 which only includes data up to week 3. I publish a new file every week. The spreadsheet does not update itself by running a macro.

It is a great idea to put buttons on each page so only those macros will run. However I don't like to do that because when I copy the whole page of data, those buttons keep pasting over and over on top of the buttons that is already there.

As for not showing the screen update while it is running, I didn't know you can do that. Thanks for pointing it out. I learn something new today. However it is a personal preference. I like to see it updating while it is running. That way I know it is doing something and not just got hung up in an infinite loop. You can modify my spreadsheet to suit your preference. It is open source.

Originally Posted by BigBen

First, AWESOME spreadsheet!



I am using Windows 7 Home Premium and Excel 2013.

I think the tabs people are having problems with are the combined data pages, all of the competition tabs are updated (minus the alliance pick/tournament results "Column O" of course). The "WM data", "WM results", "OPR data", "OPR results", "Adjusted OPR results", and "Worldrank" tabs do not seem to have been updated with the week 4 results. Using CTRL+SHIFT+P on these pages results in "Microsoft Visual Basic Run-time error '1004': Application-defined or object-defined error". Going to debug, the following line in Module 2 (Code) is highlighted: Selection.QueryTable.Refresh BackgroundQuery:=False

Also, column B, the "Location" data is blank for all but Team 1 in each of those tabs.

Truthfully I don't know if you wanted us to use CTRL+SHIFT+P to update those tabs, but it looks like the last time they were updated was week 3.

I think the easiest thing to do on each tab would be to put a small "radio button" or two at the top of the page that would run the expected macro or macros for each tab. Also, if you want the macro to not show the screen updating while it is running, which should make it faster as well, set "Application.ScreenUpdating = False" at the beginning and "= True" just before the end.

[Ed Law]

Originally Posted by Citrus Dad

Ed
Thanks for doing this. I've used it extensively for pre-scouting coming events. A few questions/comments:

- I noticed a few cases where the OPR for specific segments (i.e., Assists & TeleOp give out-of range results). For example 2156 shows Assists = -520.5 and TeleOp = + 417.9. The net is -103.4 for that aspect. The Auto = 74.2 and the Truss & Catch = 42.4, so the overall net gets down to +14.0. Another team has a similar result. I don't remember seeing such oddities before. Is this a bug in your results, or an artifact of the analysis?

- Which brings me to the second point. Since this is a linear regression should be able to produce two fundamental statistics that tell about goodness of fit. (There's other stats that also can tell us about potential bias as well, but those are more difficult in a spreadsheet.) First is to compute the standard error around each estimate so we can see the probability that the parameter estimates are statistically significant. The second is the r-squared that tells about the overall goodness of fit. Can you produce either or both of these in future versions?

- And one last observation. For the statistically minded, this looks like a random effects model. (http://faculty.ucr.edu/~hanneman/linear_models/c4.html). Probably too difficult to implement in a spreadsheet, but there's some important differences in the statistical properties.

Thanks for pointing that out. Others have pointed it out also recently. I need to look into it. This happens last year when I did it the old way. Every time a team plays a surrogate match, things can get mess up. I added a shift so the total of the sub OPR score will add up to OPR. It seems to work in all the events from last year. However this year there are instances when it will happen. I am not sure if I can fix it quickly as my team is competing week 4, 5 and 6. And also 7 if we make it to Michigan State Championship. However I know the program is okay for most of the other teams since it does the shift independently of each other. I will try it without the shift and see if it is the shift that mess it up.

(Update)
I looked into the macro and found the problem. I came up with the shifting instead of scaling over the summer. Unfortunately I used the wrong version to start this year's spreadsheet which is using the old way. I updated the macro and reran the data of all the 5 weeks of events. The problem should not come up any more. Sorry about the confusion.

[Ether]

Originally Posted by Citrus Dad

Since this is a linear regression should be able to produce two fundamental statistics that tell about goodness of fit. (There's other stats that also can tell us about potential bias as well, but those are more difficult in a spreadsheet.) First is to compute the standard error around each estimate so we can see the probability that the parameter estimates are statistically significant. The second is the r-squared that tells about the overall goodness of fit.

Since the assumption of normal distribution is almost certainly false, I think a cumulative histogram of unpenalized score Ax-b residuals may provide better insight concerning how well the data match the OPR model.

However, if someone would be willing to extract from Ed's spreadsheet his carefully crafted unpenalized alliance scores and team composition into a simple CSV or whitespace-delimited file and post it here, I will compute the parameter variances.

Required fields in each record:

r1 r2 r3 b1 b2 b3 rsu bsu

... where rsu & bsu are the unpenalized alliance scores

[Joe Ross]

Originally Posted by Ether

Required fields in each record:

r1 r2 r3 b1 b2 b3 rsu bsu

... where rsu & bsu are the unpenalized alliance scores

Isn't this available with a little math from the twitter data? I don't think it's collated all in one place in Ed's spreadsheet.

[Ether]

Originally Posted by Joe Ross

Isn't this available with a little math from the twitter data?

Yes, I could do this easily with just the Twitter data.

But the Twitter data is frustratingly incomplete, and contains errors and redundancies.

Ed starts with the official Match Results and Team Standings data, then integrates the Twitter data with loving care and attention to detail. Bless you Ed. I don't have the stamina or patience to do that.

[Ed Law]

Originally Posted by Joe Ross

Isn't this available with a little math from the twitter data? I don't think it's collated all in one place in Ed's spreadsheet.

For events that have complete twitter data, I paste them starting in cell A401. The unpenalized scores are in column O and P starting from row 154.

[Ed Law]

There is a new presentation to explain how to calculate OPR, sub-OPR and CCWM in more details. It is what I presented today at the Michigan State Championship seminars.

[Navid Shafa]

Originally Posted by Ed Law

There is a new presentation to explain how to calculate OPR, sub-OPR and CCWM in more details. It is what I presented today at the Michigan State Championship seminars.

Is there video of this and/or presentation notes available?

[Ed Law]

Originally Posted by Navid Shafa

Is there video of this and/or presentation notes available?

No, there is no video, only the powerpoint slides that was used in the presentation. It can be found in the same white paper where I put all the scouting data.
http://www.chiefdelphi.com/media/papers/2174?

[nuclearnerd]

Hey Ed, I don't know if you can do anything about it, but the latest versions of your scouting database crashes Excel for Mac 2011. The weird thing is the 2013 databases, and the earlier versions of the 2014 database (V6 maybe? Can't check) open fine. But V7.0, V7.1 and 2014 CMP V1 hang on opening.

Maybe you have an idea about what changed? I have a windows PC with excel viewer that lets me see the data at least, but unless I can get it open on OS X again, I can't edit (or filter or search) anything

[Ed Law]

Originally Posted by nuclearnerd

Hey Ed, I don't know if you can do anything about it, but the latest versions of your scouting database crashes Excel for Mac 2011. The weird thing is the 2013 databases, and the earlier versions of the 2014 database (V6 maybe? Can't check) open fine. But V7.0, V7.1 and 2014 CMP V1 hang on opening.

Maybe you have an idea about what changed? I have a windows PC with excel viewer that lets me see the data at least, but unless I can get it open on OS X again, I can't edit (or filter or search) anything

Hi,

I did not do anything different in the spreadsheet except the files get bigger and bigger with more weeks of data. I think started from v6.0 or v7.0, I started zipping the file first before I upload. Did you unzip the file? I don't have a Mac so I don't know if you can unzip. I uploaded a Championship v1 without zipping for you. Try to download that one and see if it works. Let me know if you are able to unzip. If not, I will not zip it in the future when I upload.

[nuclearnerd]

Originally Posted by Ed Law

I uploaded a Championship v1 without zipping for you. Try to download that one and see if it works. Let me know if you are able to unzip. If not, I will not zip it in the future when I upload.

The file unzips fine it seems. Unfortunately the unzipped file crashes as well - file starts to open, asks to enable macros, throws up a progress bar, and then stalls. I wish I had more to give you. Is there anyone else on CD using office for Mac?

[Gregor]

Originally Posted by nuclearnerd

The file unzips fine it seems. Unfortunately the unzipped file crashes as well - file starts to open, asks to enable macros, throws up a progress bar, and then stalls. I wish I had more to give you. Is there anyone else on CD using office for Mac?

No issues for me.