 |
|
|
|
|
|
|
Hoadley Finance
Add-in for Excel & Associated Applications
|
| FAQs |
For FAQs relating specifically the Options Strategy Evaluation Tool, see OSET FAQs.
Finance Add-in for Excel: Purchase & Installation
 |
How many add-ins are there? How many things do I need to buy? |
|
|
|
Can I install the software on more than one computer? |
|
Finance Add-in for Excel: Functions
Open Positions Manager
|
|
|
What are
all the Excel VBA error messages when I try to load the open positions
manager? |
| Finance Add-in for Excel: Puchase & Installation |
How many add-ins are there? How many things do I need to buy?
There is one Add-in: The Hoadley Finance Add-in for Excel. This add-in includes everything.
The Hoadley Finance Add-in for Excel is also the only item of software that you need to buy. All applications and functions, apart from a small number of functions specifically indicated as being for corporate clients only (eg employee stock option functions), are included in the one price when you buy the Hoadley Finance Add-in for Excel for private use. Licensing summary
Why is the Add-in so inexpensive compared with other products?
Different business models.
With a price of around a 25% of that of the product's closest competitor the private-use/non-commercial license is very inexpensive. The add-in's quality, however, no matter how you want to measure it --range and accuracy of functions, execution speed, consistency of approach across functions, documentation, working examples, email support -- is of the highest order.
And with the scope encompassing not only tools to support the analysis of options and other derivatives, but also asset allocation, portfolio optimization, portfolio performance analysis, and risk management, the Finance Add-in for Excel goes way beyond any other available product.
Plus you get the Historic Volatility Calculator, Implied Volatility Calculator, the premium version of the Options Strategy Evaluation Tool, the Open Positions Manager, the Portfolio Optimizer and other applications free of charge.
The difference can therefore only be in our business models: Ours is based on a low price and, hopefully, high volume. Our competitors must have different models.
Can I install the software on more than one computer?
Yes. A single private-use license allows you to install the Add-in and associated applications on all your computers providing you are the only user. The license is for an individual, not a specific computer.
Both the trial and full version require a simple registration.
When you register you can either download the trial version for free, or purchase the full version (private-use only. Corporate license enquiries). You can download the trial version first and come back for the purchased version later on if you want to using the same registration details.
To register you must provide a valid email address. If your email address is not valid you won't receive the validation key which enables you to download the add-in and your registration will therefore be of no value.
Please be assured that under no circumstances will your email address be divulged to any other person or organization and you will not receive any unsolicited emails from Hoadley Trading & Investment Tools.
What are the system requirements for the Finance Add-in for Excel?
The following software environment is required to run the Finance Add-in for Excel and associated applications:
|
Microsoft Windows. Windows 10 & 11, both 32-bit and 64-bit editions.
The software will not run on an Apple Mac or under any other
operating system. |
|
Microsoft
Excel for Windows. Excel 2010 - Excel 2021 (including
Microsoft 365 editions). |
|
Microsoft .NET Framework version 4.5. This
comes pre-installed on Windows 8 and above. On Windows Windows 7 it will also already be installed on most PCs. If not, you will be directed to the Microsoft .NET 4.5 download site. |
How do I install the Finance Add-in for Excel?
Download and save the setup file to your hard drive. eg on your desktop. It is recommended that you take a backup of the file in case you need it again after your one year download period has expired.
Double click the setup file you downloaded to install the add-in.
Upgrading from a previous version: There is no need to uninstall any previous versions. If you are upgrading to a new version from a previous version, always install the new version in the same directory as the old one. ie replace the old version with the new one. The setup program will suggest this as the default.
Installing on another PC
The add-in must be installed on each PC that you want to use it on by running the setup file you downloaded. If you simply copy the files in the add-in directory from one PC to another without running the installation setup it won't work.
Why does the Implied Volatility Calculator show as "read only"? Why can't I save it?
If the Implied Volatility Calculator, and other files such as the Historic Volatility Calculator and the Options Strategy Evaluation Tool, show as "read only" in the Excel title bar -- and you therefore can't save the files -- then that's caused by your Windows security settings.
In Windows Vista or later, files installed into the Program Files directory are treated as "read only" unless you have your User Account Control (UAC) in Windows on its lowest setting.
The solution to this "issue" is not to change your Windows security settings, but simply to copy the file you intend to save (eg the Implied Volatility Calculator, Portfolio Optimizer, etc) to your usual documents folder on your hard drive following installation. Make sure this folder is a trusted location in versions of Excel 2007 or later. You do that by adding the folder to Trusted Locations in Excel's Trust Center.
You will then be able to use and save your files as normal.
Why do I get "Delete file failed: code 5" during installation? What should I do?
That's a Windows message telling you it can't complete the installation because other applications, like Microsoft Excel, are open and using some files on your PC that need to be replaced. The setup program tells you to close all applications. That message shows you didn't do this.
Close all applications and run the setup again. In the unlikely event that you continue to get the error message even though you closed the applications, re-boot your PC as this means that not all files were released when you closed the applications.
I've installed the add-in but where can I find the historic and implied volatility calculators?
The Implied Volatility Calculator and the Historic Volatility Calculator are separate Excel applications which are installed on to your hard drive when you install the add-in. They can be launched in any of three ways:
|
From the Hoadley Add-in desktop icon, which was placed on your desktop when you installed the add-in. Open this up and you will see these applications. Double click to start. |
|
From the Windows start menu. A program group (HoadleyOptions add-in) was set up when you installed the add-in. In Windows, click start/programs/HoadleyOptions add-in, and you will see these applications. Note for Windows 8 users: Windows 8 doesn't have a desktop start menu but the Hoadley add-in program group is still there and can be accessed by right clicking from the start screen (ie the tiles) and selecting "All Apps". |
|
From the directory on your hard disk where you installed the add-in. This will default to something like C:\Program Files (x86)\HoadleyOptions depending on the version of Windows you are using, unless you change it when you install the add-in. Double clicking the required application in this directory will launch it. |
Note that following installation, you should copy these applications to your normal documents folder on your PC to avoid "read only" issues associated with saving files in your Program Files folder. More information.
I upgraded to the latest version but it still seems to be the old version. Why?
If the upgrade hasn't worked -- the old version number still appears when you select "about" in the HoadleyOptions menu, or some of the newer functions seem to be missing -- then it means that you installed the upgrade into a different directory from the original. So now you have two versions of the add-in on your hard drive. Excel doesn't know anything about the new one (as it keeps track of add-ins itself) so it keeps on using the old one.
To solve the problem re-install the new version into the same directory as the old version. This will completely replace the old version and Excel will automatically use the new version.
I'm using Excel 2007 or above. Should I save Hoadley applications as Excel 2007 or later files?
Definitly not! Never change the file format of any spreadsheet downloaded from this web site.
The Historic Volatility Calculator, Implied Volatility Calculator, the Portfolio Optimizer, the Options Strategy Evaluation Tool (OSET), the sample spreadsheets etc are all Excel 97-2003 Workbooks (XLS) or XLSB files. With these formats these Excel applications and samples will run under all supported versions of Excel, 32-bit and 64-bit editions.
If you use "save as" to change the file type to the newer Excel 2007 or later format (XLSM) nothing will work correctly under any version of Excel.
There is no reason whatsoever to change the file format and contrary to what many people think, simply saving a spreadsheet in the new Excel format will usually result in errors and slowness in loading for all but the simplest of spreadsheets.
Of course any spreadsheet you create yourself and which uses functions from the Finance Add-in for Excel can be saved in any format, including the new Excel formats. It is only the file format for applications and samples downloaded from this web site which must not be changed.
Why can't I get the quotes toolbar to appear in Excel 2007 or later?
In Excel 2007 and above there are no floating toolbars. Instead, all toolbars are grouped under the Add-ins tab on the ribbon bar. You may not have noticed but when you bring up the Yahoo toolbar, or a toolbar from any other data provider, an Add-ins tab will appear on the ribbon bar immediately to the left of the Hoadley tab if one isn't already there. Click the Add-ins tab and you will find the quotes toolbar. Demo: Using the streaming quotes toolbar (2:36 mins).
Why do I get "Run-time error 429 - ActiveX component can't create object"?
This is normally caused by the Microsoft Scripting Runtime file not being properly registered on your PC. The error is completely unrelated to the add-in -- but the add-in, like a lot of other software, requires the scripting runtime file to be installed correctly.
Why is the scripting runtime not properly registered on your PC? A Google search of error 429 will show that there are thousands of reasons. eg Microsoft's data access components (MDAC, DAO) seem to sometimes cause this error.
The problem can normally be fixed by opening a command prompt (Start menu --> run) and typing REGSVR32 SCRRUN.DLL , and pressing enter. This registers the scripting runtime.
Do I need to install the add-in in order to use the Options Strategy Evaluation Tool?
No. And yes.
The Options Strategy Evaluation Tool can be used without the add-in. However with the Hoadley Finance Add-in for Excel installed you will get access to the Options Strategy Evaluation Tool (OSET) Premium Features which greatly increase the power of OSET.
Installing the add-in will automatically enable the Premium Features in OSET.
| Finance Add-in for Excel: Functions |
Why are there two types of stock price probability function?
The HoadleyProbAboveAtEnd function calculates the probability that at the end of a specified number of days the spot stock price will be above (or below) a target price. This is useful for seeing, for example, whether an European option is likely to be in the money at expiration. This function is available in both the trial and full versions of the add-in.
The HoadleyProbAnyTime function calculates the probability that the stock price will exceed (or fall below) a target price at any time during the period. These probabilities are quite different from the "at end" probabilities and are extremely useful in evaluating American-exercise options -- ie options that can be exercised at any time before expiration. As all equity options have American-style exercise this function is therefore of great value to most options traders as they need to understand the chances of target prices being reached before expiration as well as on expiration. This function is only available in the full version of the add-in.
Both probability functions are 100% analytic -- no simulation is used -- so they are very fast and thus suitable for the construction of probability tables in real-time using an external data feed.
An example of a such a probability table showing how both types of probability change with volatility is contained in the sample excel spreadsheet which comes with the add-in. See the probability demo for a overview of how these two functions can be used.
How is historical volatility calculated and why do I need it as well as implied volatility?
The historical volatility function and the implied volatility function are complementary: the implied volatility function will give you the volatility implied by current market prices, and the historical volatility function will shed light on whether this is higher or lower than actual past volatilities. Together they can provide some indication as to whether options are historically expensive or cheap, and/or whether the market believes volatility will change in the future.
If you believe, for instance, that current implied volatility is too high based on your analysis of historical volatility then selling a straddle could be a good strategy; if you think implied volatility is too low then buying a straddle could be a good strategy. The suitability of either strategy would depend on how quickly you expect implied volatility to fall into line with the "true" volatility, and that's where the GARCH model can help (see below).
The add-in will let you calculate historical volatility in six ways:
|
Close-Close: the square root of the mean of the squared deviations of closing prices from a sample. This is the most widely used approach. |
|
High-Low: uses the formula by Parkinson (1980) which produces an estimate of the volatility using high and low prices from each period in the sample |
|
High-Low-Close: uses the formula by Garman and Klass (1980) which produces an estimate using high, low and closing prices. |
|
High-Low-Open-Close: uses the formula by Rogers and Satchell (1991) which produces an estimate using high, low, open, and close prices. |
|
EWMA: Uses closing prices to calculate volatility using the exponential weighted moving average model. Includes optional estimating of the smoothing constant (Lambda) using the maximum likelihood method. EWMA is a specific case of the GARCH model -- basically GARCH without mean reversion. |
|
GARCH: Uses closing prices to calculate volatility using GARCH(1,1). The GARCH function also includes a forecasting capability which lets you estimate the volatility for specified periods into the future (eg the volatility one month, two months and three months into the future). |
The historical volatility function lets you select the observation frequency. For example, it could be closing prices every trading day with 252 trading days in a year; or every week, every month or anything at all. Similarly the sample could consist of three observations, three thousand observations or any number.
The various calculation types have their advantages and disadvantages. Of the equally weighted methods (the first four above), close and high-low-open-close seem to produce the "best" results (although close is less statistically efficient and thus requires more observations than the others), with high-low and high-low-close often underestimating the true volatility. The EWMA method will usually produce a better result than the un-weighted methods.
The GARCH model (which stands for generalized autoregressive conditional heteroscedasticity) is the most sophisticated of the models. It takes into account the well known phenomenon of volatility clustering -- that during some periods volatility may be higher than normal and during other periods it may be lower. The GARCH model also incorporates mean reversion: the tendency for volatility after a period of being unusually high or low to move towards a long run average level. The GARCH model will calculate the rate at which this is likely to occur from the sample of prices thereby enabling estimates of future volatility by time to be made.
Volatility term structures can easily be constructed using GARCH -- the HoadleyGARCH function lets you forecast volatility for any number of days into the future. None of the other models can do this as their best estimate of future volatility is simply the most recent estimate of volatility for the asset. See the GARCH demo for more information on forecasting.
The so called GARCH parameters (omega, alpha, beta, and the conditional mean) are automatically estimated by the add-in using an iterative procedure called the maximum likelihood method. See how accurate is HoadleyGARCH for benchmark information.
As the add-in does all the hard work, the GARCH model function in the add-in is no more complex to use than the other models. It should therefore generally be the model of choice for most applications. Certainly it's the only one to use if you want to look at how volatility is likely to change into the future.
However, one thing is clear about volatility: there is no one right approach for all situations. Hence the huge number of research papers available on the web which compare and contrast these models, and which cover other issues such as the optimal period over which prices should be sampled.
All the required figures for calculating historical volatility are freely available from sites such as Yahoo. The Historic Volatility Calculator will download data, calculate and graph volatility automatically. The Historical Volatility Calculator demo shows how easy it is to extract the relevant data, calculate and chart volatilities.
How accurate is the HoadleyGARCH historic volatility function?
The HoadleyGarch function is very accurate -- on published benchmarks it performs at the top of the range in terms of accuracy compared with other GARCH/econometric products.
For example the most widely used benchmark is by Bollerslev-Ghysels (JBES 4/96) using Deutschmark-British Pound returns (3 Jan 1984 to 3 Dec 1991). The estimates of all HoadleyGarch parameters are accurate to at least six decimal places. eg the first six numbers after the decimal point for alpha are exactly the same as the benchmark.
HoadleyGARCH will produce standard error estimates for all parameters. Three methods for standard error estimation are available: outer product of gradient (BHHH), Hessian matrix, and robust (QML-White). Standard errors by all methods are accurate to five decimal places against published benchmarks.
Contact Peter Hoadley for a copy of the benchmark Excel spreadsheet containing the raw data, the HoadleyGARCH function results using the data, and a comparison with the published benchmark results. Using this spreadsheet and the full version of the Finance add-in for Excel you can run the benchmark yourself.
Does the add-in assume 365 days in a year, or what?
Yes, it assumes a 365 day year for most option valuation functions but it can be easily changed to any number.
Simply convert your number of days to base 365. ie multiply the number of days input to the relevant function (eg number of days to expiration) by 365 and divide by the number of days in your year.
For example, if you want to use a 252 day year (eg if your year is measured in trading days) then 504 days (ie exactly two trading years) would be input to all functions as 504*365/252 = 730.
Note that this can only be done using the functions which accept days rather than absolute dates as input. ie HoadleyOptions2, HoadleyImpliedVolatility2 etc.
Can I estimate volatility on indices using the Historic Volatility Calculator?
Yes, but you need to use the correct symbol, and the symbols may be different from what you expect. All symbols for indices (and stocks) can be found on Yahoo. eg https://au.finance.yahoo.com/intlindices for major world indices.
What is the approach taken to volatility smile modelling?
Deeply in-the-money and deeply out-of-the-money options are generally under priced, and at-the-money options over priced by the Black-Scholes model and other related models like the binomial model. When these models are used the volatility parameter is "jacked up" (or down) to get the results observed in the market. The adjusted volatility, in effect, becomes a calibration factor rather than a representation of the "true volatility" of the underlying asset.
The presence of a volatility smile may imply a non-lognormal distribution rather than a volatility which somehow varies by strike. For example a distribution with fat tails (excess kurtosis) implies that extreme returns occur more frequently than would be predicted by a normal distribution of asset returns.
A number of attempts have been made to model this. One approach, implemented in the the Historic Volatility Calculator is based on using a non-lognormal probability distribution taking into account estimates of skewness (symmetry) and kurtosis (fatness of distribution tails and height of peaks) in price distributions. Options are priced by a Jarrow-Rudd modified Black-Scholes model (European options) or a Rubinstein implied binomial tree model (American options) using the non-lognormal, rather than the usual lognormal, distribution. The skewness and kurtosis coefficients from the distribution are usually only useful as a starting point for volatility smile modelling and the Historic Volatility Calculator let you vary these and other variables so to more closely calibrate the volatility smile with observed market option prices.
Using the prices from these modified models it is possible to estimate the implied volatility that would have to be used in the Black-Scholes or binomial models to get the option prices implied by the non-lognormal distribution and the "implied" coefficients of skewness and kurtosis. The Historic Volatility Calculator using the HoadleyOptionsNLN function in the add-in does exactly this.
This can then be compared to the actual market volatility surface produced by the Implied Volatility Calculator. This effectively compares historical asset distributions with the distribution implied by currently traded options.
The key question is: how well does this approach capture the volatility smile?
Simply measuring excess kurtosis and skewness over some arbitrary time period with some arbitrary measurement frequency and then using the results to model the volatility smile is unlikely to produce sensible results. Large excess kurtosis (eg >3.5), for instance, is usually indicative of once-off unusually large jumps in a stock price in response to specific events rather than indicating an on-going departure from lognormality.
However despite the difficulties there is quite a bit of evidence that used carefully the modified pricing formulas do quite a good (but not perfect) job of accounting for the volatility smile, particularly when the coefficients of skewness and kurtosis are derived, as they can be using the HoadleyOptionsNLN function in the add-in, from observed market option prices rather than on the underlying price returns.
For further information interested users should check out some of the large body of research on this topic. A few of the many references include:
|
Lindqvist,T and Soderman, R (2000): "Evaluating the Predictability of the Volatility Smile using Return Distributions - an application of the Jarrow-Rudd model on European index options markets." Swedish School of Economics and Business Administration Working Papers. Download. |
|
Corrado, C.J. & Su, T. (1997): "Implied Volatility Skews and Stock Index Skewness and Kurtosis Implied by S&P 500 Index Option Prices", Journal of Derivatives, vol. 4, number 4, pp. 8-19. Download. |
|
Rubinstein, M (1998): "Edgeworth Binomial Trees". Journal of Derivatives. Download. |
For an in depth (and very technical) discussion on a different approach to modelling non-normal returns, including a lot about what excess kurtosis numbers mean, see the book, Market Models - A Guide to Financial Data Analysis by Carol Alexander (published by John Wiley & Sons).
SABR: Another more recent approach to modelling the volatility smile is the SABR stochastic volatility model. The Finance Add-in for Excel includes functions which implement the SABR model, one of which will calibrate the SABR "parameters" with the volatility smile from traded options. The SABR model, captures the dynamics of the volatility smile in a self consistent way across all strikes thereby improving hedging effectiveness. SABR is probably now the most widely used model for European option volatility smile modelling.
| Open Positions Manager |
Why would I ever need to use both the Options Strategy Evaluation Tool and the Open Positions Manager?
Because of the Law of the Hammer. The two products perform quite different functions. You need them both.
The Options Strategy Evaluation Tool (OSET) is designed to be used for evaluating possible strategies before any trades take place. It has features that let you easily create, analyse, compare and save potential strategies. It's not designed to maintain a portfolio of open positions. It's true you can sort of use it for this but that would be like using Excel as a word processor.
The Open Positions Manager (OPM) is designed to maintain a portfolio of open positions on an ongoing basis. As such it lets you revalue and view the entire portfolio on one sheet, monitor exposures for the entire portfolio or for a specific expiry date etc. It's not, however, designed for easily evaluating alternative strategies.
Another key difference is that the Options Strategy Evaluation Tool is a self contained application -- what you see is what you get and it can't be changed. The Open Positions Manager on the other hand is designed as a development tool and can be changed/enhanced to suit.
In summary, there is some overlap between the functionality of the Options Strategy Evaluation Tool and the functionality of the Open Positions Manager, but not a lot. You need them both.
What are all the Excel VBA error messages when I try to load the open positions manager?
They mean that you haven't installed the add-in. See How do I install the add-in.