Moving Averages - period selection help

[- Bobb -]

I'm a tinkerer in Excel - I use it, as well as Money, to track my finances. I'm trying to use a trendline as they do on CNBC when
they review a stock chart and "buy" "sell" signals. If someone else has done this - a simple question: how to have the moving avg
trendline " current - up to today". ?

I have a weekly chart in my worksheet which shows 52 weekly columns (from another sheets info) with 3 trendlines - 2wk, 4wk and 7
week moving average of the data and it works ok except that since they all start on Jan 1, of course the 7 week is "7 weeks behind
the current data" as is the 4 and 2. Yet I see on financial shows when they show moving averages, they are "up to date", not
trailing - that defeats the whole purpose, so: Now that I have a 52 week history, how to have the trendlines "apply to the PAST 2 ,
4 and 7 weeks rather than always trailing by that amount ?" Is it as simple as reversing the order for the series ( $BE$1: $i$1
etc)? mathematically , is that right in Excel ? I thought that I would, but I couldn't find a box to check to choose the order of
the moving average.

The formula I currently have for the series is:
=SERIES("Weekly Moving Average",'Portfolio'!$I$1:$BE$1,'Portfolio'!$I$26:$BE$26,1)

and for the averages I have 3 trendlines:
Type = moving average , period = 2 , 4 and 7 week . order = 2 (that's greyed out)

Thanks very much.
Bobb

 

[Jef Gorbach]

The point of moving averages, at least within the context of the financial
markets, is to smooth the data by establishing the average price over the
requested period as a method of determining whether the stock is currently
trading above or below its recent historical average price. Therefore the
signal ALWAYS trails by the requested period (ie: a 20MA is current as of 20
data-pts ago, a 52MA is current as of 52 data points ago, etc.
The common periods being 20 (1 financial month) and 200 (1 financial year).

That said, although most people use the comparison as-is (ie: generate a
Sell signal if current price drops below #ma), some do apply a trend line to
the moving average results to forecast where the average price -should- be
some point in the future provided the trend doesnt change; which is what i
think you may be considering.

Regardless, moving averages, while valid confiming indicators, should only
be one of several formulas used to generate Buy/Sell signals because of
their inherent whip-saw effect and significant time-delay in generating
signals; as you've noticed.

I'm a tinkerer in Excel - I use it, as well as Money, to track my

finances. I'm trying to use a trendline as they do on CNBC when

they review a stock chart and "buy" "sell" signals. If someone else has

done this - a simple question: how to have the moving avg

trendline " current - up to today". ?

I have a weekly chart in my worksheet which shows 52 weekly columns (from

another sheets info) with 3 trendlines - 2wk, 4wk and 7

week moving average of the data and it works ok except that since they all

start on Jan 1, of course the 7 week is "7 weeks behind

the current data" as is the 4 and 2. Yet I see on financial shows when

they show moving averages, they are "up to date", not

trailing - that defeats the whole purpose, so: Now that I have a 52 week

history, how to have the trendlines "apply to the PAST 2 ,

4 and 7 weeks rather than always trailing by that amount ?" Is it as

simple as reversing the order for the series ( $BE$1: $i$1

etc)? mathematically , is that right in Excel ? I thought that I would,

but I couldn't find a box to check to choose the order of

 

[- Bobb -]

Jeff,

I agree - and I am not buying or selling anything based on a max of 7 weeks info ( since that's as far as Excel will do ). I'm
really trying to learn to use Excel more / better. I also agree with "what the ma is supposed to show". But when they show charts
online or TV - the MA's are up to date - how is that ? I know that they've spent more than a few bucks on the software, but if you
go toYahoo and plug in a stock - DIS for example, and add the 20 day and 50 day moving avgs, the trend lines are not 20 and 50 days
behind today's date:
http://finance.yahoo.com/q/ta?s=DIS&t=1y&l=on&z=m&q=l&p=m20,m50&a=&c=
as they are with "my way of doing it". That's what I'm trying to see.

Bobb

 

[Robin Hammond]

Bobb,

What they are showing you for a given day's MAV is the average of the
preceding n days, where n is the period of the mav.

The problem is actually that using this definition, you can't get mav data
for your earliest data points, rather than the problem you are suggesting.

You should also make sure that you are only using trading days (i.e. not
carrying over values through the weekend).

e.g.

Stock Price for last ten days (this is in a bull market)
1
2
3
4
5
6
7
8
9
10

MAV5 for last ten days
na
na
na
na
3.0
4.0
5.0
6.0
7.0
8.0


Robin Hammond
www.enhanceddatasystems.com

 

[- Bobb -]

Robin,

First of all I want in on that stock you bought - great chart ! ( or did it go from 10 to 1 ?? )
After that, I agree that I'll lose the first data points (x weeks) input ( BTW these are WEEKS price points that I'm using). That's
fine. The trend lines on the far left can fade out - Ok with me.
I'd like the 3 trend lines to show : the x week's moving average from THIS week's data.

Here's what I have for chart/data: ( - I couldn't attach - get MS newsgroup error)
To get back to my original question, before I forget it ...
Can I simply reverse the variable order and get useful info ?

Bobb

 

[Robin Hammond]

Bobb,

Not sure I understand the question.

Whatever you look at it, the MAV is a lagging indicator based on past
periods. Brief elaboration on some of the ways it is used.

1. Show a single moving average.
Stock price breaking up/down through the MAV line implies gaining/losing
relative strength against recent trend. (Do not confuse relative strength
with Wheeler RSI which is a different indicator)
3. Show 2 moving averages, say 10 and 50 day.
10 day cuts up through 50 shows a positive signal and vice versa.
Stock above both implies positive trend
Stock above both but 10 cutting below 50 implies losing momentum
Stock falls below both - serious loss of momentum (but then again it might
be an anomally and buying opportunity).
Mostly you are looking for the 10/50 crossovers here as the signals.
4. MACD - much more serious and difficult to model, look at a text book.
Measures the Convergence/Divergence of moving averages. I've never found it
that useful myself.

Overall caution. Until you feel that you understand exactly how this works
and have read up a bit on the technical analysis signals, be careful about
using it as an investment indicator.

If you are really serious about getting into this, it is not that easy to
model but I find the RSI provides less frequent but better
overbought/oversold indicators. It tends to work better in thinner markets
though, so might not be too useful for NYSE big caps. And, one way or
another, the pros have bigger black boxes and this tends to reduce the
usability of the more commonly understood tech signals, particularly in
liquid markets.

General advice - find something that is supported by strong fundamentals,
valued correctly, where you have a chance of understanding the industry
dynamics, average in, and only use the techs for a little fine tuning on
your timing. Getting the sell timing right is the real trick!

HTH,
Robin Hammond
www.enhanceddatasystems.com