[XL-2010] Recherche de valeur sous conditions multiples (prix de revient d'action)

[caperni]

Bonjour à tous,

je suis bloqué par un problème à priori simple et qui me semble désormais insoluble, j'ai besoin d'aide!!

Je construis un fichier excel me permettant de suivre des opérations boursières. Il y a une ligne par opération, nom de l'action (colonne A), date d'opération (colonne B), sens d'opération (achat ou vente) (colonne C), quantité d'actions achetée ou vendue (colonne D), cours d'opération (colonne E), montant de l'opération (colonne F) et prix de revient (colonne H).

J'ai d'énormes difficultés à récupérer OU à calculer le prix de revient (PR) de mes actions lors d'une vente partielle.

Exemple :
opération 1 - en 2004 j'achète 10 actions Pernod Ricard à 10€. Le PR en G2 est donc 10€.
opération 2 - en 2005 je rachète 10 actions Pernod Ricard à 20€. Le PR en G3 est donc 15€ (10*10€+10*20€)/20. Jusque là, pas de pb.
opération 3 - en 2006 j'achète 20 actions Véolia à 5€. Le PR en H4 est donc de 5€ (celui de Véolia et non de Pernod Ricard).
opération 4 - en 2007 l'action Pernod Ricard s'est envolée, j'en vends une partie : je vends 15 actions sur les 20 que je possède. Le PR est censé être le même pour les 5 actions en solde que pour les 20 précédentes. Je souhaite récupérer le prix de revient précédent et ou bien le calculer correctement (je n'y arrive pas). Ce qui me semble le plus simple est de faire afficher en H5 la valeur de H3 dans le cas ou la ligne 5 correspond à une vente. D'autres méthodes probablement possibles.

J'ai pensé à des recherches en fonction du nom de l'action en colonne A et en fonction de la date la plus récente en colonne B mais je n'y arrive pas.

J'espère être assez clair!! Et merci d'avance à tous ceux qui pourront m'aider.

[corona]

Bonjour,
Une proposition, parmi d'autres sans doute.

Code :

=SOMMEPROD(($A2=$A$2:$A$5)*($C$2:$C$5="A") * ($F$2:$F$5)) / SOMMEPROD(($A2=$A$2:$A$5)*($C$2:$C$5="A") * ($D$2:$D$5))

[caperni]

Bonjour CORONA et merci de la réponse,

vous avez choisi l'option calcul du PR.
j'ai modifié la formule proposé comme suit :

Code :

=SOMMEPROD(($A5=$A$2:$A5)*($C$2:$C5="A") * ($F$2:$F5)) / SOMMEPROD(($A5=$A$2:$A5)*($C$2:$C5="A") * ($D$2:$D5))

la valeur cherchée est A5 (celle de l'opération concernée par la ligne) et non A2, et j'ai supprimé des $ pour pouvoir copier la formule de ligne en ligne. L'idée serait d'avoir une BDD allant de la ligne 2 à 5 pour l'opération de la ligne 5, puis de 1 à 6 pour l'opération de la ligne 6 etc etc...

effectivement cela fonctionne très bien pour le cas de figure énoncé.
mais si je poursuis la formule dérape.

imaginons que :
opération 5 - en 2008 l'action Pernod Ricard baisse, je rachète 5 actions à 25€. Mon précédent PR était de 15€ par action, pour 5 actions, il sera désormais de 20€ (5*15€+5*25€)/10.

Avec la formule j'obtiens 17€ car elle recalcule tout depuis le départ. Donc comme si j'avais encore 20 actions avec un PR de 15€ alors que je n'en ai plus que 5

Sur une option "calcul" de la valeur, je pense qu'il faut quand même à un moment aller chercher la dernière valeur de PR renseignée - ou bien la somme investie correspondante aux 5 actions restantes sur la base du nombre d'action encore en possession.

désolé, j'écris bcp mais c'est pas simple, c'est un beau challenge

PS j'ai un fichier avec les formules évoquées qui permettrait d'y voir plus clair mais j'ai l'impression qu'on ne peut pas le mettre en PJ.

[alsimbad]

Code :

=SOMMEPROD(($A$2:A7=A7)*($C$2:C7="A")*($E$2:E7)*($D$2:D7))/SOMMEPROD(($A$2:A7=A7)*($C$2:C7="A")*($D$2:D7))

j'obtiens la même formule que corona et qui fonctionne sur toutes les lignes et qui est étirable
je pense qu'il y a quelque chose d'éroné dans ce que tu recherche qui rend caduque les formules
si tu ne prend en compte que les actions qui te restent il faut pouvoir dire a la formule de prendre en compte cette vente, cad, d'une façon mathématique, en lui plaçant un signe moins quelque part, mais dans ce cas, elle calculera que tu es bénéficiaire (ou pas ) mais elle fera le calcul global Vente et Achats
et si tu ne lui indique pas, elle ne peut pas savoir que tu les a vendue, donc elle fera le calcul global des Achats
dans le cas que tu décris, tu a été bénéficiaire, donc le pr restant est simple, mais imaginons que tu ai vendu a perte, par exemple un besoin de liquidite et tu a vendu tes 15 actions a 2€, le prix de reviens des 5 qui te reste serait t'il de
(10*10) + (10*20)/20 = 5
ou
(10*10) + (10*20)-(15*2)/20 = 13.5 ?
si tu veux ne prendre en compte que le dernier PR, il te faut peut etre rechercher par une formule de recherche et non de calcul
rechercheV n'ira pas puisque il trouvera la premiere occurence du tableau et non la derniere, peut etre index/equiv, mais dans ce cas, en trouvant la derniere occurence, tu trouvera le dernier PR mais pas le nombre d'action qu'il te reste. peut-etre dans ton tableau dois tu inclure une colonne avec ton nombre d'action restant

[caperni]

Envoyé par alsimbad

Code :

=SOMMEPROD(($A$2:A7=A7)*($C$2:C7="A")*($E$2:E7)*($D$2:D7))/SOMMEPROD(($A$2:A7=A7)*($C$2:C7="A")*($D$2:D7))

j'obtiens la même formule que corona et qui fonctionne sur toutes les lignes et qui est étirable

Bonjour Alsimbad.

Alors je ne comprends pas la formule, pourquoi dans le 1er sommeprod met-on $A$2 puisque la référence de mon action est en A5?

Quelques réponses à tes questions :
Le sens achat ou vente est bien indiqué (A ou V en colonne C). La formule fait d'ailleurs référence aux "A" en C.

Pour le calcul du PR : que je sois bénéficiaire ou que je vende à perte, mon PR reste le même pour les actions restantes => dans tous les cas je les ai acheté à un certain prix, qu'importe le prix de vente des "copines vendue". On ne modifie le PR d'action possédées qu'en en achetant de nouvelles, le PR augmente si on rachète plus cher et diminue si on rachète mon cher (je sais qu'à priori deux écoles existent, en tout cas c'est mon hypothèse de travail).

Pour récupérer le PR, je pense effectivement que le mieux est d'utiliser une fonction de recherche.

Et je suis également d'accord avec toi, j'arrive à la conclusion qu'il me faut également une colonne avec les actions "en possession" => pour pouvoir calculer le PR d'actions rachetées après une vente (ça devient tordu).

[alsimbad]

Envoyé par caperni

pourquoi dans le 1er sommeprod met-on $A$2 puisque la référence de mon action est en A5?

tout simplement parce qu'on ne connait pas ton tableau si ton tableau commence a la cinquieme ligne c'est A5, tu n'as qu'a remplacer toutes les ref qui sont en X2 pr X5

[caperni]

Envoyé par alsimbad

tout simplement parce qu'on ne connait pas ton tableau si ton tableau commence a la cinquieme ligne c'est A5, tu n'as qu'a remplacer toutes les ref qui sont en X2 pr X5

J'avais mis mon tableau dans le message de départ, mais il a été supprimé par le modérateur.
La ligne 2 est la 1ere opération, la plus ancienne.
La ligne 3 est la seconde...
La ligne 5 est la 4eme opération et ainsi de suite...

[alsimbad]

la formule va pour toute les lignes de la plus ancienne a la derniere, elle te donnera le PR au jour du dernier achat, simplement il faut la modifier pour tenir compte des ventes
peut etre qu'il te faut faire un tableau séparé, un tableau de synthese de tes action, qui irait pecher les valeurs de reviens de tes action dans le tableau de suivi, mais le pb est le même, comment tenir compte du nombre d'action restante, ced d'une vente, sans prendre en compte la valeur que donne a l'action la vente en question

[caperni]

Envoyé par alsimbad

comment tenir compte du nombre d'action restante, ced d'une vente, sans prendre en compte la valeur que donne a l'action la vente en question

J'utilise le code suivant :

Code :

=SOMME.SI.ENS(F$4:F6;C$4:C6;[@Actions];D$4:D6;"achat")-SOMME.SI.ENS(F$4:F6;C$4:C6;[@Actions];D$4:D6;"vente")

Avec en colonne F le nombre d'action concernées par les différentes opérations et en D le sens de l'opération, vente ou achat. J'ai un suivi à jour du nombre d'actions possédées après l'opération sans m'occuper du prix de vente.