I’m trying to automate the process of getting scores on my Dart League web page. To that end I’ve reworked the typical dart league standing Spreadsheet (called a model for the rest of the the article) for easier integration with a website.
Here’s the sheet: Dart League Model.
The idea is that the League secretary or statistician will update this model to generate the periodic league standings sheet. The added bonus is that this model has a sheet (see why I wanted to call it a “model”) that serializes the results data for easily and automatically updating the mod_python/postgresql or php/mysql or whatever you prefer. In the long haul I’ll use the following toolset:
- php or mod_python to handle the file upload form
- mod_python or php to handshake in the uploaded file
- xls2csv to convert from Microsoft Excel format to comma separated value format
- mod_python to insert the data into a temporary table in mysql or postgresql
- mysql or postgresql to integrate the results into the website.
Alternatively I may use procmail and python to handshake in the data from an email attachment. This replaces steps 1 and 2 above. The result will be either a webform where you upload your statistics or an email address for a bot which automatically handshakes in your stats. If I go email I may have to wrap everything with GnuPG to sign the data so I can know what to ignore.
Just for the purposes of naming I’m going to refer to a “spreadsheet” as a “model” from here on in. This is because Excel allows you to have multiple “sheets” within a model and you can link the data between the sheets. The model that I’m presenting makes pretty heavy use of this feature. The following describes how it works and I’m gonna give you estimates of how long it took me to set things up. I consider myself pretty good at Excel so double any times that I quote for Novice Excel users. If someone here is good at Excel Macros I can see spending some time to embed some of my knowledge into the model in the form of macros.
To use this model you start by laying out the sheet called “Tables”. This sheet has entries for your teams and the dates that you play on. These table contain your master data for your league. Since they get referenced in the rest of the sheet this is where you would go if you want to use the correct a misspelled team name. This took me about a hour to setup and needs to get done once at the start of the season. After that you shouldn’t ever have to touch it.
The next thing to do is to setup the sheet called “Enter Data Here”. This is where your week-to-week data entry goes. There’s an row for each team in your league and a column for each week that you play. That defines a box for each score. So if the Beachcomber Cafe team scores 11 points in Week 1 (June 26th) you put an 11 in the appropriate box. There’s a separate area for penalty points should you get a late score sheet or a late payment for a player. This took me about another hour to setup. You would have to set this up once also during the season. Adding this weeks scores took less than 15 minutes. Adding scores has to get done each week but I expect that this work will become common so it should take the same effort as me, week to week, as anyone else.
There is a sheet called “Results for Display”. This is the sheet that you want to print out or convert to PDF and distribute to your members. It’s exactly like the sheet that you are currently using for this with the exception you do not enter the data here. This sheet references the data on the previous sheet: “Enter Data Here”. The only thing would want to do to this sheet is fix the formating to suite your own league. I did address a pet peeve of mine in my rendition of this sheet. I show the team, then their total score, followed by their average. And the teams sort into places, first, second, third, by average. I’ve talked to a bunch of people about this while they agree with my math they say that sorting the scores by average rather than raw points is beyond the math skills of the common dart player. You don’t have to do it this way.
This sheet took me a little less than a hour to setup for MSDL. It requires weekly maintenance to sort the teams into order. The sorting columns are hidden so the maintainer has to know how to show hidden columns, sort from Z-to-A, and then rehide the columns. All told this takes about a minute for me to do.
There is a sheet called “Results for Display”. This is the sheet that you want to print out or convert to PDF and distribute to your members. It’s exactly like the sheet that you are currently using for this with the exception you do not enter the data here. This sheet references the data on the previous sheet: “Enter Data Here”. The only thing would want to do to this sheet is fix the formating to suite your own league. I did address a pet peeve of mine in my rendition of this sheet. I show the team, then their total score, followed by their average. And the teams sort into places, first, second, third, by average. I’ve talked to a bunch of people about this while they agree with my math they say that sorting the scores by average rather than raw points is beyond the math skills of the common dart player. You don’t have to do it this way.
The final sheet is called export_for_website. It took me about a half hour to set that up but for someone else the effort is all done. This sheet formats the data into one big long table suitable for direct import into a database. My plan is to import this data using a combination of tools which will take the sheet, convert it into a data format (CSV or comma separated value), load it into a database table and then then have some database code (a stored procedure) which updates the website tables. The trigger to load the database could be a special email recipient or I could put together a web form with a box for uploading the spreadsheet. Either method should be pretty easy to work for a novice user. If you don’t understand any of that I will be glad to explain it to you. If you do understand that but don’t get the next piece that’s completely okay. For advanced users I could set things up so that you directly import the data if someone is using the full Microsoft office Suite including Access and ODBC. Similar functionality is available to people who use OpenOffice. *Plug* I’m using open office to create all this and the package is Free!