Export and analyze your own data in Excel

1…910121415

Replies

  • GingerbreadCandy
    GingerbreadCandy Posts: 403 Member
    Brilliant!
  • debrag12
    debrag12 Posts: 1,071 Member
    edited January 2015
    I don't have excel only open office :(

    Edit: downloads a free trial but issues opening it.
  • Russ8681
    Russ8681 Posts: 4 Member
    Problems with data import

    There are 72 rows of data. On the CHECK IN tab, in the “weight change” column, the data stops populating after 31 rows due to no formulas being placed in the cells after this point.

    On the CALC tab, I get #VALUE! Error in columns J, AH, and AI with blank cells in columns g, h, t, u after the problem starts in the 42 row of data. Also, the formula stops populating in column w after 52 rows of data.

    Now, assuming I can ever get my data complete, I’m sure I still have no clue of how to interpret the data. From what I can gather, it says I should be losing weight like crazy, which I have been. But, after 35 lbs in 9 weeks, I’m doing the exact same things and the losses have stopped completely. It’s actually starting to creep back up.

    No wonder losing the weight you need is really impossible. I have run every math angle I can and can’t find anything that justifies the weight loss I was having unless my BMR swings from 500 to 4500 from day to day. The numbers just don’t add up. A calorie must not be a calorie. A pound must not be 3500 calories. A 300 calorie intake deficit from what the theoretical BMR is supposed to be, combined with 300 calories of extra walking at 3mph doesn’t even come close to accounting for an average weight loss of ½ pound a day for 60 days, then a complete of all loss.

    So frustrated. To finally get my mind right and get started, but only 1/3 of the way into my goal to have it all fall apart.
  • stumpfk
    stumpfk Posts: 2 Member
    DapperKay wrote: »
    Hi. I wrote a tool to export the reports data as .csv files for use in Excel or similar programs. No fancy graphs, but it should work on both Mac and PCs. I just finished writing it a few hours ago, so with your feedback, I'd love to make it a better tool. Thanks! http://foodfastfit.com/myfitnesspal-x-foodfastfit/

    Thats pretty cool, thanks. Wondering if the dates are messed up on purpose? For example:

    Nov-13 - 01/11/2013
    Nov-14 - 01/11/2014
    Nov-15 - 01/11/2015
    Nov-16 - 01/11/2016

    Incrementing by years and not days?

    Hi. I just made a small change to explicitly set the 4-digit year. This should help Excel correctly infer the date. Please let me know if this works for you. Thanks. http://foodfastfit.com/myfitnesspal-x-foodfastfit/

    Much better now. Now follows: MM/DD/YYYY. I'll just re-configure it to DD/MM/YYYY myself, thanks!

    AWESOME!!! Thanks so much, this is great! You've just saved me HOURS.
  • ephphy
    ephphy Posts: 20 Member
    @Russ8681:
    The weight change column is used to determine your regularity in weighing, used in the scoreboard on the first tab. It will give you an A grade if you have weighed (and had the weight change number) more than 5 times in the last 31 days - so once a week ish. If you weigh on average less than once a week if will give you the other grades, and less than twice in 31 days will get you a D grade. This doesn't need to go back further than 31 days hence this column finishing where it does.

    The W column on the Calc tab stops after 52 rows because this spreadsheet is geared up for a yearly snapshot, or 52 weeks. You will see columns W through AC are headed up "week graphs data" and the results in column W show a range that includes seven cells, for seven days in a week.

    When you say blank cells after the 42 row, does this mean you're only getting about 8 rows of data come through? Is your diary set to public? If not that is the likely culprit to that error.

    Hope that info helps.
  • Russ8681
    Russ8681 Posts: 4 Member
    edited January 2015
    I have 78 days of total data. The imported data in those columns stops after 41 rows.

    1893 1893 122.2 3814
    1895 1895 122.4 3650
    1893 1893 122.7 3733
    1873 1873 122.9 3862
    123.2 #VALUE!
    123.4 #VALUE!
    123.7 #VALUE!
    123.9 #VALUE!
    124.1 #VALUE!
    124.4 #VALUE!
    124.6 #VALUE!
    124.8 #VALUE!
  • Russ8681
    Russ8681 Posts: 4 Member
    Sorry, I was trying to edit that. The cell with the Value errors depend on data in the columns that are empty . Yes, the settings are as required. It just stops pulling the data.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Ok, I'm sorry I have not been back to address questions or new issues with the tool. I'll try to do so over the next days.
  • hupsii
    hupsii Posts: 258 Member
    Great idea ! Thank you so much
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    stedgeg wrote: »
    tsawrie wrote: »
    I don't think mine is working correctly. On my "check-in" tab, it doesn't list any calories, carb, fat, etc imported. But it shows net cals, cals burned. Nothing listed in the calculated from setup columns either. What am I doing wrong?

    I am getting the same result. Any update available? Thanks for your work!

    Current version is 5.01. It should solve that issue. But I'm currently reviewing the app for the new year and site modifications, expect an update in the next days.
    atb001 wrote: »
    What happens with incomplete entries, e.g. I filled in lunch but left the other meals? Can the spreadsheet detect that, or do I need to manually remove those days? Also, is there a way I can manually enter the weight data, as I haven't entered it on MFP but do have the info on an excel sheet. Thanks

    The app treats that as a day of x calories. x is what you entered. If you didn't enter the data, it still considers that what you entered is your full day.

    If you wish to manually enter the weight data - go to Check_In tab and enter it there, not in the Weight tab.

  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    edited January 2015
    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
    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
    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
    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
    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
    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
    thanks I have downloaded the free trial of excel so will see how it goes.
  • NoelFigart1
    NoelFigart1 Posts: 1,276 Member
    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
    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
    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
    I'll try this from another computer just to be sure.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    Taking this bug to PM, so we can try various solutions.
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    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
    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
    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
    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
    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
    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
    This is amazing, and I love this thing. Wahoo, observed TDEE is now 3000!
  • EvgeniZyntx
    EvgeniZyntx Posts: 24,208 Member
    edited January 2015
    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