Export and analyze your own data in Excel

Options
1…1618202122

Replies

  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    edited January 2015
    Options
    tsawrie wrote: »
    I have a calorie burn question. my scorecard says that I am sedentary or lower vs eTDEE or logging is incomplete. Can someone explain that to me? Also, I work out almost every day and sometimes twice a day but I also sit at a desk every day. I have mfp setting to sedentary for that reason. However, mfp calories burned from yesterday was 2690. So, should I adjust my activity level? And where is this information being used in the excel sheet?

    It means that your observed TDEE (which is the TDEE that is calculated from cals you log and actual weight loss logged) is lower than the estimated TDEE (from standard equations)

    causes of this:
    - incomplete import
    - incomplete logging
    - sedentary
    - issue with program or data

    If you like, we can discuss this via PM - or in another thread, I just don't want to clutter this thread with what could be long personal cases. BTW, if we go to PM - I'm not selling anything :smile: promise.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    ephphy wrote: »
    The formulas in P14 and S14 on the Weight and TDEE graphs tab need a tweak. They're trying to call from cells that have text, without trimming the text out (P7 and P8 respectively). I noticed it when it didn't give me expected goal dates because it said I had no loss when the table above clearly showed loss! When I tweaked them to remove the units from P7 and P8 they came up with a goal date range spot on for what I'm expecting based on other tracking.
    Thanks a huge bunch for this though! I'm going to start looking at the vba to see if I can isolate some of the things that want to include in another tracking spreadsheet I've got going.

    Good catch, bug created when I integrated the units into the field.
    Correction now updated into the next release.

    Thanks!
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    ckt873 wrote: »
    This looks fantastic but doesn't seem to be working correctly for me. I suspect that the issue has something to do with a recent update to MFP, but the spreadsheet seems to have difficulty collecting the detailed data from MPF. For example, for all but 5 of my 118 day logging streak, the spreadsheet apparently believes that I didn't eat. I am getting data on calories burned, and a total net calories per day, but this is screwing with many of the calculations. Time for another update soon?
    ephphy wrote: »
    With the wrong year error, try this quick fix (it worked for me, and also cleared up the wrong logging error that @ckt873 brought up that I was experiencing).
    • With the spreadsheet open, go to Visual Basic (Alt+F11, or under the developer tab, hit the button for Visual Basic).
    • If it doesn't start you there, open the Module1 module - in the side menu under VBAProject (mfp5v10.xlsm), Modules, Module1 and double click.
    • You should then see something that starts like this: 
      'Updated and majority work by evgenizyntx 2013, 2014
'EZ version 4.20
'inspiration from orginal input by gavins, 2012
    • Do a search for 2014, and find next until it shows you this line: 
      ddd(i) = DateSerial(2014, imm, idd)
    • Change the 2014 to 2015
    • Save
    • Close Visual Basic.
    • On the first/main tab of the spreadsheet, hit the reset button to clear anything that was there for a 'fresh start'
    • Run as usual and fingers crossed you get the data you should.

    As I said, it worked for me (I don't proclaim to be an expert at VB), so hopefully it does for you too.

    Thanks for looking in the code for the fix - I've updated it so that it will work in 2015+
    Your assessment is correct.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    fillingame wrote: »
    Will this ever be available to work on a Mac? Just asking, I know you spent more time than I can imagine on it, so I am not whining, just asking! Thanks!

    No, while I have access to a Mac, the macro function library just isn't the same and I wont be doing the development from scratch.

    If I do change the development I would move it to a web page or welcome MFP to do so, however that does require passing passwords and id to a server to do the data upload and I do not want to manage the risk associated with an app that asks you for your MFP id/passwrd.

  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    NoelFigart1 wrote: »
    Why does this need to be macro enabled? What calcs are you doing that require them? Can I see the code for it?

    Because the import data functions and some of the calculations are done in macros.
    Yes, you can see all the code in the macro module.

    Just load the program and open the code. The program asks for no password or id (but might open a browser that requires you to log in) and it never writes or opens any file on your hard drive.

    If you do not trust macros to run on your machine, I'm afraid you this tool will not work for you. You might want to use one of the on-line exporting tools.

  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    debrag12 wrote: »
    I don't have excel only open office :(

    Edit: downloads a free trial but issues opening it.

    This program won't work in Open Office as it does not have the right macro language.
  • debrag12
    debrag12 Posts: 1,071 Member
    edited January 2015
    Options
    thanks I have downloaded the free trial of excel so will see how it goes.
  • NoelFigart1
    NoelFigart1 Posts: 1,276 Member
    Options
    EvgeniZyntx wrote: »
    NoelFigart1 wrote: »
    Why does this need to be macro enabled? What calcs are you doing that require them? Can I see the code for it?

    Because the import data functions and some of the calculations are done in macros.
    Yes, you can see all the code in the macro module.

    Just load the program and open the code. The program asks for no password or id (but might open a browser that requires you to log in) and it never writes or opens any file on your hard drive.

    If you do not trust macros to run on your machine, I'm afraid you this tool will not work for you. You might want to use one of the on-line exporting tools.

    Long's I can look at the code, I'm dandy. Thanks!
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    Tool has been updated and is now available from the usual location - This is now Version 5.02

    http://www.myfitnesspal.com/blog/EvgeniZyntx/view/new-mfp-data-export-tool-major-update-659927

    Update works in 2015, corrected bugs on the "you will reach your goal on" calculations and improved the weight extraction calculation - previously the data was extracted "as is" from MFP, and this gave large jumps if you were missing weight data. These are now extrapolated during the extraction.

    Testing is just beginning so you might see a bug or two that I failed to catch or that might occur on your own data. Please let me know if you have any issues..

  • eric829_
    eric829_ Posts: 20 Member
    Options
    Hi EvgeniZyntx,

    I downloaded a new copy and get the error "After location 3 in function ImportXMLDataMFP, Import Step 3 Most recent error 424. Object Required.

    A few pages in IE11 opened, but it stopped and it's at a blank white page attempting to read calories burned.

    I've tried several times but it always stops at the same error/page.
  • eric829_
    eric829_ Posts: 20 Member
    Options
    I'll try this from another computer just to be sure.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    Taking this bug to PM, so we can try various solutions.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    New update based on input yesterday - is now available from the usual location - This is now Version 5.03

    http://www.myfitnesspal.com/blog/EvgeniZyntx/view/new-mfp-data-export-tool-major-update-659927

    Corrections include:
    - restructured debugging info, most users won't see this but allows me to have better insights when debugging
    - corrected important error - start and end date were not being written prior to diary extract, caused issues
    - corrected weight data for today if not entered in mfp
  • Paul_Collyer
    Paul_Collyer Posts: 160 Member
    Options
    This looks amazing, but I get an error message straight away......

    "After location 1in function ImportXMLDataMFP. Input step:0 The most recent error number is 5. Its message text is:Invalid procedure call or argument. Please report this to EvgeniZyntx"
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    Paul_Collyer wrote: »
    This looks amazing, but I get an error message straight away......

    "After location 1in function ImportXMLDataMFP. Input step:0 The most recent error number is 5. Its message text is:Invalid procedure call or argument. Please report this to EvgeniZyntx"

    Msg sent, Paul.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    edited January 2015
    Options
    Small update based on corrections - is now available from the usual location - This is now Version 5.04

    http://www.myfitnesspal.com/blog/EvgeniZyntx/view/new-mfp-data-export-tool-major-update-659927

    Corrections include:
    - for recent MFP accounts, shifts in start date corrected
    - optimized a few equations for speed

    Report Card now looks like this:
    qryda4g4uorl.jpg


  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Options
    Export Tool update - version 5.05: http://www.myfitnesspal.com/blog/EvgeniZyntx/view/mfp-data-export-tool-the-overview-659927

    corrections include
    - removed bug on reading dairy where the Foods (ie Whole Foods, or Best Foods) resulted in a blank calorie count for the day
    - removed weight calculation bug for empty yesterday data
  • ephphy
    ephphy Posts: 20 Member
    Options
    Glad you're back :) I tried to help, but as I'm not a wizard like you I could only try fix the problems I also encountered.
  • girlviernes
    girlviernes Posts: 2,402 Member
    Options
    This is amazing, and I love this thing. Wahoo, observed TDEE is now 3000!
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    edited January 2015
    Options
    ephphy wrote: »
    Glad you're back :) I tried to help, but as I'm not a wizard like you I could only try fix the problems I also encountered.

    Thanks, you did a great job!
    girlviernes wrote: »
    This is amazing, and I love this thing. Wahoo, observed TDEE is now 3000!

    That's impressive!

Categories