Iron Arena Spreadsheet

For a while now (at least 12 months) one of my pet side projects has been exploring Google Docs for capturing Warmachine and Hordes league data. I started last year with Machinations, and with each league the template grew and expanded and became more refined as I learned more about the capabilities of the platform. I also started making my sheets available to other league administrators, and have taken their feedback into later iterations.

I really like Google Docs, as it allows for quite rapid development; sure I could likely use more traditional technology stacks (.Net, SQL, etc) but there is an infrastructure overhead that I just don’t need for these projects. Recently, I explored the use of public/hidden sheets and with letting players self-record their sign-up, league matches and painting efforts (I trust my players not to cheat the system). Even more recently, I explored the Forms aspect of Google Docs and I love it! It is a great way to have better control over the data quality without risking that users will overwrite a honed formula with their ignorance.

At the start of this year I helped run the Iron Arena at Cancon. For the unaware, the Iron Arena is a casual convention format for Warmachine & Hordes; players are free to organise their own games, play at their own pace and earn points which they can trade in for prizes such as posters, dice, widgets, models, etc. It is a really good format and had a lot of interest this year at Cancon. Recording information for the Iron Arena at Cancon was a paper-based process (due to only having around 50 participants) and had all the associated hook ups (human error, filing, time delays, etc).

Being a software developer by trade, I knew there had to be a better way.

Sheets

Main Sheet

Main Spreadsheet

The main spreadsheet is where all the magic is consolidated; it presents a total view of each player, their games, points, achievements and purchases. Behind the scenes are a number of key sheets that summarise/filter and transform the raw data into a format suitable for quick, (fairly) easy to understand formulas.

Player Sheet

Player Sheet

This sheet is a cut-down version of the Main Sheet, focusing on an individual player (selectable from the drop-down). This allows for auditing of player information (as a result of enquiries or issues) and acts as a sanity check for the main sheet. It contains the same information as the Main Sheet, along with raw data entries for double checking.

Forms

The forms are what drive the data entry. If needs be the raw data can be edited or created within the spreadsheet itself, but that is not ideal. At the moment, there are 3 forms that collect the necessary data.

Game Form

Game Form The game form (obviously) collects the game data. It uses fields and drop downs to help with data quality. I will likely expand this form to allow support for multi-player games. It contains fields for the game size and player faction, state, etc. As a side-effect of using Google Forms, I also get a timestamp of when the game was recorded.

Tournament Form

Tournament Form People who played in a tournament also got some bonus Iron Arena points.

Purchase Form

Purchase Form This form tracks purchases made by players (DERR!). The costs are stored in a reference sheet and so need not be manually tracked.

Charts

A cute side-effect of having all this data is providing visualisations. I hope to add more as I tinker.

Faction ChartState Chart

 

Final Comments

It is not a perfect system. There is next to no validation (so garbage in, garbage out) as as of right now Google Forms does not permit pulling drop-down contents and validation from spreadsheets; and correcting errors (wrong name, wrong faction, etc) is a case of editing the raw data (and thus subject to human error). And some parts are cludgy; The Tournament Form does not show what you’ve already entered it just lets you record a new data record; processing out the unique set has to be done with spreadsheet formula.

However we must appreciate this (from conception to it’s current state) has taken approximately 6 hours. If you can write a full database-driven, web-based data collection and exploration tool in under 6 hours – please send me your email address. Sure, a lot of this form has stood on the back of the hours I’ve spent refining my league sheets, but is the same for learning any new technology.

If you want to see it in action; knock yourself out. You should be unable to edit the sheet, but I can always open it up if you want to see how I did something or offer improvement suggestions.

Iron Arena Spreadsheet

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s