SQL Server DownsideDeviation Function
Updated 2024-02-22 21:10:10.243000
Description
Use the aggregate function DownsideDeviation to calculate the downside deviation of asset returns. The formula for DownsideDeviation is:
\mathrm{DownsideDeviation=\sqrt{\frac{\Sigma{max}\left(0,MAR-R\right)^2}{n}}
Where
| column 1 | column 2 | column 3 |
|---|---|---|
| R | = | asset return |
| MAR | = | minimum acceptable return |
| n | = | either the number of rows in the GROUP or the number of rows where R < MAR |
Syntax
SELECT [westclintech].[wct].[DownsideDeviation](
<@R, float,>
,<@MAR, float,>
,<@Full, bit,>)
Arguments
@R
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.
@MAR
the minimum acceptable return in floating point format (i.e. 10% = 0.10). @MAR is an expression of type float or of a type that can be implicitly converted to float.
@Full
a bit value which determines the treatment of n in the DownsideDeviation equation. When @Full is TRUE then n is the number of non-null rows in the GROUP; when @Full is FALSE then n is the number of rows where @R < @MAR.
Return Type
float
Remarks
If @R IS NULL it is not included in the calculation.
If @MAR IS NULL it is not included in the calculation.
If there are no non-NULL rows in a GROUP then NULL is returned.
@MAR must be the same for all values in a GROUP.
Examples
In this example we have returns for an asset and its benchmark for the last three years. The minimum acceptable return is 0.005.
SELECT wct.DownsideDeviation(Ra, 0.005, 'True') as [DownsideDeviation True],
wct.DownsideDeviation(Ra, 0.005, 'False') as [DownsideDeviation False]
FROM
(
VALUES
('2012-12-31', 0.001378, -0.003929),
('2013-01-31', 0.028677, -0.001701),
('2013-02-28', 0.005801, 0.003165),
('2013-03-31', 0.01442, -0.006487),
('2013-04-30', 0.00229, -0.004653),
('2013-05-31', 0.014905, 0.009577),
('2013-06-30', 0.008594, 0.00588),
('2013-07-31', 0.011531, 0.005089),
('2013-08-31', 0.008268, 0.005233),
('2013-09-30', 0.013993, -0.004338),
('2013-10-31', 0.009147, -0.006109),
('2013-11-30', -0.00316, -0.002222),
('2013-12-31', -0.00595, 0.005451),
('2014-01-31', 0.013398, -0.008099),
('2014-02-28', 0.002847, -0.000299),
('2014-03-31', -0.009544, -0.009809),
('2014-04-30', 0.002516, 0.008875),
('2014-05-31', 0.004626, -0.002681),
('2014-06-30', -0.002141, 0.000312),
('2014-07-31', 0.009247, 0.00936),
('2014-08-31', -0.01253, 0.005434),
('2014-09-30', 0.00441, 0.008157),
('2014-10-31', 0.01626, -0.006766),
('2014-11-30', 0.013207, 0.005742),
('2014-12-31', -0.008561, -0.005063),
('2015-01-31', 0.012357, 0.004357),
('2015-02-28', -0.002057, -0.00044),
('2015-03-31', 0.008217, -0.004866),
('2015-04-30', -0.013439, -0.007649),
('2015-05-31', 0.004391, -0.002073),
('2015-06-30', -0.008997, 0.00399),
('2015-07-31', -0.001878, -0.00613),
('2015-08-31', -0.014381, -0.00351),
('2015-09-30', -0.001885, 0.001172),
('2015-10-31', -0.001121, -0.000024),
('2015-11-30', 0.000816, 0.002596),
('2015-12-31', -0.003088, -0.009025)
) n (dt, Ra, Rb);
This produces the following result.
| DownsideDeviation True | DownsideDeviation False |
|---|---|
| 0.0076206946799754 | 0.00988289278133041 |
In this example we have 6 managers plus a benchmark stored in the #managers table 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),
('2013-01-31', 0.00977, NULL, NULL, 0.001, NULL, -0.007579, 0.008614),
('2013-02-28', 0.024726, NULL, NULL, 0.005703, NULL, 0.006496, -0.004624),
('2013-03-31', 0.000942, NULL, NULL, 0.007578, NULL, 0.008333, 0.005031),
('2013-04-30', 0.022139, NULL, NULL, 0.007118, NULL, -0.007312, -0.005431),
('2013-05-31', 0.019449, NULL, NULL, 0.005659, NULL, -0.003902, -0.001878),
('2013-06-30', -0.007964, NULL, NULL, 0.003757, NULL, -0.001899, -0.00706),
('2013-07-31', -0.008262, -0.002824, NULL, 0.003762, NULL, -0.007347, -0.001982),
('2013-08-31', 0.009617, 0.007319, NULL, 0.0022, NULL, -0.002116, 0.003867),
('2013-09-30', -0.004118, 0.004128, NULL, 0.001394, NULL, 0.008333, 0.001356),
('2013-10-31', 0.010754, -0.001578, NULL, 0.003483, NULL, -0.004724, -0.001342),
('2013-11-30', 0.002402, 0.020835, NULL, 0.001, NULL, 0.003954, -0.000306),
('2013-12-31', 0.004581, 0.015689, NULL, 0.002146, NULL, 0.008333, 0.001118),
('2014-01-31', -0.00055, -0.001027, -0.008245, 0.00732, NULL, 0.004025, -0.007227),
('2014-02-28', -0.001512, 0.001653, -0.009029, 0.009919, NULL, -0.008333, -0.003878),
('2014-03-31', 0.008784, 0.004364, -0.011608, 0.001, NULL, -0.008333, -0.004822),
('2014-04-30', 0.008412, -0.012369, -0.004692, 0.004536, NULL, -0.006303, 0.004306),
('2014-05-31', 0.003945, 0.010651, -0.016833, 0.001, NULL, -0.007974, 0.005221),
('2014-06-30', 0.012371, 0.01773, -0.010384, 0.010593, NULL, -0.004781, -0.000731),
('2014-07-31', 0.011915, 0.004308, -0.012965, 0.001, NULL, 0.007751, -0.009239),
('2014-08-31', -0.013738, 0.00039, 0.000009, 0.005139, 0.001, -0.003319, -0.003636),
('2014-09-30', -0.004081, 0.01968, -0.008214, 0.007976, 0.001, -0.003799, 0.005563),
('2014-10-31', 0.01608, 0.015291, -0.002969, 0.01303, 0.001, -0.004645, 0.001599),
('2014-11-30', 0.011241, 0.012312, 0.007088, 0.00384, 0.000852, 0.006783, -0.008487),
('2014-12-31', -0.004251, 0.008737, -0.013576, 0.001, 0.001, -0.0034, 0.001013),
('2015-01-31', -0.004039, 0.012938, -0.011891, 0.004576, 0.001, -0.004195, -0.002653),
('2015-02-28', 0.026326, 0.019695, -0.013178, 0.010737, 0.001, 0.006418, -0.00952),
('2015-03-31', -0.000628, 0.008029, 0.005917, 0.017461, 0.00048, -0.00501, 0.007879),
('2015-04-30', 0.005688, 0.01249, -0.018813, 0.001, -0.002104, 0.003657, 0.009886),
('2015-05-31', 0.010039, 0.008442, -0.01465, 0.001, 0.001, 0.001024, 0.001695),
('2015-06-30', -0.004267, 0.001391, -0.022504, 0.003104, 0.001, -0.003507, 0.009854),
('2015-07-31', -0.001672, 0.020558, -0.011859, 0.001, 0.001, NULL, 0.008361),
('2015-08-31', 0.015726, 0.017419, -0.004556, 0.001, -0.000462, NULL, -0.007253),
('2015-09-30', 0.001977, 0.007301, -0.00811, 0.013011, -0.001032, NULL, 0.009911),
('2015-10-31', 0.016201, 0.00877, -0.005536, 0.014527, -0.000833, NULL, 0.009897),
('2015-11-30', 0.019996, -0.000785, -0.002161, 0.001947, 0.001, NULL, -0.007307),
('2015-12-31', -0.003254, 0.009595, -0.013785, 0.001, 0.001, NULL, 0.007196)) n (dt, man1, man2,
man3, man4, man5,
man6, bmark);
--To calculate the downside deviation for each manager using a minimum acceptable return of 0.5% we run the following SQL.
SELECT wct.DownsideDeviation(man1, 0.005, 'False') as man1,
wct.DownsideDeviation(man2, 0.005, 'False') as man2,
wct.DownsideDeviation(man3, 0.005, 'False') as man3,
wct.DownsideDeviation(man4, 0.005, 'False') as man4,
wct.DownsideDeviation(man5, 0.005, 'False') as man5,
wct.DownsideDeviation(man6, 0.005, 'False') as man6,
wct.DownsideDeviation(bmark, 0.005, 'False') as bmark
FROM #managers;
This produces the following result.
| man1 | man2 | man3 | man4 | man5 | man6 | bmark |
|---|---|---|---|---|---|---|
| 0.00865093912455995 | 0.00689585718186849 | 0.0162006736490128 | 0.00315539473862174 | 0.00462948019569724 | 0.00948357156624939 | 0.00880216921909244 |
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),
('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-02-28', 'man1', 0.024726),
('2013-02-28', 'man4', 0.005703),
('2013-02-28', 'man6', 0.006496),
('2013-02-28', 'bmark', -0.004624),
('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-04-30', 'man1', 0.022139),
('2013-04-30', 'man4', 0.007118),
('2013-04-30', 'man6', -0.007312),
('2013-04-30', 'bmark', -0.005431),
('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-06-30', 'man1', -0.007964),
('2013-06-30', 'man4', 0.003757),
('2013-06-30', 'man6', -0.001899),
('2013-06-30', 'bmark', -0.007060),
('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-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-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-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-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-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),
('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-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-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-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-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-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-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-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-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-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-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-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),
('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-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-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-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-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-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-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-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-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-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-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-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)
) n (dt, man, R);
SELECT man as manager,
wct.DownsideDeviation(r, 0.005, 'False') as DownsideDeviation
FROM #nmanagers m
GROUP BY man;
This produces the following result.
| manager | DownsideDeviation |
|---|---|
| bmark | 0.00880216921909244 |
| man1 | 0.00865093912455995 |
| man2 | 0.00689585718186849 |
| man3 | 0.0162006736490128 |
| man4 | 0.00315539473862174 |
| man5 | 0.00462948019569724 |
| man6 | 0.00948357156624939 |
See Also
DOWNSIDEFREQUENCY - Calculate the downside frequency of asset returns
DOWNSIDEPOTENTIAL - Calculate the downside potential of asset returns
OMEGA - Calculate the Omega of asset returns
OMEGAEXCESSRETURN - Calculate the Omega Excess Return
OMEGASHARPERATIO - Calculate the Omega-Sharpe ratio of asset returns
UPSIDEFREQUENCY - Calculate the upside frequency of asset returns
UPSIDEPOTENTIALRATIO - Calculate the Upside Potential Ratio
UPSIDERISK - Calculate the Upside Risk, Upside Variance or Upside Deviation.