Home > Uncategorized > Adventures in Self-Tracking & my quest to export data from MyFitnessPal

Adventures in Self-Tracking & my quest to export data from MyFitnessPal

Recently, I adopted a series of new habits, which I have since learned are part of the culture known as self-tracking. My motivation was based on the hypothesis that my haphazard sleep, exercise, and nutrition habits were contributing to a decline in my daily productivity. As the co-founder of an early-stage startup company, a drop in my productivity at this point would be as disastrous as a sprained ankle for someone who is training for a marathon. Bad news. So, I heeded some timeless advice passed on from a wise local entrepreneur: What Gets Measured Gets Done (WGMGD).

I have done a decent job of tracking quantitative data about other parts of my life. Years before Mint.com made the process utterly simple for me, I kept a fairly detailed log of my personal financial habits, setting savings goals and entering every receipt I received into a spreadsheet. My fuel purchase data has been tracked since 2005, when I began doing a lot of driving between home and college (and began working with some other vehicle efficiency gurus, the UK Solar Car Team). It's all shared via Fueleconomy.gov. It feels good to know that my 1993 Ford Ranger 2WD 3.0L V6 is averaging almost 22mpg over the 46,000 miles I have driven. I have even saved and graphed the data from all those pieces of paper from the Kentucky Blood Center showing my vital stats (blood pressure, heart rate, temperature). But none of these data feeds seemed to correlate well with my personal productivity trends.

I started my process by searching for a few relevant food diary and sleep monitoring apps. As soon as I discovered MyFitnessPal, I knew I'd want a way to get my data off of the site and into an easily consumable format. Unfortunately, a few forum posts indicated that MFP didn't natively have this capability. So, like any good little engineer who encounters a problem, I waded around in the muck until I really understood where I was and where I wanted to be, then began to patch together a solution. I haven't spent much time further automating this process, but at this point, it’s not too much of a hassle, and the daily “touch” on the spreadsheet isn’t a bad thing for forcing me to review the feedback.

I’ve learned a few things since I started digging (like how to hack HTML forms by changing POST requests to GET, thanks cURL guys & Chris Pederick), and found a solution that woks nicely with the Spreadsheets app in Google Docs. Here is the process I'm currently using:

  1. Make your MFP diary page public, and click on the link to your "printable diary"
  2. Change the form POSTs to GETs (that Chrome/Firefox Web Developer extension comes in handy here) to determine the proper URL query structure
  3. Use ImportXML to scrape the data from this page.
  1. My table has a date column, from which I parse out the day, month, and year and feed it into URL using the concatenate() function
  2. The importXML() function has two arguments: URL and Query. Encase each argument in quotes. Format your Query based on the XPath Syntax.
  3. Note that I'm only pulling the data for my daily totals, but you could grab the whole shebang if you so desire (just a little more regex work)
  • Use find() and left() to remove the “g” and “mg” units
    1. =left(M18,(FIND("g",M18)-1))
  • Use value() to convert text string into numbers
  • Resulting in this nasty formula (for my "carbs" data):
  • =value(left(ImportXML(concatenate("http://www.myfitnesspal.com/reports/printable_diary/",$L$2,"?username=",$L$2,"&from%5Bmonth%5D=",month($A25),"&from%5Bday%5D=",day($A25),"&from%5Byear%5D=",year($A25),"&show_food_diary=1&to%5Bmonth%5D=",month($A25),"&to%5Bday%5D=",day($A25),"&to%5Byear%5D=",year($A25)),"//table[@id='food']/tfoot/tr/td[3]"),(FIND("g",ImportXML(concatenate("http://www.myfitnesspal.com/reports/printable_diary/",$L$2,"?username=",$L$2,"&from%5Bmonth%5D=",month($A25),"&from%5Bday%5D=",day($A25),"&from%5Byear%5D=",year($A25),"&show_food_diary=1&to%5Bmonth%5D=",month($A25),"&to%5Bday%5D=",day($A25),"&to%5Byear%5D=",year($A25)),"//table[@id='food']/tfoot/tr/td[3]"))-1)))
  • In order to make this process much simpler for general users (not just you spreadsheet programming gurus), there are a few additional steps needed:

    1. Simplify input for “username” (maybe use a Google Form and I'll do it by hand until demand is significant?)
    2. Find a way to automate this daily (write a script). Right now, I duplicate down (Ctrl+D) my final row and change the date to today, then copy and "paste values" for yesterday's row. This is necessary because Google Spreadsheets places a 50-instance limit on the importXML() function per sheet.
    3. Automated graph creation (a prettier output page would be nice)

    For any of you struggling to find a way to more easily pull your data out of MyFitnessPal while waiting for an API, I hope this helps! To make your own spreadsheet, check out my sheet and select "File –> "Make a copy". Then change the "username" cell value to your username, and voila!

    Categories: Uncategorized
    1. 2011/11/30 at 1:36 am

      Great post. Without realizing it I’ve been a self-tracker for quite some time. Now that I know about the Quantified Self and the Self Tracking movement I’m getting more and more excited about the stuff I can do. Thanks for sharing your hack for MyFitnessPal. Also, it’s nice to meet someone with as extensive of a financial record as me – I’ve been scanning my receipts for 10 years 🙂

    2. Carrie
      2013/01/14 at 9:34 am

      Thanks very much for posting this. It sounds like just what I was after.

    1. 2012/05/24 at 1:55 am
    2. 2013/07/06 at 11:12 pm

    Leave a Reply

    Fill in your details below or click an icon to log in:

    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

    %d bloggers like this: