Procédure événementielle calcul de moyenne

Bonjour à toutes et à tous,

Voila j'ai crée un module permettant de générer des valeurs (je vous passe les détails mais c'est une simulation de Monte Carlo). Elle marche nickel.

Petit soucis maintenant que je cherche à calculer des moyennes . Le but est juste de recréer le fonction MOYENNE de excel mais :

1) en disant à cette requête de faire attention au nombre de valeur à moyenner si j'augmente mon nombre j (= nombre de simulation)

2) que cette moyenne se recalcule à chaque modification de la plage à moyenner

Apres plusieurs lecture j'ai trouvé que :

• pour résoudre mon premier problème notamment avec la variation des i je peux utiliser cela afin de désigner la dernière ligne de ma feuille: Range("A" & Rows.Count).End(xlUp).Row
• je peux utiliser une procédure événementielle pour que la moyenne se recalcule à chaque fois

Le soucis est que je n'arrive pas du tout à écrire le bon code je suis vraiment un débutant (je vous joins mon fichier pour mieux comprendre) donc svp ne vous moquez pas je sais qu'il y a énormément de fautes et d'incohérences

Private Sub Worksheet_Change(ByVal Target As Range)

DIRE QUE SI LA DERNIÈRE LIGNE DE LA FEUILLE EST MODIFIÉE ALORS (RE)CALCULER LES MOYENNES
If Not Intersect(Target, Range("A" & Rows.Count).End(xlUp).Row) Is Nothing Then 

Dim strike As Double, PayoffCall As Double, PayoffPut As Double, NbreSimulation As Double
Dim M_Payoff_Call As Double, M_Payoff_Put As Double
Dim j As Integer
Dim DerniereLigne As Long

With Sheets("MC")
NbreSimulation = .Cells(6, 2).Value
strike = .Cells(7, 2).Value
DerniereLigne = Range("A" & Rows.Count).End(xlUp).Row

DIRE QUE LES VALEURS A MOYENNER CORRESPONDENT AU MAX ENTRE 0 ET la différence entre la valeur de la dernière cellule de la dernière ligne et le strike (l'inverse pour le put) et cela de la première colonne jusqu'à  la colonne correspondant au nombre de simulations  
For j = 1 To NbreSimulation
Set range_payoffcall = WorksheetFunction.Max(.Cells(DerniereLigne, j).Value - strike, 0)
Set range_payoffput = WorksheetFunction.Max(strike - .Cells(DerniereLigne, j).Value, 0)

Next j

FAIRE LA MOYENNE DES VALEURS DÉFINIES A L'ETAPE PRÉCÉDENTE
M_Payoff_Call = WorksheetFunction.Average(range_payoffcall)
M_Payoff_Put = WorksheetFunction.Average(range_payoffput)

AFFICHER LES VALEURS DANS DES CELLULES CHOISIES
.Cells(3, 10).Value = M_Payoff_Call
.Cells(5, 10).Value = M_Payoff_Put

End With
End If
End Sub

Merci pour votre aide je deviens fou ahah

Bonjour,

Ce qui assez étrange dans ta macro ... ce sont les deux définitions que tu veux donner aux range_payoff pour les Calls et pour les Puts ...

Au delà du fait que ce sont d'ailleurs des variables que tu n'as pas déclarées ...

Quelles sont concrètement les plages dont tu as besoin ...???

Hello James007,

Je te remercie pour ta réponse et Tu as tout à fait raison. Lorsque j’ai ecris ces deux lignes je m’en suis demandé si j’avais le droit.

Très concrètement :

1)Je ne peux pas désigner mes payoffs comme étant une plage réelle car cela m’obligerait à ajouter une dernière ligne en bas de page et biaiserait donc la formule désignant la dernière ligne (censée être la dernière periode de chaque simulation plutôt que les payoffs eux même)

2) je passe donc par la formule indiquée dans les rang_payoffs pour dire que la valeur à moyenner et la somme de chaque payoff de j=1 jusqu’à nombre de simulation. La formule des payoffs étant celle avec le Max (....)

Je me rends compte que c’est complètement faux ce que j’ai fait mais je vois Pas comment faire.

Je te remercie encore et excuse moi pour ce code minable

Re,

Si on met de côté pendant deux minutes ta macro évènementielle ...

Pour les cellules G3 et G5, peux-tu saisir dans ton fichier les deux formules de moyennes de payoff qu'il te faut ...

Avec un exemple chiffré ... cela facilitera la construction de la macro ...

Re,

Voila le détail pas à pas des formules que je souhaiterais reproduire en code VBA sous les conditions évoquées dans mon premier message.

La difficulté vient surtout du fait que les deux lignes grisées du haut , je ne souhaite pas les faire apparaitre (comme dis précédemment sous l'hypothèse ou ces payoffs soient calculés et renseignés en bas de page - le plus logique - la formule sélectionnant la dernière ligne serait biaisée).

Merci encore de prendre le temps de m'aider j'apprécie énormément.

A+

J'espère avoir bien compris ta demande.....

Re,

Tu as parfaitement compris ... !!!

Et je crois pouvoir dire ... que tu as même supprimé l'utilité d'une macro évènementielle ...

puisque toute mise à jour de ta simulation ... ajuste toutes les données y compris les payoff ...

Si je comprends bien pas besoin de macro événementielle car :

nouvelle simulation -> nouveaux payoffs -> nouvelles moyennes ?

Merci beaucoup effectivement je n’avais pas vu cela sous cet angle.

Du coup j’ai juste à déclarer payoff et le moyenner dans le module VBA ? En lui indiquant la formule d’un payoff avec la dernière ligne de la feuille ?

Thanks again

Re,

On peut tout à fait légèrement modifier les formules ... pour ne pas s'encombrer de macro supplémentaire ...

Ci-joint ton fichier ... avec deux petits twists dans les formules de pay-off ...

Je te remercie pour ta réponse effectivement je pensais faire comme cela et même descendre jusqu'à A65536 (sachant que 252 périodes ne représente qu'un an donc si je passe à 10 on a vite dépasser les 500).

Ceci étant dit, je commence à être relou , le but ici est justement d'outrepasser les formules Excel et utiliser uniquement VBA.

Peux - tu me dire si écrire cela est correct et correspond à ce que je cherche sachant que je maitrise assez mal la formule désignant la dernière ligne et aussi comment définir les valeurs à moyenner à VBA?

Sub Moyenne Payoff() 

Dim strike As Double, PayoffCall As Double, PayoffPut As Double, NbreSimulation As Double, M_Payoff_Call As Double
Dim M_Payoff_Put As Double
Dim  range_payoffcall  as Range, range_payoffput as Range
Dim j As Integer
Dim DerniereLigne As Long

With Sheets("MC")
NbreSimulation = .Cells(6, 2).Value
strike = .Cells(7, 2).Value
DerniereLigne = Range("A" & Rows.Count).End(xlUp).Row

DEFINIR QU'EST CE QU'UN PAYOFF
For j = 1 To NbreSimulation
Set range_payoffcall = WorksheetFunction.Max(.Cells(DerniereLigne, j).Value - strike, 0)
Set range_payoffput = WorksheetFunction.Max(strike - .Cells(DerniereLigne, j).Value, 0)
Next j

DIRE QUE LA MOYENNE DES PAYOFF C'EST LA MOYENNE DES RANGES DEFINIS AU DESSUS
M_Payoff_Call = WorksheetFunction.Average(range_payoffcall)
M_Payoff_Put = WorksheetFunction.Average(range_payoffput)

ASSIGNER LES VALEUR MOYENNEES DANS DES CELLULES
.Cells(3, 10).Value = M_Payoff_Call
.Cells(5, 10).Value = M_Payoff_Put

End With
End Sub

Re,

le but ici est justement d'outrepasser les formules Excel et utiliser uniquement VBA ...

Why not ...???

Dès que j'ai un petit moment ... je revisite ton code ...

Re,

Parce que:

1) les "deux petits twists dans les formules de pay-off " + la formule pour trouver le numéro de la dernière ligne marche très bien si on modifie le nombre de période (i=252 dans mon fichier). Mais à partir du moment ou j'augmente mon nombre de simulation (j=5 dans mon exemple) j'augmente le nombre de payoffs à moyenner et dois donc manuellement dérouler les formules de payoffs que tu m'as données vers la droite pour qu'Excel prenne bien en compte l'ensemble de mes simulation. Idem pour la formule de la moyenne que je dois adapter pour prendre en compte les nouveaux payoffs disponible.

2) Ici l'objectif c'est vraiment d'avoir un fichier ou dès qu'on lance les simulations et même si on modifie i ou j tout se fasse tout seul.

Je te remercie pour ta patience et ton temps et reste à ta dispo si tu as des questions.

Re,

Si l'unique problème des deux twists de formules ... consiste à bouger la formule de la celulle N1 vers.... la cellule G7 ... pour laisser la place à n simulations , pour le coup ... je suis sûr que tu n'as pas besoin de mon aide ...

Mais si tu tiens à 100% VBA ... je vais y jeter un oeil ...

Je n'oserais même pas publier sur un forum si ce n'était que ça .

Merci j'attends ton retour

Re,

Rendre toutes les formules dynamiques .. est très rapidement fait ... pour n simulations ...

Re,

Tu fais intervenir des formules dont j'ignorais totalement l'existence

Je souhaite néanmoins privilégier le 100% VBA, car par la suite je vais également intégrer la formule d'actualisation et chercher une façon d'écraser les colonnes et lignes en trop lorsque (i ou j) n-1 > (i ou j) n.

Je te remercie pour toute tes réponses en tout cas et hésite pas à jeter un coup d'œil quand tu as deux minutes au code VBA stp

thanks

Re,

Entendu ... je prendrai un moment pour la transcription VBA ...

Hello,

je reviens vers toi car j'ai essayé le code (voir fichier joint) et malheureusement :

1) je ne trouve pas le bonne valeur de la moyenne (je ne vois pas où l'erreur peut être!) ce qui est grisé c'est un test pour vérifier que mon code est bon (ce qui n'est ps le cas)

2) à chaque nouvelle simulation la moyenne n'est pas recalculé comme tu me l'avais suggéré. C'est d'ailleurs pour cela que je m’étais mis en tête de faire une requête événementielle.

Merci à toi

NB : Si j'ai bien compris dans ma formule des payoffs je lui dis pas que c'est la somme de j=1 jusqu'à n mais je lui désigne la valeur dans la cellule n de la dernière ligne . Comment y remédier stp

Bonjour Max,

Il va de soi ... que le premier cap à franchir ...est celui d'avoir les bonnes formules de Payoff ...

See attached ...

Hello,

Merci à toi.

Néanmoins la formule des payoff indiquée dans mon fichier d'hier soir est correcte (le max entre (0; différence entre la valeur de la dernière ligne et le strike)) pour le call (oublions le put pour le moment).

Mon soucis réside dans le fait que je n'arrive pas à retranscrire cela en code VBA.

Dans le fichier que je t'ai envoyé hier, tu vois que mon Sub MoyennePayoff(), en plus de ne pas s'exécuter à chaque simulation, considère ma moyenne des payoffs comme étant tout simplement le payoff de ma dernière colonne, ce qui est complément faux.

Si je devais faire ça en formule Excel ça serait très simple et tu m'as déjà donné une solution parfaite hier. Le soucis c'est vraiment VBA là.

Je pense qu'il doit y avoir un problème dans une de ces deux lignes qui ne doivent pas retranscrire ce que je cherche à dire:

DerniereLigne = Range("A" & Rows.Count).End(xlUp).Row 
......
For j = 1 To NbreSimulation
PayoffCall = WorksheetFunction.Max(.Cells(DerniereLigne, j).Value - strike, 0)

Merci à toi

A+

NB :Je suis entrain de voir si je peux pas diviser le code en définissant tout d'abord la formule d'un payoff call puis définir la somme des payoffs call puis moyenner somme.

Voila le code en l'état actuel, qui me donne la même chose : le max en 0 et la valeur de la cellule de la dernière ligne et dernière colonne - strike. Je vois pas ou j'ai faux

Sub MoyennePayoff()

Dim strike As Double, PayoffCall As Double, NbreSimulation As Double
Dim M_Payoff_Call As Double, Sum_PayoffCall As Double
Dim j As Integer
Dim DerniereLigne As Long

    With Sheets("MC")
        NbreSimulation = .Cells(6, 2).Value
        strike = .Cells(7, 2).Value
        DerniereLigne = Range("A" & Rows.Count).End(xlUp).Row

            For j = 1 To NbreSimulation
              PayoffCall = WorksheetFunction.Max(.Cells(DerniereLigne, j).Value - strike, 0)
            Next j

    Sum_PayoffCall = WorksheetFunction.Sum(PayoffCall)

    M_Payoff_Call = WorksheetFunction.Average(Sum_PayoffCall)

    .Cells(3, 8).Value = M_Payoff_Call

End With
End Sub