Building a Nutrition Macro Calculator Spreadsheet
2022 August 11

I really want to lose some weight this month, but nutrition is hard, and it can’t run on instinct. So I made a spreadsheet to calculate calories and macros. I’m not a dietician or a nutritionist so my diet numbers should definitely not be trusted. I just followed examples and combined them to build my diet. What can be trusted is how I loaded the information from official government sources, and how I did my research.

The main reason I built it was because I found no free online macro diet planners that didn’t require account sign up. I found things that told me my macros, but nothing that suggested a diet to me or let me start trying to goal seek towards the suggested macros. I used a generic online macro calculator set to low carb. The next step was to locate a database of macro and nutrition information. Luckily the USDA publishes such a database, and someone kindly went to the trouble of converting it to sqlite.

I would much prefer to work with SQLite, because I can join tables, and perform calculations in a much easier fashion than in a bunch of spreadsheets, and joining things manually. I used the following query to compute the information to import into Google Sheets.

SELECT
	food.id,
	food.long_desc  || ', serving=' || weight.description as food_item,
	weight.gm_weight as weight,
	(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 203) * weight.gm_weight / 100 as protein100g,
	(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 204) * weight.gm_weight / 100 as fat100g,
	(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 205) * weight.gm_weight / 100 as carbs100g,
	(SELECT amount FROM nutrition WHERE nutrition.food_id = food.id AND nutrition.nutrient_id = 208) * weight.gm_weight / 100 as calories100g
FROM food
INNER JOIN weight ON weight.food_id = food.id

The database is organized in a relative normalized form. I wanted it denormalized to include macros and calories per serving size. I find that using serving size makes it easier for my monkey brain to get a grip on the quantity. After all, how much really is 100 grams of eggs? It’s not a human like quantity. I want to know the values for 3 eggs, and they are unlikely to be perfect. I used correlated subqueries, since that was easier than joining or pivoting any special way. The nutrient_id fields come from the nutrient table which has specific nutrients for every food item in 100 gram quantities.

This information was exported as a CSV and imported into a single sheet. I then used =INDEX(MATCH(...)) statements to look up foods from this table. I also had it extend quantities out and perform some autocompletion via the data validation features of Google Sheets. The autocomplete made it really easy to locate the macros and quickly create a balanced meal plan.

The final spreadsheet for a given day

If there’s any interest I can share my actual spreadsheet. Otherwise I think it’s a good exercise to go and build it yourself. The data munging is fairly interesting as is digging through the USDA’s data dictionary on this database. Future work could include automatic goal seek to balance out the macros or porting to use real excel for better performance. Google Sheets leaves something to be desired with autocomplete speed.

*****
Written by Henry J Schmale on 2022 August 11
Hit Counter

Remember you can also subscribe using RSS at the top of the page!

Share this on → Twitter LinkedIn Reddit