Completed Excel Program Request

[Carlton Patterson]

Hello All,

First I want to start by thanking all of you guys that have assisted me
in the past. You have greatly reduced the burden on trading the stock
market.

I have another request which I’m not sure if anyone will be able to
help. I have outlined exactly what I’m after below.

I would like to point out that although the excel program I’m seeking
help will have its final use in trading stocks you really don’t need to
be an expert in the stock market to appreciate what I’m attempting to
achieve

I have outlined as much detail about my request as possible but I
understand further clarification may be needed so please don’t hesitate
to ask me any questions.


Triple Exponential Smoothing

TRIX is a momentum indicator that displays the percent rate-of-change of
a triple exponentially smoothed moving average of a security's closing
price. It was developed in the early 1980's by Jack Hutson, an editor
for Technical Analysis of Stocks and Commodities magazine. Oscillating
around a zero line, TRIX is designed to filter out stock movements that
are insignificant to the larger trend of the stock. The user selects a
number of periods (such as 3) with which to create the moving average,
and those cycles that are shorter than that period are filtered out.

Now, for those of you that are going to help me you really don’t need
to understand some of the technically babble such as a ‘exponential’,
‘triple’, ‘smoothed’, etc… They are just terms used by traders.

Anyway, the following describes the excel program I would like. It’s an
example of a triple smoothing of a straight 3-day moving average:

P = (P1, P2,.... P3 )

The single moving average at day n is

Mn = (Pn-2 + Pn-1 + Pn)/3

Smoothing the new series M gives

Mn’ = (Mn-2 + Mn-1 + Mn)/3

= (Pn-4 + 2Pn-3 + 3Pn-2 + 2Pn-1 + Pn)/9

and the third smoothing gives

Mn” = (Mn’-2 + Mn’-1 + Mn’)/3

= (Pn-6 +3Pn-5 + 6Pn-4 + 7Pn-3 + 6Pn-2 + 3Pn-1 + Pn) / 27


So, that’s it. If someone can help I would truly appreciate it. I admit
it’s a completed request, however I have looked at other peoples request
and I’m astonished how some of you guys are able to find a solution.

I will do anything to help achieve this goal, so if you think you may be
able to compile a program but need further information don’t hesitate to
ask me questions.

In a nutshell:

To calculate TRIX, you must first pick a period with which to create an
exponential moving average of the closing prices. For a 15-day period:
1) Calculate the 15-day exponential moving average of the closing price
2) Calculate the 15-day exponential moving average of the moving average
calculated in step #1
3) Calculate the 15-day exponential moving average of the moving average
calculated in step #2

You now have triple exponentially smoothed the moving average of closing
prices, greatly reducing volatility.

4) Finally, calculate the 1-day percent change of the moving average
calculated in step #3

Finally, I want to thank all in advance to those that attempt to help
me.

Cheers

Carlton

P.S.

Its 2:00am here in England so if I don’t respond to any questions
immediately its because I’m off to bed. Chat with you in the morning.

 

[Paul Martin]

Hi Carlton

I have an example of double-exponential smoothing at home if that's of
any interest to you. It shouldn't be too difficult to extrapolate from
the structure of this to add the complexity of the third smoothing.
Let me know if you're interested, and I'll send you a copy.

BTW, you might get a better response to a question like this in the
microsoft.public.excel.misc newsgroup.

Regards

Paul Martin
Melbourne, Australia

 

[Carlton Patterson]

Hi Paul,

Yes, I would very much appreciate it if you could send the
double-exponential smoothing to my email address.

(e-mail address removed)

Cheers mate.

Carlton