SQL Server BetaCoVar Function
Updated 2024-02-14 16:57:06.510000
Description
Use the aggregate function BetaCoVar to calculate the beta-covariance of an asset return and a benchmark return. BetaCoVar is calculated as:
\mathrm{BetaCoVar=\frac{\sigma_{R_a,R_b}}{\sigma^2_{R_b}}
Where
Ra = asset return
Rb = benchmark return
Syntax
SELECT [westclintech].[wct].[BetaCoVar](
<@Ra, float,>
,<@Rb, float,>)
Arguments
@Ra
the asset return for a period; the percentage return in floating point format (i.e. 10% = 0.10). @Ra 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). @Rb is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If @Ra IS NULL or @Rb IS NULL then that row is not included in the calculation.
If n = 0 then NULL is returned.
Examples
In this example we have returns for an asset and its benchmark for the last three years.
SELECT wct.BetaCoVar(Ra, Rb) as BetaCoVar
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.
| BetaCoVar |
|---|
| 0.234187219388425 |
In this example we have 6 managers plus a benchmark stored in the #managers table which is in 'spreadsheet' format.
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 beta-covariance for each manager against the benchmark we run the following SQL.
SELECT wct.BetaCoVar(man1, bmark) as man1,
wct.BetaCoVar(man2, bmark) as man2,
wct.BetaCoVar(man3, bmark) as man3,
wct.BetaCoVar(man4, bmark) as man4,
wct.BetaCoVar(man5, bmark) as man5,
wct.BetaCoVar(man6, bmark) as man6
FROM #managers;
This produces the following result.
| man1 | man2 | man3 | man4 | man5 | man6 |
|---|---|---|---|---|---|
| -0.468006037852044 | 0.107078286729982 | -0.281026195064153 | 0.0981845254480347 | -0.0516219863678916 | -0.252149786272334 |
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 m.man as manager,
wct.BetaCoVar(m.r, b.r) as BetaCoVar
FROM #nmanagers m
INNER JOIN #nmanagers b
ON m.dt = b.dt
WHERE m.man <> 'bmark'
AND b.man = 'bmark'
GROUP BY m.man;
This produces the following result.
| manager | BetaCoVar |
|---|---|
| man1 | -0.468006037852044 |
| man2 | 0.107078286729982 |
| man3 | -0.281026195064153 |
| man4 | 0.0981845254480347 |
| man5 | -0.0516219863678916 |
| man6 | -0.252149786272334 |
See Also
BETACOSKEW - calculate the beta-coskewness of an asset return and a benchmark return
BETACOKURT - Calculate the beta-cokurtosis of an asset return and a benchmark return
FINCOSKEW - Calculate coskewness of an asset return and a benchmark return.
FINCOKURT - Calculate the cokurtosis of an asset return and a benchmark return