[CQ-Contest] in depth score (and log?) analysis using a spreadsheet

[wx3m at comcast.net]

I think the idea of web-accessible line scores is an excellent idea.

On the ARRL scores page(s), the ability to sort the scores that you view is
really great. However, I dislike the HTML/Java interface. It is capable and
user-friendly, I just don't like it, and really for only three (trivial)
reasons:
1. It will only display 25 entries at a time.
2. It involves a lot of data being transmitted and received over your
internet connection.
3. You can only view one "page" at a time, unless you open multiple browser
windows.
I have a cable modem (and a pretty fast one, at that), and many others do,
so maybe #2 is a weak reason. However, somebody with a dial-up (or other)
connection might be concerned about the amount of time it takes to transfer
data back and forth repeatedly.

All that aside, there is another way to sort and view the data, using a
spreadsheet. The forms that are output by the web-page are essentially a
spreadsheet, just formatted into HTML and displayed in your browser. The raw
data that this "utility" uses to generate the sorted displays is available
for download. You can then import the data from this file into a spreadsheet
(Excel). Once in Excel, you can essentially do the same thing that the
web-page allows you to do. However, because the data and the program
manipulating the data are resident on your machine instead of communicating
everything over the 'net, you can work faster. In addition, you can view
all the entries at once, instead of 25 at a time, and create multiple
sheets, each sorted with different criteria, for faster, easier, and more
comprehensive analysis.

Here is how I do it:
1. At the bottom of the DEFAULT scores display list, under "download current
selection (x-records):" right click on the link "tab delimited" and save the
file to your hard drive. The default filename is download.txt. NOTE: When
you view the contest results under their default criteria on the web page,
it is using the entire database, sorted by score only. You will get the same
download options if you enter criteria in the "SELECT ENTRIES TO LIST" or
"CHOOSE SORT ORDER" boxes, but if you have chosen something other than "ALL"
you may not get the entire database.
2. Open Microsoft Excel. You may have or use a different spreadsheet
program, but this is the only one I use and, therefore, the only one I know
HOW to use.
3. Choose FILE --> OPEN --> choose "ALL FILES" from the file types box, then
choose the directory and filename you downloaded from the ARRL web site.
(Remember, the default was download.txt.) Click "OPEN".
4. The next screen (Text Import Wizard 1 of 3) asks how you would like the
data/text formatted when it gets to the spreadsheet. Choose "delimited" and
"start import at ROW 2". Click "next". (I do this because ROW 1 is the
column headers. I really don't need to see them and it sometimes makes the
column widths weird. In addition, they are useless when sorting the data.)
5. The next screen (Text Import Wizard 2 of 3) allows you to choose the
delimiters in the file and to preview what it will look like. Under
"delimiters" check "tabs" and click "next". NOTE: This is because we
downloaded a "tab delimited" file. If we downloaded a "comma delimited"
file, we would choose that option at this time.
6. The next screen (Text Import Wizard 3 of 3) asks you to format the cells
(based on column) to accept the particular type of data in that column. Just
click "finish" and it will choose "general" for all of them. Numbers will
automatically be converted to numeric values and treated as numbers, calls
as text, dates as dates, etc.

You should now have a recognizable display of callsigns and scores. Save
this file as a Microsoft Excel Workbook (*.xls) using whatever name you
want.

To sort the results:
1. "Select" the entire sheet. This is done by clicking the blank box above
ROW 1 and to the left of COLUMN 1. The entire sheet should turn purple.
2. Under menu item "DATA", choose sort. Choose which columns you wish to
sort by, and in what order, then sort. I use descending, that way the
highest values are at the top instead of the bottom, and resemble the way
scores have been traditionally published in QST.

Voila! You can now sort and view the scores a lot faster than through the
web-based interface. In addition, you can create several sheets in a
workbook, each one being sorted by a different criteria. You can compare and
analyze very thoroughly, very easily.
To create multiple sheets in a workbook, each sorted by different criteria:
1. choose EDIT --> MOVE OR COPY SHEET...
2. check the box for "create copy"
3. choose which sheet you would like it to appear before (not very
important)
4. click OK
You now have an exact replica of the original data. You can choose to sort
this data by different criteria, so you can quickly switch between the two
for comparison. If you right-click on the tab (at the bottom of the sheet)
for the name of the sheet, you can rename each sheet based on its sort
order.

After the 2002 Michigan QSO party, I did this with my log for some analysis.
I created 5 sheets, sorted thus:
1. UTC
2. CALL
3. QTH
4. BAND then MODE
5. MODE then QTH
I was able to (at a glance) figure out how many times we worked particular
stations, sections, etc. In addition, I could check for mistakes (see
below).

I hope that somebody might be able to use this information to analyze the
ARRL line scores to learn about their own performance and improve it for
future contesting.

73
Don
WX3M

P. S. This can also be done on a Cabrillo (et al) log . . .

You can import it the same way, and sort by time, call, exchange, etc, to
analyze who you worked, where, when, how often, and so forth.

I suppose this raises an ethical issue, though. Those who subscribe to the
opinion that "when the contest is over, you cannot edit the log" would not
approve of this. However, I do not entirely subscribe to this school of
thought. If there is a 30 (15?) day period to turn in you log, then it seems
to me that the time is given to "check for mistakes" and clean it up.

The level of analysis described above is very useful for checking for
innocent keyboard boo-boos or other errors. For instance, after the 2002
Michigan QSO party, I had 20 separate files - one from each county W8UE and
I worked. I merged them all into one spreadsheet for comparison. I could
sort by callsign and make sure we got all of the QTHs correct. If we worked
AD1C nineteen times (which we did), but one of the times we logged his QTH
as MS instead of MA, it would be easy to spot and fix. This could be caused
by keyboard error or LID ears, but I think it would be OK to fix the
mistake. After all, my reference is my own log, created during the contest.
If an operator looks up a callsign on www.qrz.com to confirm a QTH, grid, or
other vital QSO information during analysis after the contest (or even
during the contest), s/he is  using a forum outside of the contest and this
is, in my eyes, way beyond the spirit of honest competition.