header image
[ # ] Calculate Expected Shortfall in SQL
August 20th, 2015 under Programming

A commonly used risk measure is the Expected Shortfall. Unfortunately, this function to calculate the sample expected shortfall is rarely implemented. As there is much data stored in databases accessible through SQL, I tried to calculate this value directly in SQL. It turns out this is a one-liner. Let’s assume we have a table called “LossSimulation” and a column/field named “LossValue” then the sample expected shortfall at level 95% is

1
SELECT AVG(LossValue) FROM (SELECT TOP 95 PERCENT LossValue FROM LossSimulation ORDER BY LossValue)
SELECT AVG(LossValue) FROM (SELECT TOP 95 PERCENT LossValue FROM LossSimulation ORDER BY LossValue)

Note: Microsoft Access may list more than 95% of all records because if multiple value at the 95% quantile value are the same then all these records are listed (see here).



Flickr
Scuba DivingEigerEiger, MönchKleine Scheidegg

View All Photos

Recent Posts
Quotes
Boyd: God says you look for excuses to be alone.
Dr. House: See, that is exactly the kind of brilliance that sounds deep, but you could say it about any person who doesn't pine for the social approval of everyone he meets - which you were cleverly able to deduce about me by not being a moron. Next time, tell God to be more specific.”
IP
[FSF Associate Member]
[FSF Associate Member]
Michael Pfeuti
Profil von Michael Pfeuti auf LinkedIn anzeigen
profile for Michael Pfeuti at Stack Overflow, Q&A for professional and enthusiast programmers