Skewness: the skewness of a distribution

'=========================================
'   Function:       Calculates the skewness of the distribution for the given sample (left skew if < 0, right skew if > 0)
'
'   Input:          var_Sample = data array
'
'   Requires:       Option Base 1
'                   VBA-F: ArithmeticAverage
'                   VBA-F: Variance
'
'   Algorithm:      -
'
'   Author:         Christopher Scherer
'   Date:           12/16/2012
'
'   Changes:        -
'========================================='
Function Skewness(ByVal var_Sample As Variant) As Double
Dim lng_Observations As Single
Dim lng_idxobs As Single
Dim dbl_SampleMean As Double
Dim dbl_TempSum As Double
Dim dbl_StDev As Double

'Determine the number of observations (option base 1 needs to be declared)
lng_Observations = UBound(var_Sample)
dbl_SampleMean = ArithmeticAverage(var_Sample)
dbl_StDev = Variance(var_Sample) ^ 0.5
'Calculate the
For lng_idxobs = 1 To lng_Observations
dbl_TempSum = dbl_TempSum + (var_Sample(lng_idxobs) - dbl_SampleMean) ^ 3
Next lng_idxobs
'Calculate the skewness
Skewness = dbl_TempSum / (dbl_StDev ^ 3 * lng_Observations)
End Function

17. Dezember 2012 von QuantAdmin
Kategorien: Descriptive Statistics | Schreibe einen Kommentar

ArithmeticAverage: Calculate the arithmetic average for a data set

'=========================================
'   Function:       Calculates the arithmetic average for the given sample
'
'   Input:          var_Sample = data array
'
'   Algorithm:      -
'
'   Author:         Christopher Scherer
'   Date:           12/16/2012
'
'   Changes:        -
'========================================='
Function ArithmeticAverage(ByVal var_Sample As Variant) As Double
Dim lng_idxobs As Long
Dim lng_Observations As Long
Dim dbl_SumObservations As Double

lng_Observations = UBound(var_Sample)
'Calculate the sum of all observations
For lng_idxobs = 1 To lng_Observations
dbl_SumObservations = dbl_SumObservations + var_Sample(lng_idxobs)
Next lng_idxobs
'Calulate the average
ArithmeticAverage = dbl_SumObservations / lng_Observations
End Function

17. Dezember 2012 von QuantAdmin
Kategorien: Descriptive Statistics | Schreibe einen Kommentar

Variance: Calculate the variance for a data set

Simple function to calculate the variance:

'=========================================
'   Function:       Calculates the variance for the given sample
'
'   Input:          var_Sample = data array
'
'   Requires:       VBA-F: ArithmeticAverage
'
'   Algorithm:      -
'
'   Author:         Christopher Scherer
'   Date:           12/16/2012
'
'   Changes:        -
'========================================='
Function Variance(ByVal var_Sample As Variant) As Double
Dim lng_idxobs As Single
Dim lng_Observations As Single
Dim dbl_SumDifference As Single
Dim dbl_Mean As Double

lng_Observations = UBound(var_Sample)
dbl_Mean = ArithmeticAverage(var_Sample)
'Calculate the sum of all observations
For lng_idxobs = 1 To lng_Observations
dbl_SumDifference = dbl_SumDifference + (var_Sample(lng_idxobs) - dbl_Mean) ^ 2
Next lng_idxobs
'Calulate the average
Variance = dbl_SumDifference / lng_Observations
End Function

17. Dezember 2012 von QuantAdmin
Kategorien: Descriptive Statistics | Schreibe einen Kommentar

MinMax: Returns the minimum or maximum of two values

</pre>
'=========================================
'   Function:     Returns the minimum or maximum of two values
'   Input:            dbl_value1 = Value 1
'                            dbl_value1 = Value 2
'                            bool_minimum = True for Minimum, False for Maximum
'
'   Author:         Christopher Scherer
'   Date:               11/13/2011
'
'   Changes:       -
'=========================================
Function MinMax(ByVal dbl_value1 As Double, ByVal dbl_value2 As Double, ByVal bool_minimum As Boolean) As Double

If bool_minimum = True Then

If dbl_value1 > dbl_value2 Then

MinMax = dbl_value2

Else

MinMax = dbl_value1

End If

ElseIf bool_minimum = False Then

If dbl_value1 < dbl_value2 Then

MinMax = dbl_value2

Else

MinMax = dbl_value1

End If

End If

End Function
<pre>

09. August 2012 von QuantAdmin
Kategorien: Numerical Methods | Schreibe einen Kommentar

Cholesky_Approximation: Cholesky approximation for a 2×2 matrix


'=========================================
'   Function:            Perform a Cholesky decomposition for the special case of an array with 2 dimensions
'
'   Input:               var_matrix = array to decomposite
'
'   Algorithm:           Cholesky decomposition calculation
'
'   Author:              Christopher Scherer
'   Date:                07/05/2011
'
'   Changes:             -
'=========================================
Function Cholesky_Approximation(ByVal var_matrix As Variant) As Variant

Dim dbl_correlation As Double
Dim dbl_stdev1 As Double
Dim dbl_stdev2 As Double
Dim var_cam(2, 2) As Variant

'Calculating the standard deviations and the correlation based on the values of the variance-covariance matrix var_matrix
dbl_stdev1 = var_matrix(1, 1) ^ 0.5
dbl_stdev2 = var_matrix(2, 2) ^ 0.5
dbl_correlation = var_matrix(2, 1) / (dbl_stdev1 * dbl_stdev2)

'Fill the array var_cam to return it as the function value
var_cam(0, 0) = dbl_stdev1
var_cam(0, 1) = 0
var_cam(1, 0) = dbl_correlation * dbl_stdev2
var_cam(1, 1) = (1 - dbl_correlation ^ 2 * dbl_stdev2) ^ 0.5

Cholesky_Approximation = var_cam

End Function

12. Juli 2012 von QuantAdmin
Kategorien: Numerical Methods | Schreibe einen Kommentar

Prime_Test: Test integer if it’s a prime

Code to test if a given integer is a prime.

'=========================================
'   Function:            Tests if a given integer is a prime
'
'   Input:               int_x = integer to test
'
'   Algorithm:           -
'
'   Author:              Christopher Scherer
'   Date:                07/08/2011
'
'   Changes:             -
'=========================================
Function Prime_Test(ByVal int_x As Integer) As Boolean

Dim int_index As Integer

int_index = 2

Do While int_x / int_index <> Int(int_x / int_index)

     int_index = int_index + 1

Loop

If int_index = int_x Then

     Prime_Test = True

Else

     Prime_Test = False

End If

End Function

12. Juli 2012 von QuantAdmin
Kategorien: Numerical Methods | Schreibe einen Kommentar

NDRV: Normally distributed random variable (Box-Muller)

Code to create a normally distributed random variable based on the Box-Muller algorithm


'=========================================
'   Function:       Return a normally distributed random variable
'
'   Input:          dbl_my = expected value (optional)
'                   dbl_sigma = standard deviation (optional)
'
'   Algorithm:      Box-Muller
'
'   Constants:      PI = 3.14159265358979
'
'   Author:         Christopher Scherer
'   Date:           06/29/2011
'
'   Changes:        -
'=========================================
Function NDRV(Optional ByVal dbl_my As Double = 0, Optional ByVal dbl_sigma As Double = 1) As Double

Dim dbl_nrand As Double

dbl_nrand = Cos(2 * PI * Rnd()) * Sqr(-2 * Log(Rnd()))

NDRV = dbl_my + dbl_sigma * dbl_nrand

End Function

12. Juli 2012 von QuantAdmin
Kategorien: Numerical Methods | Schreibe einen Kommentar

Brownian_Motion: Brownian Motion

Code for a brownian motion:

'=========================================
'   Function:               Return a value based on a brownian motion
'
'   Input:                  dbl_start = start value
'                           dbl_time1 = time 1
'                           dbl_time2 = time 2 (both together form the discrete steps of the process)
'
'   Algorithm:              -
'
'   Author:                 Christopher Scherer
'   Date:                   09/04/2011
'
'   Changes:                09/10/2011: Reference changed from ByRef to ByVal
'=========================================
Function Brownian_Motion(ByVal dbl_start as Double, ByVal dbl_time1 As Double, ByVal dbl_time2 As Double)

Dim dbl_rand As Double
Dim dbl_stepwidth As Double

dbl_stepwidth = dbl_time2 - dbl_time1
dbl_rand = NRand(0, dbl_stepwidth)

Brownian_Motion = dbl_start + dbl_rand

End Function

12. Juli 2012 von QuantAdmin
Kategorien: Numerical Methods, Stochastic Processes | Schreibe einen Kommentar

Longstaff-Schwartz Algorithm for American Swaptions

This is the model I used for my master thesis at the University of Augsburg.

Based on the CIR-Model, short rate paths are generated and the underlying swap is valued. The value of the swaption is determined by regressing the conditional value of exercise over the relevant paths and determining the best exercise strategy.

The file shows step for step the spreadsheet valuation and compares it to the value determined by the VBA routine.

Please note: the theoretical framework of the underlying paper classified this swaption as American, it actually is just a Bermudan Swaption since it doesn’t allow for accrued interest compounding between the settlements.

LSM Paper Example

09. Juli 2012 von QuantAdmin
Kategorien: Interest Rate Derivatives, Monte Carlo, Term Structure Models | Schreibe einen Kommentar

Cox Ingersoll Ross model of the short rate

Manipulate the CIR parameters and view the effect on yield curve, zero bond discount factors and the stochastic short rate process.

CIR-Model

09. Juli 2012 von QuantAdmin
Kategorien: Term Structure Models | Schreibe einen Kommentar

← Ältere Artikel