/

### VBA code to check analytical/semi-analytical analysis of the characteristics of the Expected Worst Loss in T realisations for Normal random variables

See here for an analytical/semi-analytical analysis. This page provides VBA code (for Microsoft Excel) capable of carrying out a simulation analysis to check the analytical/semi-analytical analysis:

Option Explicit

'(c) Nematrian 2011

Public Sub TestExpectedWorstLostInTRealisationsNormal()

'tests (in VBA) results derived analytically or semi-analytically for Expected Worst Loss

'in T realisations, if underlying random variables are unit Normal (i.e. N(0,1))

Dim i As Integer

Dim j As Integer

Dim m As Integer

Dim n As Integer

Dim r() As Double

Dim rmin() As Double

Dim avrmin() As Double

Dim xsum As Double

m = 256

n = 10000

ReDim r(0 To m - 1, 0 To n - 1)

ReDim rmin(0 To m - 1, 0 To n - 1)

ReDim avrmin(0 To m - 1)

'populate m series each with n example draws.

For i = 0 To m - 1

For j = 0 To n - 1

r(i, j) = Excel.WorksheetFunction.NormSInv(Rnd())

Next

Next

'calculate minimum of first i of the series, for each of the example draws, and their averages

xsum = 0

For j = 0 To n - 1

rmin(0, j) = r(0, j)

xsum = xsum + rmin(0, j)

Next

avrmin(0) = xsum / n

For i = 1 To m - 1

xsum = 0

For j = 0 To n - 1

If r(i, j) > rmin(i - 1, j) Then

rmin(i, j) = rmin(i - 1, j)

Else

rmin(i, j) = r(i, j)

End If

xsum = xsum + rmin(i, j)

Next

avrmin(i) = xsum / n

Next

'and output

With ThisWorkbook.Worksheets("Sheet1")

.Cells(1, 1) = "T"

.Cells(1, 2) = "EWL in T realisations (approx)"

For i = 0 To m - 1

.Cells(2 + i, 1) = i + 1

.Cells(2 + i, 2) = avrmin(i)

Next

End With

End Sub