Archive for January, 2012

Training Log and Bicycle Component Log

January 11, 2012 10:09 pm

Since 1988, I have used Excel to keep track of my training.  Initially it was just running and I would track how many miles I ran each date and the pace.  Later when I got into biking, the log expanded to track cycling, then later track the mileage on my bicycles and the various componets.  Over the years this Excel file became increasingly complicated.

Even after writing an article about online training logs, I still keep up my Excel spreadsheet.  For over a year I upload my GPS data to a couple of websites (Strava and Garmin Connect) and to a local computer based program (Ascent). One might think that a website like should be more than enough, and might be for most people, for me personally I want to do more, including:

  • Chart cycling and running miles separately
  • Track how I am doing compared with prior years at this same time
  • Track for both my wife and I together since mostly bike and run together
  • Track details on mileage on various bike components on various bikes
  • Track which club or non-club miles I cycle.

This manual entry was becoming a chore after all these years so I sought to automate the process.  I looked at the above and decided to scratch the last entry since no online training log let’s you record that.  I still wanted to track how I was doing against prior years, using my old training log data, so none of the online training logs are by themselves are sufficient.  I finally came up the approach to modify my current Excel file, which has sheets for all prior years, a sheet for my bike component log, and a lot of other training specific data.  But instead of adding a new sheet for 2012 with places for every single workout during the year, I added a sheet for 2012 with weekly totals.  A small section looks like this.

If you look across the actual Excel file you will see similar blocks for my running, as well as Anne’s running and biking.  I decided it was no longer necessary to record every workout in this Excel file, just the weekly totals.  That allows me to plot how I am doing compared with prior years.  Initially I was going to manually enter this data but found a much easier way.  Since I upload my GPS devices to Garmin Connect, I use the great report feature in that program to export to CVS files.

I just select the report type “Group by Week” for “This Year”.  After doing a one time customization of the report to get the columns I wanted,   I then select Cycling and export the CVS file.  I repeat for running.  Then repeat for Anne’s Garmin Connect information for both running and cycling.  This creates four CVS files.  In my main Excel file I use links to these external CVS files to fill in all the data.  It is not something I need to do very often, just when I want to see how I am doing.  Even if I do monthly, I will still get the data in a weekly total format.

Bike Component Wear

The only missing item is the mileage on each bicycle.  For that I end up doing some manual entry, here taking the data from which allows you to track your rides by bicycle.  I just enter the current mileage on each bicycle and use that in my existing formulas on component wear.  Hopefully if Gramin Connect ever adds this feature I can automate that also.  I might just switch over and add all the components in and only use that for component tracking.

In Excel I track all components on all bikes.  This example is for one of the bikes.

How is My Training Going

Using this new approach, I can still easily see how my running and cycling are doing compared with prior years. This shows how I did in 2011 compared with the prior years.

Now for 2012,  I will still have similar graphs, but using the data that is auto filled in from the Garmin Connect CVS files.  I have friends who don’t even use a cyclometer to cycle with and have no idea how many miles they have ridden.  But I also notice these same guys are always having some issue with cassette wear because they didn’t change their chain until it was too late.  It might be the engineer in me that loves to see the data so I know how I am doing and how my bikes are doing.  Now I have an effortless way to get that data in a format I can use for my own purposes and not rely on the limitations of the online training logs.  For most people this is more than they will want and are happy to just use what one of the online training logs offer, but if you want to get things in a format you can manipulate for your own purposes, this might give you an idea.