Exponential Moving Average
Question:
I used the Data Analysis pak and found the syntax below for moving average.
=AVERAGE(H22:H31)
Can someone tell me what the syntax for an exponential moving average is? I am enclosing the "blurb" of Exponential Moving Average from MetaStock, could you take a look at
it, and tell me the formula for what I want.
A1 1
A2 2
A3 3
A4 4
A5 5
Average(Exp(A1:A5))
Gave a number way off of 46.64
An exponential (or exponentially weighted) moving average is calculated by applying a percentage of
today's closing price to yesterday's moving average value.
For example, to calculate a 9% exponential moving average of IBM: First, we would take today's
closing price and multiply it by 9%. We would then add this product to the value of yesterday's
moving average multiplied by 91% (100% - 9% = 91%).
Because most investors feel more comfortable working with time periods rather than with percentages,
MetaStock converts days into an exponential percentage. For example, if a 21-day exponential moving
average is requested, a 9% moving average is calculated.
The formula for converting days to exponential percentages is as follows:
For example, to calculate a 10-day exponential moving average, you would use 0.18:
To convert an exponential percentage into time periods, you would use the following formula:
Using our previous example, we can check to see that a 0.18 exponential moving average is actually a
10-day average.
The method used to calculate an exponential moving average puts more weight toward recent data and
less weight toward past data than does the simple moving average method. This method is often
called exponentially weighted.
Answer:
Implementing this is difficult because?
Use a helper column with the weighted averages, and it is
straightforward. I would tend to calculate it using the equvalent formula
a' = a + 9%*(x'-a)
where a is yesterday's exponential moving average, x' is today's closing
price, and a' is today's exponential moving average. The expnential
moving average moves in the direction of today's close, but that change
is discounted.
To calculate without the helper column, you would do something like
=SUMPRODUCT(0.09*A$1:A12*0.91^(ROW(A12)-ROW(A$1:A12)))
+0.91*A$1*0.91^(ROW(A12)-ROW(A$1))
to get the exponetial moving average of A1:A12. With enough data, the
add-on at the end will vanish.
Submit Your Comments and Answers
