* Update – Trading models that were designed earlier using Yahoo! Finance API have been rewritten to retrieve quotes from Bloomberg Markets or an Open Source API. Only US stocks and ETF quotes can be retrieved from the UI model at this time. See setup instructions.
Background on Ulcer Index
Ulcer Index (UI) is a tool for measuring investment risk that addresses maximum drawdown of an asset value, unlike the commonly used standard deviation (SD) of return. UI measures the depth and duration of drawdowns in asset prices from earlier highs.
The index was developed by Peter G. Martin in 1987 and was first published in The Investor’s Guide to Fidelity Funds: Winning Strategies for Mutual Fund Investors. The ulcer index is based on maximum drawdown in an asset value a portfolio experiences over the analysis period. It is a volatility measure that ignores upside movements and captures only the continuous downside volatility. The longer the drawdown, the higher the UI.
Ulcer Index Indicator
Ulcer Index is the root mean square (RMS) or quadratic mean of all drawdown values, D1 through Dn.
|
1 |
<img class="mathtex-equation-editor alignleft" src="http://chart.apis.google.com/chart?cht=tx&chl=UI%3D%5Cfrac%7B%5Csqrt%7BD_1%5E2%2BD_2%5E2%2B...D_n%5E2%7D%7DN" alt="UI=\frac{\sqrt{D_1^2+D_2^2+...D_n^2}}N" width="181" height="55" align="absmiddle"> |
where,
– D is the drawdown value for each slice in the look-back period, and
– N is the number of periods in the look-back period
Note that each Drawdown is a percentage drop from the moving maximum price, so the Ulcer Index can be expressed as a percentage as well. Let’s say we take weekly Drawdowns and pick N=208. That was the last four years of weekly prices taken, since 4*52 = 208.
The algorithm for calculating Ulcer Index is straight forward and can be represented in pseudo code:
|
1 2 3 4 5 6 7 8 9 |
<strong><code>Initialize sumOfSquares Initialize maxValue do if value[T] > maxValue maxValue = value[T] else sumOfSquares = sumOfSquares + sqrt(100 * (value[T] - maxValue) / maxValue) while T <= numberOfPeriods UI = sqrt(sumOfSquares / numberOfPeriods)</code> </strong> |
Ulcer Index in Excel
Ulcer Index Indicator in this spreadsheet is easy to use. Just plug in the symbol, start, and end date ranges. The program performs all the necessary calculations behind the scenes and plots Ulcer Index chart.
The excel spreadsheet in this example builds Ulcer Index on a 14-day day rolling period. Let us take the case of CBST (Cubist Pharmaceuticals) which has been in the news lately. The closing prices have been tracked since Mar-18-2013 to Jan-18-2015 or a 22 month period to study the Ulcer Index model. Note that UI drops below 1 on Nov-02-2014 and then hovers around zero for the remainder of 2014. During this period, the stock price skyrocketed to 100. This could have been primarily due to FDA approval of Cubist’s new antibiotic drug on Dec-19-2014 and the announcement of subsequent takeover by Merck & Co in late 2014.
Measuring Investment Performance
The common method for measuring “investment performance” is to take the ratio of the excess return of an investment by its risk. Excess return is total return minus the return offered by risk free investments such as T-bills. This calculation provides us a number that quantifies both risk and return. It reports the additional return obtained per unit of risk assumed. Typically, the Sharpe Ratio is used, where risk is referred to as standard deviation of return:
|
1 |
<img class="mathtex-equation-editor" src="http://chart.apis.google.com/chart?cht=tx&chl=SharpeRatio%3D%5Cfrac%7BTotalReturn-RiskFreeReturn%7D%7BStandardDeviation%7D" alt="SharpeRatio=\frac{TotalReturn-RiskFreeReturn}{StandardDeviation}" align="absmiddle"> |
Standard Deviation does not cover all the gamut, and is a poor performance measure. This can be improved by simply replacing SD with UI which mainly focuses on downside risks. This new performance measure has been coined as “Martin Ratio” or “UPI” (Ulcer Performance Index).
|
1 |
<img class="mathtex-equation-editor" src="http://chart.apis.google.com/chart?cht=tx&chl=MartinRatio%3D%5Cfrac%7BTotalReturn-RiskFreeReturn%7D%7BUlcerIndex%7D" alt="MartinRatio=\frac{TotalReturn-RiskFreeReturn}{UlcerIndex}" align="absmiddle"> |
The calculation should factor in compounded annual returns, dividends, distributions, and transaction costs for consistency.
Ulcer Index measures risk by factoring in drawdowns represented by price declines. UI is targeted mostly for investors with long positions. The index oscillates around zero when prices record prolonged advances. The index rises when prices move lower from their recent highs. Hence, UI tracks downside risk and can be extended to calculate risk-adjusted returns.
The UI spreadsheet retrieves quote information from Bloomberg OpenMarkets through a lightweight data-interchange format called JSON or JavaScript Object Notification. If this is your first time programming JSON in Excel, you must add certain COM objects to retrieve Bloomberg Markets Web Service through VBA.
Under Developer->Visual Basic->Tools->References, add the highlighted objects.
Spreadsheet works good BUT is password protected.
the link to download doesn’t exist. pls correct
Fixed. The link should be working now.