SQL Server co-skewness of asset returns

FinCoSkew

Updated: 31 January 2016

Use the aggregate function FinCoSkew to calculate the coskewness of an asset return and a benchmark return. FinCoSkew is calculated as:

Where

 Ra = asset return Rb = benchmark return Ra = average asset return Rb = average benchmark return n = number of observations

Syntax
SELECT [wct].[FinCoSkew](
<@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.
Â·         Available in XLeratorDB / financial 2008 only
Examples
In this example we have returns for an asset and its benchmark for the last three years.
SELECT
wct.FinCoSkew(Ra,Rb) as FinCoSkew
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.

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 coskewness for each manager against the benchmark we run the following SQL.
SELECT
wct.FinCoSkew(man1,bmark) as man1
,wct.FinCoSkew(man2,bmark) as man2
,wct.FinCoSkew(man3,bmark) as man3
,wct.FinCoSkew(man4,bmark) as man4
,wct.FinCoSkew(man5,bmark) as man5
,wct.FinCoSkew(man6,bmark) as man6
FROM
#managers

This produces the following result.

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.FinCoSkew(m.r, b.r) as CoSkew
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.