Preface to Microsoft Excel for Stock and Option Traders: Build Your Own Analytical Tools for Higher Returns
This chapter is from the book
This chapter is from the book
This chapter is from the book
Preface
In August 2010, Cisco stock (ticker: CSCO) hovered just a few cents below $25. Several analysts identified the stock as a strong buy. They pointed to the rising demand for network infrastructure that, among other things, was being driven by explosive growth in online video gaming and Internet television. Cisco, they believed, would continue to dominate the consumer market while benefiting from a weak dollar and low manufacturing costs. They must have been wrong because the stock fell 15% when earnings were released on August 11. The price continued to decline until August 31, when it bottomed out at $19—24% below its previous high. About the time that everyone had given up and turned bearish, the stock began to rally. On November 10 the price was, once again, back up to $24.50. Then came another earnings report and another sharp decline. The price immediately fell 16% and continued plunging until, on December 3, it once again bottomed out at $19. These bizarre dynamics played out a third time, with the stock rallying steadily to $22 on February 9, 2011, before falling back to $18.92 the very next day after earnings were released—another 14% decline. Figure P.1 displays Cisco closing prices from June 1, 2010, to February 11, 2011.
)
Figure P.1 CSCO closing prices June 1, 2010 to February 11, 2011.
Wild unpredictability doesn't seem to discourage speculators because the trading volume for Cisco remains shockingly high. Moreover, the number of investors who bet on the direction of the stock seems to peak just before and after earnings—the most dangerous times of all. For example, the trading volume climbed above 125 million shares on February 9, 2011 (before earnings), and skyrocketed to 560 million shares the next day after the numbers were released. Each of the previously outlined events was accompanied by a similar pattern of extremely high volume the day before earnings were announced and even higher volume the day after.
Who would trade this completely unpredictable stock? Why does the volume soar at the most dangerous times when anything seems possible? More importantly, why do analysts believe that they know enough to make predictions? The answers are simple. Analysts can make all the predictions they want because it's not their money that ends up being lost, and speculators always believe they can find a bargain. As a group, investors tend to be arrogant. They typically believe that they have unique insights and that these insights give them an advantage over the market. One of the most common mistakes is relying on traditional off-the-shelf technical indicators that often prove to be even less reliable than fundamental analysis. The Cisco story represents one of the best examples of the problem.
Various technical indicators signaled that the stock would continue to rally just before each of the sharp declines displayed in Figure P.1. They were clearly wrong. Moreover, technical indicators cannot be valid if the underlying trend being analyzed is statistically insignificant. Yet technical analysts routinely talk about moving-average crosses, momentum, or relative strength, without any reference to the statistical strength of the underlying trend being studied. We can compile the relevant statistics for any stock in just a few seconds by loading the information into a spreadsheet and applying Excel's r-squared function. Not surprisingly, the test reveals that most trends appearing on stock charts have very low statistical significance. For Cisco, a relatively weak r-squared value of 0.7 is achieved less than 30% of the time using a 10-day sliding window. Highly significant trends with r-squared values above 0.9 occur with a frequency less than 5%. Table P.1 displays r-squared data for 2 years of Cisco stock.
Table P.1. Compiled r-squared values for Cisco stock February 2009 to February 2011. Calculations span a 10-day sliding window.
|
rsq>.9 |
rsq>.8 |
rsq>.7 |
rsq>.6 |
rsq>.5 |
|
|
Days |
24 |
96 |
146 |
189 |
238 |
|
Percent |
4.8% |
19.4% |
29.5% |
38.2% |
48.1% |
The table is divided into columns that reveal the number and percentage of days appearing in trends with minimum r-squared values listed in the column headings. In some sense the data represents a dose of reality. It is common, for example, to hear a technical analyst turn bullish because the 50-day moving average has crossed above the 200-day moving average. However, it is unlikely that you will ever hear the same analyst report the r-squared value of the current trendline. Fortunately, however, most good trading platforms have an r-squared function that can display on a chart, and the data can be exported to a spreadsheet where more detailed analysis can be used to study different length windows and combinations of indicators. This kind of analysis can be used to validate, invalidate, or tune combinations of indicators and give investors an edge against the market. In today's complex computer-driven markets, this kind of analysis can make the difference between winning and losing.
Modern trading platforms always include sophisticated tools for back-testing indicators and strategies. But before a strategy can be tested, it must first be developed, and that development is best accomplished on a foundation of statistical analysis. Spreadsheets and databases are the perfect platform for that kind of technical work. In most cases the process involves a sequence of basic questions designed to reveal the statistical behavior of a stock following a specific set of conditions. There is virtually no limit to the size, number, or complexity of the experiments that can be performed to search for unique correlations that are not generally known to the market.
This book is designed to help technically minded private investors learn to run just a little faster than the market. A few years ago the discussion would have been too complex to be generally useful because it would have been focused on data mining strategies in large databases. That has all changed. Most of the complex statistical analysis and model building that a few years ago could only be accomplished at the institutional level is now within the reach of any investor with a trading platform and a copy of Microsoft Excel. This book is built on that theme. It is designed to help investors learn to translate complex questions into simple spreadsheet models. The discussions span a range from simple conditionals and logical expressions to relatively complex VBA programs that generate statistical summary tables. My goal was to include content that can add value to the efforts of a wide range of investors and to challenge everyone to improve their analytical capabilities.