ET News & Sponsor Info
General Topics
Markets
Technical Topics
Brokerage Firms
Company Specific
Tools of the Trade
Trading for a Living
Community Lounge
Site Support

# Calculating a 200 day EMA in Excel

Discussion in 'Technical Analysis' started by ANCHOR, Jun 20, 2003.

1. Does anyone know the formula to calculate a 200 day EMA in an Excel spreadsheet?

I know the formula for an exponential moving average is:

X = (K x (C - P)) + P

X = Current EMA
C = Current Price
P = Previous period's EMA*
K = Smoothing constant
(*A SMA is used for first period's calculation)

and the smoothing constant is 2/(N+1) with "N" being the number of days in the average.

This formula works for all of my other EMA's (5,10,20,50...) but it does not work for the 200. That is that the EMA's price in Excel using this formula does not match the EMA's price displayed on my charting software or the EMA displayed in a Java chart on Prophet.net. Why is this calculation not working for the 200 day EMA?

Any help?

2. Are you starting with the proper seed value? Or do you have at least 200 values?

Post the spreadsheet and I or someone else can fix it.

3. Yes, I have 3 years of data in my current spreadsheets. I start off the data with a 200 day SMA. All of my other EMA's work fine but not the 200 day EMA. It is off by more than dollar. I am not sure if there is a different formula I can use for 200 day EMA or not.

4. Okay here is a simple spreadsheet with prices and a 200 EMA. It shows the current 200 day EMA price as 25.23 but my charting software shows the current 200 day EMA price at 24.23. A one dollar difference. Can you tell me what I doing wrong here?

• ###### 200 ema.xls
File size:
81 KB
Views:
514
5. Alright...

Thanks for uploading the spreadsheet. I calculated the 200 EMA using a slightly different formula:

(Today's Close * percent) + (Yesterday's Moving Average * (1 - percent))

(where percent is equal to your smoothing factor). I came up with the exact same numbers as you did.

Further, I went to BigCharts.com and compared the values to the values on their chart and they seemed right. The weird thing is that when I went to shorter timeframes at bigcharts than six months, the values were off. I'm thinking the apps you are comparing against do not calculate on history, but rather just on the data that's displayed.

I'm also thinking your formula is working.

6. I am using e-signal for data feed and my 200 SMA on April 9 2002 is 37.65 and 200 EMA is 40.24
so change your begining value to 40.24 and you'll be fine
your formula is good