More “Exponential” Exponential Moving Average in Excel?

The purpose of a moving average tool is to give you a sense of how strong your trend is, and, ultimately to serve as a signal. You can run backtests on this stuff and it is gravy ex-post but is terrible ex-ante.

What you really want to do is evaluate the strength of a trend using a Kalman filter and then measure volatility of the price using variance or std. Combining the two is a powerful signal generator. However, you will need to backtest the optimal lookback period, as that changes per stock per volume etc.
 
 

Attachments

  • technicalindicators-net.jpg
    technicalindicators-net.jpg
    41.9 KB · Views: 8
Last edited:
I have been calculating exponential moving averages in Excel using a formula I found on the Internet
EMA=(2/(N+1))*Spot+(1-(2/(N+1)))*Previous EMA), copied and pasted down the column.

PIlJULK.png


Can anyone provide a formula to calculate an EMA in Excel that's more "exponential", i.e. even heavier weight to the recent values?

For example, if the current "strength" of the most recent data in the standard EMA formula above is 2 (just an example, don't know if it's true), then how to increase the "strength" to be, say 3 or 4?

Thank you.

Why dont you just change N?
 
I have been calculating exponential moving averages in Excel using a formula I found on the Internet
EMA=(2/(N+1))*Spot+(1-(2/(N+1)))*Previous EMA), copied and pasted down the column.

PIlJULK.png


Can anyone provide a formula to calculate an EMA in Excel that's more "exponential", i.e. even heavier weight to the recent values?

For example, if the current "strength" of the most recent data in the standard EMA formula above is 2 (just an example, don't know if it's true), then how to increase the "strength" to be, say 3 or 4?

Thank you.
Hard to get a direct and correct answer on the internet about trading, isn't it?

Increase the smoothing factor. In your formula, the smoothing factor is 2.

Doing a proper google search will reveal more details about the smoothing factor.

(That said, obviously, decreasing the number of days also increases the weight of the most recent data points.)
 
The attached articles by John Ehlers give you an idea what can be done with adapative EMAs. Btw, moving averages are lowpass filters in digital signals processing terminology. EMAs are infinite impulse response (IIR) lowpass filters. An SMA is an example of a finite impulse response (FIR) lowpass filter.
 

Attachments

To answer my own question, also for those who are interested.
Actually I just need to increase the '2's in the formula to increase the strength.

Pgk1FE87RX_UawQDHhqHqA.png

Thanks for everyone who has replied.
 
To answer my own question, also for those who are interested.
Actually I just need to increase the '2's in the formula to increase the strength.

Pgk1FE87RX_UawQDHhqHqA.png

Thanks for everyone who has replied.
You're welcome:

upload_2020-10-30_22-19-11.png


I'll block you later.
 
Back
Top