Equation différentielle stochastique sous Excel avec VBA

black and scholes

2014-01-30 Equation différentielle stochastique sous Excel avec VBA

C'est un TD que je donnais à l'ENSAE il y a quelques années. Mais le VBA n'a pas trop changé depuis cette époque, tout du moins comparé à d'autres langages. L'équation différentielle stochastique de Black et Scholes apparaît souvent lorsqu'on parle de finance :

$\frac{dY_t}{Y_t} = r dt + \sigma dW_t$

Avec :

: est le prix à l'instant t d'une action
: est le taux d'intérêt sans risque
$\sigma$ : est la volatilité du prix de l'action
t : représente le temps
: un petit intervalle de temps
: est un mouvement brownien
: est une variable aléatoire normale de moyenne nulle et de variance dt

On cherche à résoudre numériquement cette équation avec Excel. C'est un peu le début de l'implémentation d'un pricer sous Excel.

Quelques remarques en préambule, il ne faut pas hésiter à se promener dans l'aide de Microsoft concernant VBA, en appuyant sur la touche F1 par exemple. Internet est également une bonne source, il suffit parfois de recopier un message d'erreur obscur dans Google ou Bing pour obtenir une réponse. Une autre méthode simple consiste à enregistrer sous forme de macro l'action que vous voulez programmer. Microsoft convertit toute macro sous la forme d'un code écrit en VBA dont il est facile de s'inspirer. Je garderai pour cet article la forme d'un TD, c'est-à-dire des questions avec la solution disponible à la fin.

Cette équation différentielle, si on la résoud sans tenir compte de sa partie stochastique donne $Y_t = Y_0 e^{rt}$ . Avec un taux d'intérêt positif, le prix d'une action augmente. Mais cela n'est jamais beau et on est obligé de tenir compte des aléas plus ou moins importants selon la valeur de $\sigma$ . Plus on avance dans le temps, plus la part du hasard est importante et plus la probabilité que le prix de l'action suive la courbe $Y_t = Y_0 e^{rt}$ est faible.

Si on cherche à résoudre l'équation différentielle stochastique de manière numérique entre les temps 0 et T, on procède comme pour une équation différentielle classique. On considère donc que dt est un petit intervalle de temps et on écrit l'équation comme ceci :

$Y_{t+dt} = Y_t \left( 1 + r dt + \sigma dW_t \right)$

Ensuite, à partir de Y_0 qui est connu, on calcule les valeurs $Y_{dt}, Y_{2dt}, Y_{3dt}$ , ..., $Y_{T-dt}, Y_{T}$ . La différence avec une équation différentielle non stochastique est qu'il n'y a plus une seule solution mais une infinité : à chaque itération pour calculer, $Y_{(n+1)dt}$ en fonction de $Y_{ndt}$ , on utilise un dW_t différent pris au hasard.

Ceci signifie que Y_T n'est pas une constante mais une variable aléatoire. Il faut donc calculer un grand nombre de solutions pour obtenir sa moyenne, sa variance, son maximum, son minimum... Et c'est l'objectif de cet article.

1

Pour commencer, il faut lancer l'application Excel accessible depuis le menu Démarrer (ou Start) de Windows. Ensuite, il faut recopier exactement ce qu'il y a dans la figure qui suit. Il est préférable de ne pas se tromper de ligne ou de colonne pour que la suite de l'énoncé soit cohérente avec votre travail. N'oubliez pas de sauver le fichier avec un nom de votre choix. Par la suite, sauvez régulièrement au cas où la mauvaise humeur d'Excel l'amènerait à planter en pleine programmation (ce ne devrait normalement pas vous fort heureusement).

2

La suite va se passer dans une autre fenêtre : celle où l'on écrit les programme en VBA. La figure 2 montre comment ouvrir l'éditeur VBA qui est aussi accessible grâce au raccourci ALT+F11. Il faut ensuite ouvrir une fenêtre ou module permettant de saisir le programme VBA (seconde image de la figure 2).

figure 2a : Création d'un module : fenêtre où va être écrit le programme VBA

figure 2b : Création d'un module : fenêtre où va être écrit le programme VBA

Une nouvelle fenêtre apparaît. Il faut maintenant saisir le programme de la figure 3.

L'instruction Option Explicit indique au langage VBA qu'il doit être plus stricte lors de sa compréhension du programme, cela évite qu'il interprète mal une instruction, il détectera une erreur à la place. Une fois que ce petit programme est tapé, on écrit à l'intérieur de la procédure (entre les instructions Sub et End Sub la ligne :

MsgBox "boîte à message"

Placez ensuite le curseur sur cette ligne et cliquez sur l'icône en forme de triange orienté vers la droite dans la barre d'outils. Une boîte à message doit normalement apparaître à l'écran : le programme est en train de tourner et vous demande de presser le bouton Ok pour conclure.

3

On cherche maintenant à récupérer les valeurs présentes dans la feuille Excel de la question 1. Il y a 5 informations, on doit donc créer 5 variables. On considère que ce sont toutes des variables réelles et pour en déclarer une, on écrit :

    Dim r As Double

Aparté : Le nom des variables, comme celui des fonctions ne peut inclure ni espace, ni accent. Aucune distinction n'est faite entre minuscules et majuscules. Il n'est pas besoin d'être très rigoureux sur les minuscules et majuscules, VBA les corrige lorsqu'elles ne correspondent pas au nom utilisé lors de la déclaration de la variable.

On appelle les 5 variables à récupérer r, sigma, x0, dt, T. Une fois que celle-ci sont créées, on peut leur affecter une valeur en récupérant celles présentes dans la feuille Excel Pour récupérer le contenu d'une case, on utilise :

r = Worksheets("Sheet1").Cells(4, 1).Value

Aparté : Avec l'instruction Option Explicit, il est impossible d'utiliser une variable sans que celle-ci ait été préalablement déclarée.

N'oubliez de déclarer vos variables avant de leur affecter une valeur. Il en sera de même plus tard pour les variables utilisées dans les boucles For. Sheet1 est le nom de la feuille. Si la version d'Excel que vous utilisez est française, ce sera un autre nom comme Feuil1. L'instruction précédente récupère donc la valeur de la case ligne 4, colonne 1 de la feuille Sheet1. On procède de même pour sigma, x0, dt, T.

4

On va maintenant créer une fonction permettant de calculer une solution de l'équation différentielle stochastique de Black et Scholes. Elle prend comme paramètres :

: un réel ou Double en VBA
: un réel ou Double en VBA
: un réel ou...
: un réel ou...
: un entier ou...

Elle retournera un tableau de valeurs correspondant au vecteur $\left(Y_0, Y_{dt}, Y_{2dt}, ..., Y_T\right)$ . Le type de résultat en VBA est un Variant. Pour déclarer la fonction, on utilise la syntaxe suivante :

Function Simulation(ByVal r As Double, ByVal sigma As Double, ByVal x0 As Double, _
            ByVal dt As Double, ByVal T As Long) As Variant
            
    '  code de la fonction            
   
    ' Lorsqu'on connaît le résultat de la fonction, on écrit
    Simulation = résultat
   
End Function

Le blanc souligné \_ à la fin de la première ligne permet d'écrire une instruction sur deux lignes et non sur une seule comme le langage VBA l'impose. Le blanc souligné doit toujours être précédé d'un espace. Le mot-clé ByVal signifie qu'on passe les paramètres par valeur : ils sont recopiés. L'inverse est ByRef qui signifie un passage par adresse : ils ne sont pas recopiés et peuvent être modifiés dans la fonction. Le type du résultat apparaît en fin de ligne. L'apostrophe sert à insérer un commentaire dans le programme. Cette fonction doit être insérée juste après Option Explicit et avant la procédure qui suit et qui devra faire appel à cette fonction.

5

On cherche à calculer une solution de l'équation de Black et Scholes. Cette solution est un tableau qui contient $n = \frac{T}{dt}+1$ valeurs

    Dim n as Long
    n = T / dt + 1

On déclare un tableau de réels dont les indices vont de 0 à n. La première valeur est x0.

    Dim res() As Double
    ReDim res(n)
    res (0) = x0

Pour simuler une variable normale de moyenne nulle et de variance dt, on utilise le code suivant :

    Dim w As Double
    w = Rnd     ' nombre aléatoire de loi uniforme [0,1]
    w = Application.WorksheetFunction.NormSInv(w) * dt ^ 0.5

La fonction NormSInv est une fonction d'Excel, il suffit d'aller voir l'aide pour savoir ce qu'elle fait. D'une manière générale, pour utiliser une fonction dans un programme VBA alors qu'on a l'habitude de se servir dans une feuille de calcul, il faut utiliser le préfixe : Application.WorksheetFunction. Il ne reste plus qu'à savoir comment faire une boucle pour terminer la fonction :

    For i = 1 To nb
       res (i) = res(i-1) * ... à compléter
    Next i

Enfin, lorsque le tableau est complet et qu'il est le résultat de la fonction Simulation, il ne reste plus qu'à ajouter à la fin la ligne :

    Simulation = res

Il ne reste plus qu'à écrire le code complet de cette fonction. Un dernier indice toutefois, intéressez-vous particulièrement à l'emplacement des deux lignes qui génère un nombre aléatoire.

6

On désire maintenant recopier les valeurs d'une solution dans la feuille Excel de départ. La première étape consiste à récupérer le tableau calculé par la fonction de la question précédente :

    Dim solution As Variant
    solution = Simulation(r, sigma, x0, dt, T)

On récupère le nombre d'éléments dans \codes{solution} :

    Dim nb as Long
    nb = UBound (solution)

On recopie les valeurs dans la feuille, celles-ci doivent être réparties sur deux colonnes : le temps t et Y_t :

    For i = 0 To nb
        Worksheets("Sheet1").Cells(7 + i, 1) = ... ' temps t
        Worksheets("Sheet1").Cells(7 + i, 2) = ... ' Y_t
    Next i

Une fois que tout cela est fait, on place le curseur dans la procédure principale et on clique sur le même triangle que tout-à-l'heure. Si tout se passe bien, la résultat apparaît sur la feuille Excel. Sinon, le debugger permet d'afficher le contenu des variables en cours d'exécution.

7

C'est le petit moment de détente avant de retourner à la programmation : on veut associer un bouton de la barre d'outils à la procédure (ou macro) qu'on vient de créer. Un clic droit dans la barre d'outils fait apparaître l'image de la figure 4. On clic sur Customize. La seconde image de la figure 4 apparaît. Il faut s'arranger pour faire apparaître Custom Button. En maintenant le bouton gauche appuyé, on fait glisser le bouton jusqu'à la barre d'outils où il s'insère. Une fois posé, on presse le bouton droit dessus et on voir apparaître le troisième image de la figure 4.

La fenêtre de la figure 5 apparaît. Il ne reste plus qu'à sélectionner votre macro et à cliquer sur Ok. On ferme ensuite la première fenêtre. Et, enfin, on clique sur le bouton ajouté. Normalement, le contenu de la feuille Excel change : une nouvelle solution apparaît. Les valeurs de la solution précédente ont été remplacées par une autre, un second graphique apparaît.

8

La dernière épreuve consiste à créer un graphe à partir de la solution précédemment calculée. On récupère d'abord la plage contenant les données pour le graphe.

    Dim plage As Range
    Set plage = Worksheets("Sheet1").Range(_
                       Worksheets("Sheet1").Cells(7, 1), _
                       Worksheets("Sheet1").Cells(8, 2) _
                   )

L'instruction Set plage = ... permet d'associer à plage une partie de la feuille Excel sans la recopier. Dans ce cas, sans le mot-clé Set, l'affectation est impossible. En fait, le type Range désigne une partie d'une feuille Excel, toute variable de ce type doit faire référence à un ensemble de vraies cases, c'est pourquoi on ne peut pas les recopier. Il en va de même pour de nombreux objets d'Excel tels que les graphes. L'instruction Set permet en faire de donner un nom plus court à quelque chose de long. On créé ensuite un graphe dans la feuille Sheet1 :

    Dim graphe_in As ChartObject
    Dim graphe As Chart
    Set graphe_in = Worksheets("Sheet1").ChartObjects.Add(100, 30, 400, 250)
    Set graphe = graphe_in.Chart

100, 30, 400, 250 sont les coordonnées du graphe dans la feuille. Le graphe est désigné par la variable graphe. On spécifie maintenant son type : XY.

    graphe.ChartType = xlXYScatterLines

On précise quelles sont les données et qu'elles sont présentées en colonnes :

    graphe.SetSourceData plage, xlColumns

On précise qu'il a un titre et quel est-il.

    graphe.HasTitle = True
    graphe.ChartTitle.Text = "Black Scholes"

On précise qu'il a des axes et la légende de ces axes.

    graphe.Axes(xlValue, xlPrimary).HasTitle = True
    graphe.Axes(xlValue, xlPrimary).AxisTitle.Text = "Xt"
    
    graphe.Axes(xlCategory, xlPrimary).HasTitle = True
    graphe.Axes(xlCategory, xlPrimary).AxisTitle.Text = "temps (jour)"

On modifie le titre de la série et son apparence :

    graphe.SeriesCollection(1).Name = "Courbe1"
    graphe.SeriesCollection(1).Border.Color = RGB(0, 0, 255)
    graphe.SeriesCollection(1).MarkerStyle = xlMarkerStyleNone

Et c'est fini ou presque pour les plus courageux qui peuvent essayer de tracer plusieurs solutions dans un même graphe et pour y arriver, voici quelques lignes qui permettent d'ajouter une série à un graphe.

        Dim serie
        Set serie = graphe.SeriesCollection.NewSeries
        serie.XValues = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(7, 1), _
                            Worksheets("Sheet1").Cells(7 + nb, 1))
        serie.Values = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(7, 1 + i), _
                            Worksheets("Sheet1").Cells(7 + nb, 1 + i))
        serie.Name = "nouvelle série"

Lorsqu'on ne connaît le type de la variable à déclarer comme pour serie, Dim serie suffit sans indication de type. Néanmoins, pour des types simples (Long, String, Double, il est préférable de préciser ce type, le programme est plus rapide.

Programme complet


' il est préférable de mettre cette ligne en haut du fichier
' afin de préciser à VBA qu'il ne doit rien faire de manière
' implicite comme utiliser une variable non déclarée
Option Explicit


'
' cette fonction prend 5 paramètres qui permettent de simuler
' l'équation stochastique de Black Scholes
'
' elle retourne un tableau de réels
'
'
Function Simulation(ByVal r As Double, ByVal sigma As Double, ByVal x0 As Double, _
            ByVal dt As Double, ByVal T As Long) As Variant


    Dim res() As Double
    Dim X As Double
    Dim i As Long
    Dim w As Double
    Dim nb As Long
    
    nb = T / dt + 1
    
    ReDim res(nb)
    X = x0
    res(0) = X
    
    For i = 1 To nb
        w = Rnd
        w = Application.WorksheetFunction.NormSInv(w) * dt
        X = X * (1 + r * dt + sigma * w)
        res(i) = X
    Next i
    
    Simulation = res
    
End Function



'
' définition de la macro Simulation_macro
'
Sub Simulation_macro()

    Dim r As Double
    Dim sigma As Double
    Dim x0 As Double
    Dim dt As Double
    Dim T As Double
    
    ' on récupère les informations depuis la feuille Excel
    r = Worksheets("Sheet1").Cells(4, 1).Value
    sigma = Worksheets("Sheet1").Cells(4, 2).Value
    x0 = Worksheets("Sheet1").Cells(4, 3).Value
    dt = Worksheets("Sheet1").Cells(4, 4).Value
    T = Worksheets("Sheet1").Cells(4, 5).Value
    
    ' on appelle la fonction simulation 5 fois
    Dim i As Long
    Dim marche(5) As Variant
    For i = 1 To 5
        marche(i) = Simulation(r, sigma, x0, dt, T)
    Next i
    
    ' on trace la courbe avec r = 0
    Dim non_stochastique As Variant
    non_stochastique = Simulation(r, 0, x0, dt, T)
    
    ' on récupère le nombre de points dans une solution
    Dim nb As Long
    nb = UBound(non_stochastique)
    
    
    ' on recopie les valeurs de temps et le résultats de la fonction Simulation
    Dim k As Long
    For i = 0 To nb
        Worksheets("Sheet1").Cells(7 + i, 1) = dt * i
        For k = 1 To 5
            Worksheets("Sheet1").Cells(7 + i, 1 + k) = marche(k)(i)
        Next k
    Next i
    
    ' on recopie la solution non stochastique
    k = 6
    For i = 0 To nb
        Worksheets("Sheet1").Cells(7 + i, 1 + k) = non_stochastique(i)
    Next i
    
    
    ' on met une légende
    Worksheets("Sheet1").Cells(6, 1) = "temps"
    Worksheets("Sheet1").Cells(6, 7) = "non stochastique"
    For k = 1 To 5
        Worksheets("Sheet1").Cells(6, 1 + k) = "Simulation " & k
    Next k
    
    
    '
    ' deuxième partie
    ' on crée le graphique s'il n'existe pas
    '
    
    Dim nb_graphe As Long
    
    ' on compte le nombre de graphes de la feuille Sheet1
    nb_graphe = Worksheets("Sheet1").ChartObjects.Count
    
    If nb_graphe = 0 Then
        ' s'il n'y a pas alors...
    
        Dim plage As Range
        
        ' on récupère les données liées à la feuille (2 colonnes)
        Set plage = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(7, 1), _
                           Worksheets("Sheet1").Cells(7 + nb, 2))
        
        ' on crée un graphe
        Dim graphe_in As ChartObject
        Dim graphe As Chart
        Set graphe_in = Worksheets("Sheet1").ChartObjects.Add(100, 30, 400, 250)
        Set graphe = graphe_in.Chart
        
        ' on spécifie son type
        graphe.ChartType = xlXYScatterLines
        
        ' on lui dit quelles sont les données à dessiner,
        ' le second paramètres précise qu'elles sont organisées en colonnes
        graphe.SetSourceData plage, xlColumns
        
        ' on lui met un titre
        graphe.HasTitle = True
        graphe.ChartTitle.Text = "Black Scholes"
        
        ' on met un titre sur l'axe des Y
        graphe.Axes(xlValue, xlPrimary).HasTitle = True
        graphe.Axes(xlValue, xlPrimary).AxisTitle.Text = "Xt"
        
        ' on met un titre sur l'axe des X
        graphe.Axes(xlCategory, xlPrimary).HasTitle = True
        graphe.Axes(xlCategory, xlPrimary).AxisTitle.Text = "temps (jour)"
        
        ' on modifie le nom de la première série
        graphe.SeriesCollection(1).Name = "Courbe1"
        graphe.SeriesCollection(1).Border.Color = RGB(0, 0, 255)
        graphe.SeriesCollection(1).MarkerStyle = xlMarkerStyleNone
        
        ' on ajoute les séries suivantes
        Dim serie As Series
        For i = 2 To 5
            Set serie = graphe.SeriesCollection.NewSeries
            serie.XValues = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(7, 1), _
                           Worksheets("Sheet1").Cells(7 + nb, 1))
            serie.Values = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(7, 1 + i), _
                           Worksheets("Sheet1").Cells(7 + nb, 1 + i))
            serie.Name = "Courbe" & i
            serie.Border.Color = RGB(0, 0, 255)
            serie.MarkerStyle = xlMarkerStyleNone
        Next i
        
        ' on ajoute la solution non stochastique
        i = 6
        Set serie = graphe.SeriesCollection.NewSeries
        serie.XValues = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(7, 1), _
                            Worksheets("Sheet1").Cells(7 + nb, 1))
        serie.Values = Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(7, 1 + i), _
                            Worksheets("Sheet1").Cells(7 + nb, 1 + i))
        serie.Name = "non stochastique" & i
        serie.Border.Color = RGB(255, 0, 0)
        serie.MarkerStyle = xlMarkerStyleNone
        serie.Border.Weight = xlMedium
    
    End If
    
End Sub

La feuille Excel est accessible ici : td_vba1.xlsm.

Xavier Dupré

XD blog

2014-01-30 Equation différentielle stochastique sous Excel avec VBA

1

2

3

4

5

6

7

8

Programme complet