1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247
| '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.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 Variant, End_Date As Variant, Total_Days As Variant, Total_Fixings As Variant
Dim Strike As Variant, Barrier_DI As Variant, Barrier_UO As Variant
Dim Vol As Variant, Exp_Return As Variant
Dim i As Integer, j As Variant, Fixings As Integer, Guarantee As Integer
Dim cCount As Variant
'qualibrage de la simulation (longueur)
Set Start_Date = wsRC_Simple.Cells(19, 1)
Set End_Date = wsRC_Simple.Cells(19, 2)
Set 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
'parametres pour le tirage aleatoire
Set Vol = wsRC_Simple.Cells(12, 2)
Set Exp_Return = wsRC_Simple.Cells(12, 3)
Total_Days = networkdays(Start_Date, End_Date)
Fixings = Total_Days / Total_Fixings
Guarantee = wsRC_Simple.Cells(19, 4)
'debut de la simulation avec spot = 100
wsOutput.Cells(1, 2) = 100 * wsRC_Simple.Cells(16, 1)
'n'affiche pas les calculs a l'ecran
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To Total_Days
'iterations dans la colonne 1
'tirage au sort au sein d'une loin lognormale
'pointer pour les dates de fixing
wsOutput.Cells(i + 1, 1) = i
wsOutput.Cells(i + 1, 2) = "=" & wsOutput.Cells(i, 2).Address & "*lognorm2sim(" & Exp_Return & "," & Vol & ",""0.004"")" 'rajouter tirage logreturns + parameters
For j = 1 To Fixings
If i = (j + Guarantee) * Fixings Then 'takes into account if there is a guarantee
Set Spot_Current = wsOutput.Cells(i + 1, 2) 'get the current spot level
wsOutput.Cells(i + 1, 3) = "=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
Next
'on verifie que l'on ait bien tous les fixings
'le cas echeant on rajoute le dernier ici
rStart = wsOutput.Cells(Rows.Count, "C").End(xlUp).Address 'derniere evaluation Spot vs Strike de la colonne C
rEnd = wsOutput.Cells(Rows.Count, "C").End(xlUp).Offset(Fixings, 0).Address 'derniere cellule de B, offset de 1 (colonne C
cCount = Evaluate("SUMPRODUCT((Len(" & rStart & ":" & rEnd & ") = 1) * 1)")
If cCount = 1 Then
wsOutput.Cells(Rows.Count, "B").End(xlUp).Offset(0, 1) = "=RC_Evaluation(" & wsOutput.Cells(Rows.Count, "B").End(xlUp).Address & "," & Strike & "," & Barrier_DI & "," & Barrier_UO & ")"
End If
'copie le spot et Spot vs Strike en colonne E et F
Call Strategy_Recap
'spot < au strike a une date de fixing
wsOutput.Cells(1, 8) = "Output 1"
wsOutput.Cells(1, 9) = "=IF(COUNTIF(C:C,0),0,1)+ outputv()"
'defines the name of the output
wsOutput.Names.Add Name:="RC_No_Memory", RefersToR1C1:="=Output!R1C9"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'dont forget to empty the variables that were SET
End Sub
'should be a copy of RC_Simple taking into account 2 to 3 stocks
'should take into consideration the Correlation between stocks
Sub RC_Multi()
End Sub
Sub Accu()
Call Constants
Call Cleanup
Dim Start_Date As Variant, End_Date As Variant, Total_Days As Variant, Total_Fixings As Variant
Dim Strike As Variant, Barrier_DI As Variant, Barrier_UO As Variant, Leverage As Variant, Product As Variant
Dim Vol As Variant, Exp_Return As Variant
Dim i As Integer, j As Variant, Fixings As Integer, Guarantee As Integer
Dim cCount As Variant
'qualibrage de la simulation (longueur)
Set Start_Date = wsAccu_Simple.Cells(19, 1)
Set End_Date = wsAccu_Simple.Cells(19, 2)
Set Total_Fixings = wsAccu_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 = wsAccu_Simple.Cells(16, 2) * 100 'idem
Barrier_DI = wsAccu_Simple.Cells(16, 3) * 100 'idem
Barrier_UO = wsAccu_Simple.Cells(16, 4) * 100 'idem
Leverage = wsAccu_Simple.Cells(16, 5) * 100
'parametres pour le tirage aleatoire
Set Vol = wsAccu_Simple.Cells(12, 2)
Set Exp_Return = wsAccu_Simple.Cells(12, 3)
Total_Days = networkdays(Start_Date, End_Date)
Fixings = Total_Days / Total_Fixings
Guarantee = wsAccu_Simple.Cells(19, 4)
'debut de la simulation avec spot = 100
wsOutput.Cells(1, 2) = 100 * wsAccu_Simple.Cells(16, 1)
'n'affiche pas les calculs a l'ecran
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To Total_Days
'iterations dans la colonne 1
'tirage au sort au sein d'une loin lognormale
'pointer pour les dates de fixing
wsOutput.Cells(i + 1, 1) = i
wsOutput.Cells(i + 1, 2) = "=" & wsOutput.Cells(i, 2).Address & "*lognorm2sim(" & Exp_Return & "," & Vol & ",""0.004"")" 'rajouter tirage logreturns + parameters
For j = 1 To Fixings
If i = (j + Guarantee) * Fixings Then 'takes into account if there is a guarantee
Set Spot_Current = wsOutput.Cells(i + 1, 2) 'get the current spot level
wsOutput.Cells(i + 1, 3) = "=Accu_Evaluation(" & Spot_Current.Address & "," & Strike & "," & Barrier_DI & "," & Barrier_UO & "," & Leverage & ", " & Product & ")" 'on utilise Adress ici car a chaque iteration on souhaite recuperer le resultat de la fonction
End If
Next
Next
'on verifie que l'on ait bien tous les fixings
'le cas echeant on rajoute le dernier ici
rStart = wsOutput.Cells(Rows.Count, "C").End(xlUp).Address 'derniere evaluation Spot vs Strike de la colonne C
rEnd = wsOutput.Cells(Rows.Count, "C").End(xlUp).Offset(Fixings, 0).Address 'derniere cellule de B, offset de 1 (colonne C
cCount = Evaluate("SUMPRODUCT((Len(" & rStart & ":" & rEnd & ") = 1) * 1)")
If cCount = 1 Then
wsOutput.Cells(Rows.Count, "B").End(xlUp).Offset(0, 1) = "=RC_Evaluation(" & wsOutput.Cells(Rows.Count, "B").End(xlUp).Address & "," & Strike & "," & Barrier_DI & "," & Barrier_UO & ")"
End If
'copie le spot et Spot vs Strike en colonne E et F
Call Strategy_Recap
'spot < au strike a une date de fixing
wsOutput.Cells(1, 8) = "Output 1"
wsOutput.Cells(1, 9) = "=IF(COUNTIF(C:C,0),0,1)+ outputv()"
'defines the name of the output
wsOutput.Names.Add Name:="RC_No_Memory", RefersToR1C1:="=Output!R1C9"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
'dont forget to empty the variables that were SET
End Sub
'si pour une valeur dans B, il y a une valeur dans c
'copier la valeur (dynamique) de B et C dans E et F l'un au dessous de l'autre
Sub Strategy_Recap()
Dim i As Integer, j As Integer
'retourne le nombre de cellules non vides dans un Range
'cCount = Evaluate("SUMPRODUCT((Len(C1:C65000) > 0) * 1)")
wsOutput.Cells(1, 5) = "Spot on Fixing"
wsOutput.Cells(1, 6) = "Spot vs Strike"
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 C pour une valeur de B on retourne l'adresse de B et C dans E et F
For i = 1 To wsOutput.Cells(Rows.Count, "B").End(xlUp).Row
If Not IsEmpty(wsOutput.Cells(i, 3)) 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, 5) = "=" & wsOutput.Cells(i, 2).Address & ""
wsOutput.Cells(j, 6) = "=" & wsOutput.Cells(i, 3).Address & ""
j = j + 1
End If
Next
End Sub
Sub Cleanup()
Worksheets("Output").Activate
Cells.Select
Selection.Delete Shift:=xlUp
End Sub |
Partager