No. of Recommendations: 17
Thanks to all for looking at what I posted.
So many questions so
here is an example for Amazon with some of the Excel data that I used.
The results:
The summary values are for every three years.
Not every grouping was a winner, but survived the downturns well.
Next section has a slice of the calculations to see whether
or not to buy or sell.
In this case, a 6 month lookback was used counting up the last 7 months values.
(that will become more clear with the example below - I hope.)
Yes, every stock or ETF can be varied (from the 6/7 criteria) - this
one is for Amazon and was optimized by Excel's solver-
(
I did not do walk-forward testing or other concepts to minimize
any datamining- least I excite anyone...)
AMAZON-switch B&H AMAZON-sw B&H
value values period return
4/1/23 417.20 102.30 32% -17%
4/1/20 316.62 123.70 176% 167%
4/1/17 114.71 46.25 171% 204%
4/1/14 42.29 15.21 52% 55%
4/1/11 27.82 9.79 93% 149%
4/1/08 14.41 3.93 60% 143%
4/1/05 8.99 1.62 136% -57%
EOM 12/1/99 3.81 3.81
And for the monthly calculations:
A B C D E F G H I
4/1/22 168 122 124 0 1 0 3 0
3/1/22 171 134 163 0 1 0 2 0
2/1/22 164 138 154 0 0 1 1 1
1/1/22 171 135 150 0 1 1 1 1
12/1/21 178 165 167 0 0 1 0 1
11/1/21 188 164 175 0 0 2 0 1
10/1/21 174 159 169 0 0 2 0 1
9/1/21 177 164 164 0 0 0 2 0
High Low close NH? NL? Sum of NH
Sum of NL BUY /SELL for next month
Now, for calculation of values in col F1 and H1 - looking at the lows and whether to sell or not
Col A Date
Col B Stock High
Col c Stock Low
Col D Adjusted Close
Col E Is this a New High over the past 6 months, if so, cell get a +1, else 0
Note not any new highs, thus a string of zeros.
Col F Is this a New Low over the past 6 months, if so, cell get a +1, else 0
YES, 122 (C1) IS LESS THAN 134,138,135,165,164,159 SO THIS CELL (F1)GETS A "1"
col G Add up the previous 7 months counts of NH leaving a 0. In col G
Coil H Add up the previous 7 months counts of NL ( in col F)
1+1+0+0+1+0+0= 3 (H1)
Do the same for the Highs- to get values for col E and G
Since H1 has more NL counts, than the NHs G1, a sell indicator is issued "0" I1.
If new high count is >= new low count, BUY or hold FOR THE NEXT MONTH.
else sell FOR THE NEXT MONTH.
WHY, because all the numbers in col B,C,D are end of month.
You are calculating what to do next after the monthly data is in.
(I guess can try using weekly data if that works for you)
After typing all this up it sure looks complicated, but once it's coded up
in Excel, it's pretty straight forward.
Is this better than all the other previous bear catchers, I don't know and
with the nice weather coming up I will be on the golf course and not doing
any more calculations!
If you find a simpler method, let me know, but I was just trying to look at
and use new highs and new lows (incorporating capturing higher highs and
lower lows vs using moving averages or other of my
favorite indicators - CCI and MACD.)
It's nice to be back after getting a GIST (Google that one!) removed
and 44 radiation treatments for prostate cancer.
- tpoto
