Getting stats from ride files?

[ScottChapman]

I was thinking of writing a little script which could collect some data from my ride files and load them into a spreadsheet for analysis (yea, I know I can buy software, but I already have a good spreadsheet program!)

Anyway, it seems obvious to me how I can generate average speed, watts, etc. from the data in the files. But I don't see how I can derrive other important metrics, like NP, TSS, etc...

I am guessing these are not store in the ride files themselves? Or am I not looking in the right place?

[minus10]

NP and TSS/IF can be calculated.

I think NP= 4th root of the sum of Watts^4

IF=NP/FTP

TSS=[(s x W x IF)/(FTP x 3600)] x 100

Where s is in seconds. W is NP in Watts. From first equation this can be simplified to

TSS = [(s x IF^2)/3600] x 100

Or

TSS = [(s x NP^2)/(FTP^2 x 3600)] x 100

I quite like the equations that show the explicit squared relationship between effort and TSS.
It means those high effort intervals increase TSS a fair bit. Conversely, low effort recovery rides
Are just that and do not stress the system.

Equations from Training and Racing with a Power Meter by Allen and Coggan Phd.

[lorduintah]

I think NP is just a little different:

4th root of [(sum of {s * W^4})/total s]

Here s is the seconds (time) at some W, watts. Watts over each s-time increment is raised the the 4th power. This sum is divided by the total time and then the 4th root is taken.

Tom

[minus10]

Yes. I agree. Just wasn't explicit enough in NP formula.

[ScottChapman]

is it:

4th root((s*(w^4))/total s)

?

If so, then for a ride I calculated 286, but the software shows 231

(although my calc for average power is spot on with what the software says)

[lorduintah]

Are you doing a sum over each time interval or did you take the average Watts? Be careful about stop time and riding time. You may have a cumulative time used, which includes (or not) the non-speed portions.

The equation - pseudo code - that I posted is correct. Now there may be a subtle delta from that the iBike is using - I would have to see what Travis or John would have to say.

The equation uses values by time increment.

[ScottChapman]

Each interval shows watts.

So I took that, brought it up to the 4th power, then multiplied by the interval in seconds. And totaled that up for all the intervals.

When I was done, I divided by the total number of seconds, and got the 4th root of it.

Since my number is higher than what the software shows, I think dropping intervals where speed = 0 would only make matters worse?

[lorduintah]

Quite true. In part the use of the 4th power will lessen the impact of the zero power regions in your ride.

Soooo - it is possible that there is a small bug in the iBike calculation - or - as I mentioned - the iBike software is doing something different. Now are you talking the iBike software or NP calculated on the iBike itself? Here again there could be something more going on - especially if the power values are adjusted or not.

Tom

[ScottChapman]

Talking about the software.

Basically I took one of the ride files, and looked at what the software was generating for stats. I figured that would be my guide.

My calc is 286, but the software shows 231.

[racerfern]

Have you taken a look at this thread: http://www.ibikeforum.com/viewtopic.php ... 9614#p7216

MechGT has built a free plugin for SportTracks. It calculates NP, TSS, IF and builds a very nice chart of TSB values. It even forecasts by allowing you to enter TSS for future events. You can also export the report table as csv data which eliminates a lot of copy and paste.

[lorduintah]

FWIW -

I just took a look at a casual 10 mile ride - Excel calculations lead to 175 for NP, iBike: 144. There may very well be a software bug......TRAVIS!!!!!! - check it out.

[racerfern]

I have never seen the iB4 software, the firmware on the iBike, WKO, or SportTracks via 3 different plugins over two different versions differ by more than 1w for NP and TSS is always within a point or two.

[ScottChapman]

lorduintah,
Can you post your file and I can run my calc on it to see what I get?

[lorduintah]

I had to drop about 4 entries to get rid of #DIV/0 errors in the calculation of times for each recorded interval(unedited). The first entry has the equations and also the summary after line 2490.

Tom

[ScottChapman]

Uh wait a sec. How could you get a Div/o error? The only division I do in my calc is with total seconds, which can't be zero.

One thing I assumed, is that normalized power shouldn't be averaged over the ride. Meaning you don't calculate NP for each interval and then average over the whole ride. Is that a correct assumption?

[ScottChapman]

My calc also gives 175NP

[lorduintah]

Scott -

Looks like you found that I calculate the interval for each recorded time step. This is done with speed from each step recorded and Distance for each step - where you have to take a difference from the previous step to get the incremental distance. When you do this a couple of the intervals do get #DIV/0 errors in the csv file.

At least you and I get the same overall NP.

Tom

[ScottChapman]

Speed? What are you doing with speed? I just look for the interval number (cell E4) which tells me how many seconds at the specified power (Column C). Then I do the calc above.

Speed doesn't come into the equation for me anyway...

[racerfern]

I'm not good at nor do I bother to create Excel formulas when I have various programs that do if for me. I am pretty good at looking at the big picture and trying to make sense of it (at least I think so).

There are two files, one has comments and won't open in iB4, the other one will open. Other than the comments I assume they're the same data. Now, with an average wattage of 113, you would have to have significant sections of time well over 250w to get NP of 170 and they're just not there. This was more or less a steady state ride with only a few efforts over 200w. You would have to have quite a few efforts or intervals at quite a high wattage to make the AP and NP differ by 57w.

WKO says 144, iB4 says 144, SportTracks both TrainingLoad and Training Analysis say 144 and they're completely independent of each other.

[ScottChapman]

So that means the formula we are using isn't right...

Anyone have any idea what the correct formula is?

[racerfern]

Does this help?

http://www.slowtwitch.com/Training/Gene ... a_302.html

or this, look for NP calculation part way down.

http://groups.google.com/group/wattage/ ... this+group

[ScottChapman]

(can't see the google group, but I am using essentially what is mentioned in the slow twitch article.)

What I do for each data point (interval = 1sec) is take the power and raise to the forth power, then multiple by the number of seconds. I then cummulate that for all data points.

Then I take that total, divide by the total number of seconds, and get the fourth root of that.

I registered for the google group so hopefully I can see their forumla, but I suspect it to bethe same.

Wonder what I am doing wrong, looks pretty simple...

[ScottChapman]

Ah ha! Forgot to include the Coggan 30 second smoothing of the power (which I think just means to average it over every 30 seconds). Let me see if that does it.

[ScottChapman]

OK, now I get 146.

I am guessing that is within the margin of error right?

[racerfern]

I didn't know there was a margin for error, it's straight math.

Seriously though, that's certainly close enough.

[ScottChapman]

Oh I assume there is some rounding off and such.

Anyway, does anyone happen to see the FTP value in the ride file? I looked and didn't see it.

I am guessing that the iBike profile is serialized in the file in some form that makes it unreadable. And that the FTP value for the rider is in there somewhere... Where I can't get it... Tragically...

[racerfern]

iB4 shows it at 210.9 but that's the only place I've ever seen it since it's stored in the profile not the ride (although the profile is now part of the ride file itself). If you change it, it changes the profile stored in the ride.

[ScottChapman]

So, for your profile your FTP is 210.9? Do you happen to see that value in your ride file anywhere?

[racerfern]

It's not in the ride file as something that's readable since it's stored as part of the profile. BTW, it's not my ride file we're talking about it's lorduintah's. I'm just participating in the discussion.

[ScottChapman]

Right, sorry about that.

I suspected it was serialized with the profile in a manner that makes it unreadable.