topic suggested by JanSz, with the help of KevinL
Here's a spreadsheet that may (or may not) be useful (and continues a discussion that started
here).
We're interested in switching between sectors like Financials, Healthcare, Biotech, Retail, ...
>Yeah, so?
So here's a strategy that we might try:
 We assume the prices of a particular sector, for the past N weeks, are
p_{1}, p_{2}, p_{3}, ... p_{N}.
 We calculate, for this sector, the Weighted Moving Average of the most recent N prices:
WMA(N) = [p_{1} + 2p_{2} + 3p_{3} + 4p_{4} + ... + Np_{N}] / [ N(N+1)/2 ]
where p_{N} is this week's closing price and p_{1} is the price N1 weeks ago
... so the more recent prices are weighted more heavily (by virtue of those numbers out front, like 1, 2, 3, ... N).
 Next week we have**:
WMA(N+1) = [p_{2} + 2p_{3} + 3p_{4} + 4p_{5} + ... + Np_{N+1}] / [ N(N+1)/2 ]
where (as before) p_{N+1} is the closing price for the week and p_{2} is the price N1 weeks earlier.
 We're interested in that sector which has the greatest Rate of Change in WMA, so we calculate:
WMA(N+1)  WMA(N) = [ N p_{N+1}  (p_{1} + p_{2} + ... + p_{N}) ] / [ N(N+1)/2 ]
which can be rewritten:
WMA(N+1)  WMA(N) = [ (N+1) p_{N+1}  (p_{1} + p_{2} + ... + p_{N} + p_{N+1}) ] / (N+1) / [ N/2 ]
which can be rewritten:
(N/2)[ WMA(N+1)  WMA(N) ] = p_{N+1} [1/(N+1)] (p_{1} + p_{2} + ... + p_{N} + p_{N+1})
 We recognize [1/(N+1)] (p_{1} + p_{2} + ... + p_{N} + p_{N+1})
as the ordinary, garden variety Moving Average
(which we'll call MA). Hence we see that the Rate of Change in WMA (at week N+1) is proportional to:
gMA(N+1) = p_{N+1}  MA(N+1)
or, equivalently:
** The [N(N+1)/2] is to ensure that, should all prices be the
same, the WMA would equal this common price. But this ain't important.
What we do is pick a Favourite sector and switch to another sector only if the gMA of that
other sector is greater than our Favorite gMA by at least some factor (in cell J4).
Because, as it turns out, our gMA is just the deviation of the current price from the Moving Average MA, we
want to select that sector with the largest deviation. The one with the largest price, compared to its MA.
>Yeah, so?
So here's a DRAFT spreadsheet which plays this game:
RIGHTclick on the picture and Save Target to download. (It's a BIG .ZIPd file!)
>Wow! Look at the Portfolio gains. How did you ...?
Well, hold on. We fiddled a bit. See the
Math.
>No thanks. You said draft?
Yes ... but I'd like feedback from anybuddy who plays with it (so I can make any necessary changes).
>Changes ... or correct your errors?
Uh ... well, yes.
The Explain sheet looks something like
this.
>There's a Magic Number in cell I1. What ...?
What's that?
Well, there are (at least) two strategies:
[1] Choose the sector which has the largest gMA, meaning the sector
whose price is farthest above the Moving Average. That's assuming there's a upward trend with that sector and
we want to be in on that trend. Indeed, since the gMA is the rate of increase of that Weighted Moving Average,
we want the sector with the largest Rate of Increase.
[1] Choose the sector whose price is farthest below its Moving Average.
That's consistent with Buy Low. We anticipate that we'll being buying a bargain.
You can see the result of either strategy, by choosing either 1
or 1 in cell I1.
>And which do you use?
Neither. I just like to play in my gummy_stuff sandbox
However, before you play with the spreadsheet, you really should look at the
Math, because, although our original intention was to
enable these two strategies, we got something different !
Indeed, even for the 1 case, the spreadsheet always
looks at the sector with the largest gMA and compares to your choice of Favourite. Interesting, eh?
>No, and besides. You're looking at the past thinking the future is a replica and ...
Yes, you're right. But it's fun to pretend, like this:
>And beyond 2003? What about that future?
Use this.
See also an Explanation.
