Exploring Historical Market Patterns in Excel

A major topic of the Daily Trading Coach book--the entire Chapter 10 is devoted to it--is using historical analysis with Excel to develop trading hypotheses.

If you look at my posts on using three-minute moves in TICK to anticipate the distribution of moves over the next three minutes and what happens after large upside opening gaps, you'll see a couple of examples of the kinds of explorations I conduct in Excel.

In Chapter 10 of the Daily Trading Coach book, I describe how to put together spreadsheets for exploration. Now, in his kind review of the book, Rob Hanna of Quantitative Edges has linked to an actual spreadsheet that he assembled from the book's chapter. The spreadsheet download is free; more will be coming from his download site. In fact, if there's interest, I will post a historical study to this blog and make the spreadsheet that I used available through Rob's site.

To my knowledge, the chapter in the new book is the only one I've seen that outlines the process of exploring historical market data for hypotheses using nothing more than data from your real time feed and Excel. Note that I emphasize that the purpose of the explorations is to generate hypotheses, not conclusions. We're using the data to see if there is a historical edge to a trading pattern, not to get locked into a fixed view of markets. Indeed, some very valuable trading information comes from occasions when the market is NOT following its usual historical path.

Drop a comment to this post if this is a topic you'd like to see discussed further, with spreadsheet examples from Rob's site. I find the historical looks a valuable part of preparation for the market day.
.