Attribute VB_Name = "BloombergDataCleaning"
Option Explicit
Public Sub CleanBloombergExport()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim rowIndex As Long
    Dim colIndex As Long
    Dim cellValue As Variant
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    If lastRow < 2 Then
        MsgBox "Active sheet does not contain enough Bloomberg export data.", vbExclamation
        Exit Sub
    End If
    Application.ScreenUpdating = False
    ' Remove common title rows until the header row starts with DATE or TICKER.
    Do While lastRow > 1
        If UCase$(Trim$(CStr(ws.Cells(1, 1).Value))) = "DATE" Or UCase$(Trim$(CStr(ws.Cells(1, 1).Value))) = "TICKER" Then
            Exit Do
        End If
        ws.Rows(1).Delete
        lastRow = lastRow - 1
    Loop
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    For rowIndex = 2 To lastRow
        For colIndex = 1 To lastCol
            cellValue = ws.Cells(rowIndex, colIndex).Value
            If IsError(cellValue) Then
                ws.Cells(rowIndex, colIndex).ClearContents
            ElseIf Trim$(CStr(cellValue)) = "#N/A N/A" Or Trim$(CStr(cellValue)) = "#N/A" Then
                ws.Cells(rowIndex, colIndex).ClearContents
            ElseIf Left$(Trim$(CStr(cellValue)), 1) = "'" Then
                ws.Cells(rowIndex, colIndex).Value = Mid$(Trim$(CStr(cellValue)), 2)
            End If
            If IsDate(ws.Cells(rowIndex, colIndex).Value) Then
                ws.Cells(rowIndex, colIndex).Value = CDate(ws.Cells(rowIndex, colIndex).Value)
                ws.Cells(rowIndex, colIndex).NumberFormat = "yyyy-mm-dd"
            ElseIf IsNumeric(ws.Cells(rowIndex, colIndex).Value) Then
                ws.Cells(rowIndex, colIndex).Value = CDbl(ws.Cells(rowIndex, colIndex).Value)
                ws.Cells(rowIndex, colIndex).NumberFormat = "#,##0.00"
            End If
        Next colIndex
    Next rowIndex
    ' Drop fully blank rows left behind after removing Bloomberg artifacts.
    For rowIndex = lastRow To 2 Step -1
        If WorksheetFunction.CountA(ws.Rows(rowIndex)) = 0 Then
            ws.Rows(rowIndex).Delete
        End If
    Next rowIndex
    With ws.Rows(1)
        .Font.Bold = True
        .Interior.Color = RGB(217, 187, 106)
    End With
    ws.Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = True
    MsgBox "Bloomberg export cleaned and normalized.", vbInformation
End Sub