1 Attachment(s)
Need a help with loading data using class module (Derivative & Portfolio)
Hi VBA Experts,
I thought I added a new module and added a function then set, and created the class using the defined function correctly.
But, it does not allow me to upload the data in the yellow highlighted area in the attached file.: pray2::banghead:
Could you please tell me what I am missing to display the table regarding COB, Value, and Returns for each instruments using VBA?
Also, please be advised that I presented how the tables should look like starting in column AC using the excel function, and I put the range reference as Output_Cash, Output_Bond, Output_Stock, Output_Option, Output_CDS, and Output_Portfolio where I would like to load the data.
Class module for Portfolio
Code:
Option Explicit
Private pDerivatives As Collection
Public Property Get Derivatives() As Collection
Set Derivatives = pDerivatives
End Property
Public Property Set Derivatives(lDerivatives As Collection)
Set pDerivatives = lDerivatives
End Property
Function GetData(strInstrumentType As String) As Variant
'10 Dates, Date, Value, Returns
Dim Mat(1 To 10, 1 To 3) As Variant
Dim i As Integer
i = 1
Dim Der As Derivative
'Load the array with values and Dates for that instrument Type
For Each Der In Derivatives
If strInstrumentType = Der.InstrumentType Then
Mat(i, 1) = Der.COB
Mat(i, 2) = Der.Value
i = i + 1
End If
Next Der
'Returns
For i = 2 To 11
Mat(i, 3) = (Mat(i, 2) / Mat(i - 1, 2)) - 1
Next i
GetData = Mat
End Function
Class module for Derivative
Code:
Option Explicit
Private pValue As Double
Private pInstrumentType As String
Private pCOB As Date
Public Property Get Value() As Double
Value = pValue
End Property
Public Property Let Value(lValue As Double)
pValue = lValue
End Property
Public Property Get InstrumentType() As String
InstrumentType = pInstrumentType
End Property
Public Property Let InstrumentType(lInstrumentType As String)
pInstrumentType = lInstrumentType
End Property
Public Property Get COB() As Double
COB = pCOB
End Property
Public Property Let COB(lCOB As Double)
pCOB = lCOB
End Property
:help
Regular Module
Code:
Option Explicit
Function PopulateArray() As Collection
Dim cDers As Collection
Dim Der As Derivative
Set cDers = New Collection
Dim i As Integer
'Loads in all the instruments and Value from Spreadsheet
For i = 2 To 51
Set Der = New Derivative
Der.InstrumentType = Sheets("VaR").Cells(i, 1)
Der.COB = Sheets("VaR").Cells(i, 2)
Der.Value = Sheets("VaR").Cells(i, 3) ' Let function
cDers.Add Der
Next i
Set PopulateArray = cDers
End Function
Sub TestGetPortfolioValue()
Dim newPort As Portfolio
Set newPort = New Portfolio
Set newPort.Derivatives = PopulateArray()
Dim i As Integer, j As Integer
Dim Mat As Variant
Mat = newPort.GetData
'---------------------------------------------------
'load output_Cash range with matrix from Getdata() Function
For i = 1 To 10
For j = 1 To 3
Range("Output_Cash").Cells(i, j) = Mat(i, j)
Next j
Next i
'---------------------------------------------------
'load output_Stock range with matrix from Getdata() Function
For i = 1 To 10
For j = 1 To 3
Range("Output_Stock").Cells(i, j) = Mat(i, j)
Next j
Next i
'---------------------------------------------------
'load output_Bond range with matrix from Getdata() Function
For i = 1 To 10
For j = 1 To 3
Range("Output_bond").Cells(i, j) = Mat(i, j)
Next j
Next i
'---------------------------------------------------
'load output_Option range with matrix from Getdata() Function
For i = 1 To 10
For j = 1 To 3
Range("Output_Option").Cells(i, j) = Mat(i, j)
Next j
Next i
'---------------------------------------------------
'load output_CDS range with matrix from Getdata() Function
For i = 1 To 10
For j = 1 To 3
Range("Output_CDS").Cells(i, j) = Mat(i, j)
Next j
Next i
'---------------------------------------------------
'load output_Portfolio range with matrix from Getdata() Function
For i = 1 To 10
For j = 1 To 3
Range("Output_Portfolio").Cells(i, j) = Mat(i, j)
Next j
Next i
End Sub
I do not fully understand the concept of the class.
I would really appreciate if you could correct my code.
Regards,
Demian
