Click here to Skip to main content
15,867,771 members
Articles / Programming Languages / SQL

Sharpe Ratio and Sortino Ratio for a Portfolio in SQL

Rate me:
Please Sign up or sign in to vote.
3.42/5 (10 votes)
23 Apr 2006CPOL5 min read 50.6K   38   1
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.

Sharpe Ratio on the Efficient Frontier of a CAPM Portfolio

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.

SQL
-- 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, _
	@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

-- Retrieve the latest timestamp, used for annualized form
SELECT TOP 1 @lastClose = ut_account_balance, _
	@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

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

SQL
-- 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 / _
	currentTrade.ut_account_balance - 1)
FROM usertrade as currentTrade
INNER JOIN usertrade as lastTrade ON
lastTrade.ut_close_timestamp = 
(SELECT TOP 1 subTrade.ut_close_timestamp FROM usertrade AS subTrade
WHERE subTrade.ut_us_user_id = @userID AND
subTrade.ut_close_timestamp IS NOT NULL AND
subTrade.ut_close_timestamp > @startDate AND
subTrade.ut_close_timestamp < @endDate AND
subTrade.ut_close_timestamp < currentTrade.ut_close_timestamp
ORDER BY subTrade.ut_close_timestamp DESC)
WHERE
currentTrade.ut_us_user_id = @userID AND
currentTrade.ut_close_timestamp IS NOT NULL AND
currentTrade.ut_close_timestamp > @startDate AND
currentTrade.ut_close_timestamp < @endDate

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

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

SQL
-- 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 / _
	currentTrade.ut_account_balance - 1)
FROM usertrade as currentTrade
INNER JOIN usertrade as lastTrade ON
lastTrade.ut_close_timestamp = 
(SELECT TOP 1 subTrade.ut_close_timestamp FROM usertrade AS subTrade
WHERE subTrade.ut_us_user_id = @userID AND
subTrade.ut_close_timestamp IS NOT NULL AND
subTrade.ut_close_timestamp > @startDate AND
subTrade.ut_close_timestamp < @endDate AND
subTrade.ut_close_timestamp < currentTrade.ut_close_timestamp
ORDER BY subTrade.ut_close_timestamp DESC)
WHERE
currentTrade.ut_us_user_id = @userID AND
currentTrade.ut_close_timestamp IS NOT NULL AND
currentTrade.ut_close_timestamp > @startDate AND
currentTrade.ut_close_timestamp < @endDate AND
(lastTrade.ut_account_balance / currentTrade.ut_account_balance - 1) < @threshold

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

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

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.

Further Reading

Financial Modeling of the Equity Market : From CAPM to Cointegration (Frank J. Fabozzi Series)
Financial Modeling of the Equity Market : From CAPM to Cointegration (Fabozzi)

Investments (6th Edition)
Investments (6th Edition) (William Sharpe)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States 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.

Comments and Discussions

 
QuestionSharpe and Sortino In Sql - Function Question Pin
Member 1243884116-May-16 14:19
Member 1243884116-May-16 14:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.