Operational Risk Methodology on VBA

[Mazurofgod]

Hi folks,
I am trying to create a oprisk methodology based on the guidelines provided using VBA until we switch to R language. Could someone verify the code below based on the guidelines provided as well. Statistician background would be really helpful.

Guidelines:
Step 1: Calculate the mean number of claims based on the claims historically observed and recorded in the claim database.
Step 2: Calculate the mean and standard deviation of the amount of claims based on the claims historically observed and recorded in the claim database.
Step 3: Calculate the number of claims using a Poisson distribution. The calculation is based on the mean of the historical claims from the claim database, which was calculated in step 1.
Step 4: Calculate the amount of claims using a lognormal distribution. The calculation is based on the mean and standard deviation of the claims historically observed and recorded in the claim database, which were calculated in step 2.
Step 5: Apply a Monte Carlo simulation. The Monte Carlo simulation is based on determining the number of claims using the Poisson distribution from step 3 and determining the amount of claims using the lognormal distribution from step 4. Using the Monte Carlo simulation, 1 million simulation runs are calculated, in which a potential future number of claims and the associated claim amounts are determined for each simulation run.
Step 6: Summing up the claim amounts resulting from the Monte Carlo simulation.
Step 7: Calculating the expected loss by calculating the mean of the summed results of the claim amount from step 6.
Step 8: Calculating the unexpected loss by calculating the standard deviation of the summed results of the claim amount from step 6.
Step 9: Determining the value at risk at the 99.9% confidence level.

Sample data is just as below. 3 columns: No. of Claims (Col A); Recorded Claim (Col. B)

VBA Code:

Sub CalculateOperationalRisk()
    ' Initialize parameters and define variables
    Dim wsClaims As Worksheet, wsResults As Worksheet
    Dim lastRow As Long
    Dim sumClaims As Double, meanClaims As Double
    Dim sumLoss As Double, meanLoss As Double, stdDevLoss As Double
    Dim numSimulations As Long, i As Long, j As Long
    Dim claimsPerRun As Integer, totalLossAmount As Double
    Dim lossAmount As Double, sumSimulationResults As Double, sumSimulationSquares As Double
    Dim expectedLoss As Double, unexpectedLoss As Double, var99_9 As Double
    Dim simulationResults() As Double
    Dim muLog As Double, sigmaLog As Double

    ' Set up worksheets and initial parameters
    Set wsClaims = ThisWorkbook.Sheets("ClaimsData")    ' Claims data worksheet
    Set wsResults = ThisWorkbook.Sheets("Results")      ' Results worksheet
    numSimulations = 1000000                            ' Number of Monte Carlo simulations
    ReDim simulationResults(1 To numSimulations)        ' Array to store results of simulations

    ' Disable screen updating to improve performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual        ' Turn off automatic calculations

    ' Step 1: Calculate Mean Number of Claims (?)
    lastRow = wsClaims.Cells(wsClaims.Rows.Count, "A").End(xlUp).Row
    meanClaims = lastRow - 1                              ' ? - average number of damage events (incident count)

    ' Step 2: Calculate Mean (µ) and Standard Deviation (s) of Loss Amounts (using Gross Loss)
    Dim grossLosses() As Variant
    grossLosses = wsClaims.Range("B2:B" & lastRow).Value   ' Load gross loss data into an array for faster access

    ' Calculate mean gross loss
    For i = 1 To UBound(grossLosses, 1)
        sumLoss = sumLoss + grossLosses(i, 1)
    Next i
    meanLoss = sumLoss / (lastRow - 1)                       ' µ - average gross loss amount

    ' Calculate standard deviation (s) for loss amounts
    For i = 1 To UBound(grossLosses, 1)
        sumSimulationSquares = sumSimulationSquares + (grossLosses(i, 1) - meanLoss) ^ 2
    Next i
    stdDevLoss = Sqr(sumSimulationSquares / (lastRow - 2))

    ' Convert mean and stdDev to lognormal parameters muLog and sigmaLog
    muLog = Log((meanLoss ^ 2) / Sqr(stdDevLoss ^ 2 + meanLoss ^ 2))
    sigmaLog = Sqr(Log(1 + (stdDevLoss ^ 2 / meanLoss ^ 2)))

    ' Step 3-4: Monte Carlo Simulation with 1 Million Runs (store results in array)
    For i = 1 To numSimulations
        claimsPerRun = SimulatePoisson(meanClaims)       ' Calculate number of claims for each run
        totalLossAmount = 0
       
        ' Sum loss amounts for this simulation run using log-normal distribution
        For j = 1 To claimsPerRun
            lossAmount = SimulateLognormal(muLog, sigmaLog)
            totalLossAmount = totalLossAmount + lossAmount
        Next j
       
        ' Store the result in the simulationResults array
        simulationResults(i) = totalLossAmount
        sumSimulationResults = sumSimulationResults + totalLossAmount
    Next i

    ' Calculate Expected Loss (EL)
    expectedLoss = sumSimulationResults / numSimulations

    ' Calculate Unexpected Loss (UL) - Standard Deviation of Simulation Results
    sumSimulationSquares = 0
    For i = 1 To numSimulations
        sumSimulationSquares = sumSimulationSquares + (simulationResults(i) - expectedLoss) ^ 2
    Next i
    unexpectedLoss = Sqr(sumSimulationSquares / (numSimulations - 1))

    ' Calculate Value at Risk (VaR) at 99.9% confidence level
    ' Sort the array in memory to get the 99.9th percentile
    QuickSort simulationResults, LBound(simulationResults), UBound(simulationResults)
    var99_9 = simulationResults(Application.WorksheetFunction.RoundUp(0.999 * numSimulations, 0))

    ' Output results to the Results sheet
    wsResults.Range("C2").Value = "Expected Loss (EL)"
    wsResults.Range("D2").Value = expectedLoss
    wsResults.Range("C3").Value = "Unexpected Loss (UL)"
    wsResults.Range("D3").Value = unexpectedLoss
    wsResults.Range("C4").Value = "Value at Risk (VaR) at 99.9%"
    wsResults.Range("D4").Value = var99_9

    ' Re-enable screen updating and automatic calculations
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    ' Display completion message
    MsgBox "Operational Risk Calculation done!", vbInformation
End Sub

' Function to simulate number of claims using Poisson distribution
Function SimulatePoisson(lambda As Double) As Integer
    Dim r As Double, L As Double, k As Integer
    L = Exp(-lambda)
    k = 0
    r = 1
    Do
        k = k + 1
        r = r * Rnd()
    Loop While r > L
    SimulatePoisson = k - 1
End Function

' Function to simulate loss amount using lognormal distribution
Function SimulateLognormal(muLog As Double, sigmaLog As Double) As Double
    Dim u1 As Double, u2 As Double
    Dim z0 As Double

    ' Generate two uniform random numbers in the range (0,1)
    u1 = Rnd()
    u2 = Rnd()
   
    ' Check if u1 is zero to avoid logarithm error
    If u1 = 0 Then
        u1 = 0.0001 ' Set a small positive number instead
    End If
   
    ' Box-Muller transform to get normally distributed value
    z0 = Sqr(-2 * Log(u1)) * Cos(2 * Application.WorksheetFunction.Pi() * u2)

    ' Transform to lognormal distribution
    SimulateLognormal = Exp(muLog + sigmaLog * z0)
End Function

' QuickSort function to sort the simulation results array for VaR calculation
Sub QuickSort(arr As Variant, ByVal first As Long, ByVal last As Long)
    Dim low As Long, high As Long
    Dim midValue As Double, temp As Double
   
    low = first
    high = last
    midValue = arr((first + last) \ 2)
   
    Do While low <= high
        Do While arr(low) < midValue
            low = low + 1
        Loop
        Do While arr(high) > midValue
            high = high - 1
        Loop
        If low <= high Then
            temp = arr(low)
            arr(low) = arr(high)
            arr(high) = temp
            low = low + 1
            high = high - 1
        End If
    Loop
   
    If first < high Then QuickSort arr, first, high
    If low < last Then QuickSort arr, low, last
End Sub

Will provide the respective formulas if needed. Thank you in advance for helping out.