J’aurais besoin de vos lumières pour remplir automatiquement un tableau nommé « TbOperations » en utilisant du code VBA. Les données sources proviennent de deux autres tableaux : « TbMontantInvesti » et « TbHistoriqueTaux ».
Voici un peu plus de détails sur :
TbMontantInvesti : Contient les dates d’investissement et les montants pour chaque investisseur.
TbHistoriqueTaux : Contient les plans d’investissement, les taux de gains et le rendement hebdomadaire.
TbOperations : C’est le tableau que je veux remplir automatiquement.
Une procédure appelée « DeterminerPeriodeInvestissement » qui calcule la semaine acceptée pour chaque date d’investissement dans TbMontantInvesti est fournie dans le fichier exemple.
Pour infos, la colonne « RepartitionSemaine » dans TbOperations sera calculée par une procédure spéciale en plusieurs étapes, qu’elle sera créer plus tard. Cette procédure sera appelée lors de l’insertion ou de la mise à jour des semaines de rendement dans TbOperations.
Est-ce que quelqu’un aurait des idées sur la meilleure façon d’aborder ce problème ? Des astuces VBA pour rendre le code efficace ? Ou peut-être une approche alternative à laquelle je n’aurais pas pensé ?
Merci d’avance pour votre aide ! N’hésitez pas si vous avez besoin de plus d’informations.
si un retraite n’est pas fait le vendredi alors il est fait le vendredi précédent et pour un dépot qui n’est pas fait le vendredi, il est fait le vendredi suivant.
voir feuille « Chronologie » TraitementOperations.xlsm (64,4 Ko)
RendementSemaine : =SI([@Quoi]=« update »;_xlfn.XLOOKUP([@Date];TbRendement[Date];TbRendement[RendementSemaine];« ? »;0);« - »)
-Questions sur les colonnes « Solde1 » et « Solde2 » et l’impact des retraits dans TbMontantInvesti
Quel est le rôle des colonnes « Solde1 » et « Solde2 » dans le tableau TbOperations ?
J’ai remarqué qu’un retrait de -100 en date du 01/06/2024 n’est pas classé chronologiquement dans TbMontantInvesti. Est-ce que cela pourrait affecter la procédure Atlas() pour le remplissage de TbMontantInvesti si l’ordre des dépôts doit correspondre à cette date ?
Pour les dépôts, ils doivent être effectués du vendredi au dimanche pour être inclus dans l’investissement de la semaine en cours (du samedi au vendredi). Les dépôts réalisés en dehors de cette période seront comptabilisés pour la semaine suivante.
Tu as raison, j’ai oublié de traiter les retraits dans ce contexte.
En attente des tests à effectuer sur le nouveau code, voici un exemple de la logique du calcul des répartitions des gains, sur les investisseurs de montants dans une même date et du même rendement hebdomadaire :
Exemple fourni:
Supposons que nous ayons deux investisseurs AA avec un montant de 520€ et BB avec un montant de 100€
Ces données sont récupérés de TbMontanInvesti.
Etape 01 :
Gain avec investissements séparés :
1.a Gain Individuel pour des montants séparés :
= MontantInvestiIndividuel * RendementSemaine * tauxPlan
AA : = 520 * 32.50% * 65% = 109.85 (TauxPlan=65% parce que 520 est <= 3000)
BB : = 100 * 32.50% * 60% = 19.5 (TauxPlan=60% parce que 100 est <= 500)
1.b Total des gains séparés = 109.85 + 19.5 = 129.35
Etape 02 :
Gain avec investissement combinés :
2.a Gain combiné pour des montants combinés :
= MontantInvestiTotal * RendementSemaine * tauxPlan
AA et BB = (520+100) * 32.50% * 65% = 130.975 (TauxPlan=65% parce que 620 est <= 3000)
2.b Total gain combiné : 130.975
Différence de gain entre étape 01 et étape 02 :
3.a =130.975 - 129.35 = 1.625
Proportions de chacun : (Proportions récupérées de TbMontanInvesti)
4.a AA : 520/620 = 0.83870968
4.b BB : 100/320 = 0.16129032
Gain plus pour chacun :
5.a AA : 1.625 * 0.83870968 = 1,36290323
5.b BB : 1.625 * 0.16129032 = 0.26209677
AA à gagner 111.21 euro en montants combinés que 109.85 euro en montants séparés
BB à gagner 19.76 euro en montants combinés que 19.5 euro en montants séparés
Affichage dans colonne RepartitionSemaine:
AA : 111,21 € | BB : 19,76 €
une réponse « quick and dirty », je n’ai pas encore vérifié tout. TraitementOperations.xlsm (69,5 Ko)
EDIT : oubliez ce « quick and dirty », j’ai ajouté du commantaire dans la macro.
L’ancienne macro « Atlas » est inutile maintenant
Je fais encore les tests, mais j’ai quelques questions :
La formule =SI(ESTNUM([@RendementSemaine])>0;B8+7;"") dans la feuille HistoriqueTaux renvoie toujours une valeur vraie, même si j’entre 0, car SI(VRAI>0;B8+7;"") est toujours vraie. Pourquoi la formule ne fonctionne-t-elle pas pour exclure les zéros ?
Le tableau TbHistoriqueTaux ou TbRendement (Je ne sais lequel est utile pour le calcul, puisque les deux contiennent la colonne 'RendementSemaine') doit être mis à jour et complété avec les semaines manquantes après chaque calcul de partition des gains dans la feuille « Chronologie » ?
Est-il possible d’ajouter, dans la colonne ‹ Quoi ›, ‹ Dépôt › pour les apports supplémentaires (valeurs positives) et ‹ Retrait › pour les valeurs négatives dans le tableau TbChrono ?
Le partage des gains hebdomadaires, suit-il correctement les étapes de calcul des gains comme illustré dans l’exemple fourni ?
Je ne comprends pas les lignes suivantes avec les valeurs utilisées :
If Abs(Delta) > 0.00000001 Then aX2(2, j) = Delta * aX(3, j) (diviser ce delta entre ces participants)
If Abs(Delta) > 0.0000000001 Then MsgBox Delta (signaler si c’est plus grand que prévu)
j’ai supprimé tout ce qui n’est plus utile, comme ça il n’y a plus de confusion pour les 2 premier points
depot/retraite = okay
ce calcul, je ne l’ai pas testé mais je crois que oui, j’ai même ajouté un tableau avec les 2 types de gain par investisseur sur la feuille « MontantInvesti »
excel calcule avec une précision de 15 chiffres, donc il y a toujours des erreurs à cause des arrondis. Si ces erreurs sont trop petites, on peut les ignorer. Le msgbox vous signalera que le cumul des ces erreurs est déjà 0.000…01 €, bon, c’est ridicule, mais pour le moment il n’a pas encore signalé cette erreur. En réalité une erreur de 0.01 ou 0.001€ suffit, je suppose
If Abs(Delta) > 0.001 Then MsgBox Delta` (signaler si c’est plus grand que prévu) TraitementOperations.xlsm (57,0 Ko)
J’ai réintégrée le tableau TbMontanInvesti avec la colonne des proportions calculée par la procédure Atlas(), afin de faire une comparaison avec les taux calculées dans TbChrono.
Mon objectif de calculer les proportions dans « TbMontantInvesti » et d’appliquer ces proportions au gain de chaque semaine jusqu’à la prochaine date d’investissement. Je me demande si cette approche est correcte du point de vue comptabilité. En résumé, il s’agit d’appliquer les proportions basées sur le total des apports supplémentaires ou des montants investis à une date donnée, et de maintenir ces taux jusqu’à ce qu’il y ait un changement dans le total du capital investi par tous les investisseurs. donc les proportions sont calculées et déterminées au moment d’une nouvelle date d’investissement et sont maintenues jusqu’à nouvelle date d’investissement.
Est-ce une méthode appropriée pour la gestion des proportions des gains ?
J’ai essayé d’ajouter des commentaires et des Debug.Print pour comprendre le code, dans le fichier ci-joint : TraitementOperations-v5.xlsm (55,7 Ko)
nouveau fichier :
pour un dépot ou retrait, le vendredi est la clé, c’est une sorte de date de validation.
Donc si ce jour n’est pas un vendredi, ce jour sera décalé. Pour un dépot, le vendredi, samedi et dimanche seront le vendredi, pour les autres jours ce sera le vendredi suivant. Pour un retrait et ce n’est pas un vendredi, c’est le vendredi précédent.
Puis, pour les calculation, le premier calcule du vendredi, c’est le « Update » = le montant de la semaine passée * le TauxPlan * le rendement et ce gain est à diviser entre les participants.
Les calculations suivantes du vendredi sont les depots et retraites d’argent avec ce vendredi comme date de validation. Si on a plusieurs retraits/dépots le même vendredi, la repartition du dernier est considéré comme celui a utilisé.
J’ai utilisé une MFC (ligne rouge entre les semaines) dans le tableau « TbMontantInvesti2 » pour mieux visualiser cela. J’éspère que vous avez le même idée sur ce sujet … .
L’autre tableau « TbMontantInvesti », vous pouvez le supprimer.
Pour la gestion des gains, je l’ai ajouté dans un nouveau tableau sur la feuille « Calcul ». Vous voyez le vendredi et l’ID de l’investisseur, son montant, sa part, son Tauxplan, le Tauxplan combiné, le rendement, puis son gain (avec son Tauxplan) et le gain supplémentaire à cause du Tauxplan combiné.
A droit, j’ai 3 colonnes dans lesquelles, je vous montre mieux cela. Colonne K est la gain en utilisant le TauxPlan à lui. La colonne L, c’est le surplus à cause d’un Tauxplan combiné plus élevé. Mais une partie de ce surplus est à repartir avec les autre participants. Voir colonne M.
J’ai aussi utilisé une MFC pour les vendredi ici. Si on fait la somme de la colonne M pour chaque vendredi, le resultat sera 0 (ou bien, parce que c’est excel 0.0000000…1 (voir résultat dans M1). Colonne I = colonne L + colonne M. Les lignes vertes recoivent un surplus à cause d’un tauxplan combiné plus élevé(colonne L), mais elles donnent une partie de ce surplus (colonne M) aux lignes blanches.
Êtes-vous d’accord avec cette façon de penser ? TraitementOperations-v5.xlsm (77,1 Ko)
Désolé pour la réponse tardive, j’étais en déplacement.
Donc les vendredi représente ou fais référence aux semaine qui suivent les dates d’investissement ?
Normalement, TbOperations ou TbChrono, est alimentés par TbMontantInvesti, TbRendement et TbHistorique et non pas l’inverse ?
Pour un retrait et ce n’est pas un vendredi, c’est le vendredi précédent. Normalement c’est le même traitement affecté aux « dépôts » ?
Pour la répartition des gains, elle repose sur le calcul des parts de la différence entre la somme des gains individuels séparés et le gain combiné pour un même rendement semaine et une même date d’investissement.
J’aimerais prendre dans la répartition des gains, les investissement à période préétablie (ex:6 mois; 12 mois; …)
la condition de type d’investissement est que les dépôts/retraits ne sont pas autorisés durant la période prédéfinie.
Si on a plusieurs retraits/dépôts le même vendredi, la répartition du dernier est considéré comme celui a utilisé. Comme ça on pénalisera le dernier investisseur, car le montant de sa transaction est déduit de son capital investi tandis que les autres investisseurs qui ont effectués des transactions la même journée, auront plus de gains alors que leur capitales individuels n’ont pas été touchés (calculs sur des faux capitales individuels) !? et puis comment definissons nous la derniere transaction effectuée, puisqu’on a que la meme date pour toutes les transactions ?
J’éspère que vous avez le même idée sur ce sujet .... : je n’ai pas encore d’idées sur le traitement à appliquer sur le cas de plusieurs transactions (dépôts/retraits) pour une même date.
Dans la colonne L, je vois des vides, malgré les gains combinés ?
Dans la colonne M, je vois des valeurs négatives parfois positives et autres vides ?
Le tableau dans la feuille Calcul contient des comparaisons intéressantes, à comprendre bien sur …
oui, la base est toujours le vendredi, s’il y a un retrait/dépot pendant la semaine, c’est toujours validé le vendredi précédent ou suivant.
Oui, TbMontantInvsti et TbRendement fournissent les données. Dans ce tableau TbMontantInvesti, la plage bleu, ce sont des calculations dans ce même tableau, la plage verte, c’est le résultat qu’Excel a récherché dans le tableau TbChrono, donc c’est le vendredi après validation et traitement de tous les transactions… Si vous ajoutez une nouvelle transaction, les valeurs que vous voyez là sont oubien érronées oubien ne pas encore mis à jour, parce que la macro « Chronlogie » doit encore faire son boulot. Donc, ici, pour la plage verte, c’est l’inverse !
pour un retrait, c’est toujours le vendredi précédent quand ce n’st pas un vendredi, pour un dépot, tous ceux du vendredi au dimanche, sont validés le vendredi (donc au max 2 jours décalé vers le vendredi précédent. A partir du lundi, c’est pour le vendredi suivant. (C’est la colonne « Vendredi » du TbMontantInvesti)
*Oui, la répartition des gains s fait comme ça et les détails, vous pouvez les voir dans le tableau « tabel7 » de la feuille « Calcul »
ça, je ne comprends pas. Donc le gain supplémentair est pendant 6 ou 12 mois pour la « banque/pot ». Cela veut dire que je dois savoir le minimum de chaque personne pendant ces 6 ou 12 mois. Supposons qu’elle a 1.000€ pendant ces 6 mois sauf 1 jour (sans importance quand) de 100€, le gain sera calculé sur de 100 € ??? Oei, …je m’étouffe. Et je prends la date de la validation (le vendredi) ou la date du transaction, donc colonne D ou G de TbMontantInvesti (peut-être l’influence de cela est minimal).
.
Le rendement de la semaine précédente et la repatition des gains est fait comme première calculation, le « update », le vendredi, voir tableau TbChrono. C’est après cette calculation que les dépôts et retraits sont traités. Ce sont donc 2 choses différentes. Par exemple 31/5 (ligne 17), c’est la repartition de la semaine précédente. Et puis, on a 2 dépôts et un retrait. Imaginez qu’Excel fait cela l’un après l’autre pendant une seconde. C’est le résultat après la dernière transaction qui compte pour les 3, donc ligne 20, cellule S20.
Colonne L est vide quand cette personne a le même TauxPlan que le TauxPLan combiné.(colonne E = colonne F), donc pour elle, il n’y a pas de gains sur son montant cumulé, mais il y aura à cause des autres personnes. Donc pour elle, ce sera un chiffre positif dans la colonne M et pour les autres un chiffre négatif. La somme de la colonne M pour un vendredi est 0,
.
sorry, apparament, je dois attendre àprès 3 postes que quelqu’un autre réagit …et puis, c’est de nouveau à moi … TraitementOperations-v6.xlsm (74,1 Ko)
En comparant les proportions obtenues dans la dernière version avec celles calculées par la macro Atlas(), j’ai remarqué une différence : tes proportions sont basées sur (MontantInvesti individuel + Gains) / (MontantTotalInvesti + GainsTotal), alors que les miennes sont basées uniquement sur MontantInvesti individuel / MontantTotalInvesti. Elles sont calculées une seule fois lors d’un nouvel investissement et ne changent pas avec les variations des gains hebdomadaires.
La colonne J « spare », toujours vide, représente quoi dans le tableau « TbCalcul » ?
Parfois quand je lance la macro Chronologie(), l’id de l’investisseur (1) n’est pas affiché dans les résultats obtenus des colonnes « Répartition Gain » et « Répartition » de TbChrono ?
J’ai essayé avec deux montants investis : 100 et 340.43 pour le même investisseur 1, alors le premier montant 100 est traité a part et le deuxième 340.43 a part, et il n’y avait pas de cumul de montants investis, en plus, l’id 1 n’est pas affiché dans le traitement du monantant 340.43
Re,
En ce qui concerne l’écart qui déclenche un message MsgBox, une fois la condition est vrai : Somme Montants Cumulés - Somme Colonne "Salde Fin" > 0.0000001
Que fait le code lorsque l’écart dépasse la tolérance définie ? Doit-il revenir au début pour recalculer, ou revoir les formules dans TbChrono ? Sinon, faut-il intervenir pour corriger l’écart avant de poursuivre les autres calculs ?
j’ai refait l’exercise et maintenant tout est sur une feuille, la feuille « Brouillon », pour l’instant, il ne faut pas encore regarder aux autres feuilles, je n’ai pas encore fini là.
feuille « Brouillon »,
colonne A : des vendredis successifs (la mise à jour sera fait par une macro plus tard
Colonne B, le rendement de ce vendredi (feuille « rendement »
Colonne C : le TauxPlan du montant de la colonne D
Colonne D : le montant qu’on a utilisé cette semaine = le solde de la semaine passée (ligne précédente & colonne AF ), on verra cà directement
colonne E:I : vous parliez d’une période d’attente avant qu’on pouvait profiter du système des montants cumulés. Bon, avec la cellule G1, vous pouvez choisir cette périodes (en semaines). Si vous prenez là, la valeur 0, alors pour la repartition du solde des gains sur la partie cumulée, on prend le montant de cette personne de la semaine passée (= ligne précédente, colonnes AA:AE). Si on choissit 2, cela veut dire que vous prenez la valeur minimale de cette personne de la semaine passée + 2 semaines supplémentaires = le minimum dès 3 semaines.précédentes. Si cette valeur est 0, alors cette personne n’aura pas un gain supplémentair dû au cumul des montants.
Colonne J : le gain total avec le montant de la colonne D, son tauxPlan (C) et le rendement (colonne B)
colonnes K:O = les gains individuels, dû au gain avec son propre montant du vendredi passé (voir colonne AA:AE de la ligne précédente) multiplié avec le rendement (B) et son propre TauxPlan + le produit de la différence entre le tauxplan cumulé et le tauxplan individuel * le montant décalé (colonnes E:I) * le rendement (B)
colonne P, je suppose que vous ne voulez pas qu’une personne reçoit une pourcentage du gain d’une autre personne, alors tout ce qui n’est pas attribué aux participants est pour la banque. (D’accord ???)
PS. la gains individuels sont arrondis à 2 chiffres après la virgule, le gain du banque contient donc aussi le cumul de ces arrondis.
colonnes Q:U : la question de hier, qu’est-ce qu’on fait avec ces gains, on les réinvestit ou cela est mis dans un pot individuel. Vous pouvez choisir cela avec la liste déroulante de la cellule Q1, donc vous choississez « intérêt simple » et les gains seront déposé dans les pots individuels des colonnes Q:U, oubien vous choississez « intérêt composé » et les gains seront cumulés dans les montants des colonnes AA:AE; A vous le choix.
colonnes V:Z : le solde des dépôts ou retraits des montants par ces personnes validé ce vendredi. Ce solde est aussi cumulé dans les colonnes AA:AE
colonnes AE = les soldes des personnes après tous les calculs du vendredi et donc les montants qu’on utilisera la semaine suivante (éventuellement corrigé = le minimum de quelque semaines) = solde de la semaine passée + le gain (en cas d’intérêt composé) + le solde des dépots&retraits.
la colonne AF = la somme des ces soldes individuels, = le montant qu’on utilisera la semaine suivante = colonne D de la ligne suivante.
les colonns AG:AK = les pourcentages des soldes individuels dans cette somme
Que pensez-vous ? C’est plus facile à comprendre ce qu’on le fait comme ça ?
Si vous n’avez pas des questions, la chose suivante à faire, c’est relier ces montants vers le tableau « tbMontantInvesti »
C’est surtout la partie avec la « banque » qui peut donner des malentendus, je suppose. TraitementOperations-v6.xlsm (87,2 Ko)
Maintenant, c’est pour 5 participants, mais s’il y en a plus, alors, on ajoute des colonnes au milieu, comme ça, les sommes s’adaptent tout seul. (peut-être je crée une macro pour cela ?)
C’est vraiment surprenant ! C’est plus détaillé que je ne l’imaginais, même si je ne comprends pas tout ce qui est affiché, pour le moment
Peux-tu adapter le classeur à la version Excel 2019 ? À partir de la colonne C, je n’obtiens que des #NOMS? et des #DIV/0!.
Actuellement, il y a 6 participants.
Quand tu parles d’ « intérêts simples/composés », fais-tu référence aux « bénéfices combinés/non combinés » ? C’est-à-dire réinjecter ou non le bénéfice hebdomadaire dans l’investissement de la semaine suivante ?
Peux-tu m’expliquer ce que représente le décalage de 5 ? Cela concerne-t-il la durée d’un plan d’investissement, comme « Investisseur » (6 mois à 75%) ou « ELITE » (6 mois minimum à 75%) avec blocage des dépôts/retraits pendant cette période ?
Comment gérer un investisseur qui peut se retirer à tout moment, que ce soit dans un plan ouvert ou un plan à durée fixe ?
Peut-on envisager d’autres catégories d’investissement, comme l’immobilier ou les actions en bourse, par exemple ?
Comment gère-t-on les périodes avec des pertes ?
Comment gère-t-on les changements de taux plan en cours d’investissement ?
excel2019, dans la colonne R de « Montantinvesti », j’utilisais « unique » dans une formule pour recupérer les ID_investisseurs « uniques ». et 2019 ne connait pas cela. Donc maintenant dans la macro « Brouillon », les vendredis sont ajouté dans la colonne A et les « ID_investisseurs » sont ajouté dans la ligne 12, chaque fois au dessus une entête qui termine avec « 01 ». Comme ça les formules savent l’investisseur. Maintenant c’est pour 10 personnes.
Pour ces intérêts, comme je ne suis pas francophone, peut être on peut utiliser des termes plus adaptés, mais l’idée est effectivement de pouvoir choisir si on réinjecte c bénéfice ou pas. pendant la semaine suivante. Je vois maintenant que vous vous demandez déjà de faire ce choix pour chaque personne individuelle, c’est possible. TraitementOperations-v6.xlsm (102,2 Ko)
