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

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

Ed Law 31-03-2010 13:11
Re: paper: New Scouting Database from Team 2834
 
Week 4 data is published. Colorado data is missing from FIRST.

Ed Law 06-04-2010 10:40
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.

Ed Law 07-04-2010 00:59
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.

Ed Law 08-04-2010 18:52
Re: paper: New Scouting Database from Team 2834
 
Championship division data is now included. I will be posting divisonal strength tonight.

Ed Law 12-04-2010 23:43
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.

cygnus.x1 13-04-2010 21:56
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!

Ed Law 15-04-2010 23:02
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.

Ed Law 16-04-2010 21:34
Re: paper: New Scouting Database from Team 2834
 
I updated the Championship data with Friday results. Good luck to all tomorrow!

Jacob Plicque 17-04-2010 08:12
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.:)

Ed Law 17-04-2010 23:13
Re: paper: New Scouting Database from Team 2834
 
I updated the final results of each division and recalculated the OPRs.

Nathan Streeter 19-04-2010 09:26
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 954686)
I updated the final results of each division and recalculated the OPRs.
Excellent, thank you! It's been really cool to use this spreadsheet for a statistical analysis of teams - our scouting team even used it for "pre-scouting" the Archimedes teams. Do you intend to update the spreadsheet by adding the championship data to the list of event ratings in the query sheet? Thank you very much for sharing all this information!

Ed Law 19-04-2010 09:37
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Nathan Streeter (Post 955204)
Excellent, thank you! It's been really cool to use this spreadsheet for a statistical analysis of teams - our scouting team even used it for "pre-scouting" the Archimedes teams. Do you intend to update the spreadsheet by adding the championship data to the list of event ratings in the query sheet? Thank you very much for sharing all this information!
Hi Nathan,

Yes I do plan to include the championship division data to the Query sheet like last year. It is on my list of things to do.

I am also thinking about the possibility of publishing a true world ranking taking into account all the district/regional/championship data so all interactions will be taken into account. Then there is no argument about strong or weak events or whether the world ranking should be based on average, most recent or weighted average. Do you think there is interest in such data? If there is, then I will look into whether my program can handle solving such a hugh system of equationos.

Ed

Ed Law 20-04-2010 12:34
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.

quinxorin 30-08-2010 19:55
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.

Ed Law 30-08-2010 22:36
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by quinxorin (Post 972897)
Question: why is the OPR rank for teams listed in Worldrank (and on the query page) different from the rank on the OPR Results page? For example, OPR Results gives 67 a rank of 2, but the query and Worldrank gives them a rank of 4.
Good observation! It was not a mistake.

On the sheet "Query", it gives the true World Ranking. If you go to cell M32, you will see that it is reading data from sheet "Worldrank". I felt that when somebody does a query of a particular team, they are probably more interested in the true world ranking, how they compare with other teams.

On the sheet "OPR results", it is still using the regional by regional calculation of OPR and then using either highest, most recent, simple average or weighed average OPR depending on the value used in cell G2. A blank value in G2 means using weighted average at week 6. My rationale in keeping those options is if somebody goes to that sheet that has OPR numbers of each regional, they are probably more interested in comparing between different regionals. To get the true world ranking of all the teams on one page, one can go to sheet "Worldrank".

I hope I answered your question.

quinxorin 31-08-2010 00:03
Re: paper: New Scouting Database from Team 2834
 
It did.

Nathan Streeter 08-03-2011 07:31
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! :-)

cje36 08-03-2011 16:57
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!

Ed Law 17-03-2011 13:33
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.

Nathan Streeter 17-03-2011 14:13
Re: paper: New Scouting Database from Team 2834
 
No problem at all - thanks a bunch for doing... it's really useful!

Ed Law 18-03-2011 13:34
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.

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

Ed Law 22-03-2011 20:37
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.
Code:
Team        Wins        Losses        Ties        Played        QS        RS        MP        Avg Score        OPR        OPR Rank        Avg WM        CCWM        CCWM Rank        Record
1114        16        1        0        16        30        33.81        128        30.52        65.94        1        18.98        51.80        5        (16-1-0)
111        16        0        0        16        32        30.94        132        31.54        64.43        2        21.23        52.91        2        (16-0-0)
148        15        1        0        15        28        19.73        135        28.24        60.03        3        21.33        51.88        4        (15-1-0)
2054        18        2        0        18        32        28.56        135        28.37        58.08        4        17.65        62.77        1        (18-2-0)
33        18        1        0        18        34        21.94        114        24.31        55.68        5        16.80        52.59        3        (18-1-0)
217        37        5        0        37        64        27.65        122        27.75        53.21        6        17.84        46.54        8        (37-5-0)
987        18        4        0        18        28        37.11        135        24.17        52.81        7        8.85        14.26        120        (18-4-0)
1678        16        1        0        16        30        15.81        99        21.19        49.37        8        15.63        44.03        9        (16-1-0)
456        17        2        0        17        30        29.00        121        24.98        46.84        9        14.31        37.17        18        (17-2-0)
179        16        0        0        16        32        21.25        112        24.52        46.74        10        17.44        49.61        6        (16-0-0)

Jacob Plicque 23-03-2011 00:33
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
:confused:

Ed Law 23-03-2011 11:47
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Jacob Plicque (Post 1044133)
Ed,
There is something wrong with the world rank sheet. It shows Team 86 with 16 wins and 6 losses. We only have 9W-6L with the playoff games. Our only competition was the Florida Regional.
Jacob
:confused:
Jacob,

Thanks for catching the mistake. All the data of the World Ranking is correct except for the column Wins (column C). The program overwrote that column with the number of matches played (column F) by mistake which is why the two columns had the same value. I have posted version 3 to correct the mistake.

Ed Law 24-03-2011 00:17
Re: paper: Scouting Database from Team 2834
 
Here is the corrected Top 10. Again, only the column Wins posted before was wrong.

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

Ed Law 26-03-2011 23:59
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.

Code:
Team        Wins        Losses        Ties        Played        QS        RS        MP        Avg Score        OPR        OPR Rank        Avg WM        CCWM        CCWM Rank        Record
1114        32        1        0        33        64        37.55        150        33.53        72.40        1        20.88        59.30        1        (32-1-0)
111        31        2        0        33        62        39.64        135        32.62        63.83        2        18.98        47.91        7        (31-2-0)
148        14        1        0        15        28        19.73        135        28.24        59.68        3        21.33        53.66        4        (14-1-0)
2054        32        5        0        37        64        27.89        135        27.10        57.15        4        16.95        56.67        2        (32-5-0)
33        17        1        0        18        34        21.94        114        24.31        56.22        5        16.80        54.01        3        (17-1-0)
987        14        4        0        18        28        37.11        135        24.17        52.28        6        8.85        15.55        124        (14-4-0)
217        32        5        0        37        64        27.65        122        27.75        51.70        7        17.84        45.36        9        (32-5-0)
2363        15        1        0        16        30        26.81        116        25.19        51.07        8        14.65        41.86        13        (15-1-0)
2056        29        6        0        35        58        27.06        150        29.26        50.51        9        18.95        44.76        10        (29-6-0)
1678        15        1        0        16        30        15.81        99        21.19        49.45        10        15.63        44.26        11        (15-1-0)
1114 continues to increase their OPR and CCWM and is now ranked #1 in both. Their MP is an amazing 150 from a match this weekend.

Jacob Plicque 27-03-2011 18:38
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
:D

Ed Law 27-03-2011 19:27
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.

Jacob Plicque 27-03-2011 23:02
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.
:cool:

bobosalad 28-03-2011 00:46
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.

Ed Law 28-03-2011 01:05
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"

shocker4256 31-03-2011 20:17
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

Ed Law 31-03-2011 23:53
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by shocker4256 (Post 1047945)
Is there any reason why I can't seem to populate the CT match list correctly? I get times and team numbers and scores for Qualifications but they seem random....

edit: It also seems like the Qualification data that it is pulling is going where the elimination data normally is
The links for future events are still pointing to last year's events. I cannot setup the new links ahead of time when the web pages did not exist until Thursday or Friday. This was possible before in Excel 2003, but this does not seem to be possible with Excel 2007 and 2010. This will be my summer project if I have time to automate this as much as possible so I can set them up quickly. Doing it manually is quite tedious when there are so many events.

People who knows Excel well enough are able to change the links themselves to point to the current data.

Ed Law 01-04-2011 12:18
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.

Ed Law 01-04-2011 13:34
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.

Ed Law 04-04-2011 00:25
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.

Vikesrock 04-04-2011 00:29
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 1049047)
North Star finals match results incomplete
Oddly enough they are. Match 2 was actually a 0 to 0 win for the Blue Alliance. Blue ended with a score of 0 after penalties, but Red had a DQ so they lost, forcing a third and deciding match.

Ed Law 04-04-2011 00:55
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Vikesrock (Post 1049050)
Oddly enough they are. Match 2 was actually a 0 to 0 win for the Blue Alliance. Blue ended with a score of 0 after penalties, but Red had a DQ so they lost, forcing a third and deciding match.
Thanks for letting me know. That was strange. I can't tell and my spreadsheet can't tell who won that match when it is 0-0. I thought that was a tie and required a 4th match that was not posted. I knew the outcome from the Awards page.

RMiller 04-04-2011 01:05
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 1049060)
Thanks for letting me know. That was strange. I can't tell and my spreadsheet can't tell who won that match when it is 0-0. I thought that was a tie and required a 4th match that was not posted. I knew the outcome from the Awards page.
Can you manually edit the spreadsheet to have something like 0.000001 as a score? I am not familiar with the spreadsheet and do not have Excel installed on this computer otherwise I would check.

Ed Law 13-04-2011 21:17
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.

Travis Hoffman 13-04-2011 21:45
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 1052561)
Week 6 results are out. All previous missing and wrong data from FIRST have been corrected and incorporated in this version. Keep in mind that the True World Ranking is from the whole season's performance and not an indication of strength going into Championship. The latest OPR may be a better indication of that.

I will be posting another file once the division data is out. I will also add the web links to the divisions on Thursday so teams can update the OPR numbers during the competition.

Let me know if you have any questions.
Like! Ahead of Saturday, too.

techvikesmom 14-04-2011 10:28
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!!

Vikesrock 14-04-2011 10:38
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by techvikesmom (Post 1052715)
Where can I find your spreadsheet?? I have found in the previous weeks and I would love to find it today!! Thanks for the all the great input!!
If you go the very first post in this thread and click the link you will be brought to a page with a number of different versions of the database on it. The latest one is "Team 2834 2011_Scouting_Database week 6 "

Here is another copy of the link in the first post:
http://www.chiefdelphi.com/media/papers/2174?

Here is a direct download link for the latest version:
http://www.chiefdelphi.com/media/papers/download/3056

techvikesmom 14-04-2011 21:02
Re: paper: New Scouting Database from Team 2834
 
thank you for the links!!!

Ed Law 18-04-2011 08:53
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)

Travis Hoffman 18-04-2011 11:19
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 1053982)
The divisions data is now included. I fixed the calculation for weighted average for 2011. You can select the option you prefer to rank teams.

Today is tax return due date. I looked at the number of downloads for the presentation file that explains the scouting database. Guess what the number is. (1040)
Thanks, Ed!

Can you please explain in more detail the calculation on the WM Results tab, column G, "Wt. Avg. CCWM", when calculated in "default" mode? How is that number obtained? How is the weighting done?

Here are your Top 25, sorted by that metric:

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

Ed Law 18-04-2011 11:39
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Travis Hoffman (Post 1054021)
Thanks, Ed!
Can you please explain in more detail the calculation on the WM Results tab, column G, "Wt. Avg. CCWM", when calculated in "default" mode? How is that number obtained? How is the weighting done?
Here is the quick answer directly from the macro.
Code:
    Case 2 To 7
        Range("G1").Select
        ActiveCell.Cells = "Wt.Avg" + cword
        For i = 1 To numrow
            rscale = 0
            rvalue = 0
            For j = 1 To numcol
                If (a(i, j) <> "") Then
                    If (a(i, j) <> 0) Then
                        rscale = rscale + 1 / (mcal - week(j))
                        rvalue = rvalue + a(i, j) / (mcal - week(j))
                    End If
                End If
            Next j
            If (rscale <> 0) Then
                rrvalue(i) = rvalue / rscale
            Else
                rrvalue(i) = -99
            End If
        Next i
When using default, mcal=7 means the weighed average is projected to Week 7. Basically the idea is earlier weeks has less weight than later weeks based on number of weeks to the projected week.
To translate this to English for the exact formula, it will have to wait until I have time after work.

Travis Hoffman 18-04-2011 15:50
Re: paper: New Scouting Database from Team 2834
 
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?

The Lucas 18-04-2011 17:17
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Travis Hoffman (Post 1054114)
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?
I assume it is a space issue since Ed already has to zip it for CD to accept it and Excel 2003 format would be significantly larger. It would be nice if someone could figure out what part of the spreadsheet crashes the 2003 converter (I suspect the OPR Results sheet with all its buttons and some of the colors don't exist in Excel 2003 :rolleyes: ).

I have had success opening the spreadsheet in a new Windows version of OpenOffice. I was then able to copy (to Excel 2003 format) the WorldRank sheet and all its precious data :)

Ed Law 18-04-2011 22:46
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Travis Hoffman (Post 1054114)
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?
I tried many different ways to save it to Excel 2003 format but it crashed every time no matter what I did. I tried taking out all the colors and macros that I can find but it still crashed.
Yes it was a space issue. The file got too big with more teams and more events. I also added the World ranking sheet.
Does anybody know what the file size limit is?

Ed Law 18-04-2011 23:52
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Travis Hoffman (Post 1054021)
Thanks, Ed!

Can you please explain in more detail the calculation on the WM Results tab, column G, "Wt. Avg. CCWM", when calculated in "default" mode? How is that number obtained? How is the weighting done?
The weighted average is calculated this way. This year there are 6 weeks of competition. By default, the weighted average is projected to the week after which is 7.
The OPR from each event is scaled by multiplying the reciprocal of the number of weeks between the event and week 7. For example if the event was in week 3, then the OPR (x) will multiply by 1/4. Let's say this team also attended a Week 5 event, that OPR (y) will multiply by 1/2. The weighted average will be (x/4+y/2)/(1/4+1/2). In this case, the week 5 event will have double the weight than the week 3 event since x is divided by 4 and y is only divided by 2.
I hope this makes sense. There is nothing special about this way of assigning weight. There can be many other ways to do it. The idea is the events closer to week 7 should have a higher weight than events that are earlier. I did it this way out of simplicity and convenience. You can use a quadratic function or exponential function if you wish.

Ed Law 20-04-2011 23:18
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.

Ed Law 21-04-2011 20:53
Re: paper: New Scouting Database from Team 2834
 
I did not realize they added some teams. Version 2b reflects the changes.

PSHRobotics 29-04-2011 20:16
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:
Quote:
Run-time error '1004':

Application-defined or object-defined error
If you need any help debugging I'd be happy to help, although my experience with complex excel functions and visual basic is very limited.

Edit: I did change the shortcut to ctrl-u and the same problem occurred.

Vikesrock 29-04-2011 20:19
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by PSHRobotics (Post 1057494)
Thank you so much for the work you have put in!
Although it is not that big of a deal for me at the moment, I do want to let you know for other people and for the future. When I use ctrl-shift-p to refresh, I get an error that says:


If you need any help debugging I'd be happy to help, although my experience with complex excel functions and visual basic is very limited.

Edit: I did change the shortcut to ctrl-u and the same problem occurred.
The links for the division data are wrong. If Ed doesn't see this and post a fixed version, I can post one when I get to my hotel tonight, probably around 11.

Ed Law 29-04-2011 22:06
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.

Ed Law 29-04-2011 22:20
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.

Vikesrock 29-04-2011 23:52
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 1057513)
Version 3 is out. It contains data through Friday matches at Championship.
Thanks Ed!

Ed Law 01-05-2011 18:24
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.

Ed Law 02-05-2011 17:23
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

Ed Law 30-07-2011 00:40
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Travis Hoffman (Post 1054114)

Thanks, Ed. I thought it was a "what have you done for me lately" kind of deal.

Another question, one you've been asked before, likely. When I attempt to open your spreadsheet in Excel 2003 (I have the Office 2007 compatibility pack), it always seems to freeze up.

Is there a way to get your spreadsheet to work as an Excel 2003 xls, or perhaps is there a way to re-save it in another 2007 format such that it can be opened in 2003?
I finally was able to save it in 2003 format. It is version 4b. After I zipped the file, the file size is actually smaller than the zipped 2007 version. Quite surprising.

Ed Law 27-02-2012 02:27
Re: paper: New Scouting Database from Team 2834
 
The 2012 Scouting Database spreadsheet is ready. It is at http://www.chiefdelphi.com/media/papers/2174

I created links of standing and match results for all the events already so all you have to do is refresh it at the event. I will test it to make sure they work during the Week 1 events. This is the first year we are not going to a Week 1 event so I will have more time to check the results and post them immediately afterwards.

I know there are discussions of OPR before or after penalties. I will try to address them at a later time.

Ed Law 06-03-2012 03:00
Re: paper: New Scouting Database from Team 2834
 
Week 1 results are posted. It took a little longer to get this done because FIRST changed the format of the team standing page and I have to modify quite a bit in the macro.

I also fixed all the links for all future events. They should be working now.

Ed Law 06-03-2012 13:17
Re: paper: New Scouting Database from Team 2834
 
Good News. I was able to get the OPR of Hybrid, Bridge, Teleop and Coopertition separately. It is much easier than I thought. It took about 20 lines of code and 20 minutes. I put the OPR results in each event. I put it all the way to the right so it does not mess up the columns of other data.

Would somebody please compare my numbers with numbers published by others to see if it is consistent?

To get the True World OPR for each individual component will take more work. I will add it in the next few weeks.

Ed Law 12-03-2012 00:26
Re: paper: New Scouting Database from Team 2834
 
Week 2 results are out. New true world ranking is posted. The highest OPR is now 36.71 from Team 2056 folowed closely by Team 67 with OPR of 36.16

All the links of week 2 events that I put in last week worked. Just use Cntl-Shirt-P to run the macro and you will get the overall OPR as well as OPR for hybrid, bridge, teleop and coopertition during the event.

Mike Starke 17-03-2012 23:40
Re: paper: New Scouting Database from Team 2834
 
Hey Ed!
First off, thank you SO MUCH for doing this!
Secondly, would it be at all possible to have the file available as a ".xls"? My Mac doesn't seem to like the .xlsx
Thanks! If it works better for you to email it to me, shoot me a DM!
Thanks!

Ed Law 18-03-2012 00:34
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Mike Starke (Post 1145453)
Hey Ed!
First off, thank you SO MUCH for doing this!
Secondly, would it be at all possible to have the file available as a ".xls"? My Mac doesn't seem to like the .xlsx
Thanks! If it works better for you to email it to me, shoot me a DM!
Thanks!
Please PM me your email address. You have no contact info on CD.

Doc Wu 19-03-2012 08:19
Re: paper: New Scouting Database from Team 2834
 
This is an amazing spreadsheet. As someone who used to write Lotus 1-2-3 macros back in the day, I am in awe.

I was able to run the macros to update the week 3 regionals successfully. How do I get that data merged into the OPR and WM tabs?

Thanks...

Ed Law 19-03-2012 10:16
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Doc Wu (Post 1146029)
This is an amazing spreadsheet. As someone who used to write Lotus 1-2-3 macros back in the day, I am in awe.

I was able to run the macros to update the week 3 regionals successfully. How do I get that data merged into the OPR and WM tabs?

Thanks...
I am glad it worked out for you. Every week after the events are over, I update the OPR numbers, check to make sure all results FIRST posted are correct, and then I transfer that week's data to the OPR and WM tabs. I guess I am performing the Quality Control function and make sure there is only one set of data in the CD community instead of everybody running them and potentially have slightly different numbers.

Week 3 data were already posted yesterday. There is a separate post to discuss each week's data. See http://www.chiefdelphi.com/forums/sh...hreadid=104743

marccenter 19-03-2012 13:12
Re: paper: New Scouting Database from Team 2834
 
Dr Ed Law,
Thanks for revising the database to add points for teams that elected to gather coopertition points for their alliance. At the Michigan Waterford District tournament we found ourselves often in a position that we were the most consistent bridge climbing team and other opposing alliance often met with us to discuss going together on the white bridge to gain this valuable seeding points. We could have climbed the alliance bridge for 10 points but elected to go the other route. Should the coopertition points really be 10 points instead of the 5 shown in your database? We still climbed a bridge and balanced?

Ed Law 19-03-2012 13:29
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by marccenter (Post 1146132)
Dr Ed Law,
Thanks for revising the database to add points for teams that elected to gather coopertition points for their alliance. At the Michigan Waterford District tournament we found ourselves often in a position that we were the most consistent bridge climbing team and other opposing alliance often met with us to discuss going together on the white bridge to gain this valuable seeding points. We could have climbed the alliance bridge for 10 points but elected to go the other route. Should the coopertition points really be 10 points instead of the 5 shown in your database? We still climbed a bridge and balanced?
We are giving 5 bridge points for every 1 coop point. However when you balance on the coop bridge in each match, you get 2 coop points which means you are getting 10 bridge points. If you had gone to the alliance bridge by yourselves or balance with a partner, it is also 10 points extra, so it is the same. If you feel that you want to look at it differently by sorting using more than 5 bridge points for every 1 coop point, you can change the number in cell AF1 to whatever you like.

Ed Law 19-03-2012 14:02
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Mike Starke (Post 1145453)
Hey Ed!
First off, thank you SO MUCH for doing this!
Secondly, would it be at all possible to have the file available as a ".xls"? My Mac doesn't seem to like the .xlsx
Thanks! If it works better for you to email it to me, shoot me a DM!
Thanks!
Mike, it is your lucky day. Excel finally cooperated and let me write it out in .xls format.
Ether, now you can look at the data using Excel 2003.

Ether 19-03-2012 14:07
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Ed Law (Post 1146167)
Mike, it is your lucky day. Excel finally cooperated and let me write it out in .xls format.
Ether, now you can look at the data using Excel 2003.
Thanks Ed !

Ed Law 22-04-2012 23:27
Re: paper: New Scouting Database from Team 2834
 
The database has been updated with Divisions information and links were set up for use at Championship to easily update the OPR etc. if you have access to the internet.

Good luck to all at the Championship. Our team is in Newton division. Stop by to say hi. I would love to hear from you and how you use this database for your team.

Jacob Plicque 23-04-2012 12:35
Re: paper: New Scouting Database from Team 2834
 
Ed,
Thanks for the update. Your efforts make it a lot easier for Team 86 and surely others to find the diamond in the rough.
In my previous email, the playoffs were correctly predicted by the eliminations about 70% of the time. Have you considered calculating the playoff rounds with the elims to raise the accuracy of the predictor?
See you in St Louis.
Jacob:cool:
Mentor Team Resistance
Newton Division

Ed Law 24-04-2012 00:01
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Jacob Plicque (Post 1161906)
Ed,
Thanks for the update. Your efforts make it a lot easier for Team 86 and surely others to find the diamond in the rough.
In my previous email, the playoffs were correctly predicted by the eliminations about 70% of the time. Have you considered calculating the playoff rounds with the elims to raise the accuracy of the predictor?
See you in St Louis.
Jacob:cool:
Mentor Team Resistance
Newton Division
We are in Newton Division too. I will look forward to seeing you. Perhaps we can share our scouting data.

Ed Law 27-04-2012 09:38
Re: paper: New Scouting Database from Team 2834
 
Championship data version 2 is out with Thursday's results. I also fixed some of the link problems that I can find.

Siri 27-04-2012 21:24
Re: paper: New Scouting Database from Team 2834
 
Hi Ed,

We seem to be having a problem with updating Thursday's database for Friday's results. Crtl+Shift+P updates the ranking and official FIRST data, but the OPR data doesn't appear to change. Are we missing something? Thanks

Ed Law 28-04-2012 00:59
Re: paper: New Scouting Database from Team 2834
 
Championship data Version 3 including Thursday and Friday results are uploaded. I fixed one problem with the OPR data calculation. However there is something very strange going on with the links. It keeps changing the link to KC, NH, TN and STX. If the Ctrl Shift P does not work, you will need to manually copy and paste into the spreadsheet.

A lot of these latest problem with the spreadsheet is because the spreadsheet was developed not expecting any regional to have more than 97 teams. Since we have 100 teams in each division, there was not enough room to put the standing data between 1 and 100. There are also more matches than I have allocated room for. I have to add rows and there were a lot of changes in the macros and formulas that I sometimes missed.

After the championship is over, I will need to clean this up and probably delete and redo all the links once and for all.

faust1706 18-03-2013 00:12
Re: paper: New Scouting Database from Team 2834
 
wow. that is truly an amazing work of excel. Thanks for posting this, cant even begin to imagine how it was made!

Siri 18-03-2013 00:56
Re: paper: New Scouting Database from Team 2834
 
A note to passers-by, this is the 2012 thread. (Took me a minute.) 2013 is here: http://www.chiefdelphi.com/forums/sh...d.php?t=115101

Ed Law 18-03-2013 01:10
Scouting Database from Team 2834
 
Quote:
Originally Posted by faust1706 (Post 1249425)
wow. that is truly an amazing work of excel. Thanks for posting this, cant even begin to imagine how it was made!
Thanks. It is through a combination of Excel formulas and VBA. Very little is done by hand.
This was the original thread from some years ago. I still put the weekly data here to make it easier for people to find it. Each week I create a new thread to let people know new data is available.

Citrus Dad 19-05-2013 14:47
Re: paper: New Scouting Database from Team 2834
 
There appears to be an error in the OPR calculation for the 2013 Championships. The sum of the individual OPRs diverges significantly from the total OPR, whereas the differences in the regionals are much smaller. I suggest going back and checking the calculations in that version.

Ether 20-05-2013 17:15
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Citrus Dad (Post 1275929)
There appears to be an error in the OPR calculation for the 2013 Championships. The sum of the individual OPRs diverges significantly from the total OPR, whereas the differences in the regionals are much smaller. I suggest going back and checking the calculations in that version.
redirect here:

http://www.chiefdelphi.com/forums/sh...30#post1276030

Ed Law 24-02-2014 01:15
Scouting Database from Team 2834 (2014)
 
The database is ready for 2014. Everything should work for calculating the OPR/CCWM during the competition. I cannot test it since the links are not up yet. It will also calculate the sub-OPR for Assist, Autonomous, Truss & Catch and Teleop. I will check it on Friday to make sure it works. If there are any problems, I will post any correction ASAP.

marccenter 28-02-2014 22:35
Re: paper: New Scouting Database from Team 2834
 
Ed,

Thanks for doing this. It didn't seem to work for me as expected. Will you be posting a new version, soon.

The CTRl-SHift-P did provide some data but ended with an error code (not expected). The subsequent Ctrl_Shift-O
did not populate data either (also debug message).

jwhite 01-03-2014 09:27
Re: paper: New Scouting Database from Team 2834
 
It's easy to fix - pop into the debugger and do a find for 5000; change all occurrences of 5000 to 6000, and the spreadsheet goes back to working it's wonders.

Cheers,

Jeremy

Jacob Plicque 01-03-2014 19:26
Re: paper: New Scouting Database from Team 2834
 
Ed
I am getting a runtime "13" error. :rolleyes: After fixing the 5000 with a 6000 for some of the regionals

Ed Law 01-03-2014 21:57
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by jwhite (Post 1351319)
It's easy to fix - pop into the debugger and do a find for 5000; change all occurrences of 5000 to 6000, and the spreadsheet goes back to working it's wonders.

Cheers,

Jeremy
I published a new one to fix the 5000 limit and a few minor errors. It is hard to anticipate everything and with no way to test before publishing. 6 years ago when I wrote this, 5000 seems to be a very big number as the teams were only in 2000 range. I have completely forgotten about it.

Banderoonies 10-03-2014 20:12
Re: paper: New Scouting Database from Team 2834
 
On behalf of team 195, I want to thank you for the excellent work you do in putting this data together.

olapmonkey 11-03-2014 01:15
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Jacob Plicque (Post 1351539)
Ed
I am getting a runtime "13" error. :rolleyes: After fixing the 5000 with a 6000 for some of the regionals
I too am getting errors on many of the tabs when I try the ctrl-shift-p combination. Additionally, it is unclear to me on how the WM data and OPR data tabs update. If I try ctrl-shift-P they error as well and currently they are only showing partial information. Any ideas?

Thanks for putting this together Ed. It looks to be quite useful.

Ed Law 11-03-2014 01:21
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by olapmonkey (Post 1357140)
I too am getting errors on many of the tabs when I try the ctrl-shift-p combination. Additionally, it is unclear to me on how the WM data and OPR data tabs update. If I try ctrl-shift-P they error as well and currently they are only showing partial information. Any ideas?

Thanks for putting this together Ed. It looks to be quite useful.
Can you give me one example of which one does not work? All week 2 events work for me. There was one event in Week 1 that did not work because of something I did.

Make sure you do not have other spreadsheets open that have macros, especially older versions of the scouting spreadsheets.

What version of Excel are you using and what kind of computers are you using?

theawesome1730 16-03-2014 23:08
Re: paper: New Scouting Database from Team 2834
 
On a Mac running office 2011 what would be Ctrl+Shift+P? I would think Cmd+Shift+P but that opens a new project

Ed Law 16-03-2014 23:26
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by theawesome1730 (Post 1360031)
On a Mac running office 2011 what would be Ctrl+Shift+P? I would think Cmd+Shift+P but that opens a new project
Sorry I have no experience with Mac. The shortcut is just for convenience on a PC. You can open up the macro and manually run the macro in Module 2 called "RefreshData". It is on the top of all the macros in Module 2.

Ether 25-03-2014 16:10
Re: paper: New Scouting Database from Team 2834
 

Is there anyone out there in the CD community who has managed to figure out a way to convert Ed's xlsm spreadsheet so that it can be opened with Excel 2000 ?

BigBen 26-03-2014 07:47
Re: paper: New Scouting Database from Team 2834
 
First, AWESOME spreadsheet!

Quote:
Originally Posted by Ed Law (Post 1357147)
Can you give me one example of which one does not work? ........What version of Excel are you using and what kind of computers are you using?
I am using Windows 7 Home Premium and Excel 2013.

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

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

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

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

Citrus Dad 28-03-2014 12:54
Re: paper: New Scouting Database from Team 2834
 
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 30-03-2014 17:40
Re: paper: New Scouting Database from Team 2834
 
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.

Quote:
Originally Posted by BigBen (Post 1364976)
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 30-03-2014 17:45
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Citrus Dad (Post 1365977)
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 31-03-2014 00:37
Re: paper: New Scouting Database from Team 2834
 
Quote:
Originally Posted by Citrus Dad (Post 1365977)
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


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

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