demian
10-05-2017, 02:36 PM
Hi VBA Experts!
I coded to extract and load tables for each instrument ( Cash, Stock, Bond, Option, CDS) showing its value as well as the total portfolio value for the same date.(highlighted in Blue)
Now, I need to make 5 different scenario tables for each instrument, filling the yellow highlighted areas as in the below screen shot for each Scenario.
For example, Scenario 1 is the combination of Stock, Bond, Option and CDS (but, No Cash), which equals to the total Total Portfolio minus cash.
20584
I want to use the remove method in Collection and Select, Case statement as below. But, the remove statement in the Sub TestGetPortfolioValue() below doesn't work:rotlaugh:
Please share with me if there is better way to do this.
(I put the range reference as Sceario1, Scenario2, Scenario3, Scenario4, Scenario 5 where I would like load the data in a spreadsheet.)
.
Select Case
Case is newPort.Der.Remove "cash"
For i = 1 To 10
Range("scenario1").Cells(i, 1) = newPort.GetTotal(Range("Scenario1").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Stock"
For i = 1 To 10
Range("scenario2").Cells(i, 1) = newPort.GetTotal(Range("Scenario2").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Bond"
For i = 1 To 10
Range("scenario3").Cells(i, 1) = newPort.GetTotal(Range("Scenario3").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Option"
For i = 1 To 10
Range("scenario4").Cells(i, 1) = newPort.GetTotal(Range("Scenario4").Cells(i, 1))
Next i
Case is newPort.Der.Remove "CDS"
For i = 1 To 10
Range("scenario5").Cells(i, 1) = newPort.GetTotal(Range("Scenario5").Cells(i, 1))
Next i
End Select
Anyone of you can enlighten me?
Any help is greatly appreciated.
Regards,
Demian
I coded to extract and load tables for each instrument ( Cash, Stock, Bond, Option, CDS) showing its value as well as the total portfolio value for the same date.(highlighted in Blue)
Now, I need to make 5 different scenario tables for each instrument, filling the yellow highlighted areas as in the below screen shot for each Scenario.
For example, Scenario 1 is the combination of Stock, Bond, Option and CDS (but, No Cash), which equals to the total Total Portfolio minus cash.
20584
I want to use the remove method in Collection and Select, Case statement as below. But, the remove statement in the Sub TestGetPortfolioValue() below doesn't work:rotlaugh:
Please share with me if there is better way to do this.
(I put the range reference as Sceario1, Scenario2, Scenario3, Scenario4, Scenario 5 where I would like load the data in a spreadsheet.)
.
Select Case
Case is newPort.Der.Remove "cash"
For i = 1 To 10
Range("scenario1").Cells(i, 1) = newPort.GetTotal(Range("Scenario1").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Stock"
For i = 1 To 10
Range("scenario2").Cells(i, 1) = newPort.GetTotal(Range("Scenario2").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Bond"
For i = 1 To 10
Range("scenario3").Cells(i, 1) = newPort.GetTotal(Range("Scenario3").Cells(i, 1))
Next i
Case is newPort.Der.Remove "Option"
For i = 1 To 10
Range("scenario4").Cells(i, 1) = newPort.GetTotal(Range("Scenario4").Cells(i, 1))
Next i
Case is newPort.Der.Remove "CDS"
For i = 1 To 10
Range("scenario5").Cells(i, 1) = newPort.GetTotal(Range("Scenario5").Cells(i, 1))
Next i
End Select
Anyone of you can enlighten me?
Any help is greatly appreciated.
Regards,
Demian