Calculates Annualized Sharpe Ratio and Sortino Ratio for a Portfolio Trading History in SQL

Introduction to the Sharpe Ratio

The Sharpe Ratio is commonly used by hedge funds, mutual funds, managed futures funds, and other money managers as a standardized way of reporting the level of risk the fund is using to achieve its returns. It can also be thought of as ‘reward per unit of risk’ or ‘reward-to-volatility-ratio.

William Sharpe, along with Harry Markowitz and Merton Miller, all shared a Nobel Prize in Economics for introducing and extending the Capital Asset Pricing Model (CAPM). This model distinguishes between systematic (market-wide) and specific risk (asset-specific). William Sharpe’s contribution was what is now called the Sharpe Ratio, a metric that is now used for determining the optimum balance of risk and reward for a portfolio of assets (cash, stocks, funds, etc). The illustration on the right shows the efficient frontier for a portfolio. The slope of the line drawn is equal to the Sharpe Ratio of x.

To find a risk-efficient portfolio, fund managers often find the combination of assets that has the highest Sharpe Ratio.

Traders also use the Sharpe Ratio in both backtesting trading ideas and evaluating trading history.

Why Use SQL?

Transact-SQL provides quite a few simple aggregate functions, such as SUM. AVG. STDEV. etc. For purposes of reuse and encapsulation, having a SQL user defined function that calculated the Sharpe Ratio will allow us to use this algorithm in a variety of applications. Of course, we could also implement this in C++ or C# or any other language, but it gets a little more interesting when we use SQL.

The Algorithm

The Sharpe Ratio is defined as the portfolio’s annualized return less the risk-free rate, divided by the portfolio’s volatility. So, the algorithm is made up of essentially two parts:

Excess Return

What is meant by excess return is simply the return of a portfolio that is above and beyond what an investor would make if he simply held the investment in an account bearing the risk-free rate. Usually 90-day T-bills are used as the best proxy for this theoretical interest rate.

Because the Sharpe Ratio is almost always given in an annualized form, we will use the ACT/365 day count convention to convert excess return to an annualized form.

In the below SQL, we assume the existence of a table usertrade. which contains the timestamps for when a position was opened and closed, and other relevant data about a trade. Because we can retrieve the profit or loss amount and the balance of the account, we will assume that this is available to our algorithm.

In the above code, I write two separate SELECT statements to retrieve the earliest and latest timestamp. I could have easily incorporated this into the final SELECT. It would, however, slow down the final SELECT. It is also more readable this way, which is always a good thing.

Standard Deviation of Returns or Variance

Transact-SQL provides an built-in aggregate function, STDEV. To calculate the variance of all transactions, we have to do JOIN on the usertrade table (self-join). This join allows us to calculate the percentage change of the account balance between each transaction. Of course, we also annualize this in the end using our simple ACT/365 method.

Now that we have the two components to calculating the Sharpe Ratio, excess return and variance of returns, we can now write a third user defined function that gives us the Sharpe Ratio:

While many money managers, fund managers, and investing websites use the Sharpe Ratio for comparing reward per unit of risk, some practitioners argue that this metric unfairly puts trend-following strategies at a disadvantage. The reason for this is that the Sharpe Ratio’s calculation of variance includes positive returns. Proponents of an alternative metric, the Sortino Ratio, argue that downside deviation is the only type of variance that is harmful to an investment and as such, the metric should calculate variance only on the standard deviation of downside returns.

In thinking of how to implement the Sortino Ratio on top of the work we’ve already done here for the Sharpe Ratio, I thought that it would be ideal if instead of only including downside deviation (less than or equal to zero), but also allowed the user to define a threshold (including positive returns) for comparison to a benchmark.

For the Sortino Ratio, we simply use a separate function for Downside Standard Deviation, which allows us to reuse the Excess Return function. The top-level function for Sortino Ratio is therefore:

Improvements to this Implementation

If you look closely at my rate of return calculation, you will notice that it is a simple rate of return. The reason I chose this method is because my own implementation will never be calculated for positions held over 1 year. For portfolios that are held over many years, substituting average rate of return would be more accurate.

Additional Points of Interest

Both the Sharpe Ratio and the Sortino Ratio can be effective measures of risk for a portfolio of assets. The inclusion of the Risk-free Rate and using a Threshold value for the Sortino Ratio make these calculations robust and practical for real-world CAPM modelling.

I look forward to the community’s comments on this implementation of the Sharpe and Sortino ratios in Transact-SQL. I am still learning more about CAPM and other models that are useful in capital and equity markets. Some of the more interesting things I’m currently learning include stochastic calculus, PDEs (partial differential equations), option pricing, & risk management. Getting these things out in a form that other people can hopefully understand is a way for me to concretize the concepts I’m learning.