Tuesday, April 16, 2013

Calculate Exponential Moving Average in excel

How to calculate Dow Jones Exponential Moving Average - illustrated with data in excel spreadsheet.

What is exponential moving average(ema) and how is it different from simple moving average(sma)? Simple moving average takes an average of the no of days. So a 20 day sma would calculate an average of all the figures for preceding 20 days. In EMA, we give more weight to recent observations. This is done via using a factor called exponent.

The value for the exponent = 2/(No of days +1)
So for EMA of 20 days the exponent = .047619

Now we multiply today's closing figure with this exponent, and yesterday's EMA with (1-exponent) to get new ema. Obviously you have to start somewhere, so we start with a simple average for the first 20 data points and then move onto the EMA formula from there. Confusing? Have a look at the excel sheet it will become a little more clearer as to what it is that I am talking about and how exactly it is calculated.

Label: ema excel

Saturday, July 7, 2012

How to calculate Exponential Moving Average in Excel

How to calculate Exponential Moving Average illustrated with an excel example.

Have a look at the spreadsheet below. Once it opens to download simply press Ctrl+S

Now the formulas used.

1. Exponent function value - this is essentially the weight used to weigh each new period.

= 2/(1+ no of periods)
= 2/(1+20) for a 20 period EMA

2. Actual calculation of EMA

= Previous EMA * (1-Exponent) + Current Close * Exponent

One clarification here. For calculation of the first EMA we simply use a SMA as value of EMA needs a previous value and thus we need to start somewhere. This does not affect the value of the EMA after the first 5-6 values.

For any clarifications or queries, please leave a comment.

Dow Jones Index Daily Historical data

For those looking for all time dow jones historical data one can download the same from here...

Once it has happened simply press Ctrl+S to download.

What is the difference between simple moving average and exponential moving average

Both are similar in the sense that both serve the same purpose of trying to smoothen out the volatility of the underlying data to give a sense of the direction and trend of the data.

How they differ is in the weightages given to each value. While simple moving average (Sma) gives equal weight to all values, the exponential moving average gives more weightage to recent events and thus is more sensitive to movements in the underlying data than the sma.

A simple way to put it would be that a 10 period exponential moving average would behave like a 7 or 8 period simple moving average, but will be smoother and is thus better to use. Overall, Ema's have proved to be more useful than Sma's and thus one will find that many technical indicators use EMAs. Like MACD uses only EMAs and no SMAs.

Have a look at the actual calculation of both simple and exponential moving average in another post of mine. Looking at the calculation in excel will improve your understanding of how they are calculated and move. 

What is Exponential Moving Average

Exponential Moving Average or EMA is not very different from the Simple Moving Average (SMA). Both are essentially used to smoothen a curve to get a better sense of the underlying trend.

How they are different is that the Ema is more sensitive than the Sma. Ema reacts and moves faster than the Sma in following the value of the data. How that actually happens is that in the calculation of Ema, recent events are given greater weight than earlier events and thus it tracks the data much more closely. The Sma on the other hand gives equal weight to all values, and thus is slightly more lagged.

Thursday, April 16, 2009

Calculate Dow Jones Moving Average in excel

Someone asked me how does one calculate a moving average of any stock or index. So here's an illustration of how to calculate moving averages in excel using dow jones data. Simply open the spreadsheet and follow the calculations, they are very simple to understand. This is the simple moving average.

You can download the excel spreadsheet from here.

I have also included charts in another sheet in the same file. It has a couple of all time historical chart for the dow in both log scale and normal scale. There's another chart of the 2007-09 recession and stock market crash along with a comparison with the Great Depression of 1929-32. The same charts also have the 200 day moving average plotted to show how the 200 dma is a key technical level.

Calculate Dow Jones RSI in Excel

RSI calculation done in excel spreadsheet to illustrate how rsi is calculated. Data is of Dow jones index, daily open, high, low, close since 1928, when the index started.

Its very simple. Just download excel sheet and go through the formulas. In case you dont understand anything just leave a comment and I will write a post explaining exactly how to do the calculation.