|
PureBytes Links
Trading Reference Links
|
Another issue you will face is to get an exact match, you must be using the same number of data points in both calculations.
From: mgf_za_1999 <no_reply@xxxxxxxxxxxxxxx>
Reply-To: equismetastock@xxxxxxxxxxxxxxx
To: equismetastock@xxxxxxxxxxxxxxx
Subject: [EquisMetaStock Group] Re: Can't duplicate Metastock EMA in Excel, any help appreciated.
Date: Fri, 30 Sep 2005 04:23:35 -0000
Hi there,
You need to change your formula a bit, you take the weighted average
of a simple moving average and the most recent value, which is not an
exponential moving average.
> =(A5*0.3333)+((SUM(A1:A4)/4)*(1-0.3333))
Rather try something like
B1: =A1
B2: =A2*0.3333+B1*(1-0.3333)
B3: =A3*0.3333+B2*(1-0.3333)
and so on (you can just copy it down) to get to the final one, which
should be similar. To get exactly the same answer, don't use 0.3333 in
the formula, which is a rounded figure, but calculate the weight
in
say C1 and use a reference to that, ie your formula will become
B2: =A2*C$1+B1*(1-C$1)
and so on.
Regards
MG Ferreira
TsaTsa EOD Programmer and trading model builder
http://www.ferra4models.com
http://fun.ferra4models.com
--- In equismetastock@xxxxxxxxxxxxxxx, clymbore <no_reply@xxxx> wrote:
> I can't seem to duplicate Metastocks Exponential MA in Excel even
> though I think I am following the Metastock formula
>
>
>
> In metastock I get a 5 period exponential moving average of
> 26.744 using "Mov(C,5,E)"for the following closing prices
> 27.34,27.11,26.78, 26.76,26.44
>
>
> To try to duplicate that value in Excel I followed the Metastock
> Calculation given in the
Metastock Help file. ("Moving Average
> Calculation Methods">Exponential)
>
> "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%)
> m.a. = [(today's close) x 0.09] + [(yesterday's sma) x 0.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:
> Exponential percentage = 2/(time periods)+1"
>
> To duplicate that, I put
> 27.34
> 27.11
> 26.78
> 26.76
> 26.44
> (In Cells A1-A5)
> Metastock Percentage formula should be
> =2/(5+1)
> Which equals
> .33333
>
> And
> =(A5*0.3333)+((SUM(A1:A4)/4)*(1-0.3333))
> To get the 5 day exponential moving average of
> 26.81169
>
> Which does not match.
>
>
> * At the beginning of a chart, the first time Metastock shows a five
> day EMA is on the 5th bar. The explanation says to take
> "yesterdays
> moving average" that is why I am using a 4 day sma in the
> calculation.
> I am guessing that it
would have to be that way. I have tried it a
> bunch of ways and nothing gets the same value
>
> (To make sure I am using the right data I checked to make sure
> MOV(c,5,s) in metastock equals =SUM(A1:A5)/5 and both equal 26.886 )
>
> Thanks for any ideas
SPONSORED LINKS
YAHOO! GROUPS LINKS
SPONSORED LINKS
YAHOO! GROUPS LINKS
|