Testing for trading edges using excel

Dear forum,

There are a few general stock market tendencies that I would like to test in excel. For instance, what is the average 1-to-5 day return in the S&P 500 index following a 52 week high/low? Or what is the average 1-to-5 day return when the VIX reaches extremes in either direction? Or what is the average 1-to-5 day return following short term price strength/weakness? Basically, I would like to test some of the ideas I have read about to see if they have provided an historical edge before I move towards developing strategies around these ideas.

I am in the process of acquiring OHLC historical data and would like to import to excel to perform this sort of analysis. Does someone know of any online sources that will help me perform this sort of analysis once I have the data?

Thanks in advance!

I don't know of any online resource that "teaches" you how to do stock market analysis using excel. It's basic Excel knowledge applied to spreadsheet with OHLC data.

Build your formulae from the columns to the right of the data. Instead of making one big formula in a cell break it up into multiple cells.

Ask here where you have your next question and I'll help you. I've done tons of what you're about to do.

Thanks for the reply. Well, the first round of tests is complete where I tested 1-5 day returns after the market as measured by the S&P 500 reached 5 day highs and lows over a 15 year period. The conclusion is pretty clear - the index tends to reach areas of being overbought and oversold and a potential edge exists to develop a strategy around this tendency with the longer term trend.

The next test I'd like to run is to look at 1-5 day returns after the index makes multiple day higher highs/lower lows. Howevever, I'm having problems with the formulas necessary in excel to mark 3 consecutive days of higher highs or lower lower lows. I would greatly appreciate it if you could provide some guidance.

I think figured out the excel formula I was having problems with. I have the high of the day in Column C. To figure out if today's high was the 3rd higher high of the last 3 days, I would use this formula:
=IF(AND(C5>C4,C4>C3,C3>C2),1,"") where the 1 would mark instances where this today's high is the 3rd and highest in 3 consecutive days of higher highs. Please let me know if this is how you would have gone about it.

quote:

---

Originally posted by whatwhat

...
=IF(AND(C5>C4,C4>C3,C3>C2),1,"")
...

---

Yes, that's exactly how I would do it. Also, depending on the strategy that you're checking, you may only be interested in the 3rd highest high and not the forth, fifth etc.

In that case you can put:

=IF(AND(AND(C5>C4,C4>C3,C3>C2),AND(C5>C4,C4>C3,C3>C2,C2<=C1)),1,"")

This would then only return 1 if only the 3 most recent were higher highs and not if it was the forth highest high.

Here's the spreadsheet with the formula: