TREYNOR2
Updated: 21 December 2012
Use TREYNOR2 to calculate the Treynor ratio based upon price or valuation data. You have the option of computing the Treynor ratio using either simple returns or geometric returns. For details on the formulae used to calculate the Treynor ratio, see the TREYNOR documentation.Syntax
Arguments
@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.
@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.
@BValue
the benchmark rate. @BValue 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.
@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.
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 Treynor ratio using return data, use the TREYNOR 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.
· @Rf 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 Treynor ratio using the S&P 500 prices.
SELECT wct.TREYNOR2(tdate,pr,prb,.001/cast(252 as float),NULL,NULL) as TREYNOR
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.
TREYNOR
----------------------
0.000381122001119534
In this example, we use weekly prices and calculate the Treynor ratio using geometric returns.
SELECT wct.TREYNOR2(tdate,pr,prb,.001/cast(52 as float),52,'True') as TREYNOR
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.
TREYNOR
----------------------
0.103301285778773
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.TREYNOR2(s1.tdate,s1.pr,s2.pr,.001/cast(12 as float),12,'True') as TREYNOR
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.
ticker TREYNOR
------ ----------------------
AAPL 0.209417579904324
GOOG 0.229392124874043
IBM 0.042873155029482
MSFT 0.054918431708661
ORCL 0.0596202415935896
SP500 NULL
See Also