How to calculate Exponential Moving Average to show Fitness and Fatigue

Hi All,

I’m trying to recreate the Exponential Moving Average Calculation in Training Peaks using the TSS Score.

For fitness you need to take the Exponential Moving Average for the past 42 Days

For Fatigue you need to take the Exponential Moving Average for the past 7 days.

I feel I am close as I already have the pattern for a 42 and 7 day Day moving Average. Just need to change this to Exponential.

Fatigue (ATL) = 
AVERAGEX(
	DATESINPERIOD( Dates[Date],
		LASTDATE( Dates[Date] ),
		-7, DAY ),
		[Total TSS] )

Fitness (CTL) = 
AVERAGEX(
	DATESINPERIOD( Dates[Date],
		LASTDATE( Dates[Date] ),
		-42, DAY ),
		[Total TSS] )

The only other thing I need help for is to calculate Form (TSB) which is the balance of TSS equal to yesterday’s fitness minus yesterday’s fatigue.

Here’s an export of my data from Training Peaks with the TSS Scores of my training.

workouts 2017.csv (9.0 KB)

I’ve found an article to show how to calculate Exponential Moving Average which I think has everything we need to do the calculation it even has an excel file with an explanation. I need help bringing in the previous days TSS.

From the explanation below I’ve calculated the weighting multiplier for 42 Days to be 0.0465 and 7 Days to be 0.25.

For example for a 42 Day Weighting Multiplier

(2/(42+1) = 0.0465

For example for a 7 Day Weighting Multiplier

(2/(7+1) = 0.25

Exponential Moving Average Calculation
Exponential moving averages (EMAs) reduce the lag by applying more weight to recent prices. The weighting applied to the most recent price depends on the number of periods in the moving average. EMAs differ from simple moving averages in that a given day’s EMA calculation depends on the EMA calculations for all the days prior to that day. You need far more than 10 days of data to calculate a reasonably accurate 10-day EMA.

There are three steps to calculating an exponential moving average (EMA). First, calculate the simple moving average for the initial EMA value. An exponential moving average (EMA) has to start somewhere, so a simple moving average is used as the previous period’s EMA in the first calculation. Second, calculate the weighting multiplier. Third, calculate the exponential moving average for each day between the initial EMA value and today, using the price, the multiplier, and the previous period’s EMA value. The formula below is for a 10-day EMA.

Initial SMA: 10-period sum / 10

Multiplier: (2 / (Time periods + 1) ) = (2 / (10 + 1) ) = 0.1818 (18.18%)

EMA: {Close - EMA(previous day)} x multiplier + EMA(previous day).
The Weighting Multiplier
A 10-period exponential moving average applies an 18.18% weighting to the most recent price. A 10-period EMA can also be called an 18.18% EMA. A 20-period EMA applies a 9.52% weighting to the most recent price (2/(20+1) = .0952). Notice that the weighting for the shorter time period is more than the weighting for the longer time period. In fact, the weighting drops by half every time the moving average period doubles.

If you want to use a specific percentage for an EMA, you can use this formula to convert it to time periods and then enter that value as the EMA’s parameter:

Time Period = (2 / Percentage) - 1

3% Example: Time Period = (2 / 0.03) - 1 = 65.67 time periods
EMA Accuracy
Below is a spreadsheet example of a 10-day simple moving average and a 10-day exponential moving average for Intel. The SMA calculation is straightforward and requires little explanation: the 10-day SMA simply moves as new prices become available and old prices drop off. The exponential moving average in the spreadsheet starts with the SMA value (22.22) for its first EMA value. After the first calculation, the normal EMA formula is used.

The formula for an EMA incorporates the previous period’s EMA value, which in turn incorporates the value for the EMA value before that, and so on. Each previous EMA value accounts for a small portion of the current value. Therefore, the current EMA value will change depending on how much past data you use in your EMA calculation. Ideally, for a 100% accurate EMA, you should use every data point the stock has ever had in calculating the EMA, starting your calculations from the first day the stock existed. This is not always practical, but the more data points you use, the more accurate your EMA will be. The goal is to maximize accuracy while minimizing calculation time.

The spreadsheet example below goes back 30 periods. With only 30 data points incorporated in the EMA calculations, the 10-day EMA values in the spreadsheet are not very accurate. On our charts, we calculate back at least 250 periods (typically much further), resulting in EMA values that are accurate to within a fraction of a penny.

cs-movavg.xls (12.5 KB)

I think I may have it :slight_smile:

You need to do a couple of seperate measures to get there. I’ll show how I got to Fitness CTL first.

First you need Standard 42 Day Moving average which is this

TSS 42 Day Moving Average = 
AVERAGEX(
	DATESINPERIOD( Dates[Date],
		LASTDATE( Dates[Date] ),
		-42, DAY ),
		[Total TSS] )

Then you need yesterdays 42 Day Moving Average which is this.

Yesterdays 42 Day Moving Average = 
CALCULATE( 
	[TSS 42 Day Moving Average] , 
		PREVIOUSDAY( Dates[Date] ))

You then need to calculate the 42 Day Weighting multiplier which is as I calculated above

(2/(42+1) = 0.0465

You can then calculate fitness using the following formula (I think)

Fitness (CTL) = 
(0.0465*([Total TSS]-[Yesterdays 42 Day Moving Average])) + [Yesterdays 42 Day Moving Average]

To Fatigue you do something similar

TSS 7 Day Moving Average = 
AVERAGEX(
	DATESINPERIOD( Dates[Date],
		LASTDATE( Dates[Date] ),
		-7, DAY ),
		[Total TSS] )

Yesterdays 7 Day Moving Average = 
CALCULATE( 
	[TSS 7 Day Moving Average] , 
		PREVIOUSDAY( Dates[Date] ))

Then

7 Day Weighting Multiplier

(2/(7+1) = 0.25

Then

Fatigue (ATL) = 
(0.25*([Total TSS]-[Yesterdays 7 Day Moving Average])) + [Yesterdays 7 Day Moving Average]

Then to finally calculate form you need yesterdays Fitness and yesterdays fatigue

Yesterdays Fitness = 
CALCULATE( 
	[Fitness (CTL)] , 
		PREVIOUSDAY( Dates[Date] ))

Yesterdays Fatigue = 
CALCULATE( 
	[Fatigue (ATL)] , 
		PREVIOUSDAY( Dates[Date] ))

Which when subtracted from each other gives you todays Form

Form (TSB) =
CALCULATE([Yesterdays Fitness] - [Yesterdays Fatigue])

I feel like my numbers are right but when I put my TSS scores in My fitness appears high.

Let me know how you go and if you think I should make a change somewhere. I think a few people would be interested in calculating this so my donation to the group :slight_smile:

1 Like

Yes really like this one.

To me looks like the exact technique you should use. Stepped out in a nice way.

This is a cool concept.

I’m flagging it for a follow up idea. May use it in something in the future.

1 Like

Hi David,

I am looking at your formula for CTL. I am new to excel. If you could hopefully help me by stating the exact formula you typed in to generate the CTL.
I would greatly appreciate it.

Thank, Pete

HI @plessard

Please make a new posting as this posting is already solved back in 2018. Your request might not be solved as people in the forum don’t look at already solved posting.

Welcome to the forum.

Thanks
Keith

Hi Keith,
Thanks.

Hi David,

I read your article more closely and found that formula. Thanks for the quickresponse and the formula. I will try it out to see if it corresponds well to my data.
Thanks again.

Please start a new topic it wasn’t David that responds to you