Calculating a 200 day EMA in Excel

A

ANCHOR

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?
 
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.
 
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.
 
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?
 

Attachments

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.
 
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
 
richtrader,

I have also calculated the EMA with the formula that you use and it is the same. I think now I'm going to look at my charting software to see if it is correct.

Thanks.
 
You must log in or register to reply here.
Back
Top