The latest version of the Finance Add-in for Excel is You can download the full version free of charge for one year from date of purchase. The latest versions of other applications that use the add-in are:. Portfolio Optimizer Personal 3. See Portfolio Optimizer changes. Options Strategy Evaluation Tool OSET: Adjusted closing prices from Yahoo: Yahoo has recently stopped adjusting closing prices for dividends. Until this problem is corrected by Yahoo, adjusted closing prices will optionally be calculated for historic prices downloaded from Yahoo.

Update 10 June Yahoo has started providing adjusted prices again. The add-in will automatically use the Yahoo adjusted price if present. Yahoo historical prices download: Update to handle changes made by Yahoo to their historical prices download service. New versions of the Personal edition version 3. 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. 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.

Enfusion

Interactive Brokers IB streaming quotes bug fix: Fixed bug introduced in version Interactive Brokers IB bit compatibility release: IB streaming quotes and option chains will now run under Excel bit.

Requires IB API version 9. IB released version 9. Users of the IB data feed in the Finance Add-in for Excel should note that the new API requires version IB users should also ensure they are using TWS build 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 and API 9. For instructions see "Notes on Interactive Brokers" in the option chain section of the function reference manual.

Implied Volatility Calculator 4. 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. Portfolio Optimizer Pro version 5. Incorporation of the Jorion and. Ledoit-Wolf Bayes-Stein shrinkage models into the Portfolio Optimizer to improve covariance estimation.

Option chains from file: Increased the maximum number of options in a chain when importing from a file from 3, to 20, Excel 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 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. Corporate license required for OPM template. Streaming quotes from eSignal, TD Ameritrade and BullSignal: Excel 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 bit and bitincluding Office 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. Correlated simulation using single index model: New function, HoadleySimSingleIndex which can be used to simulate correlated prices for large portfolios. Open Positions Manager version 7 July 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. Style Analyzer version 4. 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.

Update to the HoadleyOXQuotes function to handle a change made by OptionsXpress to their streaming quotes service. Style Analyzer version 3. Added an historical returns chart showing cumulative returns for the fund, style benchmark, and selection active component over time. 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. 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. Update for data provider Eurex to handle some new and amended ISIN codes. 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.

Factor Analyzer version 1. Analysis of drawdowns is now available. 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. Style Analyzer version 2. Drawdown analysis can now be viewed as either log or flat returns. 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.

Principal Component Regression PCR: HoadleyPCR uses the PCR method to minimize the negative impacts of multicollinearity when performing linear regressions. HoadleyMLRcheck checks data for multicollinearity. Correlation and Principal Components Analysis: These functions can now use historical returns. Fixes a problem with the Quandle data provider API documentation which limited the number of Quandl favourites to New feature to enable portfolio weights to be constrained by overall exposure to one or more underlying factors, eg Beta.

Correlation matrices from factors: Two new functions for the construction of correlation matrices from asset exposures to underlying factors: Particularly useful when the number of assets exceeds the length of available price histories. 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. 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.

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. 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: MSN option chains are no longer available due to changes at MSN.

Quandl has moved to a secure HTTPS API and will be phasing out the less secure HTTP API in No user changes required other than updating to version Three additional functions to support risk-based portfolio construction: HoadleyMDP for allocation using the Most Diversified Portfolio model; HoadleyMVP for constructing long-only minimum-variance portfolios; HoadleyPCARisk to analyse the extent to which a portfolio is diversified across underlying uncorrelated risk factors.

This function can also be used to construct Diversified Risk Parity DRP portfolios where risk is allocated evenly across underlying risk factors rather than across assets. Risk diversification additional samples: A new additional samples spreadsheet, which illustrates several key principles of portfolio risk diversification, is available for download. Portfolio Optimizer Pro Version 4. Graphical analysis of historical trends and stability over time for beta, volatility, prices and correlations.

New data analysis functions: Three new functions for analyzing historical correlation trends using a rolling windowand for calculating arithmetic and geometric returns from historical prices or returns. 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. 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. New release will retrieve historical data from Quandl. Includes access to users' Quandl favourites if set up to streamline dataset selection.

Excel bit compatibility issue: Historical data from Yahoo: Update to handle a recent format change at Yahoo Finance that prevented download of historical data in some situations. Recently IB changed the contract specifications for a small number of US stocks MSFT, CSCO, INTC 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. New auto-selection of clusters feature complements existing interactive cluster selection. Correlation Clustering Analyzer version 2. New features in version 2. 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. New additional samples spreadhseet illustrating the valuation of portfolios using CPPI, comparing CPPI with Option Based Portfolio Insurance OBPICPPI simulation and more.

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.

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. Retirement Planner Version 2. Release date 8 September 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.

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.

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. 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. 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: Can be used to produce more meaningful volatility surface charts etc. 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. 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. 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. 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.

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.

Four utility functions to convert nominal rates and risk premiums to their real equivalents and vice-versa. VaR additional samples spreadsheet version 2. 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 On some configurations running Excelthe Open Positions Manager causes Excel to crash. Earlier versions of Excel not affected. The latest version of the OPM should work around these Excel stability issues. Log in to download. As a result of recent IB API changes retrieval times for option chains have been significantly reduced. 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 Without this upgrade mini options may return zero values for bid, ask and last. 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 A in version 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. The Finance Add-in for Excel, and all applications and samples have been updated to provide full compatibility with the release version of Excel bit and bitincluding Office Home Premium.

Note that Excel 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 The latest version of the Finance Add-in has been optimized for SDI when running under Excel and for MDI when running under earlier versions of Excel.

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.

Pricing Cross Currency Swaps

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

Release of new application for analyzing correlation matrices using hierarchical cluster analysis. 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. 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.

New function for the valuation of standard, and forward start delayed start cross-currency swaps. 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.

Black-Litterman Returns Estimator application: Release of a new application for estimating investment portfolio returns using the Black-Litterman model.

Portfolio Optimizer Mean CVaR edition version 1. Fixed error which caused Excel to crash under some conditions. New example added to the additional samples spreadsheet. Existing samples enhanced and explanatory text added. Implied Volatility Calculator version 4. The number of options in a chain which can be analyzed as a group has been increased from 2, to 4, 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. 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 bit compatibility release: The Finance Add-in for Excel is now compatibile with the bit edition of Excel.

For details, including exceptions, see systems requirements. Portfolio Optimizer Pro version 3. Introduction of the geometric mean return efficient frontier model capital growth model. New sample spreadsheet which compares and contrasts efficient frontiers based on arithmetic mean variance optimization MVO with those based on geometric MVO. Release date 25 June Release of Mean-CVaR Portfolio Optimizer: Individual asset return distributions and their dependency structure are specified using any of the copulas supported by the Add-in.

Finance Add-in for Excel version Update to provide additional functionality required by the Mean-CVaR Portfolio Optimizer. VaR Simulator version 1. Minor update to remove protection from a few incorrectly locked cells. Release date 12 May Implementation of three risk-based asset allocation models: Minimum variance, maximum diversification, and risk parity. 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.

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

Portfolio Optimizer with Transaction Costs Release date 14 February 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.

OptionsXpress streaming futures and futures options quotes: Streaming quotes for futures and futures options are now available for OptionsXpress customers. New function, HoadleyEngleGranger for testing multivariate time series for cointegration using the Engle-Granger methodology. Returns comprehensive statistics to aid interpretation of results. 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.

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. 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. Two new functions to simplify the aggregation of linear VaR and CVaR from sub-portfolios eg business units. 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.

The implied volatility functions now check for unrealistically large implied volatilities caused by bad market data, and will return in these cases. These "bad" volatilities sometimes caused overflow errors in the Implied Volatility Calculator. Release of a set of eleven functions for the calibration of copulas, and simulation of returns and prices using copulas GaussianStudent 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. Value at Risk VaR simulation component: 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.

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.

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: Update to handle recent format changes which caused a problem retrieving the underlying prices for some assets.

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

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. Additional trades in the Options Strategy Evaluation Tool OSET: The number of legs per strategy in OSET has been increased from six to ten. A new version of OSET is also available. 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 TSRagainst a peer group.

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.

US Option chains requested through MSN: 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.

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. 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. Two new examples have been write poems and make money to the VaR additional samples spreadsheet which illustrate the use of FHS.

Monte Carlo simulation and FHS are compared using the same historical dataset. 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.

Fixed bug, introduced in last release, which caused the quotes wizard to fail on loading in some situations. Yahoo Quotes subroutine HoadleyGetQuotes: 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 forex chart reviews. MSN has recently started incorrectly classifying some puts as calls. A new version of the eSignal control dll is forex rates uae to fix this problem.

Corrected error, introduced in version 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.

Marketfeed has corrected a bug in their data API which caused binary option trading indicators 5 minutes quotes to halt with an error in some situations.

The latest version of IB's TWS build 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 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 job after msc in computer science compatibility with the release version of Excel 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. Snapshot quotes now refresh much faster when "refresh" is clicked on the toolbar. Marketfeed has fixed an issue which caused incomplete option chains to be returned for indices and SPY.

Update to handle a change made by OptionsXpress to their data API. The Insert Function command from the Hoadley menu has been updated to remove minor issues that occur under various combinations of Excel and Windows. 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. Component Value at Risk VaR: A new function, HoadleyComponentVaRhas been added to the VaR Tools 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: 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: 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. The maximum number of options that can be handled in a single chain has been increased to handle some very large futures option chains intraday pivot points trading system 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.

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

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

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.

Fix for Excel and Excel bug in latest Microsoft security patch: The security patch released by Microsoft on 11 November KB for Excel and KB for Excel 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. Update to work around the above issue plus another bug fix.

Risk adjusted portfolio analysis using the M3 methodology: New function, HoadleyM3, which calculates the proportions of an active portfolio, a scottrade download stock data 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. Active Portfolio Management - Adjusting Beta: 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 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. New utility function HoadleyInterpolation for the estimation of missing numbers eg in a yield curve using either linear or cubic spline interpolation. Update to handle changes at the Euronext data provider.

Previous versions of the add-in will not work with Euronext after 30th June Markets supported now include Amsterdam as well as London, Paris, and Brussels. Option chains equities and and FTSE MIB from the Italian Stock Exchange have been added to the list of data providers.

Cross Currency Swap Pricing with VBA • The smile of Thales

Chains stock ford rims f250 be requested by exchange code or ISIN. Borsa Italiana has been added to the list of data providers. 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.

Fixed a problem when running with some non-English regional settings in Windows which caused the decimal point barclays stockbrokers reviews the underlying price to be in the wrong place. Excel SP 2 compatibility release: Works around several bugs in Excel service pack 2 released on 24 April which caused a chart in the Historic Volatility Calculator and a chart the Style analyzer to display incorrectly.

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.

Tax-adjusted portfolio optimization examples: Fixed minor problem, introduced with version 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. 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. 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 which stocks to buy for long term investment india 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.

Portfolio Optimizer pro version 3. 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. Update to handle an option chain format change at the Australian Stock Exchange. A problem which caused the function to return an error code when interesting facts forex trading returns for a period were negative has been fixed.

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 binary option diary 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 to return no options.

National Stock Exchange of India NSE index option chains: Option chains for indices NIFTY, MINIFTY, CNX, 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. New function, HoadleyRiskAttribution, to calculate the contribution and marginal contribution of individual assets and asset groups eg industries, countries 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.

New risk attribution analysis for optimal portfolio. Yahoo quotes function -- automatic refresh: The HoadleyYahooQuotes function will now automatically refresh quotes at a frequency determined by the user. 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 alpha ea forex 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 forex book review calibrate the SABR parameters with market data: Portfolio Optimizer Pro version 2. Option chain filtering to exclude non-standard options: Traffic racer earn money of option chains has been introduced to enable non-standard state street global markets equity research to be excluded from option chains.

For example, US Fixed Return Options FROs introduced in May and traded on AMEX should be excluded from option chains used in the Implied Volatility Calculator and in Easiest way to earn zeny in ragnarok 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.

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. Release of version 2. 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. Problem downloading option chains from MSN on PCs with French regional settings has been fixed.

Update to handle changes made by Marketfeed to their web infrastructure. Users of Marketfeed need to move foreign exchange rates forex version Performance measures based forex swing trading strategy pdf 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.

Portfolio Style Analyzer version 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. Update to Historic Volatility Calculator version 6.

If you have applied this Microsoft update to Excel and the Historic Volatility Calculator cara ikut forex online longer works or runs very slowly then version 6. Historic Volatility Calculator now runs significantly faster under all versions of Excel. Conditional Value at Risk CVaR: Optimal exercise boundary american put option, 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. Employee stock options Introductory broker forex 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 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 yieldsindices, 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 Interactive Brokers added as a data provider. The maximum size of an option chain handled has been increased from 1, to 2, options. HoadleyDownsideDeviation to calculate downside deviation, and HoadleyDownsideCorrel to calculate the downside correlation matrix for two or more assets.

Using this methodology, the don godwin stockbroker of downside deviation and correlation -- ie semicovariance -- can be mcdonald stock buy or sell in standard Markowitz mean variance optimizers, like the Hoadley Portfolio Optimizerto 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. This enhancement is designed to make currency swap valuation excel functions more convenient to use in certain situations.

Note that no applications that use this function like the Portfolio Simulator or the sample spreadsheet were affected by this issue. Style analyzer version 2. New function to calculate the volatility of a portfolio containing a mixture of domestic and foreign assets.

Various minor documentation and menu auto binary options trading signals franco enhancements. Update to handle format change at Yahoo Finance. Note that the GetQuotes subroutine was not affected by the Yahoo format change. 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. Update to handle changes to the OptionsXpress US option chain data format. This is an essential update best forex robot comparison 2014 OptionsXpress users.

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.

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: Release of version 1. Update to the Portfolio Monte Carlo Simulation class re handling of short positions.

Portfolio Monte Carlo Simulation: 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.

Release of the Hoadley Portfolio Simulator application. Excel 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 service pack 1. It also corrects a problem in 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.

New function, HoadleyDrawdown, will analyze the drawdown history of a fund or portfolio. One or more drawdowns within the analysis period can be call option taxability eg the maximum drawdown and recovery dates, and forex price pattern trading second largest drawdown and recovery datesand canadianforex review is returned to enable the plotting of drawdown "under water" charts.

New release of the Hoadley Style Analyzer, which includes a drawdown analysis of the fund or portfolio. 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.

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

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 buying stocks on the dips 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: All statistics are produced by individual asset and by portfolio. Portfolio Optimizer Pro version 1. A new release of the Portfolio Optimizer Pro which produces comprehensive active portfolio management statistics. 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. A new version of OSET has been released which includes MSN as a provider. Fixed problem on 3D volatility surface graph where the expiry month axis descriptions sometimes forex market opening times not fully displayed. 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. 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. 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. It's also now necessary to log into their site before retrieving option chains. Portfolio Optimizer Pro 1. New function, HoadleyEfficientFrontierwill optimize a portfolio and produce the efficient frontier. The optimal tangency portfolio is also returned by the function. 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. 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.

View the Black-Litterman tutorial. A how to earn money in lakhs 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.

Individual assets on efficient frontier graph. Improved optimization performance, and the flexibility of graph scaling. 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. 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 Forex trading tips and tricks file. This facility is useful for analysing volatility on assets for which Yahoo Finance does not provide historical data.

The date format local or US and the column numbers containing dates and prices can be specified by the user. 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.

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. New function HoadleyVolatilityCone for plotting volatility cones. Volatility cones can help determine whether current implied volatility eg from the Implied Royal mail share price equiniti Calculator is high or low compared with historical volatility measured over the same periods.

New version which includes volatility cones. This version also fixes some minor cosmetic issues introduced with the Excel compatibility release. Versions of the add-in since 9. Versions prior to that date may not run correctly under Windows Vista. Enhancement to take advantage of key features of the Excel ribbon bar. 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 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 Two functions to simplify the data management and computation issues associated with calculating value at risk VaRpreparing 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.

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. 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: 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. 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: Requires full version of add-in.

A new version of the Portfolio Optimizer version 3. The number of assets has also been increased from 30 to For details see Portfolio Optimizer change details. Indexed employee stock options: This function is only available with a commercial license. Update to handle recent changes to the Eurex option chain data format. Four new functions for valuing regular, single barrier and Asian Quanto options: The functions handle both European and American exercise.

Various changes made to the help system and other areas to ensure Vista compatibility. 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. New application included with the add-in to produce probability cones showing in visual form the most probable range of future asset prices.

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. 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. New function, HoadleySpreadOption, for the valuation, hedge parameters, and implied correlation for options on the price differential between two assets. Uses a modified Black model for European options and Rubinstein's three dimensional binomial trees for American options. 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. 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: Implied Volatility Calculator -- version 3. 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. New version of the Implied Volatility Calculator containing two enhancements:. 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.

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.

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: 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. 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: VaR currency rebasing of volatilities and correlations: Two new functions in VaR tools 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. 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.

This function can be found under utilities in the add-in. Both measures can be calculated using either equally weighted or EWMA models. All users of the Marketfeed data service should upgrade to version 9.

Employee stock option enhancement: The HoadleyESO4 function will now take account of recurring blackout periods during which employees may not exercise their options. 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.

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. The forecast volatility sheet has been enhanced to show both instantaneous and "average" volatilities on the term structure chart. Changes to option chain component to handle server changes at Marketfeed.

Users of the Marketfeed data service should upgrade to release 9. Implied volatility calculator 2. 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.

Finance Add-in for Excel | Hoadley

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. New version release date 10 May which fixed a small bug, introduced in the version dated 19 April which didn't split the screen correctly on the Positions Analysis sheet.

A new version of the Implied Volatility Calculator 2. 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. New version release date 19 April which increases the number of trades which can be simultaneously displayed on the positions analysis sheet from 19 to 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.

UK streaming quotes from MoneyAM: Function updated to handle small change with MoneyAM protocol. Employee Stock Options ESOs: 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 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. None of the add-in's implied volatility functions are affected by this, only the Implied Volatility application. 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.

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.

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. Implied Volatility Calculator version 2. 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: Previously a blank exchange defaulted to the CBOE exchange. 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. Eurex support added to the on-line option chain component: 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 or above -- users of Excel 97 or Excel 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. 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. 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. 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 chain component updated to handle minor change in CBOE data formats.

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. Hedging optimization functionality added to the implied volatility calculator version 1. The application will now scan an entire option chain to select the optimal combination of trades to meet specific hedging objectives within specified constraints.

New component which can be used to retrieve option chains into spreadsheets. Exchanges currently supported include all US exchanges using CBOE as the providerThe Australian Stock Exchange, Montreal Exchange, London Exchange and the Paris Exchange.

Data is free and delayed 15 minutes. 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. 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.

Adjustment can be made using only options, or by using the underlying plus options. The Options Strategy Evaluation Tool version 24 April and Open Positions Manager version 5 May now use the hedging function for position hedging.

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. Time zone specification for HoadleyGetQuotes function: 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.

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.

Changes to improve execution under Excel eg elimination of spurious Excel pop-up warnings. 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. Volatility calculator enhanced to take advantage of the closing price data adjusted for splits and dividends which is now being provided by Yahoo.

Enhanced to take advantage of the "adjusted close" data from Yahoo. Options on a portfolio "basket" of assets.

Analytic computation used ie not simulation. 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: VaR tools expanded to include functions for producing correlation and covariance matrices using the EWMA model. Introduction of VaR tools -- 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 optionscash flow mapping for interest rate instruments, correlation and covariance matrix construction, portfolio volatility estimation and more. Adjusted closing price calculation: Adjusted closing prices will. 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 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. To use, click "Factor Constraints" button on optimize sheet. Portfolio Optimizer Pro version 4. Portfolio Optimizer Mean-CVar edition, version 1. Introduction of portfolio optimization based on the geometric multi-period model. The user can now select either the geometric return model known as the capital growth model or the standard arithmetic return model when producing an efficient frontier.

The portfolio which produces the highest geometric return is now produced by the optimizer. A sample spreadsheet which illustrates the principles of both optimization models is available for download. Mean-CVar Portfolio Optimizer version 1. 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 Importing data from non-Yahoo sources: Portfolio Optimizer with Transaction Costs version 1. 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.

The installation procedure for the personal edition of the Portfolio Optimizer has been simplified. Value at Risk VaR analysis: Fix to Excel and Excel bug caused by Microsoft security patch: Fixed a bug introduced in verson 3.

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

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.