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

Implied Volatility Calculator: 4.6g (included with full version of the add-in)

Portfolio Optimizer Personal (3.5i), Pro (5.3e), Portfolio Optimizer with Transaction Costs (1.1d) and Mean-CVar Portfolio Optimizer (1.0f): (separate download). See Portfolio Optimizer changes.

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

Style Analyzer: 4.0f (separate download)

Portfolio Simulator: 1.1h (separate download)

VaR Simulator: 1.0e (separate download)

Correlation Clustering & Hierarchical Asset Allocator 3.0b (separate download)

Factor Analyzer 2.2a (separate download)

Retirement Planner: 2.0a  (separate download)

Open Positions Manager: 1 August 2021 (separate download).

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

Yahoo quotes functions:  Update to the HoadleyYahooQuotes and HoadleyYHStreamQuotes functions to handle changes with the Yahoo Finance data feed.

Yahoo quotes functions:  Update to the HoadleyYahooQuotes and HoadleyYHStreamQuotes functions to handle changes with the Yahoo Finance data feed.

Option chains on the Montreal Exchange: Update to handle changes with the Yahoo data feed which caused option chains to fail when using MX as the data provider.

Option chains for Euronext options: -- Option chains for options traded on Euronext are now available.  Option chains are free of charge and delayed. Provider code is Euronext.

Option chains on Canadian equities and ETFs: -- Option chains for Canadian options traded on the Montreal Exchange are now available.  Option chains are free of charge and real-time. Provider code is MX.

Eurex option chains: Update to handle recent data provider changes.

Historic Volatility Calculator version 7.4b:  Fixed issue where the screen did not refresh after retrieving data. This issue, which only affects some Windows/Excel configurations, appeared after a recent update to Excel 365.

Partial Index Replicator application version 1.1b:  Matching algorithm efficiency improvements; fixed screen refresh issue..  Application details

S&P500 Returns Analyzer version 3.4: Will now use a backup dataset if the Shiller web site is unavailable.  Application details

Crypto option chains on Binance exchange:  Update to handle changes with the Binance data service (API).

Stockwatch Canadian option chains: Fixed bug with expiry dates on Canadian weekly options.

Partial index replication function:  New function, HoadleyIndexTrack, to build an index (or ETF) tracking portfolio from a subset of candidate assets. The maximum number of assets to be included in the index tracking portfolio can be specified. Details

Partial Index Replicator application:  New application to simplify the construction of an index-tracking portfolio.  Details

Style Analyzer version 4.0f:  Fixed graph scaling issues which sometimes occurred with daily historical returns.

Correlation Clustering & Hierarchical Asset Allocator -- version 3.0:  Major enhancement to asset allocation/portfolio construction capabilities.  The application now implements a Hierarchical Risk Parity/Equal Risk Contribution model for diversifying risk within and across clusters.  Detailed schedule of weights and risk contribution by asset, cluster and portfolio produced.  Application details

Eurex option chains: Update to handle recent data provider changes which caused incorrect data for bid and ask to be returned when using Eurex as the data provider.

Streaming quotes from Yahoo Finance (free): New function, HoadleyYHStreamQuotes, provides streaming quotes for most exchanges.  Whether quotes are delayed, real-time, streaming or snapshot only, depends on Yahoo's support for each exchange.  eg Streaming real-time quotes for US stocks are available.  This function complements the existing HoadleyYahooQuotes function which provides snapshot quotes with auto-refresh.  On-line data feeds.

Crypto option chains: Option chains can now be retrieved directly from five crypto exchanges: crypto spot options on Binance, Bit.com, OKX and Delta exchanges; Crypto futures options on the Deribit exchange. The "Provider" when retrieving crypto options is specified as CRYPTO and the exchange is specified in the exchange field. Crypto options can be analyzed by the Options Strategy Evaluation Tool, the Implied Volatility Calculator etc. or in users' own spreadsheets.

Crypto futures option chains: Real-time cryptocurrency option chains for options on Bitcoin and Ethereum futures contracts traded on the Deribit Crypto Options and Futures Exchange are now available (free to use).

TD Ameritrade streaming quotes:  Strike, expiry date, and option type are now available through the HoadleyTDQuotes function.

Employee Stock Option (ESO) with variable expiry date: A template is now available for corporate users to value ESOs where the expiry date is dependent on the date at which the share price remains above a target price for a specified number of consecutive days at any time during a vesting period. Once the performance target has been achieved the expiry date is set. ESO functions.

Option chains for NSE --  National Stock Exchange of India:  Free option chains for equities and indices are now available using NSE as the data provider.

Options chains through TD Ameritrade: Worked around an issue with the TD Ameritrade datafeed which caused expiry dates to be out by one day.

Streaming quotes wizard: Fixed problem which prevented the streaming quotes wizard from working correctly with non-Microsoft 365 versions of Excel 2016 or later.  Excel 2010, Excel 2013 and Excel 365 were not affected.

Numerous minor usability enhancements.

Historic volatility function - GKYZ model:  Addition of the GKYZ (Garman-Klass-Yang-Zhang) volatility estimation model to the HoadleyHistoricVolatility function.

Historic Volatility Calculator version 7.3: GKYZ volatility is now displayed when using Yahoo Finance as the data provider.

Accumulator function - guaranteed pricing level: A guaranteed pricing level can now be specified with the HoadleyAccumulator function.  Details.

Accumulator pricing:  Valuation of forward accumulator or decumulator contracts.  Details.

ASX option chains: Changes to work around minor data corruption issues with the ASX data provider.

Implied Volatility Calculator version 4.6:  Increased the number of options in a chain that can be analysed simultaneously from 4,000 to 10,000 options. Volatility skew charts, volatility surface charts and volatility cone analysis updated to handle larger chains (eg for AMZN, SPY).

Portfolio factor risk attribution:  New function, HoadleyFactorRiskAttribution, to identify the contribution of individual factors (eg Fama-French factors) to investment portfolio systematic risk. Includes an example of using this, in conjunction with other existing functions, for Risk-Adjusted Performance Attribution across both portfolio assets and risk factors.  Details

S&P500 Returns Analyzer version 3.1:  Enhancements to the Shiller Cyclically Adjusted Price to Earnings (CAPE) ratio returns prediction analysis. Details

Numerous minor function and function example enhancements.

S&P500 Returns Analyzer version 3.0:  This application now includes a flexible tool for analyzing how well the Shiller Cyclically Adjusted Price to Earnings (CAPE) ratio, and the regular P/E ratio perform as predictors of subsequent returns. Details

Polynomial linear regression function: New function, HoadleyPLR, adds polynomial regression to the regression toolset.  Regression functions

Employee stock options: The HoadleyESO2 function and all hybrid models have been enhanced to allow the specification of a payout cap, eg for stock appreciation rights - (SARs). Corporate license required. ESO functions.

Factor Analyzer: The Sharpe ratio is now shown for each asset and for user-defined portfolios.  Factor Analyzer

HoadleyEfficientFronter function and Portfolio Optimizer Pro 5.3b: Fixed a problem with the handling of negative factor constraints.

ASX index option chains: Update to fix problem retrieving index (XJO) option chains following the recent update to the ASX web site. Equity option chains not affected.

Minimum Linear Torsion parity function: The HoadleyMinTorsionParity function has been enhanced to handle larger portfolios.  Risk-based asset allocation

Add-in function wizard improvements: Inserting functions, using "insert function" from the Hoadley tab in the ribbon, which return (recently introduced) dynamic arrays or array formulas across multiple cells has been streamlined.  Clicking "OK" will return either a single-cell formula or a dynamic array (eg a correlation matrix); Entering "ctrl-shift-enter" will return an array formula into cells selected. This applies to Excel 2016 or above.

Eurex option chains: Fixed problem, when using Eurex as the option chain provider, which caused no data to be returned for a number of ISIN codes.

Employee Stock Option (ESO) hybrid relative performance models: Enhancement to enable a minimum TSR hurdle to be optionally specified for the TSR vs index and TSR vs peer group models. Corporate license required.  Relative performance plans

Minor performance and stability tweaks.

Portfolio Optimizer Pro version 5.3a:  Fixed error which caused the Portfolio Optimzer to halt with an error message when the geometric returns for all assets were negative for the period for which data was downloaded.

ASX option chains:  Fixed error which caused volume to be incorrect in some situations when using ASX as the provider.

Employee Stock Option (ESO) relative TSR against peer group valuation: Enhancement to optionally specify that ESOs should not vest if the total return for the period is negative irrespective of the percentile ranking against the peer group of companies. Corporate license required. Details

HoadleyBond function: Fixed bug with yield to maturity and modified duration which sometimes returned zero results for bonds with very long durations (30+years) and negative yields to maturity.

Interactive Brokers datafeed: It is no longer necessary to install the IB API to use IB as a data provider.

Excel 365 recent build issue: An update to the function examples workbook and Historic Volatility Calculator to eliminate full workbook recalculations occurring on loading.  This minor cosmetic issue appeared following a recent Excel 365 update.

Charles Schwab quotes service:  Update to the Finance Add-in for Excel to handle a change of market data vendor at Charles Schwab.  Users of streaming data from Charles Schwab (using the HoadleyCSQuotes function) should update to the latest version of the add-in as the current service will no longer be available after 31st December 2019. Data from Charles Schwab will now be real-time snapshot with optional auto-refresh.

TD Ameritrade quotes & option chains:  Update to handle new user authentication procedures at TD Ameritrade.  Users of TD Ameritrade will need to move to the add-in version 10.8n or later by 31st December 2019.  Streaming futures quotes now available.

S&P500 Returns Analyzer version 2.1: Minor change to historical arithmetic return calculations. Application information

Fixed error in the HoadleyMLR function (multiple linear regression) which occasionally caused Excel to crash.

Factor Analyzer version 2.0:

	Fama-French five factor model: The FF five factor model is now available in addition to the three factor model.
	Performance attribution:  The contribution of all factors to total asset and portfolio returns is now shown for each asset and for the portfolio.

Factor Analyzer overview

Asian options: fixed vega error with Asian options which occurred with a few combinations of inputs.

Tradier Brokerage: Tradier brokerage has been added as a data provider for US equity, option, and index snapshot quotes, streaming quotes and option chains.  More details.

Trade execution through Tradier Brokerage: Option strategies created in the Options Strategy Analysis Tool can now be sent directly to Tradier Brokerage as trade orders.  More details

S&P500 Returns Analyzer version 2.0: Two enhancements to the S&P500 Historical Returns Analyzer: 1. Equity risk premiums (ERPs), both arithmetic and geometric are now included in analyses. 2. New Drawdown analysis.  More details.

CBOE Option Chains: Update to handle recent changes with CBOE data format when using CBOE as the data provider. 

Portfolio Optimizer Pro version 5.3:  Incorporation of the Single Index (single factor) Model into the Portfolio Optimizer to improve correlation matrix estimation in some situations. More details.

Portfolio systematic risk using factor exposures: Two functions for the calculation of systematic risk in portfolios exposed to a single risk factor (HoadleyRiskSingleFactor) or multiple risk factors (HoadleyRiskMultiFactors). See factor models. 

Excel 365 recent build issue: An update to the function examples workbook and Historic Volatility Calculator to eliminate full workbook recalculations occurring on loading.  This minor cosmetic issue appeared following a recent Excel 365 update.

Option chains using Eurex as the provider: Update to handle changes to the Eurex data format.

Option chains using ASX as the provider:  Fixed problem where underlying price was returned as "close" rather than "last".

Option chains using Eurex as the provider: Fixed problem where underlying price was returned as zero in some situations.

Streaming quotes from Schwab: bid size and ask size now available.

Excel 2016 recent build issue: An update to the function examples workbook and Historic Volatility Calculator to eliminate full workbook recalculations occurring on loading.  This minor cosmetic issue appeared following a recent Excel 2016 update.

Release of two complementary utility applications:

	Volatility, Beta, and Correlation Calculator:  Downloads historical data for multiple assets and calculates volatility, beta and correlation. Highlights data issues; handles price histories of unequal length.
	S&P500 Returns Analyzer:  Comprehensive analysis of long-term US equity returns.

More information

Option chains using CBOE as the provider:  Update to handle changes at CBOE.

HoadleyReturnsStambaugh: Fixed bug in the HoadleyReturnsStambaugh function which caused it to sometimes fail to return a result.

Excel 2016 recent build issue: An update to the function examples workbook to eliminate a full workbook recalculation occurring on loading.  This minor issue appeared following a recent Excel 2016 update.

ASX option chains:  Retrieval of option chains from the ASX has been streamlined (simpler; faster).

Historical prices from Yahoo Finance:  Improvements to the synchronization of historical data across multiple time zones. Update to handle changes made by Yahoo to their historical prices service.

Open Positions Manager:  (version dated 22 November 2017) Update to handle the changes to snashot quotes from Yahoo.

Yahoo snapshot quotes function: Update to the HoadleyYahooQuotes function to handle changes at Yahoo Finance.  Also includes some enhanced features: real-time quotes for US and some other exchanges, US option quotes, and some additional data items are available. More information.

Stockwatch streaming quotes company name:  Company name is now available with the HoadleySWQuotes function.

Eurex option chains: Fixed recent problem which caused an underlying price of zero to be returned when using "Eurex" as the data provider.

Stockwatch streaming quotes: Quotes for currencies and some metals (eg gold) are now available.  See function example spreadsheet for symbology.

ASX option chains:   Fixed recent problem which caused an underlying price of zero to be returned for XJO option chains. Equity option chains were not affected by this issue.

Yahoo option chains: Fixed recent problem which caused an underlying price of zero to be returned for some symbols at certain times of the day.

OptionsXpress/Schwab platform conversion:  Futures option chains are now available for Schwab customers who were previously with OptionsXpress. With the release of version 10.7u all features previously available for OptionsXpress customers are now available for OptionsXpress customers who have migrated to the Schwab platform.  More information.

OptionsXpress/Schwab platform conversion: Following the conversion of OptionsXpress customers to Charles Schwab, streaming quotes (using function HoadleyCSquotes), equity and index option chains are now available for Schwab customers who were previously with OptionsXpress.  More information.

Yahoo option chains: Fixed issue which sometimes caused no data to be returned when requesting index option chains using Yahoo as the provider.

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

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.

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.

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.

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.

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.

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

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.

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

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

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

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

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.

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.

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.

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.

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.

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

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.

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

Numerous clarifications to function reference manual.

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.

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.

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. Details.  Corporate license required for OPM template.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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

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. 

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

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.

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

Removed redundant observation_frequency argument from the HoadleyPricesStambaugh function.

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.

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.

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.

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

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

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.

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

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.

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

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.

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.

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.

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.

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.

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.

Interactive Brokers (IB)  API version 9.71 compatibility release.  

Risk-based asset allocation:  Three additional functions to support risk-based portfolio construction: More details.

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

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.

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.

Interactive Brokers (IB)  API version 9.70 compatibility release.

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.

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:

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

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.

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.

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

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.

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

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.

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.

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.

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.

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.

Interactive Brokers (IB)  API version 9.69 compatibility release.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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

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.

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.

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

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

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. 

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.

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

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.

Interactive Brokers (IB)  API version 9.67 compatibility release

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.

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

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

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.

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.

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. 

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

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.

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.

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

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

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.

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

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

Risk-based asset allocation: Implementation of three risk-based asset allocation models..  More details.

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

Column widths have been expanded to accommodate longer option symbols.

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.

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.

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.

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.

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

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.

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.  

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 logically arranged and the 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.

Fixed a problem with the handling of negative factor constraints.

Fixed problem which caused the Portfolio Optimizer to halt with an error when all geometric returns in a portfolio were negative for the period for which data was downloaded.

Incorporation of the Single Index (single factor) Model into the Portfolio Optimizer which can improve correlation matrix estimation in some situations.  Users can now select from the following models when calculating correlation matrices from downloaded data or user supplied data:  Equally weighted, EWMA, Jorion shrinkage, Ledoit Wolf shrinkage, Single Index Model (equally weighted) or Single Index Model (EWMA).

The Single Index models can be optionally selected by clicking the "Populate..." button on the optimize tab.

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

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

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.

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.

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

 Fixed error which caused Excel to crash under some conditions.

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.

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.

Risk-based asset allocation: Implementation of risk-based asset allocation models:   More details.

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

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.

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

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.