Sport Results Management


September 2007

Below you will find a desciption on how to:

  1. Automate creation of a csv file, comma seperated values, suitable for import into a spread sheet. The spread sheet describes a tournament with groups, teams and play-off.
  2. UltraEdit macro that transforms the spread sheet export into an html document suitable for upload to an internet site, i.e. take a look here!


Creation of a Csv File

A simple c# program, fkc01.exe, has been written that takes a number of parameters describing the tournament. Output is a csv file.
If you look at one division, i.e. YH in the tournament you will see that a division can be described by:

  1. Division name, i.e. YH
  2. Number of rounds in play-off, i.e. 2
  3. Number of groups, i.e. 2
  4. Number of teams in each division groups i.e. 4
  5. List of team names in division. Number of groups times number of teams in each group. 

This gives a number of parameters:


YH 2 2 4 BMS BKA Herlev Roskilde Hørsholm SISU Værløse Falcon 


Now, you are likely to have several divisions in a tournamet and you just repeat the set of parameters for each division.


In the case of this ex. you will have this parameter line. This set of parameters describes all the divisions with all groups and teams:


YH 2 2 4 BMS BKA Herlev Roskilde Hørsholm SISU Værløse Falcon YD 2 2 4 Værløse Åbyhøj Falcon Hørsholm SISU Malbas BKA Virum JH 3 3 4 Hørsholm-1 SISU-2 BMS Falcon HorsensIC Værløse-2 SISU-1 Brønshøj Jonstrup BKA Hørsholm-2 Værløse-1 JD 3 4 4 Gimle BKA-93 Herlev Hillerød-1 SISU Hillerød-2 BKA-92 Lobas Hørsholm-1 Falcon BMS Virum Malbas Værløse Jonstrup Hørsholm-2 DR 3 4 4 Brønshøj Hørsholm Lobas Falcon-2 BKA Hillerød Malbas-95 BMS Malbas-94 Falcon-1 SISU-2 Jonstrup Værløse Køge SISU-1 Nässjö PI 3 3 4 Falcon BKA Hørsholm-2 Værløse SISU-1 BMS Jonstrup Lobas Åbyhøj Brønshøj Hørsholm-1 SISU-2 


Running the c# program, fkc01.exe, wtih the parameters above gives output that you would pipe to a file. You will note spread some sheet functions in the output. These functions add the scores in the group. Please note that the c# program, fkc01.exe, is called through a batch program, FKC01.BAT, so we can maintain the parameters in the batch file. Only part of the output has been shown:


FKC01.bat | more
<b>YH</b>
;
<b>Play-off</b>
;
Finale
Semi1
Semi2
;
<b>Groups</b>
;
<b>YH1</b>;BMS;BKA;Herlev;Roskilde;Score;Placering
BMS;X;0-0;0-0;0-0;"=TEXT(VALUE(LEFT(C12;FIND(CHAR(45);C12)-1))+VALUE(LEFT(D12;FIND(CHAR(45);D12)-1))+VALUE(LEFT(E12;FIND(CHAR(45);E12)-1));0)&CHAR(45)&TEXT(VALUE(mid(C12;FIND(CHAR(45);C12)+1;10))+VALUE(mid(D12;FIND(CHAR(45);D12)+1;10))+VALUE(mid(E12;FIND(CHAR(45);E12)+1;10));0)"
BKA;0-0;X;0-0;0-0;"=TEXT(VALUE(LEFT(B13;FIND(CHAR(45);B13)-1))+VALUE(LEFT(D13;FIND(CHAR(45);D13)-1))+VALUE(LEFT(E13;FIND(CHAR(45);E13)-1));0)&CHAR(45)&TEXT(VALUE(mid(B13;FIND(CHAR(45);B13)+1;10))+VALUE(mid(D13;FIND(CHAR(45);D13)+1;10))+VALUE(mid(E13;FIND(CHAR(45);E13)+1;10));0)"
Herlev;0-0;0-0;X;0-0;"=TEXT(VALUE(LEFT(B14;FIND(CHAR(45);B14)-1))+VALUE(LEFT(C14;FIND(CHAR(45);C14)-1))+VALUE(LEFT(E14;FIND(CHAR(45);E14)-1));0)&CHAR(45)&TEXT(VALUE(mid(B14;FIND(CHAR(45);B14)+1;10))+VALUE(mid(C14;FIND(CHAR(45);C14)+1;10))+VALUE(mid(E14;FIND(CHAR(45);E14)+1;10));0)"
Roskilde;0-0;0-0;0-0;X;"=TEXT(VALUE(LEFT(B15;FIND(CHAR(45);B15)-1))+VALUE(LEFT(C15;FIND(CHAR(45);C15)-1))+VALUE(LEFT(D15;FIND(CHAR(45);D15)-1));0)&CHAR(45)&TEXT(VALUE(mid(B15;FIND(CHAR(45);B15)+1;10))+VALUE(mid(C15;FIND(CHAR(45);C15)+1;10))+VALUE(mid(D15;FIND(CHAR(45);D15)+1;10));0)"
;
<b>YH2</b>;H°rsholm;SISU;Vµrl°se;Falcon;Score;Placering
H°rsholm;X;0-0;0-0;0-0;"=TEXT(VALUE(LEFT(C18;FIND(CHAR(45);C18)-1))+VALUE(LEFT(D18;FIND(CHAR(45);D18)-1))+VALUE(LEFT(E18;FIND(CHAR(45);E18)-1));0)&CHAR(45)&TEXT(VALUE(mid(C18;FIND(CHAR(45);C18)+1;10))+VALUE(mid(D18;FIND(CHAR(45);D18)+1;10))+VALUE(mid(E18;FIND(CHAR(45);E18)+1;10));0)"
SISU;0-0;X;0-0;0-0;"=TEXT(VALUE(LEFT(B19;FIND(CHAR(45);B19)-1))+VALUE(LEFT(D19;FIND(CHAR(45);D19)-1))+VALUE(LEFT(E19;FIND(CHAR(45);E19)-1));0)&CHAR(45)&TEXT(VALUE(mid(B19;FIND(CHAR(45);B19)+1;10))+VALUE(mid(D19;FIND(CHAR(45);D19)+1;10))+VALUE(mid(E19;FIND(CHAR(45);E19)+1;10));0)"
Vµrl°se;0-0;0-0;X;0-0;"=TEXT(VALUE(LEFT(B20;FIND(CHAR(45);B20)-1))+VALUE(LEFT(C20;FIND(CHAR(45);C20)-1))+VALUE(LEFT(E20;FIND(CHAR(45);E20)-1));0)&CHAR(45)&TEXT(VALUE(mid(B20;FIND(CHAR(45);B20)+1;10))+VALUE(mid(C20;FIND(CHAR(45);C20)+1;10))+VALUE(mid(E20;FIND(CHAR(45);E20)+1;10));0)"
Falcon;0-0;0-0;0-0;X;"=TEXT(VALUE(LEFT(B21;FIND(CHAR(45);B21)-1))+VALUE(LEFT(C21;FIND(CHAR(45);C21)-1))+VALUE(LEFT(D21;FIND(CHAR(45);D21)-1));0)&CHAR(45)&TEXT(VALUE(mid(B21;FIND(CHAR(45);B21)+1;10))+VALUE(mid(C21;FIND(CHAR(45);C21)+1;10))+VALUE(mid(D21;FIND(CHAR(45);D21)+1;10));0)"
;
;
<b>YD</b>
;
<b>Play-off</b> 


The batch file FKC01.bat looks like this:


@echo off
fkc01.exe YH 2 2 4 BMS BKA Herlev Roskilde Hørsholm SISU Værløse Falcon YD 2 2 4 Værløse Åbyhøj Falcon Hørsholm SISU Malbas BKA Virum JH 3 3 4 Hørsholm-1 SISU-2 BMS Falcon HorsensIC Værløse-2 SISU-1 Brønshøj Jonstrup BKA Hørsholm-2 Værløse-1 JD 3 4 4 Gimle BKA-93 Herlev Hillerød-1 SISU Hillerød-2 BKA-92 Lobas Hørsholm-1 Falcon BMS Virum Malbas Værløse Jonstrup Hørsholm-2 DR 3 4 4 Brønshøj Hørsholm Lobas Falcon-2 BKA Hillerød Malbas-95 BMS Malbas-94 Falcon-1 SISU-2 Jonstrup Værløse Køge SISU-1 Nässjö PI 3 3 4 Falcon BKA Hørsholm-2 Værløse SISU-1 BMS Jonstrup Lobas Åbyhøj Brønshøj Hørsholm-1 SISU-2 


Source code for the c# program is listed below:


/*
1. sep. 2007


This program accepts a number of input parameters describing a tournament, i.e.
- level/age group
- rounds in playoff
. number of groups
- list of teams
- Optionally repeat the parameters above for a new level/age group
The program outputs a csv file for import into a spread sheet. In the spread sheet game results are entered and spread sheet
can be exported to a txt file for import into UltraEdit. An UltraEdit macro inserts html tags and we have a text string
suited for upload to an internet site publishing tournament results.


Change log
Date By Remarks
25/9/8 TPS Calculate column with points and insert check value for scorediff.
*/


using System;
using System.Text;


namespace FKC01
{
 /// <summary>
 /// Summary description for Class1.
 /// </summary>
 class Class1
 {
  string[] playoff = new String[4] {"Finale", "Semi", "Kvart", "Ottendedel"};
   
  int outputLineNumber = 0;
  
  /// <summary>
  /// The main entry point for the application.
  /// </summary>
  [STAThread]
  static void Main(string[] args)
  {
   //
   // TODO: Add code to start application here
   //
   Class1 mc = new Class1(args);
  }


  // Main class. Call fct. to output tournament program.
  public Class1(string[] args)
  {
   outputCSV(0,args);
  }


  // Output the tournament program. Called recursively for each level/age group.
  public void outputCSV(int start, string[] args)
  {
   int i,j,h;
   string group = args[start];
   int roundsInPlayoff = int.Parse(args[start+1]);
   int groups = int.Parse(args[start+2]);
   int teamsPerGroup = int.Parse(args[start+3]);


   output("<b>" + group + "</b>");
   output(";");
   output("<b>" + "Play-off" + "</b>");
   output(";");


   // Play off
   for (i = 0; i < roundsInPlayoff; i++)
   {
h = 1;
for (j = 0; j < i; j++)
 h = h * 2;
for (j = 0; j < h; j++)
{
 int final = j + 1;
 output(playoff[i]
  + (i == 0 ? "" : final.ToString())
  + ";;?;?;0-0"
  + (i == 0 ? ";;<b>vinder;?</b>" : ""));
}
   }


   output(";");
   output("<b>" + "Groups" + "</b>");
   output(";");
   String line = "";
   for (i = 0; i < groups; i++)
   {
// Group header line
line = "<b>" + group + (i + 1) + "</b>" + ";";
for (j = start + 4 + i * teamsPerGroup; j < start + 4 + i * teamsPerGroup + teamsPerGroup; j++)
{
 line = line + args[j] + ";";
}
output(line + "Score;ScoreDiff;Point;Placering");


// Group team/detail line
line = "";
string scoresum = "";
h = 0;
for (j = start + 4 + i * teamsPerGroup; j < start + 4 + i * teamsPerGroup + teamsPerGroup; j++)
{
 output(args[j]
  + ";" + lineInGroup(h, teamsPerGroup)
  + calculateScore(j - (start + 4 + i * teamsPerGroup) ,teamsPerGroup)
  );
 // Find first cell for cell range with ScoreDiff.
 if (scoresum.Equals("")) scoresum = col(teamsPerGroup+1) + outputLineNumber.ToString();
 h++;
}
// Sum all ScoreDiffs. If this value != 0 then we have entered a wrong score!
output(
 "".PadRight(teamsPerGroup + 6,';')
 + "=SUM(" + scoresum + ":" + col(teamsPerGroup+1) + outputLineNumber.ToString() + ")"
);
   }


   // Handle next level/age group
   if ((start + 4 + groups * teamsPerGroup) < args.Length)
   {
output(";");
outputCSV(start + 4 + groups * teamsPerGroup, args);
   }
  }


  // Return start scores, 0-0, for a team in a group.
  string lineInGroup(int h, int teamsPerGroup)
  {


   string s = "";
   for (int i = 0; i < teamsPerGroup; i++)
   {
if (i == h)
 s = s + "X;";
else
 s = s + "0-0;";
   }
   return s;
  }


  // Return start scores, 0-0, for a team in a group.
  string OriglineInGroup(int h, int teamsPerGroup)
  {
   string s = "";
   for (int i = 0; i < teamsPerGroup; i++)
   {
if (i == h)
 s = s + "X;";
else
 s = s + "0-0;";
   }
   return s;
  }


  // Return spread sheet functions that calculate total score, score diff. and match points for a team in a group.
  string calculateScore(int teamNumberInGroup, int teamsPerGroup)
  {
   string home = "";
   string away = "";
   for (int i = 0; i < teamsPerGroup; i++)
   {
if (i != teamNumberInGroup)
 home = home
  + (home.Length > 0 ? "+" : "")
  + "VALUE(LEFT(" + col(i)
  + "afterburnlinenumber;FIND(CHAR(45);" + col(i)
  + "afterburnlinenumber)-1))";
   }


   for (int i = 0; i < teamsPerGroup; i++)
   {
if (i != teamNumberInGroup)
 away = away
  + (away.Length > 0 ? "+" : "")
  + "VALUE(mid(" + col(i)
  + "afterburnlinenumber;FIND(CHAR(45);"
  + col(i)
  + "afterburnlinenumber)+1;10))";
   }


   string matchPoints = "";
   string homeScore = "";
   string awayScore = "";
   for (int i = 0; i < teamsPerGroup; i++)
   {
if (i != teamNumberInGroup)
{
 homeScore = "VALUE(LEFT(" + col(i)
  + "afterburnlinenumber;FIND(CHAR(45);" + col(i)
  + "afterburnlinenumber)-1))";
 awayScore = "VALUE(mid(" + col(i)
  + "afterburnlinenumber;FIND(CHAR(45);"
  + col(i)
  + "afterburnlinenumber)+1;10))";
 // If statement implemeted below:
 // if homescore > awayscore then 2   home wins
 // else if homescore < awayscore then 0  home looses
 // else if homescore = 0 then 0 game not played
 // else 1  tie game
 matchPoints = matchPoints
  + "\"="
  + "IF("
  + homeScore
  + ">"
  + awayScore
  + ";2;"
  + "IF("
  + homeScore
  + "<"
  + awayScore
  + ";0;"
  + "IF("
  + homeScore
  + "="
  + "0"
  + ";0;1)))"
  + "\";";
}
   }



   string totalScore = "\"=TEXT(" + home + ";0)&CHAR(45)&TEXT(" + away + ";0)\"";
   string s = col(teamsPerGroup) + "afterburnlinenumber";
   string scoreDiff = "\"=VALUE(LEFT(" + s + ";FIND(CHAR(45);" + s + ")-1))"
+ " - VALUE(MID(" + s + ";FIND(CHAR(45);" + s + ")+1;10))\"";


   //   return totalScore + ";" + scoreDiff;
   return totalScore + ";"
+ scoreDiff
+ ";=SUM(" + col(teamsPerGroup + 5) + "afterburnlinenumber:" + col(2 * teamsPerGroup + 5 - 2) + "afterburnlinenumber)"
+ ";;;"
+ matchPoints;
  }


  // Return spread sheet functions that calculate total score and score diff. for a team in a group.
  string OrigcalculateScore(int teamNumberInGroup, int teamsPerGroup)
  {
   string home = "";
   string away = "";
   for (int i = 0; i < teamsPerGroup; i++)
   {
if (i != teamNumberInGroup)
 home = home
  + (home.Length > 0 ? "+" : "")
  + "VALUE(LEFT(" + col(i)
  + "afterburnlinenumber;FIND(CHAR(45);" + col(i)
  + "afterburnlinenumber)-1))";
   }


   for (int i = 0; i < teamsPerGroup; i++)
   {
if (i != teamNumberInGroup)
 away = away
  + (away.Length > 0 ? "+" : "")
  + "VALUE(mid(" + col(i)
  + "afterburnlinenumber;FIND(CHAR(45);"
  + col(i)
  + "afterburnlinenumber)+1;10))";
   }


   string totalScore = "\"=TEXT(" + home + ";0)&CHAR(45)&TEXT(" + away + ";0)\"";
   string s = col(teamsPerGroup) + "afterburnlinenumber";
   string scoreDiff = "\"=VALUE(LEFT(" + s + ";FIND(CHAR(45);" + s + ")-1))"
+ " - VALUE(MID(" + s + ";FIND(CHAR(45);" + s + ")+1;10))\"";


   //   return totalScore + ";" + scoreDiff;
   return totalScore + ";" + scoreDiff + ";";
  }


  // Return a spread sheet column name
  string col(int i)
  {
   return Convert.ToChar(65 + 1 + i).ToString();
  }


  // Output a line. Insert spread sheet line number.
  void output(string s)
  {
   outputLineNumber++;
   s = s.Replace("afterburnlinenumber", outputLineNumber.ToString());
   Console.WriteLine(s);
  }
 }
}


Once the program above has been run with an appropriate set of parameters describing the tournament and you have piped the output to a csv-file you just open the csv-file in your spread sheet program. Before entering results, by overwriting the "0-0" cells with game results, you have to change the format of these cells. Select a format of text, otherwise the function in column F will not calculate correctly.


UltraEdit Macro

Once you have entered scores in the spread sheet it is time to upload results from the tournament to the internet.


Follow this process:

  1. Export the spread sheet to a txt file.
  2. Open the text file in UltraEdit
  3. Run UltraEdit macro that transform the text file to html

Below you see the UltraEdit Macro. Basically it is a number of search and replace commands and insertion of a limited number of html tags:


InsertMode
ColumnModeOff
HexOff
Find "^p"
Replace All "&nbsp;</td></tr>^p<tr><td>"
Find "^t"
Replace All " </td><td>"
"<table border="0"><tr><td>"
Bottom
Key END
Key BACKSPACE
Key BACKSPACE
Key BACKSPACE
Key BACKSPACE
Key BACKSPACE
Key BACKSPACE
Key BACKSPACE
Key BACKSPACE
"</table>"
Top 


Create an Ultraedit macro so you will be able to reuse the macro next time you have to transform a spreadsheet to html.

Csv-file in Spreadsheet program

Once you open the csv-file in your spreadsheet program, you will notice columns for check sums. See below:

In this case the values in columns F and G are calculated and should be part of what you publish. Columns K, L and M are also calculated and represent check sums. You are likely to have entered inconsistant results if the check sums are not correct! You enter actual game results in columns B to E.

Good luck!

--------------------------------------------------------------------------------
If you have comments, corrections or ideas then please contact tps@netmaster.dk