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

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

Language: English

Pages: 208

ISBN: 0137131828

Format: PDF / Kindle (mobi) / ePub

Trade more profitably by exploiting Microsoft Excel’s powerful statistical and data mining tools:


·    Uncover subtle anomalies and distortions that signal profit opportunities

·    Create powerful new custom indicators, alerts, and trading models

·    Visualize and analyze huge amounts of trading data with just a few clicks

·    Powerful techniques for every active investor who can use Excel


Now that high-speed traders dominate the market, yesterday’s slower-paced analysis strategies are virtually worthless. To outperform, individual traders must discover fleeting market trends and inefficiencies and act on them before they disappear. Five years ago, this required multimillion-dollar data mining and analytical infrastructures. Today, traders can use Excel with the help of world-class trader Jeff Augen’s Microsoft Excel for Stock and Option Traders: Build your Own Analytical Tools for Higher Returns.


Augen shows how to use Excel 2007 or 2010 to uncover hidden correlations and reliable trade triggers based on subtle anomalies and price distortions, create and test new hypotheses others haven’t considered, and visualize data to reveal insights others can’t see!


"Jeff Augen turns things inside out in his remarkable and challenging book Microsoft Excel for Stock and Option Traders." 
- John A. Sarkett, SFO Magazine, October 2011











(2007/11/21), then the conditional for XYZ record 3 would give an error message because the record it points to no longer exists, and all conditionals that follow would be corrupted. The solution to this problem is to point to individual records using indirection, which allows a cell to store a pointer to another cell. If, for example, cell M2 contains the value G11, then the statement INDIRECT(M2) will return the contents of cell G11. We can use this construct to build a table of pointers that

Stock and Option Traders compared when determining the fair value of an option contract. An investor might, for example, calculate volatility using different-length windows—20 days to represent the past month and 90 days to measure an entire quarter—in addition to annualization factors based on 252 and 365 days. Each result would generate a different option price when plugged into Black-Scholes or other pricing models. To calculate historical volatility, we must compute the standard deviation of

compared 62 price changes for 10 stocks. Such comparisons are helpful when the goal is to identify broad correlations that tend to be persistent. However, more precise comparisons that characterize the responses of individual stocks to a very limited number of specific events can be powerful predictive tools. In this context, our previous example can be thought of as a 10-stock comparison under 62 different sets of market conditions. Since most of the 62 days were unremarkable, the results tend

than .001 (line 2 of tables 3.9 and 3.11), the chance of a reversal was 65.3% in the 1 day time frame but only 53.1% in the 5 day time frame. One interpretation would be that a relatively small downward spike during the course of a downtrend is unlikely to completely disrupt the trend and trigger a long-term reversal in the form of regression to the mean. At the practical trading level it could be said that the down spike is too small to trigger a short covering rally. The fading effect is also

exiting option positions on the Philadelphia Gold/Silver Index (ticker: XAU).4 The results can also be used to time investments in physical gold. In this context gold is represented by the SPDR Gold Trust exchange traded fund (ticker: GLD).5 The results are somewhat complex because rather than directly comparing prices or price changes, the chart relates the GLD/XAU ratio to the value of XAU. That is, some time frames are characterized by a precise relationship between the GLD/XAU ratio and the

Download sample