Augen, Microsoft Excel for Stock and Option Traders

Jeff Augen’s books are always challenging, as he intends them to be. Microsoft Excel for Stock and Option Traders: Build Your Own Analytical Tools for Higher Returns (FT Press, 2011) is no exception. Admittedly, my own skill levels are modest: I’m reasonably comfortable with standard Excel functions but am regularly foiled by VBA, despite the author’s claim that it is relatively easy to learn. Is it worth the effort to keep building databases and pounding away at VBA?

Augen would answer with a resounding yes: “investors who limit themselves to traditional off-the-shelf indicators will always lose money to sophisticated traders armed with more powerful tools. The days of buying and selling stocks when moving averages cross or when an oscillator reaches one side of a channel are over.” (p. 58) Moreover, he argues, “the capability gap between private and institutional investors increases as the trading time frame decreases.” Charting patterns designed around the behavior of human investors “have little relevance in a time frame that has come to be dominated by high-speed algorithmic trading.” (p. 156)

Although Augen hasn’t abandoned short-term trading (for instance, he illustrates the often complex but sometimes revelatory relationship between implied volatility and stock price on a 5-minute AAPL chart as well as on daily index charts), most of the data referenced in this book are end-of-day.

Ideally, the trader trying to gain a statistical edge has both a database program such as Access and a spreadsheet program such as Excel. But with the dramatic increase in the capacity of Excel (Excel 2010 worksheets can contain over 1,000,000 rows and 16,384 columns) many traders can get by, at least initially, with Excel alone.

Augen’s book presupposes a working knowledge of Excel. In the chapter entitled “The Basics” he shows how to manage date formats, perform volatility calculations, create ratios that simulate candlestick bars, construct summary tables with VBA, unearth statistical correlations, and draw polynomial trendlines on Excel charts.

In the chapter on advanced topics Augen explains in some detail how to develop and test hypotheses—for instance, whether sharp downward corrections in AMZN are followed by a relatively strong rally. For those who are inexperienced in backtesting with Excel, this chapter is exceedingly useful. It illustrates how to go about quantifying such qualitative terms as “sharp” and “relatively strong,” how to build complex statements from the inside out, and how to automate the process. It will save the would-be backtester countless hours of frustration. Even the column descriptions for a sample experiment should make this clear.

(click to enlarge)

Unfortunately, as far as I can ascertain, there is no accompanying web site where the reader can grab the Excel and VBA coding printed in the book. The reader who wants to crib some of Augen’s work will have to retype—very carefully.