What Does this code do?

[InitialD]

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

[Blade Hunter]

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


If bondInt <> 0 Or repoInt <> 0 Or pl <> 0 Or mtd <> 0 Or ytd <> 0 then it put's Closed P&L in Column 6

[InitialD]

What about the part that says "strategies" and "dead strategies"

'do closed P&L for previous strategy
If strategy <> "NOT A REAL STRATEGY" And strategy <> "ZZ-MISC" Then

what is it doing when they are talking about the strategy part?

[Blade Hunter]

That is where it populates the following:
bondInt
repoInt
pl
mtd
ytd

Post population if they are 0 then it writes it out.

The IF is standardStrategy <> "NOT A REAL STRATEGY" And standardStrategy <> "ZZ-MISC"

So if standardStrategy does NOT = "NOT A REAL STRATEGY" AND it's also NOT "ZZ-MISC" then it does the calculation, basically it is checking that it IS a real strategy before calculating the variables for you.

[InitialD]

Then why is it repeating the same process when it's doing the "dead strategy" part?


'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

[Blade Hunter]

In short, I don't know. Without having your data and your understanding of your business I can't guess at this.

Is the code exactly the same in both instances or are there minor differences?

Cheers

Dan

[InitialD]

Yeah... I don't have the data with me, but the code is suppose to extract data from somewhere else.

We only use the daily, mtd, and ytd.

The label "closed price" is in Cells(i, 6).Value... it is only the label.
But I want to know where or how they calculate the "close price".

I remember the value for the daily is always 0, but theres always a number value for the mtd and ytd.
So I want to know how they get that value.

[Frosty]

Couple things will help you here:

1. Stepping through the code
2. Viewing your "Locals" window as well as your "Watch" window (you'll need to set watches... just select a chunk of text, right-click and choose Add Watch)
3. Testing bits of logic in the immediate window by doing ?daily or something...

You can step through code, hover over variables, examine the Locals Window, set watches to help you analyze what's going on in a given code chunk.

It takes a little getting used to-- but it's a lot easier than throwing a wall of text at even experienced coders trying to guess what might be going on without all of the parts available.

My guess from a cursory glance (based on the fact that most of this code is just grabbing the value of a particular cell) is that there is some "coding" being done in your data sheet (i.e., formulas) which this vba is simply utilizing the results of. So not all of the calculations are being done in the code, and thus it's kind of tough to tell you what's actually going on. The "real" work is probably being done in a formula in the cells, while the VBA is really just being used to leverage output which has been validated on the spreadsheet.