Microsoft Excel for Stock and Option Traders: Build Your Own Analytical Tools for Higher Returns

by Jeff Augen

Quick Summary

A practical guide to building sophisticated stock and options analysis tools using Microsoft Excel, covering volatility calculation methods, price-change distribution analysis, options pricing mechanics, and custom spreadsheet construction. Augen teaches traders to create their own analytical infrastructure rather than relying on third-party tools, enabling deeper understanding and customized analysis.

Categories

• Options
• Quantitative Trading
• Trading Tools
• Volatility Analysis

Detailed Summary

"Microsoft Excel for Stock and Option Traders: Build Your Own Analytical Tools for Higher Returns" by Jeff Augen, published in 2011 by FT Press (Pearson Education), provides a hands-on guide to constructing quantitative analysis tools for stock and options trading using Microsoft Excel. Augen, a former IBM executive turned options trading author, approaches the subject with the perspective of a technologist who understands both the mathematical foundations and the practical implementation challenges.

The book opens with "The Value of Information," establishing the thesis that proprietary analytical tools provide a competitive advantage because they allow traders to ask questions and analyze data in ways that commercial software does not support. Augen argues that dependence on off-the-shelf tools limits a trader's ability to discover unique insights, while custom-built Excel tools enable exploration and analysis tailored to specific trading approaches.

A major focus is volatility analysis, which Augen considers the most important analytical dimension for options traders. The book covers multiple methods for calculating historical volatility, including close-to-close volatility (the standard method using natural logarithms of daily price ratios), intraday high-low volatility (using daily trading ranges), and hybrid approaches. Augen demonstrates how to implement each calculation in Excel, with specific formulas provided: for close-to-close volatility using the natural log function LN(C/D) measured over a 20-day rolling window, and for intraday volatility using LN(High/Low) calculations.

Augen presents a particularly valuable insight about comparing different volatility measures: when intraday volatility significantly exceeds close-to-close volatility, it indicates that large price swings occur during the trading session but tend to reverse before the close, identifying stocks that are "excellent day trading candidates" and potentially profitable long straddle candidates. Conversely, when intraday volatility is lower, the largest price changes occur in pre- or after-market sessions, and options tend to be more fairly priced.

The construction of normalized price-change analysis tools is covered, showing how to express price changes in standard deviations against recent volatility windows. This normalization allows meaningful comparison across different securities regardless of their price levels or volatility characteristics. Augen explains the importance of measuring each price change against a volatility window that ends just before the change to avoid self-referential distortion.

Data management sections cover the practical challenges of working with financial data in Excel, including date alignment across different securities, handling of stock splits and dividends, removal of unmatched records, and efficient organization of large datasets. Augen provides specific techniques for automating data import, cleaning, and alignment processes.

The options-specific sections cover implied volatility analysis, the construction of volatility surfaces, the comparison of implied versus historical volatility, and the identification of mispriced options. Augen demonstrates how to build pricing models in Excel and use them to evaluate specific trading opportunities, including calendar spreads, straddles, and directional option positions.

The YEARFRAC function and other Excel date-handling functions are discussed in the context of calculating time to expiration for options pricing. Augen addresses the practical nuances of options time calculations, including the treatment of weekends and holidays.

The book includes VBA (Visual Basic for Applications) programming sections for readers who want to automate their analytical workflows, covering the declaration of variables (DataRow, IterationIndex), loop structures, and the creation of custom functions. This programming material extends the book's utility beyond simple spreadsheet construction into genuine tool development.

Augen's dedication to his wife Lisa, "who changed everything when she said: 'Why don't you just calculate the integral between those two points and chart the value as it changes over time?'" captures the book's spirit: the marriage of mathematical insight with practical implementation tools that enable individual traders to perform institutional-quality analysis.