PDA

View Full Version : Passing one function to another



Kaz
04-04-2016, 02:26 AM
I was wondering if I could get help with the following.

I have created a function that calculates the annualised standard deviation of a timeseries of returns. I am then trying to pass it to another function. The second function takes the difference between two time series of returns and then is supposed to calculate the standard deviation of the differences.

The first function

Function AnnualisedStandardDeviation(Data As Range, DataFrequency As String) As Double


Dim Annualiser As Integer


Select Case DataFrequency

Case "d"
Annualiser = 365
Case "w"
Annualiser = 52
Case "m"
Annualiser = 12
Case "q"
Annualiser = 4
Case "y"
Annualiser = 1

End Select


AnnualisedStandardDeviation = Application.WorksheetFunction.StDev_P(Data) * (Annualiser ^ (1 / 2))
End Function

The second function

Function TrackingError(Data As Range, IndexData As Range, DataFrequency As String) As Double


Dim i, RowNo As Integer
Dim ExcessReturn() As Double



RowNo = Data.Rows.Count


For i = 1 To RowNo
ExcessReturn(i) = Data(i) - IndexData(i)
Next i

Call AnnualisedStandardDeviation(ExcessReturn, DataFrequency)
TrackingError = AnnualisedStandardDeviation(ExcessReturn, DataFrequency)
End Function


Thanks in advance

PAB
04-04-2016, 03:34 AM
Hi Kaz,

Welcome to the board.

Give this a go...


AnnualisedStandardDeviation = Application.WorksheetFunction.StDevP(Data) * (Annualiser ^ (1 / 2))

I hope this helps!

Paul_Hossler
04-04-2016, 05:57 AM
I'm assuming that you mean to use the first function in the second one??

Functions typically return a value that is used, so I don't think you need the Call in the second one

I added a Redim in the second to hold the data, and changed the call to a Variant in the first one

I don't know if the math is correct, but this can get you farther along



BTW, in your original Dim I, RowNo As Integer the 'I' is a Variant since you need to be very wordy when Dim-ing VBA variables make it Din I as Integer



Option Explicit
'The first function
Function AnnualisedStandardDeviation(Data As Variant, DataFrequency As String) As Double

Dim Annualiser As Integer

Select Case DataFrequency
Case "d"
Annualiser = 365
Case "w"
Annualiser = 52
Case "m"
Annualiser = 12
Case "q"
Annualiser = 4
Case "y"
Annualiser = 1
End Select

AnnualisedStandardDeviation = Application.WorksheetFunction.StDev_P(Data) * (Annualiser ^ (1 / 2))

End Function


'The second function
Function TrackingError(Data As Range, IndexData As Range, DataFrequency As String) As Double

Dim i As Integer, RowNo As Integer
Dim ExcessReturn() As Double

RowNo = Data.Rows.Count

ReDim ExcessReturn(1 To RowNo)

For i = 1 To RowNo
ExcessReturn(i) = Data(i) - IndexData(i)
Next i
TrackingError = AnnualisedStandardDeviation(ExcessReturn, DataFrequency)
End Function

Kaz
04-04-2016, 05:18 PM
[QUOTE=Paul_Hossler;340853]I'm assuming that you mean to use the first function in the second one??

Functions typically return a value that is used, so I don't think you need the Call in the second one

I added a Redim in the second to hold the data, and changed the call to a Variant in the first one

I don't know if the math is correct, but this can get you farther along



BTW, in your original Dim I, RowNo As Integer the 'I' is a Variant since you need to be very wordy when Dim-ing VBA variables make it Din I as Integer

[CODE]
Option Explicit
'The first function
Function AnnualisedStandardDeviation(Data As Variant, DataFrequency As String) As Double

Dim Annualiser As Integer

Select Case DataFrequency
Case "d"
Annualiser = 365
Case "w"
Annualiser = 52
Case "m"
Annualiser = 12
Case "q"
Annualiser = 4
Case "y"
Annualiser = 1
End Select

AnnualisedStandardDeviation = Application.WorksheetFunction.StDev_P(Data) * (Annualiser ^ (1 / 2))

End Function


'The second function
Function TrackingError(Data As Range, IndexData As Range, DataFrequency As String) As Double

Dim i As Integer, RowNo As Integer
Dim ExcessReturn() As Double

RowNo = Data.Rows.Count

ReDim ExcessReturn(1 To RowNo)

For i = 1 To RowNo
ExcessReturn(i) = Data(i) - IndexData(i)
Next i
TrackingError = AnnualisedStandardDeviation(ExcessReturn, DataFrequency)
End Function



Hey, thanks so much!