Convert Daily Mile Raw Stats into Miles per Week and Month

In addition to my Garmin (510), which I use as my main cycling computer, I run the Cyclemeter app on my phone. It works very well, and I can keep the phone safely padded and put up while viewing the more rugged Garmin.

When I first started using Cyclemeter it offered by default to upload my stats to the Daily Mile web site. The site is useful and has a nice display for showing your miles in terms of days, weeks and months.

It also has a major limitation if you’re not using the premium version of the site. I’m not complaining, I understand, but the service isn’t quite useful enough to justify payment from me.

That limitation is that only the past 30 days, 26 weeks or 13 months worth of data is made visible.

The good news is that the site offers a download of your entire ride history in a CSV file.

Following is how I convert this file into a format that is useful to me.

First, the results. The first picture is my Miles per Month view and the second is Miles per Week.

(You can click either image to see a larger version.)

Converting a Daily Mile CVS download to a spreadsheet, with graph, that displays numbers by month or by week.

Start with the CVS file from Daily Mile. It has the following fields:

date activity_type distance time elevation_gain

First, delete all but the date and distance columns.

Then convert meters into miles using the following formula (change C1 to the appropriate column if necessary) in a the top row of a column to the right of the distance column:

=CONVERT(C1, "m", "mi")


Then click the 2nd row of that column, and drag the fill handle down the ranges that you wish to convert.

Now, select that entire column, (which should now be displaying the miles ridden per day instead of meters per day) using Ctl-Shift-8, and press Ctl-c to make a copy.

In a different column, (Pay attention here! 🙂 do a right-mouse click and select “paste as value.” This will make a column showing the miles that is not dependent on the meters column.

You may now delete the distance column and the first column showing miles.

Rename the column showing miles as “Miles.”

Make sure your spreadsheet looks like the following:

* two columns, the first with date info and the 2nd with Miles.
* there can be no blank columns between these two

Click the first row of the date column. Ctl-click the first row of the Miles column.

Press Shift-Ctl-8 to select the contents of both columns.

Now, click insert and click Pivot Table.

The date range should be set automatically based on your previous selection

Choose “new worksheet” and press OK.

In the new sheet select date and Miles for Pivot Table Fields.

Then, right-click any entry in the date column and select Group.

For a monthly view, select years and months

For a Weekly view, specify days, and the number of days as 7.

To add a graph, simply insert and select Pivot Graph.

Play with the colors,label the axis, resize and voilà!

The following 3 sites were very helpful to this Excel Novice:

Conversions

Pivot Tables 1

Pivot Tables 2