HOW TO MAKE AN OPPONENT RECORD HISTORY
(WITH TOTAL RATING DELTA)
by Hogan Long


The DCI has finally gotten their act together and now legend members can view
all kind of neat stuff on the DCI internet site including a tournament history.
Unfortunately they do not give you the option to see your personal records vs.
each opponent.  Well fear not... with these simple steps (and a copy of MS
Excel) you can have a list of all your opponent match records.

For the Excel-Inept:  You can select a column by clicking on the
letter of the column.  You can select a row by clicking on the number for the
row.

[I have also figured out how to make a total rating delta (for the math-
illiterate, delta refers to change.  Thus a total rating delta would be the
total of all rating point gains and losses from an individual.)  For those that
don’t want to use this feature ignore all the text enclosed in braces.]

Here is how to make an opponent record history:

1) Download your tournament record from the DCI (you must be a legend member to
   do this).

2) Edit the file, take out all the dates and rating information so that each
   line has just the players name and the match result.  [Leave the rating
   information in if you want a total rating delta.]  I suggest a good text
   editor for this process - brief, codeview, or some other programming
   environment.  If you have to use Word or some other word processor make sure
   to do the following - use tabs between columns and save the file as a text
   file with line breaks. [For RD include a column with the current rating
   value after the win/loss/draw column.]  When you are done editing the file
   you need to have should have everything line up in columns - for example:
      Joe Scrub        Win
      I. B. Proplayer  Loss
      Mr Knowitall     Loss
      Kant Pulleh      Win
    [with rating it would look like like this:
      Joe Scrub        Win   1607
      I. B. Proplayer  Loss  1602
      Mr Knowitall     Loss  1594
      Kant Pulleh      Win   1609]

   If you don't have a fixed font this may not look right... make sure and use
   ONE tab in between each column so the import to Excel will work.

3) Import the file to excel.  Column A should contain all the players names and
   column B should contain the Win/Loss/Draw information.  [column C would have
   the current rating values]

4) Select column B. and press copy.  Now select Column C and press paste, then
   select column D and press paste. You should now have four columns where B, C,
   and D are all the same.   [if the rating values are included you need to
   insert two columns before it prior to performing the above step.]

5) a) Select column B.  Use the replace (in selected) to replace "Win" with "1".
      Then replace "Loss" with "0".  Then replace "Draw" with "0".  You should
      now have a column B with a one in every place a "Win" was and a zero
      everywhere else.

   b) Select column C.  Use the replace (in selected) to replace "Win" with "0".
      Then replace "Loss" with "1".  Then replace "Draw" with "0".  You should
      now have a column C with a one in every place a "Loss" was and a zero
      everywhere else.

   c) Select column D.  Use the replace (in selected) to replace "Win" with "0".
      Then replace "Loss" with "0".  Then replace "Draw" with "1".  You should
      now have a column D with a one in every place a "Draw" was and a zero
      everywhere else.

[The following two steps are only performed if you want total rating delta:
5.1) In column F you are going to calculate the rating delta for each match.
     to do this you need to subtract the last ranking value from the current.
     (This will tell you the delta of your ranking.)  For example, in cell
     F1 put =E1-1600, then in cell F2 put =E2-E1.  Now copy cell F2.  Then
     paste this to all the cells in the F column except F1.  The easy way to
     do this past is to select all the cells after some data in the first 3
     columns, then hit paste.  The F column now shows how many points you won
     or lost for each match.  (The rating delta).

5.2) Now select column F and press copy.  Select column G and press
     Edit|Paste Special...  In the dialog box select “Values Only”.  Column G
     now looks just like column F but it does not have the formulas.  Delete
     columns E and F before the next steps.  NOTE: You need to perform these
     steps BEFORE you sort the data.
]

6) [optional] I recommend to change all the unknowns at this time to the same
   name (you can do this after step 7, and then redo step 7 again - this is the
   easiest way).

7) Select all data.  From the menus select Data then Sort.  It should default
   to sort by column A - this is good.

8) Select row 1.  Now select insert row.  You should have a new blank row at
   the top of the sheet.  In A1 type Player, in B1 type Win, in C1 type Loss,
   in D1 type Draw.  [in cell E1 type Delta.]

9) Now for the fun part.  Select all 4 columns.  From the menu select Data then
   Subtotals.  In the dialog box set:
   -> at each change in PLAYER
   -> use function SUM
   -> click on the check box next to win, loss, and draw.  [and Delta]
   -> Make sure the replace totals item is NOT checked.
   -> [optional] You can check the summary below data to get a grand total at
      the bottom.
   Click OK.

10) Wait a 'till it is done.  Now for the fun part... see the funky lines on
    the left.  At the top you will see three buttons (numbered 1, 2, and 3)
    click on the one numbered 2.  Now all you see is a line for each player
    with your record for that player.  FUN EH?  For easy print out I suggest
    you select the data and copy it MS Word, then format.  (I used 3 columns
    on a horizontal page with a 9 point font.)

If you have any questions feel free to write me at hogan@akula.com
(I prefer questions about this article.)

Hogan Long
Team Grumpy Old Men
NYC  8-17-98
http:\\www.akual.com\~hogan\magic
Limited match record 479-279-22 (is that good?)