Download Fitness Tracker

I have read and accept the license terms.

Please accept the terms.

Fitness Tracker

Version 0.9 beta

for Microsoft® Excel®

Fitness Tracker is Program that aims to help you maintaining or improving your physical fitness.

You can use it to keep track of your various physical exercises and to monitor your nutrition and eating habits. While there are plenty of free and commercial applications out there that help you with either of those tasks, Fitness Tracker distinguishes itself with the capability to put exercises and nutrition in relation, allowing you to track your daily overall energy balance. Aiming for balance, it promotes a healthy way to stay fit and it builds awareness for the effects of different types of food and exercise.

Fitness Tracker is basically a Microsoft® Excel® document ("workbook") with some intelligence added using the built-in Visual Basic® for Applications (VBA) in Excel. As such, it practically works like a full program but it requires Microsoft Excel to run. Since Microsoft offers reasonably priced home and student versions of its office suite it has become a fairly wide-spread software on home PCs in addition to many office computers.

Description and Main Features

Awareness is a key to a healthy lifestyle. Unrealistic exercise plans or diets that ban all your favorite foods and replace them with bland plastic-wrapped dietary products decorated with some green leaves won't make you feel good, let alone some pharmaceutical products that may do more harm than good. Build awareness using the following features of the program, and you will happily treat yourself to a nice meal knowing that you just need a bit of exercise to balance it out:

  • track your meals and snacks
  • track physical exercises and how you perform on them
  • determine your overall energy needs per day based on age, height, gender, and exercise
  • put the amount of energy taken in and energy burnt in relation, and monitor the trend
  • keep track of your weight or body mass index (BMI) alongside with your energy balance
  • learn how different foods and activities affect your energy balance

Note that the energy balance is essential but only one aspect of a healthy lifestyle. Of course, you still need variation in your nutrition, and you need to adjust exercising to your physical capabilities and health condition. Do not, in an attempt to quickly lose weight, aim for a significant negative energy balance every day because it is neither healthy nor sustainable. Know your limits! Make small, gradual changes if needed. Fitness Tracker helps you with that.

System Requirements

Fitness Tracker requires a PC running Microsoft Excel 2010 or 2013 and Microsoft Windows®.

Known Issues

This is a beta release of Fitness Tracker. It means that this version is believed to be fully functional, but first time available to a large number of users. Any feedback (comments, suggestions, bug reports, etc.) are welcome. All known bugs were corrected before the release. I will add any issues here as soon as I become aware of them.

Fitness Tracker was developed using Excel 2010 and performs well on that version. Using the application in Excel 2013 you may encounter minor performance issues such as delays and screen flicker when you navigate among the worksheets/pages. This is a side-effect of increased security in Excel 2013 using improved encryption algorithms and counter-acting brute-force methods to break worksheet protection. The functionality of the application is not affected. I might release an optimized version in the future.

User Guide

Getting Started

Starting the Application for the First Time

When you run Fitness Tracker for the first time from a specific location, Excel will display a security warning similar to the one shown in the picture. You need to enable macros in order for the application to function. You will not be asked again unless you decide to rename the file or move it to a different folder. This warning is a built-in security feature in Excel that is there to protect you. It can be disabled but I do not recommend that.

Next, the application asks you to confirm the license terms. The application is basically free to use but there are a few things you should know. Please read the license terms and confirm that you agree. If you do not agree, the application will close. The license terms will be shown again when you run the application on another computer or from another Windows logon. This is by design to ensure new users have read the terms. I recommend using the same computer at least most of the time.

Entering Your Personal Data

For some of the functions such as the calculation of the calorie consumption the application needs to know a few things about you. Enter your information on the main tab. There you can also choose your preferred measurement units and choose a language. Because you decide where the workbook is stored (e.g. your PC) you maintain full control over your data.

Name: Enter your name here. You can freely choose what you enter. Only purpose is to personalize the document.

Date of Birth: This information is required because your age is used for calculating your daily calorie consumption. The date format depends on the country-specific settings for the in Windows. If Excel does not recognize the date it will display a warning.

Gender: Your calorie consumption also depends on your gender, so this is required.

Height: Height also affects the calorie consumption. You can decide to enter it in centimeters, or feet and inches. The latter will automatically update the value in centimeters as that is used for the application's internal calculations. This information is required.

Language: You can select the display language of the application here. Options are currently English and German, English being the default. Select the language from the dropdown menu. It takes up to a few seconds to update the worksheets.

Weight Unit: Choose your preferred weight unit here. Options are kilograms (kg) and pound (lbs.), with one pound corresponding to approximately 0.454 kilograms.

Calorie Tables

Entering New Foods

Before you can start tracking your meals you must enter the amount of Calories or kilocalories for the different types of food you are consuming. Click on the "Calorie Tables" tab or on the corresponding link on the "Main" page. On the Calorie Tables page you can enter data in nine generic food categories, which you can freely use as they work best for you: milk products, fats and oils, fruit, vegetables, meat, grains, liquids, sweets, and other. It is possible to enter all ingredients separately or (recommended to save time) to combine the ingredients of a typical meal in one entry. Note that Calories (capital "C") and kilocalories are actually the same units, the former more common among native English speakers and the latter in many other languages, i.e. 1Cal = 1kcal.

To enter the information for a new food first navigate to the corresponding section. You can use the vertical scroll bar or click on the category name in the table on the left-hand side. Look for the first free line in that category. Enter a description including unit quantity in the left field as well as the number of Calories or kilocalories in the right field. Nutrition information on the package or many websites can help you finding the right values. Before entering your first meal in the food log include all needed calorie information here. You can come back to this table any time and add more.

Deleting Foods

In order to delete any entries in the calorie tables the tables must be updated first. This means that any new entries or changes to existing ones are updated in the corresponding drop-down lists and sorted alphabetically. The lists are updated when you navigate away from and back to the calorie table sheet, or, easier, by clicking on the "Update Lists" button in the upper left (above the category table). If you try any of the actions below when the table is not up-to-date you will get a warning (two actually, because Excel wants to inform you too in addition to the more meaningful note from the application).

Now select the item to be deleted by double-clicking it. The item is then highlighted in red. You can select additional items. To de-select an item double-click it again. After that, click on the button labeled "Remove Selected Items". The application will then confirm the deletion of every selected item individually.

You can delete only data that you have not used in your food log as otherwise information would be lost and the tables and charts broken. The application will warn you in this case, and you need to remove or update the corresponding log entries first. Also, altering the existing entries is not possible (except changing the calorie amount) because consistency of the data could not be maintained this way.

With the calorie information added you are now ready to enter your first meal.

Entering Meals

About the Food Log Page

Clicking on the Food Log tab or on the corresponding link on the Main page takes you to the table where you can enter all your meals and snacks. The Food Log table uses information from the Calorie Tables where a meal and its calories must be entered before you can use it here. That's a bit of extra work in the beginning but you will start seeing the advantages soon. The table includes the following columns:

Date: date of the log entry (day the meal was consumed),

Category: food category as listed on the "Calorie Tables" page,

Item: food selection based on the category,

Energy per Unit: amount of calories per unit as specified in the "Calorie Tables",

Amount: number of units consumed (multiplier),

Total Energy: total energy included in the portion,

×: delete marker.

Additionally, the page contains some buttons for adding and removing entries, as well as links to the Main and Calorie Tables pages.

Entering a Meal

To enter a new meal click on the New Entry button (you cannot add a row at the bottom of the table). The application creates a new row for you to enter the data (scroll to the top if you cannot see it). The current date is automatically added but you can change it if needed. Next, select the food category from the drop-down list. In the item column, which is already filtered based on the selected category, select the food. The Energy per Unit is filled in automatically. In the Amount column specify the number of units you have consumed. The total energy is again automatically added.

Deleting Meals

To delete a table row you must first select it. Selection is done by double-clicking an entry in the × column, which places a marker in that row. You can select multiple rows. They are then deleted by clicking on the Remove Marked Entries button.

Hey, what was that?

After you made a new entry for a day, for which you haven't entered any data before, and move to another page Fitness Tracker creates a new record in the Fitness Log and informs you about that. Nothing to worry about - the Fitness Log is where your daily information is summarized, and it makes sense to create an entry as soon as some data is available, saving you the effort.

Entering Exercises

About the Exercise Log Page

Clicking on the Exercise Log tab or on the corresponding link on the Main page takes you to the table where you can enter all your workouts and activities. Because of individual variations in energy consumption for different exercises (e.g. due to running style, equipment, physical condition, etc.), and in order to enable consistency with other equipment you may be using (e.g. sports watches), most of the data in this table is entered manually. There are websites that help you determining the burnt energy for your activities. This also allows you to fine-tune the data for you personally.

Date: date of the log entry (day of the activity),

Activity: type of exercise, e.g. running, cycling, swimming, etc.,

Time: time spent on the activity,

Speed Average: average speed where relevant

Average Heart Rate: measured heart rate during the exercise,

Maximum Heart Rate: measured heart rate during the exercise,

Calories Burnt: energy used during the exercise,

×: delete marker.

Additionally, the page contains some buttons for adding and removing entries, as well as links to the Main and Calorie Tables pages.

Entering an Exercise

To enter an exercise click on the New Entry button (you cannot add a row at the bottom of the table). The application creates a new row for you to enter the data (scroll to the top if you cannot see it). The current date is automatically added but you can change it if needed. Next, enter an activity type. You can freely choose what you use here. Then enter values for Distance in kilometers and Time. For the time, you should use the format hh:mm:ss (for example 01:15:30 for one hour, fifteen minutes, and 30 seconds), and Fitness Tracker automatically calculates the speed average. If you us a heart rate monitor you can also add heart rate values for the exercise, though this is optional. Last, you add the amount of burnt Calories. If you are using some sports tracking device such as a sports watch you can directly copy the value from there. If not, there are some web addresses further down in this guide that help you estimating. The input for this field is intentionally kept manual to allow you to synchronize your data with your sports tracking devices, and to fine-tune the value for you personally.

Deleting Exercises

To delete a table row you must first select it. Selection is done by double-clicking an entry in the × column, which places a marker in that row. You can select multiple rows. They are then deleted by clicking on the "Remove Marked Entries" button.

Note

Just like in the Food Log a new Fitness Log entry is created when you enter the first data for a given day.

Fitness Data

About the Fitness Log Page

Now you are at the core of the application. Here is where all your fitness data is summarized and combined. Good news is that most of the columns here are filled in automatically based on other columns on this page and on other pages. A bit of information you need to add though. Let's have a look at the table columns:

Date: date of the log entry

Weight: measured weight using preferred unit

BMI: Body Mass Index

Resting Heart Rate: measured resting heart rate

Maximum Heart Rate: maximum heart rate from all activities on the given day

Calories Consumed: calories consumed (based on Food Log)

Calories Burnt: sum of basal metabolic rate and calories burnt during exercise

Energy Balance: difference consumed energy minus burnt energy

×: delete marker.

Additionally, the page contains some buttons for adding and removing entries, as well as a link to the Main page.

The Basal Metabolic Rate (BMR)

Besides the calories burnt during exercise there is a considerable amount of energy you use every day just by keeping up your basic body functions. This energy is called basal metabolic rate. It varies among people and depends on height, weight and gender. It also assumes a certain level of activity, so your personal rate may be affected if you spent most of your time sitting at your desk or if you move around a lot on a typical day. Actual exercises are not included in the BMR.

There are a few standard formulas available for calculating the BMR, all with very similar results. There even exist methods to measure your personal BMR (e.g. spiroergometry) but the level of accuracy they provide is not useful for calculating your daily energy balance. You will not be able to determine the effectiveness of your digestive system as accurately. Fitness Tracker uses the so-called Harris Benedict Formula and the Broca Index Correction, calculating your BMR based on age, gender, weight, height, and, where applicable, overweight.

Entering Fitness Data

The corresponding Fitness Log entry is automatically created after entering data in Food Log or Exercise Log. There should only be one entry per day in this table. To add your daily fitness record manually click on the New Entry button (you cannot add a row at the bottom of the table). The current date is automatically added but you can change it if needed. Next, enter your measured weight, which is needed to calculate the BMR. Optionally, you can add the Resting Heart Rate. The next four columns are filled in automatically.

Deleting Fitness Data

To delete a table row you must first select it. Selection is done by double-clicking an entry in the × column, which places a marker in that row. You can select multiple rows. They are then deleted by clicking on the "Remove Marked Entries" button.

Viewing Charts

Chart Elements

On the Fitness Chart page you can see a graphical representation of your daily fitness data. Here you can track your daily energy intake and output, the energy balance, as well as your weight more visually.

Weight: measured weight using preferred unit

BMI: Body Mass Index

Calories Consumed: calories consumed (based on Food Log)

Calories Burnt: sum of basal metabolic rate and calories burnt during exercise

Energy Balance: difference consumed energy minus burnt energy

The button on the upper right takes you back to the Main page.

Note that, by design, the timeline shown in the chart is not a true date scale. If you skip days they will not be shown as gaps in the chart.

Selecting Data

Using the selection options on the right-hand side of the chart you can show and hide data series. To select a data series place a checkmark in the corresponding box. Remove the checkmark if you want to hide the series. If you have weight index selected you can also choose the weight index (weight or BMI) you want to see.

Using the drop-down list select the range of dates, for which you want to view the data.

Troubleshooting

Bad Input or Broken Table Cells

Fitness Tracker runs within Excel. As the user of an Excel workbook you, by default, have a lot of freedom editing and formatting data. When programming Excel VBA, editing capalilities are not designed and permissions not added. Instead, in order to protect the document from accidental damage, default permissions are restricted. Now there is some trade-off in terms of how much restriction is reasonable without too many sacrifices in simplicity and performance. In almost all cases, Fitness Tracker and Excel will guide you. Fitness Tracker even has some capability to heal itself. But, for instance, by copying data across columns you might lose data format or validation. If that happens it is best to simply delete the affected table rows and re-enter the data.

Errors

There are two types of errors you may encounter:
Formula Errors: If you see strange entries such as "#DIV/0!" or "#VALUE!" in a table, a formula or one of the cells it uses for calculation is broken. In that case, proceed as mentioned before, delete the affected row, and re-enter the data.
Script Errors: This (or any application error) is typically indicated by a dialog box often containing additional information about the error. The cause could be internal or external to Fitness Tracker. When this occurs try repeating the last action. If the problem persists it is best to close and re-open the document without saving your changes or saving the document with a new name or in a different folder. If you are unable to recover the current document you can revert to the last working copy.

Macros Not Enabled

As mentioned in the Getting Started section Macros must be enabled for Fitness Tracker to work. Disabled macros could lead, among others, to any of the following symptoms

  • an error message indicating that a script could not be run
  • table cells not being updated
  • chart not being updated
  • chart selections not working

If you cannot see the button to enable macros then click on the File tab above the Excel ribbon or enter ALT-F to access the file menu. There, in the Info section, you find a security warning with a button labeled Enable Content next to it. Click that button and select Enable All Content. Make sure that, when you use the Save As function, you always select the document type Excel Macro-Enabled Workbook (*.xlsm), otherwise all VBA code is removed in the process.

Contact Me

If you run into trouble and are not able to correct the problem yourself feel free to contact me using the form on the bottom of this page.

Hints and Tips

Auto-Fill Support in Tables

The current version of Fitness Tracker does not support auto-filling table cells based on allowed values. So, when you use a value for the first time, you have to select it from the drop-down list (recommended) or type it completely. This is to keep things simple and avoid performance penalties, and also to avoid overriding Excel's own auto-fill. After you have used a value once you can re-use it by just typing the first letters (as many as needed to uniquely identify the value) and, when Excel auto-filled the value, hitting Enter, which greatly simplifies the usage of the application. If you select a not allowed value this way (a food from the wrong category) Excel asks you to correct it.

Avoid Copy/Paste

Except for the date where copying works and saves time, it is recommended not to copy data among cells, especially not different types of data. Because cells include formatting, data validation, and formulas that are not visible, copying data and formatting may lead to errors. But don't worry, if you accidentally broke a table row, you can fix it by removing the particular row (placing the × marker in the last column and clicking the Remove Marked Entries button). Then re-enter the data.

Web Sites with Calorie Information

As a primary source you should use nutrition information provided on the food package. If that is not available, the following websites could be useful to you:

  • In English:
    • USDA National Nutrient Database (link)
    • NutritionData (link, partially based on USDA National Nutrient Database)
    • Nutritionix (link, focused on restaurant nutrition information)
  • In German:
    • FDDB (link)
    • nährwertrechner.de (link)
    • Kalorientabelle.net (link)

Note that the links to these websites do not imply endorsement of their products, services, or views, nor do they guarantee correctness of the information. They are merely examples, and there are many more sources that are not listed here. Search the web for additional sites. In order to get reasonably accurate information I recommend consulting at least two independent sources.

Backups

Don't hesitate making occasional backups of your Fitness Tracker file. Having program logic and data combined in the same file always bears the risk that if the logic breaks the data might be difficult to recover. On the other hand it is awfully convenient not having to deal with multiple program and data files and easily moving the program around. Do backups - it's as simple as copying a single file.

These terms are valid for Fitness Tracker version 0.9 beta, and for later versions unless replaced by new terms.

GENERAL:

This is an agreement between me, Juergen Goebeler, as the licensor of the software and you as the end user and licensee. The software is provided to you free of charge for personal use. Please understand, that, in consequence, "free" also means that I cannot accept any liability whatsoever nor guarantee any support. The software is verified to work reasonably well and I have taken reasonable precautions to ensure it is not harmful to your system. You can freely use and distribute the software under the conditions outlined below. You must accept these terms before you can take the software into use.

The software including the Excel® workbook, worksheets, content, formulas, and Visual Basic® for Applications (VBA) code that is not built-in functionality of Microsoft® Excel® remains my property. Any re-use, partially or complete, is prohibited without written permission.

YOU MAY:

  • use the software on as many of your computers and make as many copies as you wish
  • provide copies of the original software to people you know
  • provide a link to the software’s web page.

YOU MAY NOT:

  • distribute the software or any part of it without the original protection of worksheets, workbook, and VBA project
  • attempt to break into password-protected components of the software, disassemble or reverse-engineer any part of the software (this is to protect my own work and also to ensure there are no altered versions in circulation that are beyond my control and potentially contain harmful code).

You may also not

  • distribute modified versions of the software
  • remove license terms or copyright labels
  • sell the software
  • offer the software or any component of it as part of any commercial or non-commercial offering (product or service) without the my permission
  • distribute the software to a wider audience e.g. on web sites, through mass e-mails, magnetic or optical media, with magazines, etc. without my permission.

WARRANTY AND LIABILITY:

The software is provided as is. Reasonable precautions using commercially available security software were taken to keep the software and the system, on which it was developed, virus-free. The software (Excel document including formulas and VBA-code) does not add any functionality that communicates with other systems.

I can claim this for any copy directly obtained from my website, but not for copies you receive via other parties.

You use this software entirely at your own risk. No guarantee is made that it is free of errors or that it meets your needs. I accept no direct or indirect liability whatsoever resulting from the use of the program.

HEALTH NOTICE:

This application replaces neither a physician nor a professional coach. Use common sense and avoid going to extremes. Understand your own abilities and your limits. Aim for balance, not for rapid change.

  1. Educate yourself about healthy nutrition, and keep a balanced diet. Do not starve yourself.
  2. Do not over-exercise and consider your overall physical condition. Consult your physician concerning types, amount, and frequency of exercise suitable for you.

This application may help you finding a balance of energy intake and consumption. But that is only part of the picture. If you don't understand that do not use this application. Your are using this software at your own risk, and I take no responsibility for any health issues that may occur. If you don't understand that do not use this application.

FUTURE VERSIONS:

This software is intended to be free for personal use. Currently, there are no plans to get any revenue from it. It is however possible that significant future development or contracted work make it necessary to charge for coming (or custom) versions. Of course, you may continue using the current version free of charge.

I value feedback. If you contact me by e-mail as a user of the current version, I will continue offering future publicly available (not custom) versions to you free of charge for a minimum of one year.

SUPPORT:

As this software is free, I cannot guarantee any level of support. However, I will do my best to help resolving any problems you encounter, fixing possible bugs, and developing the software further as time allows. It is therefore worth contacting me if you find any issues or if you have improvement suggestions.

ACKNOWLEDGEMENTS:

Microsoft, Excel, and Visual Basic are registered trademarks of Microsoft Corporation in the United States and other countries.

LICENSOR:

Juergen Goebeler
juergen@goebeler.com
www.goebeleler.com

Feedback

Thank you for using this program. Please let me know your thoughts and ideas, or if you have found any problems. If you want me to contact you please enter your email address and make sure it is correct. In case you report a problem include additional information such as the software versions you are using, the symptoms, as well as the sequence of actions needed to reproduce the problem.

 
 
 
 
 

Home | Contact | About
Copyright © 2012-2017 Juergen Goebeler