Training guidance tools using the API

Haven’t thought of this before, but it should work just as well with SDNN if that’s what you have. Then you need to edit the formula in C2 to get data from the SDNN column on the wellness tab and drag the formula down.

My manually downloaded CSV does not contain any HRV or hrvSDNN values… Hence, I guess this won’t be useful for me since my watch doesn’t support these metrics

Yes… that’s what I did. copied and pasted it to the wellness sheet. (but had to manually move some of the data to the “right” place given my manually downloaded CSV is quite different.)

There is no single watch doing this. You need an app like Kubios, HRV4Training, iThlete, Oura-ring…
Those are the apps that will give you decent morning HRV values. The Garmin “stress” indicator for example is completely useless for this.

1 Like

I have this, but am definitely not religious in using it every morning…

This is what I have in my Excel for mac.


The updated Excel file for linking a manual download is ready.
Download the file, and let me know of any problems. This will also work on a Windows-based computer, it you wish to use the manual download of CSV files.

Try use the latest file I shared a few minutes ago.
It’s also setup to get the data from your downloads folder.

1 Like

It may be a us formula… the week and year counting is not the same for the first and last week of the year

The formula uses isoweeknum, which is the first Thursday of the year. I was meant to provide a fix for that, and forgot about it.

I forgot about the Kg/Pounds conversion, but did have a look at it.

The dropdown list shows “Kilograms (Kg)”, but the formula shows “Kilograms” only.
If you add " (Kg)" it will be correct; note there is a space between Kilograms and (Kg).

Before:

After:

There is a hidden tab (called lookup), that does a lookup on the data from Activities and Wellness, to then summarise the weekly Load, Fitness, Fatigue, Ramp Rate and Hours, effective every Monday. The date might show 2022-52 but it is “taken care of” in the lookup tab.

1 Like

Do I understand correctly that only Power Query is supported on Mac? If yes, I’m outta luck 'cause I have v16.16 and v16.57 or later is needed.

Importing a text file just throws the data into new columns and nothing else populates.

I installed the trial version of Excel 365 and now have Get Data (Power Query). However, no option window opens when clicking Data Source Settings.

Trying the Google Sheets version… Is there a ReadMe for that version? I can’t figure out how to make it work either.

Thanks for your work guys looks great.
I’m usually on a Mac so had held off until something like the sheet @Gerald produced a couple of weeks ago arrived.
I already have a simple shell script that pulls activities via API calls for me and my “coached” athletes (the family) and then opens an Excel file that pulls in the data.

So making this work for activities was a simple matter of editing the Data Source, all done and working. :slight_smile:
I hoped wellness and getting HRV graph would be as easy. API call was straightforward, file downloads, looks OK, but returns an Expression.Error on Run_eftp whenever I try to refresh, has anyone else seen this, or have any suggestions?

image

Easiest workaround is to add a fake FTP to your settings for running in intervals.
This has been corrected in the latest Win version.Don´t know if it is in latest Mac Version from @Gerald .

A shortcut is to enter the run eFTP in the following two places:

  1. Settings (set FTP under run settings)
  2. Activities (set eFTP for Run)


I haven’t updated it since version 1.0
I’ll try spend some time updating it based on the the latest changes required on the windows version.

Open the Power Query Editor advanced editor and copy paste the highlighted section from the latest Win version.
Don’t change the source setting and you should be good.

1 Like

Thanks @MedTechCD and @Gerald, I have an FTP set (for run and bike) and eFTP (at least relatively recently), I’ve tried adding in the eFTP as suggested, and only getting wellness data from last year or so (where the data is “good”) and ran in to same problem.

In the end I skipped the import and did an old school copy and paste from csv open as separate Excel sheet, and found that there’s an extra column in mine. Unfortunately with many columns in the middle blank I don’t know where the misalignment has occurred yet (somewhere between sleep quality and Vo2max is all I can tell so far), but can see a whole pile of things working nicely so many thanks guys!

1 Like

I am getting an error too from the Wellness tab. As the MacOS version is a manual download and not API linked, it shouldn’t give errors like on the Windows version.

I’ll take a look later today, or over the weekend.