SHARPE2
Updated: 20 June 2013
Use SHARPE2 to calculate the Sharpe ratio based upon price or valuation data. You have the option of computing the Sharpe ratio using either simple returns or geometric returns. For details on the formulae used to calculate the Sharpe ratio, see the SHARPE documentation. Syntax
Arguments
@PDate
the date associated with the price or valuation. @PDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@PValue
the price or value for the @PDate. @PValue is an expression of type float or of a type that can be implicitly converted to float.
@RF
the risk-free rate. @RF is an expression of type float or of a type that can be implicitly converted to float.
@Scale
the scaling factor used in the calculation. @Scale is an expression of type float or of a type that can be implicitly converted to float.
@Geometric
identifies whether or not to use geometric returns in the calculation. @Geometric is an expression of type bit or of a type that can be implicitly converted to bit.
Return Type
float
Remarks
· If @Geometric IS NULL then @Geometric is set equal to 'False'.
· If @Scale IS NULL them @Scale is set to 1.
· For daily returns set @Scale = 252.
· For weekly returns set @Scale = 52.
· For monthly returns set @Scale = 12.
· For quarterly returns set @Scale = 4.
· To calculate the Sharpe ratio using return data, use the SHARPE aggregate function.
· @Geometric must be the same for all rows in the GROUP BY
· @Scale must the same for all rows in the GROUP BY.
· If there are multiple rows for the same date, the @PValue is accumulated.
· The return values are automatically calculated by putting the @PValue in @PDate order.
Examples
In this example we have price data for IBM and we want to calculate the simple Sharpe ratio assuming an annual risk-free rate of 0.10%.
SELECT wct.SHARPE2(tdate,pr,.001/cast(252 as float),252,'False') as SHARPE
FROM (VALUES
('IBM','2012-12-18',195.69),
('IBM','2012-12-17',193.62),
('IBM','2012-12-14',191.76),
('IBM','2012-12-13',191.99),
('IBM','2012-12-12',192.95),
('IBM','2012-12-11',194.2),
('IBM','2012-12-10',192.62),
('IBM','2012-12-07',191.95),
('IBM','2012-12-06',189.7),
('IBM','2012-12-05',188.65),
('IBM','2012-12-04',189.36),
('IBM','2012-12-03',189.48),
('IBM','2012-11-30',190.07),
('IBM','2012-11-29',191.53),
('IBM','2012-11-28',191.98),
('IBM','2012-11-27',191.23),
('IBM','2012-11-26',192.88),
('IBM','2012-11-23',193.49),
('IBM','2012-11-21',190.29),
('IBM','2012-11-20',189.2),
('IBM','2012-11-19',190.35),
('IBM','2012-11-16',186.94),
('IBM','2012-11-15',185.85),
('IBM','2012-11-14',185.51),
('IBM','2012-11-13',188.32),
('IBM','2012-11-12',189.25),
('IBM','2012-11-09',189.64),
('IBM','2012-11-08',190.1),
('IBM','2012-11-07',191.16),
('IBM','2012-11-06',194.22),
('IBM','2012-11-05',193.29),
('IBM','2012-11-02',192.59),
('IBM','2012-11-01',196.29),
('IBM','2012-10-31',193.68)
)n(ticker,tdate,pr)
This produces the following result.
SHARPE
----------------------
0.618904269463314
In this example, we have monthly values for a portfolio and monthly risk-free rates.
SELECT wct.SHARPE2(pdate,pvalue,rf,12,'False') as SHARPE
FROM (VALUES
('2010-12-31',100000,0.005202),
('2011-01-31',100457.78,0.004891),
('2011-02-28',101518.83,0.005097),
('2011-03-31',102055.61,0.004959),
('2011-04-30',103216.4,0.005014),
('2011-05-31',103970.62,0.004995),
('2011-06-30',104915.46,0.005035),
('2011-07-31',105640.67,0.004929),
('2011-08-31',107643.46,0.005027),
('2011-09-30',108017.35,0.004865),
('2011-10-31',109334.07,0.005033),
('2011-11-30',110373.01,0.005042),
('2011-12-31',111550.51,0.004938),
('2012-01-31',112898.34,0.005016),
('2012-02-29',113909.43,0.00499),
('2012-03-31',114682.07,0.005084),
('2012-04-30',114877.46,0.004889),
('2012-05-31',115731.44,0.004898),
('2012-06-30',117754.23,0.005133),
('2012-07-31',118578.8,0.004719),
('2012-08-31',120634.65,0.004908),
('2012-09-30',121411.91,0.00496),
('2012-10-31',123689.75,0.004986),
('2012-11-30',124274.22,0.004853),
('2012-12-31',125307.78,0.004995),
('2013-01-31',126672.4,0.00499),
('2013-02-28',128171.03,0.00497),
('2013-03-31',129291.6,0.004814),
('2013-04-30',131008.02,0.00503),
('2013-05-31',132464.56,0.005208),
('2013-06-30',133378.55,0.005126),
('2013-07-31',133769.46,0.00493),
('2013-08-31',135724.66,0.004932),
('2013-09-30',136462.27,0.00486),
('2013-10-31',137830.14,0.004946),
('2013-11-30',139773.62,0.004971),
('2013-12-31',142001.01,0.00495)
)n(pdate,pvalue,rf)
This produces the following result.
SHARPE
----------------------
3.76729327551418
In this example, we use weekly prices and calculate the Sharpe ratio using geometric returns.
SELECT wct.SHARPE2(tdate,pr,rf,12,'True') as SHARPE
FROM (VALUES
('IBM','2012-12-17',195.08,0.000191),
('IBM','2012-12-10',191.76,0.000192),
('IBM','2012-12-03',191.95,0.000192),
('IBM','2012-11-26',190.07,0.000193),
('IBM','2012-11-19',193.49,0.000194),
('IBM','2012-11-12',186.94,0.000191),
('IBM','2012-11-05',189.64,0.000192),
('IBM','2012-10-31',192.59,0.000194),
('IBM','2012-10-22',192.43,0.000188),
('IBM','2012-10-15',192.52,0.000195),
('IBM','2012-10-08',206.89,0.00019),
('IBM','2012-10-01',209.67,0.000194),
('IBM','2012-09-24',206.55,0.000196),
('IBM','2012-09-17',205.08,0.000189),
('IBM','2012-09-10',205.91,0.000193),
('IBM','2012-09-04',198.63,0.000194),
('IBM','2012-08-27',194,0.000192),
('IBM','2012-08-20',196.91,0.000191),
('IBM','2012-08-13',200.34,0.000192),
('IBM','2012-08-06',198.42,0.000191),
('IBM','2012-07-30',196.81,0.000194),
('IBM','2012-07-23',194.7,0.000196),
('IBM','2012-07-16',190.8,0.000191),
('IBM','2012-07-09',184.41,0.000193),
('IBM','2012-07-02',189.77,0.000194),
('IBM','2012-06-25',193.9,0.000192),
('IBM','2012-06-18',192.04,0.000192),
('IBM','2012-06-11',197.39,0.000193),
('IBM','2012-06-04',193.46,0.000195),
('IBM','2012-05-29',187.46,0.00019),
('IBM','2012-05-21',192.63,0.000191),
('IBM','2012-05-14',194.2,0.000191),
('IBM','2012-05-07',199.44,0.000194),
('IBM','2012-04-30',202.38,0.000193),
('IBM','2012-04-23',204.18,0.000191),
('IBM','2012-04-16',197.06,0.00019),
('IBM','2012-04-09',200.22,0.000191),
('IBM','2012-04-02',202.86,0.000194),
('IBM','2012-03-26',205.99,0.000193),
('IBM','2012-03-19',202.86,0.000196),
('IBM','2012-03-12',203.39,0.000195),
('IBM','2012-03-05',198.07,0.000189),
('IBM','2012-02-27',196.28,0.000191),
('IBM','2012-02-21',195.24,0.000194),
('IBM','2012-02-13',190.96,0.000193),
('IBM','2012-02-06',189.97,0.000193),
('IBM','2012-01-30',190.43,0.00019),
('IBM','2012-01-23',187.31,0.000193),
('IBM','2012-01-17',185.4,0.000188),
('IBM','2012-01-09',176.19,0.000193),
('IBM','2012-01-03',179.52,0.000191)
)n(ticker,tdate,pr,rf)
This produces the following result.
SHARPE
----------------------
0.22973695571498
In this example, we look at prices for several different symbols and group the results by symbol. The risk-free rate is contained in the table with a ticker of 'LIB'
SELECT *
INTO #s
FROM (VALUES
('IBM','2012-12-03',195.08),
('IBM','2012-11-01',190.07),
('IBM','2012-10-01',193.68),
('IBM','2012-09-04',206.55),
('IBM','2012-08-01',194),
('IBM','2012-07-02',194.3),
('IBM','2012-06-01',193.9),
('IBM','2012-05-01',191.24),
('IBM','2012-04-02',204.44),
('IBM','2012-03-01',205.99),
('IBM','2012-02-01',194.23),
('IBM','2012-01-03',189.41),
('IBM','2011-12-01',180.84),
('IBM','2011-11-02',184.89),
('MSFT','2012-12-03',27.31),
('MSFT','2012-11-01',26.62),
('MSFT','2012-10-01',28.31),
('MSFT','2012-09-04',29.52),
('MSFT','2012-08-01',30.57),
('MSFT','2012-07-02',29.04),
('MSFT','2012-06-01',30.14),
('MSFT','2012-05-01',28.76),
('MSFT','2012-04-02',31.34),
('MSFT','2012-03-01',31.58),
('MSFT','2012-02-01',31.07),
('MSFT','2012-01-03',28.72),
('MSFT','2011-12-01',25.25),
('MSFT','2011-11-02',24.88),
('GOOG','2012-12-03',720.11),
('GOOG','2012-11-01',698.37),
('GOOG','2012-10-01',680.3),
('GOOG','2012-09-04',754.5),
('GOOG','2012-08-01',685.09),
('GOOG','2012-07-02',632.97),
('GOOG','2012-06-01',580.07),
('GOOG','2012-05-01',580.86),
('GOOG','2012-04-02',604.85),
('GOOG','2012-03-01',641.24),
('GOOG','2012-02-01',618.25),
('GOOG','2012-01-03',580.11),
('GOOG','2011-12-01',645.9),
('GOOG','2011-11-02',599.39),
('AAPL','2012-12-03',526.31),
('AAPL','2012-11-01',585.28),
('AAPL','2012-10-01',592.61),
('AAPL','2012-09-04',664.07),
('AAPL','2012-08-01',662.22),
('AAPL','2012-07-02',605.38),
('AAPL','2012-06-01',578.86),
('AAPL','2012-05-01',572.64),
('AAPL','2012-04-02',578.84),
('AAPL','2012-03-01',594.27),
('AAPL','2012-02-01',537.67),
('AAPL','2012-01-03',452.46),
('AAPL','2011-12-01',401.44),
('AAPL','2011-11-02',378.84),
('ORCL','2012-12-03',34.09),
('ORCL','2012-11-01',32),
('ORCL','2012-10-01',30.91),
('ORCL','2012-09-04',31.22),
('ORCL','2012-08-01',31.41),
('ORCL','2012-07-02',29.97),
('ORCL','2012-06-01',29.42),
('ORCL','2012-05-01',26.22),
('ORCL','2012-04-02',29.12),
('ORCL','2012-03-01',28.82),
('ORCL','2012-02-01',28.92),
('ORCL','2012-01-03',27.88),
('ORCL','2011-12-01',25.3),
('ORCL','2011-11-02',30.92),
('LIB','2012-12-03',0.000834),
('LIB','2012-11-01',0.000831),
('LIB','2012-10-01',0.000842),
('LIB','2012-09-04',0.000839),
('LIB','2012-08-01',0.00084),
('LIB','2012-07-02',0.000834),
('LIB','2012-06-01',0.000832),
('LIB','2012-05-01',0.000841),
('LIB','2012-04-02',0.000839),
('LIB','2012-03-01',0.000835),
('LIB','2012-02-01',0.000822),
('LIB','2012-01-03',0.000831),
('LIB','2011-12-01',0.000829),
('LIB','2011-11-02',0.000842)
)n(ticker,tdate,pr)
SELECT s1.ticker
,wct.SHARPE2(s1.tdate,s1.pr,s2.pr,12,'True') as SHARPE
FROM #s s1
JOIN #s s2
ON s1.tdate = s2.tdate
AND s1.ticker <> 'LIB'
AND s2.ticker = 'LIB'
GROUP BY s1.ticker
DROP TABLE #s
This produces the following result.
ticker SHARPE
------ ----------------------
AAPL 0.996079757565636
GOOG 0.655131542088044
IBM 0.278205208390779
MSFT 0.358126008744239
ORCL 0.290452673911992
See Also