RunningSHARPE
Updated: 30 Jun 2013
Use RunningSHARPE to calculate the Sharpe ratio from column values in an ordered resultant table without the need to a self-join. The Sharpe ratio is calculated over all the values from the first value to the last value in the ordered group or partition. If the column values are presented to the function out of order, an error message will be generated.
The Sharpe ratio is calculated using Sharpe’s 1994 revision of his original formula and is calculated as the mean difference of the returns and risk-free rate divided by the standard deviation of the differences multiplied by the square root of a scaling factor. For daily returns the scale factor might be 252; for weekly returns 52; for monthly returns 12. For the sake of consistency, the risk-free rate should be in the same units as the scaling factor. The standard deviation is the sample standard deviation.
Syntax
SELECT [westclintech].[wct].[RunningSHARPE](
<@R, float,>
,<@Rf, float,>
,<@Scale, float,>
,<@Prices, bit,>
,<@RowNum, int,>
,<@Id, tinyint,>)
Arguments
@R
the return or price value; if return values are being supplied, it should the percentage return in floating point format (i.e. 10% = 0.1). @R 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.
@Prices
a bit value identifying whether the supplied @R values are prices (or portfolio values) or returns. If @Prices is true, then the return is calculated. @Prices is an expression of type bit or of a type that can be implicitly converted to bit.
@RowNum
the number of the row within the group for which the Sharpe ratio is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the RunningSHARPE calculation. @Id allows you to specify multiple Sharpe ratios within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Return Type
float
Remarks
· If @Scale IS NULL then @Scale is set to 12.
· If @Prices IS NULL then @Prices is set to 'False'.
Examples
In this example we have 12 months’ worth of return data for and we want to calculate the SHARPE ratio.
SELECT CAST(eom as date) as eom
,cast(r as float) as r
,Cast(rf as float) as rf
into #s
FROM (VALUES
('2012-01-31',-0.0123631941629511,0.010733),
('2012-02-29',0.021547301457008,0.008281),
('2012-03-31',0.109883487344315,0.009401),
('2012-04-30',-0.135203619909502,0.009261),
('2012-05-31',0.053578903306823,0.007886),
('2012-06-30',-0.0701231624950338,0.008206),
('2012-07-31',0.100833155308695,0.009777),
('2012-08-31',0.0118377644090821,0.011936),
('2012-09-30',-0.0176448024549291,0.009263),
('2012-10-31',-0.0300663803201874,0.008798),
('2012-11-30',-0.0853462157809984,0.013932),
('2012-12-31',0.143485915492958,0.008757)
)n(eom,r,rf)
SELECT EOM
,r
,rf
,wct.RunningSHARPE(
r --@R
,rf / 12 --@Rf
,12 --@Scale
,'False' --@Prices
,ROW_NUMBER() OVER (ORDER BY eom ASC) --@RowNum
,NULL --@Id
) as SHARPE
FROM #s
DROP TABLE #s
This produces the following result.
EOM r rf SHARPE
---------- ---------------------- ---------------------- ----------------------
2012-01-31 -0.0123631941629511 0.010733 NULL
2012-02-29 0.021547301457008 0.008281 0.545661833493556
2012-03-31 0.109883487344315 0.009401 2.13410450673763
2012-04-30 -0.135203619909502 0.009261 -0.164456691190495
2012-05-31 0.053578903306823 0.007886 0.254426478335439
2012-06-30 -0.0701231624950338 0.008206 -0.244225006510992
2012-07-31 0.100833155308695 0.009777 0.346854303695086
2012-08-31 0.0118377644090821 0.011936 0.384351708574908
2012-09-30 -0.0176448024549291 0.009263 0.272061894639026
2012-10-31 -0.0300663803201874 0.008798 0.113603855400751
2012-11-30 -0.0853462157809984 0.013932 -0.257896431576602
2012-12-31 0.143485915492958 0.008757 0.277573805034166
In this example we have 13 months’ worth of price data for and we want to calculate the SHARPE ratio.
SELECT CAST(eom as date) as eom
,cast(pr as float) as pr
,cast(rf as float) as rf
into #s
FROM (VALUES
('2011-12-31',49.34,0.011124),
('2012-01-31',48.73,0.010733),
('2012-02-29',49.78,0.008281),
('2012-03-31',55.25,0.009401),
('2012-04-30',47.78,0.009261),
('2012-05-31',50.34,0.007886),
('2012-06-30',46.81,0.008206),
('2012-07-31',51.53,0.009777),
('2012-08-31',52.14,0.011936),
('2012-09-30',51.22,0.009263),
('2012-10-31',49.68,0.008798),
('2012-11-30',45.44,0.013932),
('2012-12-31',51.96,0.008757)
)n(eom,pr,rf)
SELECT EOM
,pr
,rf
,wct.RunningSHARPE(
pr --@R
,rf / 12 --@Rf
,12 --@Scale
,'True' --@Prices
,ROW_NUMBER() OVER (ORDER BY eom ASC) --@RowNum
,1 --@Id
) as SHARPE
FROM #s
DROP TABLE #s
This produces the following result.
EOM pr rf SHARPE
---------- ---------------------- ---------------------- ----------------------
2011-12-31 49.34 0.011124 NULL
2012-01-31 48.73 0.010733 NULL
2012-02-29 49.78 0.008281 0.54566183349356
2012-03-31 55.25 0.009401 2.13410450673763
2012-04-30 47.78 0.009261 -0.164456691190498
2012-05-31 50.34 0.007886 0.254426478335437
2012-06-30 46.81 0.008206 -0.244225006510994
2012-07-31 51.53 0.009777 0.346854303695083
2012-08-31 52.14 0.011936 0.384351708574905
2012-09-30 51.22 0.009263 0.272061894639024
2012-10-31 49.68 0.008798 0.113603855400749
2012-11-30 45.44 0.013932 -0.257896431576604
2012-12-31 51.96 0.008757 0.277573805034163
In this example we have 13 months’ worth of price data for three different portfolios and we want to calculate the SHARPE ratio.
SELECT CAST(eom as date) as eom
,aaa
,bbb
,ccc
,rf
into #s
FROM (VALUES
('2011-12-31',74.58,49.34,54.97,0.011124),
('2012-01-31',75.17,48.73,56.07,0.010733),
('2012-02-29',74.61,49.78,54.66,0.008281),
('2012-03-31',74.69,55.25,55.39,0.009401),
('2012-04-30',75.6,47.78,57.4,0.009261),
('2012-05-31',75.53,50.34,54.92,0.007886),
('2012-06-30',75.41,46.81,55.12,0.008206),
('2012-07-31',75.83,51.53,55.56,0.009777),
('2012-08-31',74.58,52.14,54.24,0.011936),
('2012-09-30',74.77,51.22,57.41,0.009263),
('2012-10-31',74.33,49.68,55.76,0.008798),
('2012-11-30',75.06,45.44,56.28,0.013932),
('2012-12-31',75.25,51.96,53.19,0.008757)
)n(eom,aaa,bbb,ccc,rf)
SELECT EOM
,wct.RunningSHARPE(AAA, rf / 12, 12, 'True', ROW_NUMBER() OVER (ORDER BY eom ASC), 1) as AAA
,wct.RunningSHARPE(BBB, rf / 12, 12, 'True', ROW_NUMBER() OVER (ORDER BY eom ASC), 2) as BBB
,wct.RunningSHARPE(CCC, rf / 12, 12, 'True', ROW_NUMBER() OVER (ORDER BY eom ASC), 3) as CCC
FROM #s
DROP TABLE #s
This produces the following result.
EOM AAA BBB CCC
---------- ---------------------- ---------------------- ----------------------
2011-12-31 NULL NULL NULL
2012-01-31 NULL NULL NULL
2012-02-29 -0.181542909020628 0.545661910626499 -0.366206366233852
2012-03-31 -0.126885410892028 2.13410453560661 0.278195680508385
2012-04-30 1.07500165715537 -0.164456676551127 1.37903712868579
2012-05-31 0.818001181511542 0.254426495294565 -0.0522708164825227
2012-06-30 0.553334383732037 -0.244224988358572 0.00901909082311352
2012-07-31 0.867334958883116 0.346854317988981 0.13899666737245
2012-08-31 -0.286251416838262 0.384351725488128 -0.27706453633369
2012-09-30 -0.19060920026246 0.272061914029634 0.48771957908082
2012-10-31 -0.453791039394707 0.113603876950388 0.119798191788006
2012-11-30 -0.0820413791871152 -0.257896411797891 0.19893568874376
2012-12-31 -0.0137900125439397 0.277573820987464 -0.314234829951125
In this example, we have the same data as in the previous example, except that is stored in 3rd normal form rather than in the de-normalized form. The risk-free rate is included in the table with using the symbol RF. We use the PARTITION clause in order to generate the correct row number within a symbol.
SELECT CAST(eom as date) as eom
,sym
,CAST(pr as float) as pr
INTO #s
FROM (VALUES
('2011-12-31','AAA',74.58),
('2012-01-31','AAA',75.17),
('2012-02-29','AAA',74.61),
('2012-03-31','AAA',74.69),
('2012-04-30','AAA',75.6),
('2012-05-31','AAA',75.53),
('2012-06-30','AAA',75.41),
('2012-07-31','AAA',75.83),
('2012-08-31','AAA',74.58),
('2012-09-30','AAA',74.77),
('2012-10-31','AAA',74.33),
('2012-11-30','AAA',75.06),
('2012-12-31','AAA',75.25),
('2011-12-31','BBB',49.34),
('2012-01-31','BBB',48.73),
('2012-02-29','BBB',49.78),
('2012-03-31','BBB',55.25),
('2012-04-30','BBB',47.78),
('2012-05-31','BBB',50.34),
('2012-06-30','BBB',46.81),
('2012-07-31','BBB',51.53),
('2012-08-31','BBB',52.14),
('2012-09-30','BBB',51.22),
('2012-10-31','BBB',49.68),
('2012-11-30','BBB',45.44),
('2012-12-31','BBB',51.96),
('2011-12-31','CCC',54.97),
('2012-01-31','CCC',56.07),
('2012-02-29','CCC',54.66),
('2012-03-31','CCC',55.39),
('2012-04-30','CCC',57.4),
('2012-05-31','CCC',54.92),
('2012-06-30','CCC',55.12),
('2012-07-31','CCC',55.56),
('2012-08-31','CCC',54.24),
('2012-09-30','CCC',57.41),
('2012-10-31','CCC',55.76),
('2012-11-30','CCC',56.28),
('2012-12-31','CCC',53.19),
('2011-12-31','RF',0.011124),
('2012-01-31','RF',0.010733),
('2012-02-29','RF',0.008281),
('2012-03-31','RF',0.009401),
('2012-04-30','RF',0.009261),
('2012-05-31','RF',0.007886),
('2012-06-30','RF',0.008206),
('2012-07-31','RF',0.009777),
('2012-08-31','RF',0.011936),
('2012-09-30','RF',0.009263),
('2012-10-31','RF',0.008798),
('2012-11-30','RF',0.013932),
('2012-12-31','RF',0.008757)
)n(eom,sym,pr)
SELECT s1.EOM
,s1.sym
,wct.RunningSHARPE(s1.pr, s2.pr / 12, 12, 'True', ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1) as SHARPE
FROM #s s1
JOIN #s s2
ON s1.eom = s2.eom
WHERE s1.sym <> 'RF'
AND s2.sym = 'RF'
DROP TABLE #s
This produces the following result.
EOM sym SHARPE
---------- ---- ----------------------
2011-12-31 AAA NULL
2012-01-31 AAA NULL
2012-02-29 AAA -0.181543074627337
2012-03-31 AAA -0.12688566719547
2012-04-30 AAA 1.07500147666457
2012-05-31 AAA 0.818000958395123
2012-06-30 AAA 0.553334158265181
2012-07-31 AAA 0.867334743771591
2012-08-31 AAA -0.286251571435952
2012-09-30 AAA -0.190609377952824
2012-10-31 AAA -0.453791227700159
2012-11-30 AAA -0.0820415528633414
2012-12-31 AAA -0.0137901803260117
2011-12-31 BBB NULL
2012-01-31 BBB NULL
2012-02-29 BBB 0.54566183349356
2012-03-31 BBB 2.13410450673763
2012-04-30 BBB -0.164456691190498
2012-05-31 BBB 0.254426478335437
2012-06-30 BBB -0.244225006510994
2012-07-31 BBB 0.346854303695083
2012-08-31 BBB 0.384351708574905
2012-09-30 BBB 0.272061894639024
2012-10-31 BBB 0.113603855400749
2012-11-30 BBB -0.257896431576604
2012-12-31 BBB 0.277573805034163
2011-12-31 CCC NULL
2012-01-31 CCC NULL
2012-02-29 CCC -0.36620642343843
2012-03-31 CCC 0.278195603450318
2012-04-30 CCC 1.37903706990828
2012-05-31 CCC -0.0522708651764702
2012-06-30 CCC 0.00901903880240953
2012-07-31 CCC 0.13899661821098
2012-08-31 CCC -0.277064588884071
2012-09-30 CCC 0.487719530266477
2012-10-31 CCC 0.119798141071374
2012-11-30 CCC 0.198935640030015
2012-12-31 CCC -0.31423487026325
Using the same data, we could PIVOT the results into a tabular format.
SELECT EOM
,AAA
,BBB
,CCC
FROM (
SELECT s1.EOM
,s1.sym
,wct.RunningSHARPE(s1.pr, s2.pr / 12, 12, 'True', ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1) as SHARPE
FROM #s s1
JOIN #s s2
ON s1.eom = s2.eom
WHERE s1.sym <> 'RF'
AND s2.sym = 'RF'
) d
PIVOT(sum(SHARPE) for sym in(AAA,BBB,CCC))as P
This produces the following result.
EOM AAA BBB CCC
---------- ---------------------- ---------------------- ----------------------
2011-12-31 NULL NULL NULL
2012-01-31 NULL NULL NULL
2012-02-29 -0.181543074627337 0.54566183349356 -0.36620642343843
2012-03-31 -0.12688566719547 2.13410450673763 0.278195603450318
2012-04-30 1.07500147666457 -0.164456691190498 1.37903706990828
2012-05-31 0.818000958395123 0.254426478335437 -0.0522708651764702
2012-06-30 0.553334158265181 -0.244225006510994 0.00901903880240953
2012-07-31 0.867334743771591 0.346854303695083 0.13899661821098
2012-08-31 -0.286251571435952 0.384351708574905 -0.277064588884071
2012-09-30 -0.190609377952824 0.272061894639024 0.487719530266477
2012-10-31 -0.453791227700159 0.113603855400749 0.119798141071374
2012-11-30 -0.0820415528633414 -0.257896431576604 0.198935640030015
2012-12-31 -0.0137901803260117 0.277573805034163 -0.31423487026325