InitialD
03-02-2011, 05:54 PM
Hi, I need some help analyzing this code. I want to know how it get the values for "Closed P&L". I know it extract data from somewhere else, but how does it label what's "Closed" and what's not closed.
Private Sub buildClosedPL(exports As Workbook, output As Workbook, fund As String)
Dim liveBondInt As Double, liveRepoInt As Double, livePL As Double, liveMTD As Double, liveYTD As Double
Dim bondInt As Double, repoInt As Double, pl As Double, mtd As Double, ytd As Double
Dim strategy As String, standardStrategy As String
Dim i As Variant, j As Variant, k As Variant
Dim liveStrategies As Collection, deadStrategies As Collection, deadStdStrats As Collection
strategy = "NOT A REAL STRATEGY"
standardStrategy = "NOT A REAL STRATEGY"
Set liveStrategies = New Collection
i = 2
'traverse current output
output.Activate
While Cells(i, 1).Value <> "" Or Cells(i - 1, 1).Value <> ""
If Cells(i, 3).Value <> strategy Then 'new strategy
'do closed P&L for previous strategy
If strategy <> "NOT A REAL STRATEGY" And strategy <> "ZZ-MISC" Then
'add up daily bond interest and repo interest
j = 2
While exports.Sheets("Daily Interest").Cells(j, 1).Value <> ""
If exports.Sheets("Daily Interest").Cells(j, 2).Value = fund And exports.Sheets("Daily Interest").Cells(j, 4).Value = strategy Then
bondInt = bondInt + exports.Sheets("Daily Interest").Cells(j, 7).Value
repoInt = repoInt + exports.Sheets("Daily Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'add up daily pl
j = 2
While exports.Sheets("Daily PL").Cells(j, 1).Value <> ""
If exports.Sheets("Daily PL").Cells(j, 2).Value = fund And exports.Sheets("Daily PL").Cells(j, 4).Value = strategy Then
pl = pl + exports.Sheets("Daily PL").Cells(j, 15).Value
End If
j = j + 1
Wend
'add up mtd
j = 2
While exports.Sheets("MTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("MTD PL").Cells(j, 2).Value = fund And exports.Sheets("MTD PL").Cells(j, 4).Value = strategy Then
mtd = mtd + exports.Sheets("MTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("MTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("MTD Interest").Cells(j, 2).Value = fund And exports.Sheets("MTD Interest").Cells(j, 4).Value = strategy Then
mtd = mtd + exports.Sheets("MTD interest").Cells(j, 8).Value
End If
j = j + 1
Wend
'add up ytd
j = 2
While exports.Sheets("YTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("YTD PL").Cells(j, 2).Value = fund And exports.Sheets("YTD PL").Cells(j, 4).Value = strategy Then
ytd = ytd + exports.Sheets("YTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("YTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("YTD Interest").Cells(j, 2).Value = fund And exports.Sheets("YTD Interest").Cells(j, 4).Value = strategy Then
ytd = ytd + exports.Sheets("YTD Interest").Cells(j, 8).Value
End If
j = j + 1
Wend
'write to output
output.Activate
Rows(i).Insert
Cells(i, 2).Value = Cells(i - 1, 2).Value
Cells(i, 3).Value = Cells(i - 1, 3).Value
Cells(i, 6).Value = "Closed P&L"
Cells(i, 14).Value = bondInt - liveBondInt
Cells(i, 15).Value = repoInt - liveRepoInt
Cells(i, 16).Value = pl - livePL
Cells(i, 17).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value
Cells(i, 18).Value = mtd - liveMTD
Cells(i, 19).Value = ytd - liveYTD
i = i + 1
End If
'reset P&L
bondInt = 0
repoInt = 0
pl = 0
mtd = 0
ytd = 0
liveBondInt = 0
liveRepoInt = 0
livePL = 0
liveMTD = 0
liveYTD = 0
strategy = Cells(i, 3).Value
liveStrategies.Add strategy
End If
If Cells(i, 2).Value <> standardStrategy Then 'new standard strategy
'do closed P&L for dead strategies within this standard strategy
If standardStrategy <> "NOT A REAL STRATEGY" And standardStrategy <> "ZZ-MISC" Then
Set deadStrategies = New Collection
'traverse YTD PL to identify dead strategies
j = 2
While exports.Sheets("YTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("YTD PL").Cells(j, 3).Value = standardStrategy And exports.Sheets("YTD PL").Cells(j, 2).Value = fund Then
If Not containsItem(liveStrategies, exports.Sheets("YTD PL").Cells(j, 4).Value) Then
If Not containsItem(deadStrategies, exports.Sheets("YTD PL").Cells(j, 4).Value) Then
deadStrategies.Add exports.Sheets("YTD PL").Cells(j, 4).Value
End If
End If
End If
j = j + 1
Wend
'traverse YTD Interest to identify dead strategies
j = 2
While exports.Sheets("YTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("YTD Interest").Cells(j, 3).Value = standardStrategy And exports.Sheets("YTD Interest").Cells(j, 2).Value = fund Then
If Not containsItem(liveStrategies, exports.Sheets("YTD Interest").Cells(j, 4).Value) Then
If Not containsItem(deadStrategies, exports.Sheets("YTD Interest").Cells(j, 4).Value) Then
deadStrategies.Add exports.Sheets("YTD Interest").Cells(j, 4).Value
End If
End If
End If
j = j + 1
Wend
'for each dead strategy, get closed P&L
For k = 1 To deadStrategies.Count
'add up daily bond interest and repo interest
j = 2
While exports.Sheets("Daily Interest").Cells(j, 1).Value <> ""
If exports.Sheets("Daily Interest").Cells(j, 2).Value = fund And exports.Sheets("Daily Interest").Cells(j, 4).Value = deadStrategies(k) Then
bondInt = bondInt + exports.Sheets("Daily Interest").Cells(j, 7).Value
repoInt = repoInt + exports.Sheets("Daily Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'add up daily pl
j = 2
While exports.Sheets("Daily PL").Cells(j, 1).Value <> ""
If exports.Sheets("Daily PL").Cells(j, 2).Value = fund And exports.Sheets("Daily PL").Cells(j, 4).Value = deadStrategies(k) Then
pl = pl + exports.Sheets("Daily PL").Cells(j, 15).Value
End If
j = j + 1
Wend
'add up mtd
j = 2
While exports.Sheets("MTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("MTD PL").Cells(j, 2).Value = fund And exports.Sheets("MTD PL").Cells(j, 4).Value = deadStrategies(k) Then
mtd = mtd + exports.Sheets("MTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("MTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("MTD Interest").Cells(j, 2).Value = fund And exports.Sheets("MTD Interest").Cells(j, 4).Value = deadStrategies(k) Then
mtd = mtd + exports.Sheets("MTD Interest").Cells(j, 7).Value
mtd = mtd + exports.Sheets("MTD Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'add up ytd
j = 2
While exports.Sheets("YTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("YTD PL").Cells(j, 2).Value = fund And exports.Sheets("YTD PL").Cells(j, 4).Value = deadStrategies(k) Then
ytd = ytd + exports.Sheets("YTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("YTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("YTD Interest").Cells(j, 2).Value = fund And exports.Sheets("YTD Interest").Cells(j, 4).Value = deadStrategies(k) Then
ytd = ytd + exports.Sheets("YTD Interest").Cells(j, 7).Value
ytd = ytd + exports.Sheets("YTD Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'write to output
If bondInt <> 0 Or repoInt <> 0 Or pl <> 0 Or mtd <> 0 Or ytd <> 0 Then
output.Activate
Rows(i).Insert
Cells(i, 2).Value = Cells(i - 1, 2).Value
Cells(i, 3).Value = deadStrategies(k)
Cells(i, 6).Value = "Closed P&L"
Cells(i, 14).Value = bondInt
Cells(i, 15).Value = repoInt
Cells(i, 16).Value = pl
Cells(i, 17).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value
Cells(i, 18).Value = mtd
Cells(i, 19).Value = ytd
i = i + 1
End If
bondInt = 0
repoInt = 0
pl = 0
mtd = 0
ytd = 0
Next k
Set liveStrategies = New Collection
liveStrategies.Add strategy
End If
'reset P&L
bondInt = 0
repoInt = 0
pl = 0
mtd = 0
ytd = 0
liveBondInt = 0
liveRepoInt = 0
livePL = 0
liveMTD = 0
liveYTD = 0
standardStrategy = Cells(i, 2).Value
End If
'add current live P&L
If Cells(i, 10).Value <> 0 Or IsNumeric(Cells(i, 11).Value) Then
If Cells(i, 10).Value = 0 And IsNumeric(Cells(i, 11).Value) Then
If Cells(i, 11).Value <> 0 Or Cells(i, 16).Value <> 0 Then
liveBondInt = liveBondInt + Cells(i, 14).Value
liveRepoInt = liveRepoInt + Cells(i, 15).Value
livePL = livePL + Cells(i, 16).Value
liveMTD = liveMTD + Cells(i, 18).Value
liveYTD = liveYTD + Cells(i, 19).Value
End If
Else
liveBondInt = liveBondInt + Cells(i, 14).Value
liveRepoInt = liveRepoInt + Cells(i, 15).Value
livePL = livePL + Cells(i, 16).Value
liveMTD = liveMTD + Cells(i, 18).Value
liveYTD = liveYTD + Cells(i, 19).Value
End If
End If
i = i + 1
Wend
End Sub
Private Sub buildClosedPL(exports As Workbook, output As Workbook, fund As String)
Dim liveBondInt As Double, liveRepoInt As Double, livePL As Double, liveMTD As Double, liveYTD As Double
Dim bondInt As Double, repoInt As Double, pl As Double, mtd As Double, ytd As Double
Dim strategy As String, standardStrategy As String
Dim i As Variant, j As Variant, k As Variant
Dim liveStrategies As Collection, deadStrategies As Collection, deadStdStrats As Collection
strategy = "NOT A REAL STRATEGY"
standardStrategy = "NOT A REAL STRATEGY"
Set liveStrategies = New Collection
i = 2
'traverse current output
output.Activate
While Cells(i, 1).Value <> "" Or Cells(i - 1, 1).Value <> ""
If Cells(i, 3).Value <> strategy Then 'new strategy
'do closed P&L for previous strategy
If strategy <> "NOT A REAL STRATEGY" And strategy <> "ZZ-MISC" Then
'add up daily bond interest and repo interest
j = 2
While exports.Sheets("Daily Interest").Cells(j, 1).Value <> ""
If exports.Sheets("Daily Interest").Cells(j, 2).Value = fund And exports.Sheets("Daily Interest").Cells(j, 4).Value = strategy Then
bondInt = bondInt + exports.Sheets("Daily Interest").Cells(j, 7).Value
repoInt = repoInt + exports.Sheets("Daily Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'add up daily pl
j = 2
While exports.Sheets("Daily PL").Cells(j, 1).Value <> ""
If exports.Sheets("Daily PL").Cells(j, 2).Value = fund And exports.Sheets("Daily PL").Cells(j, 4).Value = strategy Then
pl = pl + exports.Sheets("Daily PL").Cells(j, 15).Value
End If
j = j + 1
Wend
'add up mtd
j = 2
While exports.Sheets("MTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("MTD PL").Cells(j, 2).Value = fund And exports.Sheets("MTD PL").Cells(j, 4).Value = strategy Then
mtd = mtd + exports.Sheets("MTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("MTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("MTD Interest").Cells(j, 2).Value = fund And exports.Sheets("MTD Interest").Cells(j, 4).Value = strategy Then
mtd = mtd + exports.Sheets("MTD interest").Cells(j, 8).Value
End If
j = j + 1
Wend
'add up ytd
j = 2
While exports.Sheets("YTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("YTD PL").Cells(j, 2).Value = fund And exports.Sheets("YTD PL").Cells(j, 4).Value = strategy Then
ytd = ytd + exports.Sheets("YTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("YTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("YTD Interest").Cells(j, 2).Value = fund And exports.Sheets("YTD Interest").Cells(j, 4).Value = strategy Then
ytd = ytd + exports.Sheets("YTD Interest").Cells(j, 8).Value
End If
j = j + 1
Wend
'write to output
output.Activate
Rows(i).Insert
Cells(i, 2).Value = Cells(i - 1, 2).Value
Cells(i, 3).Value = Cells(i - 1, 3).Value
Cells(i, 6).Value = "Closed P&L"
Cells(i, 14).Value = bondInt - liveBondInt
Cells(i, 15).Value = repoInt - liveRepoInt
Cells(i, 16).Value = pl - livePL
Cells(i, 17).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value
Cells(i, 18).Value = mtd - liveMTD
Cells(i, 19).Value = ytd - liveYTD
i = i + 1
End If
'reset P&L
bondInt = 0
repoInt = 0
pl = 0
mtd = 0
ytd = 0
liveBondInt = 0
liveRepoInt = 0
livePL = 0
liveMTD = 0
liveYTD = 0
strategy = Cells(i, 3).Value
liveStrategies.Add strategy
End If
If Cells(i, 2).Value <> standardStrategy Then 'new standard strategy
'do closed P&L for dead strategies within this standard strategy
If standardStrategy <> "NOT A REAL STRATEGY" And standardStrategy <> "ZZ-MISC" Then
Set deadStrategies = New Collection
'traverse YTD PL to identify dead strategies
j = 2
While exports.Sheets("YTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("YTD PL").Cells(j, 3).Value = standardStrategy And exports.Sheets("YTD PL").Cells(j, 2).Value = fund Then
If Not containsItem(liveStrategies, exports.Sheets("YTD PL").Cells(j, 4).Value) Then
If Not containsItem(deadStrategies, exports.Sheets("YTD PL").Cells(j, 4).Value) Then
deadStrategies.Add exports.Sheets("YTD PL").Cells(j, 4).Value
End If
End If
End If
j = j + 1
Wend
'traverse YTD Interest to identify dead strategies
j = 2
While exports.Sheets("YTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("YTD Interest").Cells(j, 3).Value = standardStrategy And exports.Sheets("YTD Interest").Cells(j, 2).Value = fund Then
If Not containsItem(liveStrategies, exports.Sheets("YTD Interest").Cells(j, 4).Value) Then
If Not containsItem(deadStrategies, exports.Sheets("YTD Interest").Cells(j, 4).Value) Then
deadStrategies.Add exports.Sheets("YTD Interest").Cells(j, 4).Value
End If
End If
End If
j = j + 1
Wend
'for each dead strategy, get closed P&L
For k = 1 To deadStrategies.Count
'add up daily bond interest and repo interest
j = 2
While exports.Sheets("Daily Interest").Cells(j, 1).Value <> ""
If exports.Sheets("Daily Interest").Cells(j, 2).Value = fund And exports.Sheets("Daily Interest").Cells(j, 4).Value = deadStrategies(k) Then
bondInt = bondInt + exports.Sheets("Daily Interest").Cells(j, 7).Value
repoInt = repoInt + exports.Sheets("Daily Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'add up daily pl
j = 2
While exports.Sheets("Daily PL").Cells(j, 1).Value <> ""
If exports.Sheets("Daily PL").Cells(j, 2).Value = fund And exports.Sheets("Daily PL").Cells(j, 4).Value = deadStrategies(k) Then
pl = pl + exports.Sheets("Daily PL").Cells(j, 15).Value
End If
j = j + 1
Wend
'add up mtd
j = 2
While exports.Sheets("MTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("MTD PL").Cells(j, 2).Value = fund And exports.Sheets("MTD PL").Cells(j, 4).Value = deadStrategies(k) Then
mtd = mtd + exports.Sheets("MTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("MTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("MTD Interest").Cells(j, 2).Value = fund And exports.Sheets("MTD Interest").Cells(j, 4).Value = deadStrategies(k) Then
mtd = mtd + exports.Sheets("MTD Interest").Cells(j, 7).Value
mtd = mtd + exports.Sheets("MTD Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'add up ytd
j = 2
While exports.Sheets("YTD PL").Cells(j, 1).Value <> ""
If exports.Sheets("YTD PL").Cells(j, 2).Value = fund And exports.Sheets("YTD PL").Cells(j, 4).Value = deadStrategies(k) Then
ytd = ytd + exports.Sheets("YTD PL").Cells(j, 15).Value
End If
j = j + 1
Wend
j = 2
While exports.Sheets("YTD Interest").Cells(j, 1).Value <> ""
If exports.Sheets("YTD Interest").Cells(j, 2).Value = fund And exports.Sheets("YTD Interest").Cells(j, 4).Value = deadStrategies(k) Then
ytd = ytd + exports.Sheets("YTD Interest").Cells(j, 7).Value
ytd = ytd + exports.Sheets("YTD Interest").Cells(j, 6).Value
End If
j = j + 1
Wend
'write to output
If bondInt <> 0 Or repoInt <> 0 Or pl <> 0 Or mtd <> 0 Or ytd <> 0 Then
output.Activate
Rows(i).Insert
Cells(i, 2).Value = Cells(i - 1, 2).Value
Cells(i, 3).Value = deadStrategies(k)
Cells(i, 6).Value = "Closed P&L"
Cells(i, 14).Value = bondInt
Cells(i, 15).Value = repoInt
Cells(i, 16).Value = pl
Cells(i, 17).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value
Cells(i, 18).Value = mtd
Cells(i, 19).Value = ytd
i = i + 1
End If
bondInt = 0
repoInt = 0
pl = 0
mtd = 0
ytd = 0
Next k
Set liveStrategies = New Collection
liveStrategies.Add strategy
End If
'reset P&L
bondInt = 0
repoInt = 0
pl = 0
mtd = 0
ytd = 0
liveBondInt = 0
liveRepoInt = 0
livePL = 0
liveMTD = 0
liveYTD = 0
standardStrategy = Cells(i, 2).Value
End If
'add current live P&L
If Cells(i, 10).Value <> 0 Or IsNumeric(Cells(i, 11).Value) Then
If Cells(i, 10).Value = 0 And IsNumeric(Cells(i, 11).Value) Then
If Cells(i, 11).Value <> 0 Or Cells(i, 16).Value <> 0 Then
liveBondInt = liveBondInt + Cells(i, 14).Value
liveRepoInt = liveRepoInt + Cells(i, 15).Value
livePL = livePL + Cells(i, 16).Value
liveMTD = liveMTD + Cells(i, 18).Value
liveYTD = liveYTD + Cells(i, 19).Value
End If
Else
liveBondInt = liveBondInt + Cells(i, 14).Value
liveRepoInt = liveRepoInt + Cells(i, 15).Value
livePL = livePL + Cells(i, 16).Value
liveMTD = liveMTD + Cells(i, 18).Value
liveYTD = liveYTD + Cells(i, 19).Value
End If
End If
i = i + 1
Wend
End Sub