Monday 22 October 2012

Parkrun - Community 5k weekly running. #analysis


In 2012 i started running. This was inspired by a brief chat with one of my best friends and a notice in the local paper about a free weekly 5km timed run each Saturday at 9am. 

Now before 2012, 9am on a Saturday was rarely a time i would consider waking up by let alone think about running 5km or 3.2 miles. Way back in March i got up extra early, jumped in the car with my best mate Simon and headed off to the local common. Neither of us thought we would make it round the course with out walking but on that first attempt i managed 26mins 46 Seconds and finished 76th, i was hooked!

I used to enjoy running as a child being in the schools athletic club and taking part in cross country, but that's where it stopped and university was more a training ground in drinking than keeping fit, suffice to say in the last 15 ish years i had not looked after myself. 

You can find out more about Parkrun by visiting the website, they take place all over the UK and throughout the world. They are not meant to be a race and are marketed as a timed run. However you do get a position (I'm a little competitive), points and there is a results table. With my initial run done and finishing in 76th and a good 10mins behind the quickest runners i knew i had work to do. 

Since my initial run i have sliced 6mins 40secs off my time and now regularly finish within the top 25 runners to do this i have had to train (something i did not initially think i would need to do) and run about 6 - 12 miles extra each week. I have also started to enter official races to see how i compete against club runners with my next race being the Great South Run, so all in all parkrun has been a great experience for me. 

Once i had the running under control and was making progress i thought that i needed to keep an eye on my stats. Now Parkrun provide some data on their site, so you get a graph of your age grade results which looks a little like this:

With almost everything else in table format, on the website. This got me thinking about Google Docs or Excel and the fact you can import HTML into spreadsheets!

So without further ado i will run you through my Google Docs Spreadsheet.

First things first, my spreadsheet has a config page this is basically to make it easy for other parkrun runners  to make a copy and plugin in some data and everything else will work. Secondly i am by no means a spreadsheet guru, i am very happy with how far i have taken my document and may add more in future. If you do use it, make improvements or spot mistakes let me know in the comments (also spread the word to other parkrun fans!!). 

Link to the Google Doc parkrun Spreadsheet

So why would you want to use a spreadsheet? 

Perhaps you want a fancy graph of your age grade stats:
Above you can see where i hurt my back and took 4 weeks to get back to PB form and how i took it easy on the first run.

You might want to know how you are doing in the points table: 

Anyone looking at the graph and wondering how someone can have more points but be lower down the table should read the parkrun FAQ (i know i had to). The reason is like this graph is that it shows if its purely running or a mix of volunteering and running that gets you to the top! 

While I'm talking about volunteering here is a graph showing the percentage of volunteers for your sex: 

Interestingly my initial thoughts were that about 10% of people would volunteer at least once and the above chart sort of backs that up (all be just for males at Newbury). I think i need to improve the calculation to only include people that have run more than once but that will have to wait for now. 

Last but by no means least how about the PB count for the latest results: 

Looks like a pretty good week!

Obviously you should check the calculations to make sure you agree with my formulas but i think it works pretty well and once you get started i am sure there is more analysis you can do. 

To get started use the link to the spreadsheet above and save a copy, on the config worksheet add in the right values for your run and the rest should update for you.

A couple of caveats now: 

  1.  If parkrun change the format of the website the spreadsheet will stop working!
  2. Some of the tables need to be manually updated each week to account for changes (i think excel would work better for this using a data table as that would update). 
    1. volunteered on the points sheet 
    2. PB on the weekly results. 
  3. I like to make a copy of the weekly results into a separate worksheet each week to preserve the data in case i want to check something

If you want to have a go your self here is a quick example of the formulas i used in Google Docs: 


What does it mean? Basically grab the content from the page listed passing in the Athlete Number from the Config worksheet, looking for the HTML element of type table and select table number 4.