SQL Server SpecificRisk Function
Updated 2024-02-29 14:50:10.657000
Description
Use the aggregate function SpecificRisk to calculate Specific Risk, the standard deviation of the error term in the regression equation. Specific Risk is calculated as:
\rm{SpecificRisk=\sigma_{\left(Ra-Rb\ \ast\ \beta-\ \alpha\right)}\ \times\sqrt{Freq}}
\rm{\beta=\ BetaCovar(Ra-Rf,Rb-Rf)}
\rm{\alpha=\ INTERCEPT(Ra-Rf,Rb-Rf)}
Where
| column 1 | column 2 | column 3 |
|---|---|---|
| Ra | = | asset return |
| Rb | = | benchmark return |
| Rf | = | risk-free return |
| freq | = | periodicity of returns |
Syntax
SELECT [westclintech].[wct].[SpecificRisk](
<@Ra, float,>
,<@Rb, float,>
,<@Rf, float,>
,<@Freq, int,>)
Arguments
@Ra
the asset return for a period; 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.
@Rb
the benchmark return for a period; 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.
@Rf
the risk-free return for the period in floating point format (i.e. 10% = 0.10). @Rf is an expression of type float or of a type that can be implicitly converted to float.
@Freq
the period in which @Ra, @Rb, and @Rf are expressed. For example, a @Freq of 1 would indicate that the returns are annual; 4 would be quarterly, 12 would be monthly and 252 would be business-daily. @Freq must be of a type int or of a type that implicitly converts to int.
Return Type
float
Remarks
If @Ra or @Rb IS NULL it is not included in the calculation.
If @Rf IS NULL it is set to zero.
If there are no non-NULL rows in a GROUP then NULL is returned.
@Freq must be greater than zero.
If @Freq IS NULL then @Freq is set to 12.
Examples
In this example we have monthly returns for an asset, its benchmark, and risk-free returns for the last three years.
SELECT wct.SpecificRisk(Ra, Rb, Rf, 12) as SpecificRisk
FROM
(
VALUES
('2012-12-31', 0.001378, -0.003929, 0.00142),
('2013-01-31', 0.028677, -0.001701, 0.00075),
('2013-02-28', 0.005801, 0.003165, 0.001579),
('2013-03-31', 0.01442, -0.006487, 0.00017),
('2013-04-30', 0.00229, -0.004653, 0.001034),
('2013-05-31', 0.014905, 0.009577, 0.00071),
('2013-06-30', 0.008594, 0.00588, 0.000256),
('2013-07-31', 0.011531, 0.005089, 0.00101),
('2013-08-31', 0.008268, 0.005233, 0.001522),
('2013-09-30', 0.013993, -0.004338, 0.001119),
('2013-10-31', 0.009147, -0.006109, 0.001232),
('2013-11-30', -0.00316, -0.002222, 0.001374),
('2013-12-31', -0.00595, 0.005451, 0.001406),
('2014-01-31', 0.013398, -0.008099, 0.000624),
('2014-02-28', 0.002847, -0.000299, 0.00164),
('2014-03-31', -0.009544, -0.009809, 0.001427),
('2014-04-30', 0.002516, 0.008875, 0.001618),
('2014-05-31', 0.004626, -0.002681, 0.000937),
('2014-06-30', -0.002141, 0.000312, 0.0008),
('2014-07-31', 0.009247, 0.00936, 0.001733),
('2014-08-31', -0.01253, 0.005434, 0.000325),
('2014-09-30', 0.00441, 0.008157, 0.000535),
('2014-10-31', 0.01626, -0.006766, 0.001325),
('2014-11-30', 0.013207, 0.005742, 0.001152),
('2014-12-31', -0.008561, -0.005063, 0.001246),
('2015-01-31', 0.012357, 0.004357, 0.000372),
('2015-02-28', -0.002057, -0.00044, 0.001045),
('2015-03-31', 0.008217, -0.004866, 0.001419),
('2015-04-30', -0.013439, -0.007649, 0.001383),
('2015-05-31', 0.004391, -0.002073, 0.000783),
('2015-06-30', -0.008997, 0.00399, 0.0007),
('2015-07-31', -0.001878, -0.00613, 0.001188),
('2015-08-31', -0.014381, -0.00351, 0.000771),
('2015-09-30', -0.001885, 0.001172, 0.000742),
('2015-10-31', -0.001121, -0.000024, 0.001119),
('2015-11-30', 0.000816, 0.002596, 0.001116),
('2015-12-31', -0.003088, -0.009025, 0.001332)
) n (dt, Ra, Rb, Rf);
This produces the following result.
| SpecificRisk |
|---|
| 0.0324179638746894 |
In this example we have the monthly returns for 6 managers plus a benchmark stored in the #managers ta ble which is in 'spreadsheet' format. The minimum acceptable rate is 0.005.
SELECT *
INTO #managers
FROM ( VALUES ('2012-12-31', -0.002546, NULL, NULL, 0.003006, NULL, 0.005007, -0.001839, 0.00142),
('2013-01-31', 0.00977, NULL, NULL, 0.001, NULL, -0.007579, 0.008614, 0.00075),
('2013-02-28', 0.024726, NULL, NULL, 0.005703, NULL, 0.006496, -0.004624, 0.001579),
('2013-03-31', 0.000942, NULL, NULL, 0.007578, NULL, 0.008333, 0.005031, 0.00017),
('2013-04-30', 0.022139, NULL, NULL, 0.007118, NULL, -0.007312, -0.005431, 0.001034),
('2013-05-31', 0.019449, NULL, NULL, 0.005659, NULL, -0.003902, -0.001878, 0.00071),
('2013-06-30', -0.007964, NULL, NULL, 0.003757, NULL, -0.001899, -0.00706, 0.000256),
('2013-07-31', -0.008262, -0.002824, NULL, 0.003762, NULL, -0.007347, -0.001982, 0.00101),
('2013-08-31', 0.009617, 0.007319, NULL, 0.0022, NULL, -0.002116, 0.003867, 0.001522),
('2013-09-30', -0.004118, 0.004128, NULL, 0.001394, NULL, 0.008333, 0.001356, 0.001119),
('2013-10-31', 0.010754, -0.001578, NULL, 0.003483, NULL, -0.004724, -0.001342, 0.001232),
('2013-11-30', 0.002402, 0.020835, NULL, 0.001, NULL, 0.003954, -0.000306, 0.001374),
('2013-12-31', 0.004581, 0.015689, NULL, 0.002146, NULL, 0.008333, 0.001118, 0.001406),
('2014-01-31', -0.00055, -0.001027, -0.008245, 0.00732, NULL, 0.004025, -0.007227, 0.000624),
('2014-02-28', -0.001512, 0.001653, -0.009029, 0.009919, NULL, -0.008333, -0.003878, 0.00164),
('2014-03-31', 0.008784, 0.004364, -0.011608, 0.001, NULL, -0.008333, -0.004822, 0.001427),
('2014-04-30', 0.008412, -0.012369, -0.004692, 0.004536, NULL, -0.006303, 0.004306, 0.001618),
('2014-05-31', 0.003945, 0.010651, -0.016833, 0.001, NULL, -0.007974, 0.005221, 0.000937),
('2014-06-30', 0.012371, 0.01773, -0.010384, 0.010593, NULL, -0.004781, -0.000731, 0.0008),
('2014-07-31', 0.011915, 0.004308, -0.012965, 0.001, NULL, 0.007751, -0.009239, 0.001733),
('2014-08-31', -0.013738, 0.00039, 0.000009, 0.005139, 0.001, -0.003319, -0.003636, 0.000325),
('2014-09-30', -0.004081, 0.01968, -0.008214, 0.007976, 0.001, -0.003799, 0.005563, 0.000535),
('2014-10-31', 0.01608, 0.015291, -0.002969, 0.01303, 0.001, -0.004645, 0.001599, 0.001325),
('2014-11-30', 0.011241, 0.012312, 0.007088, 0.00384, 0.000852, 0.006783, -0.008487, 0.001152),
('2014-12-31', -0.004251, 0.008737, -0.013576, 0.001, 0.001, -0.0034, 0.001013, 0.001246),
('2015-01-31', -0.004039, 0.012938, -0.011891, 0.004576, 0.001, -0.004195, -0.002653, 0.000372),
('2015-02-28', 0.026326, 0.019695, -0.013178, 0.010737, 0.001, 0.006418, -0.00952, 0.001045),
('2015-03-31', -0.000628, 0.008029, 0.005917, 0.017461, 0.00048, -0.00501, 0.007879, 0.001419),
('2015-04-30', 0.005688, 0.01249, -0.018813, 0.001, -0.002104, 0.003657, 0.009886, 0.001383),
('2015-05-31', 0.010039, 0.008442, -0.01465, 0.001, 0.001, 0.001024, 0.001695, 0.000783),
('2015-06-30', -0.004267, 0.001391, -0.022504, 0.003104, 0.001, -0.003507, 0.009854, 0.0007),
('2015-07-31', -0.001672, 0.020558, -0.011859, 0.001, 0.001, NULL, 0.008361, 0.001188),
('2015-08-31', 0.015726, 0.017419, -0.004556, 0.001, -0.000462, NULL, -0.007253, 0.000771),
('2015-09-30', 0.001977, 0.007301, -0.00811, 0.013011, -0.001032, NULL, 0.009911, 0.000742),
('2015-10-31', 0.016201, 0.00877, -0.005536, 0.014527, -0.000833, NULL, 0.009897, 0.001119),
('2015-11-30', 0.019996, -0.000785, -0.002161, 0.001947, 0.001, NULL, -0.007307, 0.001116),
('2015-12-31', -0.003254, 0.009595, -0.013785, 0.001, 0.001, NULL, 0.007196, 0.001332)) n (dt, man1,
man2,
man3,
man4,
man5,
man6,
bmark, rf);
To calculate the Specific Risk for each manager we run the following SQL.
SELECT wct.SpecificRisk(man1, bmark, rf, 12) as man1,
wct.SpecificRisk(man2, bmark, rf, 12) as man2,
wct.SpecificRisk(man3, bmark, rf, 12) as man3,
wct.SpecificRisk(man4, bmark, rf, 12) as man4,
wct.SpecificRisk(man5, bmark, rf, 12) as man5,
wct.SpecificRisk(man6, bmark, rf, 12) as man6
FROM #managers;
This produces the following result.
| man1 | man2 | man3 | man4 | man5 | man6 |
|---|---|---|---|---|---|
| 0.0327736264619401 | 0.0276805185140117 | 0.0232802445759231 | 0.0151265193313263 | 0.0029721418383429 | 0.0194103610942203 |
Using the same data from the previous example in the #nmanagers table which is in 3rd normal form we perform the same calculation.
SELECT *
INTO #nmanagers
FROM
(
VALUES
('2012-12-31', 'man1', -0.002546),
('2012-12-31', 'man4', 0.003006),
('2012-12-31', 'man6', 0.005007),
('2012-12-31', 'bmark', -0.001839),
('2012-12-31', 'rf', 0.001420),
('2013-01-31', 'man1', 0.009770),
('2013-01-31', 'man4', 0.001000),
('2013-01-31', 'man6', -0.007579),
('2013-01-31', 'bmark', 0.008614),
('2013-01-31', 'rf', 0.000750),
('2013-02-28', 'man1', 0.024726),
('2013-02-28', 'man4', 0.005703),
('2013-02-28', 'man6', 0.006496),
('2013-02-28', 'bmark', -0.004624),
('2013-02-28', 'rf', 0.001579),
('2013-03-31', 'man1', 0.000942),
('2013-03-31', 'man4', 0.007578),
('2013-03-31', 'man6', 0.008333),
('2013-03-31', 'bmark', 0.005031),
('2013-03-31', 'rf', 0.000170),
('2013-04-30', 'man1', 0.022139),
('2013-04-30', 'man4', 0.007118),
('2013-04-30', 'man6', -0.007312),
('2013-04-30', 'bmark', -0.005431),
('2013-04-30', 'rf', 0.001034),
('2013-05-31', 'man1', 0.019449),
('2013-05-31', 'man4', 0.005659),
('2013-05-31', 'man6', -0.003902),
('2013-05-31', 'bmark', -0.001878),
('2013-05-31', 'rf', 0.000710),
('2013-06-30', 'man1', -0.007964),
('2013-06-30', 'man4', 0.003757),
('2013-06-30', 'man6', -0.001899),
('2013-06-30', 'bmark', -0.007060),
('2013-06-30', 'rf', 0.000256),
('2013-07-31', 'man1', -0.008262),
('2013-07-31', 'man2', -0.002824),
('2013-07-31', 'man4', 0.003762),
('2013-07-31', 'man6', -0.007347),
('2013-07-31', 'bmark', -0.001982),
('2013-07-31', 'rf', 0.001010),
('2013-08-31', 'man1', 0.009617),
('2013-08-31', 'man2', 0.007319),
('2013-08-31', 'man4', 0.002200),
('2013-08-31', 'man6', -0.002116),
('2013-08-31', 'bmark', 0.003867),
('2013-08-31', 'rf', 0.001522),
('2013-09-30', 'man1', -0.004118),
('2013-09-30', 'man2', 0.004128),
('2013-09-30', 'man4', 0.001394),
('2013-09-30', 'man6', 0.008333),
('2013-09-30', 'bmark', 0.001356),
('2013-09-30', 'rf', 0.001119),
('2013-10-31', 'man1', 0.010754),
('2013-10-31', 'man2', -0.001578),
('2013-10-31', 'man4', 0.003483),
('2013-10-31', 'man6', -0.004724),
('2013-10-31', 'bmark', -0.001342),
('2013-10-31', 'rf', 0.001232),
('2013-11-30', 'man1', 0.002402),
('2013-11-30', 'man2', 0.020835),
('2013-11-30', 'man4', 0.001000),
('2013-11-30', 'man6', 0.003954),
('2013-11-30', 'bmark', -0.000306),
('2013-11-30', 'rf', 0.001374),
('2013-12-31', 'man1', 0.004581),
('2013-12-31', 'man2', 0.015689),
('2013-12-31', 'man4', 0.002146),
('2013-12-31', 'man6', 0.008333),
('2013-12-31', 'bmark', 0.001118),
('2013-12-31', 'rf', 0.001406),
('2014-01-31', 'man1', -0.000550),
('2014-01-31', 'man2', -0.001027),
('2014-01-31', 'man3', -0.008245),
('2014-01-31', 'man4', 0.007320),
('2014-01-31', 'man6', 0.004025),
('2014-01-31', 'bmark', -0.007227),
('2014-01-31', 'rf', 0.000624),
('2014-02-28', 'man1', -0.001512),
('2014-02-28', 'man2', 0.001653),
('2014-02-28', 'man3', -0.009029),
('2014-02-28', 'man4', 0.009919),
('2014-02-28', 'man6', -0.008333),
('2014-02-28', 'bmark', -0.003878),
('2014-02-28', 'rf', 0.001640),
('2014-03-31', 'man1', 0.008784),
('2014-03-31', 'man2', 0.004364),
('2014-03-31', 'man3', -0.011608),
('2014-03-31', 'man4', 0.001000),
('2014-03-31', 'man6', -0.008333),
('2014-03-31', 'bmark', -0.004822),
('2014-03-31', 'rf', 0.001427),
('2014-04-30', 'man1', 0.008412),
('2014-04-30', 'man2', -0.012369),
('2014-04-30', 'man3', -0.004692),
('2014-04-30', 'man4', 0.004536),
('2014-04-30', 'man6', -0.006303),
('2014-04-30', 'bmark', 0.004306),
('2014-04-30', 'rf', 0.001618),
('2014-05-31', 'man1', 0.003945),
('2014-05-31', 'man2', 0.010651),
('2014-05-31', 'man3', -0.016833),
('2014-05-31', 'man4', 0.001000),
('2014-05-31', 'man6', -0.007974),
('2014-05-31', 'bmark', 0.005221),
('2014-05-31', 'rf', 0.000937),
('2014-06-30', 'man1', 0.012371),
('2014-06-30', 'man2', 0.017730),
('2014-06-30', 'man3', -0.010384),
('2014-06-30', 'man4', 0.010593),
('2014-06-30', 'man6', -0.004781),
('2014-06-30', 'bmark', -0.000731),
('2014-06-30', 'rf', 0.000800),
('2014-07-31', 'man1', 0.011915),
('2014-07-31', 'man2', 0.004308),
('2014-07-31', 'man3', -0.012965),
('2014-07-31', 'man4', 0.001000),
('2014-07-31', 'man6', 0.007751),
('2014-07-31', 'bmark', -0.009239),
('2014-07-31', 'rf', 0.001733),
('2014-08-31', 'man1', -0.013738),
('2014-08-31', 'man2', 0.000390),
('2014-08-31', 'man3', 0.000009),
('2014-08-31', 'man4', 0.005139),
('2014-08-31', 'man5', 0.001000),
('2014-08-31', 'man6', -0.003319),
('2014-08-31', 'bmark', -0.003636),
('2014-08-31', 'rf', 0.000325),
('2014-09-30', 'man1', -0.004081),
('2014-09-30', 'man2', 0.019680),
('2014-09-30', 'man3', -0.008214),
('2014-09-30', 'man4', 0.007976),
('2014-09-30', 'man5', 0.001000),
('2014-09-30', 'man6', -0.003799),
('2014-09-30', 'bmark', 0.005563),
('2014-09-30', 'rf', 0.000535),
('2014-10-31', 'man1', 0.016080),
('2014-10-31', 'man2', 0.015291),
('2014-10-31', 'man3', -0.002969),
('2014-10-31', 'man4', 0.013030),
('2014-10-31', 'man5', 0.001000),
('2014-10-31', 'man6', -0.004645),
('2014-10-31', 'bmark', 0.001599),
('2014-10-31', 'rf', 0.001325),
('2014-11-30', 'man1', 0.011241),
('2014-11-30', 'man2', 0.012312),
('2014-11-30', 'man3', 0.007088),
('2014-11-30', 'man4', 0.003840),
('2014-11-30', 'man5', 0.000852),
('2014-11-30', 'man6', 0.006783),
('2014-11-30', 'bmark', -0.008487),
('2014-11-30', 'rf', 0.001152),
('2014-12-31', 'man1', -0.004251),
('2014-12-31', 'man2', 0.008737),
('2014-12-31', 'man3', -0.013576),
('2014-12-31', 'man4', 0.001000),
('2014-12-31', 'man5', 0.001000),
('2014-12-31', 'man6', -0.003400),
('2014-12-31', 'bmark', 0.001013),
('2014-12-31', 'rf', 0.001246),
('2015-01-31', 'man1', -0.004039),
('2015-01-31', 'man2', 0.012938),
('2015-01-31', 'man3', -0.011891),
('2015-01-31', 'man4', 0.004576),
('2015-01-31', 'man5', 0.001000),
('2015-01-31', 'man6', -0.004195),
('2015-01-31', 'bmark', -0.002653),
('2015-01-31', 'rf', 0.000372),
('2015-02-28', 'man1', 0.026326),
('2015-02-28', 'man2', 0.019695),
('2015-02-28', 'man3', -0.013178),
('2015-02-28', 'man4', 0.010737),
('2015-02-28', 'man5', 0.001000),
('2015-02-28', 'man6', 0.006418),
('2015-02-28', 'bmark', -0.009520),
('2015-02-28', 'rf', 0.001045),
('2015-03-31', 'man1', -0.000628),
('2015-03-31', 'man2', 0.008029),
('2015-03-31', 'man3', 0.005917),
('2015-03-31', 'man4', 0.017461),
('2015-03-31', 'man5', 0.000480),
('2015-03-31', 'man6', -0.005010),
('2015-03-31', 'bmark', 0.007879),
('2015-03-31', 'rf', 0.001419),
('2015-04-30', 'man1', 0.005688),
('2015-04-30', 'man2', 0.012490),
('2015-04-30', 'man3', -0.018813),
('2015-04-30', 'man4', 0.001000),
('2015-04-30', 'man5', -0.002104),
('2015-04-30', 'man6', 0.003657),
('2015-04-30', 'bmark', 0.009886),
('2015-04-30', 'rf', 0.001383),
('2015-05-31', 'man1', 0.010039),
('2015-05-31', 'man2', 0.008442),
('2015-05-31', 'man3', -0.014650),
('2015-05-31', 'man4', 0.001000),
('2015-05-31', 'man5', 0.001000),
('2015-05-31', 'man6', 0.001024),
('2015-05-31', 'bmark', 0.001695),
('2015-05-31', 'rf', 0.000783),
('2015-06-30', 'man1', -0.004267),
('2015-06-30', 'man2', 0.001391),
('2015-06-30', 'man3', -0.022504),
('2015-06-30', 'man4', 0.003104),
('2015-06-30', 'man5', 0.001000),
('2015-06-30', 'man6', -0.003507),
('2015-06-30', 'bmark', 0.009854),
('2015-06-30', 'rf', 0.000700),
('2015-07-31', 'man1', -0.001672),
('2015-07-31', 'man2', 0.020558),
('2015-07-31', 'man3', -0.011859),
('2015-07-31', 'man4', 0.001000),
('2015-07-31', 'man5', 0.001000),
('2015-07-31', 'bmark', 0.008361),
('2015-07-31', 'rf', 0.001188),
('2015-08-31', 'man1', 0.015726),
('2015-08-31', 'man2', 0.017419),
('2015-08-31', 'man3', -0.004556),
('2015-08-31', 'man4', 0.001000),
('2015-08-31', 'man5', -0.000462),
('2015-08-31', 'bmark', -0.007253),
('2015-08-31', 'rf', 0.000771),
('2015-09-30', 'man1', 0.001977),
('2015-09-30', 'man2', 0.007301),
('2015-09-30', 'man3', -0.008110),
('2015-09-30', 'man4', 0.013011),
('2015-09-30', 'man5', -0.001032),
('2015-09-30', 'bmark', 0.009911),
('2015-09-30', 'rf', 0.000742),
('2015-10-31', 'man1', 0.016201),
('2015-10-31', 'man2', 0.008770),
('2015-10-31', 'man3', -0.005536),
('2015-10-31', 'man4', 0.014527),
('2015-10-31', 'man5', -0.000833),
('2015-10-31', 'bmark', 0.009897),
('2015-10-31', 'rf', 0.001119),
('2015-11-30', 'man1', 0.019996),
('2015-11-30', 'man2', -0.000785),
('2015-11-30', 'man3', -0.002161),
('2015-11-30', 'man4', 0.001947),
('2015-11-30', 'man5', 0.001000),
('2015-11-30', 'bmark', -0.007307),
('2015-11-30', 'rf', 0.001116),
('2015-12-31', 'man1', -0.003254),
('2015-12-31', 'man2', 0.009595),
('2015-12-31', 'man3', -0.013785),
('2015-12-31', 'man4', 0.001000),
('2015-12-31', 'man5', 0.001000),
('2015-12-31', 'bmark', 0.007196),
('2015-12-31', 'rf', 0.001332)
) n (dt, man, R);
SELECT m.man as manager,
wct.SpecificRisk(m.r, b.r, rf.r, 12) as SpecificRisk
FROM #nmanagers m
INNER JOIN #nmanagers b
ON m.dt = b.dt
INNER JOIN #nmanagers rf
ON m.dt = rf.dt
WHERE m.man <> 'bmark'
AND m.man <> 'rf'
AND b.man = 'bmark'
AND rf.man = 'rf'
GROUP BY m.man;
This produces the following result.
| manager | SpecificRisk |
|---|---|
| man1 | 0.0327736264619401 |
| man2 | 0.0276805185140117 |
| man3 | 0.0232802445759231 |
| man4 | 0.0151265193313263 |
| man5 | 0.0029721418383429 |
| man6 | 0.0194103610942203 |
See Also
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
SORTINO2 - Sortino ratio based upon price or valuation data
TREYNOR - TREYNOR ratio based upon return data
TREYNOR2 - Calculate the Treynor ratio based upon price or valuation data.