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