INFORATIO2

Updated 2023-10-09 20:03:21.213000

Syntax

SELECT [[westclintech].[wct].[INFORATIO2] (
  <@PDate, datetime,>
 ,<@PValue, float,>
 ,<@BValue, float,>
 ,<@Scale, float,>
 ,<@Geometric, bit,>)

Description

Use INFORATIO2 to calculate the Information ratio based upon price or valuation data. You have the option of computing the Information ratio using either simple returns or geometric returns. For details on the formulae used to calculate the Information ratio, see the INFORATIO documentation.

Arguments

@BValue

the benchmark rate. @BValue is an expression of type float or of a type that can be implicitly converted to float.

@PValue

the price or value for the @PDate. @PDate is an expression of type float or of a type that can be implicitly converted to float.

@Geometric

identifies whether or not to use geometric returns in the calculation. @Geometric is an expression of type bit or of a type that can be implicitly converted to bit.

@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.

@PDate

the date associated with the price or valuation. @PDate is an expression of type datetime or of a type that can be implicitly converted to datetime.

Return Type

float

Remarks

If @Geometric IS NULL then @Geometric is set equal to 'False'.

If @Scale IS NULL them @Scale is set to 1.

For daily returns set @Scale = 252.

For weekly returns set @Scale = 52.

For monthly returns set @Scale = 12.

For quarterly returns set @Scale = 4.

To calculate the Information ratio using return data, use the INFORATIO aggregate function.

@Geometric must be the same for all rows in the GROUP BY

@Scale must the same for all rows in the GROUP BY.

If there are multiple rows for the same date, the @PValue and @Bvalue are accumulated.

The return values are automatically calculated by putting the @PValue and @Bvalue in @PDate order.

Examples

In this example we have price data for IBM and we want to calculate the simple Information ratio using the S&P 500 prices.

SELECT wct.INFORATIO2(tdate, pr, prb, 252, 'False') as INFORATIO

FROM

(

    VALUES

        ('IBM', '2012-12-18', 195.69, 1446.79),

        ('IBM', '2012-12-17', 193.62, 1430.36),

        ('IBM', '2012-12-14', 191.76, 1413.58),

        ('IBM', '2012-12-13', 191.99, 1419.45),

        ('IBM', '2012-12-12', 192.95, 1428.48),

        ('IBM', '2012-12-11', 194.2, 1427.84),

        ('IBM', '2012-12-10', 192.62, 1418.55),

        ('IBM', '2012-12-07', 191.95, 1418.07),

        ('IBM', '2012-12-06', 189.7, 1413.94),

        ('IBM', '2012-12-05', 188.65, 1409.28),

        ('IBM', '2012-12-04', 189.36, 1407.05),

        ('IBM', '2012-12-03', 189.48, 1409.46),

        ('IBM', '2012-11-30', 190.07, 1416.18),

        ('IBM', '2012-11-29', 191.53, 1415.95),

        ('IBM', '2012-11-28', 191.98, 1409.93),

        ('IBM', '2012-11-27', 191.23, 1398.94),

        ('IBM', '2012-11-26', 192.88, 1406.29),

        ('IBM', '2012-11-23', 193.49, 1409.15),

        ('IBM', '2012-11-21', 190.29, 1391.03),

        ('IBM', '2012-11-20', 189.2, 1387.81),

        ('IBM', '2012-11-19', 190.35, 1386.89),

        ('IBM', '2012-11-16', 186.94, 1359.88),

        ('IBM', '2012-11-15', 185.85, 1353.33),

        ('IBM', '2012-11-14', 185.51, 1355.49),

        ('IBM', '2012-11-13', 188.32, 1374.53),

        ('IBM', '2012-11-12', 189.25, 1380),

        ('IBM', '2012-11-09', 189.64, 1379.85),

        ('IBM', '2012-11-08', 190.1, 1377.51),

        ('IBM', '2012-11-07', 191.16, 1394.53),

        ('IBM', '2012-11-06', 194.22, 1428.39),

        ('IBM', '2012-11-05', 193.29, 1417.26),

        ('IBM', '2012-11-02', 192.59, 1414.2),

        ('IBM', '2012-11-01', 196.29, 1427.59),

        ('IBM', '2012-10-31', 193.68, 1412.16)

) n (ticker, tdate, pr, prb);

This produces the following result.

{"columns":[{"field":"INFORATIO","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INFORATIO":"-1.47787598470777"}]}

In this example, we use weekly prices and calculate the Information ratio using geometric returns.

SELECT wct.INFORATIO2(tdate, pr, prb, 52, 'True') as INFORATIO

FROM

(

    VALUES

        ('IBM', '2012-12-17', 195.08, 1435.81),

        ('IBM', '2012-12-10', 191.76, 1413.58),

        ('IBM', '2012-12-03', 191.95, 1418.07),

        ('IBM', '2012-11-26', 190.07, 1416.18),

        ('IBM', '2012-11-19', 193.49, 1409.15),

        ('IBM', '2012-11-12', 186.94, 1359.88),

        ('IBM', '2012-11-05', 189.64, 1379.85),

        ('IBM', '2012-10-31', 192.59, 1414.2),

        ('IBM', '2012-10-22', 192.43, 1411.94),

        ('IBM', '2012-10-15', 192.52, 1433.19),

        ('IBM', '2012-10-08', 206.89, 1428.59),

        ('IBM', '2012-10-01', 209.67, 1460.93),

        ('IBM', '2012-09-24', 206.55, 1440.67),

        ('IBM', '2012-09-17', 205.08, 1460.15),

        ('IBM', '2012-09-10', 205.91, 1465.77),

        ('IBM', '2012-09-04', 198.63, 1437.92),

        ('IBM', '2012-08-27', 194, 1406.58),

        ('IBM', '2012-08-20', 196.91, 1411.13),

        ('IBM', '2012-08-13', 200.34, 1418.16),

        ('IBM', '2012-08-06', 198.42, 1405.87),

        ('IBM', '2012-07-30', 196.81, 1390.99),

        ('IBM', '2012-07-23', 194.7, 1385.97),

        ('IBM', '2012-07-16', 190.8, 1362.66),

        ('IBM', '2012-07-09', 184.41, 1356.78),

        ('IBM', '2012-07-02', 189.77, 1354.68),

        ('IBM', '2012-06-25', 193.9, 1362.16),

        ('IBM', '2012-06-18', 192.04, 1335.02),

        ('IBM', '2012-06-11', 197.39, 1342.84),

        ('IBM', '2012-06-04', 193.46, 1325.66),

        ('IBM', '2012-05-29', 187.46, 1278.04),

        ('IBM', '2012-05-21', 192.63, 1317.82),

        ('IBM', '2012-05-14', 194.2, 1295.22),

        ('IBM', '2012-05-07', 199.44, 1353.39),

        ('IBM', '2012-04-30', 202.38, 1369.1),

        ('IBM', '2012-04-23', 204.18, 1403.36),

        ('IBM', '2012-04-16', 197.06, 1378.53),

        ('IBM', '2012-04-09', 200.22, 1370.26),

        ('IBM', '2012-04-02', 202.86, 1398.08),

        ('IBM', '2012-03-26', 205.99, 1408.47),

        ('IBM', '2012-03-19', 202.86, 1397.11),

        ('IBM', '2012-03-12', 203.39, 1404.17),

        ('IBM', '2012-03-05', 198.07, 1370.87),

        ('IBM', '2012-02-27', 196.28, 1369.63),

        ('IBM', '2012-02-21', 195.24, 1365.74),

        ('IBM', '2012-02-13', 190.96, 1361.23),

        ('IBM', '2012-02-06', 189.97, 1342.64),

        ('IBM', '2012-01-30', 190.43, 1344.9),

        ('IBM', '2012-01-23', 187.31, 1316.33),

        ('IBM', '2012-01-17', 185.4, 1315.38),

        ('IBM', '2012-01-09', 176.19, 1289.09),

        ('IBM', '2012-01-03', 179.52, 1277.81)

) n (ticker, tdate, pr, prb);

This produces the following result.

{"columns":[{"field":"INFORATIO","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"INFORATIO":"-0.310396296260186"}]}

In this example, we look at prices for several different symbols and group the results by symbol.

SELECT *

INTO #s

FROM

(

    VALUES

        ('IBM', '2012-12-03', 195.08),

        ('IBM', '2012-11-01', 190.07),

        ('IBM', '2012-10-01', 193.68),

        ('IBM', '2012-09-04', 206.55),

        ('IBM', '2012-08-01', 194),

        ('IBM', '2012-07-02', 194.3),

        ('IBM', '2012-06-01', 193.9),

        ('IBM', '2012-05-01', 191.24),

        ('IBM', '2012-04-02', 204.44),

        ('IBM', '2012-03-01', 205.99),

        ('IBM', '2012-02-01', 194.23),

        ('IBM', '2012-01-03', 189.41),

        ('IBM', '2011-12-01', 180.84),

        ('IBM', '2011-11-02', 184.89),

        ('MSFT', '2012-12-03', 27.31),

        ('MSFT', '2012-11-01', 26.62),

        ('MSFT', '2012-10-01', 28.31),

        ('MSFT', '2012-09-04', 29.52),

        ('MSFT', '2012-08-01', 30.57),

        ('MSFT', '2012-07-02', 29.04),

        ('MSFT', '2012-06-01', 30.14),

        ('MSFT', '2012-05-01', 28.76),

        ('MSFT', '2012-04-02', 31.34),

        ('MSFT', '2012-03-01', 31.58),

        ('MSFT', '2012-02-01', 31.07),

        ('MSFT', '2012-01-03', 28.72),

        ('MSFT', '2011-12-01', 25.25),

        ('MSFT', '2011-11-02', 24.88),

        ('GOOG', '2012-12-03', 720.11),

        ('GOOG', '2012-11-01', 698.37),

        ('GOOG', '2012-10-01', 680.3),

        ('GOOG', '2012-09-04', 754.5),

        ('GOOG', '2012-08-01', 685.09),

        ('GOOG', '2012-07-02', 632.97),

        ('GOOG', '2012-06-01', 580.07),

        ('GOOG', '2012-05-01', 580.86),

        ('GOOG', '2012-04-02', 604.85),

        ('GOOG', '2012-03-01', 641.24),

        ('GOOG', '2012-02-01', 618.25),

        ('GOOG', '2012-01-03', 580.11),

        ('GOOG', '2011-12-01', 645.9),

        ('GOOG', '2011-11-02', 599.39),

        ('AAPL', '2012-12-03', 526.31),

        ('AAPL', '2012-11-01', 585.28),

        ('AAPL', '2012-10-01', 592.61),

        ('AAPL', '2012-09-04', 664.07),

        ('AAPL', '2012-08-01', 662.22),

        ('AAPL', '2012-07-02', 605.38),

        ('AAPL', '2012-06-01', 578.86),

        ('AAPL', '2012-05-01', 572.64),

        ('AAPL', '2012-04-02', 578.84),

        ('AAPL', '2012-03-01', 594.27),

        ('AAPL', '2012-02-01', 537.67),

        ('AAPL', '2012-01-03', 452.46),

        ('AAPL', '2011-12-01', 401.44),

        ('AAPL', '2011-11-02', 378.84),

        ('ORCL', '2012-12-03', 34.09),

        ('ORCL', '2012-11-01', 32),

        ('ORCL', '2012-10-01', 30.91),

        ('ORCL', '2012-09-04', 31.22),

        ('ORCL', '2012-08-01', 31.41),

        ('ORCL', '2012-07-02', 29.97),

        ('ORCL', '2012-06-01', 29.42),

        ('ORCL', '2012-05-01', 26.22),

        ('ORCL', '2012-04-02', 29.12),

        ('ORCL', '2012-03-01', 28.82),

        ('ORCL', '2012-02-01', 28.92),

        ('ORCL', '2012-01-03', 27.88),

        ('ORCL', '2011-12-01', 25.3),

        ('ORCL', '2011-11-02', 30.92),

        ('SP500', '2012-12-03', 1435.81),

        ('SP500', '2012-11-01', 1416.18),

        ('SP500', '2012-10-01', 1412.16),

        ('SP500', '2012-09-04', 1440.67),

        ('SP500', '2012-08-01', 1406.58),

        ('SP500', '2012-07-02', 1379.32),

        ('SP500', '2012-06-01', 1362.16),

        ('SP500', '2012-05-01', 1310.33),

        ('SP500', '2012-04-02', 1397.91),

        ('SP500', '2012-03-01', 1408.47),

        ('SP500', '2012-02-01', 1365.68),

        ('SP500', '2012-01-03', 1312.41),

        ('SP500', '2011-12-01', 1257.6),

        ('SP500', '2011-11-02', 1246.96)

) n (ticker, tdate, pr);

SELECT s1.ticker,

       wct.INFORATIO2(s1.tdate, s1.pr, s2.pr, 12, 'True') as INFORATIO

FROM #s s1

    LEFT JOIN #s s2

        ON s2.tdate = s1.tdate

           AND s2.ticker = 'SP500'

           AND s2.ticker != s1.ticker

GROUP BY s1.ticker;

This produces the following result.

{"columns":[{"field":"ticker"},{"field":"INFORATIO"}],"rows":[{"ticker":"AAPL","INFORATIO":"0.846519681907702"},{"ticker":"GOOG","INFORATIO":"0.196110154561227"},{"ticker":"IBM","INFORATIO":"-1.07169324842413"},{"ticker":"MSFT","INFORATIO":"-0.327361553338124"},{"ticker":"ORCL","INFORATIO":"-0.191027096092106"},{"ticker":"SP500","INFORATIO":"NULL"}]}

INFORATIO2

Syntax

Description

Arguments

@BValue

@PValue

@Geometric

@Scale

@PDate

Return Type

Remarks

Examples

See Also