building my tools: step one, gathering data

Note: I spent about a month building food and exercise tracking tools. I could have used prebuilt tools available online (Calorie Count comes to mind), so why do this myself? I love spreadsheets, organizing information and owning the process. I'm writing this brief series to detail how I built these tools for anyone interested in doing something similar. My resources include the USDA's food datasets, dietary guidelines, the University of South Carolina's Compendium of Physical Activities, Excel, Google Docs spreadsheets and a heart rate monitor.

step one: data collection
Food datasets. I wanted to use high-quality, scientific datasets. The USDA seemed a clear choice for nutritional information. They provide downloadable, comprehensive files for use by Excel (my choice) or database software (which I may actually look into for a future project, but that's a whole other series of blog entries).

Here's a snippet ...


A little overwhelming, no? You'll see later that I filter down the content to make it more useful and useable (I also edit the heights on those rows to build in a little white space and improve readability). If you download this Excel file, you'll find it comes with a documentation PDF. I suggest using it to understand the abbreviations and organization the USDA applies to the dataset.

Food guidelines. We all know about this ... five food groups (updated to the food pyramid a few years ago). But did you know that the USDA publishes a ridiculously thorough handbook for those guidelines? I'm using the tables found in Appendix A: Eating Patterns (numbered pages 51 and 52 of the handbook; if you use Acrobat to navigate, go to pages 62 and 63).

Another snippet ...


Ultimately, this data feeds my calculations for, say, how many servings of vegetables are in 1 oz. of spinach. Crazy, right? I'll go over that in my equations entry.

MET(s). Also known as metabolic equivalent, or a unit of measure used to help calculate calories burned doing various exercises. The University of South Carolina publishes a Compendium of Physical Activity with MET data for dozens of exercises (including household chores, gardening, etc.).

Snippet ...


This is probably the most unfriendly dataset I worked with. I could only find it in PDF format, and I had to copy and paste the content into a Google spreadsheet. In so doing, though, I chose to only include activities that are relevant to my routine.

I'm also using a heart rate monitor to collect data for additional exercise calculations, but I'll go over that when I discuss my equations.

step one, complete
And on that note, I'm done with data collection. Step two will cover how I filtered this huge mass of data; step three, the equations I use to make the data meaningful; and step four, how I integrated everything into a daily food/exercise tracking tool.