VBA moyenne mobile

Sub MoyenneM_Bis()
Dim dernier&
dernier = Cells(Rows.Count, 2).End(xlUp).Row
With Range("I1:J1")
    .Font.Color = 255: .Interior.Color = 55
    .Value = Array("MM20", "MM50")
End With
With Cells(21, "I").Resize(dernier - 20)
    .FormulaR1C1 = "=AVERAGE(R[-19]C[-7]:RC[-7])"
    .Value = .Value
End With
With Cells(51, "J").Resize(dernier - 50)
    .FormulaR1C1 = "=AVERAGE(R[-49]C[-8]:RC[-8])"
    .Value = .Value
End With
End Sub

Option Base 1

Sub OBPI()
 
Dim rf As Double, S0 As Double, Mu As Double, Sigma As Double, Plancher As Double
Dim Strike As Double, dT As Double, Nb_Points As Double, Simulations As Double
Dim Position_Actions As Double, Position_sansrisque as double, Valorisation_Position As Double, beta As Double, Echeance as double
 
    S0 = 100
    Mu = 0.08
    Sigma = 0.3
 
    rf = 0.03
 
    Plancher = 80
    Strike = Deter_Strike(S0, Plancher, rf, Sigma, 1, Plancher / 100)
 
    Nb_Points = 252
    dT = 1 / Nb_Points
    Simulations = 1000
 
    ReDim Performance(Simulations, 2) As Double
 
    For i = 1 To Simulations
 
        Spot = S0
        Echeance = 1
        beta = Proportion_Actions(Spot, Strike, rf, Sigma, Echeance)
 
        Position_Actions = beta * S0
        Position_sansrisque = (1 - beta) * S0
 
        For j = 1 To Nb_Points
 
            Taux_Rentabilite = Taux_Rentabilite_Action(Mu, Sigma, dT)
            Spot = Spot * Exp(Taux_Rentabilite)
            Valorisation_Position = Position_Actions * Exp(Taux_Rentabilite) + Position_sansrisque * Exp(rf * dT)
            Echeance = WorksheetFunction.Max(Echeance - dT, 1E-08)
            beta = Proportion_Actions(Spot, Strike, rf, Sigma, Echeance)
            Position_Actions = beta * Valorisation_Position
            Position_sansrisque = (1 - beta) * Valorisation_Position
 
        Next j
 
 
        Performance(i, 2) = Valorisation_Position 
        Performance(i, 1) = Spot         
Next i
    Range("A1:B1000").Value = Performance
End Sub
Function Deter_Strike(S, K, R, Sigma, T, proportion_garde)
    epsilon = 1E-15
    Do
        d1 = (Log(S / K) + (R + 0.5 * Sigma ^ 2) * T) / (Sigma * Sqr(T))
        d2 = d1 - Sigma * Sqr(T)
        K = (-proportion_garde * (S + BS_STD("Put", S, K, R, Sigma, T)) + K * proportion_garde * Exp(-R * T) * Nd(-d2)) / _
        (proportion_garde * Exp(-R * T) * Nd(-d2) - 1)
        erreur = proportion_garde * (S + BS_STD("Put", S, K, R, Sigma, T)) - K
    Loop Until Abs(erreur) < epsilon
    Deter_Strike = K
End Function
Function Taux_Rentabilite_Action(Mu, Sigma, dT)
    epsilon = WorksheetFunction.NormSInv(Rnd)
    Taux_Rentabilite_Action = (Mu - Sigma ^ 2 / 2) * dT + Sigma * epsilon * Sqr(dT)
End Function
Function Proportion_Actions(Spot, Strike, rf, Sigma, Echeance)
    d1 = (Log(Spot / Strike) + (rf + Sigma ^ 2 / 2) * Echeance) / (Sigma * Sqr(Echeance))
    d2 = d1 - Sigma * Sqr(Echeance)
    Proportion_Actions = Spot * Nd(d1) / (Spot * Nd(d1) + Strike * Exp(-rf * Echeance) * Nd(-d2))
End Function
Function BS_STD(TypeOption, S, K, R, Sigma, T)
    d1 = (Log(S / K) + (R + 0.5 * Sigma ^ 2) * T) / _
    (Sigma * Sqr(T))
    d2 = d1 - Sigma * Sqr(T)
    Z = Switch(TypeOption)
    BS_STD = Z * (S * Nd(Z * d1) - K * Exp(-R * T) * Nd(Z * d2))
End Function
Function Nd(d)
    Nd = WorksheetFunction.NormSDist(d)
End Function
Function Switch(TypeOption)
    TypeOption = UCase(TypeOption)
    If TypeOption = "C" Or TypeOption = "CALL" Then
        Switch = 1
    Else
        Switch = -1
    End If
End Function