User defined function

Bonjour tout le monde,

Je fais appel à vous parce que malgré de nombreuses recherches hasardeuses je n'ai pas réussi à compléter mon programme Vba pour analyser un portefeuille d'investissement.

Grosso modo j'ai les prix de deux actions dans une colonne chacune, et ce que je veux faire c'est créer une UDF pour calculer les caractéristiques d'un portefeuille comprenant ces deux actions.

Mon problème c'est que VBA doit calculer de manière cachée les retours sur investissements de chaque action, avant de pouvoir les utiliser pour connaître les retours du portfeuille.

Pour calculer les retours il faut utiliser LN.

Donc par exemple les retours entre Avril 2013 et Mai 2013 pour Walmart seraient donnés par Ln (B4/B3).

J'aimerais donc compléter cette ligne de code :

(Code pour calculer les retours du portefeuille selon la proportion investie dans l'actif1)

Function Retour_Moyen_Portefeuille(Actif1 As Range, Actif2 As Range, ProportionActif1 As Double) As Variant

Retour_Moyen_Portefeuille = (ProportionActif1 * Actif1 + (1 - ProportionActif1) * Actif2)

Actif1= ...... c'est la que je bloque, j'aimerais qu'actif1 représente la moyenne des retours (Ln) de Walmart

Merci infiniment je suis vraiment perdu, je ne sais pas du tout comment faire pour qu'Excel aille dans le tableau de walmart pour calculer la moyenne des retours en utilisant ln...

bonjour

fais les calculs dans la feuille de calcul avec des formules

et tu les affiches dans une colonne dédiée

si ça marche, tu pourras les passer dans un code et les afficher dans un USF

amitiés

Oui j'y ai pensé, mais apparemment dans les consignes, on est sensés trouver un moyen pour qu'Excel calcule de lui même les retours à l'intérieur de Vba sans qu'on ait à créer une colonne dédiée aux retours de chaque action.

Ce serait du genre excel sélectionne les données du tableau Walmart, calcule les retours, fait la moyenne des retours, et cette moyenne des retours est désignée par le nom Actif1.

Ça m'a l'air vraiment compliqué et je patauge pour faire ça depuis des heures, un ami à moi m'a dit que ce serait peut être possible avec des arrays, mais ça ne m'a pas vraiment aidé :'(

Bonjour,

à tester,

Option Explicit
Option Base 1

Function Retour_Moyen_Portefeuille(Actif1 As Range, Actif2 As Range, Rng1 As Range, Rng2 As Range) As Variant
Dim r As Double
r1 = ProportionActif1(Rng1)  'Rng1  --> Range("B4:B74")
r2 = ProportionActif1(Rng2)  'Rng2  --> Range("E4:E74")
Retour_Moyen_Portefeuille = (r1 * Actif1 + (1 - r2) * Actif2)
End Function

Function ProportionActif1(Rng)
Dim i As Long, v As Double, t As Double, n As Long, r As Double
For i = Rng(1).Row To Rng(Rng.Count).Row
    v = Application.Ln(Cells(i, 1).Value / Cells(i - 1, 1).Value)
    t = t + v
    n = n + 1
Next
r = t / n
ProportionActif1 = r
End Function

Merci pour cette réponse, c'est vraiment gentil.

Mais je crois que j'ai fait une bourde dans mes explications, j'ai essayé de détailler ma démarche à l'intérieur de la feuille (en bas) afin que tu comprennes mieux ce que je voulais faire.

Je suis vraiment désolé d'abuser de ton temps, encore merci!

Apprentiexcel a écrit :

Oui j'y ai pensé, mais apparemment dans les consignes, on est sensés trouver un moyen pour qu'Excel calcule de lui même les retours à l'intérieur de Vba sans qu'on ait à créer une colonne dédiée aux retours de chaque action.

re

salut au passage Isabelle

"consignes" ? qui donne des consignes ?

Excel est un tableur, il trouve seul les résultats de 2+2

amitiés

Et bien, notre prof de gestion de portefeuille nous a dit que VBA pouvait calculer des trucs dans son coin sans que ça s'affiche sur la feuille de calcul, du coup je voulais bien voir comment ça se faisait.

Parce que sinon oui si je voulais m'en débarrasser vite fait je pourrais faire plusieurs colonnes et faire tous les calculs sans utiliser Vba.

Mais là je suis vraiment curieux de voir comment résoudre cette histoire avec VBA.

Bonjour,

dit moi si ça convient ?

Ah, je comprends, on s'est mal compris xD

En fait la ProportionActif1 n'est pas une valeur qu'on doit calculer, mais c'est une valeur que doit choisir l'utilisateur.

Je te résume rapidement le but de l'outil.

L'utilisateur lance le programme et reçoit une question:

"Quelle % de votre portefeuille souhaitez-vous investir dans l'actif 1?"

L'utilisateur tape une valeur, par exemple 30%.

Ensuite VBA enregistre cette valeur et l'utilise pour calculer les retours du portefeuille.

En fait en finance pour calculer le retour moyen d'un portefeuille, ce qu'on doit savoir c'est:

Le retour moyen de l'actif 1 (qui se fait via les LN)

Le retour moyen de l'actif 2 (qui se fait via les LN aussi)

La part investie dans l'actif 1

La part investie dans l'actif 2

On va dire qu'en faisant les LN pour l'actif 1, on obtient 0.005276867 ==> ce résultat c'est le retour moyen de l'actif1

Dans notre fonction on va appeler ce retour moyen : Actif1

En faisant les LN pour l'actif 2, on obtient 0.016040598 ==> ce résultat c'est le retour moyen de l'actif2

Dans notre fonction on va appeler ce retour moyen: Actif2

Pour calculer le retour du portefeuille, c'est tout simple, c'est une moyenne pondérée.

VBA récupère ProportionActif1 dans l'inputbox, on avait dit plus tôt que c'était 30%

Cela veut dire qu'on investit 30% d'actif 1 dans le portefeuille.

Vu qu'on a deux actifs, il nous reste toujours 70%.

Globalement, ProportionActif1 est donné par l'utilisateur, et grâce à ça on en déduit la proportion de l'actif 2.

La fonction qu'on veut faire fait donc:

(Actif1 * ProportionActif1) + (Actif2 * (1 - ProportionActif1))

Actif1 c'est 0.005276867

ProportionActif1 c'est 30% (donc 0,3)

Actif2 c'est 0.016040598

1 - ProportionActif1 ==> 0,7

Voilà, encore merci!!!

Je t'ai mis un petit exemple dans ce fichier, en montrant ce que je voulais faire avec un exemple sans utiliser VBA.

J'aimerais faire la même chose mais sans avoir à créer de colonne LN, ni a avoir à taper manuellement les proportions (en les obtenant juste via VBA grâce à une inputbox qui va me demander quelles proportions je désire).

re

note : si l'utilisateur saisit des données dans un USF sans qu'elles soient écrites sur la feuille, il perd l'info. Impossible de voir s'il y a eu erreur de saisie par exemple.

amitiés

Justement, l'utilisateur n'a qu'une seule chose à faire, c'est d'indiquer la proportion d'actif qu'il souhaite allouer à l'actif 1 dans une inputbox, c'est cette donnée qui sera ensuite utilisée dans la formule pour calculer la moyenne des retours du portefeuille.

En fait je me casse la tête depuis tout à l'heure afin de trouver comment faire en quelque sorte une fonction imbriquée dans une autre.

Avec genre actif1 = Moyenne des Ln du premier tableau

actif2 = moyenne des Ln du deuxième tableau

Puis une fonction qui va les mettre en relation avec:

(Actif1*ProportionActif1) + (Actif2 * (1-ProportionActif1))

ProportionActif1 n'est pas donné dans le tableau parce qu'il est demandé à l'utilisateur lorsqu'il lance le programme.

Etape par étape ce serait:

L'utilisateur lance le programme, il lui est demandé de renseigner ProportionActif1 dans une inputbox

VBA calcule les retours de l'actif1 en faisant la moyenne les Ln

VBA calcule les retours de l'actif2 en faisant la moyenne des Ln

VBA calcule la moyenne des retours du portfolio avec la fonction: (Actif1*ProportionActif1) + (Actif2 * (1-ProportionActif1))

J'ai testé un truc et j'ai pas mal avancé, mais la dernière ligne de code me coince.

Mon code VBA se bloque à cause de cette dernière ligne:

Range("K4").Value = Application.WorksheetFunction.portfolio_mean_return(LogReturnAsset1, LogReturnAsset2, 100)

Je voulais tester pour voir si ma démarche était bonne, mais je me retrouve avec le code d'erreur "Propriété ou méthode non gérée par cet objet".