Login     Register

        Contact Us     Search

Menu

XLeratorDB/financial Documentation

SQL Server semi-deviation of asset returns


SemiDeviation

Updated: 31 January 2016


Use the aggregate function SemiDeviation to calculate the semi-deviation of asset returns. The formula for SemiDeviation is:
Semi Deviation formula 
 
Where

R
=
asset return
R
=
average asset return

Syntax
SELECT [wct].[SemiDeviation](
  <@R, float,>)
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.
Return Type
float
Remarks
·         If @R IS NULL it is not included in the calculation.
·         If there are no non-NULL rows in a GROUP 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.SemiDeviation(Ra) as SemiDeviation
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 semi-deviation for each manager against the benchmark we run the following SQL.
SELECT
   wct.SemiDeviation(man1) as man1
   ,wct.SemiDeviation(man2) as man2
   ,wct.SemiDeviation(man3) as man3     
   ,wct.SemiDeviation(man4) as man4     
   ,wct.SemiDeviation(man5) as man5     
   ,wct.SemiDeviation(man6) as man6
   ,wct.SemiDeviation(bmark) as bmark
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
    man as manager
   ,wct.SemiDeviation(r) as SemiDeviation
FROM
   #nmanagers m
GROUP BY
   man

This produces the following result.

 

See Also

 



View Topic History
Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service