PB de Graphique dynamique: Schéma d'Euler
Bonsoir,
La Sub ci dessous est sensé créer un graf de manière dynamique pourtant si je lance le calcul avec une maturité de 3 ans, et successivement avec une maturité de 1 ans le graphique conserve l'axe des abscisses correspondant à 3 ans de données discrètes.
La Sub est lancée via le bouton CUI MC
Pourtant le code me semble correct:
Le code est identique dans le.xlsm
Sub CUIPRICE()
Dim Barrierefranchise As Integer
GetData
Dim Stinit As Double
Stinit = St
Dim moyenneCUI As Double
Dim unif As Double
moyenneCUI = 0
Dim Stplusdt As Double
Dim i As Long
Dim j As Long
Dim tincr As Double
Dim RNG As Range
Randomize
If t = 0 Then
tincr = 1 / 365
End If
Dim counter As Long
counter = 0
Sheets("Feuil3").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
If St < L Then
For i = 1 To N
counter = counter + 1
Barrierefranchise = 0
For j = partieentiere(tincr * 365) To partieentiere((M * 365))
unif = Rnd
z = WorksheetFunction.Norm_Inv(unif, 0, 1)
Stplusdt = St * (1 + z * sigma * WorksheetFunction.Power((M - t) / 365, 0.5) + (r - div) * ((M - t) / 365))
St = Stplusdt
If Stplusdt > L Then
Barrierefranchise = 1
End If
If counter = 1 Then
Worksheets("Feuil3").Cells(j, 1) = St
End If
Next
If counter = 1 Then
Worksheets("Feuil3").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("Feuil3!A:A")
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Graphique ?chantillon du Sch?ma d'Euler de la m?thode MC "
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Graphique ?chantillon du Sch?ma d'Euler de la m?thode MC"
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.PlotArea.Select
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 233
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.ChartTitle.Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
End With
Selection.Format.Line.Visible = msoFalse
ActiveChart.ChartArea.Select
With Selection.Format.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
ActiveChart.Parent.Cut
Sheets("Feuil1").Select
Range("A27").Select
ActiveSheet.Paste
End If
If Barrierefranchise = 1 Then
moyenneCUI = moyenneCUI + Barrierefranchise * partiepositive(St - K)
End If
St = Stinit
Next
ElseIf St > L Then
moyenneCUI = 0
End If
Worksheets("Feuil1").Cells(17, 4) = Exp(-r * (M - t)) * moyenneCUI / N
End Sub
PB résolu en modifiant le code ainsi:
Sub CUIPRICE()
Dim Barrierefranchise As Integer
GetData
Dim Stinit As Double
Stinit = St
Dim moyenneCUI As Double
Dim unif As Double
moyenneCUI = 0
Dim Stplusdt As Double
Dim i As Long
Dim j As Long
Dim tincr As Double
Dim RNG As Range
Randomize
If t = 0 Then
tincr = 1 / 365
ElseIf t <> 0 Then
tincr = t
End If
Dim counter As Long
counter = 0
Sheets("Feuil3").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
If St < L Then
For i = 1 To N
counter = counter + 1
Barrierefranchise = 0
For j = partieentiere(tincr * 365) To partieentiere((M * 365))
unif = Rnd
z = WorksheetFunction.Norm_Inv(unif, 0, 1)
Stplusdt = St * (1 + z * sigma * WorksheetFunction.Power((1 / 365), 0.5) + (r - div) * (1 / 365))
St = Stplusdt
If Stplusdt > L Then
Barrierefranchise = 1
End If
If counter = 1 Then
Worksheets("Feuil3").Cells(j, 1) = St
End If
Next
If counter = 1 Then
Worksheets("Feuil3").Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("A1:A" & CStr(partieentiere(Worksheets("Feuil1").Cells(4, 6).Value)))
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Graphique ?chantillon du Sch?ma d'Euler de la m?thode MC "
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Graphique ?chantillon du Sch?ma d'Euler de la m?thode MC"
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.PlotArea.Select
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 233
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.ChartTitle.Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(192, 0, 0)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
.Transparency = 0
End With
Selection.Format.Line.Visible = msoFalse
ActiveChart.ChartArea.Select
With Selection.Format.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
ActiveChart.Parent.Cut
Sheets("Feuil1").Select
Range("A27").Select
ActiveSheet.Paste
End If
If Barrierefranchise = 1 Then
moyenneCUI = moyenneCUI + Barrierefranchise * partiepositive(St - K)
End If
St = Stinit
Next
ElseIf St > L Then
moyenneCUI = 0
End If
Worksheets("Feuil1").Cells(17, 4) = Exp(-r * (M - t)) * moyenneCUI / N
End Sub