débutant excel finances

[sophie23]

bonjour, je suis vraiment débutante en excel et vba et voici un code vba que j'aimerais faire marcher sous excel, mais je ne sais pas comment faire!
ce code est tiré du pdf www.esg.uqam.ca/recherche/document/2006/11-06.pdf
pour ceux qui y comprennent qqchose ;-)

voici le code :

Sub simopt()

‘Simulation de Monte Carlo pour calculer prix d’une option asiatique
‘Range("d4 :iv6000").ClearContents
‘Range("option1").ClearContents
Dim iopt1, s1, x1, rf1, q1, t1, sigma1, nsimg1, pas

Dim rnmutg, sigtg, sumg, randnsg, S1g, payoff1g, sigsum, sigmoyenne
Dim I As Integer
Dim j As Integer

‘Randomize
iopt1 = 1
s1 = 80
x1 = 85
rf1 = 0.05
q1 = 0
t1 = 1
sigma1 = 0.2
pas = 100
nsimg1 = 100

rnmutg = (rf1 – q1 – 0.5 * sigma1^2) * (t1/pas)
sigtg = sigma1 * Sqrt(t1/pas)

For k = 1 To 100
sumg = 0

For i = 1 To nsimg1

S1g = s1
S2g = s1
sigsum1 = 0
sigsum2 = 0
For j=1 To pas

randnsg = Application.NormSInv(Rnd)
S1g = S1g * Exp(rnmutg + randnsg * sigtg)
‘Range(“prix1”).Offset(j-1, i-1)= S1g
sigsum1 = sigsum1 + S1g
S2g = S2g * Exp(rnmutg – randnsg * sigtg)
sigsum2 = sigsum2 + S2g

Next j

sigmoyenne1 = sigsum1 / pas
‘Range(“prix”).Offset(i-1,0) = sigmoyenne
sigmoyenne2 = sigsum2 / pas

payoff1g = 0.5 * Application.Max(iopt1 * (sigmoyenne1 – x1),0) + 0.5 * Application.Max(iopt1 * (sigmoyenne2 – x1), 0)
‘Range("cash").Offset(i-1, 0) = payoff1g

sumg = sumg + payoff1g
Next i

Option 1 = Exp (-rf1 * t1) * sumg/nsimg1
Range("histo5").Offset(k,0) = option1

Next k


End Sub


Sophie

 

[sophie23]

Re : débutant excel finances

ok albert g bien téléchargé ton fichier excel, mais peux tu m'expliquer comment cela fonctionne avec la méthode montecarlo?
je débute en finances et j'aimerais me familliariser avec les options, merci bcp

 

[Compte Supprimé 979]

Re : débutant excel finances

Salut Bruno

Non, c'est vrai, je ne sais pas dans quelle galère...
Qu'est-ce que j'ignore?

Cdt
Joss

Re Joss,

Il suffit juste de jeter un coup d'oeil sur ce fil
https://www.excel-downloads.com/threads/sauvegarde-excel.94200/

Aller Sophie, c'est juste pour déc...er
On a tous débuté, bon d'accord y'en as qui ont plus de mal que d'autres ...

 

[albert]

Re : débutant excel finances

ben, Sophie, tu es autodidacte ou tu es étudiante?
c'est important pour savoir par quoi commencer pour t'expliquer. Je suis autodidacte et j'ai étudié la question il y a quelques années, il faut que je m'y remette....
si tu es étudiante, à quel niveau es-tu ?

tu connais la formule Black Scholes ?

albert

 

[sophie23]

Re : débutant excel finances

quelle est la différence entre le pricer black n scholes montecarlo que tu m'as envoyé et le pricer black and scholes ci joint?!
merci

 

[albert]

Re : débutant excel finances

La méthode de Monte-Carlo permet de créer des modèles aléatoires

Dans le modèle que j’ai envoyé, dans la feuille Monte-Carlo, tu peux simuler différents prix avec les mêmes valeurs s,k,r,sigma... la simulation donne une variable aléatoire


Le modèle que tu présentes permet de calculer un prix unique, fictif, d’une d’option (c'est probablement un call)

 

[sophie23]

Re : débutant excel finances

ok mais est ce que tu peux m'expliquer comment fonctionne ce programme?!
(pricer montecarlo)

 

[albert]

Re : débutant excel finances

Bonjour Sophie,

Option asiatique : Option dont le prix d’exercice est calculé sur la moyenne de cours tout au long de la durée de vie de l’option
=> ce qui justifie une simulation pour essayer de déterminer le prix de l’option

//**********

le code vba (Alt+F11) ou outil/macro/visualbasic editor – dans vba project à gauche, modules/module 1
Cells(i, 1) = WorksheetFunction.NormInv(Rnd, 0, 1)

NormInv() = fonction Loi.normale.inverse() d’excel
alea() d’exel = Rnd ; tirage de nombres aléatoires
=> dans la feuille Monte-Carlo, colonne A,i

i est une boucle For i = 5 To iteration qui commence à la ligne 5 jusqu’au nombre d’itérations
iteration = Feuil1.Range("iteration")

Range("iteration") tu le trouves dans la liste que tu obtiens en cliquant sur la flèche située à gauche du signe =

Tu cliques dessus et tu obtiens la sélection de A2, feuille Monte-Carlo

//*****************
Cells(i, 2) = s * Exp((rate - (sigma ^ 2) / 2) * t + sigma * Cells(i, 1))
Cells(i, 2) => dans la colonne B le calcul s’effectue avec les référence : s (flèche située à gauche du signe =)
Exp tu positionnes le curseur dessus, tu tappes F1 au clavier et tu obtiens l’aide : Exp, fonction, Renvoie une valeur de type Double indiquant la valeur de e (base des logarithmes népériens) élevé à une puissance….

Et tu continues la recherche toute seule, c’est comme ça qu’on apprend

//******************
« je débute en finances et j'aimerais me familliariser avec les options, merci bcp »
commencer par l'Option asiatique risque peut-être de te dégoûter...
à ta place, je commencerais déjà par apprendre à manier la formule de Black-Scholes, à comprendre le fonctionnement des Grecs et ensuite tu peux aborder les options plus complexes, asiatique, à barrière, etc…
il faut avoir une solide formation en mathématiques pour se lancer dans ce sujet – ça s’apprend, mais il faut du temps et de la méthode.

Pour les principes de base
Ce lien n'existe plus

à gauche il y a la liste des fiches valeur d'une option, stratégies élémentaires....

bon courage

albert

 

[sophie23]

Re : débutant excel finances

Salut Albert,

merci pour ton aide, mais voila je ne comprends pas plusieur choses dans le fichier excel :
1) y a t il un rapport entre les 2 onglets (pricer black and scholes et montecarlo ? )
2) Pourquoi le pricer ne me donne pas le meme résultat pour la prime qu'un autre pricer aussi sous excel ? il y a une legere différence ...
3) Peux tu m'expliquer quel est l'intéret d'un pricer comme celui ci?A quoi te sert de calculer la prime ? Et comment cela fonctionne exactement ce pricer montecarlo (en gros et simplifié)

merci 1000 fois pour ton aide encore
sophie

 

[albert]

Re : débutant excel finances

1) y a t il un rapport entre les 2 onglets (pricer black and scholes et montecarlo ? )
Non : Pour vérifier tu vas dans VBAProject :

Feuil1(Monte-Carlo) tu cliques dessus et dans la page de droite tu obtiens le code qui est actionné par le bouton qui est dans la feuille Monte-Carlo

Tu cliques au début du code – page de droite - Dim iteration As Double, tu réduis la fenêtre de manière à voir la page qui est derrière et tu cliques sur « Exécution »
Si tu es sur la page Black & Scholes, tu obtiens une erreur d’exécution... si tu es sur la page Monte-Carlo, le code s’exécute => c’est le code de la bonne page. Puisqu’il y a un code différent pour chaque page logiquement tu peux en déduire que les 2 pages sont indépendantes

Tu peux faire fonctionner le code ligne par ligne en appuyant su F8

//***************

2) Pourquoi le pricer ne me donne pas le meme résultat pour la prime qu'un autre pricer aussi sous excel ? il y a une legere différence ...

parce que le calcul peut être fait en logarithme ou en numérique, ce qui donne des résultats légèrement différents.

Mais ces différences sont sans importance puisque le pricer calcule un prix théorique et ensuite il faut le comparer au prix du marché => le prix de l’option se forme avec la comparaison offre/demande

//*******************

3) Peux tu m'expliquer quel est l'intéret d'un pricer comme celui ci?A quoi te sert de calculer la prime ?

la prime, c’est le prix théorique de l’option => le prix est différent selon que tu achètes un call ou un put (toutes les données étant égales : sous-jacent, prix du strike, durée....)

//***************

comment cela fonctionne exactement ce pricer montecarlo

H2 = prix du call et J2 prix du put (c’est écrit en anglais)

Les chiffres à gauche => résultat des tirages aléatoires. Ces tirages aléatoires servent à modéliser la courbe des valeurs qui pourraient être prises par « Option asiatique : le prix d’exercice est calculé sur la moyenne de cours tout au long de la durée de vie de l’option »
=> il s’agit de la moyenne d’un historique de cours, ce n’est plus un calcul à un moment donné comme dans Black & Scholes.

Bien sûr, il s’agit d’un calcul sur des tirages aléatoires => les cours ne sont pas certains par définition et le résultat ne l’est pas non plus...c’est une approximation pour négocier le prix



albert

 

[sophie23]

Re : débutant excel finances

ok mais qu'apporte la méthode de montecarlo par rapport a l'onglet black and scholes?
l

Comme tu dis, montecarlo sert a donner une liste de nombre aléatoires, et qu'est ce que fait le programme de cette liste de nombres?
je pourrais obtenir une courbe?

sophie

 

[albert]

Re : débutant excel finances

ok mais qu'apporte la méthode de montecarlo par rapport a l'onglet black and scholes

Méthode de Monte-Carlo - Wikipédia

//*****************
je pourrais obtenir une courbe?


Pièce jointe, onglet Monte Carlo, il y a toutes les simulations, reproduites en graphique dans l’onglet Price Paths

 

[sophie23]

Re : débutant excel finances

salut albert, j ai encore regardé le fichier excel black and scholes (avec les 2 onglets un black and scholes et l'autre montecarlo)

1) je comprends pas tres bien comment le calcul des call en montecarlo

si on fait la moyenne des valeurs obtenues aléatoirement on trouve une valeur qui est un peu au dessus de la valeur du call affiché ...comment se fait il que l'on obtient pas cette meme valeur ?
par ex, si je choisi une itération=1, on constate ce probleme ...

2) comment le programme fait il pour choisir ses nombres aléatoires, si on prend s(t)=100 et K = 110, on aura des valeurs aléatoire entre 70 et 140 environ ....
comment le programme calcule t il les fourchettes de valeurs montecarlo?

merci bcp
sophie

 

[albert]

Re : débutant excel finances

bonjour Sophie,

« comment se fait il que l'on obtient pas cette meme valeur ? »

la valeur obtenue dans H2, soit Cells(2, 8) est dans le code vba (Alt+F11)

Cells(2, 8) = (Exp(-rate * t)) * WorksheetFunction.Max _
((WorksheetFunction.Sum(Selection) / iteration), 0)

rate = r(annual)
t = T-t
Exp => Exp, fonction
Renvoie une valeur de type Double indiquant la valeur de e (base des logarithmes népériens) élevé à une puissance.
WorksheetFunction.Max => Dans Visual Basic, les fonctions de feuille de calcul Microsoft Excel sont disponibles par l'intermédiaire de l'objet WorksheetFunction.
Max pour déterminer la plus grande valeur d'une plage de cellules.

WorksheetFunction.Sum(Selection) / iteration

Selection => Range("d5").Select
Range(Selection, Selection.End(xlDown)).Select

//*******************

comment le programme fait il pour choisir ses nombres aléatoires, si on prend s(t)=100 et K = 110, on aura des valeurs aléatoire entre 70 et 140 environ ....

?? il n’y a pas s(t) dans la feuille Monte-Carlo

s(t) est dans la feuille Black & Scholes

sinon, si tu veux savoir comment se font les calculs dans les colonnes A à C :
A => Cells(i, 1) = WorksheetFunction.NormInv(Rnd, 0, 1)
B => Cells(i, 2) = s * Exp((rate - (sigma ^ 2) / 2) * t + sigma * Cells(i, 1))
C => Cells(i, 3) = Cells(i, 2) – k

En résumé, si tu veux comprendre le fonctionnement du code, il faut aller dans vba. Tu ne trouveras pas de réponse dans les cellules de la feuille, puisqu’il n’y a pas de formules excel pour effectuer les calculs

//*********

au fait, tu n'as pas encore dit si tu es étudiante (ce qui suppose que les math n'ont plus de secret pour toi) ou si tu es autodidacte, alors il faut tout apprendre...
parce que moi...je peux t'expliquer la programmation, mais pour les formules mathématiques, ne compte pas sur moi (d'ailleurs ce n'est pas l'objectif du forum)...

sur le veveve on trouve des tas de démonstrations sur ces formules

albert

 

[sophie23]

Re : débutant excel finances

re : albert

oui je suis étudiante en 3eme année alors j ai de bonnes connaissances en maths, mais par contre g jamais fait de vba ......
peux tu m aider a comprendre ce programme?
par exemple : comment fait on pour créer des boutons pour cliquer comme reset?
merci

 

[albert]

Re : débutant excel finances

Bonsoir Sophie,

bon, pour les maths je n'aurai pas besoin de me prendre la tête pour t'expliquer les formules.
Mais, pour apprendre excel et vba, il faut pratiquer régulièrement.
ils ne donnent pas de cours pour les bases excel et vba dans ton université?

//*******************

Pour ajouter un bouton dans la feuille, clic droit dans la bande grise au-dessus de la feuille/boîte à outil de contrôle/clic gauche sur le bouton dans la liste des objets.
En sortant du formulaire, le curseur s’est transformé en croix/clic gauche dans une cellule pour placer le bouton => CommadButton

Clic droit sur CommadButton/propriétés => une fenêtre s’ouvre
Pour changer le nom à droite de Caption : CommadButton => modifier et le nom change sur le bouton

Couleur => backcolor, forecolor...etc...

//************

Clic droit sur CommadButton/visualiser le code : permer d’écrire le code vba dans la feuille

Ensuite, pour rendre le bouton actif : fichier/enregistrer
Puis tu fermes le classeur excel. Lorsque tu l’ouvres à nouveau, le bouton est actif
//****************************
Il y a une autre solution

clic droit dans la bande grise au-dessus de la feuille/formulaires, tu glisses le bouton dans la feuille/affecter une macro => tu sélectionnes la maro vba préalablement préparée/OK et tu sauvegardes
pour modifier => clic droit sur le bouton ...etc...

albert