MovingINFORATIO
Updated: 30 Jun 2013
Use MovingINFORATIO to calculate the information ratio from column values in an ordered resultant table without the need for a self-join. The information 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 information ratio is calculated as the mean difference of the returns and a benchmark return 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. The standard deviation is the sample standard deviation.
Syntax
SELECT [westclintech].[wct].[MovingINFORATIO](
<@R, float,>
,<@Rb, 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.
@Rb
the benchmark return or price; if a benchmark return is being supplied, is should be the percentage in floating point format (i.e. 10% = 0.1). @Rb 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 and @Rb values are prices (or portfolio values) or returns. If @Prices is true, then the returns are 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 information 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 MovingINFORATIO calculation. @Id allows you to specify multiple information ratios 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 information ratio with a window size of 36 rows.
SELECT cast(eom as date) as eom
,CAST(r as float) as r
,CAST(rb as float) as rb
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,rb)
SELECT eom
,r
,rb
,wct.MovingINFORATIO(
r --@R
,rb --@Rf
,12 --@Scale
,'False' --@Prices
,36 --@Offset
,ROW_NUMBER() OVER (ORDER BY eom ASC) --@RowNum
,NULL --@Id
,'True' --@Exact
) as INFO
FROM #s
DROP TABLE #s
This produces the following result.
eom r rb INFO
---------- ---------------------- ---------------------- ----------------------
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 money) as pr
,CAST(bmk as money) as bmk
INTO #s
FROM (VALUES
('2013-12-31',70.31,104.91),
('2013-11-30',69.88,104.83),
('2013-10-31',69.56,104.75),
('2013-09-30',70.37,104.66),
('2013-08-31',70.48,104.58),
('2013-07-31',70.08,104.49),
('2013-06-30',70.06,104.4),
('2013-05-31',69.61,104.31),
('2013-04-30',69.26,104.23),
('2013-03-31',69.09,104.14),
('2013-02-28',69.94,104.05),
('2013-01-31',70.34,103.96),
('2012-12-31',69.8,103.88),
('2012-11-30',69.89,103.8),
('2012-10-31',69.94,103.71),
('2012-09-30',69.28,103.63),
('2012-08-31',68.06,103.54),
('2012-07-31',67.28,103.45),
('2012-06-30',67.4,103.37),
('2012-05-31',67.32,103.29),
('2012-04-30',67.35,103.21),
('2012-03-31',67.03,103.12),
('2012-02-29',65.84,103.04),
('2012-01-31',65.19,102.96),
('2011-12-31',64.36,102.88),
('2011-11-30',63.27,102.8),
('2011-10-31',63.64,102.72),
('2011-09-30',63.35,102.64),
('2011-08-31',63.03,102.55),
('2011-07-31',62.08,102.48),
('2011-06-30',61.91,102.4),
('2011-05-31',61.65,102.32),
('2011-04-30',61.22,102.23),
('2011-03-31',61.11,102.15),
('2011-02-28',60.93,102.07),
('2011-01-31',60.86,101.99),
('2010-12-31',60.36,101.91),
('2010-11-30',59.91,101.84),
('2010-10-31',59.34,101.75),
('2010-09-30',58.91,101.68),
('2010-08-31',58.02,101.6),
('2010-07-31',57.63,101.52),
('2010-06-30',56.89,101.44),
('2010-05-31',56.7,101.36),
('2010-04-30',56.15,101.28),
('2010-03-31',55.36,101.21),
('2010-02-28',55.09,101.12),
('2010-01-31',55.17,101.05),
('2009-12-31',54.62,100.96),
('2009-11-30',54.27,100.89),
('2009-10-31',54.21,100.8),
('2009-09-30',53.69,100.72),
('2009-08-31',53.62,100.64),
('2009-07-31',53.55,100.56),
('2009-06-30',53.09,100.48),
('2009-05-31',52.3,100.4),
('2009-04-30',52.41,100.32),
('2009-03-31',51.59,100.24),
('2009-02-28',50.67,100.17),
('2009-01-31',50,100.09),
('2008-12-31',50,100)
)n(eom,pr,bmk)
SELECT eom
,pr
,bmk
,wct.MovingInfoRatio(
pr --@R
,bmk --@Rb
,12 --@Scale
,'True' --@Prices
,36 --@Offset
,ROW_NUMBER() OVER (ORDER BY eom ASC) --@RowNum
,NULL --@Id
,'True' --@Exact
) as INFO
FROM #s
DROP TABLE #s
This produces the following result.
eom pr bmk INFO
---------- --------------------- --------------------- ----------------------
2008-12-31 50.00 100.00 NULL
2009-01-31 50.00 100.09 NULL
2009-02-28 50.67 100.17 NULL
2009-03-31 51.59 100.24 NULL
2009-04-30 52.41 100.32 NULL
2009-05-31 52.30 100.40 NULL
2009-06-30 53.09 100.48 NULL
2009-07-31 53.55 100.56 NULL
2009-08-31 53.62 100.64 NULL
2009-09-30 53.69 100.72 NULL
2009-10-31 54.21 100.80 NULL
2009-11-30 54.27 100.89 NULL
2009-12-31 54.62 100.96 NULL
2010-01-31 55.17 101.05 NULL
2010-02-28 55.09 101.12 NULL
2010-03-31 55.36 101.21 NULL
2010-04-30 56.15 101.28 NULL
2010-05-31 56.70 101.36 NULL
2010-06-30 56.89 101.44 NULL
2010-07-31 57.63 101.52 NULL
2010-08-31 58.02 101.60 NULL
2010-09-30 58.91 101.68 NULL
2010-10-31 59.34 101.75 NULL
2010-11-30 59.91 101.84 NULL
2010-12-31 60.36 101.91 NULL
2011-01-31 60.86 101.99 NULL
2011-02-28 60.93 102.07 NULL
2011-03-31 61.11 102.15 NULL
2011-04-30 61.22 102.23 NULL
2011-05-31 61.65 102.32 NULL
2011-06-30 61.91 102.40 NULL
2011-07-31 62.08 102.48 NULL
2011-08-31 63.03 102.55 NULL
2011-09-30 63.35 102.64 NULL
2011-10-31 63.64 102.72 NULL
2011-11-30 63.27 102.80 NULL
2011-12-31 64.36 102.88 3.58676647770117
2012-01-31 65.19 102.96 3.82645525563777
2012-02-29 65.84 103.04 3.81662592575437
2012-03-31 67.03 103.12 3.81942228173519
2012-04-30 67.35 103.21 3.74586731957684
2012-05-31 67.32 103.29 3.82009916798985
2012-06-30 67.40 103.37 3.64300308949477
2012-07-31 67.28 103.45 3.36627840205728
2012-08-31 68.06 103.54 3.54066825327087
2012-09-30 69.28 103.63 3.67913079309479
2012-10-31 69.94 103.71 3.67793484228446
2012-11-30 69.89 103.80 3.61263646816859
2012-12-31 69.80 103.88 3.39845497725758
2013-01-31 70.34 103.96 3.37543427064359
2013-02-28 69.94 104.05 3.19899177463182
2013-03-31 69.09 104.14 2.63763958109604
2013-04-30 69.26 104.23 2.51568680151571
2013-05-31 69.61 104.31 2.46184423321181
2013-06-30 70.06 104.40 2.50798818529081
2013-07-31 70.08 104.49 2.34800967176227
2013-08-31 70.48 104.58 2.33339463359427
2013-09-30 70.37 104.66 2.13498079784971
2013-10-31 69.56 104.75 1.73097464944098
2013-11-30 69.88 104.83 1.67845680576442
2013-12-31 70.31 104.91 1.66280544275062
In this example we calculate the information ratio for multiple portfolios simultaneously. Notice that we have to use a different value in @Id for each information ratio 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(BMK as money) as BMK
INTO #s
FROM (VALUES
('2013-12-31',61.27,169.65,97.98,148.82),
('2013-11-30',61.08,167.14,97.7,148.27),
('2013-10-31',60.88,165.64,97.82,147.47),
('2013-09-30',60.66,163.21,97.74,145.95),
('2013-08-31',60.44,163.01,96.64,143.12),
('2013-07-31',60.25,165.28,97.16,143.6),
('2013-06-30',60.05,166.37,96.59,141.63),
('2013-05-31',59.83,164.31,96.67,141.13),
('2013-04-30',59.6,164.05,95.87,140.78),
('2013-03-31',59.38,161.58,95.85,140.76),
('2013-02-28',59.09,161.13,95.94,140.11),
('2013-01-31',58.9,159.4,95.6,139.16),
('2012-12-31',58.74,158.12,94.9,137.33),
('2012-11-30',58.45,154.55,94.15,136.05),
('2012-10-31',58.26,155.28,93.13,135.79),
('2012-09-30',58.03,155.16,91.83,135.83),
('2012-08-31',57.79,152.62,90.8,135.1),
('2012-07-31',57.63,151.5,90.15,134.01),
('2012-06-30',57.45,151.46,89.88,132.21),
('2012-05-31',57.22,149.27,89.41,131.02),
('2012-04-30',57.07,147.52,88.67,129.33),
('2012-03-31',56.92,147.29,88.81,128.45),
('2012-02-29',56.74,145.65,88.25,127.07),
('2012-01-31',56.46,142.62,88.35,126.12),
('2011-12-31',56.26,142.36,88.13,125.11),
('2011-11-30',56.07,140.5,87.64,123.79),
('2011-10-31',55.87,139.13,87.16,122.94),
('2011-09-30',55.71,137.5,86.64,122.55),
('2011-08-31',55.52,137.73,86.79,121.82),
('2011-07-31',55.34,135.45,85.37,120.17),
('2011-06-30',55.19,135.27,85.04,119.14),
('2011-05-31',55.02,133.4,83.79,118.73),
('2011-04-30',54.84,133.32,83.87,117.49),
('2011-03-31',54.65,131.37,83.9,117.93),
('2011-02-28',54.4,129.37,83.65,116.67),
('2011-01-31',54.28,128.24,83.88,116.53),
('2010-12-31',54.1,127,82.83,115.78),
('2010-11-30',53.93,125.76,82.58,114.58),
('2010-10-31',53.8,125.95,82.12,113.8),
('2010-09-30',53.62,124.8,81.19,113.23),
('2010-08-31',53.38,121.84,80.42,112.69),
('2010-07-31',53.21,120.64,80.39,110.27),
('2010-06-30',53.06,119.69,79.47,109.11),
('2010-05-31',52.91,121.53,79.18,108.52),
('2010-04-30',52.74,120.49,78.54,106.97),
('2010-03-31',52.59,118.04,77.17,106.3),
('2010-02-28',52.45,115.21,77.06,105.28),
('2010-01-31',52.23,114.18,76.85,105.39),
('2009-12-31',52.09,112.7,76.73,105.73),
('2009-11-30',51.91,110.84,76.68,105.42),
('2009-10-31',51.66,110.09,76.97,104.83),
('2009-09-30',51.53,108.19,76.67,104.5),
('2009-08-31',51.4,107.4,77.14,104.42),
('2009-07-31',51.22,106.72,76.85,103.98),
('2009-06-30',51.05,105.57,76.62,102.58),
('2009-05-31',50.88,105.55,76.22,102.99),
('2009-04-30',50.77,103.41,75.83,101.56),
('2009-03-31',50.65,103.5,75.88,101),
('2009-02-28',50.44,102.87,76,101.24),
('2009-01-31',50.34,101.01,75.29,102.25),
('2008-12-31',50.17,99.92,75.45,101.21)
)n(eom,AAA,BBB,CCC,BMK)
SELECT eom
,wct.MovingINFORATIO(AAA,BMK,12,'True',36,ROW_NUMBER() OVER (ORDER BY EOM ASC),1,'TRUE') as AAA
,wct.MovingINFORATIO(BBB,BMK,12,'True',36,ROW_NUMBER() OVER (ORDER BY EOM ASC),2,'TRUE') as BBB
,wct.MovingINFORATIO(CCC,BMK,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 -1.50264335385127 1.36339934163092 -0.680484554124345
2012-01-31 -1.47405028124021 1.28172706095312 -0.613067733094883
2012-02-29 -1.84676037014358 1.23307512240945 -1.0556643740288
2012-03-31 -2.15641678352096 1.15476509186243 -1.12857934562344
2012-04-30 -2.1739413324635 1.16992728545154 -1.15148862718521
2012-05-31 -2.17128210170209 1.0894336069323 -1.10663872200846
2012-06-30 -2.5317422089487 1.10289847320593 -1.33589225180152
2012-07-31 -2.53188351427334 0.96073332708266 -1.33562547382318
2012-08-31 -2.62837551419835 0.930951955594759 -1.34294940890139
2012-09-30 -2.72145792313755 0.968198232770012 -1.14410892844854
2012-10-31 -2.55195013877502 0.85615565913842 -0.885800768444971
2012-11-30 -2.49044158602105 0.76997655662585 -0.63863952256145
2012-12-31 -2.6058963440003 0.768909077101311 -0.627978199910875
2013-01-31 -3.00315246759007 0.571397151287231 -0.76868217990417
2013-02-28 -3.35044041416273 0.516290645810907 -0.86526391043653
2013-03-31 -3.17148170381401 0.357226587544548 -0.836517628192648
2013-04-30 -2.92585906326791 0.363271637643036 -1.03293876400083
2013-05-31 -2.71821547287965 0.417827941047359 -0.856405284557855
2013-06-30 -2.64361364266275 0.787047673692989 -0.889764187487621
2013-07-31 -2.6529046127412 0.537841694271647 -1.0024183141239
2013-08-31 -2.35020101018412 0.558355847746184 -0.836435734282757
2013-09-30 -2.44660010597957 0.159096275100095 -1.03422090678605
2013-10-31 -2.53463230735959 0.162088282377083 -1.28325469416878
2013-11-30 -2.48913598084977 0.290293376295917 -1.35717062783412
2013-12-31 -2.37004988731189 0.408487055655326 -1.26791636115069
In this example, we have the same data as in the previous example, except that it is stored in 3rd normal form rather than in the de-normalized form. The benchmark is included in the table with using the symbol BMK. 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',61.27),
('2013-11-30','AAA',61.08),
('2013-10-31','AAA',60.88),
('2013-09-30','AAA',60.66),
('2013-08-31','AAA',60.44),
('2013-07-31','AAA',60.25),
('2013-06-30','AAA',60.05),
('2013-05-31','AAA',59.83),
('2013-04-30','AAA',59.6),
('2013-03-31','AAA',59.38),
('2013-02-28','AAA',59.09),
('2013-01-31','AAA',58.9),
('2012-12-31','AAA',58.74),
('2012-11-30','AAA',58.45),
('2012-10-31','AAA',58.26),
('2012-09-30','AAA',58.03),
('2012-08-31','AAA',57.79),
('2012-07-31','AAA',57.63),
('2012-06-30','AAA',57.45),
('2012-05-31','AAA',57.22),
('2012-04-30','AAA',57.07),
('2012-03-31','AAA',56.92),
('2012-02-29','AAA',56.74),
('2012-01-31','AAA',56.46),
('2011-12-31','AAA',56.26),
('2011-11-30','AAA',56.07),
('2011-10-31','AAA',55.87),
('2011-09-30','AAA',55.71),
('2011-08-31','AAA',55.52),
('2011-07-31','AAA',55.34),
('2011-06-30','AAA',55.19),
('2011-05-31','AAA',55.02),
('2011-04-30','AAA',54.84),
('2011-03-31','AAA',54.65),
('2011-02-28','AAA',54.4),
('2011-01-31','AAA',54.28),
('2010-12-31','AAA',54.1),
('2010-11-30','AAA',53.93),
('2010-10-31','AAA',53.8),
('2010-09-30','AAA',53.62),
('2010-08-31','AAA',53.38),
('2010-07-31','AAA',53.21),
('2010-06-30','AAA',53.06),
('2010-05-31','AAA',52.91),
('2010-04-30','AAA',52.74),
('2010-03-31','AAA',52.59),
('2010-02-28','AAA',52.45),
('2010-01-31','AAA',52.23),
('2009-12-31','AAA',52.09),
('2009-11-30','AAA',51.91),
('2009-10-31','AAA',51.66),
('2009-09-30','AAA',51.53),
('2009-08-31','AAA',51.4),
('2009-07-31','AAA',51.22),
('2009-06-30','AAA',51.05),
('2009-05-31','AAA',50.88),
('2009-04-30','AAA',50.77),
('2009-03-31','AAA',50.65),
('2009-02-28','AAA',50.44),
('2009-01-31','AAA',50.34),
('2008-12-31','AAA',50.17),
('2013-12-31','BBB',169.65),
('2013-11-30','BBB',167.14),
('2013-10-31','BBB',165.64),
('2013-09-30','BBB',163.21),
('2013-08-31','BBB',163.01),
('2013-07-31','BBB',165.28),
('2013-06-30','BBB',166.37),
('2013-05-31','BBB',164.31),
('2013-04-30','BBB',164.05),
('2013-03-31','BBB',161.58),
('2013-02-28','BBB',161.13),
('2013-01-31','BBB',159.4),
('2012-12-31','BBB',158.12),
('2012-11-30','BBB',154.55),
('2012-10-31','BBB',155.28),
('2012-09-30','BBB',155.16),
('2012-08-31','BBB',152.62),
('2012-07-31','BBB',151.5),
('2012-06-30','BBB',151.46),
('2012-05-31','BBB',149.27),
('2012-04-30','BBB',147.52),
('2012-03-31','BBB',147.29),
('2012-02-29','BBB',145.65),
('2012-01-31','BBB',142.62),
('2011-12-31','BBB',142.36),
('2011-11-30','BBB',140.5),
('2011-10-31','BBB',139.13),
('2011-09-30','BBB',137.5),
('2011-08-31','BBB',137.73),
('2011-07-31','BBB',135.45),
('2011-06-30','BBB',135.27),
('2011-05-31','BBB',133.4),
('2011-04-30','BBB',133.32),
('2011-03-31','BBB',131.37),
('2011-02-28','BBB',129.37),
('2011-01-31','BBB',128.24),
('2010-12-31','BBB',127),
('2010-11-30','BBB',125.76),
('2010-10-31','BBB',125.95),
('2010-09-30','BBB',124.8),
('2010-08-31','BBB',121.84),
('2010-07-31','BBB',120.64),
('2010-06-30','BBB',119.69),
('2010-05-31','BBB',121.53),
('2010-04-30','BBB',120.49),
('2010-03-31','BBB',118.04),
('2010-02-28','BBB',115.21),
('2010-01-31','BBB',114.18),
('2009-12-31','BBB',112.7),
('2009-11-30','BBB',110.84),
('2009-10-31','BBB',110.09),
('2009-09-30','BBB',108.19),
('2009-08-31','BBB',107.4),
('2009-07-31','BBB',106.72),
('2009-06-30','BBB',105.57),
('2009-05-31','BBB',105.55),
('2009-04-30','BBB',103.41),
('2009-03-31','BBB',103.5),
('2009-02-28','BBB',102.87),
('2009-01-31','BBB',101.01),
('2008-12-31','BBB',99.92),
('2013-12-31','CCC',97.98),
('2013-11-30','CCC',97.7),
('2013-10-31','CCC',97.82),
('2013-09-30','CCC',97.74),
('2013-08-31','CCC',96.64),
('2013-07-31','CCC',97.16),
('2013-06-30','CCC',96.59),
('2013-05-31','CCC',96.67),
('2013-04-30','CCC',95.87),
('2013-03-31','CCC',95.85),
('2013-02-28','CCC',95.94),
('2013-01-31','CCC',95.6),
('2012-12-31','CCC',94.9),
('2012-11-30','CCC',94.15),
('2012-10-31','CCC',93.13),
('2012-09-30','CCC',91.83),
('2012-08-31','CCC',90.8),
('2012-07-31','CCC',90.15),
('2012-06-30','CCC',89.88),
('2012-05-31','CCC',89.41),
('2012-04-30','CCC',88.67),
('2012-03-31','CCC',88.81),
('2012-02-29','CCC',88.25),
('2012-01-31','CCC',88.35),
('2011-12-31','CCC',88.13),
('2011-11-30','CCC',87.64),
('2011-10-31','CCC',87.16),
('2011-09-30','CCC',86.64),
('2011-08-31','CCC',86.79),
('2011-07-31','CCC',85.37),
('2011-06-30','CCC',85.04),
('2011-05-31','CCC',83.79),
('2011-04-30','CCC',83.87),
('2011-03-31','CCC',83.9),
('2011-02-28','CCC',83.65),
('2011-01-31','CCC',83.88),
('2010-12-31','CCC',82.83),
('2010-11-30','CCC',82.58),
('2010-10-31','CCC',82.12),
('2010-09-30','CCC',81.19),
('2010-08-31','CCC',80.42),
('2010-07-31','CCC',80.39),
('2010-06-30','CCC',79.47),
('2010-05-31','CCC',79.18),
('2010-04-30','CCC',78.54),
('2010-03-31','CCC',77.17),
('2010-02-28','CCC',77.06),
('2010-01-31','CCC',76.85),
('2009-12-31','CCC',76.73),
('2009-11-30','CCC',76.68),
('2009-10-31','CCC',76.97),
('2009-09-30','CCC',76.67),
('2009-08-31','CCC',77.14),
('2009-07-31','CCC',76.85),
('2009-06-30','CCC',76.62),
('2009-05-31','CCC',76.22),
('2009-04-30','CCC',75.83),
('2009-03-31','CCC',75.88),
('2009-02-28','CCC',76),
('2009-01-31','CCC',75.29),
('2008-12-31','CCC',75.45),
('2013-12-31','BMK',148.82),
('2013-11-30','BMK',148.27),
('2013-10-31','BMK',147.47),
('2013-09-30','BMK',145.95),
('2013-08-31','BMK',143.12),
('2013-07-31','BMK',143.6),
('2013-06-30','BMK',141.63),
('2013-05-31','BMK',141.13),
('2013-04-30','BMK',140.78),
('2013-03-31','BMK',140.76),
('2013-02-28','BMK',140.11),
('2013-01-31','BMK',139.16),
('2012-12-31','BMK',137.33),
('2012-11-30','BMK',136.05),
('2012-10-31','BMK',135.79),
('2012-09-30','BMK',135.83),
('2012-08-31','BMK',135.1),
('2012-07-31','BMK',134.01),
('2012-06-30','BMK',132.21),
('2012-05-31','BMK',131.02),
('2012-04-30','BMK',129.33),
('2012-03-31','BMK',128.45),
('2012-02-29','BMK',127.07),
('2012-01-31','BMK',126.12),
('2011-12-31','BMK',125.11),
('2011-11-30','BMK',123.79),
('2011-10-31','BMK',122.94),
('2011-09-30','BMK',122.55),
('2011-08-31','BMK',121.82),
('2011-07-31','BMK',120.17),
('2011-06-30','BMK',119.14),
('2011-05-31','BMK',118.73),
('2011-04-30','BMK',117.49),
('2011-03-31','BMK',117.93),
('2011-02-28','BMK',116.67),
('2011-01-31','BMK',116.53),
('2010-12-31','BMK',115.78),
('2010-11-30','BMK',114.58),
('2010-10-31','BMK',113.8),
('2010-09-30','BMK',113.23),
('2010-08-31','BMK',112.69),
('2010-07-31','BMK',110.27),
('2010-06-30','BMK',109.11),
('2010-05-31','BMK',108.52),
('2010-04-30','BMK',106.97),
('2010-03-31','BMK',106.3),
('2010-02-28','BMK',105.28),
('2010-01-31','BMK',105.39),
('2009-12-31','BMK',105.73),
('2009-11-30','BMK',105.42),
('2009-10-31','BMK',104.83),
('2009-09-30','BMK',104.5),
('2009-08-31','BMK',104.42),
('2009-07-31','BMK',103.98),
('2009-06-30','BMK',102.58),
('2009-05-31','BMK',102.99),
('2009-04-30','BMK',101.56),
('2009-03-31','BMK',101),
('2009-02-28','BMK',101.24),
('2009-01-31','BMK',102.25),
('2008-12-31','BMK',101.21)
)n(eom,sym,pr)
SELECT s1.EOM
,s1.sym
,wct.MovingINFORATIO(s1.pr, s2.pr, 12, 'True', 36, ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1, 'True') as INFO
FROM #s s1
JOIN #s s2
ON s1.eom = s2.eom
WHERE s1.sym <> 'BMK'
AND s2.sym = 'BMK'
This produces the following result.
EOM sym INFO
---------- ---- ----------------------
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 -1.50264335385127
2012-01-31 AAA -1.47405028124021
2012-02-29 AAA -1.84676037014358
2012-03-31 AAA -2.15641678352096
2012-04-30 AAA -2.1739413324635
2012-05-31 AAA -2.17128210170209
2012-06-30 AAA -2.5317422089487
2012-07-31 AAA -2.53188351427334
2012-08-31 AAA -2.62837551419835
2012-09-30 AAA -2.72145792313755
2012-10-31 AAA -2.55195013877502
2012-11-30 AAA -2.49044158602105
2012-12-31 AAA -2.6058963440003
2013-01-31 AAA -3.00315246759007
2013-02-28 AAA -3.35044041416273
2013-03-31 AAA -3.17148170381401
2013-04-30 AAA -2.92585906326791
2013-05-31 AAA -2.71821547287965
2013-06-30 AAA -2.64361364266275
2013-07-31 AAA -2.6529046127412
2013-08-31 AAA -2.35020101018412
2013-09-30 AAA -2.44660010597957
2013-10-31 AAA -2.53463230735959
2013-11-30 AAA -2.48913598084977
2013-12-31 AAA -2.37004988731189
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 1.36339934163092
2012-01-31 BBB 1.28172706095312
2012-02-29 BBB 1.23307512240945
2012-03-31 BBB 1.15476509186243
2012-04-30 BBB 1.16992728545154
2012-05-31 BBB 1.0894336069323
2012-06-30 BBB 1.10289847320593
2012-07-31 BBB 0.96073332708266
2012-08-31 BBB 0.930951955594759
2012-09-30 BBB 0.968198232770012
2012-10-31 BBB 0.85615565913842
2012-11-30 BBB 0.76997655662585
2012-12-31 BBB 0.768909077101311
2013-01-31 BBB 0.571397151287231
2013-02-28 BBB 0.516290645810907
2013-03-31 BBB 0.357226587544548
2013-04-30 BBB 0.363271637643036
2013-05-31 BBB 0.417827941047359
2013-06-30 BBB 0.787047673692989
2013-07-31 BBB 0.537841694271647
2013-08-31 BBB 0.558355847746184
2013-09-30 BBB 0.159096275100095
2013-10-31 BBB 0.162088282377083
2013-11-30 BBB 0.290293376295917
2013-12-31 BBB 0.408487055655326
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.680484554124345
2012-01-31 CCC -0.613067733094883
2012-02-29 CCC -1.0556643740288
2012-03-31 CCC -1.12857934562344
2012-04-30 CCC -1.15148862718521
2012-05-31 CCC -1.10663872200846
2012-06-30 CCC -1.33589225180152
2012-07-31 CCC -1.33562547382318
2012-08-31 CCC -1.34294940890139
2012-09-30 CCC -1.14410892844854
2012-10-31 CCC -0.885800768444971
2012-11-30 CCC -0.63863952256145
2012-12-31 CCC -0.627978199910875
2013-01-31 CCC -0.76868217990417
2013-02-28 CCC -0.86526391043653
2013-03-31 CCC -0.836517628192648
2013-04-30 CCC -1.03293876400083
2013-05-31 CCC -0.856405284557855
2013-06-30 CCC -0.889764187487621
2013-07-31 CCC -1.0024183141239
2013-08-31 CCC -0.836435734282757
2013-09-30 CCC -1.03422090678605
2013-10-31 CCC -1.28325469416878
2013-11-30 CCC -1.35717062783412
2013-12-31 CCC -1.26791636115069
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.MovingINFORATIO(s1.pr, s2.pr, 12, 'True', 36, ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1, 'True') as INFO
FROM #s s1
JOIN #s s2
ON s1.eom = s2.eom
WHERE s1.sym <> 'BMK'
AND s2.sym = 'BMK') d
PIVOT(sum(INFO) 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 -1.50264335385127 1.36339934163092 -0.680484554124345
2012-01-31 -1.47405028124021 1.28172706095312 -0.613067733094883
2012-02-29 -1.84676037014358 1.23307512240945 -1.0556643740288
2012-03-31 -2.15641678352096 1.15476509186243 -1.12857934562344
2012-04-30 -2.1739413324635 1.16992728545154 -1.15148862718521
2012-05-31 -2.17128210170209 1.0894336069323 -1.10663872200846
2012-06-30 -2.5317422089487 1.10289847320593 -1.33589225180152
2012-07-31 -2.53188351427334 0.96073332708266 -1.33562547382318
2012-08-31 -2.62837551419835 0.930951955594759 -1.34294940890139
2012-09-30 -2.72145792313755 0.968198232770012 -1.14410892844854
2012-10-31 -2.55195013877502 0.85615565913842 -0.885800768444971
2012-11-30 -2.49044158602105 0.76997655662585 -0.63863952256145
2012-12-31 -2.6058963440003 0.768909077101311 -0.627978199910875
2013-01-31 -3.00315246759007 0.571397151287231 -0.76868217990417
2013-02-28 -3.35044041416273 0.516290645810907 -0.86526391043653
2013-03-31 -3.17148170381401 0.357226587544548 -0.836517628192648
2013-04-30 -2.92585906326791 0.363271637643036 -1.03293876400083
2013-05-31 -2.71821547287965 0.417827941047359 -0.856405284557855
2013-06-30 -2.64361364266275 0.787047673692989 -0.889764187487621
2013-07-31 -2.6529046127412 0.537841694271647 -1.0024183141239
2013-08-31 -2.35020101018412 0.558355847746184 -0.836435734282757
2013-09-30 -2.44660010597957 0.159096275100095 -1.03422090678605
2013-10-31 -2.53463230735959 0.162088282377083 -1.28325469416878
2013-11-30 -2.48913598084977 0.290293376295917 -1.35717062783412
2013-12-31 -2.37004988731189 0.408487055655326 -1.26791636115069