CODE FOR REFERENCE
Sub Auto_Open()
Application.ScreenUpdating = False
If Sheets("RawData").Visible = True Then
RemoveMetricCol
End If
End Sub
Sub RemoveMetricCol()
'Deletion of Metrics Column
Dim colnum As Integer
Dim rownum As Integer
Dim RngObject As Range
Sheets("RawData").Select
Set RngObject = Cells.Find(What:="Metrics", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If RngObject Is Nothing Then
Range("A1").Select
PivotTable
Else
Cells.Find(What:="Metrics", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.EntireColumn.Delete
Range("A1").Select
PivotTable
End If
End Sub
Sub PivotTable()
Dim rownum As String
Dim colnum As String
Dim UsedRange As String
Dim Reporttype As String
Sheets("Top N Gainers & Losers").Select
Reporttype = Range("A1000").Value
Sheets("RawData").Select
colnum = ActiveSheet.UsedRange.Columns.Count
rownum = ActiveSheet.UsedRange.Rows.Count
UsedRange = "RawData!R1C1:R" & rownum & "C" & colnum
Sheets("Pivot").Select
Cells.Select
Selection.Delete
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
UsedRange).CreatePivotTable TableDestination:= _
"Pivot!R4C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Desk")
.Orientation = xlPageField
.Position = 1
End With
Range("A4").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Trader")
.Orientation = xlPageField
.Position = 1
End With
' Included to have the report date page by
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report Date")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Issuer(P)")
.Name = "Issuer (P)"
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Period")
.Name = "Period"
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total PnL"), "Total PnL ", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("New/Cancel'd"), "New/Cancel'd ", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Credit"), "Credit ", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Rates"), "Rates ", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Less Rates"), "Total Less Rates ", xlSum
' If condition included to differentiate the pivot table for different reports
If Reporttype = "1" Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Less Rates Less FX"), "Total Less Rates Less FX ", xlSum
ElseIf Reporttype = "2" Then
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Trading"), "Trading ", xlSum
Else
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Trading less FX"), "Trading less FX ", xlSum
End If
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Credit Notional"), "Credit Notional ", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("CS01"), "CS01 ", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("IR01"), "IR01 ", xlSum
With ActiveSheet.PivotTables("PivotTable1").DataPivotField
.Orientation = xlColumnField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.RowGrand = False
End With
Sheets("RawData").Select
ActiveWindow.SelectedSheets.Visible = False
' Formatting of the Pivot table starts here
' The number format is changed to have comma seperated values
Sheets("Pivot").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total PnL ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("New/Cancel'd ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Credit ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Rates ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Less Rates ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
' If condition included to differentiate the pivot table for different reports
If Reporttype = "1" Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Total Less Rates Less FX ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
ElseIf Reporttype = "2" Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Trading ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
Else
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Trading less FX ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
End If
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Credit Notional ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CS01 ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("IR01 ")
.NumberFormat = "#,##0;[Red](#,##0)"
End With
ActiveSheet.PivotTables("PivotTable1").MergeLabels = True
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Daily", xlDataAndLabel, True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "MTD", xlDataAndLabel, True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
ActiveSheet.PivotTables("PivotTable1").PivotSelect "YTD", xlDataAndLabel, True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 1
.Weight = xlMedium
End With
Cells.Select
Cells.EntireColumn.AutoFit
With Selection.Interior
.PatternColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Color = -10079488
End With
Range("A6:AB6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
Selection.Font.Bold = True
Range("A7:AB7").Select
Selection.Font.Bold = True
Range("B5:AB5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
With Selection.Font
.Color = -10079488
End With
Selection.Font.Bold = True
Range("A1").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
Range("A2").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
Range("B4").Select
Selection.Font.Bold = True
Range("A3").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
'Descending order for values of Total Less Rates Less FX
'ActiveSheet.PivotTables("PivotTable1").PivotFields("Issuer (P)").AutoSort _
xlDescending, "Total Less Rates Less FX ", ActiveSheet.PivotTables( _
"PivotTable1").PivotColumnAxis.PivotLines(6), 1
Range("G8").Select
Selection.Sort Key1:="R8C7", Order1:=xlDescending, Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
'Grouping the MTD and YTD metrics
ActiveWindow.Zoom = 80
Columns("B:AB").Select
Selection.ColumnWidth = 15
Rows("7:7").EntireRow.AutoFit
Columns("K:AB").Select
Selection.Columns.Group
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
'Added to left align the values under Issuer column
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Issuer (P)'[All]", _
xlLabelOnly, True
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
End With
If Reporttype = "1" Then
ActiveSheet.PivotTables("PivotTable1").PivotSelect _
"'Total Less Rates Less FX '", xlDataAndLabel, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
ElseIf Reporttype = "2" Then
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Trading '", xlDataAndLabel _
, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
Else
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Trading less FX '", _
xlDataAndLabel, True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14472642
End With
End If
'Keeping all the metric values in the Centre
ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Total PnL ':'IR01 '", _
xlDataAndLabel, True
With Selection
.HorizontalAlignment = xlCenter
End With
Range("A2").Select
End Sub