Attribute VB_Name = "HistoricalVaRCalculator"
Option Explicit
Public Sub CalculateHistoricalVaR()
    Dim ws As Worksheet
    Dim rawData As Variant
    Dim cleanReturns() As Double
    Dim lastRow As Long
    Dim rowIndex As Long
    Dim cleanCount As Long
    Dim returnValue As Double
    Dim var95 As Double
    Dim var99 As Double
    Dim es95 As Double
    Dim es99 As Double
    Dim es95Count As Long
    Dim es99Count As Long
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "Put daily P&L returns in column A, starting on row 2.", vbExclamation
        Exit Sub
    End If
    rawData = ws.Range("A2:A" & lastRow).Value
    ReDim cleanReturns(1 To UBound(rawData, 1))
    ' Keep only numeric observations so blanks and labels do not distort the tail risk.
    For rowIndex = 1 To UBound(rawData, 1)
        If IsNumeric(rawData(rowIndex, 1)) And Not IsEmpty(rawData(rowIndex, 1)) Then
            cleanCount = cleanCount + 1
            cleanReturns(cleanCount) = CDbl(rawData(rowIndex, 1))
        End If
    Next rowIndex
    If cleanCount < 20 Then
        MsgBox "At least 20 numeric return observations are required.", vbCritical
        Exit Sub
    End If
    ReDim Preserve cleanReturns(1 To cleanCount)
    var95 = WorksheetFunction.Percentile_Inc(cleanReturns, 0.05)
    var99 = WorksheetFunction.Percentile_Inc(cleanReturns, 0.01)
    For rowIndex = LBound(cleanReturns) To UBound(cleanReturns)
        returnValue = cleanReturns(rowIndex)
        If returnValue <= var95 Then
            es95 = es95 + returnValue
            es95Count = es95Count + 1
        End If
        If returnValue <= var99 Then
            es99 = es99 + returnValue
            es99Count = es99Count + 1
        End If
    Next rowIndex
    es95 = es95 / es95Count
    es99 = es99 / es99Count
    With ws.Range("C2:E7")
        .ClearContents
        .Interior.Color = RGB(9, 20, 40)
        .Font.Color = RGB(241, 245, 249)
        .Borders.LineStyle = xlContinuous
    End With
    ' Output positive risk numbers even though the raw return tail is negative.
    ws.Range("C2").Value = "Historical VaR Summary"
    ws.Range("C3").Value = "Metric"
    ws.Range("D3").Value = "95%"
    ws.Range("E3").Value = "99%"
    ws.Range("C4").Value = "VaR"
    ws.Range("C5").Value = "Expected Shortfall"
    ws.Range("C6").Value = "Observations"
    ws.Range("D4").Value = -var95
    ws.Range("E4").Value = -var99
    ws.Range("D5").Value = -es95
    ws.Range("E5").Value = -es99
    ws.Range("D6").Value = cleanCount
    ws.Range("D4:E5").NumberFormat = "0.00%"
    ws.Range("C2:E3").Font.Bold = True
    ws.Columns("C:E").AutoFit
    MsgBox "Historical VaR summary written to columns C:E.", vbInformation
End Sub