'cree les constantes
Public wbBook As Workbook
Public wsRC_Simple As Worksheet
Public wsAccu_Simple As Worksheet
Public wsOutput As Worksheet
'active les constantes
Sub Constants()
Set wbBook = Workbooks("Backtesting_Hoadley_200806_Master.xls")
Set wsRC_Simple = Worksheets("Input_RC_Simple")
Set wsAccu_Simple = Worksheets("Input_Accumulator")
Set wsOutput = Worksheets("Output")
End Sub
'Mouvement Brownien du sous jacent (1 seule action)
'payoff
Sub RC_simple()
Call Constants
Call Cleanup
Dim Start_Date As Double, End_Date As Double, Total_Days As Double, Total_Fixings As Double
Dim Strike As Double, Barrier_DI As Double, Barrier_UO As Double, Guarantee As Integer
Dim Vol As Double, Exp_Return As Double
Dim Start_Price As Double, Iterations As Double
Dim Prices() As Double
Dim objSim As Object
Dim i As Integer, j As Integer, k As Integer, Fixings As Integer
Dim Counter As Double, PctDone As Single 'used for the progress bar
Dim cCount As Double
Dim aFrequencies()
'qualibrage de la simulation (longueur)
Start_Date = wsRC_Simple.Cells(19, 1)
End_Date = wsRC_Simple.Cells(19, 2)
Total_Fixings = wsRC_Simple.Cells(19, 3)
'parametrage produit (on n'utilise pas le SET ici car on initialise ET on calcule)
'on multiplie par 100 car la fonction ne prends pas les %
Strike = wsRC_Simple.Cells(16, 2) * 100 'idem
Barrier_DI = wsRC_Simple.Cells(16, 3) * 100 'idem
Barrier_UO = wsRC_Simple.Cells(16, 4) * 100 'idem
Guarantee = wsRC_Simple.Cells(19, 4)
'parametres pour le tirage aleatoire
Vol = wsRC_Simple.Cells(12, 2)
Exp_Return = wsRC_Simple.Cells(12, 3)
Total_Days = networkdays(Start_Date, End_Date)
Fixings = Total_Days / Total_Fixings
Iterations = wsRC_Simple.Cells(12, 5)
'n'affiche pas les calculs a l'ecran
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set objSim = CreateSimulationObject
ReDim Prices(Total_Days + 1) 'on rajoute + 1 au total_days car la boucle du dessous commence a 2
Start_Price = wsRC_Simple.Cells(16, 1) * 100
Prices(1) = Start_Price
Counter = 1 'utilise pour afficher la barre de progression
'le tableau de frequence doit etre initialise AVANT de lancer la simulation
ReDim aFrequencies(1 To 2, Total_Fixings) 'tableau multi dimensions
For k = 1 To Total_Fixings
aFrequencies(1, k) = 0 'tableau regroupant les frequences de paiement de coupon
aFrequencies(2, k) = 0 'tableau regroupant les frequences de call du produit
Next k
'la suite du tableau est geree dans GGGGGGGG
'demarrage de la simulation, c'est parti pour la grande boucle!
For i = 1 To Iterations
For j = 1 To Total_Days
'iterations dans la colonne 1
'tirage au sort au sein d'une loin lognormale
'pointer pour les dates de fixing
Prices(j + 1) = objSim.NextPrice(Prices(j), Vol, Exp_Return, 1, 0)
wsOutput.Cells(j, 1) = Prices(j + 1)
Next j
For k = Total_Days To 1 Step -Fixings
If Guarantee <> 0 And k - 1 <= Guarantee * Fixings Or k = 1 Then
wsOutput.Cells(k, 2) = ""
Else
Set Spot_Current = wsOutput.Cells(k, 1)
wsOutput.Cells(k, 2) = "=RC_Evaluation(" & Spot_Current.Address & "," & Strike & "," & Barrier_DI & "," & Barrier_UO & ")" 'on utilise Adress ici car a chaque iteration on souhaite recuperer le resultat de la fonction
End If
Next k
Call Strategy_Recap 'copie les fixings (spot et resultat) en colonne E et F
Call Proba_Coupon 'resultat du monte carlo
PctDone = Iterations * 5 / 100 'retourne la progression du processus en %
If i = Counter * PctDone Then
Application.StatusBar = "Simulations completed: " & Format(Counter * 0.05, "##0.00%")
Counter = Counter + 1
End If
Next i
Call Display_Data
Columns("D:M").EntireColumn.AutoFit
Application.StatusBar = ""
Application.ScreenUpdating = True
'Call cRC_Simple
End Sub
j = 2 'on va copier les cellules de B et C dans E et F sous une legende
's 'il y a une valeur dans B pour une valeur de A on retourne l'adresse de B et C dans E et F
For i = 1 To wsOutput.Cells(Rows.Count, "A").End(xlUp).Row
If Not IsEmpty(wsOutput.Cells(i, 2)) Then
'ne fonctionne pas correctement (retourne 2 valeurs dans 1 seule cellule...)
'wsOutput.Cells(j, 3).Offset(, 1).Resize(, 2) = "=" & wsOutput.Cells(i, 3).Offset(, -1).Resize(, 1).Address & ""
wsOutput.Cells(j, 4) = "=" & wsOutput.Cells(i, 1).Address & ""
wsOutput.Cells(j, 5) = "=" & wsOutput.Cells(i, 2).Address & ""
j = j + 1
End If
wsOutput.Cells(1, 4) = "Spot on Fixing"
wsOutput.Cells(1, 5) = "Spot vs Strike"
Next
End Sub
Sub Proba_Coupon()
Dim l As Variant, k As Integer
Dim sProducts As Double
'Dim aFrequencies()
sProducts = wsRC_Simple.Cells(2, 3)
Total_Fixings = wsRC_Simple.Cells(19, 3)
Select Case sProducts 'output different en fonction du type de produit teste
Case Is = 1 'cas d'un RC convertible simple: 1 fixing a maturite
If wsOutput.Cells(2, 5) = 1 Then
wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1
Else
wsOutput.Cells(3, 9) = wsOutput.Cells(3, 9) + 1
End If
Case Is = 2
Last_Cell = wsOutput.Cells(Rows.Count, "E").End(xlUp).Address
k = 1
For Each l In wsOutput.Range("E2:E" & _
wsOutput.Range("E" & Rows.Count).End(xlUp).Row)
If l = 0 And wsOutput.Cells(k + 1, 5).Address = Last_Cell Then 'on est converti en actions
wsOutput.Cells(2, 9) = wsOutput.Cells(2, 9) + 1
Exit For
ElseIf l = 1 Then 'on touche le coupon
wsOutput.Cells(3, 9) = wsOutput.Cells(3, 9) + 1
aFrequencies(1, k) = aFrequencies(1, k) + 1
ElseIf l = 2 Then 'la structure est callee
wsOutput.Cells(4, 9) = wsOutput.Cells(4, 9) + 1
aFrequencies(2, k) = aFrequencies(2, k) + 1
Exit For
End If
k = k + 1
Next l
End Select
End Sub
Sub Display_Data()
Dim sProducts As Double, SumRange As Double
Dim Iterations As Integer
sProducts = wsRC_Simple.Cells(2, 3)
Iterations = wsRC_Simple.Cells(12, 5)
Select Case sProducts
'si le produit un Reverse Convertible
Case Is = 1
wsOutput.Cells(2, 8) = "Coupon Paid"
wsOutput.Cells(3, 8) = "No Coupon Paid"
wsOutput.Cells(1, 9) = "Occurences"
wsOutput.Cells(1, 10) = "Probability"
wsOutput.Cells(2, 10) = wsOutput.Cells(2, 9) / Iterations
wsOutput.Cells(3, 10) = wsOutput.Cells(3, 9) / Iterations
wsOutput.Cells(2, 10).NumberFormat = "#0.00%"
wsOutput.Cells(3, 10).NumberFormat = "#0.00%"
'si le produit est un Range Accrual
Case Is = 2
SumRange = Application.WorksheetFunction.Sum(Range("I2:I4"))
wsOutput.Cells(2, 8) = "Redeemed in shares"
wsOutput.Cells(3, 8) = "Coupon Paid"
wsOutput.Cells(4, 8) = "Called"
wsOutput.Cells(1, 9) = "Occurences"
wsOutput.Cells(1, 10) = "Probability"
wsOutput.Cells(2, 10) = wsOutput.Cells(2, 9) / SumRange
wsOutput.Cells(3, 10) = wsOutput.Cells(3, 9) / SumRange
wsOutput.Cells(4, 10) = wsOutput.Cells(4, 9) / SumRange
wsOutput.Cells(2, 10).NumberFormat = "#0.00%"
wsOutput.Cells(3, 10).NumberFormat = "#0.00%"
wsOutput.Cells(4, 10).NumberFormat = "#0.00%"
End Select
For k = 1 To Total_Fixings
wsOutput.Cells(k + 1, 18) = k
wsOutput.Cells(k, 19) = aFrequencies(1, k)
wsOutput.Cells(k, 20) = aFrequencies(2, k)
Next
End Sub
Sub Cleanup()
Worksheets("Output").Activate
Cells.Select
Selection.Delete Shift:=xlUp
End Sub