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

Implied Volatility Calculator: 4.0k (included with full version of the add-in)

Portfolio Optimizer Personal (3.3b) and Pro (3.3b): (separate download). See Portfolio Optimizer changes.

Portfolio Style Analyzer: 2.3b (separate download)

Portfolio Simulator: 1.1c (separate download)

VaR Simulator: 1.0a (separate download)

Retirement Planner: 1.0b  (separate download)

Open Positions Manager: 15 June 2010 (separate download).

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

VaRtools Samples 2.5 (separate download)

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.

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

OptionsXpress user name and password entry has been streamlined.

Interactive Brokers (IB)  API version 9.66 compatibility release.  IB has recently released version 9.66 of its API (dated 30 November 2011 but released this week).  Users of the IB data feed in the Finance Add-in for Excel should note that the new API (version 9.66) requires version 10.4u (or later) of the add-in and version 10.4u (or later) of the add-in requires version 9.66 of the API.  ie both the Finance Add-in for Excel and the IB API must be upgraded in tandem. IB users should also upgrade to the latest version of TWS (build 922.6 or later).  See the Finance Add-in help file notes on IB for more information

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.

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 conveniently arranged and function is much faster.

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.

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.

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.

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

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

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.

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.

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

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.

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.

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

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).

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).

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

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.

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.

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.

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

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).

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.

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

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.

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.

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.

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.

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

Interactive Brokers (IB)  API version 9.64 compatibility release.

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.

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.

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.

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.

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.

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

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.

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

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.

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

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

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).

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.

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.

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.

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).

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.

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

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.

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

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.

Interactive Brokers (IB)  API version 9.63 compatibility release.

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).

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). 

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..

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.

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.

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

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.

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.

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).

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.

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.

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.

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.

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.

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

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. 

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.

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.

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

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.

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.

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

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.

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.

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

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.

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

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.

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

Portfolio Optimizer Pro version 3.2.  Details.

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

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.

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.

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

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

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

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.

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

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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).

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

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.

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

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.

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.

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).

Interactive Brokers (IB)  API version 9.60 compatibility release.

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.

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

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.

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

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

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.

Details.

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.

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.

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

	Saving and retrieve optimization scenarios from the optimize sheet.
	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.

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

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

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.

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.

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

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.

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

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.

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

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.

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

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.

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.

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.

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.

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).

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.

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.

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

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.

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

Various minor documentation and menu grouping enhancements.

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

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

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.

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

Hoadley Retirement Planner: Release of the Retirement Planner application.

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.

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.

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

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

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

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.

Portfolio Simulator:  Release of the Hoadley Portfolio Simulator application.

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.

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).

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.

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

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.

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.

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.

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.

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.

Improved error checking for the HoadleyPortfolioStats function.

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.

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

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

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.

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

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. 

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.

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.

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..

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

Stability improvements and additional error checking for the HoadleyEfficientFrontier function.

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.

Portfolio Optimizer Pro 1.1. See details.

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).

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

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

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.

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

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

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.

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.

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.

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.

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. 

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.

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.

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

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.

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

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

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.

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

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.

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).

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.

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.

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

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.)

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.

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.

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.

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.

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

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. 

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.

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. 

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.

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

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.

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

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.

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.

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).

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.

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.

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.

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.

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.

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.

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

Implied Volatility Calculator -- version 3.2: 

	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.
	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.

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

	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.
	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.

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).

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

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.

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.

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.

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.

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

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

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.

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.

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.

Historical price download from Yahoo:

	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.
	Component for use in a VBA module for downloading history under program control.

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).

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. 

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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

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.

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.

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.

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.

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

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.

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

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.

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.

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.

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.

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

Option chain class updated to handle to ASX data changes.

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.

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. 

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

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).

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.

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

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).

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.

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

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.

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.

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

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.

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.

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.

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

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

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.

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.

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).

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.

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.

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. 

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.

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.

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.

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

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.

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. 

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.

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

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

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.

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.

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

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

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

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.

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

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.

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.

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.

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.

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.

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 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).

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.

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

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.

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

	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.
	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.

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

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).

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.

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

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.

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.

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).

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.

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.

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

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

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").

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.

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.

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.

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

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.

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

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

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

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.

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

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