I had multiple chart in one Excel Worksheet and wanted to change the scale of the x-axis and y-axis to the logarithmic scale. The following macro toggles between the linear and logarithmic scale for all charts:

Sub ChartsToggle()
For i =1 To ActiveSheet.ChartObjects.Count
Application.DisplayAlerts= False
If ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType= xlLinear Then
ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType= xlLogarithmic
ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType= xlLogarithmic
Else
ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType= xlLinear
ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType= xlLinear
End If
Next i
Application.DisplayAlerts= True
End Sub

Sub ChartsToggle()
For i = 1 To ActiveSheet.ChartObjects.Count
Application.DisplayAlerts = False
If ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear Then
ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLogarithmic
ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLogarithmic
Else
ActiveSheet.ChartObjects(i).Chart.Axes(xlCategory).ScaleType = xlLinear
ActiveSheet.ChartObjects(i).Chart.Axes(xlValue).ScaleType = xlLinear
End If
Next i
Application.DisplayAlerts = True
End Sub

Note the Application.DisplayAlerts= False. This prevents Excel from opening a dialog box, for every chart, informing you that negative values cannot be displayed. The dialog is just shown once at the end. I had to put this line inside the for-loop because it got changed back to true after the chart modification.

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

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

MATLAB/GNU Octave offers simple ways to find the minimum of any function. The two easiest methods are fminsearch and fminunc. 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:

x = sort([0.00274000.00937300.04584600.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])

I was faced with the problem of finding the last value of a data range in Excel. Assume the range resides in the column A. Today there are 50 values in A1:A50. Over the time new values will be added and the goal is to get the most recent (the last) value.

The solution is the INDEX() function. It allows you to select a value in a range by index. Therefore, the following solves my issue if I know there won’t be more than 100 values INDEX(A1:A100;COUNT(A1:A100))

Otherwise INDEX(A:A;COUNT(A:A)) gives the answer as long as there aren’t any empty cells between the values.