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.
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
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
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
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.
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
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
DECLARE @YearPart real
SELECT @YearPart = ((DATEDIFF(d,@firstDate,@lastDate) / 365))
SELECT @YearPart = @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:
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.
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
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
DECLARE @YearPart real
SELECT @YearPart = ((DATEDIFF(d,@firstDate,@lastDate) / 365))
SELECT @YearPart = @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:
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