MovingSHARPE
Updated: 30 Jun 2013
Use MovingSHARPE 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 for each value from the first value in the window to the last value in the window. 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].[MovingSHARPE](
<@R, float,>
,<@Rf, float,>
,<@Scale, float,>
,<@Prices, bit,>
,<@Offset, int,>
,<@RowNum, int,>
,<@Id, tinyint,>
,<@Exact, bit,>)
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.
@Offset
specifies the window size. @Offset is an expression of type int or of a type that can be implicitly converted to int.
@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 MovingSHARPE calculation. @Id allows you to specify multiple moving Sharpe ratio calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
@Exact
a bit value which tells the function whether or not to return a NULL value if the number of rows in the window is smaller the @Offset value. If @Exact is 'True' and the number of rows in the window is less the @Offset then a NULL is returned. @Exact is an expression of type bit or of a type that can be implicitly converted to bit.
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 60 months’ worth of return data for and we want to calculate the SHARPE ratio with a window size of 36 rows.
SELECT cast(eom as date) as eom
,CAST(r as float) as r
,CAST(rf as float) as rf
INTO #s
FROM (VALUES
('2013-12-31',0.006141,0.000721),
('2013-11-30',0.004506,0.000794),
('2013-10-31',-0.011454,0.00082),
('2013-09-30',-0.001549,0.000774),
('2013-08-31',0.00568,0.000869),
('2013-07-31',0.000241,0.00087),
('2013-06-30',0.00648,0.000845),
('2013-05-31',0.005113,0.000796),
('2013-04-30',0.002461,0.000832),
('2013-03-31',-0.012148,0.00087),
('2013-02-28',-0.005735,0.000826),
('2013-01-31',0.007749,0.000806),
('2012-12-31',-0.001317,0.000815),
('2012-11-30',-0.000728,0.000845),
('2012-10-31',0.009667,0.000775),
('2012-09-30',0.017884,0.000859),
('2012-08-31',0.011488,0.000833),
('2012-07-31',-0.001725,0.000813),
('2012-06-30',0.001178,0.000792),
('2012-05-31',-0.000403,0.00076),
('2012-04-30',0.004734,0.000849),
('2012-03-31',0.018142,0.000753),
('2012-02-29',0.0099,0.000788),
('2012-01-31',0.012923,0.000801),
('2011-12-31',0.01715,0.000793),
('2011-11-30',-0.005677,0.000783),
('2011-10-31',0.004575,0.000764),
('2011-09-30',0.005032,0.000821),
('2011-08-31',0.015355,0.000762),
('2011-07-31',0.002744,0.000737),
('2011-06-30',0.004112,0.000794),
('2011-05-31',0.007087,0.000833),
('2011-04-30',0.001757,0.000818),
('2011-03-31',0.002962,0.000778),
('2011-02-28',0.001222,0.000782),
('2011-01-31',0.008292,0.00078),
('2010-12-31',0.007464,0.000738),
('2010-11-30',0.009659,0.00084),
('2010-10-31',0.007148,0.000693),
('2010-09-30',0.015428,0.000777),
('2010-08-31',0.006701,0.000799),
('2010-07-31',0.013108,0.000785),
('2010-06-30',0.003393,0.000856),
('2010-05-31',0.009797,0.000731),
('2010-04-30',0.01416,0.000718),
('2010-03-31',0.004966,0.000854),
('2010-02-28',-0.001553,0.000752),
('2010-01-31',0.01009,0.000804),
('2009-12-31',0.006488,0.000721),
('2009-11-30',0.001107,0.00092),
('2009-10-31',0.009641,0.000774),
('2009-09-30',0.001297,0.000846),
('2009-08-31',0.001314,0.000767),
('2009-07-31',0.008622,0.000815),
('2009-06-30',0.015167,0.000721),
('2009-05-31',-0.002137,0.0008),
('2009-04-30',0.016035,0.000798),
('2009-03-31',0.018067,0.00077),
('2009-02-28',0.013449,0.000823),
('2009-01-31',-0.000029,0.000852)
)n(eom,r,rf)
SELECT eom
,r
,rf
,wct.MovingSharpe(
r --@R
,rf --@Rf
,12 --@Scale
,'False' --@Prices
,36 --@Offset
,ROW_NUMBER() OVER (ORDER BY eom ASC) --@RowNum
,NULL --@Id
,'True' --@Exact
) as SHARPE
FROM #s
DROP TABLE #s
This produces the following result.
eom r rf SHARPE
---------- ---------------------- ---------------------- ----------------------
2009-01-31 -2.9E-05 0.000852 NULL
2009-02-28 0.013449 0.000823 NULL
2009-03-31 0.018067 0.00077 NULL
2009-04-30 0.016035 0.000798 NULL
2009-05-31 -0.002137 0.0008 NULL
2009-06-30 0.015167 0.000721 NULL
2009-07-31 0.008622 0.000815 NULL
2009-08-31 0.001314 0.000767 NULL
2009-09-30 0.001297 0.000846 NULL
2009-10-31 0.009641 0.000774 NULL
2009-11-30 0.001107 0.00092 NULL
2009-12-31 0.006488 0.000721 NULL
2010-01-31 0.01009 0.000804 NULL
2010-02-28 -0.001553 0.000752 NULL
2010-03-31 0.004966 0.000854 NULL
2010-04-30 0.01416 0.000718 NULL
2010-05-31 0.009797 0.000731 NULL
2010-06-30 0.003393 0.000856 NULL
2010-07-31 0.013108 0.000785 NULL
2010-08-31 0.006701 0.000799 NULL
2010-09-30 0.015428 0.000777 NULL
2010-10-31 0.007148 0.000693 NULL
2010-11-30 0.009659 0.00084 NULL
2010-12-31 0.007464 0.000738 NULL
2011-01-31 0.008292 0.00078 NULL
2011-02-28 0.001222 0.000782 NULL
2011-03-31 0.002962 0.000778 NULL
2011-04-30 0.001757 0.000818 NULL
2011-05-31 0.007087 0.000833 NULL
2011-06-30 0.004112 0.000794 NULL
2011-07-31 0.002744 0.000737 NULL
2011-08-31 0.015355 0.000762 NULL
2011-09-30 0.005032 0.000821 NULL
2011-10-31 0.004575 0.000764 NULL
2011-11-30 -0.005677 0.000783 NULL
2011-12-31 0.01715 0.000793 3.58503856570884
2012-01-31 0.012923 0.000801 3.82383934381681
2012-02-29 0.0099 0.000788 3.81336027480443
2012-03-31 0.018142 0.000753 3.8117754050257
2012-04-30 0.004734 0.000849 3.73906105356095
2012-05-31 -0.000403 0.00076 3.81736208000486
2012-06-30 0.001178 0.000792 3.64078347149186
2012-07-31 -0.001725 0.000813 3.36555089288179
2012-08-31 0.011488 0.000833 3.53859523956385
2012-09-30 0.017884 0.000859 3.67945590535237
2012-10-31 0.009667 0.000775 3.67967091074914
2012-11-30 -0.000728 0.000845 3.61560156829587
2012-12-31 -0.001317 0.000815 3.39832634900535
2013-01-31 0.007749 0.000806 3.37419677095366
2013-02-28 -0.005735 0.000826 3.2028444131869
2013-03-31 -0.012148 0.00087 2.6387754644457
2013-04-30 0.002461 0.000832 2.51760823941849
2013-05-31 0.005113 0.000796 2.46372838791937
2013-06-30 0.00648 0.000845 2.51076506223729
2013-07-31 0.000241 0.00087 2.34923346061192
2013-08-31 0.00568 0.000869 2.33506468325438
2013-09-30 -0.001549 0.000774 2.13660818458873
2013-10-31 -0.011454 0.00082 1.73629331839779
2013-11-30 0.004506 0.000794 1.68126520479592
2013-12-31 0.006141 0.000721 1.66695161658538
In this example, we do the same calculation, using price data rather than return data.
SELECT cast(eom as date) as eom
,CAST(pr as float) as pr
,CAST(rf as float) as rf
INTO #s
FROM (VALUES
('2013-12-31',70.31,0.000721),
('2013-11-30',69.88,0.000794),
('2013-10-31',69.56,0.00082),
('2013-09-30',70.37,0.000774),
('2013-08-31',70.48,0.000869),
('2013-07-31',70.08,0.00087),
('2013-06-30',70.06,0.000845),
('2013-05-31',69.61,0.000796),
('2013-04-30',69.26,0.000832),
('2013-03-31',69.09,0.00087),
('2013-02-28',69.94,0.000826),
('2013-01-31',70.34,0.000806),
('2012-12-31',69.8,0.000815),
('2012-11-30',69.89,0.000845),
('2012-10-31',69.94,0.000775),
('2012-09-30',69.28,0.000859),
('2012-08-31',68.06,0.000833),
('2012-07-31',67.28,0.000813),
('2012-06-30',67.4,0.000792),
('2012-05-31',67.32,0.00076),
('2012-04-30',67.35,0.000849),
('2012-03-31',67.03,0.000753),
('2012-02-29',65.84,0.000788),
('2012-01-31',65.19,0.000801),
('2011-12-31',64.36,0.000793),
('2011-11-30',63.27,0.000783),
('2011-10-31',63.64,0.000764),
('2011-09-30',63.35,0.000821),
('2011-08-31',63.03,0.000762),
('2011-07-31',62.08,0.000737),
('2011-06-30',61.91,0.000794),
('2011-05-31',61.65,0.000833),
('2011-04-30',61.22,0.000818),
('2011-03-31',61.11,0.000778),
('2011-02-28',60.93,0.000782),
('2011-01-31',60.86,0.00078),
('2010-12-31',60.36,0.000738),
('2010-11-30',59.91,0.00084),
('2010-10-31',59.34,0.000693),
('2010-09-30',58.91,0.000777),
('2010-08-31',58.02,0.000799),
('2010-07-31',57.63,0.000785),
('2010-06-30',56.89,0.000856),
('2010-05-31',56.7,0.000731),
('2010-04-30',56.15,0.000718),
('2010-03-31',55.36,0.000854),
('2010-02-28',55.09,0.000752),
('2010-01-31',55.17,0.000804),
('2009-12-31',54.62,0.000721),
('2009-11-30',54.27,0.00092),
('2009-10-31',54.21,0.000774),
('2009-09-30',53.69,0.000846),
('2009-08-31',53.62,0.000767),
('2009-07-31',53.55,0.000815),
('2009-06-30',53.09,0.000721),
('2009-05-31',52.3,0.0008),
('2009-04-30',52.41,0.000798),
('2009-03-31',51.59,0.00077),
('2009-02-28',50.67,0.000823),
('2009-01-31',50,0.000852),
('2008-12-31',50,NULL)
)n(eom,pr,rf)
SELECT eom
,pr
,rf
,wct.MovingSharpe(
pr --@R
,rf --@Rf
,12 --@Scale
,'True' --@Prices
,36 --@Offset
,ROW_NUMBER() OVER (ORDER BY eom ASC) --@RowNum
,NULL --@Id
,'True' --@Exact
) as SHARPE
FROM #s
DROP TABLE #s
This produces the following result.
eom pr rf SHARPE
---------- ---------------------- ---------------------- ----------------------
2008-12-31 50 NULL NULL
2009-01-31 50 0.000852 NULL
2009-02-28 50.67 0.000823 NULL
2009-03-31 51.59 0.00077 NULL
2009-04-30 52.41 0.000798 NULL
2009-05-31 52.3 0.0008 NULL
2009-06-30 53.09 0.000721 NULL
2009-07-31 53.55 0.000815 NULL
2009-08-31 53.62 0.000767 NULL
2009-09-30 53.69 0.000846 NULL
2009-10-31 54.21 0.000774 NULL
2009-11-30 54.27 0.00092 NULL
2009-12-31 54.62 0.000721 NULL
2010-01-31 55.17 0.000804 NULL
2010-02-28 55.09 0.000752 NULL
2010-03-31 55.36 0.000854 NULL
2010-04-30 56.15 0.000718 NULL
2010-05-31 56.7 0.000731 NULL
2010-06-30 56.89 0.000856 NULL
2010-07-31 57.63 0.000785 NULL
2010-08-31 58.02 0.000799 NULL
2010-09-30 58.91 0.000777 NULL
2010-10-31 59.34 0.000693 NULL
2010-11-30 59.91 0.00084 NULL
2010-12-31 60.36 0.000738 NULL
2011-01-31 60.86 0.00078 NULL
2011-02-28 60.93 0.000782 NULL
2011-03-31 61.11 0.000778 NULL
2011-04-30 61.22 0.000818 NULL
2011-05-31 61.65 0.000833 NULL
2011-06-30 61.91 0.000794 NULL
2011-07-31 62.08 0.000737 NULL
2011-08-31 63.03 0.000762 NULL
2011-09-30 63.35 0.000821 NULL
2011-10-31 63.64 0.000764 NULL
2011-11-30 63.27 0.000783 NULL
2011-12-31 64.36 0.000793 3.58724197797327
2012-01-31 65.19 0.000801 3.82512141627199
2012-02-29 65.84 0.000788 3.81514470512535
2012-03-31 67.03 0.000753 3.81628158408394
2012-04-30 67.35 0.000849 3.74312817148531
2012-05-31 67.32 0.00076 3.81797115658713
2012-06-30 67.4 0.000792 3.64105620698124
2012-07-31 67.28 0.000813 3.36318127012988
2012-08-31 68.06 0.000833 3.53652927170119
2012-09-30 69.28 0.000859 3.6766522451641
2012-10-31 69.94 0.000775 3.67525498693465
2012-11-30 69.89 0.000845 3.61183671490358
2012-12-31 69.8 0.000815 3.39662943092625
2013-01-31 70.34 0.000806 3.37247951499307
2013-02-28 69.94 0.000826 3.19998086133833
2013-03-31 69.09 0.00087 2.63759350838852
2013-04-30 69.26 0.000832 2.51628408527974
2013-05-31 69.61 0.000796 2.46154993112264
2013-06-30 70.06 0.000845 2.50909939495311
2013-07-31 70.08 0.00087 2.34889516219999
2013-08-31 70.48 0.000869 2.33430425359313
2013-09-30 70.37 0.000774 2.13564303358242
2013-10-31 69.56 0.00082 1.73263809935584
2013-11-30 69.88 0.000794 1.67933086905761
2013-12-31 70.31 0.000721 1.66468205379024
In this example we calculate the SHARPE ratio for multiple portfolios simultaneously. Notice that we have to use a different value in @Id for each Sharpe column in the resultant table.
SELECT CAST(eom as date) as eom
,CAST(AAA as money) as AAA
,CAST(BBB as money) as BBB
,CAST(CCC as money) as CCC
,CAST(rf as float) as rf
INTO #s
FROM (VALUES
('2013-12-31',52.03,97.35,73.04,0.000721),
('2013-11-30',58.92,95.74,72.65,0.000794),
('2013-10-31',48.58,107.59,68.81,0.00082),
('2013-09-30',54.22,83.56,66.05,0.000774),
('2013-08-31',54.82,106.24,58.86,0.000869),
('2013-07-31',41.44,104.6,67.41,0.00087),
('2013-06-30',44.9,94.92,71.37,0.000845),
('2013-05-31',54.5,90.99,81.44,0.000796),
('2013-04-30',48.96,97.37,78.01,0.000832),
('2013-03-31',57.34,97.38,62.76,0.00087),
('2013-02-28',52.24,98.21,72.69,0.000826),
('2013-01-31',55.93,104.99,76.76,0.000806),
('2012-12-31',49.4,101.6,80.51,0.000815),
('2012-11-30',41.82,105.63,79.25,0.000845),
('2012-10-31',53.53,113.72,78.97,0.000775),
('2012-09-30',52.48,98.52,87.03,0.000859),
('2012-08-31',42.28,103.66,87.31,0.000833),
('2012-07-31',51.54,103.55,70.13,0.000813),
('2012-06-30',46.31,99.08,77.34,0.000792),
('2012-05-31',52.38,90.17,73.11,0.00076),
('2012-04-30',43.06,99.05,77.64,0.000849),
('2012-03-31',48.65,99.85,80.5,0.000753),
('2012-02-29',57.31,97.77,66.44,0.000788),
('2012-01-31',56.15,93.11,84.58,0.000801),
('2011-12-31',48.6,107.23,72.52,0.000793),
('2011-11-30',50.56,88.77,66.47,0.000783),
('2011-10-31',44.84,91.39,59.29,0.000764),
('2011-09-30',41.18,104.57,79.52,0.000821),
('2011-08-31',51.37,89.04,77.67,0.000762),
('2011-07-31',54.1,104.02,82.81,0.000737),
('2011-06-30',45.17,94.51,75.64,0.000794),
('2011-05-31',44.66,110.92,70.9,0.000833),
('2011-04-30',50.12,105.48,79.38,0.000818),
('2011-03-31',51.53,103.38,71.51,0.000778),
('2011-02-28',44.86,90.51,80.92,0.000782),
('2011-01-31',58.52,90.08,80.27,0.00078),
('2010-12-31',46.93,97.22,82.46,0.000738),
('2010-11-30',41.79,92.71,68.57,0.00084),
('2010-10-31',47.35,100.14,78.22,0.000693),
('2010-09-30',63.3,100.09,74.26,0.000777),
('2010-08-31',47.38,100.04,59.61,0.000799),
('2010-07-31',47.44,103.36,77.98,0.000785),
('2010-06-30',45.69,96.24,78.26,0.000856),
('2010-05-31',50.75,103.71,67.34,0.000731),
('2010-04-30',50.96,105.03,75.53,0.000718),
('2010-03-31',56.29,98.58,78.84,0.000854),
('2010-02-28',54.66,98.59,82.37,0.000752),
('2010-01-31',49.01,103.69,56.64,0.000804),
('2009-12-31',44.88,103.58,83.74,0.000721),
('2009-11-30',54.67,98.86,64.38,0.00092),
('2009-10-31',53.41,97.42,79.84,0.000774),
('2009-09-30',49.59,100.01,63.9,0.000846),
('2009-08-31',60.45,92.4,71.18,0.000767),
('2009-07-31',45.36,107.93,63.73,0.000815),
('2009-06-30',51.16,105.77,74.11,0.000721),
('2009-05-31',45.33,88.62,65.69,0.0008),
('2009-04-30',56.12,103.07,83.46,0.000798),
('2009-03-31',52.43,109.46,75.83,0.00077),
('2009-02-28',57.2,94.24,69.08,0.000823),
('2009-01-31',41.42,92.54,79.03,0.000852),
('2008-12-31',49.89,101.87,69.56,NULL)
)n(eom,AAA,BBB,CCC,rf)
SELECT eom
,wct.MovingSHARPE(AAA,rf,12,'True',36,ROW_NUMBER() OVER (ORDER BY EOM ASC),1,'TRUE') as AAA
,wct.MovingSHARPE(BBB,rf,12,'True',36,ROW_NUMBER() OVER (ORDER BY EOM ASC),2,'TRUE') as BBB
,wct.MovingSHARPE(CCC,rf,12,'True',36,ROW_NUMBER() OVER (ORDER BY EOM ASC),3,'TRUE') as CCC
FROM #s
DROP TABLE #s
This produces the following result.
eom AAA BBB CCC
---------- ---------------------- ---------------------- ----------------------
2008-12-31 NULL NULL NULL
2009-01-31 NULL NULL NULL
2009-02-28 NULL NULL NULL
2009-03-31 NULL NULL NULL
2009-04-30 NULL NULL NULL
2009-05-31 NULL NULL NULL
2009-06-30 NULL NULL NULL
2009-07-31 NULL NULL NULL
2009-08-31 NULL NULL NULL
2009-09-30 NULL NULL NULL
2009-10-31 NULL NULL NULL
2009-11-30 NULL NULL NULL
2009-12-31 NULL NULL NULL
2010-01-31 NULL NULL NULL
2010-02-28 NULL NULL NULL
2010-03-31 NULL NULL NULL
2010-04-30 NULL NULL NULL
2010-05-31 NULL NULL NULL
2010-06-30 NULL NULL NULL
2010-07-31 NULL NULL NULL
2010-08-31 NULL NULL NULL
2010-09-30 NULL NULL NULL
2010-10-31 NULL NULL NULL
2010-11-30 NULL NULL NULL
2010-12-31 NULL NULL NULL
2011-01-31 NULL NULL NULL
2011-02-28 NULL NULL NULL
2011-03-31 NULL NULL NULL
2011-04-30 NULL NULL NULL
2011-05-31 NULL NULL NULL
2011-06-30 NULL NULL NULL
2011-07-31 NULL NULL NULL
2011-08-31 NULL NULL NULL
2011-09-30 NULL NULL NULL
2011-10-31 NULL NULL NULL
2011-11-30 NULL NULL NULL
2011-12-31 0.233528869059514 0.182189869285874 0.291196991243479
2012-01-31 0.428589710945611 0.14009698868573 0.307044699255947
2012-02-29 0.232113320799138 0.170917987585063 0.253138985931664
2012-03-31 0.186596102894598 0.0342268470729224 0.311646848375667
2012-04-30 0.0683864071811432 0.086165331925673 0.237761922435001
2012-05-31 0.325969651430006 0.140971814861014 0.329606421662035
2012-06-30 0.171520195424216 0.043595137017662 0.291732893757088
2012-07-31 0.314978859162161 0.0705853443539738 0.320038455254952
2012-08-31 -0.00897211996384037 0.239679813121683 0.38467242592835
2012-09-30 0.265499846277122 0.0890325916024162 0.442739792371279
2012-10-31 0.228967007783497 0.284665529813372 0.256758310584632
2012-11-30 0.0683525995188676 0.187609733397808 0.377650345690581
2012-12-31 0.297330771025598 0.0935854855997611 0.221186189935064
2013-01-31 0.321267359914097 0.128707503416037 0.41763069773045
2013-02-28 0.207250855132609 0.111433971191136 0.0998832945308969
2013-03-31 0.249102539475316 0.102305463150085 0.0286382393253875
2013-04-30 0.214787833618066 0.0311113910316351 0.230157474733058
2013-05-31 0.286400031963515 -0.0267859046419727 0.342198854033064
2013-06-30 0.235651803024062 0.0991973300015091 0.137179545260384
2013-07-31 0.164175091686935 0.12864632079473 0.099330699605563
2013-08-31 0.343731365218627 0.180866107153194 0.183691209598159
2013-09-30 0.150910657303731 -0.0480074713822149 0.0952675431892081
2013-10-31 0.24988895362919 0.215025336732779 0.0863100381477296
2013-11-30 0.453799246689009 0.181157259784546 0.228365903568873
2013-12-31 0.302127835715223 0.153132031542535 0.0762291478934498
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
('2013-12-31','AAA',52.03),
('2013-11-30','AAA',58.92),
('2013-10-31','AAA',48.58),
('2013-09-30','AAA',54.22),
('2013-08-31','AAA',54.82),
('2013-07-31','AAA',41.44),
('2013-06-30','AAA',44.9),
('2013-05-31','AAA',54.5),
('2013-04-30','AAA',48.96),
('2013-03-31','AAA',57.34),
('2013-02-28','AAA',52.24),
('2013-01-31','AAA',55.93),
('2012-12-31','AAA',49.4),
('2012-11-30','AAA',41.82),
('2012-10-31','AAA',53.53),
('2012-09-30','AAA',52.48),
('2012-08-31','AAA',42.28),
('2012-07-31','AAA',51.54),
('2012-06-30','AAA',46.31),
('2012-05-31','AAA',52.38),
('2012-04-30','AAA',43.06),
('2012-03-31','AAA',48.65),
('2012-02-29','AAA',57.31),
('2012-01-31','AAA',56.15),
('2011-12-31','AAA',48.6),
('2011-11-30','AAA',50.56),
('2011-10-31','AAA',44.84),
('2011-09-30','AAA',41.18),
('2011-08-31','AAA',51.37),
('2011-07-31','AAA',54.1),
('2011-06-30','AAA',45.17),
('2011-05-31','AAA',44.66),
('2011-04-30','AAA',50.12),
('2011-03-31','AAA',51.53),
('2011-02-28','AAA',44.86),
('2011-01-31','AAA',58.52),
('2010-12-31','AAA',46.93),
('2010-11-30','AAA',41.79),
('2010-10-31','AAA',47.35),
('2010-09-30','AAA',63.3),
('2010-08-31','AAA',47.38),
('2010-07-31','AAA',47.44),
('2010-06-30','AAA',45.69),
('2010-05-31','AAA',50.75),
('2010-04-30','AAA',50.96),
('2010-03-31','AAA',56.29),
('2010-02-28','AAA',54.66),
('2010-01-31','AAA',49.01),
('2009-12-31','AAA',44.88),
('2009-11-30','AAA',54.67),
('2009-10-31','AAA',53.41),
('2009-09-30','AAA',49.59),
('2009-08-31','AAA',60.45),
('2009-07-31','AAA',45.36),
('2009-06-30','AAA',51.16),
('2009-05-31','AAA',45.33),
('2009-04-30','AAA',56.12),
('2009-03-31','AAA',52.43),
('2009-02-28','AAA',57.2),
('2009-01-31','AAA',41.42),
('2008-12-31','AAA',49.89),
('2013-12-31','BBB',97.35),
('2013-11-30','BBB',95.74),
('2013-10-31','BBB',107.59),
('2013-09-30','BBB',83.56),
('2013-08-31','BBB',106.24),
('2013-07-31','BBB',104.6),
('2013-06-30','BBB',94.92),
('2013-05-31','BBB',90.99),
('2013-04-30','BBB',97.37),
('2013-03-31','BBB',97.38),
('2013-02-28','BBB',98.21),
('2013-01-31','BBB',104.99),
('2012-12-31','BBB',101.6),
('2012-11-30','BBB',105.63),
('2012-10-31','BBB',113.72),
('2012-09-30','BBB',98.52),
('2012-08-31','BBB',103.66),
('2012-07-31','BBB',103.55),
('2012-06-30','BBB',99.08),
('2012-05-31','BBB',90.17),
('2012-04-30','BBB',99.05),
('2012-03-31','BBB',99.85),
('2012-02-29','BBB',97.77),
('2012-01-31','BBB',93.11),
('2011-12-31','BBB',107.23),
('2011-11-30','BBB',88.77),
('2011-10-31','BBB',91.39),
('2011-09-30','BBB',104.57),
('2011-08-31','BBB',89.04),
('2011-07-31','BBB',104.02),
('2011-06-30','BBB',94.51),
('2011-05-31','BBB',110.92),
('2011-04-30','BBB',105.48),
('2011-03-31','BBB',103.38),
('2011-02-28','BBB',90.51),
('2011-01-31','BBB',90.08),
('2010-12-31','BBB',97.22),
('2010-11-30','BBB',92.71),
('2010-10-31','BBB',100.14),
('2010-09-30','BBB',100.09),
('2010-08-31','BBB',100.04),
('2010-07-31','BBB',103.36),
('2010-06-30','BBB',96.24),
('2010-05-31','BBB',103.71),
('2010-04-30','BBB',105.03),
('2010-03-31','BBB',98.58),
('2010-02-28','BBB',98.59),
('2010-01-31','BBB',103.69),
('2009-12-31','BBB',103.58),
('2009-11-30','BBB',98.86),
('2009-10-31','BBB',97.42),
('2009-09-30','BBB',100.01),
('2009-08-31','BBB',92.4),
('2009-07-31','BBB',107.93),
('2009-06-30','BBB',105.77),
('2009-05-31','BBB',88.62),
('2009-04-30','BBB',103.07),
('2009-03-31','BBB',109.46),
('2009-02-28','BBB',94.24),
('2009-01-31','BBB',92.54),
('2008-12-31','BBB',101.87),
('2013-12-31','CCC',73.04),
('2013-11-30','CCC',72.65),
('2013-10-31','CCC',68.81),
('2013-09-30','CCC',66.05),
('2013-08-31','CCC',58.86),
('2013-07-31','CCC',67.41),
('2013-06-30','CCC',71.37),
('2013-05-31','CCC',81.44),
('2013-04-30','CCC',78.01),
('2013-03-31','CCC',62.76),
('2013-02-28','CCC',72.69),
('2013-01-31','CCC',76.76),
('2012-12-31','CCC',80.51),
('2012-11-30','CCC',79.25),
('2012-10-31','CCC',78.97),
('2012-09-30','CCC',87.03),
('2012-08-31','CCC',87.31),
('2012-07-31','CCC',70.13),
('2012-06-30','CCC',77.34),
('2012-05-31','CCC',73.11),
('2012-04-30','CCC',77.64),
('2012-03-31','CCC',80.5),
('2012-02-29','CCC',66.44),
('2012-01-31','CCC',84.58),
('2011-12-31','CCC',72.52),
('2011-11-30','CCC',66.47),
('2011-10-31','CCC',59.29),
('2011-09-30','CCC',79.52),
('2011-08-31','CCC',77.67),
('2011-07-31','CCC',82.81),
('2011-06-30','CCC',75.64),
('2011-05-31','CCC',70.9),
('2011-04-30','CCC',79.38),
('2011-03-31','CCC',71.51),
('2011-02-28','CCC',80.92),
('2011-01-31','CCC',80.27),
('2010-12-31','CCC',82.46),
('2010-11-30','CCC',68.57),
('2010-10-31','CCC',78.22),
('2010-09-30','CCC',74.26),
('2010-08-31','CCC',59.61),
('2010-07-31','CCC',77.98),
('2010-06-30','CCC',78.26),
('2010-05-31','CCC',67.34),
('2010-04-30','CCC',75.53),
('2010-03-31','CCC',78.84),
('2010-02-28','CCC',82.37),
('2010-01-31','CCC',56.64),
('2009-12-31','CCC',83.74),
('2009-11-30','CCC',64.38),
('2009-10-31','CCC',79.84),
('2009-09-30','CCC',63.9),
('2009-08-31','CCC',71.18),
('2009-07-31','CCC',63.73),
('2009-06-30','CCC',74.11),
('2009-05-31','CCC',65.69),
('2009-04-30','CCC',83.46),
('2009-03-31','CCC',75.83),
('2009-02-28','CCC',69.08),
('2009-01-31','CCC',79.03),
('2008-12-31','CCC',69.56),
('2013-12-31','RF',0.000721),
('2013-11-30','RF',0.000794),
('2013-10-31','RF',0.00082),
('2013-09-30','RF',0.000774),
('2013-08-31','RF',0.000869),
('2013-07-31','RF',0.00087),
('2013-06-30','RF',0.000845),
('2013-05-31','RF',0.000796),
('2013-04-30','RF',0.000832),
('2013-03-31','RF',0.00087),
('2013-02-28','RF',0.000826),
('2013-01-31','RF',0.000806),
('2012-12-31','RF',0.000815),
('2012-11-30','RF',0.000845),
('2012-10-31','RF',0.000775),
('2012-09-30','RF',0.000859),
('2012-08-31','RF',0.000833),
('2012-07-31','RF',0.000813),
('2012-06-30','RF',0.000792),
('2012-05-31','RF',0.00076),
('2012-04-30','RF',0.000849),
('2012-03-31','RF',0.000753),
('2012-02-29','RF',0.000788),
('2012-01-31','RF',0.000801),
('2011-12-31','RF',0.000793),
('2011-11-30','RF',0.000783),
('2011-10-31','RF',0.000764),
('2011-09-30','RF',0.000821),
('2011-08-31','RF',0.000762),
('2011-07-31','RF',0.000737),
('2011-06-30','RF',0.000794),
('2011-05-31','RF',0.000833),
('2011-04-30','RF',0.000818),
('2011-03-31','RF',0.000778),
('2011-02-28','RF',0.000782),
('2011-01-31','RF',0.00078),
('2010-12-31','RF',0.000738),
('2010-11-30','RF',0.00084),
('2010-10-31','RF',0.000693),
('2010-09-30','RF',0.000777),
('2010-08-31','RF',0.000799),
('2010-07-31','RF',0.000785),
('2010-06-30','RF',0.000856),
('2010-05-31','RF',0.000731),
('2010-04-30','RF',0.000718),
('2010-03-31','RF',0.000854),
('2010-02-28','RF',0.000752),
('2010-01-31','RF',0.000804),
('2009-12-31','RF',0.000721),
('2009-11-30','RF',0.00092),
('2009-10-31','RF',0.000774),
('2009-09-30','RF',0.000846),
('2009-08-31','RF',0.000767),
('2009-07-31','RF',0.000815),
('2009-06-30','RF',0.000721),
('2009-05-31','RF',0.0008),
('2009-04-30','RF',0.000798),
('2009-03-31','RF',0.00077),
('2009-02-28','RF',0.000823),
('2009-01-31','RF',0.000852),
('2008-12-31','RF',NULL)
)n(eom,sym,pr)
SELECT s1.EOM
,s1.sym
,wct.MovingSHARPE(s1.pr, s2.pr / 12, 12, 'True', 36, ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1, 'True') as SHARPE
FROM #s s1
JOIN #s s2
ON s1.eom = s2.eom
WHERE s1.sym <> 'RF'
AND s2.sym = 'RF'
This produces the following result.
EOM sym SHARPE
---------- ---- ----------------------
2008-12-31 AAA NULL
2009-01-31 AAA NULL
2009-02-28 AAA NULL
2009-03-31 AAA NULL
2009-04-30 AAA NULL
2009-05-31 AAA NULL
2009-06-30 AAA NULL
2009-07-31 AAA NULL
2009-08-31 AAA NULL
2009-09-30 AAA NULL
2009-10-31 AAA NULL
2009-11-30 AAA NULL
2009-12-31 AAA NULL
2010-01-31 AAA NULL
2010-02-28 AAA NULL
2010-03-31 AAA NULL
2010-04-30 AAA NULL
2010-05-31 AAA NULL
2010-06-30 AAA NULL
2010-07-31 AAA NULL
2010-08-31 AAA NULL
2010-09-30 AAA NULL
2010-10-31 AAA NULL
2010-11-30 AAA NULL
2010-12-31 AAA NULL
2011-01-31 AAA NULL
2011-02-28 AAA NULL
2011-03-31 AAA NULL
2011-04-30 AAA NULL
2011-05-31 AAA NULL
2011-06-30 AAA NULL
2011-07-31 AAA NULL
2011-08-31 AAA NULL
2011-09-30 AAA NULL
2011-10-31 AAA NULL
2011-11-30 AAA NULL
2011-12-31 AAA 0.248869936288371
2012-01-31 AAA 0.444030657476935
2012-02-29 AAA 0.248792722990318
2012-03-31 AAA 0.203083207889045
2012-04-30 AAA 0.0847920478667545
2012-05-31 AAA 0.342342084412725
2012-06-30 AAA 0.187899115011843
2012-07-31 AAA 0.331413035758184
2012-08-31 AAA 0.0082569977142725
2012-09-30 AAA 0.282502219693606
2012-10-31 AAA 0.246017206660439
2012-11-30 AAA 0.0848170952964925
2012-12-31 AAA 0.313913275318589
2013-01-31 AAA 0.337770906474395
2013-02-28 AAA 0.223839622172846
2013-03-31 AAA 0.265626718562816
2013-04-30 AAA 0.231244506212362
2013-05-31 AAA 0.30279803018456
2013-06-30 AAA 0.251821877144929
2013-07-31 AAA 0.180328302175138
2013-08-31 AAA 0.359095576511804
2013-09-30 AAA 0.167172937091591
2013-10-31 AAA 0.266787265679094
2013-11-30 AAA 0.470464851022464
2013-12-31 AAA 0.318701344232423
2008-12-31 BBB NULL
2009-01-31 BBB NULL
2009-02-28 BBB NULL
2009-03-31 BBB NULL
2009-04-30 BBB NULL
2009-05-31 BBB NULL
2009-06-30 BBB NULL
2009-07-31 BBB NULL
2009-08-31 BBB NULL
2009-09-30 BBB NULL
2009-10-31 BBB NULL
2009-11-30 BBB NULL
2009-12-31 BBB NULL
2010-01-31 BBB NULL
2010-02-28 BBB NULL
2010-03-31 BBB NULL
2010-04-30 BBB NULL
2010-05-31 BBB NULL
2010-06-30 BBB NULL
2010-07-31 BBB NULL
2010-08-31 BBB NULL
2010-09-30 BBB NULL
2010-10-31 BBB NULL
2010-11-30 BBB NULL
2010-12-31 BBB NULL
2011-01-31 BBB NULL
2011-02-28 BBB NULL
2011-03-31 BBB NULL
2011-04-30 BBB NULL
2011-05-31 BBB NULL
2011-06-30 BBB NULL
2011-07-31 BBB NULL
2011-08-31 BBB NULL
2011-09-30 BBB NULL
2011-10-31 BBB NULL
2011-11-30 BBB NULL
2011-12-31 BBB 0.208258776754981
2012-01-31 BBB 0.16574577438707
2012-02-29 BBB 0.196466117240958
2012-03-31 BBB 0.0607469271531955
2012-04-30 BBB 0.112890465476562
2012-05-31 BBB 0.168198382224739
2012-06-30 BBB 0.0722340286982434
2012-07-31 BBB 0.0991413626601237
2012-08-31 BBB 0.269552978279016
2012-09-30 BBB 0.119109595292273
2012-10-31 BBB 0.313524603030919
2012-11-30 BBB 0.21607199490278
2012-12-31 BBB 0.122140282938515
2013-01-31 BBB 0.157219556188017
2013-02-28 BBB 0.13992308863818
2013-03-31 BBB 0.130803444664502
2013-04-30 BBB 0.0599270322838943
2013-05-31 BBB 0.00186872862619966
2013-06-30 BBB 0.128041532129811
2013-07-31 BBB 0.157324024529796
2013-08-31 BBB 0.209678237365825
2013-09-30 BBB -0.0213756831322736
2013-10-31 BBB 0.238926198796967
2013-11-30 BBB 0.204810216198232
2013-12-31 BBB 0.176820754826674
2008-12-31 CCC NULL
2009-01-31 CCC NULL
2009-02-28 CCC NULL
2009-03-31 CCC NULL
2009-04-30 CCC NULL
2009-05-31 CCC NULL
2009-06-30 CCC NULL
2009-07-31 CCC NULL
2009-08-31 CCC NULL
2009-09-30 CCC NULL
2009-10-31 CCC NULL
2009-11-30 CCC NULL
2009-12-31 CCC NULL
2010-01-31 CCC NULL
2010-02-28 CCC NULL
2010-03-31 CCC NULL
2010-04-30 CCC NULL
2010-05-31 CCC NULL
2010-06-30 CCC NULL
2010-07-31 CCC NULL
2010-08-31 CCC NULL
2010-09-30 CCC NULL
2010-10-31 CCC NULL
2010-11-30 CCC NULL
2010-12-31 CCC NULL
2011-01-31 CCC NULL
2011-02-28 CCC NULL
2011-03-31 CCC NULL
2011-04-30 CCC NULL
2011-05-31 CCC NULL
2011-06-30 CCC NULL
2011-07-31 CCC NULL
2011-08-31 CCC NULL
2011-09-30 CCC NULL
2011-10-31 CCC NULL
2011-11-30 CCC NULL
2011-12-31 CCC 0.305949266953625
2012-01-31 CCC 0.321714227796533
2012-02-29 CCC 0.267554618402105
2012-03-31 CCC 0.325847052146292
2012-04-30 CCC 0.252013983893556
2012-05-31 CCC 0.344163101813709
2012-06-30 CCC 0.306397597366812
2012-07-31 CCC 0.334794025715534
2012-08-31 CCC 0.399167178968334
2012-09-30 CCC 0.457345782797578
2012-10-31 CCC 0.271650840198988
2012-11-30 CCC 0.392840231089245
2012-12-31 CCC 0.237114519995295
2013-01-31 CCC 0.43468504198757
2013-02-28 CCC 0.119647843926732
2013-03-31 CCC 0.0481173782012198
2013-04-30 CCC 0.248873242034151
2013-05-31 CCC 0.361162711248407
2013-06-30 CCC 0.156214651648683
2013-07-31 CCC 0.118365575984153
2013-08-31 CCC 0.20343443889538
2013-09-30 CCC 0.115791064184791
2013-10-31 CCC 0.10694144807856
2013-11-30 CCC 0.249252803692825
2013-12-31 CCC 0.0979063913136712
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.MovingSHARPE(s1.pr, s2.pr / 12, 12, 'True', 36, ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1, 'True') 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
---------- ---------------------- ---------------------- ----------------------
2008-12-31 NULL NULL NULL
2009-01-31 NULL NULL NULL
2009-02-28 NULL NULL NULL
2009-03-31 NULL NULL NULL
2009-04-30 NULL NULL NULL
2009-05-31 NULL NULL NULL
2009-06-30 NULL NULL NULL
2009-07-31 NULL NULL NULL
2009-08-31 NULL NULL NULL
2009-09-30 NULL NULL NULL
2009-10-31 NULL NULL NULL
2009-11-30 NULL NULL NULL
2009-12-31 NULL NULL NULL
2010-01-31 NULL NULL NULL
2010-02-28 NULL NULL NULL
2010-03-31 NULL NULL NULL
2010-04-30 NULL NULL NULL
2010-05-31 NULL NULL NULL
2010-06-30 NULL NULL NULL
2010-07-31 NULL NULL NULL
2010-08-31 NULL NULL NULL
2010-09-30 NULL NULL NULL
2010-10-31 NULL NULL NULL
2010-11-30 NULL NULL NULL
2010-12-31 NULL NULL NULL
2011-01-31 NULL NULL NULL
2011-02-28 NULL NULL NULL
2011-03-31 NULL NULL NULL
2011-04-30 NULL NULL NULL
2011-05-31 NULL NULL NULL
2011-06-30 NULL NULL NULL
2011-07-31 NULL NULL NULL
2011-08-31 NULL NULL NULL
2011-09-30 NULL NULL NULL
2011-10-31 NULL NULL NULL
2011-11-30 NULL NULL NULL
2011-12-31 0.248869936288371 0.208258776754981 0.305949266953625
2012-01-31 0.444030657476935 0.16574577438707 0.321714227796533
2012-02-29 0.248792722990318 0.196466117240958 0.267554618402105
2012-03-31 0.203083207889045 0.0607469271531955 0.325847052146292
2012-04-30 0.0847920478667545 0.112890465476562 0.252013983893556
2012-05-31 0.342342084412725 0.168198382224739 0.344163101813709
2012-06-30 0.187899115011843 0.0722340286982434 0.306397597366812
2012-07-31 0.331413035758184 0.0991413626601237 0.334794025715534
2012-08-31 0.0082569977142725 0.269552978279016 0.399167178968334
2012-09-30 0.282502219693606 0.119109595292273 0.457345782797578
2012-10-31 0.246017206660439 0.313524603030919 0.271650840198988
2012-11-30 0.0848170952964925 0.21607199490278 0.392840231089245
2012-12-31 0.313913275318589 0.122140282938515 0.237114519995295
2013-01-31 0.337770906474395 0.157219556188017 0.43468504198757
2013-02-28 0.223839622172846 0.13992308863818 0.119647843926732
2013-03-31 0.265626718562816 0.130803444664502 0.0481173782012198
2013-04-30 0.231244506212362 0.0599270322838943 0.248873242034151
2013-05-31 0.30279803018456 0.00186872862619966 0.361162711248407
2013-06-30 0.251821877144929 0.128041532129811 0.156214651648683
2013-07-31 0.180328302175138 0.157324024529796 0.118365575984153
2013-08-31 0.359095576511804 0.209678237365825 0.20343443889538
2013-09-30 0.167172937091591 -0.0213756831322736 0.115791064184791
2013-10-31 0.266787265679094 0.238926198796967 0.10694144807856
2013-11-30 0.470464851022464 0.204810216198232 0.249252803692825
2013-12-31 0.318701344232423 0.176820754826674 0.0979063913136712