Custom Search

Another way to use a spreadsheet is to determine the probabilities of a price pattern before you even design specific rules for trading it.

Figure 5 (below) shows an excerpt from a spreadsheet that shows several statistics - the average price move, median price move, the maximum price move and the minimum price move - in the three days following the completion of a price pattern.

To perform this kind of analysis, you must first import the open-high-low-close price data for the period you wish to test (the "open" column is hidden here). The period here spans May 31, 2000, to Dec. 13, 2000; many of the rows are hidden to conserve space.

Column F contains the pattern's conditions. In Excel, it is easy to string together several "If" conditions that describe a pattern. In this case, the pattern is a bar with three conditions: 1. The high must be at least one percent above the previous high;
2. The close must be below the previous close;
3. The close must be in the bottom 10 percent of the price bar.

These conditions are shown in the formula bar (for cell F22) as:
1. (C22-C21)/C21>0.01
2. E22 3. (E22-D22)/(C22-D22)<0.10

Each condition is preceded by the "IF" function, and the "1,0" at the end of the argument indicates that if all the conditions are true, " 1 " will be entered in the appropriate cell in the F column; if even one of the conditions is false, "0" will be entered. By dragging this formula to fill all the cells in column F, each bar that fulfills the pattern criteria will be flagged with a 1. (Alternately, if you have programmed the pattern conditions into your analysis software, you will be able to automatically include this information when you offload the price data to the spreadsheet.)

Columns G-0 contain the close-to-close price moves, MAE and MFE values for the three days after each pattern occurrence. Dragging the formulas makes calculating the numbers for the sheet a very simple process. This analysis could be carried out for as many days as you wish. Also shown is the total number of patterns that occurred.

For example, row 22 holds the data for the Oct. 24, 2000, pattern and shows the market dropped 1.89 percent the day after the pattern (day 1), had an MFE of .69 percent and an MAE of -2.40 percent. By the close of day 2, however, the market had risen 1.57 percent, and so on.

The summary statistics at the bottom allow you to judge the probabilities of the raw pattern signal. These are only examples of the kinds of statistics you could include. Others are the percentage of positive and negative returns at each time interval, a separate breakdown of positive returns and negative returns, and so on.

If a pattern's probabilities are favorable, you can then proceed to developing and testing trading rules to maximize the pattern's potential.