Avg Price Recon

The below is a selection of my currently longest running Excel VBA scripted macro - A macro to intake an unprepared excel export with daily position statements.

Sub AvgPriceRec()
'
'Avg_Price_Rec Macro
' Macro support provided by Scott Goley
'
Dim LR As Long

    Columns("A:A").Select
    Selection.AutoFilter
    ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:="=Account" _
        , Operator:=xlOr, Criteria2:="="
'filters column A for Account or blank

• Establish the base criteria for the report contraints - We are only interested in line items which are part of a trading account vs other account types.

ActiveSheet.UsedRange.SpecialCells (xlCellTypeVisible)
'select only visible cells
    Selection.EntireRow.Delete
    ActiveSheet.Range("A:F").AutoFilter Field:=1
    Range("A1").Select
'Clear all filters
If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

• Delete on non relevant material by means of a filter and then delete all else (variable length of rows)

'Add header
Rows("1:1").Select
Selection.Insert Shift:=xlDown
[A1].Value = "Account"
[B1].Value = "Account"
[C1].Value = "Account"
[D1].Value = "Account"
[E1].Value = "Account"
[F1].Value = "Account"
[I1].Value = "Account"
[J1].Value = "Account"
[K1].Value = "Account"

'RIC conversion without efectting ISINs
    Columns("B:B").Select
    Selection.Replace What:=".*", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows,MatchCase:=False, SearchFormat:=False _
    ReplaceFormat:=False
    

• Rename column headers according to final report structure and handle for various security types with a common output (ISIN)

'add second sheet
ActiveWorkbook.Sheets.Add After:=ActiveSheet
'Insert data validation/reason data
[A1].Value = "Reason Code"
[A2].Value = "ACCT -CLSD"
[A3].Value = "ACCT -NA"
[A4].Value = "ALLO -PENDING"
[A5].Value = "ALLO -T1"
[A6].Value = "CLNT - DUPE"
[A8].Value = "CORP"
[A9].Value = "INTCO"
[A10].Value = "INTCO - DUPE"
[A11].Value = "INTCO - ERROR"
[A12].Value = "IT - ARINA"
[A13].Value = "IT - AXIOM"
[A14].Value = "IT - DCA"
[A15].Value = "IT - FIX"
[A16].Value = "IT - GBA"
[A17].Value = "IT - OASYS"
[A18].Value = "IT - SWIFT"
[A19].Value = "IT - XOMS"
[A20].Value = "IT - CTM"
[A21].Value = "IT - IDEAL"
[A22].Value = "IT - TRACER"
[A23].Value = "OTHER"
[A24].Value = "STATIC - QUORUM"
[A25].Value = "STATIC - SECURITY ID"
[A26].Value = "STATIC - ESPEAR"
[A27].Value = "STATIC - F/O"
[A28].Value = "STATIC-GBA"
[B1].Value = ""
[B2].Value = ""
[B3].Value = ""
[B4].Value = ""
[B5].Value = ""
[B6].Value = ""
[B7].Value = ""
[B8].Value = ""
[B9].Value = ""
[B10].Value = ""
[B11].Value = ""
[B12].Value = ""
[B13].Value = ""
[B14].Value = ""
[B15].Value = ""
[B16].Value = ""
[B17].Value = ""
[B18].Value = ""
[B19].Value = ""
[B20].Value = ""
[B21].Value = ""
[B22].Value = ""
[B23].Value = ""
[B24].Value = ""
[B25].Value = ""
[B26].Value = ""
[B27].Value = ""
[B28].Value = ""

• Insert references for categorizing errors / break type.

'Define name for A column of Sheet 2
    Columns("A:A").Select
    ActiveWorkbook.Names.Add Name:="reason_codes", RefersToR1C1:="=Sheet2!C1"
    ActiveWorkbook.Names("reason_codes").Comment = ""
    Sheets("Sheet1").Select
'add SubTotal Table
    Columns("A:F").Select
    Selection.Subtotal GroupBy:=1, Function:=xlCountNums, TotalList:=Array(1,_
       2), Replace:=False, PageBreaks:=False, SummaryBelowData:=True
'define sheet name

'define name

    Sheets("Sheet2").Select
    Range("A1:A28").Select
    ActiveWorkbook.Names.Add Name:="Reason_Code", RefersToR1C1:= _
        "=Sheet2!R1C1:R28C1"
    ActiveWorkbook.Names("Reason_Code").Comment = ""

• Insert Sub Total table type grouped by similar trading account

    
'data validation
    Sheets("Sheet1").Select
    Columns("L:L").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Forumla1:="=Reason_Code"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("L2").Select
'close table to level 2
    ActiveSheet.Outline.ShowLevels RowLevels:=2
'insert top row filter and delete empty column
    Range("B1:G1").Select
    Selection.AutoFilter
    Columns("A:A").Selection
    Selection.Delete Shift:=xlToLeft
'make shared (multiple users can edit)
If Not ActiveWorkbook.MultiUserEditing Then
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ActiveWorkbook.Name, accessmode:=xlShared
    Application.DisplayAlerts = True
    MsgBox "Now Shared"
  End If
End Sub

• Data validation and final report output creation with file sharing.