Login     Register

        Contact Us     Search

Menu

XLeratorDB/financial Documentation

SQL Server Omega Excess of asset returns


OmegaExcessReturn

Updated: 31 January 2016


Use the aggregate function OmegaExcessReturn to calculate the Omega Excess Return. Omega Excess Return is calculated as:

Omega Excess Return formula
Where

Ra
=
asset return
Rb
=
benchmark return
MAR
=
minimum acceptable return
Freq
=
periodicity of returns
n
=
number of non-NULL returns in a GROUP

Syntax
SELECT [wct].[OmegaExcessReturn](
  <@Ra, float,>
 ,<@Rb, float,>
 ,<@MAR, 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.
@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.
@Freq
the period in which @Ra, @Rb, and @MAR 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 @MAR IS NULL it is set to zero.
·         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.
·         @Freq must be greater than zero.
·         If @Freq IS NULL then @Freq is set to 12.
·         Available in XLeratorDB / financial 2008 only
Examples
In this example we have monthly returns for an asset and its benchmark for the last three years. The minimum acceptable return is 0.005.
SELECT
   wct.OmegaExcessReturn(Ra,0.005) as OmegaExcessReturn
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 the monthly returns for 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 Omega excess return for each manager against a minimum acceptable return of 0.5% we run the following SQL.
SELECT
   wct.OmegaExcessReturn(man1,bmark,0.005,12) as man1
   ,wct.OmegaExcessReturn(man2,bmark,0.005,12) as man2
   ,wct.OmegaExcessReturn(man3,bmark,0.005,12) as man3
   ,wct.OmegaExcessReturn(man4,bmark,0.005,12) as man4
   ,wct.OmegaExcessReturn(man5,bmark,0.005,12) as man5
   ,wct.OmegaExcessReturn(man6,bmark,0.005,12) 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.OmegaExcessReturn(m.r, b.r,0.005,12) as OmegaExcessReturn
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.

 

See Also

 



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