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)