12,077,911 members (50,152 online)
Article
alternative version

37.8K views
38 bookmarked
Posted

# Sharpe Ratio and Sortino Ratio for a Portfolio in SQL

, 23 Apr 2006 CPOL
 Rate this:
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.

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

```-- Calculate Excess Return
CREATE FUNCTION [dbo].[udfExcessReturn]_
(@userID int, @startDate datetime, @endDate datetime, @riskFreeRate decimal(8,4))
RETURNS decimal(18,8) AS
BEGIN

DECLARE @firstClose Decimal(18,4)
DECLARE @lastClose Decimal(18,4)
DECLARE @firstDate datetime
DECLARE @lastDate datetime

-- Retrieve the earliest timestamp, used for annualized form
SELECT TOP 1 @firstClose = ut_account_balance, _
WHERE ut_us_user_id = @userID AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp > @startDate AND
ut_close_timestamp < @endDate
ORDER BY ut_close_timestamp

-- Retrieve the latest timestamp, used for annualized form
SELECT TOP 1 @lastClose = ut_account_balance, _
WHERE ut_us_user_id = @userID AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp > @startDate AND
ut_close_timestamp < @endDate
ORDER BY ut_close_timestamp DESC

-- calculate Day Count Fraction, ACT/365
DECLARE @YearPart real
SELECT @YearPart = ((DATEDIFF(d,@firstDate,@lastDate) / 365))
SELECT @YearPart = 1/ @YearPart

RETURN POWER(@lastClose / @firstClose,@YearPart) -1 - @RiskFreeRate

END```

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.

```-- Standard deviation of return
CREATE FUNCTION [dbo].[udfStdDevReturn] _
(@userID int, @startDate datetime, @endDate datetime)
RETURNS decimal(8,4) AS
BEGIN

DECLARE @returnNumber decimal(8,4)
DECLARE @firstDate datetime
DECLARE @lastDate datetime

SELECT TOP 1 @firstDate = ut_close_timestamp FROM usertrade
WHERE ut_us_user_id = @userID AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp > @startDate AND
ut_close_timestamp < @endDate
ORDER BY ut_close_timestamp

SELECT TOP 1 @lastDate = ut_close_timestamp FROM usertrade
WHERE ut_us_user_id = @userID AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp > @startDate AND
ut_close_timestamp < @endDate
ORDER BY ut_close_timestamp DESC

-- Standard deviation of percentage difference between two consecutive trades (return)
SELECT @returnNumber = STDEV(lastTrade.ut_account_balance / _
WHERE

-- we have to annualize this to ACT/365
-- calculate Day Count, ACT/365
DECLARE @YearPart real
SELECT @YearPart = ((DATEDIFF(d,@firstDate,@lastDate) / 365))
SELECT @YearPart = @YearPart
--return @YearPart
SET @returnNumber = @returnNumber * SQRT(@YearPart)

RETURN @returnNumber

END```

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:

```-- Sharpe Ratio
ALTER FUNCTION [dbo].[udfSharpeRatio] (@userID int, @startDate datetime, _
@endDate datetime, @RiskFreeRate decimal(18,8))
RETURNS decimal(8,4) AS
BEGIN
return dbo.udfExcessReturn(@userID,@startDate,@endDate, @RiskFreeRate) / _
dbo.udfStdDevReturn(@userID,@startDate,@endDate)
END```

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.

```-- Downside Standard deviation of return ( < threshold)
CREATE FUNCTION [dbo].[udfDownsideStdDevReturn] _
(@userID int, @startDate datetime, @endDate datetime, @threshold decimal(18,8))
RETURNS decimal(8,4) AS
BEGIN

DECLARE @returnNumber decimal(8,4)
DECLARE @firstDate datetime
DECLARE @lastDate datetime

SELECT TOP 1 @firstDate = ut_close_timestamp FROM usertrade
WHERE ut_us_user_id = @userID AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp > @startDate AND
ut_close_timestamp < @endDate
ORDER BY ut_close_timestamp

SELECT TOP 1 @lastDate = ut_close_timestamp FROM usertrade
WHERE ut_us_user_id = @userID AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp IS NOT NULL AND
ut_close_timestamp > @startDate AND
ut_close_timestamp < @endDate
ORDER BY ut_close_timestamp DESC

-- Standard deviation of percentage difference between two consecutive trades (return)
SELECT @returnNumber = STDEV(lastTrade.ut_account_balance / _
WHERE

-- we have to annualize this to ACT/365
-- calculate Day Count, ACT/365
DECLARE @YearPart real
SELECT @YearPart = ((DATEDIFF(d,@firstDate,@lastDate) / 365))
SELECT @YearPart = @YearPart
--return @YearPart
SET @returnNumber = @returnNumber * SQRT(@YearPart)

RETURN @returnNumber

END```

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:

```-- Downside Standard deviation of return ( < threshold)
CREATE FUNCTION [dbo].[udfSortinoRatio] (@userID int, @startDate datetime, _
@endDate datetime, @RiskFreeRate decimal(18,8), @threshold decimal(18,8))
RETURNS decimal(8,4) AS
BEGIN
RETURN dbo.udfExcessReturn(@userID,@startDate,@endDate, @RiskFreeRate) / _
dbo.udfDownsideStdDevReturn(@userID,@startDate,@endDate, @threshold)
END```

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

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.

 Investments (6th Edition) (William Sharpe)

## Share

 Web Developer United States
Andrew Peters is a systems developer interested in non-trivial trading systems and financial systems architecture. He is currently focused on realtime, high performance multi-threaded applications running on the server and the desktop.

After a 4 year stint in China learning Mandarin and Tibetan, Andrew returned to the US to learn more about enterprise development and financial markets. While in China, he translated meetings between demure Communist officials and angry American businessmen, served coffee and fetid tofu in his 'BaiSuiFang' Coffee Shop, started Fabrefactum Software and was generally laughed at for his stupid jokes in Chinese.

He currently helps the pricing/analytics team hack on code at Chatham Financial, an interest rate and foreign exchange derivative consulting company.

## You may also be interested in...

 -- There are no messages in this forum --
Web03 | 2.8.160212.1 | Last Updated 23 Apr 2006