Moving Averages
topic suggested by Kaihong T.

Here's a spreadsheet that may (or may not) be useful.
Suppose you're looking at some stock and want to buy when the 10-day moving average crosses the 100-day moving average and ...

>But it can cross from above or below and why do you choose 10 and ... ?
Wait until I finish! Okay, we subscribe to the strategy Buy Low and Sell High.
But Low compared to what?
And High compared to what?
Perhaps it should be Low compared to some slow moving average (that's, say, 100-day) and
High compared to some fast moving average.
To this end we buy when the 10-day average crosses the 100-day average from above (meaning the price is just dropped to a Low) and sell when ...

>Yeah, yeah. Sell when it crosses from below, but what about the numbers 10 and 100 and ... ?
That's where the spreadsheet comes in:
You pick a stock, download the daily stock prices and pick your own numbers for the moving averages.
The spreadsheet will tell you whether you done good.    

I should point out how the spreadsheet works:

  • You come home from work and calculate the moving averages.
  • You use the closing prices for the past umpteen days, including "today".
  • If there's a buy or sell signal, you buy or sell at tomorrow's opening price.

It will look something like this (with IBM as an example as dictated by Chet K :^)

To download a .ZIPd file, RIGHT-click on the picture above and Save Target.

>What's that "1" in the upper right corner?
Just in case you want to Buy when the faster average (that's #2) crosses the slower average (that's #1) from below and sell ...

>Why would you do that?
Surprisingly, it's sometimes the better strategy!

Anyway, if you choose "1" you get one strategy and if you choose"-1" you get the other.

>What's that Maximize button?

Uh ... well, if you're willing to wait for it, you can run through a bunch of moving averages and pick out that one which gives the maximum Total Gain.

When the spreadsheet is finished, there'll be an explanation ... something like this

>Willing to wait for it? What does that mean?
Just go for a coffee while it does the number crunching ...

>And how good are these Buy and Sell signals?
How many guesses do I get?

Figure 1

Actually, for IBM stock (from Dec/98 to Jan/03, where the stock gained about 7% over this time period), if we start with half our money in cash and half in stock and choose various long moving averages (#1) and short moving averages (#2), we'd get Figure 1 for our portfolio gain.

>For a 160-day long average and an 80-day short, we'd have made a 200% gain?
You're looking at the point with the black dot? That's 174% over about 4 years ... or about 28% annualized return.

>So that's what I'd use, right? I mean 160-day and 80-day, right?
Sure ... and pray the future is like the past. You might want to borrow this.

>Yeah. Very funny. But somebuddy told me to buy when the stock price falls below the 100-day moving average and ...

... and move to cash when the price goes above the 100-day average?
That's one of those Sell when the price is high, Buy when it's low, eh?

Check out Figure 2 where we start with $100K invested in the S&P 500 and move in and out of cash when the index crosses the 100-day average. You can see that ...

>We more than double our annual return, from 2.8% to 6.8%!

For the full six years, yes.
But do you see our portfolio after the first 3 1/2 years?
We'd have about $190K had we just kept our money in the S&P but only $170K with this buy/sell strategy.

Figure 2

>But the risk is less, eh? I mean, if you do the Buy&Sell thing, the risk ...
Okay, for this example, the volatility is reduced by about 1/3.

>But that means the risk is less, right?
Are you equating risk with volatility? Shame on you! I suggest you put your money under your pillow. That'll give you zero volatility.

>Maybe I'll just borrow your crystal ball.
Be my guest.

>And you guarantee the accuracy of the spreadsheet?
Of course. I always offer a money-back guarantee.  

There's also a spreadsheet which looks like this ... just in case you want to enter your own daily returns and have the spreadsheet run through a bunch of moving averages, picking out the Best:

Again, just do the RIGHT-click / Save Target thing ... with the picture ...


Since I wrote the first spreadsheet described above sometime in the last century? the Yahoo! data that's downloaded has changed to include an Adj* Close which takes care of dividends and stock splits so ...

>So you changed the spreadsheet, right?
Well, yes. After getting e-mail from Mike D. I've changed it correcting an error or three ... and now use that Adjusted Close and now include the option of using
Exponential Moving Averages

To download the "new" version, just RIGHT-click here and Save Target.

P.S.   There's an "Explain" sheet which looks like this.