TREYNOR
Updated 2024-02-29 20:45:25.827000
Syntax
SELECT [westclintech].[wct].[TREYNOR] (
<@R, float,>
,<@RB, float,>
,<@RF, float,>
,<@Scale, float,>
,<@Geometric, bit,>)
Description
Use Treynor to calculate the Treynor ratio based upon return data. You have the option of computing the Treynor ratio using either simple returns or geometric returns. For simple returns, the Treynor ratio is calculated as the mean of the returns minus the risk-free rate divided by the beta of the returns against the benchmark returns.
TREYNOR = \frac{\bar{R}-Rf}{\beta_{R,Rb}}
For geometric returns, the Treynor ratio is calculated as the geometric mean of the return minus the risk-free rate divided by the beta of the returns against the benchmark returns. For the sake of consistency, the risk-free rate should be in the same units as the scaling factor.
TREYNOR=\frac{\left(\prod_{i=1}^n1+R_i\right)^{scale/n}-1}{\beta_{R,Rb}}
WhereßR,Rb = SLOPE(R,Rb)
Arguments
@R
the return value; the percentage return in floating point format (i.e. 10% = 0.10). @R 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.
@RF
the risk-free rate. @RF is an expression of type float or of a type that can be implicitly converted to float.
@RB
the benchmark return value. @RB is an expression of type float or of a type that can be implicitly converted to float.
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 price data or portfolio values, use the TRYENOR2 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.
Examples
In this example we have return data for IBM and we want to calculate the simple Treynor ratio using the S&P 500 returns as a benchmark.
SELECT wct.TREYNOR(r, rb, .001 / cast(252 as float), NULL, NULL) as TREYNOR
FROM
(
VALUES
('IBM', '2012-12-18', 0.0107, 0.0115),
('IBM', '2012-12-17', 0.0097, 0.0119),
('IBM', '2012-12-14', -0.0012, -0.0041),
('IBM', '2012-12-13', -0.005, -0.0063),
('IBM', '2012-12-12', -0.0064, 0.0004),
('IBM', '2012-12-11', 0.0082, 0.0065),
('IBM', '2012-12-10', 0.0035, 0.0003),
('IBM', '2012-12-07', 0.0119, 0.0029),
('IBM', '2012-12-06', 0.0056, 0.0033),
('IBM', '2012-12-05', -0.0037, 0.0016),
('IBM', '2012-12-04', -0.0006, -0.0017),
('IBM', '2012-12-03', -0.0031, -0.0047),
('IBM', '2012-11-30', -0.0076, 0.0002),
('IBM', '2012-11-29', -0.0023, 0.0043),
('IBM', '2012-11-28', 0.0039, 0.0079),
('IBM', '2012-11-27', -0.0086, -0.0052),
('IBM', '2012-11-26', -0.0032, -0.002),
('IBM', '2012-11-23', 0.0168, 0.013),
('IBM', '2012-11-21', 0.0058, 0.0023),
('IBM', '2012-11-20', -0.006, 0.0007),
('IBM', '2012-11-19', 0.0182, 0.0199),
('IBM', '2012-11-16', 0.0059, 0.0048),
('IBM', '2012-11-15', 0.0018, -0.0016),
('IBM', '2012-11-14', -0.0149, -0.0139),
('IBM', '2012-11-13', -0.0049, -0.004),
('IBM', '2012-11-12', -0.0021, 0.0001),
('IBM', '2012-11-09', -0.0024, 0.0017),
('IBM', '2012-11-08', -0.0055, -0.0122),
('IBM', '2012-11-07', -0.0158, -0.0237),
('IBM', '2012-11-06', 0.0048, 0.0079),
('IBM', '2012-11-05', 0.0036, 0.0022),
('IBM', '2012-11-02', -0.0188, -0.0094),
('IBM', '2012-11-01', 0.0135, 0.0109)
) n (ticker, tdate, r, rb);
This produces the following result.
{"columns":[{"field":"TREYNOR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"TREYNOR":"0.000388271591630151"}]}
In this example, we use weekly returns and calculate the Treynor ratio using geometric returns.
SELECT wct.TREYNOR(r, rb, .001 / cast(52 as float), 52, 'True') as TREYNOR
FROM
(
VALUES
('IBM', '2012-12-17', 0.0173, 0.0157),
('IBM', '2012-12-10', -0.001, -0.0032),
('IBM', '2012-12-03', 0.0099, 0.0013),
('IBM', '2012-11-26', -0.0177, 0.005),
('IBM', '2012-11-19', 0.035, 0.0362),
('IBM', '2012-11-12', -0.0142, -0.0145),
('IBM', '2012-11-05', -0.0153, -0.0243),
('IBM', '2012-10-31', 0.0008, 0.0016),
('IBM', '2012-10-22', -0.0005, -0.0148),
('IBM', '2012-10-15', -0.0695, 0.0032),
('IBM', '2012-10-08', -0.0133, -0.0221),
('IBM', '2012-10-01', 0.0151, 0.0141),
('IBM', '2012-09-24', 0.0072, -0.0133),
('IBM', '2012-09-17', -0.004, -0.0038),
('IBM', '2012-09-10', 0.0367, 0.0194),
('IBM', '2012-09-04', 0.0239, 0.0223),
('IBM', '2012-08-27', -0.0148, -0.0032),
('IBM', '2012-08-20', -0.0171, -0.005),
('IBM', '2012-08-13', 0.0097, 0.0087),
('IBM', '2012-08-06', 0.0082, 0.0107),
('IBM', '2012-07-30', 0.0108, 0.0036),
('IBM', '2012-07-23', 0.0204, 0.0171),
('IBM', '2012-07-16', 0.0347, 0.0043),
('IBM', '2012-07-09', -0.0282, 0.0016),
('IBM', '2012-07-02', -0.0213, -0.0055),
('IBM', '2012-06-25', 0.0097, 0.0203),
('IBM', '2012-06-18', -0.0271, -0.0058),
('IBM', '2012-06-11', 0.0203, 0.013),
('IBM', '2012-06-04', 0.032, 0.0373),
('IBM', '2012-05-29', -0.0268, -0.0302),
('IBM', '2012-05-21', -0.0081, 0.0174),
('IBM', '2012-05-14', -0.0263, -0.043),
('IBM', '2012-05-07', -0.0145, -0.0115),
('IBM', '2012-04-30', -0.0088, -0.0244),
('IBM', '2012-04-23', 0.0361, 0.018),
('IBM', '2012-04-16', -0.0158, 0.006),
('IBM', '2012-04-09', -0.013, -0.0199),
('IBM', '2012-04-02', -0.0152, -0.0074),
('IBM', '2012-03-26', 0.0154, 0.0081),
('IBM', '2012-03-19', -0.0026, -0.005),
('IBM', '2012-03-12', 0.0269, 0.0243),
('IBM', '2012-03-05', 0.0091, 0.0009),
('IBM', '2012-02-27', 0.0053, 0.0028),
('IBM', '2012-02-21', 0.0224, 0.0033),
('IBM', '2012-02-13', 0.0052, 0.0138),
('IBM', '2012-02-06', -0.0024, -0.0017),
('IBM', '2012-01-30', 0.0167, 0.0217),
('IBM', '2012-01-23', 0.0103, 0.0007),
('IBM', '2012-01-17', 0.0523, 0.0204),
('IBM', '2012-01-09', -0.0185, 0.0088)
) n (ticker, tdate, r, rb);
This produces the following result.
{"columns":[{"field":"TREYNOR","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"TREYNOR":"0.103411484610504"}]}
In this example, we look at monthly returns for several different symbols and group the results by symbol. The bench mark is the S&P 500 and is included in the same table as the other returns.
SELECT *
INTO #s
FROM
(
VALUES
('IBM', '2012-12-03', 0.0264),
('IBM', '2012-11-01', -0.0186),
('IBM', '2012-10-01', -0.0623),
('IBM', '2012-09-04', 0.0647),
('IBM', '2012-08-01', -0.0015),
('IBM', '2012-07-02', 0.0021),
('IBM', '2012-06-01', 0.0139),
('IBM', '2012-05-01', -0.0646),
('IBM', '2012-04-02', -0.0075),
('IBM', '2012-03-01', 0.0605),
('IBM', '2012-02-01', 0.0254),
('IBM', '2012-01-03', 0.0474),
('IBM', '2011-12-01', -0.0219),
('MSFT', '2012-12-03', 0.0259),
('MSFT', '2012-11-01', -0.0597),
('MSFT', '2012-10-01', -0.041),
('MSFT', '2012-09-04', -0.0343),
('MSFT', '2012-08-01', 0.0527),
('MSFT', '2012-07-02', -0.0365),
('MSFT', '2012-06-01', 0.048),
('MSFT', '2012-05-01', -0.0823),
('MSFT', '2012-04-02', -0.0076),
('MSFT', '2012-03-01', 0.0164),
('MSFT', '2012-02-01', 0.0818),
('MSFT', '2012-01-03', 0.1374),
('MSFT', '2011-12-01', 0.0149),
('GOOG', '2012-12-03', 0.0311),
('GOOG', '2012-11-01', 0.0266),
('GOOG', '2012-10-01', -0.0983),
('GOOG', '2012-09-04', 0.1013),
('GOOG', '2012-08-01', 0.0823),
('GOOG', '2012-07-02', 0.0912),
('GOOG', '2012-06-01', -0.0014),
('GOOG', '2012-05-01', -0.0397),
('GOOG', '2012-04-02', -0.0567),
('GOOG', '2012-03-01', 0.0372),
('GOOG', '2012-02-01', 0.0657),
('GOOG', '2012-01-03', -0.1019),
('GOOG', '2011-12-01', 0.0776),
('AAPL', '2012-12-03', -0.1008),
('AAPL', '2012-11-01', -0.0124),
('AAPL', '2012-10-01', -0.1076),
('AAPL', '2012-09-04', 0.0028),
('AAPL', '2012-08-01', 0.0939),
('AAPL', '2012-07-02', 0.0458),
('AAPL', '2012-06-01', 0.0109),
('AAPL', '2012-05-01', -0.0107),
('AAPL', '2012-04-02', -0.026),
('AAPL', '2012-03-01', 0.1053),
('AAPL', '2012-02-01', 0.1883),
('AAPL', '2012-01-03', 0.1271),
('AAPL', '2011-12-01', 0.0597),
('ORCL', '2012-12-03', 0.0653),
('ORCL', '2012-11-01', 0.0353),
('ORCL', '2012-10-01', -0.0099),
('ORCL', '2012-09-04', -0.006),
('ORCL', '2012-08-01', 0.048),
('ORCL', '2012-07-02', 0.0187),
('ORCL', '2012-06-01', 0.122),
('ORCL', '2012-05-01', -0.0996),
('ORCL', '2012-04-02', 0.0104),
('ORCL', '2012-03-01', -0.0035),
('ORCL', '2012-02-01', 0.0373),
('ORCL', '2012-01-03', 0.102),
('ORCL', '2011-12-01', -0.1818),
('SP500', '2012-12-03', 0.0139),
('SP500', '2012-11-01', 0.0028),
('SP500', '2012-10-01', -0.0198),
('SP500', '2012-09-04', 0.0242),
('SP500', '2012-08-01', 0.0198),
('SP500', '2012-07-02', 0.0126),
('SP500', '2012-06-01', 0.0396),
('SP500', '2012-05-01', -0.0627),
('SP500', '2012-04-02', -0.0075),
('SP500', '2012-03-01', 0.0313),
('SP500', '2012-02-01', 0.0406),
('SP500', '2012-01-03', 0.0436),
('SP500', '2011-12-01', 0.0085)
) n (ticker, tdate, r);
SELECT s1.ticker,
wct.TREYNOR(s1.r, s2.r, .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.
{"columns":[{"field":"ticker"},{"field":"TREYNOR"}],"rows":[{"ticker":"AAPL","TREYNOR":"0.209613130538218"},{"ticker":"GOOG","TREYNOR":"0.229680999398903"},{"ticker":"IBM","TREYNOR":"0.0429792768480309"},{"ticker":"MSFT","TREYNOR":"0.054955541570367"},{"ticker":"ORCL","TREYNOR":"0.0595638829579447"},{"ticker":"SP500","TREYNOR":"NULL"}]}
See Also
TREYNOR2 - Calculate the Treynor ratio based upon price or valuation data.
INFORATIO - Information ratio based upon return data
INFORATIO2 - Information ratio based upon price or valuation data
SHARPE - Sharpe ratio based upon return data
SHARPE2 - Sharpe ratio based upon price or valuation data