macro excel pour suivi de clients en portefeuilles

Question

Bonjour,

Je travaille actuellement sur un fichier excel permettant le suivi en portefeuille de clients.
Chaque client a un portefeuille décomposé en 2 parties : un fonds euro et un fonds actions.
Le fonds action peut etre composé de plusieurs investissements différents, chacun inscrit sur une ligne différente.
Au niveau de la présentation, la feuille de travail affiche sur une ligne les données du client (nom, valeur totale du portefeuille, valeur du fonds euro, pourcentage investi en fonds action...) et ensuite en allant à chaque fois à la ligne chaque investissement réalisé dans le fonds action. Le client suivant est séparé par un saut de ligne.

J'essaie de faire une macro permettant de prélever des frais de gestion (en gros diviser des cellules par un nombre fixe) sur le fonds euro et ensuite de recalculer la valeur du contrat (somme du fonds euro et du fonds action).

Je dois pour cela selectionner chaque client, renvoyer les cellules correspondantes aux investissements en action, les diviser par un nombre prédéfini (disons un prélèvement de 0.25%), faire la somme des nouvelles valeurs investies en action, et recalculer le montant total du contrat (comme somme du fonds euro et du fonds action).

La difficulté réside dans le fait que le nombre d'investissement diffère d'un client à un autre, et qu'il peut etre amené a changer lors d'une opération d'arbitrage (basculement d'un investissement en action vers le fonds euro, et suppression de la ligne correspondante). Je dois faire une somme de cellules contigues dont le nombre peut varier et je bloque pour le moment.

J'ai deja réalisé cette macro d'arbitrage et c'est uniquement pour le prélèvement des frais de gestion et le recalcul de la valeur des contrats que je fais appel a vous.

En espérant (bien que j'en doute) avoir été assez clair, je vous remercie par avance de votre aide.

Tuning Max · Answer

Concrètement, d'un point de vue programmation (et non financier) quel est ton problème?

cs_jfloyd · Answer

Oui alors d'un point de vue programmation :

Sur une ligne j'ai les données d'un client sur les 7 premieres colonnes, puis pour la 8ème colonne je passe a la ligne et j'ai un certain nombre de valeurs dans les colonnes suivantes. Je vais ensuite encore a la ligne en fonction du nombre d'investissements différents.

En gros chaque ligne correspond à un investissement qui a une valeur, et je cherche a faire la somme de ces investissements. La valeur de chaque investissement est en colonne 12 de chaque ligne (a part la 1ere ligne ou la cellule est vide puisque je passe a la ligne pour chaque investissement).

Chaque client est séparé du suivant par un saut de ligne.

Le probleme comme je le disais c'est que le nombre de lignes a sommer diffère pour chaque client. Par exemple le 1er client peut avoir 3 investissements, le 2nd zéro, le 3ème 1, etc... Et autre difficulté le nombre de ces investissements peut varier, c'est a dire que le 1er client avec 3 investissements peut plus tard en avoir 5 par exemple.

Je cherche donc a faire une macro qui pour chaque client parvienne à repérer les lignes d'investissements de chaque client (ex: client 1 : 5 investissements).
Puis diviser la colonne 10 de chaque ligne d'investissement par une variable ( la colonne 10 correspond a un nombre de parts, si la valeur d'une cellule de cette colonne change, la colonne 12 renvoyant la valeur de l'investissement s'ajuste).
Ensuite la macro doit faire la somme des investissements de chaque client, soit la somme des valeurs de la colonne 12 (dans l'exemple du client 1, faire la somme de la valeur des 5 investissements)
Enfin une derniere somme a faire : la somme du résultat précédent et de la valeur de la cellule (ligne du client, colonne 6) à reporter en cellule (ligne du client, colonne 4)

Cette opération doit ensuite etre répétée pour chaque client.

Schéma simplifié de la feuille excel:
 Nom client1 / Agence / Valeur contrat /Valeur fonds euro
                                                                                       Investissement 1: valeur
                                                                                       Investissement 2 : valeur
                                                                                       Investissement 3 : valeur

Nom client2/Agence / Valeur contrat/ Valeur fonds euro
                                                                                       Investissement1: valeur

Nom client 3 .....

La difficulté est que je n'arrive pas à programmer quelque chose qui puisse s'adapter au nombre d'investissements variables. En gros je n'arrive pas a programmer la somme des investissements et à l'insérer dans une boucle While pour la répétition pour chaque client.

Je suis débutant en VB, je n'ai peut etre pas bien envisagé le problème, donc je pense que le bout de programme que j'ai écrit pour l'instant ne vous servirait a rien.

J'espere cette fois avoir été clair, désolé pour la longueur, et merci d'avance pour l'aide, le code, les idées ... que vous pourrez m'apporter.

Tuning Max · Answer

Question : Est ce que pour chaque ligne d'investissement, le nom du client est repris? 
Dans tout les cas j'aime autant te prévenir que le traitement de ta fonction va être longt! Peut tu déjà nous écrire le bout de programme que tu as, histoire de mieux nous rendre compte?

Dolphin Boy · Answer

Salut, si le nom des clients n'est pas repris à chaque ligne alors voici un bout de code qui te permettra de compter le nombre d'investissement de chacun : Sub Macro1() Dim i As Integer Dim nb_Investissements As Integer For i = 1 To ActiveCell.SpecialCells(xlLastCell).Row If Cells(i, 1) <> "" Then 's'il y a un nom de client nb_Investissements = 0 Else If Cells(i, 12) <> "" Then 's'il y a un investissement on compte nb_Investissements = nb_Investissements + 1 Else MsgBox nb_Investissements 'on affiche le nb d'investissements End If End If Next MsgBox nb_Investissements 'ça c'est pour le dernier client de la liste End Sub C'est juste un début. A partir de là, tu pourras faire tes calculs, sommes et autres.

cs_jfloyd · Answer

Merci pour vos réponses. Quelques précisions : Les investissements ne sont pas marqués sur la meme ligne que le nom du client Ainsi par exemple j'ai un client dont le nom et la valeur du contrat sont en ligne i. Ce client a 3 investissements, le 1er investissement est en ligne i+1, le 2ème en i+2 et le 3ème en i+3. Le nom du client suivant, dans ce cas la, est en ligne i+5 (3lignes pour les investissements plus un saut de ligne). Dolphin boy dans ton code je ne vois pas comment je pourrais intégrer la somme investissements pour chaque client, de manière a ce qu'elle soit traitée dans la boucle While. Voici un bout du programme que j'avais : Dim S as variant For each S in range ("A2:A600") 'je prends 600 au hasard parce que je n'arrivais pas à prendre le dernier client If len(S)<>0 then S.select i=activecell.row activecell.offset(1,11).select if activecell.offset(1,0).value<>0 then range(activecell, activecell.end(xldown)).select Cells(i,4).value = application.worksheetfunction.sum(selection) + cells (i,6).value Next

Dolphin Boy · Answer

Je crois que j'ai bien compris où se trouvent inscrits les investissements, c'est pourquoi mon bout de code ne tient pas compte de la ligne où se trouve le nom du client pour le comptage. Pour le calcul du total des investissements de chaque client, juste 2 ou 3 lignes à rajouter (voir bout de code suivant). Sub Macro1() Dim i As Integer Dim nb_Investissements As Integer Dim montant_Investissements as double For i = 1 To ActiveCell.SpecialCells(xlLastCell).Row If Cells(i, 1) <> "" Then 's'il y a un nom de client nb_Investissements = 0 montant_Investissements = 0 Else If Cells(i, 12) <> "" Then 's'il y a un investissement on compte nb_Investissements = nb_Investissements + 1 montant_Investissements = montant_Investissements + Cells(i, 12).Value Else MsgBox nb_Investissements & " investissements pour un montant de : " & montant_Investissements " End If End If Next MsgBox nb_Investissements & " investissements pour un montant de : " & montant_Investissements " End Sub

cs_jfloyd · Answer

Merci Dolphin Boy

J'ai testé ton code mais j'ai encore un petit problème.

J'ai rajouté entre les 2 end if le bout de code suivant (pour calculer la valeur totale du contrat) : 
cells(i,4).value = montant_investissements + cells  (i,6).value

J'ai également modifié un petit quelque chose :
montant_Investissements = montant_Investissements + Cells(i, 12).Value  en :
montant_Investissements = montant_Investissements + Cells(i+1, 12).Value

Le problème est que l'éxécution de la macro ne prend en compte que le premier investissement pour chaque client lors du calcul de la valeur totale du contrat.
Si un client a 3 investissements il ignore donc la valeur des investissements 2 et 3.

Ne faudrait-il pas faire une boucle FOR pour calculer la somme de ces investissements?

Voila, si vous voulez des précisions, n'hésitez pas.

Et si vous comprenez mon problème, merci de votre aide.

Dolphin Boy · Answer

Excuse-moi, je n'ai pas pu te répondre plus tôt. Si je comprend bien, la cellule cells(i,4) doit contenir le montant total des investissements avec i = la ligne où se trouve le nom du client ? Dans ce cas : Sub Macro1() Dim i As Integer Dim nb_Investissements As Integer Dim montant_Investissements as double Dim ligne_Infos_Client As Integer For i = 1 To ActiveCell.SpecialCells(xlLastCell).Row If Cells(i, 1) <> "" Then 's'il y a un nom de client nb_Investissements = 0 montant_Investissements = 0 ligne_Infos_Client = i Else If Cells(i, 12) <> "" Then 's'il y a un investissement on compte nb_Investissements = nb_Investissements + 1 montant_Investissements = montant_Investissements + Cells(i, 12).Value Else Cells(ligne_Infos_Client, 4).Value = montant_Investissements End If End If Next Cells(ligne_Infos_Client, 4).Value = montant_Investissements End Sub Si le nombre d'investissements ne t'es pas utile, tu peux supprimer les lignes où il y a nb_Investissements.

cs_jfloyd · Answer

Merci Dolphin Boy, excuse moi de pas avoir répondu plus tôt mais j'ai pas trop eu le temps de m'occuper de mon programme cette semaine.


J'ai compris ton raisonnement sur mon problème et je vais tester ton code demain pour etre sur que tout fonctionne bien.

Merci encore.

Macro excel pour suivi de clients en portefeuilles

9 réponses

Votre réponse

Discussions similaires