# SQL Server upside potential of asset returns

UpsidePotentialRatio

Updated: 31 January 2016

Use the aggregate function UpsidePotentialRatio to calculate the Upside Potential Ratio. The Upside Potential Ratio, developed by Frank A. Sortino, measures upside performance per unit of downside risk. The Upside Potential Ratio can be calculated as:

Where

 R = asset return MAR = minimum acceptable return n = number of rows in the GROUP nu = either n or the number of rows where R > MAR nd = either n or the number of rows where MAR > R

Syntax
SELECT [wct].[UpsidePotentialRatio](
<@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. When @Full is TRUE then nu and nd are the number of non-null rows in the GROUP; when @Full is FALSE then nu is the number of rows where @R > @MAR and nd 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 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.
Â·         If @Full IS NULL then @Full is set to TRUE.
Â·         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.UpsidePotentialRatio(Ra,0.005,'False') AS [UPR False],
wct.UpsidePotentialRatio(Ra,0.005,NULL) AS [UPS True]
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 upside potential ratio for each manager against a minimum acceptable return of 0.5% we run the following SQL.
SELECT
wct.UpsidePotentialRatio(man1,0.005,'False') as man1
,wct.UpsidePotentialRatio(man2,0.005,'False') as man2
,wct.UpsidePotentialRatio(man3,0.005,'False') as man3
,wct.UpsidePotentialRatio(man4,0.005,'False') as man4
,wct.UpsidePotentialRatio(man5,0.005,'False') as man5
,wct.UpsidePotentialRatio(man6,0.005,'False') as man6
,wct.UpsidePotentialRatio(bmark,0.005,'False') 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.UpsidePotentialRatio(r,0.005,'False') as UpsidePotentialRatio
FROM
#nmanagers m
GROUP BY
man

This produces the following result.