PDA

View Full Version : [SOLVED] Need a help with loading data using class module (Derivative & Portfolio)



demian
10-04-2017, 12:00 PM
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

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

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

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

Bob Phillips
10-04-2017, 03:37 PM
I get run time errors

20574

demian
10-04-2017, 04:54 PM
"Compile Error: Argument not optional". What does this mean?
Could you please enlighten me?

I still get the message " Compile Error: Variable not defined" after I change it as below...



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(Cash)


'---------------------------------------------------
'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

Paul_Hossler
10-04-2017, 06:22 PM
The calling procedure for GetData in class Portfolio ...


Function GetData(strInstrumentType As String) As Variant


... is looking for a string in the function call

The first time (in post #2) , you didn't have one and since it was required, it was not optional

The other error (in post #3) is telling you that you tried to use a string variable (called 'Cash') but that it was never defined (like Dim Cash As String)

demian
10-04-2017, 07:55 PM
Thanks for simplifying it for me!
I was able to load the table for each instruments as below, except the Portfolio table.

20579


Dim i As Integer, j As Integer
Dim Mat As Variant
Mat = newPort.GetData("Cash")


'load output 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



Regarding the table for Portfolio, could you please provide me with your advice?
Any suggestions most welcome, I'm open to any approach.

Regards,
Demian