Up

 

Download

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Download

Finance Add-in for Excel Version Information

Latest Version Information

The latest version of the Finance Add-in for Excel is 10.7r. Download latest version. You can download the full version free of charge for one year from date of purchase.

The latest versions of other applications that use the add-in are:

Historic Volatility Calculator: 7.2 (included with the full version of the add-in)

Implied Volatility Calculator: 4.5b (included with full version of the add-in)

Portfolio Optimizer Personal (3.5g), Pro (5.2a), Portfolio Optimizer with Transaction Costs (1.1c) and Mean-CVar Portfolio Optimizer (1.0e: (separate download). See Portfolio Optimizer changes.

Black-Litterman Returns Estimator: 1.0e (separate download)

Style Analyzer: 4.0d (separate download)

Portfolio Simulator: 1.1f (separate download)

VaR Simulator: 1.0e (separate download)

Correlation Analyzer 2.1b (separate download)

Factor Analyzer 1.5b (separate download)

Retirement Planner: 2.0a  (separate download)

Open Positions Manager: 20 February 2016 (separate download).

Options Strategy Evaluation Tool (OSET):  (separate download)

 

Get automatic update notifications (excluding OSET)
   
RSS feed:    Subscribe to update notification feed (RSS)    
   
Web-based readers:  
   

 

Finance Add-in Change History

Version 10.7r (Release date 18 July 2017)

bullet

Parisian options barrier adjustment utility: HoadleyParisianBarrier function for the valuation of Parisian options -- options where the underlying price must remain above or below a barrier for a specified number of consecutive days.  Can be used for the valuation of employee stock options (ESOs) with Parisian-style performance hurdles. This function can be found under "utilities".

bullet

Australian Security Exchange (ASX) option chains:  Users can now select American, European, or both option classes when requesting an option chain using ASX as the provider.

bullet

Factor Analyzer version 1.5: The Factor Analyzer application has been updated to obtain factor returns from a new data source as the Ken French database is no longer available on Quandl.

Version 10.7q (Release date 1 July 2017)

bullet

Function examples workbook loading issue: In the Office 365 update released by Microsoft this week (Excel build 8229.2073) a significant, but undocumented, change (possibly a bug) was made to the way workbooks are opened from within Excel 2016.  This resulted in the function examples workbook sometimes opening behind other open workbooks.  Version 19.7q works around this cosmetic, but annoying, issue.

bullet

Yahoo Historical prices download: Changes to improve the reliability of historic price downloads from Yahoo when their site intermittently malfunctions.  See also version 10.7n below.

Version 10.7p (Release date 22 June 2017)

bullet

Quandl historical data usability enhancements: Access to historical data provided by Quandl using the Historic Volatility Calculator, and the Quandl historic data toolbar and wizard, has been streamlined.

Version 10.7n (Release date 19 May 2017)

bullet

Yahoo historical prices download:  Update to handle changes made by Yahoo to their historical prices download service.

bullet

Portfolio Optimizer: New versions of the Personal edition (version 3.5g) and Pro edition (version 5.1b) which will work correctly with the changed Yahoo historical price download service.

 Version 10.7m (Release date 28 April 2017)

bullet

Option chains through ASX:  Update to handle format changes at The Australian Securities Exchange (ASX)..  

bullet

Rho for futures options:  Corrects an error in the calculation of Rho for futures options (functions HoadleyOptions1 and HoadleyOptions2).  "RF" should now be used as the calculation_type for Rho for futures options.  "R" should continue to be used for other types of options..

Version 10.7j (Release date 25 February 2017)

bullet

Option chains through CBOE:  Update to handle changes at CBOE.  

bullet 

Option chains through Yahoo: Yahoo has been added as a provider of free US option chains as a (faster) alternative to using CBOE.

bullet

Interactive Brokers (IB) streaming quotes bug fix: Fixed bug introduced in version 10.7h of the add-in which caused bid size, ask size, and last size to always be returned as zero.

Version 10.7h (Release date 25 January 2017)

bullet

Interactive Brokers (IB) 64-bit compatibility release: IB streaming quotes and option chains will now run under Excel 64-bit. Requires IB API version 9.72.

Version 10.7g (Release date 13 January 2017)

bullet

Interactive Brokers (IB)  API version 9.72 compatibility release.  IB released version 9.72 of its API on 12 Januaary 2017.  Users of the IB data feed in the Finance Add-in for Excel should note that the new API requires version 10.7g (or later) of the Finance Add-in for Excel and version 10.7g (or later) of the Add-in requires version 9.72 of the API.  ie both the Finance Add-in for Excel and the IB API must be upgraded in tandem. IB users should also ensure they are using TWS build 962 or later.  See the Finance Add-in Function Reference for more information.

bullet

Interactive Brokers delayed quotes: Delayed quotes can now be requested instead of the default real-time quotes (eg for paper trading). TWS build 962 and API 9.72 required.  For instructions see "Notes on Interactive Brokers" in the option chain section of the function reference manual.

Version 10.7f (Release date 22 December 2016)

bullet

Implied Volatility Calculator 4.5:  Enhanced IV skew Analysis.  Calls and puts can now optionally be shown separately on the one IV skew chart to highlight the differences between call and put IV and to visually identify skew crossover points.  This provides valuable information for spotting and evaluating volatility skew trading strategies (eg risk reversals). Calls and puts can also optionally be shown separately in the IV surface matrix.  IV Calculator.

bullet

Yahoo quotes function: Fixed problem with the HoadleyYahooQuotes function, as a result of a change at Yahoo Finance, which caused bid and ask values to sometimes return "N/A".

Version 10.7e (Release date 26 November 2016)

bullet

Portfolio Optimizer Pro version 5.1:  Incorporation of the Jorion and .Ledoit-Wolf Bayes-Stein shrinkage models into the Portfolio Optimizer to improve covariance estimation. More details.

bullet

Option chains from file: Increased the maximum number of options in a chain when importing from a file from 3,000 to 20,000.

bullet

Numerous clarifications to function reference manual.

Version 10.7d (Release date 20 February 2016)

bullet

Excel 2016 update compatibility release: New versions of the Finance Add-in for Excel, associated applications and additional sample sheets have been released which will load correctly under the recent Excel 2016 update.

bullet

Option chains from CBOE: Fixed an issue with very large option chains, when using CBOE as the data provider, which caused options from distant months to be dropped from the chain.

Version 10.7c (Release date 7 January 2016)

bullet

Option Pricing Method (OPM) for company valuation: A template application for the allocation of value for private companies across equity classes (preferred stock, convertible debt, common stock etc) using the OPM. DetailsCorporate license required for OPM template.

Version 10.7b (Release date 12 November 2015)

bullet

Streaming quotes from eSignal, TD Ameritrade and BullSignal: Fixed bug which sometimes caused streaming quotes from these providers to return no results with some Excel/Windows configurations.

Version 10.7a (Release date 24 September 2015)

bullet

Excel 2016 compatibility release:  The Finance Add-in for Excel, and all applications and samples have been updated to provide full compatibility with the release version of Excel 2016 (32-bit and 64-bit), including Office 365.

bullet

Employee Stock Option (ESO) relative TSR against peer group valuation:  A new valuation template based on the "single Index" model has been released to simplify the valuation of relative performance plans with very large peer groups. This complements the existing peer group model.  Corporate license required. Details.

bullet

Correlated simulation using single index model: New function, HoadleySimSingleIndex which can be used to simulate correlated prices for large portfolios. Details.

Version 10.6z (Release date 8 July 2015)

bullet

Open Positions Manager version 7 July 2015:  Beta weighting: New analysis showing totals for portfolio profit and loss, and position Greeks by underlying asset. Position delta and gamma can be optionally beta weighted to enable their aggregation into portfolio totals for risk analysis and hedging purposes.  Open Positions Manager.

Version 10.6z of the Finance Addin for Excel required to support beta weighting.

Version 10.6y (Release date 22 April 2015)

bullet

Style Analyzer version 4.0:  Batch processing of multiple funds. Creates a workbook containing the results of the analyses, with the style analysis charts and tables for each fund presented on a separate worksheet (tab).  Style Analyzer.

bullet

OptionsXpress Streaming quotes: Update to the HoadleyOXQuotes function to handle a change made by OptionsXpress to their streaming quotes service. Version 10.6y or later of the add-in is required to retrieve streaming quotes from OptionsXpress.

Version 10.6x (Release date 15 March 2015)

bullet

Style Analyzer version 3.0:  Added an historical returns chart showing cumulative returns for the fund, style benchmark, and selection (active component) over time.  Style Analyzer.

bullet

Style analysis function: A minor rounding error in the calculation of the arithmetic returns for the style and selection has been fixed in the HoadleyStyleAnalysis function and Style Analyzer application.

bullet

Yahoo Quotes subroutine:  Update to handle a recent Yahoo Finance API change. Users of the Options Strategy Evaluation Tool (OSET) will also need to move to the latest version of OSET. See OSET Updates.

bullet

Eurex option chains: Update (for data provider Eurex) to handle some new and amended ISIN codes.

Version 10.6w (Release date 17 February 2015)

bullet

Risk-based asset allocation:  Two new functions (HoadleyMinTorsionRisk and HoadleyMinTorsionParity) which implement the "Minimum-Torsion Bets" approach to measuring portfolio diversification and portfolio construction.  Update of risk diversification additional samples spreadsheet to include the new risk model.   More details. 

bullet

Factor Analyzer version 1.1: Analysis of drawdowns is now available.  More details

bullet

Backfilling short return histories:  New function, HoadleyReturnsStambaugh will generate a complete set of synthetic returns for assets whose return histories differ in length (for investment portfolio analytics, asset allocation etc). Complements the existing Stambaugh model functions.  Details.

bullet

Style Analyzer version 2.4: Drawdown analysis can now be viewed as either log or flat returns.

bullet

Removed redundant observation_frequency argument from the HoadleyPricesStambaugh function.

Version 10.6v (Release date 15 January 2015)

bullet

Factor Analyzer Application:  Release of new application to analyze the performance of individual stocks, funds, and user-defined portfolios against both the Fama-French three-factor model, and the Capital Asset Pricing Model (CAPM).  More details.

bullet

Principal Component Regression (PCR):  Two new functions: HoadleyPCR uses the PCR method to minimize the negative impacts of multicollinearity when performing linear regressions.  HoadleyMLRcheck checks data for multicollinearity.   More details.

bullet

Correlation and Principal Components Analysis: These functions can now use historical returns.  ie users can now choose between using either prices or returns as input.

bullet

Quandl favourites: Fixes a problem with the Quandle data provider API documentation which limited the number of Quandl favourites to 25.

Version 10.6u (Release date 13 December 2014)

bullet

Portfolio Optimizer Pro version 5.0 -- Optimization with factor constraints:  New feature to enable portfolio weights to be constrained by overall exposure to one or more underlying factors, eg Beta. More details

bullet

Correlation matrices from factors: Two new functions for the construction of correlation matrices from asset exposures to underlying factors:  HoadleyCorrSingleFactor, using  a single factor such as beta;  HoadleyCorrMultiFactors using two or more factors. Particularly useful when the number of assets exceeds the length of available price histories.

bullet

HoadleyEfficientFrontier function: Enhancement to enable constraints to be specified based on asset exposures to underlying factors.

bullet

Interactive Brokers option chains: A trading class can now optionally be specified when retrieving equity and index option chains from IB. This feature was previously only available for futures options.

bullet

Portfolio Style Analyzier version 2.3d: Fixed error which truncated the number of years showing on the drawdown analysis graph to seven.

Version 10.6t (Release date 7 November 2014)

bullet

Backfilling short price histories: A new function, HoadleyPricesStambaugh will generate a complete set of synthetic prices for assets whose price histories differ in length (for investment portfolio analytics, asset allocation etc). This function complements the existing HoadleyCorrelStambaugh function.  Details.

bullet

Historical data scrubbing example:  A new additional sample spreadsheet, showing how a typical set of historical prices, containing gaps and short price histories for some assets, can be cleaned up (gaps filled; short price histories backfilled) using several functions in the Finance Add-in for Excel, is available for download. Details.

bullet

Free US option chains through Yahoo Finance are no longer available due to changes in their formats.  Free US option chains are still available using CBOE as the provider.

bullet

Option chains requested through CBOE:  Fixed an issue, caused by a recent change at CBOE, which sometimes resulted in the retrieval of option chains failing with an error message.

Version 10.6r (Release date 2 October 2014)

bullet

Option chains through Yahoo Finance:  Free US option chains are now available through Yahoo Finance (Provider: "Yahoo").  MSN option chains are no longer available due to changes at MSN.  ie Yahoo has replaced MSN.  Data feeds.

bullet

Quandl historical data: Quandl has moved to a secure (HTTPS) API and will be phasing out the less secure HTTP API in 2015. Version 10.6r of the Add-in implements the secure API. No user changes required other than updating to version 10.6r of the Add-in.  Quandl.

Version 10.6q (Release date 7 August 2014)

bullet

Interactive Brokers (IB)  API version 9.71 compatibility release.  

Version 10.6p (Release date 29 July 2014)

bullet

Risk-based asset allocation:  Three additional functions to support risk-based portfolio construction: HoadleyMDP for allocation using the Most Diversified Portfolio model; HoadleyMVP for constructing long-only minimum-variance portfolios; HoadleyPCARisk to analyse the extent to which a portfolio is diversified across underlying uncorrelated risk factors.  This function can also be used to construct Diversified Risk Parity (DRP) portfolios where risk is allocated evenly across underlying risk factors rather than across assets.  More details.

bullet

Risk diversification additional samples:  A new additional samples spreadsheet, which illustrates several key principles of portfolio risk diversification, is available for download. More details.

Version 10.6n (Release date 31 May 2014)

bullet

Portfolio Optimizer Pro Version 4.0:  Graphical analysis of historical trends and stability over time for beta, volatility, prices and correlations. Version 10.6n of add-in required.

bullet

New data analysis functions: Three new functions for analyzing historical correlation trends (using a rolling window), and for calculating arithmetic and geometric returns from historical prices or returns.

Version 10.6m (Release date 27 February 2014)

bullet

Interactive Brokers (IB)  API version 9.70 compatibility release.

Version 10.6k (Release date 1 February 2014)

bullet

Interactive Brokers streaming quotes function:  Trading class can now optionally be specified when requesting streaming quotes using the HoadleyIBQuotes function.  Trading class is required to uniquely identify some option contracts.

Version 10.6j (Release date 1 January 2014)

bullet

Quandl historical datasets.  The Quandl data service provides free historical data covering a wide range of subject areas: futures (including continuous futures contracts), currencies, interest rates (including constant maturity time series), commodities and more.  Version 10.6i of the Finance Add-in for Excel includes a:

bullet

Toolbar and wizard for downloading data from multiple datasets into a spreadsheet. The toolbar will make use of Quandl favourites, if set up by the user, to simplify dataset selection.

bullet

Component for use in a VBA module for downloading Quandl datasets under program control.

bullet

Historic Volatility Calculator version 7.0: New release will retrieve historical data from Quandl.  Includes access to users' Quandl favourites (if set up) to streamline dataset selection.  More details.

bullet

Excel 2013 64-bit compatibility issue: A problem which prevented the Portfolio Optimizer Pro and HoadleyEfficientfrontier functions from running correctly under 64-bit Excel 2013 has been fixed.

bullet

Historical data from Yahoo:  Update to handle a recent format change at Yahoo Finance that prevented download of historical data in some situations.

Version 10.6h (Release date 29 November 2013)

bullet

Interactive Brokers option chains: Primary Exchange specification.  Recently IB changed the contract specifications for a small number of US stocks (MSFT, CSCO, INTC...) so that it is now necessary to specify a "primary exchange" as well as  the order destination exchange (eg SMART) for these stocks. The primary exchange can now be specified when requesting option chains.  Refer to the Add-in documentation for details and examples.

bullet

Correlation Analyzer version 2.1:  New auto-selection of clusters feature complements existing interactive cluster selection. Correlation analyzer details.

Version 10.6g (Release date 21 November 2013)

bullet

Correlation Clustering Analyzer version 2.0:  This release supports a mult-stage approach to asset allocation/portfolio construction. New features in version 2.0 include interactive selection of clusters from the hierarchical cluster diagram, correlation matrices highlighting selected clusters, and cluster correlation matrices, where individual assets are merged into clusters.  More details.

Version 10.6f (Release date 28 October 2013)

bullet

Constant Proportion Portfolio Insurance (CPPI):  Two functions for the valuation of investment portfolios which have been insured using the CPPI methodology.  Includes calculation of payoffs at expiry, application of proportional transaction costs with rebalancing at discrete time intervals, simulation of single paths for analysis, estimation of "gap risk", etc. See CPPI functions.

bullet

CPPI additional examples:  New additional samples spreadhseet illustrating the valuation of portfolios using CPPI, comparing CPPI with Option Based Portfolio Insurance (OBPI), CPPI simulation and more. See additional samples.

Version 10.6e (Release date 17 September 2013)

bullet

Bond portfolio target-date immunization:  New function, HoadleyBondImmunize, to immunize a portfolio of bonds against interest rate fluctuations while maximizing portfolio yield to maturity.  Weight constraints can be specified for individual bonds, and for groups of bonds. eg a maximum holding for the combined total of junk bonds could be specified.

bullet

Targeted portfolio optimization:   New function, HoadleyMVOTarget to produce an efficient portfolio for either a target portfolio return or a target portfolio volatility.  Like the existing HoadleyEfficientFrontier function,optimization constraints can be specified for individual assets and for groups of assets (linear constraints). Licensing conditions apply. More details.

bullet

Interactive Brokers (IB)  API version 9.69 compatibility release.

Retirement Planner Version 2.0: (Release date 8 September 2013)

bullet

Dataset saving & importing:  Plan details can now be saved independently of the Retirement Planner application itself.  This simplifies scenario handling.

bullet

Glidepath (target-date) plan analysis:  Three examples provided to enable glidepath strategies to be compared with standard asset allocation strategies (eg constant allocation).  Includes detailed explanatory notes and pointers to recent research questioning the conventional wisdom of the glidepath approach to retirement plan asset allocation. More details.

Version 10.6c (Release date 6 August 2013)

bullet

Implied growth function (Investment valuation):  New function, HoadleyImpliedGrowthDD2, to calculate the short term growth implied by the market value of an individual firm or market index.   See valuation functions for more details.

bullet

Market-Leveraged Stock Unit (MSU) valuation: New function, HoadleyMSU1, for the valuation of MSUs. Corporate license required.  More details.

bullet

Correlation matrix reordering: New utility function, HoadleySortByCluster, to simplify rearranging correlation matrices into clusters.  Can be used in conjunction with the existing hierarchical clustering and cluster merging functions.  Details of matrix reordering functions.

bullet

ASX option chains: Fixed option chain retrieval error with the Australian Securities Exchange (ASX) data provider which occurred with one particular short date setting in Windows.

Version 10.6a (Release date 27 June 2013)

bullet

Option chains requested through MSN:  Update to handle recent format changes at MSN.

bullet

Option chains requested through CBOE:  Fixed an issue which caused the retrieval of some large option chains like SPY to fail with an error message. Other providers not affected.

bullet

Implied Volatility Calculator filtering by strike:  Version 4.3 of the IV calculator has been released which allows the user to restrict the options in a chain to a specified range of strikes. Can be used to produce more meaningful volatility surface charts etc.

bullet

Implied inflation function: New utility function HoadleyInflationRate to calculate inflation implied by a nominal rate and a real rate. More details on rate utilities.

Version 10.5z (Release date 18 June 2013)

bullet

Adjusting equity beta for leverage:  New function, HoadleyReleverBeta, to adjust the beta of a firm being valued to take account of an expected change in the capital structure of the firm.  See valuation functions for more details.

bullet

Option chain sorting:  Fixes a sort order issue with options on a small number of currency futures (options appeared out of order).  Other types of options are unaffected by this change.

Version 10.5y (Release date 23 May 2013)

bullet

Investment valuation: Two new functions for the valuation of companies using the free cash flow to the firm (FCFF) model.  These functions complement the existing DD and FCFE models.  See valuation functions for more details.

Version 10.5x (Release date 16 May 2013)

bullet

Interactive Brokers streaming quotes: Company name is now available with the HoadleyIBQuotes function.

bullet

OptionsXpress futures option chains:  Works around a recent problem at OptionsXpress which caused futures options for some expiry dates to be dropped.  Time to retrieve futures option chains using OptionsXpress has been significantly reduced.

Version 10.5w (Release date 3 May 2013)

bullet

Implied Equity Risk Premium (ERP): New function to calculate the equity risk premium implied by a market index, for company valuation purposes.  See Investment Valuation functions for more information.

Version 10.5v (Release date 25 April 2013)

bullet

Investment valuation: Release of four functions for the valuation of investment assets (eg companies) using two widely used models: Dividend discount and Free Cash Flow to Equity (FCFE).  Two and three-stage versions are implemented for each model.  More details.

bullet

Real/nominal conversions: Four utility functions to convert nominal rates and risk premiums to their real equivalents and vice-versa

bullet

VaR additional samples spreadsheet (version 2.6): Update to the explanation sheet for the VaR copula simulation example to clarify the way in which the degrees of freedom argument for marginals is specified.  No functions have changed; only the explanatory text.

bullet

Open Positions Manager OPM) version 26 April 2013:  On some configurations running Excel 2013, the Open Positions Manager causes Excel to crash. Earlier versions of Excel not affected.  The latest version of the OPM should work around these Excel 2013 stability issues. Log in to download.

Version 10.5u (Release date 4 April 2013)

bullet

Interactive Brokers (IB)  Option chain performance enhancement:  As a result of recent IB API changes retrieval times for option chains have been significantly reduced. Version 10.5u is required to take advantage of these IB API changes.

bullet

Interactive Brokers option chains containing US mini options: Users can now select normal options, mini options, or both by specifying a contract multiplier as part of the exchange field when requesting option chains. This can cut option chain retrieval time in half when only one type of option is required. See "notes on IB" in function reference for details.

bullet

OptionsXpress option chains containing US mini options: Users can select normal options,mini options or both by specifying an option type in the exchange field. See function reference ("Notes on providers") for details.

bullet

Australian Stock Exchange (ASX) European exercise options: European options, which are included with American options in option chains are now automatically removed when requesting an option chain through any provider that supports ASX option chains.

Version 10.5t (Release date 1 April 2013)

bullet

Option chains containing US mini option requested through Interactive Brokers (IB): Upgrade to correctly handle option chains from IB which include US mini options (trading in US mini options commenced on 18 March 2013).  Without this upgrade mini options may return zero values for bid, ask and last.

bullet

Option chain filtering:  Option chain filtering has been redesigned to provide greater flexibility when filtering groups of options from option chains.  For example, it is now possible to exclude mini options from an option chain or, alternatively, to exclude regular options so that only mini-options are returned.  For details, click the "Applying filters" button on the option chain sample sheet (sheet 173A in version 10.5t of the Add-in).

Version 10.5s (Release date 8 March 2013)

bullet

Interactive Brokers (IB)  API version 9.68 compatibility release..

Version 10.5r (Release date 4 March 2013)

bullet

Correlation matrix asset/sector merging: New function (HoadleyCorrelMergeAssets) will physically reduce the dimensions of a correlation matrix by merging nominated assets or sectors, into clusters.  The clustered correlation matrix correctly reflects the correlation of each cluster to all other clusters and non-clustered assets. Can be used for two-stage sub-portfolio/portfolio asset allocation, departmental VaR aggregation etc.  Details.

bullet

Correlation matrix reordering:  Two functions (HoadleyReorderRow and HoadleyReorderMatrix)  to simplify changing the order in which assets/sectors appear in correlation matrices. eg to manually restructure a correlation matrix to group similar clusters of assets together

Version 10.5p (Release date 1 February 2013)

bullet

Excel 2013 compatibility release:  The Finance Add-in for Excel, and all applications and samples have been updated to provide full compatibility with the release version of Excel 2013 (32-bit and 64-bit), including Office 365 Home Premium. 

Note
that Excel 2013 has changed significantly from previous versions in that it has moved from MDI (Multiple Document Interface) to SDI (Single Document Interface). With SDI each Excel workbook opens in a new independent window with its own ribbon rather than in one Excel top-level "master" container Window. As a result previous versions of the Finance Add-in for Excel will not run correctly under Excel 2013. The latest version of the Finance Add-in has been optimized for SDI when running under Excel 2013 and for MDI when running under earlier versions of Excel.

Version 10.5o (Release date 22 January 2013)

bullet

Correlation Clustering Analyzer:  The Correlation Clustering Analyzer application has been enhanced to show the cluster relationship strength (ie strength of correlation) for each cluster of assets on both the hierarchical cluster diagram and on the step-by-step analysis.

bullet

Correlation clustering function: The HoadleyCorrelCluster function now returns the cluster relationship strength for each step on the step-by-step analysis.

Version 10.5n (Release date 11 January 2013)

bullet

Correlation Analyzer:  Release of new application for analyzing correlation matrices using hierarchical cluster analysis.  More details.

Version 10.5m (Release date 4 January 2013)

bullet

Correlation clustering:  New function (HoadleyCorrelCluster) performs a hierarchical cluster analysis on a correlation matrix.  The function re-arranges the matrix so that assets with similar characteristics are grouped into clusters.  A step-by-step analysis of the hierarchical process is also produced to aid interpretation. More details

Version 10.5k (Release date 27 December 2012)

bullet

Cross-currency swap function enhancement: The cross-currency swap valuation function (HoadleySwapFX) now allows the exchange of principal amounts at the start of the swap to be included or excluded from the valuation.  More details.

Version 10.5j (Release date 16 December 2012)

bullet

Cross-currency swap valuation: New function for the valuation of standard, and forward start (delayed start) cross-currency swaps.   More details.

bullet

Black-Litterman sample: New example added to the Black-Litterman additional samples spreadsheet.  The example compares and reconciles regression betas, and expected returns calculated with the CAPM formula, with implied betas and implied returns from reverse optimization.

Version 10.5i (Release date 10 October 2012)

bullet

Interactive Brokers (IB)  API version 9.67 compatibility release

Version 10.5h (Release date 21 September 2012)

bullet

Black-Litterman Returns Estimator application: Release of a new application for estimating investment portfolio returns using the Black-Litterman model.  This application, which uses the Black-Litterman components in the Add-in, is designed to facilitate an interactive/prototyping approach to investment portfolio design. Details.

bullet

Portfolio Optimizer Mean CVaR edition version 1.0a: Fixed error which caused Excel to crash under some conditions.

Version 10.5g (Release date 15 September 2012)

bullet

Black-Litterman additional sample:  New example added to the additional samples spreadsheet.  Existing samples enhanced and explanatory text added.  See additional samples.

bullet

Implied Volatility Calculator version 4.2: The number of options in a chain which can be analyzed as a group has been increased from 2,000 to 4,000.

bullet

eSignal futures options expiry dates:  An issue which caused expiry dates on a few futures options chains retrieved through eSignal to be out by one day has been fixed.

Version 10.5f (Release date 9 August 2012)

bullet

Futures Option chains through Interactive Brokers (IB): Futures option chains for US and international markets are now available using IB as the provider (Previously only equity and index option chains were available using IB).  Product documentation includes information and examples covering the specification of symbols, exchanges and currencies, and on filtering by trading class.  IB Overview

bullet

Fixed a recently introduced IB option chain bug which caused IB to fail to return options on some PC configurations.

bullet

NSE option chains (India): Due to format changes at the NSE free option chains are no longer available for NSE options.  Option chains for NSE are available through Interactive Brokers and eSignal.

Version 10.5d (Release date 26 July 2012)

bullet

Excel 64-bit compatibility release: The Finance Add-in for Excel is now compatibile with the 64-bit edition of Excel.  For details, including exceptions, see systems requirements.

Version 10.5c (Release date 16 July 2012)

bullet

Portfolio Optimizer Pro version 3.6: Introduction of the geometric mean return efficient frontier model (capital growth model). More details.

bullet

New sample spreadsheet which compares and contrasts efficient frontiers based on arithmetic mean variance optimization (MVO) with those based on geometric MVO. More details.

Version 10.5b: (Release date 25 June 2012)

bullet

Release of Mean-CVaR Portfolio Optimizer:  A new edition of the Portfolio Optimizer has been released which uses conditional value at risk (also known as expected shortfall (ES) and  expected tail loss (ETL) as the risk measure instead of variance.  Individual asset return distributions and their dependency structure are specified using any of the copulas supported by the Add-in. More details.

bullet

Finance Add-in for Excel version 10.5b: Update to provide additional functionality required by the Mean-CVaR Portfolio Optimizer.

bullet

VaR Simulator version 1.0b:  Minor update to remove protection from a few incorrectly locked cells.

Portfolio Optimizer Pro version 3.5: (Release date 12 May 2012)

bullet

Risk-based asset allocation: Implementation of three risk-based asset allocation models: Minimum variance, maximum diversification, and risk parity.  More details.

Implied Volatility Calculator version 4.1 (Release date 21 April 2012)

bullet

Futures options: Enhancement to show the underlying futures price next to each option on the implied volatility sheet (previously the correct futures price was used in all calculations but was not visible on the sheet).

bullet

Column widths have been expanded to accommodate longer option symbols.

Version 10.5a (Release date 24 March 2012)

bullet

OptionsXpress futures option chains. Futures options chains (CBOT, CME, NYBOT, NYMEX) are now available for OptionsXpress customers in addition to US equity and index option chains. More information.

Version 10.4z (Release date 8 March 2012)

bullet

New data provider for futures options chains and streaming quotes - Barchart: Barchart has been added as a new data provider (subscription to Barchart Market Data Solutions required). New versions of the Implied Volatility Calculator and the Options Strategy Evaluation Tool have been released to provide access to futures options chains from Barchart.

Version 10.4y (Release date 1 March 2012)

bullet

Portfolio Optimizer importing data from non-Yahoo sources: All editions of the Portfolio Optimizer can now import price history data directly from another spreadsheet.  This has significantly simplified using data from non-Yahoo sources (Bloomberg etc). New Portfolio Optimizer versions are available for download.  See Portfolio Optimizer releases.

Version 10.4x (Release date 24 February 2012)

bullet

Yahoo price history downloads: Works around a recent problem with Yahoo Finance which causes price history downloads to intermittently fail. As it is unknown when or if Yahoo intends to fix this problem, the history downloader in the add-in has been changed to retry in the event of these random Yahoo failures.

bullet

Option chains requested through MSN:  Update to handle recent format changes at MSN.

Portfolio Optimizer with Transaction Costs (Release date 14 February 2012)

bullet

Release of a new edition of the Portfolio Optimizer specifically designed to incorporate proportional transaction costs, such as brokerage, incurred in restructuring or re-balancing an existing portfolio, into the optimization framework. More details.

Version 10.4v (Release date 29 January 2012

bullet

OptionsXpress streaming futures and futures options quotes: Streaming quotes for futures and futures options are now available for OptionsXpress customers.

bullet

OptionsXpress user name and password entry has been streamlined.

Version 10.4u (Release date 19 January 2012

bullet

Interactive Brokers (IB)  API version 9.66 compatibility release.  

Version 10.4t (Release date 10 December 2011

bullet

Cointegration testing: New function, HoadleyEngleGranger for testing multivariate time series for cointegration using the Engle-Granger methodology. Returns comprehensive statistics to aid interpretation of results.

bullet

Multiple linear regression: New function, HoadleyMLR, for performing multiple (or single) linear regression. Returns coefficients, standard errors and t-stats.  Similar to Excel's LINEST function but output is more logically arranged and the function is much faster.

Version 10.4r (Release date 25 November 2011

bullet

VaR Simulator: Release of a new application for VaR Simulation.  The VaR Simulator simplifies the calculation of VaR and CVaR using Monte Carlo, Copula or Filtered Historical simulation (FHS). More details.

Version 10.4q (Release date 12 November 2011

bullet

Value at Risk component CVaR: New function for the calculation of component CVaR (conditional VaR).  This function complements the existing CVaR and component VaR functions.

bullet

VaR aggregation: Two new functions to simplify the aggregation of linear VaR and CVaR from sub-portfolios (eg business units).  Aggregated VaR and CVaR can be split into components to highlight each sub portfolio's contribution to total VaR/CVar. More details.

bullet

VaR additional samples:  Enhancements to VaR aggregation, cash-flow mapping, and FOREX VaR in the additional samples spreadsheet.

Version 10.4p (Release date 29 September 2011

bullet

Interest rate swap valuation: New function for the valuation of standard, and forward start (delayed start) interest rate swaps.  More details.

bullet

Implied volatility functions: The implied volatility functions now check for unrealistically large implied volatilities caused by bad market data, and will return 9999 in these cases. These "bad" volatilities sometimes caused overflow errors in the Implied Volatility Calculator.

Version 10.4o (Release date 18 August 2011

bullet

Copulas:  Release of a set of eleven functions for the calibration of copulas, and simulation of returns and prices using copulas (Gaussian, Student T, and Clayton). All functions are multivariate.  Overview of copula functions.

bullet

Copula additional samples spreadsheet: A new samples spreadsheet is available to illustrate the use of copulas, from worksheets and VBA modules.  Details.

bullet

Value at Risk (VaR) simulation component:  The VaR simulation component has been enhanced to enable returns/prices generated from copulas to be seamlessly used in VaR simulation.  Often using copulas will be a better choice than traditional Monte Carlo simulation based as it is on the (often unrealistic) assumptions of normally distributed returns and linear correlation.  Details.

bullet

Value at Risk (VaR) additional samples spreadsheet:  A new sample sheet has been added to the VaRtools samples which illustrates the use of copulas in calculating VaR.  Any available copula with any combination of marginal distributions can be selected. Results are compared with traditional MC simulation of VaR.

bullet

Interactive Brokers (IB)  API version 9.65 compatibility release...

Version 10.4n (Release date 15 July 2011)

bullet

Rank correlation models: Two new functions to produce correlation matrices based on the Spearman Rho correlation coefficient (HoadleyCorrelSpearman) or the Kendall Tau correlation coefficient (HoadleyCorrelKendall).

bullet

Function to correct non-positive definite correlation matrices: A new function, HoadleyFixPD, will make minor adjustments to a non-positive definite correlation matrix to transform it into one which is positive definite (as required for correlated simulation) and which is therefore also positive semi definite (as required for mean-variance optimization etc).

bullet

Eurex option chains: Update to handle recent format changes which caused a problem retrieving the underlying prices for some assets.

Version 10.4m (Release date 29 June 2011)

bullet

Excel 2010 SP1 compatibility release: The samples spreadsheet, Historic Volatility Calculator, Implied Volatility Calculator, Probability cones, and several of the portfolio additional samples have been updated to load correctly under the recently released Excel 2010 service pack 1.

Version 10.4k (Release date 12 June 2011)

bullet

Risk attribution for portfolios containing foreign assets: New function, HoadleyRiskAttributionFX, to calculate the contribution and marginal contribution to portfolio risk of individual assets in an investment portfolio containing a mixture of domestic assets and assets with foreign currency exposures.  This complements the existing risk attribution function which is for domestic assets only.

bullet

Compound options:  New function, HoadleyCompoundOption, for valuing options-on-options using a binomial tree. Options can be European on European, European on American, American on European or American on American.  The function handles dividends and other payouts expressed as a yield or as a schedule of discrete payments.

bullet

Correlated simulation error notification:  The HoadleyCorrelSim function will now return an error code if invalid correlation matrices or other errors are encountered.

Version 10.4j (Release date 23 May 2011)

bullet

Eurex option chains: Update to handle recent data format changes.  Note the symbol used to retrieve option chains is now the ISIN.  Also included in this update are improvements to the "Last Price": the daily settlement price will now be used in some situations.  Details are in the function help file note (accessible from option chain sample sheet).

Version 10.4i (Release date 15 May 2011)

bullet

Bayesian covariance estimator (Ledoit-Wolf model): New function for estimating correlations and volatilities (covariance) for portfolio optimization, VaR etc from historical data using the Ledoit-Wolf Bayes-Stein shrinkage model. More details.

bullet

Portfolio Optimizer Pro version 3.3 Value at Risk (VaR) analysis: Details.

Version 10.4h (Release date 6 May 2011)

bullet

Bayesian expected returns and covariance estimators (Jorion model): Two new functions for estimating expected returns, correlations and volatilities (covariance) for portfolio optimization, from historical data using the Jorion Bayes-Stein shrinkage model. More details.

Version 10.4g (Release date 22 March 2011)

bullet

Additional trades in the Options Strategy Evaluation Tool (OSET):  The number of legs per strategy in OSET has been increased from six to ten.  Version 10.4g is required to use this feature. A new version of OSET is also available.  More details.

bullet

Employee Stock Option (ESO)  valuation with relative TSR:  A new ESO valuation model has been released where vesting percentages depend on the relative performance of a company, measured by the percentile raking of Total Shareholder Return (TSR), against a peer group. (Corporate license required). More details.

Version 10.4f (Release date 21 February 2011)

bullet

Valuation of buyback rights on Restricted Stock grants:  A new function (HoadleyRSBuyBack) can be used to value the right of a company to buy back unvested restricted stock when employees leave the firm.  A corporate license is required to use this function. More details.

Version 10.4e (Release date 24 January 2011)

bullet

US Option chains requested through MSN:  Update to handle recent format changes at MSN.

Version 10.4d (Release date 16 December 2010)

bullet

Interactive Brokers (IB)  API version 9.64 compatibility release.

bullet

eSignal Canadian and Indian (NSE) options: Option chains and streaming quotes for Canadian and Indian (NSE) equity and index options are now available for eSignal subscribers using the Hoadley Finance Add-in for Excel.

bullet

Euronext-LIFFE option chains: Option chains using LIFFE as the provider will no longer be available. Constant changes to their undocumented option chain formats have made continued support uneconomic. Note that real-time equity option chains for Euronext-LIFFE are available through Interactive Brokers.  Details.

Version 10.4c (Release date 30 September 2010)

bullet

Value at Risk (VaR) Filtered Historical Simulation (FHS):   Filtered Historic Simulation is now available as an alternative to Monte Carlo simulation. FHS (by Barone-Adesi et al) uses the GARCH volatility model to scale past returns to more closely reflect current market conditions. See VaRtools.

bullet

VaR additional samples: Two new examples have been added to the VaR additional samples spreadsheet which illustrate the use of FHS.  Monte Carlo simulation and FHS are compared using the same historical dataset.

bullet

HoadleyGARCHArray function: A new GARCH function has been released to estimate GARCH volatility and the GARCH parameters efficiently for multiple assets.  This function complements the existing HoadleyGARCH function.

Version 10.4b (Release date 19 September 2010)

bullet

Quotes Wizard:  Fixed bug, introduced in last release, which caused the quotes wizard to fail on loading in some situations.

bullet

Yahoo Quotes subroutine (HoadleyGetQuotes):  In the past few days Yahoo Finance has stopped allowing data for symbol ^DJI to be downloaded.  Unfortunately the quote data returned by Yahoo doesn't include a notification that the request has failed. This affects the HoadleyGetQuotes subroutine, which is used in OSET. The latest release of the Finance Add-in works around this issue.

Version 10.3z (Release date 10 September 2010)

bullet

Option chains requested through MSN:  MSN has recently started incorrectly classifying some puts as calls.  Version 10.3z works around this MSN bug.

bullet

eSignal API change to last trade time:  eSignal has made a minor change to their streaming quotes interface software which causes the "time of last trade" field to show the wrong time in some situations when using version 10.3z of the add-in or later. A new version of the eSignal control dll is available to fix this problem.

Version 10.3y (Release date 23 August 2010)

bullet

HoadleyYahooQuotes function Corrected error, introduced in version10.3x (5 August 2010), which caused no data to be returned in some situations.

bullet

Marketfeed streaming quotes:  Streaming quotes will now automatically restart following network errors.

Version 10.3x (Release date 5 August 2010)

bullet

Australian Stock Exchange (ASX) index option chains:  Option chains are now available on the XJO index using ASX as the provider.  Previously only equity option chains were available.  The symbol can be XJO or ^AXJO (for Yahoo Finance compatibility).

bullet

Marketfeed streaming quotes: Marketfeed has corrected a bug in their data API which caused streaming quotes to halt with an error in some situations.  Version 10.3x of the add-in incorporates the fix to their API.

Version 10.3v (Release date 14 July 2010)

bullet

Interactive Brokers streaming quotes:  The latest version of IB's TWS (build 906.9) causes no data to be returned when options and futures quotes are requested with the HoadleyIBQuotes function using the option or futures symbol rather than the underlying symbol and the expiry date, strike, etc. Version 10.3v works around this problem. Note: version 9.63 of the IB API must be installed. Details.

Version 10.3u (Release date 22 June 2010)

bullet

Non-English regional settings error:  A problem which caused several functions to fail when runing under Windows with some non-English regional settings has been fixed.  This problem, was introduced with version 10.3r.

Version 10.3t (Release date 15 June 2010)

bullet

Excel 2010 compatibility release:  The Finance Add-in for Excel, Historic Volatility Calculator, Implied Volatility Calculator, Probability Cones, Retirement Planner and Open Positions Manager have been updated to provide full compatibility with the release version of Excel 2010 (32-bit).

bullet

OptionsXpress streaming option quotes: Update to handle a change made by OptionsXpress to their streaming quotes API which caused requests for options quotes to fail.

Version 10.3r (Release date 10 June 2010)

bullet

TD Ameritrade datafeed:  Snapshot quotes now refresh much faster when "refresh" is clicked on the toolbar.

bullet

Marketfeed option Chains:  Marketfeed has fixed an issue which caused incomplete option chains to be returned for indices and SPY.  Version 10.3r of the add-in is required to handle the change at Marketfeed.

bullet

OptionsXpress option chains:  Update to handle a change made by OptionsXpress to their data API.

Version 10.3q (Release date 26 April 2010)

bullet

Insert function:  The Insert Function command from the Hoadley menu has been updated to remove minor issues that occur under various combinations of Excel and Windows.

Version 10.3p (Release date 22 April 2010)

bullet

Interactive Brokers (IB)  API version 9.63 compatibility release.

bullet

HoadleyTrinomialTS risky rate term structure:  The HoadleyTrinomialTS function now optionally allows a term structure of risky rates to be specified in addition to the risk free rates. The risky rates are used for discounting the option payoff. (commercial license required for HoadleyTrinomialTS).

Version 10.3n (Release date 13 March 2010)

bullet

Component Value at Risk (VaR):  A new function, HoadleyComponentVaR, has been added to the VaRTools function set for the calculation of component VaR, marginal VaR and VaR beta for each individual asset in a linear portfolio (eg equities, cash flow-mapped bonds). 

bullet

Implied Volatility Calculator volatility cones: Version 4.0h fixes a problem with the volatility cone graph which occurred when analyzing option chains containing months with more than one expiry date..

Version 10.3m (Release date 16 February 2010)

bullet

Option chains through CBOE:  Automated option chain downloads using CBOE as the provider are now available again after having been recently suspended due to format changes at CBOE.  Manual entry of ticker symbols continues to be available so users can comply with the terms of use on the CBOE web site should they choose to do so.

Version 10.3k (Release date 12 February 2010)

bullet

New US option symbology support for Stockwatch:  Update to handle new US option symbology in option chains.  Documentation and samples sheet for streaming quotes updated for new symbology.

bullet

OptionsXpress new symbology documentation and samples:  Documentation and samples sheet for streaming quotes updated for new symbology.

Version 10.3j (Release date 9 February 2010)

bullet

Options chains through OptionsXpress: Option chains for equities and indices through OptionsXpress are now available again and support the new US symbology. There has been a major improvement in speed, and quarterly and weekly option expiry dates (eg in SPX and SPY) are now handled correctly.

bullet

TD Ameritrade new option symbology update:  TD Ameritrade has fixed an issue with new option symbols that have fractional strike prices. Users should update to latest version of the add-in.

bullet

eSignal option chains:  The maximum number of options that can be handled in a single chain has been increased to handle some very large futures option chains (eg CL).

bullet

Option chain documentation and samples:  The documentation and sample sheets for TD Ameritrade and Interactive Brokers have been updated to illustrate the use of the new US option symbology.

bullet

Borsa Italiana option chains: Option chains through Borsa Italiana will no longer be available. Constant changes to their undocumented option chain formats have made continued support uneconomic.

Version 10.3h (Release date 30 January 2010)

bullet

New US option symbology support for eSignal:  Users of eSignal must immediately update to the latest versions of eSignal and the Finance Add-in for Excel as follows:

  • Download and install the latest version of the eSignal software from the eSignal web site (v10.6.1832).  This must be done before the next two steps.

  • Download and install the latest version of the eSignal API dll from the Hoadley web site as per the instructions previously sent to users of the Finance Add-in for Excel with eSignal.  Contact Peter Hoadley if you no longer have these instructions.

  • Download and install the latest version of the Finance Add-in for Excel.

The latest version of eSignal must be installed first. The second and third steps can be done in any order. The Add-in will check that the correct version of eSignal and the eSignal API dll have been installed and will display an error message if there is a problem.

bullet

New US option symbology support for MSN option chains:  Update for option chains requested through MSN. Previous versions of the add-in will display the new symbols correctly but the latest release will now handle non-monthly option expiry dates correctly.

bullet

Option chains using CBOE as the provider: The automated download option using CBOE has been temporarily suspended due to format changes  at CBOE..  However the CBOE/M option (which requires manual entry of the underlying symbol) is still available. Version 10.3h of the add-in fixes script error messages caused by recent changes to the CBOE web site.

bullet

Option chains using OptionsXpress as the provider: Option chains through OptionsXpress are temporarily unavailable due to migration to new interface software.

Version 10.3g (Release date 11 January 2010)

bullet

New US option symbology support for TD Ameritrade and Marketfeed:  Updates for Marketfeed and TD Ameritrade to handle the new option symbology due to be implemented this month. 

bullet

Black-Litterman HoadleyOptimalPortfolio function:  The HoadleyOptimalPortfolio function has been updated to return an error code if the "Normalize" argument is set to true and it is not possible to form a portfolio where the asset weights sum to 100%. See function documentation for details.

bullet

A problem which caused an error code 5 to be displayed when entering data provider user details, or corporate license keys, in some situations has been fixed.  This error, introduced in version 10.3f, affected very few users.

bullet

Borsa Italiana index option chains: Update to handle a change in symbol for the FTSE MIB index which caused no options to be returned.

Version 10.3f (Release date 3 December 2009)

bullet

Equally-weighted risk contributions portfolios:  A new function, HoadleyRiskParity, will estimate the weights in a long-only investment portfolio required to equalize the contribution of each component (eg asset class) to overall portfolio volatility.  The objective of Equally-Weighted Risk Contributions Portfolios (Risk Parity Portfolios) is to maximise the diversification of risk. This asset allocation approach is gaining attention as an alternative to minimum variance portfolios and mean-variance optimization in some situations.

bullet

New US option symbology support for CBOE:  Update for CBOE option chains. An immediate benefit of the new symbology is that expiry dates for quarterly options (eg on SPY) are now correct.

bullet

Style Analyzer (version 2.3b) drawdown analysis: Drawdowns are now shown as flat returns as well as log returns to simplify interpretation.

Version 10.3e (Release date 16 November 2009)

bullet

Active correlation matrix function:  New function (HoadleyActiveCorrel) to produce a correlation matrix of active returns (ie deviations from a benchmark) using equally weighted or EWMA models.

bullet

Tracking error efficient frontier examples:  New samples spreadsheet (included with the additional samples spreadsheets download) which illustrates two approaches to producing tracking error efficient frontiers. ie Portfolio optimization against a benchmark. More details.

bullet

OptionsXpress option chains: update to handle a recent change at OptionsXpress which will cause no options to be returned in some situations.

Version 10.3d (Release date 12 November 2009)

bullet

Fix for Excel 2003 and Excel 2007 bug in latest Microsoft security patch:  The security patch released by Microsoft on 11 November 2009 (KB973475 for Excel 2003 and KB973593 for Excel 2007) has a bug which causes content from non-selected worksheets to bleed into the selected sheet in some situations.  The Historic Volatility Calculator and the Implied Volatility Calculator are both affected by this bug. Version 10.3d of the add-in includes updates to these applications to work around this Excel issue.

bullet

Portfolio Optimizer Pro version 3.2a: Update to work around the above issue plus another bug fix.  Details.

Version 10.3c (Release date 22 October 2009)

bullet

Risk adjusted portfolio analysis using the M3 methodology:  New function, HoadleyM3, which calculates the proportions of an active portfolio, a passive portfolio (benchmark) and a risk-less asset (cash) required to achieve a combined portfolio volatility equal to the benchmark volatility and a tracking error (TE) equal to a user-specified target TE.  Enables risk adjusted comparison of portfolios and funds, and assists in portfolio construction.

bullet

Tracking error (TE) function:  New function, HoadleyTrackingError to calculate portfolio TE from price histories using either equally weighted or EWMA models.

bullet

Portfolio Optimizer Pro version 3.2Details.

bullet

Active Portfolio Management - Adjusting Beta: sample spreadsheet has been enhanced to include an additional example.

bullet

TD Ameritrade option chains. Fixed a bug which caused a valid symbol to be rejected as invalid if the option chain request immediately followed a request using an invalid symbol.

Version 10.3b (Release date 28 August 2009)

bullet

Interactive Brokers Streaming Quotes enhancement: The IB streaming quotes function (HoadleyIBQuotes) has been enhanced to allow much greater flexibility when specifying details of assets for which quotes are to be retrieved.

bullet

Euronext LIFFE option chains: Update to handle a recent change at Euronext.LIFFE which caused the underlying spot price on London equities to be 100 times too large

Version 10.3a (Release date 23 July 2009)

bullet

Option chains from OptionsXpress: Update to handle changes to the OptionsXpress option chain formats.  Required if you are using option chains from OptionsXpress.

bullet

Interpolation function: New utility function (HoadleyInterpolation) for the estimation of missing numbers (eg in a yield curve) using either linear or cubic spline interpolation.

Version 10.2z (Release date 20 June 2009)

bullet

Euronext LIFFE option chains: Update to handle changes at the Euronext data provider. Previous versions of the add-in will not work with Euronext after 30th June 2009.  Markets supported now include Amsterdam as well as London, Paris, and Brussels.

bullet

Interactive Brokers (IB)  API version 9.62 compatibility release..

Version 10.2x (Release date 5 June 2009)

bullet

Borsa Italiana option chains:  Option chains (equities and and FTSE MIB) from the Italian Stock Exchange have been added to the list of data providers. Data is free. Chains can be requested by exchange code or ISIN.

bullet

Implied Volatility Calculator 4.0f:  Borsa Italiana has been added to the list of data providers. 

bullet

Eurex option chains: Options with zero strikes (ie invalid options) which have recently started coming through from the Euroex data provider for some symbols are now automatically removed.

bullet

Stockwatch option chains: Fixed a problem when running with some non-English regional settings in Windows which caused the decimal point in the underlying price to be in the wrong place.

Version 10.2w (Release date 4 May 2009)

bullet

Excel 2007 SP 2 compatibility release:  Works around several bugs in Excel 2007 service pack 2 (released on 24 April 2009) which caused a chart in the Historic Volatility Calculator and a chart the Style analyzer to display incorrectly.  Version 6.5 of the Historic Volatility Calculator and version 2.3a of the Style Analyzer work around these Excel bugs.

Version 10.2v (Release date 1 May 2009)

bullet

Tax-adjusted portfolio optimization:  Two new functions (HoadleyAfterTaxTaxable and HoadleyAfterTaxTaxDef) to make the necessary adjustments to pre-tax returns, volatilities and market-values required to correctly optimize investment portfolios on an after-tax basis. More information.

bullet

Tax-adjusted portfolio optimization examples: New samples spreadsheet providing working examples of three common portfolio planning situations faced by private investors.  Additional samples details.

bullet

Fixed minor problem, introduced with version 10.2u, with the "insert function" menu which resulted in the function being inserted into a single cell even if a range was selected.  This issue only affected some users and only when using array formula functions.

Version 10.2u (Release date 5 April 2009)

bullet

Implied returns from portfolios which include foreign currency exposures:  New function, HoadleyImpliedReturnsFX, will back-out implied returns for a portfolio which includes a mixture of domestic assets and foreign assets containing various degrees of foreign currency exposures, and hedging levels.  Returns are decomposed into basic asset returns and currency returns. This new function is part of the Black-Litterman function set.

bullet

Portfolio expected return:  New function, HoadleyPortfolioReturnFX, will calculate the expected return of a portfolio consisting of a combination of domestic assets and foreign assets with foreign currency exposures.

Version 10.2t (Release date 31 March 2009)

bullet

ASX Option chains:  Fixed issue with incorrect expiry date being returned for Australian Stock Exchange option chains under certain combinations of Excel/Windows/regional date settings.

Version 10.2s (Release date 24 March 2009)

bullet

Implied portfolio returns from a single asset:  A new function, HoadleyImpliedViews, will, given an estimate of the expected return for a single asset in an investment portfolio, back out the implied returns for all other assets (called implied views or hurdle rates in Bob Litterman's book "Modern Investment Management - an Equilibrium Approach").   The hurdle rates represent the points of indifference for the purchase or sale of any asset in the portfolio and can therefore provide some insight into which assets to buy or sell.  This new function is part of the Black-Litterman function set.

bullet

Portfolio Optimizer pro version 3.1:  Portfolio and individual asset volatility is now decomposed into active risk (tracking error) on the "Analyze" sheet (as well as residual and market-based risk).

Version 10.2r (Release date 21 March 2009)

bullet

ASX option chains:  Update to handle an option chain format change at the Australian Stock Exchange.

bullet

Style Analyzer function:  A problem which caused the function to return an error code when all returns for a period were negative has been fixed.

bullet

Style Analyzer version 2.3: The style analysis chart now allows for negative weights.

Version 10.2p (Release date 7 March 2009)

bullet

Correlation and volatility estimation when price histories differ in length:  The HoadleyCorrelStambaugh function has now been re-released following verification of results.  This function, used for calculating the volatilities and correlation matrix for assets where price histories have the same end date but different start dates, was previously in beta test awaiting independent verification of the results. Changes have been made as a result of the testing so users should upgrade to the latest version.

Version 10.2o (Release date 9 February 2009)

bullet

Option Chains from OptionsXpress:  Update to handle a format change at OptionsXpress.  The format change caused Excel versions prior to Excel 2007 to return no options.

Version 10.2n (Release date 29 January 2009)

bullet

National Stock Exchange of India (NSE) index option chains:  Option chains for indices (NIFTY, MINIFTY, CNX100, DEFTY, etc) are now available.  Previously only option chains for individual equities on the NSE were available. Like equity option chains on the NSE, full index option chains are only available after hours. Partial option chains are available when the market is open (a limitation imposed by the NSE, not by the Finance Add-in for Excel).

Version 10.2m (Release date 21 January 2009)

bullet

Interactive Brokers (IB)  API version 9.60 compatibility release.

Version 10.2k (Release date 19 January 2009)

bullet

Risk attribution: New function, HoadleyRiskAttribution, to calculate the contribution and marginal contribution of individual assets and asset groups (eg industries, countries...) to overall investment portfolio volatility.  Three examples in the samples spreadsheet illustrate use of the function for individual assets, groupings of assets, and show how to interpret marginal contribution to risk.

bullet

Portfolio Optimizer Pro version 3.0: New risk attribution analysis for optimal portfolio. More details.

Version 10.2j (Release date 9 January 2009)

bullet

Yahoo quotes function -- automatic refresh: The HoadleyYahooQuotes function will now automatically refresh quotes at a frequency determined by the user. eg every 60 seconds. The Yahoo toolbar is used to start and stop automatic updates and to set the update frequency.

bullet

Yahoo quotes function will return the time the quotes were last refreshed if "LastUpdate" is used as the symbol parameter.

Version 10.2i (Release date 2 January 2009)

bullet

Capped Stock Appreciation Rights (SARs): The HoadleyESO4 employee stock option function will now value SARs with a cap on the maximum benefit payable. 

Version 10.2h (Release date 30 December 2008)

bullet

SABR stochastic volatility model: Three new functions which implement the widely used SABR stochastic volatility model for European spot and futures options. Includes a function to calibrate the SABR parameters with market data: the volatility smile from the strikes and implied volatilities of traded options. Calibration with the CBOE S&P 500 index option chain is provided as an example.

Portfolio Optimizer Pro version 2.1 (Release date 16 November 2008)

bullet

Details.

Version 10.2g (Release date 3 November 2008)

bullet

Option chain filtering to exclude non-standard options: Filtering of option chains has been introduced to enable non-standard options to be excluded from option chains.   For example, US Fixed Return Options (FROs) introduced in May 2008 and traded on AMEX should be excluded from option chains used in the Implied Volatility Calculator and in OSET as they are not valued in the same way as regular options and their payoff profile is different.   Some data providers include FROs along with regular options and others do not. Filtering is set up by default to exclude FROs, but it can be changed, for example, to include only FROs and to exclude regular options.  Note: FROs are binary cash or nothing options which can be valued using the HoadleyBinCN function.

Version 10.2f (Release date 22 September 2008)

bullet

CBOE maximum option chain size increased: The maximum size of option chains using CBOE as the provider has been increased.  With the previous version some distant months were being be dropped from the SPY option chain.

Version 10.2e (Release date 18 September 2008)

bullet

Portfolio Optimizer Pro version 2.0: Release of version 2.0 the Portfolio Optimizer Pro which includes two major enhancements: 

bullet

Saving and retrieve optimization scenarios from the optimize sheet.

bullet

The ability to compare two optimal and minimum risk portfolios side by side, and to plot two efficient frontiers on the one chart.

More information.

bullet

Problem downloading option chains from MSN on PCs with French regional settings has been fixed.

Version 10.2d (Release date 12 September 2008)

bullet

Marketfeed quotes: Update to handle changes made by Marketfeed to their web infrastructure. Users of Marketfeed need to move to version 10.2d.

Version 10.2c (Release date 30 August 2008)

bullet

Performance measures based on Lower Partial Moments (LPM): New function, HoadleyPerformanceLPM will calculate a number of LPM measures (Omega, Downside Deviation, Sortino Ratio, Kappa 3, and Upside Potential Ratio) from the past returns of a fund or portfolio. More details.

bullet

Portfolio Style Analyzer version 2.2:  The Portfolio Style Analyzer now produces the above LPM performance measures for the fund and each of the passive benchmarks or indices.

bullet

Options chains from LIFFE: Update to handle changes to the LIFFE data formats.

Version 10.2a (Release date 14 August 2008)

bullet

Historic Volatility Calculator: Update to Historic Volatility Calculator (version 6.4) to work around an Excel 2007 bug introduced in the Microsoft Office update released on 13th August 2008.  If you have applied this Microsoft update to Excel and the Historic Volatility Calculator no longer works or runs very slowly then version 6.4 of the Historic Volatility Calculator will fix the problem.

bullet

Historic Volatility Calculator now runs significantly faster under all versions of Excel.

Version 10.2 (Release date 11 August 2008)

bullet

Conditional Value at Risk (CVaR):  CVaR, also known as Expected Shortfall, is now available using the new function HoadleyCVarLinear for linear portfolios, and the Monte Carlo simulation class for non-linear portfolios.  See VaRtools for more details.

bullet

The Portfolio Optimizer (version 1.3) will now calculate CVaR (as well as VaR) for downloaded portfolios.

Version 10.1z (Release date 11 July 2008)

bullet

Employee stock options (ESOs) with time varying exercise prices:  The HoadleyESO4 function will now value ESOs with exercise prices that change over time.  This can be used in conjunction with time varying interest rates, dividend yields and volatilities.

Version 10.1y (Release date 24 June 2008)

bullet

Fixed error (introduced in version 10.1x) with the HoadleyOptions1 function  which caused #VALUE to be returned if the optional dividend type was omitted.

Version 10.1x (Release date 15 June 2008)

bullet

Delayed start options (DSOs):  New function (HoadleyDelayedStart) for the valuation and greeks for European and American delayed start (forward start) options. The function can be used to value options on stocks (with discrete dividends or dividend yields), indices, futures and currencies.

Version 10.1w (Release date 1 June 2008)

bullet

Interactive Brokers data feed integration:   Real-time US and international streaming quotes for all instruments and markets supported by IB, and real-time equity and index option chains snapshots are now available to IB customers. Requires TWS build 884.8 or above and the IB API build 9.51.   More details.

bullet

Implied Volatility Calculator version 4.0e:  Interactive Brokers added as a data provider.  The maximum size of an option chain handled has been increased from 1,000 to 2,000 options.

Version 10.1v (Release date 22 May 2008)

bullet

Downside risk measures:  Two new functions: HoadleyDownsideDeviation to calculate downside deviation, and HoadleyDownsideCorrel to calculate the downside correlation matrix for two or more assets.  The methodology used is based on the paper by Javier Estrada  Mean-Semivariance Optimization: A Heuristic Approach.  Using this methodology, the combination of downside deviation and correlation -- ie semicovariance -- can be used in standard Markowitz mean variance optimizers, like the Hoadley Portfolio Optimizer, to perform portfolio Downside Risk Optimzation (DRO) without the need for specialized software.

Version 10.1u (Release date 11 May 2008)

bullet

M-Squared risk adjusted return: The HoadleyPortfolioStats function and Portfolio Optimizer Pro version 1.2a now include the M-Squared statistic.  M-Squared is a risk adjusted measure of the return that a portfolio would earn in excess of the benchmark return if the portfolio was leveraged/de-leveraged to match the benchmark risk (volatility).

bullet

Volatility, correlation/covariance, beta/R-Squared and other functions which use the EWMA model will now use equally weighted models if the EWMA decay factor is set to 1. This enhancement is designed to make the functions more convenient to use in certain situations.

bullet

HoadleyCorrelSim function: an error which could cause slight precision problems when generating simulated correlated prices in some (unusual) situations has been fixed. Note that no applications that use this function (like the Portfolio Simulator) or the sample spreadsheet were affected by this issue.

bullet

HoadleyBond function: The price_type argument is no longer case sensitive.

bullet

Style analyzer (version 2.1a):  includes a small data set that can be used to 'test' the application without the need to upgrade to a pro or corporate license; minor usability enhancements.

Version 10.1t (Release date 20 March 2008)

bullet

HoadleyPortfolioVolFX function:  New function to calculate the volatility of a portfolio containing a mixture of domestic and foreign assets.

bullet

Various minor documentation and menu grouping enhancements.

Version 10.1s (Release date 7 March 2008)

bullet

HoadleyYahooQuotes function:  Update to handle format change at Yahoo Finance. Note that the GetQuotes subroutine was not affected by the Yahoo format change.

Version 10.1r (Release date 22 February 2008)

bullet

Backtesting:  Support for the new backtesting feature in the Options Strategy Evaluation Tool.

Version 10.1q (Release date 10 February 2008)

bullet

TD Ameritrade data feeds:  Streaming real-time US equity, index, and option quotes, and real-time option chains snapshots are now available to TD Ameritrade customers. More details.

bullet

OptionsXpress option chains: Update to handle changes to the OptionsXpress US option chain data format. This is an essential update for OptionsXpress users.

Version 10.1p (Release date 22 January 2008)

bullet

Hoadley Retirement Planner: Release of the Retirement Planner application.

bullet

Retirement planning Monte Carlo simulation class:  New class for the preparation of retirement plans using Monte Carlo simulation.  This class forms the basis of the Hoadley Retirement Planner application.

Version 10.1n (Release date 5 January 2008)

bullet

Yahoo quotes:  Fixed problem which caused the incorrect placement of the decimal point when retrieving quotes from Yahoo with some non-English regional settings in Windows. Both the GetQuotes subroutine and the HoadleyYahooQuotes function are affected.

Version 10.1m (Release date 2 January 2008)

bullet

Historic Volatility Calculator update:  New version (6.3c) with improved handling of the display of dates on the x-axis of graphs.

bullet

Portfolio Simulator: Release of version 1.1 which improves handling of short positions.

bullet

Update to the Portfolio Monte Carlo Simulation class re handling of short positions.

Version 10.1k (Release date 19 December 2007)

bullet

Portfolio Monte Carlo Simulation:  New class for simulating future portfolio risks and returns -- including the likely spread of returns and the probabilities of occurrence  --  for portfolios consisting of mutual funds, individual assets, or asset classes.  A key feature is the ability to model the impact of periodically rebalancing the portfolio back to an optimal (eg from the Hoadley Portfolio Optimizer) or strategic asset allocation.

bullet

Portfolio Simulator:  Release of the Hoadley Portfolio Simulator application.

Version 10.1j (Release date 13 December 2007)

bullet

Excel 2007 SP 1 compatibility release:  The add-in samples, Historic and Implied Volatility Calculators and the additional samples spreadsheets have been updated to load correctly under the recently released Excel 2007 service pack 1. It also corrects a problem in 10.1i of the add-in (released on 12 December 2007)  which caused the Historic Volatility Calculator to display an error on loading.

bullet

Correlated simulation function (HoadleyCorrelSim): An addition optional argument has been added to the function to reduce computation time when simulating prices for a large number of assets (eg over 30 assets).

Version 10.1h (Release date 4 December 2007)

bullet

Drawdown Analysis: New function, HoadleyDrawdown, will analyze the drawdown history of a fund or portfolio. One or more drawdowns within the analysis period can be highlighted (eg the maximum drawdown and recovery dates, and the second largest drawdown and recovery dates), and information is returned to enable the plotting of drawdown "under water" charts.

bullet

Style Analyzer version 2.0:  New release of the Hoadley Style Analyzer, which includes a drawdown analysis of the fund or portfolio. More details.

bullet

Eurex option chains: Changes to handle recent data format changes at the data provider. Note also that in this version of the add-in the symbol used to retrieve an equity option chain is now just the WKN rather than the WKN plus an alphabetic symbol.

Version 10.1f (Release date 16 November 2007)

bullet

Style analysis: A new function, HoadleyStyleAnalysis, which can be used to analyze the style of a mutual fund or portfolio using returns-based methodology originally developed by William F Sharpe.  This approach uses quadratic programming to determine the combination of positions in passive indices, style benchmarks, or asset classes that would best replicate the performance of a fund or portfolio over a specified time frame.   Licensing conditions apply.

bullet

Hoadley Portfolio Style Analyzer:  Release of new application which produces a comprehensive style analysis of a fund or portfolio using the Sharpe returns-based methodology. Includes a graphical representation of the style history so the fund's style consistency and way it has changed over time can be assessed. Style Analyzer description.

bullet

Historic Volatility Calculator: New version (6.3) which resolves a problem using Excel versions prior to 2007 which occasionally caused a blank chart to be produced.

Version 10.1e (Release date 2 November 2007)

bullet

Active Portfolio Management Sample: A new spreadsheet which illustrates how to assess the impact on key active portfolio management statistics of changing the beta of an investment portfolio with futures contracts. The spreadsheet is included in the additional samples file which can be downloaded by users of the full version of the Finance Add-in for Excel who are within their one year download period.

bullet

Improved error checking for the HoadleyPortfolioStats function.

Version 10.1d (Release date 26 October 2007)

bullet

Active Portfolio Management Statistics functions: New function, HoadleyPortfolioStats, to produce the common statistics required for active portfolio management: returns and risk/tracking error decomposed into residual, active and market; Sharpe ratio; residual and active information ratios; residual covariance etc.  All statistics are produced by individual asset and by portfolio.

bullet

Portfolio Optimizer Pro version 1.2:  A new release of the Portfolio Optimizer Pro which produces comprehensive active portfolio management statistics. More details.

Version 10.1c (Release date 30 September 2007)

bullet

HoadleyPriceMatrix function: fixed problem with method 3 which incorrectly treated prices as being in descending order of date rather than ascending order.

Version 10.1b (Release date 10 September 2007)

bullet

Option chains from MSN: MSN has been added as a new data provider of free delayed US equity option chains.  Using MSN as the data provider is considerably faster than using CBOE as the provider, and MSN is more reliable especially out of hours.  Note that MSN does not provide option chains for index options;  CBOE should be used for these.  A new version of OSET has been released which includes MSN as a provider.

bullet

Implied Volatility Calculator version 4.0a: Fixed problem on 3D volatility surface graph where the expiry month axis descriptions sometimes were not fully displayed.

Version 10.1a (Release date 6 September 2007)

bullet

OptionsXpress option chains: Update to handle a change made at OptionsXpress to their option chain data format & server. This is an essential update for OptionsXpress users. 

Version 10.1 (Release date 1 September 2007)

bullet

NSE (India) option chains:  Option chains for equities traded on the National Stock Exchange of India (NSE) are now available. Note that only option chains for individual equities are available -- indices and futures options chains are not available.

Version 10.0z (Release date 27 August 2007)

bullet

Implied Volatility Calculator 4.0 - Volatility Cones:  Volatility cones are now produced by the IV calculator combining historical (statistical) volatility with implied volatility on the one chart. Comparing statistical forecasts of volatility with implied volatilities over time horizons equal to the terms of the options can help evaluate whether options are currently cheap or expensive. The volatility cone format provides an indication of the history of volatility compared with the market's forecast. More details.

Version 10.0y (Release date 7 August 2007)

bullet

Correlation and volatility estimation when price histories differ in length:  A new function, HoadleyCorrelStambaugh,  uses the sophisticated Stambaugh (1997) method to calculate the volatilities and correlation matrix for assets where price histories have the same end date but different start dates. This function is in beta testing awaiting independent verivation of results..

bullet

HoadleyPriceMatrix function has a new "method" (method 3) for compatibility with the HoadleyCorrelStambaugh function.

bullet

Stability improvements and additional error checking for the HoadleyEfficientFrontier function.

bullet

Euronext.LIFFE option chains: update to handle changes to data formats. It's also now necessary to log into their site before retrieving option chains.

bullet

Portfolio Optimizer Pro 1.1. See details.

Version 10.0x (Release date 23 July 2007)

bullet

Portfolio optimizer/efficient frontier function:  New function, HoadleyEfficientFrontier, will optimize a portfolio and produce the efficient frontier.  The optimal (tangency) portfolio is also returned by the function.  In addition to min/max weights by individual asset, additional linear constraints can be specified to group assets into higher level classifications.

Note that HoadleyEfficienFrontier is a function that can be used in Excel spreadsheets and VBA modules. It is not itself a portfolio optimizer application. See the Hoadley Portfolio Optimizer for details of the Portfolio Optimizer application.

When purchased under a private/personal license the number of assets that can be optimized is capped at ten.  To optimize more assets using Portfolio Optimizer Pro a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro" license upgrade for private investors ("Pro" licensing enquiries).

bullet

Hoadley Portfolio Optimizer Pro: Release of the "pro" version of the portfolio optimizer application. See portfolio optimizer changes for more details. 

Version 10.0v (Release date 11 June 2007)

bullet

Black-Litterman asset allocation model: Four new functions providing a full implementation of the Black-Litterman asset allocation model for portfolio design. Licensing conditions apply.   More details. View the Black-Litterman tutorial

bullet

Black-Litterman sample spreadsheet: A new spreadsheet which replicates the results from examples contained in two of the key papers on the Black-Litterman model is available for download with the full version of the Finance Add-in for Excel.

bullet

Portfolio Optimizer 3.2.  Individual assets on efficient frontier graph. More details.

Version 10.0t (Release date 27 May 2007)

bullet

Portfolio Optimizer 3.1: Improved optimization performance, and the flexibility of graph scaling. More details.

Version 10.0s (Release date 20 May 2007)

bullet

Forward Start Employee Stock Options: A new function (HoadleyESO4ForwardStart) is now available for the valuation of ESOs where the exercise price is set at some future time after the valuation date.  A corporate license is required to use this function. More details.

bullet

eSignal futures options:  eSignal has recently enhanced its service to provide higher quality data on futures options. Version 10.0s takes advantage of these eSignal enhancements.

bullet

Sort order of option chains with some non-English regional settings in Windows: An error which caused a small percentage of option chains to be incorrectly sorted on PCs with non-English regional settings has been fixed. This error specifically caused a problem with the sentiment indicator chart in OSET.

Version 10.0r (Release date 23 April 2007)

bullet

Historic Volatility Calculator - import from file: The Historic Volatility Calculator will now import historical price data from a CSV file. This facility is useful for analysing volatility on assets for which Yahoo Finance does not provide historical data. eg futures, interest rates, currencies, stocks on markets not available on Yahoo, mutual funds. The date format (local or US) and the column numbers containing dates and prices can be specified by the user.

Version 10.0q (Release date 15 April 2007)

bullet

Variance Swaps:  Two new functions for variance swaps to calculate the fair variance and hedging portfolio weights for a new variance swap, and to calculate the fair value of a variance swap when valued some time after inception. 

bullet

OptionsXpress streaming quotes:  The streaming quotes function for OptionsXpress now connects to their new "Xtend" streaming service. This will provide greater reliability as their "classic" streaming service, previously used by the add-in, is being phased out.  No changes are required by users other than to download the new version of the add-in.

Version 10.0n (Release date 9 April 2007)

bullet

Volatility cone function: New function (HoadleyVolatilityCone) for plotting volatility cones. Volatility cones can help determine whether current implied volatility (eg from the Implied Volatility Calculator) is high or low compared with historical volatility measured over the same periods.

bullet

Historic Volatility calculator: New version which includes volatility cones.  This version also fixes some minor cosmetic issues introduced with the Excel 2007 compatibility release.

Version 10.0m (Release date 16 February 2007)

bullet

Windows Vista compatibility:  Versions of the add-in since 9.9w (released on 29th August 2006) have been fully Vista compatible.  Versions prior to that date may not run correctly under Windows Vista.

bullet

Version 10.0m corrects a recent problem when inserting a function using the HoadleyOptions menu on PCs with Norwegian regional settings.

Version 10.0k (Release date 5 February 2007)

bullet

Excel 2007 ribbon:  Enhancement to take advantage of key features of the Excel 2007 ribbon bar.

Version 10.0j (Release date 30 January 2007)

bullet

Excel 2007 compatibility release:  The add-in, Historic Volatility Calculator, Implied Volatility Calculator, Probability Cones, Portfolio Optimizer, and additional samples spreadsheets are now fully compatible with the release version of Excel 2007.

Version 10.0i (Release date 28 January 2007)

bullet

OptionsXpress option chains: Update to handle a change made at OptionsXpress to their data format. This change is only relevant for OptionsXpress users.

Version 10.0h (Release date 21 January 2007)

bullet

Volatility adjustment functions for discrete dividends: Two functions to improve the accuracy of option valuation with discrete dividends when using historical, as opposed to implied, data for volatility estimates. The two functions, HoadleyDivAdjustVol and HoadleyDivAdjustVolTS are under the utilities section of the add-in.

bullet

Yahoo historical price downloads: Recently Yahoo made a change to their price history data formats.  The Finance Add-in for Excel has been updated to handle new Yahoo format. This change affects the add-in history download functions and any application which uses Yahoo data (eg the Historic Volatility Calculator).

Version 10.0f (Release date 8 January 2007)

bullet

Risk decomposition with statistical factor model: Two functions which use principal component analysis (PCA, introduced in version 10.0b) to decompose portfolio and individual asset risk into market factor-specific volatility, and "specific" or "residual" volatility.

bullet

Portfolio aggregation tools:  Two functions to simplify the data management and computation issues associated with calculating value at risk (VaR), preparing correlation matrices etc by providing a simple means of aggregating individual assets in sub-portfolios into higher level portfolios. A large number of individual assets can therefore be represented by one item -- a weighted price history of the sub-portfolio.   PCA can optionally be used to limit the number of factors used.  See Portfolio management tools for more information. 

A new version of the VaRtools additional samples spreadsheet which illustrates the use of the  aggregation functions in the preparation of VaR is available for download.

bullet

Portfolio optimizer:  New version 3.0b is available. See new version details.

Version 10.0e

bullet

Correlated Monte Carlo simulation function: New function (HoadleyCorrelSim) to undertake correlated Monte Carlo simulation of lognormally distributed prices for two or more assets. Can be used to value options where the payoff depends on the correlation between multiple assets (in the utilities section of the add-in.)

bullet

Conversion of discrete dividends to yield: New utility function (HoadleyDivCon) to convert a discrete dividend payment schedule to an equivalent yield taking into account the term of the option and ex-dividend dates.

Version 10.0d

bullet

Binary (digital) options:  Nine new functions for binary cash or nothing and asset or nothing, binary single barrier cash or nothing and asset or nothing, and binary double barrier cash or nothing options. Fair value and "Greeks" calculated for all options.

Version 10.0c

bullet

Orthogonal EWMA (OEWMA) and Orthogonal GARCH (OGARCH):  Two new functions to enable the preparation of covariance & correlation matrices using EWMA or GARCH without the need to use the same decay factor (EWMA) or GARCH parameters across all assets. Uses the methodology, based on principal component analysis, developed by Carol Alexander and described in the paper "Orthogonal methods for generating large positive semi-definite covariance matrices".  Two additional functions are also included enable the charting of daily direct and orthogonal volatilities for a given asset, which can be useful in assessing the suitability of the orthogonal models.

bullet

OEWMA and OGARCH sample spreadsheet: A new spreadsheet which illustrates the use of OEWMA and OGARCH is available for download with the full version of the Finance Add-in for Excel.

bullet

eSignal streaming quotes:  Settlement price for futures and and futures options is now available through the add-in for eSignal subscribers.

Version 10.0b

bullet

Principal Component Analysis (PCA):  A set of functions for undertaking a principal component analysis on a portfolio of stocks, a yield curve or similar, using a price history or covariance matrix as input. PCA is a major addition to the existing set of portfolio analysis and Value at risk tools -- see portfolio analysis tools for more details on PCA. 

bullet

Arbitrage Pricing Theory (APT) example spreadsheet: A new spreadsheet which illustrates the application of PCA in constructing pure factor (basis) portfolios and in using these factor portfolios to replicate the risk and return of a specific asset using APT principles is available for download (login-in to download). Requires full version of add-in.

Version 10.0a

bullet

Portfolio Optimizer major speed improvement & increased capacity:  A new version of the Portfolio Optimizer (version 3.0a) has been released which will radically cut down the time taken to optimize large portfolios. The number of assets has also been increased from 30 to 40.   Version 10.0a of the Finance Add-in is required to use the new Portfolio Optimizer.  For details see Portfolio Optimizer change details

Version 9.9z

bullet

Indexed employee stock options: Two new functions (HoadleyESO3Indexed and HoadleyESO4Indexed) to value  employee/executive stock options where their exercise price is indexed to an industry or market index.  More information.  This function is only available with a commercial license.

Version 9.9x

bullet

Eurex option chains:  Update to handle recent changes to the Eurex option chain data format.

Version 9.9w

bullet

Quanto (cross-currency) options:  Four new functions for valuing regular, single barrier and Asian Quanto options: options on assets denominated in a foreign currency with settlement in the domestic currency at a pre-defined exchange rate. The functions handle both European and American exercise.

bullet

Windows Vista compatibility:  Various changes made to the help system and other areas to ensure Vista compatibility.

Version 9.9v

bullet

New probability function: HoadleySpotAnyTime will calculate the spot (or futures) price that has a specified probability of occurring at any time during a specified period of time.  This function complements the existing HoadleySpotAtEnd function.

bullet

Probability cone application:  New application included with the add-in to produce probability cones showing in visual form the most probable range of future asset prices.  Both "at end (expiry)"  and "any time (touching)"  cones are produced and changes in key variables (volatility, dividend yields etc.) can easily be modelled. Probability cones are used in the latest version of the Options Strategy Evaluation Tool.

Version 9.9t

bullet

American and European basket options:  New function HoadleyBasketSim for the valuation of American and European basket options using LSMC correlated Monte Carlo simulation.  This function complements the existing analytic European function (HoadleyBasketOption).

Version 9.9s

bullet

Asian options:  Two new functions for valuing European and American-style  arithmetic average price options (Asian options).  HoadleyAsianA calculates the value and "greeks" of an European Asian option using an analytic model. Handles averaging using either continuously or discretely observed underlying prices, and averaging which occurs over only a part of the option's life.  HoadleyAsianB uses a binomial tree to value both European and American-style Asian options.

Version 9.9r

bullet

Spread options:  New function, HoadleySpreadOption, for the valuation, hedge parameters, and implied correlation for options on the price differential between two assets. eg the heating oil crack spread futures options which are traded on NYMEX.  Uses a modified Black model for European options and Rubinstein's three dimensional binomial trees for American options.

Version 9.9q

bullet

Fixed a problem with the HoadleyYahooQuotes function which caused no quote values to be returned for stocks and indices with commas in the asset name field eg ^GSPC (SPX) now has a comma in the name field.

Version 9.9p

bullet

Portfolio insurance: New function, HoadleyPortInsure, to calculate the strike and number of index put options required to insure a portfolio of equities against falling below a specified value.  Usually the beta of the portfolio to be insured will not equal one, meaning the expected portfolio returns will not mirror index returns.  The Capital Asset Pricing Model (CAPM) is therefore used to ensure that these differences are taken into account.

Version 9.9o

bullet

Option chain import from file: A new provider, "File", has been added to the list of option chain providers so a text file containing option chain records can be imported into the option chain component as if it had come directly from an on-line source.  This will enable users who know some VBA to write interfaces to exchanges in countries not explicitly supported by the option chain component.  Real-time, delayed, or end of day data can be used to produce a text file which can be used in the IV Calculator, the Options Strategy Evaluation Tool, or in any application that uses the option chain component.  The text file format is documented in the add-in's help file notes for the option chain component.   A sample application which generates a compatible text file for illustrative purposes can be downloaded here.

Version 9.9n

bullet

Stockwatch option chains: Fixed problem which prevented the option chain component automatically switching to the Stockwatch backup servers when their main server was down.  This issue only affects users of the Stockwatch service, and then only when the primary Stockwatch server is down.

bullet

OptionsXpress Australia option chains: fixed small issue with expiry dates (Australian stocks only).

Version 9.9m

bullet

Implied Volatility Calculator -- version 3.2: 

bullet

Revamp of 3D implied volatility smile surface chart. The volatility smile surface is now smoothed to iron out market anomalies, making it much easier to use.  As before, the chart can be dynamically rotated and tilted to view from any angle.

bullet

Additional controls added to the Implied Volatility skew/smile chart to control the smoothness of fit of the IV smile curve, and to enable the scale of the y-axis (IV) to be fixed to simplify comparing the smile across months.

Version 9.9j

bullet

Implied Volatility Calculator -- version 3.0:  New version of the Implied Volatility Calculator containing two enhancements:

bullet

Implied Volatility Skew/Smile chart: Volatility is shown by month for all strikes in a chain. Can move forward and back through months with one button click. Both un-smoothed and smoothed curves can be shown.  This new chart complements the existing IV surface charts and for some options provides a clearer view of volatility skew.

bullet

Underlying assets sheet: An underlying assets sheet has been introduced so underlying asset details, including data provider, exercise style, and dividend information can be saved for each underlying asset.  This streamlines the process of downloading option chains and calculating IV by eliminating the need to enter these details each time.

Version 9.9i

bullet

Option chains from OptionsXpress for Australian markets:  Users with an account at OptionsXpress which has been enabled for trading on the Australian market can now bring real-time option chains into their spreadsheets, the Options Strategy Evaluation Tool and the Implied Volatility Calculator.  (Previously option chains from OptionsXpress were only available for US markets).

bullet

Option chains from Montreal Exchange: Update to handle data format changes when using "MX" as data provider.

Version 9.9h

bullet

Probability functions: Two new "any time" probability functions which calculate probabilities of stock prices moving outside one or two target prices at any time.  Trinomial trees are used so the situation where there are discrete dividends paid during the period can be handled.  These two trinomial tree functions (HoadleyProbAnyTime1T and HoadleyProbAnyTime2T complement the existing analytic "any time" functions.

bullet

Employee Stock Option (ESO) vesting time function for ESOs with performance vesting targets: calculates the expected time until the first performance target is hit and the probability that one or more performance targets will be hit during the life of the option. The vesting time estimates are required by IFRS 2. A commercial license is required for this function. Contact Peter Hoadley for details.

Version 9.9g

bullet

Release for latest version of the Options Strategy Evaluation Tool (OSET).  OSET requires version 9.9g of the add-in for the new sentiment indicator features.

Version 9.9f

bullet

Employee stock option (ESO) function ESO5 enhanced so that the performance vesting price target can now vary over time. A commercial license is required for all ESO functions.

bullet

Option chain component amended to handle a recent change with the Eurex data provider formats which caused the underlying spot price to be incorrect.

bullet

Minor change to early exercise analysis component which affects a very small percentage of situations with calls with more than one dividend.

Version 9.9d

Historical price download and Historical Volatility Calculator: Fixed a problem that appeared when running under some non-English versions of Windows.

Employee Stock Options (ESOs) - performance vesting: New ESO functionality  to handle performance vested options where the vesting of the option is dependent on a share price target being hit at any time during the life of the option.   A commercial license is required for this function. Contact Peter Hoadley for details.

Version 9.9b

bullet

VaR currency rebasing of volatilities and correlations:   Two new functions in VaRtools to rebase volatilities and correlations matrices for currency and non-currency risk factors from one currency to another.

bullet

Compatibility update to StockWatch quotes and option chain components. Stockwatch recommends that Stockwatch users upgrade to the latest version of the add-in to ensure compatibility with changes Stockwatch is making to its service.

Version 9.9

bullet

Historical price download from Yahoo:

bullet

Toolbar and wizard for downloading price history for multiple symbols into a worksheet with one button click.  Prices are aligned by date even when prices for some dates are missing from Yahoo (eg because of trading suspensions, public holidays etc) and several simple options are provided for handling missing prices.

bullet

Component for use in a VBA module for downloading history under program control.

bullet

HoadleyPriceMatrix:  A function for converting a matrix of historical prices (prices by date by multiple ticker symbols) into a form required for the calculation of beta, r-squared, correlation matrices etc., all of which require alignment of dates across all assets with no missing prices. (This function can be found under utilities in the add-in).

Version 9.8q

bullet

Beta and R-Squared: Two new functions (included  with VaRtools) for calculating Beta and R-Squared for both individual assets and for a portfolio of assets.  Both measures can be calculated using either equally weighted or EWMA models. 

Version 9.8n

bullet

Marketfeed option chains: Retrieval of option chains from the Marketfeed service has been speeded up if,  for any reason, data for one option in a chain is temporarily unavailable.  All users of the Marketfeed data service should upgrade to version 9.8n.

Version 9.8m

bullet

Employee stock option enhancement: The HoadleyESO4 function will now take account of recurring blackout periods during which employees may not exercise their options.

Version 9.8L

bullet

GARCH volatility enhancements.  Two new calculation types have been added to the HoadleyGARCH function. A calculation type of "AV" (average volatility) forecasts the volatility between "today" and a future period appropriate for pricing an option maturing at that future period.  A calculation type of "CAV" estimates the impact of a 1% change in today's instantaneous volatility on the average volatility between "today" and a future period. This is useful for assessing the exposure of a portfolio of options to volatility changes, as it takes into account the forecast volatility term structure.

bullet

Historic Volatility Calculator:  New version (4.8)  included with add-in.  The forecast volatility sheet has been enhanced to show both instantaneous and "average" volatilities on the term structure chart.  An analysis is produced of the impact of a 1% change in instantaneous volatility on the volatility term structure.

Version 9.8k

bullet

Marketfeed option chains: Changes to option chain component to handle server changes at Marketfeed. Users of the Marketfeed data service should upgrade to release 9.8i of the add-in to avoid future logon issues.

Version 9.8h

Implied volatility calculator (2.6c): it is now possible to specify both an earliest month and a latest month for the range of options retrieved from an option chain.  So, for example, the distorting affects of options which have only a few days to expiry can be excluded from IV calculations and hedging optimization.

Option chain component updated to handle changes made by the Eurex provider (boerse.de) to their option chain format.  Note: The method of specifying the option chain symbol has changed -- see special note on Eurex in the help file documentation for the option chain component.

Version 9.8e

bullet

Option chain component updated to handle changes made by CBOE to their data access protocol.

Version 9.8d

bullet

Minor improvement to the handling of rounding errors in the VaR simulation component. This change only affects simulations where the SkipPositiveDefiniteCheck property is set to true. The effect of this change is very small.

Version 9.8c

bullet

Futures option chains from eSignal: The option chain component has been updated to take advantage of recent enhancements made by eSignal to futures options data.  Users of the add-in will not notice any changes, but should update to the latest add-in version for software compatibility reasons.

Version 9.8b

bullet

Canadian and US streaming quotes from Stockwatch: Streaming real-time quotes and real-time option chain snapshots for Canadian and US markets have been added to the list of data sources.  The Options Strategy Evaluation Tool and the Implied Volatility Calculator have been updated to handle Stockwatch. See Stockwatch for details.

bullet

Open Positions Manager: New version (release date 10 May 2005) which fixed a small bug, introduced in the version dated 19 April 2005) which didn't split the screen correctly on the Positions Analysis sheet.

Version 9.8a

bullet

3D Implied volatility surface graphs: A new version of the Implied Volatility Calculator (2.6a) includes a 3D graph of the implied volatility surface of an entire option chain (IV by strike by expiry month).  The graph can be rotated and tilted to highlight the volatility surface contours.

Version 9.8

bullet

Options chains for futures options: The option chain component will now provide real-time option chains on futures options for eSignal subscribers. Exchanges include US (eg CBOT, CME, NYBOT, NYMEX) and international exchanges (eg EUREX, SIMEX).  Futures options are matched to the relevant underlying futures contract and the current futures price for the matched futures contract is returned along with the other real-time quote data for each option.  The Options Strategy Evaluation Tool and the Implied Volatility Calculator have both been enhanced to handle futures option chains.

bullet

Open Positions Manager: New version (release date 19 April 2005) which increases the number of trades which can be simultaneously displayed on the positions analysis sheet from 19 to 32. A new button will hide the chart so underlying numbers can more easily be viewed.

Version 9.7c

bullet

US and International streaming quotes from eSignal:  Streaming real-time quotes using eSignal for US and international markets, and option chains for US markets have been added to the list of data sources.  The Options Strategy Evaluation Tool and the Implied Volatility Calculator have been updated to handle eSignal. See eSignal overview for details.

bullet

Option chains from ASX: Option chain component updated to handle recent Australian Stock Exchange data format changes.

Version 9.7b

bullet

Option pricing with volatility term structure: New function, HoadleyTrinomialTS, which handles a term structure of volatilities -- ie volatilities which vary over the term of the option.  Like the existing HoadleyBinomialTS function, the new function also handles a term structure of interest rates, dividend yields, and discrete dividends using a recombining trinomial lattice.  A commercial license is required for this function. Contact Peter Hoadley for details.

bullet

UK streaming quotes from MoneyAM: Function updated to handle small change with MoneyAM protocol.

Version 9.7a

bullet

Employee Stock Options (ESOs): New ESO functionality  to handle a  term structure of  interest rates and dividend yields which vary over the term of the option (as recommended by FASB 123R) and a  term structure of volatilities (ie volatility which varies over the life of the option).   A commercial license is required for this function. Contact Peter Hoadley for details.

bullet

Two new interest rate utility functions: HoadleyForwardRate to calculate the forward rate applying between two future times and HoadleyZeroRate to calculate the zero rate at the end of a period implied by the forward rate.

Version 9.6j

bullet

Australian streaming quotes function: Streaming real-time quotes for equities, indices and options, and real-time option chains from Netquote Information Services have been added to the list of data sources. The Options Strategy Evaluation Tool and the Implied Volatility Calculator have been updated to handle Netquote. Note 20 October 2005: Netquote Information Systems no longer provides options quotes so this service is no longer available.

Version 9.6i

bullet

UK Streaming quotes from MoneyAM: Streaming real-time quotes for UK equities and indices. Free registration with MoneyAM required for limited streaming quotes service; subscription required for unlimited service.

bullet

Streaming quotes highlighting effects:  Cells can optionally be briefly highlighted when a quote item (bid, ask, last trade etc) has changed.

bullet

Bug corrected in Implied Volatility Calculator which incorrectly applied the annual percentage increase to the dividend schedule. Note: None of the add-in's implied volatility functions are affected by this, only the Implied Volatility application.

bullet

OptionsXpress streaming quotes: Maintenance release to handle new OptionsXpress partner requirements.

Version 9.6e

bullet

Streaming quotes from OptionsXpress:  Streaming real-time quotes for US equities, indices and options, and real-time option chains from OptionsXpress have been added to the list of data sources.  These quotes are currently free for OptionsXpress customers/account holders.  More details.

bullet

The option chain component in the add-in, the Implied Volatility Calculator and the Options Strategy Evaluation Tool have been updated to provide real-time option chains from OptionsXpress.

Version 9.6d

bullet

Valuation analysis: The implied volatility calculator now includes a valuation analyser which calculates the theoretical value of all options in an option chain and compares these theoretical values with current market prices. This can provide an indication as to whether options are currently under or over priced. Volatility assumptions can be varied to see the impact on the theoretical values.

bullet

Australian streaming quotes function: Streaming real-time quotes  for equities, indices and options, and real-time option chains from WebLink's BullSignal data have been added to the list of data sources.

Version 9.6b

bullet

Implied Volatility Calculator (version 2.0) includes a new analysis showing calls and puts side by side.

bullet

Option chain class updated to handle to ASX data changes.

Version 9.6

bullet

New streaming real-time quotes function for US markets: A new quotes function (HoadleyMFQuotes) will enable streaming (ie dynamically updating requiring no refresh) or snapshot US equity, index, and options quotes from the Marketfeed data service to be inserted into spreadsheet cells. A quotes wizard and floating toolbar are provided to simplify the process. A subscription to the Marketfeed data service is required.

bullet

The Option Chain component will now, in addition to retrieving delayed option chain data, provide real-time option chain data for US options.  This requires a subscription to the Marketfeed data service.  The option chain component has also has been substantially enhanced to provide greater flexibility and ease of use. 

bullet

The Implied Volatility Calculator has been enhanced to use real-time option chains from Marketfeed.

bullet

The Options Strategy Evaluation Tool has been enhanced to retrieve option chains and options quotes using the new option chain component (using delayed or real-time data).

Version 9.5e

bullet

The "dividend count" argument has been removed from all functions such as HoadleyOptions2 which previously required you to specify the number of dividends. This change is to simplify the use of these functions. New versions of the Open Positions Manager and Options Strategy Evaluation Tool have been released for compatibility reasons.

bullet

Option chain component updated to handle new CBOE formats (using manual entry of symbols).

Version 9.4e

bullet

Enhancement to the on-line option chain component for CBOE: a blank exchange will now retrieve composite option chain data across all exchanges. (Previously a blank exchange defaulted to the CBOE exchange).

bullet

VaR simulation: An additional property added to the Value at Risk simulation component to allow the check for positive definite correlation or covariance matrices to be relaxed under certain circumstances.

bullet

VaR simulation: A minor problem with the VaR simulation examples and VaR documentation relating to the  DaysPA property has been corrected.

Version 9.4b

bullet

Eurex support added to the on-line option chain component: complete option chains can now be downloaded for options traded on the Eurex exchange as well as for the previously supported exchanges. The Implied Volatility Calculator (included with the add-in) updated to include Eurex support.

bullet

New on-line quotes function: A new function (HoadleyYahooQuotes) will retrieve quotes from Yahoo into worksheet cells by using a function (as opposed to HoadleyGetQuotes which is a subroutine for use in a VBA module.) A quotes wizard and floating toolbar are provided to simplify the process of using the function.  This function requires Excel 2002 or above -- users of Excel 97 or Excel 2000 can continue to use the existing HoadleyGetQuotes subroutine.

bullet

Resolved some issues which prevented the HoadleyYahooQuotes function and the CBOE option chain function working correctly with some non-English Windows regional settings.

Version 9.3

bullet

New probability function: HoadleySpotAtEnd will calculate the spot (or futures) price at the end of a period, given a probability (up or down).  This function, which complements the existing probability functions, can be used to plot probability cones showing expected stock or futures price distributions (probability bands) between "time now" and a future period.

bullet

Fixed a minor bug (introduced with version 9.0) with the HoadleyOptions1 and HoadleyOptions2 functions which made the option type argument (ie call or put) case-sensitive when calculating the Greeks with the Black-Scholes model.

Version 9.2a

bullet

Option pricing with time-dependent interest rates: New function (HoadleyBinomialTS) for option pricing, Greeks, and implied volatility taking into account a term structure of interest rates (ie yield curve). Handles European & American options with discrete dividends or dividends expressed as a yield (which can also vary by time). This function is useful during times of steepening yield curves where the pricing of American options using the usual assumption of constant rates will not be satisfactory.  The function also handles Bermudan-style options which cannot be exercise prior to a specific date.

bullet

Option chains: Option chain component updated to handle minor change in CBOE data formats.

bullet

Historic Volatility Calculator: New version (4.6) contains an update required to handle a recent change to Yahoo's historical price data retrieval.

Version 9.1a

bullet

Implied volatility calculator: Volatility surface component will now estimate implied volatility using interpolation for strike-expiry month combinations where no reliable market data are available (eg if there is no last price, or sensible bid or ask prices on an option). The Implied Volatility Calculator (version 1.4) is included with the add-in.

Version 9.1:

bullet

Hedging optimization functionality added to the implied volatility calculator version 1.2 (which is included with the add-in).  The application will now scan an entire option chain to select the optimal combination of trades to meet specific hedging objectives within specified constraints.

bullet

The portfolio/position hedging optimizer function in the add-in has been enhanced to allow additional constraints to be specified during the hedging optimization process (eg to limit the size of any one option trade).

Verion 9.0:

bullet

On-line option chains: New component which can be used to retrieve option chains into spreadsheets.  Exchanges currently supported include all US exchanges (using CBOE as the provider), The Australian Stock Exchange, Montreal Exchange, London Exchange and the Paris Exchange. Data is free and delayed 15 minutes.

bullet

Implied Volatility Calculator: New application which will automatically retrieve a specified option chain from an on-line provider and calculate the implied volatility for each option in the chain. A volatility surface matrix is also produced.

Version 8.9:

bullet

Portfolio/position hedging optimizer: New function which will scan a list of available options trades (eg from an options chain) and identify the optimal combination of trades in the underlying and/or options required to meet specific hedge ratio targets. 

Version 8.8:

bullet

Portfolio/position hedging: New function for calculating adjustments to current portfolio positions required to achieve specific hedge ratio targets with respect to one or more of a portfolio's position delta, position gamma, or position vega. eg simultaneously making a portfolio delta and vega neutral. Adjustment can be made using only options, or by using the underlying plus options.

The Options Strategy Evaluation Tool (version 24 April 2004) and Open Positions Manager (version 5 May 2004) now use the hedging function for position hedging.

bullet

Improved error reporting with the historic volatility HoadleyGARCH function in situations where, as a result of small sample sizes, accurate volatility estimation is not possible.

Version 8.7:

bullet

Floating rate notes (FRNs or "floaters"): New function for pricing and, given a market price, calculating the effective margin over swap, of floating rate notes with a defined maturity date and periodic coupon rate resets.  Handles exchange-traded FRNs with ex-interest dates.

bullet

Time zone specification for HoadleyGetQuotes function: A time zone can now be specified when displaying date/time information from the HoadleyGetQuotes function.

bullet

HoadleyBond enhancements: The yield curve can now be expressed with discrete compounding as well as continuous compounding.  An ex-coupon date can now be specified to handle the pricing of exchange-traded bonds and hybrids.

Version 8.6d: 

bullet

Fixed an issue with the HoadleyOptions menu display which, in some cases caused the Excel menu customization file (XLB file) to gradually grow in size. 

Version 8.6:

bullet

Implied values: Two functions for the calculation of implied values (Implied strike, implied spot, implied term, implied volatility and implied risk free rate) from either an option price or an option delta.

Version 8.5:

bullet

Excel 2003 compatibility: Changes to improve execution under Excel 2003 (eg elimination of spurious Excel pop-up warnings).

bullet

Convertible bonds:  Two new features: optional specification of call notice period, and option to receive accrued interest on conversion.

Version 8.4b:

bullet

Convertible bonds: New function for valuation of convertible bonds, convertible notes and other similar hybrids.  Handles convertibles with "hard" call, "soft" call, and put features. Valuation is by trinomial tree using the methodology by Tsiveriotis and Fernandes which takes into account issuer credit risk.

Version 8.3a:

bullet

Historic volatility calculator: Volatility calculator enhanced to take advantage of the closing price data adjusted for splits and dividends which is now being provided by Yahoo.

bullet

Portfolio Optimizer: Enhanced to take advantage of the "adjusted close" data from Yahoo.

Version 8.3:

bullet

Basket options:  Options on a portfolio ("basket") of assets. Analytic computation used (ie not simulation).

bullet

Yield conversion: New function to convert yields and rates from one compounding frequency to another.

Version 8.1a:

bullet

Portfolio Optimizer application, using the full version of the add-in, released.  The Portfolio Optimizer downloads data from the web and analyzes a portfolio terms of portfolio volatility, beta, R-Squared and VaR and calculates the efficient frontier and optimal portfolio in terms of maximum return for minimum risk.

Version 8.1:

bullet

Value at Risk (VaR): VaRtools expanded to include functions for producing correlation and covariance matrices using the EWMA model.

Version 8.0:

bullet

Value at Risk (VaR): Introduction of VaRtools -- a set of functions and components for the calculation of VaR. Includes analytic VaR, VaR by Monte Carlo simulation for non-linear portfolios (eg with options), cash flow mapping for interest rate instruments, correlation and covariance matrix construction, portfolio volatility estimation and more.

 

Portfolio Optimizer Change History

Portfolio Optimizer Pro version 5.2 (Release date 28 May 2017)

bullet

Adjusted closing price calculation:  Adjusted closing prices will .optionally be calculated if not available from Yahoo.

Portfolio Optimizer Pro version 5.1b (Release date 20 May 2017)

bullet

Historic data download from Yahoo: Update to handle data format changes at Yahoo.

Portfolio Optimizer Pro version 5.1 (Release date 26 November 2016)

bullet

Incorporation of the Jorion and Ledoit-Wolf Bayes Stein shrinkage models into the Portfolio Optimizer to improve covariance (volatility and correlation) estimation. Users can now select from the following models when calculation volatilities and correlations from downloaded data or user supplied data:  Equally weighted, EWMA, Jorion shrinkage, or Ledoit Wolf shrinkage.  The Portfolio Optimizer utilizes the existing shrinkage functions in the Finance Add-in.

These models are selected by clicking the "Populate..." button on the optimize tab.

Portfolio Optimizer Pro version 5.0 (Release date 13 December 2014)

bullet

Optimization with factor constraints: New feature to enable portfolio weights to be constrained by overall exposure to one or more underlying factors. Examples include the CAPM beta, and the Fama-French factors (beta, SMB, HML, momentum).

The exposure of each asset to each factor can be specified (factor loadings) and the portfolio will be optimized within minimum and maximum constraints. eg portfolio beta can be constrained to be between 0.9 and 1.1 for all portfolios of the efficient frontier. 

To use, click "Factor Constraints" button on optimize sheet.

Portfolio Optimizer Pro version 4.0 (Release date 31 May 2014)

bullet

Trend analysis: Graphical analysis of historical trends and stability over time for beta, volatility, prices and correlations. .

Portfolio Optimizer Mean-CVar edition, version 1.0a (Release date 27th September 2012)

bullet

 Fixed error which caused Excel to crash under some conditions.

Portfolio Optimizer Pro version 3.6 (Release date 16 July 2012)

bullet

Introduction of portfolio optimization based on the geometric (multi-period) model. The user can now select either the geometric return model (known as the capital growth model) or the standard arithmetic return model when producing an efficient frontier.

The portfolio which produces the highest geometric return is now produced by the optimizer.

A sample spreadsheet which illustrates the principles of both optimization models is available for download.

 Mean-CVar Portfolio Optimizer version 1.0 (Release date 25th June 2012)

bullet

Release of a new edition of the Portfolio Optimizer which optimizes the portfolio using Mean-CVaR principles.  Copulas are used to specify the return distributions of the assets and their dependency structures.  Requires the Finance Add-in for Excel version 10.5b or later.   More details.

Portfolio Optimizer Pro version 3.5: (Release date 12 May 2012)

bullet

Risk-based asset allocation: Implementation of three risk-based asset allocation models: Minimum variance, maximum diversification, and risk parity.  More details.

Portfolio Optimizer Pro version 3.4, Portfolio Optimizer Personal edition 3.5 and Portfolio Optimizer with Transaction Costs version 1.1 (Release date 1 March 2012)

bullet

Importing data from non-Yahoo sources: All editions of the Portfolio Optimizer can now import price history data directly from another spreadsheet.  This has significantly simplified using data from non-Yahoo sources (Bloomberg etc). 

Portfolio Optimizer with Transaction Costs version 1.0c (Release date 14 February 2012)

bullet

Release of a new edition of the Portfolio Optimizer specifically designed to incorporate proportional transaction costs, such as brokerage, incurred in redesigning or re-balancing an existing portfolio into the optimization framework. More details.

Portfolio Optimizer Personal 3.4 (Release date 6 February 2012)

bullet

Simplified installation:   The installation procedure for the personal edition of the Portfolio Optimizer has been simplified.

Portfolio Optimizer Pro version 3.3 (Release date 15 May 2011)

bullet

Value at Risk (VaR) analysis:  The leverage analysis (on the "Performance Measures 1" sheet) now shows VaR and Conditional Value at risk (CVaR) for each of the leveraged/de-leveraged portfolios shown on the analysis.

Portfolio Optimizer Pro version 3.2 a (Release date 12 November 2009)

bullet

Fix to Excel 2003 and Excel 2007 bug caused by Microsoft security patch:  The security patch released by Microsoft on 11 November 2009 (KB973475 for Excel 2003 and KB973593 for Excel 2007) has a bug which causes content from non-selected worksheets to bleed into the selected sheet in some situations.

bullet

Capital allocation chart: Fixed a bug (introduced in verson 3.2 on 22nd October 2009) which caused the CAL on the  capital allocation chart on the Optimal Portfolio Analysis sheet to be incorrectly displayed.

Portfolio Optimizer Pro version 3.2 (Release date 22 October 2009)

bullet

Risk adjusted analysis using M3 methodology: Active management statistics are now produced for a range of tracking error targets using the M3 methodology. The analysis also shows the proportions of the optimal portfolio, the benchmark and cash required to achieve each tracking error target. More details.

bullet

Efficient frontier active management statistics: A full set of active management statistics is now shown for each of the 20 portfolios on the efficient frontier.

Portfolio Optimizer pro version 3.1 (Release date 24 March 2009) 

bullet

Portfolio and individual asset volatility is now decomposed into active risk (ie tracking error) on the "Analyze" sheet (as well as residual and market-based risk).

Portfolio Optimizer Pro version 3.0 (Release date 19 January 2009)

bullet

Risk Attribution: The portfolio optimizer includes a new analysis of optimal portfolio risk.  Risk is decomposed into the contribution of individual assets and asset groups (if used) to overall portfolio volatility.  The risk contribution for each asset or asset group is expressed in terms of the contribution to risk, the percent contribution to risk, and the marginal contribution to risk.

Portfolio Optimizer Pro version 2.1 (Release date 16 November 2008)

bullet

Enhanced leverage and capital allocation analysis allowing a premium to the risk free rate to be specified for borrowing. More details.

Portfolio Optimizer Pro version 2.0a (Release date 27 September 2008)

bullet

Fixed issue on the compare sheet which prevented the capital market line on one of the scenarios displaying correctly when its risk free rate was different from that of the other scenario.

Portfolio Optimizer Pro version 2.0 (Release date 18 September 2008)

bullet

Release of version 2.0 the Portfolio Optimizer Pro which includes two major enhancements: 

bullet

Saving and retrieving optimization scenarios from the optimize sheet.  This simplifies the examination of the impact of different assumptions on returns, volatilities, maximum and minimum weights etc.  Complete scenarios can be saved and later retrieved for further analysis/fine tuning.

bullet

The ability to compare two optimal and minimum risk portfolios side by side, and to plot two efficient frontiers on the one chart.  This is done using a new "compare" sheet.  The results of previously optimized portfolios can be retrieved and easily compared, both in table form and graphically.

More information.

Portfolio Optimizer Pro version 1.3 (Release date 11 August 2008)

bullet

Conditional Value at Risk (CVar) is now shown on the analyze sheet for downloaded portfolios.

Portfolio Optimizer Pro version 1.2a (Release date 11 May 2008)

bullet

Active Portfolio Management Statistics now include M-Squared. M-Squared is a risk adjusted measure of the return that the optimal portfolio would  earn in excess of the benchmark return if the portfolio was leveraged/de-leveraged to match the benchmark risk (volatility).

Portfolio Optimizer Pro version 1.2 (Release date 26 October 2007)

bullet

Active Portfolio Management Statistics:  Comprehensive set of active portfolio management statistics. More details. Requires version 10.1d  or above of the Finance Add-in for Excel.

Portfolio Optimizer Pro version 1.1a (Release date 15th August 2007)

bullet

Now correctly ignores group constraints which are not referenced by at least one asset. Previously a message box popped up with an error message.

Portfolio Optimizer Pro version 1.1 (Release date 28th July 2007)

bullet

New "Optimal Portfolio Analysis" tab:   Analysis 1: Impact on portfolio returns and volatilities of leveraging/ de-leveraging the optimal portfolio.   Analysis 2: Optimal portfolio sorted by descending order of asset weighting; Analysis 3: Asset grouping analysis showing in total and by individual asset the weightings for each asset grouping; assets sorted in descending order of weight within each grouping.

Release of Hoadley Portfolio Optimizer Pro Version 1.0: (Release date 23 July 2007)

bullet

A new version of the portfolio optimizer has been released: Portfolio Optimizer Pro. Using the HoadleyEfficientFrontier function instead of Excel's Solver, optimization times are now a fraction of those using the previous version.  For example the time to produce the entire efficient frontier and optimal portfolio for fifty assets is now less than one second compared with between 1.5 and 3.75  minutes (depending on the version of Excel) with previous optimizer versions.

The number of assets that can be optimized using the "Pro" version is capped at ten when purchased under a private use/personal license. To optimize more assets using Portfolio Optimizer Pro a corporate/commercial license is required for businesses (Commercial licensing enquiries) or a "Pro" license upgrade for private investors ("Pro" licensing enquiries).

The previous version (renamed to Portfolio Optimizer Personal) is still included in the download file and has no theoretical limit on the number of assets that can be optimized.

See Portfolio Optimizer versions for more information.

Portfolio Optimizer Personal Version 3.3: (Release date 20 July 2007

bullet

Worked around an issue which caused an error message to appear at the end of the optimization process in a few situations on versions of Excel prior to Excel 2007. (On Excel 2007 the error message did not appear).

Portfolio Optimizer Personal Version 3.2: (Release date 8 June 2007

bullet

Individual assets on efficient frontier chart:  The individual assets in a portfolio can now optionally be displayed on the efficient frontier chart.  This lets you see at a glance the risk/return profile of each asset and its relationship to the efficient frontier.

Portfolio Optimizer Personal Version 3.1: (Release date 27 May 2007

bullet

Performance improvements and improved graph scaling: The new version will optimize large portfolios in roughly half the time of previous versions.  Scaling of the efficient frontier graph has been improved.

Portfolio Optimizer Personal Version 3.0d: (Release date 1 Februay 2007

bullet

Performance improvements with Excel 2007: Performance improvement of around 20%-25% over version 3.0c when used with with Excel 2007.

Portfolio Optimizer Personal Version 3.0c: (Release date 30 January 2007

bullet

Excel 2007 compatibility release: New version of Portfolio Optimizer works correctly under Excel 2007. 

Portfolio Optimizer Personal Version 3.0b: (Release date 8 January 2007

bullet

Risk decomposition: The analyze sheet now decomposes individual asset and portfolio volatilities into volatility due to the market ("market risk") and the volatility due to the specific asset or portfolio ("specific risk").

Portfolio Optimizer Personal Version 3.0a: (Release date 24 September 2006

bullet

Major speed improvement & increased capacity:  This version will radically cut down the time taken to optimize a large portfolio. The larger the portfolio the greater the improvement in speed.  For example a 22 asset portfolio will now take 1/8th the time taken with the previous version.  A 30 asset portfolio will now take 1/16th the time taken previously.  The capacity of the optimizer has been increased "out of the box" from 30 to 40 assets.

Portfolio Optimizer Personal Version 2.2g: (Release date 10 October 2005)

bullet

Compatibility release to work more efficiently with version 9.9 of the Finance Add-in. No new functionality. Requires Finance Add-in version 9.9 or above.

Portfolio Optimizer Personal Version 2.2e: (Release date 19 September 2005)

bullet

R-Squared statistic on the analyze sheet is now produced for short portfolios. Previously if the net portfolio position was short R-Squared was not calculated. This requires version 9.8q or above of the Finance Add-in.

Portfolio Optimizer Personal Version 2.2d: (Release date 19 August 2005)

bullet

Fixed the "Analyze using previously retrieved data" button on the analyze sheet which had inadvertently been made inoperable by a previous update.

Portfolio Optimizer Personal Version 2.2c: (Release date 26 July 2005

bullet

When populating the optimizer sheet from the analyze sheet the program now checks that the number of columns of data being transferred does not exceed the number of columns available in the optimize sheet.

Portfolio Optimizer Personal Version 2.2: (Release date 9th February 2005)

bullet

Fixed issue with the "analyze" sheet which caused the data download to halt with an error when the net portfolio value was negative.

Portfolio Optimizer Personal Version 2.1: (Release date 28th October 2004)

bullet

The optimizer now produces a minimum risk (ie minimum variance) portfolio in addition to the optimal portfolio.

Portfolio Optimizer Personal Version 2.0a: (Release date 8th September 2004)

bullet

Minor enhancement to allow the positive semi-definite check on the correlation matrix to be skipped.

Portfolio Optimizer Personal Version 2.0: (Release date 12th August 2004)

bullet

Asset groupings:  Individual assets can now optionally be assigned to user-defined asset groups.  The groups could represent industry groupings, asset style (value, growth...), asset class (equities, fixed interest...) or anything. Minimum and maximum weight constraints can be specified by asset group as well as by individual assets.  So, for example, you could specify that no more than 15% or a portfolio should be in technology stocks. As part of the optimization process an analysis is produced showing the actual allocations by asset group for each point on the efficient frontier and for the optimal portfolio.

Portfolio Optimizer Personal Version 1.7: (Release date 2nd July 2004)

bullet

Contains an update required to handle a recent change to Yahoo's historical price data retrieval.

Portfolio Optimizer Personal Version 1.6:

bullet

Optimization process speeded up six-fold. ie the optimization time for a portfolio will now be one sixth of the previous time.

     White Dot