VBA code to check
analytical/semi-analytical analysis of the characteristics of the Expected
Worst Loss in T realisations for Normal random variables
[this page | pdf | back links]
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