Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running Information Ratio function


RunningINFORATIO

Updated: 30 Jun 2013


Use RunningINFORATIO to calculate the information ratio from column values in an ordered resultant table without the need of a self-join. The information 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 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.

XLeratorDB formula for the INFORATIO function for SQL Server
Syntax
SELECT [westclintech].[wct].[RunningINFORATIO](
  <@R, float,>
 ,<@Rb, float,>
 ,<@Scale, float,>
 ,<@Prices, bit,>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>)
Arguments
@R
the return or price value; if a return value is being supplied, it should be 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.
@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 RunningINFORATIO 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.
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 information ratio.
SELECT CAST(eom as date) as eom
,cast(r as float) as r
,Cast(rb as float) as rb
into #s
FROM (VALUES
      ('2012-01-31',0.008374,0.008161),
      ('2012-02-29',0.014544,0.009818),
      ('2012-03-31',0.010549,0.003224),
      ('2012-04-30',-0.003218,-0.003088),
      ('2012-05-31',0.001395,0.008685),
      ('2012-06-30',0.016985,0.01045),
      ('2012-07-31',0.007003,0.009179),
      ('2012-08-31',-0.000834,0.006815),
      ('2012-09-30',0.009294,0.007383),
      ('2012-10-31',0.012554,0.009464),
      ('2012-11-30',0.005767,0.002783),
      ('2012-12-31',-0.000496,0.005062)
      )n(eom,r,rb)
     
SELECT EOM
,r
,rb
,wct.RunningINFORATIO(
       r                --@R
      ,rb               --@Rb
      ,12               --@Scale
      ,'False'          --@Prices
      ,ROW_NUMBER() OVER (ORDER BY eom ASC)     --@RowNum
      ,NULL             --@Id
      ) as INFO
FROM #s
 
DROP TABLE #s
This produces the following result.
EOM                             r                     rb                   INFO
---------- ---------------------- ---------------------- ----------------------
2012-01-31               0.008374               0.008161                   NULL
2012-02-29               0.014544               0.009818       2.68070681134636
2012-03-31               0.010549               0.003224        3.9351346757467
2012-04-30              -0.003218              -0.003088       2.90540015030581
2012-05-31               0.001395               0.008685      0.601537550348331
2012-06-30               0.016985                0.01045       1.19816250281277
2012-07-31               0.007003               0.009179      0.869687568642996
2012-08-31              -0.000834               0.006815      0.116174630261801
2012-09-30               0.009294               0.007383      0.244792458708043
2012-10-31               0.012554               0.009464      0.436060082807129
2012-11-30               0.005767               0.002783      0.602033363783932
2012-12-31              -0.000496               0.005062      0.225057176618478

In this example we have 13 months’ worth of price data for and we want to calculate the information ratio.
SET NOCOUNT ON
 
SELECT CAST(eom as date) as eom
,cast(r as money) as r
,Cast(rb as money) as rb
into #s
FROM (VALUES
      ('2011-12-31',50,100),
      ('2012-01-31',50.42,100.82),
      ('2012-02-29',51.15,101.81),
      ('2012-03-31',51.69,102.13),
      ('2012-04-30',51.53,101.82),
      ('2012-05-31',51.6,102.7),
      ('2012-06-30',52.47,103.78),
      ('2012-07-31',52.84,104.73),
      ('2012-08-31',52.8,105.44),
      ('2012-09-30',53.29,106.22),
      ('2012-10-31',53.96,107.23),
      ('2012-11-30',54.27,107.52),
      ('2012-12-31',54.24,108.07)
      )n(eom,r,rb)
     
SELECT EOM
,r
,rb
,wct.RunningINFORATIO(
       r                --@R
      ,rb               --@Rb
      ,12               --@Scale
      ,'True'           --@Prices
      ,ROW_NUMBER() OVER (ORDER BY eom ASC)     --@RowNum
      ,NULL             --@Id
      ) as INFO
FROM #s
 
DROP TABLE #s
This produces the following result.
EOM                            r                    rb                   INFO
---------- --------------------- --------------------- ----------------------
2011-12-31                 50.00                100.00                   NULL
2012-01-31                 50.42                100.82                   NULL
2012-02-29                 51.15                101.81       2.66922905163852
2012-03-31                 51.69                102.13       3.89285791187229
2012-04-30                 51.53                101.82       2.91747312517382
2012-05-31                 51.60                102.70      0.611034090753194
2012-06-30                 52.47                103.78       1.19287152750386
2012-07-31                 52.84                104.73      0.872234970098944
2012-08-31                 52.80                105.44      0.123221418965237
2012-09-30                 53.29                106.22      0.250620742602983
2012-10-31                 53.96                107.23       0.44162577825319
2012-11-30                 54.27                107.52      0.612159825080578
2012-12-31                 54.24                108.07      0.224522166142312


In this example we have 13 months’ worth of price data for three different portfolios and we want to calculate the information ratio. Note how each information ratio column in the resultant table has a unique @Id.
SELECT CAST(EOM as date) as EOM
,AAA
,BBB
,CCC
,BMK
INTO #s
FROM (VALUES
      ('2011-12-31',74.58,49.34,54.97,100),
      ('2012-01-31',75.17,48.73,56.07,100.82),
      ('2012-02-29',74.61,49.78,54.66,101.81),
      ('2012-03-31',74.69,55.25,55.39,102.13),
      ('2012-04-30',75.6,47.78,57.4,101.82),
      ('2012-05-31',75.53,50.34,54.92,102.7),
      ('2012-06-30',75.41,46.81,55.12,103.78),
      ('2012-07-31',75.83,51.53,55.56,104.73),
      ('2012-08-31',74.58,52.14,54.24,105.44),
      ('2012-09-30',74.77,51.22,57.41,106.22),
      ('2012-10-31',74.33,49.68,55.76,107.23),
      ('2012-11-30',75.06,45.44,56.28,107.52),
      ('2012-12-31',75.25,51.96,53.19,108.07)
      )n(EOM,AAA,BBB,CCC,BMK)
 
SELECT EOM
,wct.RunningINFORATIO(AAA, BMK, 12, 'True', ROW_NUMBER() OVER (ORDER BY eom ASC), 1) as AAA
,wct.RunningINFORATIO(BBB, BMK, 12, 'True', ROW_NUMBER() OVER (ORDER BY eom ASC), 2) as BBB
,wct.RunningINFORATIO(CCC, BMK, 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      -2.53287857225603     -0.670222203243517      -1.21254057019029
2012-03-31      -2.42887935570662       1.70831447933928     -0.562774128032074
2012-04-30      -0.28751974009322     -0.301132993654973      0.742672111272349
2012-05-31     -0.799672772223789     0.0834332934095227     -0.472794520066031
2012-06-30      -1.31183123925642     -0.468770269560868     -0.559267461050082
2012-07-31      -1.40048556295106      0.122343103745679     -0.543401746270146
2012-08-31      -1.94093672830627      0.142559896617809      -0.93424097466334
2012-09-30      -2.01054132789827    0.00868188486539341     -0.147058345789798
2012-10-31      -2.34529088690404     -0.178729871636421     -0.520105890699677
2012-11-30      -1.86758149549442     -0.535185297725232     -0.431442427795981
2012-12-31      -1.85772821653992     0.0434775121159037     -0.862858177548675
 
 
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 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
      ('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','BMK',100),
      ('2012-01-31','BMK',100.82),
      ('2012-02-29','BMK',101.81),
      ('2012-03-31','BMK',102.13),
      ('2012-04-30','BMK',101.82),
      ('2012-05-31','BMK',102.7),
      ('2012-06-30','BMK',103.78),
      ('2012-07-31','BMK',104.73),
      ('2012-08-31','BMK',105.44),
      ('2012-09-30','BMK',106.22),
      ('2012-10-31','BMK',107.23),
      ('2012-11-30','BMK',107.52),
      ('2012-12-31','BMK',108.07)
      )n(eom,sym,pr)
 
SELECT s1.EOM
,s1.sym
,wct.RunningINFORATIO(s1.pr, s2.pr, 12, 'True', ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1) 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
---------- ---- ----------------------
2011-12-31 AAA                    NULL
2012-01-31 AAA                    NULL
2012-02-29 AAA       -2.53287857225603
2012-03-31 AAA       -2.42887935570662
2012-04-30 AAA       -0.28751974009322
2012-05-31 AAA      -0.799672772223789
2012-06-30 AAA       -1.31183123925642
2012-07-31 AAA       -1.40048556295106
2012-08-31 AAA       -1.94093672830627
2012-09-30 AAA       -2.01054132789827
2012-10-31 AAA       -2.34529088690404
2012-11-30 AAA       -1.86758149549442
2012-12-31 AAA       -1.85772821653992
2011-12-31 BBB                    NULL
2012-01-31 BBB                    NULL
2012-02-29 BBB      -0.670222203243517
2012-03-31 BBB        1.70831447933928
2012-04-30 BBB      -0.301132993654973
2012-05-31 BBB      0.0834332934095227
2012-06-30 BBB      -0.468770269560868
2012-07-31 BBB       0.122343103745679
2012-08-31 BBB       0.142559896617809
2012-09-30 BBB     0.00868188486539341
2012-10-31 BBB      -0.178729871636421
2012-11-30 BBB      -0.535185297725232
2012-12-31 BBB      0.0434775121159037
2011-12-31 CCC                    NULL
2012-01-31 CCC                    NULL
2012-02-29 CCC       -1.21254057019029
2012-03-31 CCC      -0.562774128032074
2012-04-30 CCC       0.742672111272349
2012-05-31 CCC      -0.472794520066031
2012-06-30 CCC      -0.559267461050082
2012-07-31 CCC      -0.543401746270146
2012-08-31 CCC       -0.93424097466334
2012-09-30 CCC      -0.147058345789798
2012-10-31 CCC      -0.520105890699677
2012-11-30 CCC      -0.431442427795981
2012-12-31 CCC      -0.862858177548675
 


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.RunningINFORATIO(s1.pr, s2.pr, 12, 'True', ROW_NUMBER() OVER (PARTITION BY s1.SYM ORDER BY s1.sym, s1.eom ASC), 1) 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
---------- ---------------------- ---------------------- ----------------------
2011-12-31                   NULL                   NULL                   NULL
2012-01-31                   NULL                   NULL                   NULL
2012-02-29      -2.53287857225603     -0.670222203243517      -1.21254057019029
2012-03-31      -2.42887935570662       1.70831447933928     -0.562774128032074
2012-04-30      -0.28751974009322     -0.301132993654973      0.742672111272349
2012-05-31     -0.799672772223789     0.0834332934095227     -0.472794520066031
2012-06-30      -1.31183123925642     -0.468770269560868     -0.559267461050082
2012-07-31      -1.40048556295106      0.122343103745679     -0.543401746270146
2012-08-31      -1.94093672830627      0.142559896617809      -0.93424097466334
2012-09-30      -2.01054132789827    0.00868188486539341     -0.147058345789798
2012-10-31      -2.34529088690404     -0.178729871636421     -0.520105890699677
2012-11-30      -1.86758149549442     -0.535185297725232     -0.431442427795981
2012-12-31      -1.85772821653992     0.0434775121159037     -0.862858177548675


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service