Calculate Expected Shortfall in SQL 
August 20th, 2015 under Programming. [ Comments: none ]

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 oneliner. 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).

Unconstrained Optimization 
August 13th, 2015 under Programming. [ Comments: none ]

MATLAB/GNU Octave offers simple ways to find the minimum of any function. The two easiest methods are
and
. The former uses a simplex search method while the latter uses a gradient based method.
Here is a simple example for a lognormal parameter estimation:
1
2
3
4
 x = sort([0.0027400 0.0093730 0.0458460 0.0135300])
y = (1:length(x))./(length(x)+1)
fun = @(p) sum( (logninv(y,p(1),p(2))  x).^2)
estimate = fminsearch(fun,[5;2]) 
x = sort([0.0027400 0.0093730 0.0458460 0.0135300])
y = (1:length(x))./(length(x)+1)
fun = @(p) sum( (logninv(y,p(1),p(2))  x).^2)
estimate = fminsearch(fun,[5;2])



