Intégration d'opérations financières récurentes dans un journal de compte
Bonjour,
Je suis en cours de création d'une application pour suivre mes comptes bancaires.
J'ai un onglet par compte (en tout, 11 onglets). Sur ces onglets, je note chaque opération qui touche à ce compte. Comme tout le monde, j'ai des opérations récurrentes (avec des fréquences variables : annuelles, mensuelles....).
Lors de la saisie de l'opération, par exemple : une facture mensuelle de téléphonie, j'inscris dans une colonne "Mensuel". Dans une autre colonne, j'obtiens la date de la prochaine opération.
Je voudrais donc récupérer cette nouvelle date pour qu'il affiche automatiquement une nouvelle ligne dans mon tableau en recopiant le type de dépense, le poste, le tiers et le montant.
Pour le moment, je compte filtrer les données présentes dans la colonne "Prochaine échéance" et faire un copier/coller des lignes pour obtenir le report des opérations régulières. L'idéal serait que cela soit fait automatiquement. De plus, j'aimerai que ces nouvelles lignes s'affichent dans l'ordre chronologique.
Je joins (enfin j'espère), le fichier en question.
J'aurai aussi une autre demande qui consiste à enregistrer un virement de compte à compte. Par exemple, si j'envoie le dd/mm/aaaa 100€ du compte A vers le compte B, je voudrais que le compte A ait une ligne "virement vers" "Compte B" et que le compte B ait la ligne réciproque, à savoir "Virement depuis" "Compte A".
En espérant que quelqu'un puisse m'aider !! Merci beaucoup.
Bonjour et bienvenue,
je n'ai pas accès à ton fichier, je t'ai fait la demande
ou alors mets le en lecture https://www.sheets-pratique.com/fr/cours/partage
J'aurai aussi une autre demande qui consiste à enregistrer un virement de compte à compte. Par exemple, si j'envoie le dd/mm/aaaa 100€ du compte A vers le compte B, je voudrais que le compte A ait une ligne "virement vers" "Compte B" et que le compte B ait la ligne réciproque, à savoir "Virement depuis" "Compte A".
Pour cette seconde question, tu peux adapter le code de cette réponse https://forum.excel-pratique.com/sheets/exporter-une-ligne-d-ecriture-selon-un-tiers-precis-148777/2...
Ce qui est complexe, c'est qu'en cas de changement A > B en A > C il faut répercuter les changements sur les 3 comptes A, B et C
Bonjour,
je t'ai autorisé l'accès il me semble. Merci de ton aide !
J'ai partagé le le fichier
https://docs.google.com/spreadsheets/d/1ys_4ytnbsaQOkszxn2jRWTygXrKVSJWLz-D3UxAR4sE/edit?usp=sharing
merci
bon, cela ne va pas être simple ... mais on va y arriver (il me faudra un moment car je risque de manquer de disponibilité)
au-delà de tes demandes, je vois que tu maîtrises pas mal, il y a peut-être quelques améliorations sur
- il est possible de faire des formules écrites une seule fois en tête avec arrayformula (mais j'ai essayé sur ton tableau, cela coince, il faudrait ajouter un critère sur date probablement)
=arrayformula(IF(I3:I="Mensuel";date(YEAR(C3:C);MONTH(C3:C)+1;DAY(C3:C));IF(I3:I="Semestriel";date(YEAR(C3:C);MONTH(C3:C)+6;DAY(C3:C));IF(I3:I="Annuel";date(YEAR(C3:C)+1;MONTH(C3:C);DAY(C3:C));IF(I3:I="Bimestriel";date(YEAR(C3:C);MONTH(C3:C)+2;DAY(C3:C));"")))))- il est possible pour la colonne F de faire une liste de validation conditionnelle selon la valeur de la colonne C https://forum.excel-pratique.com/sheets/liste-deroulante-dynamique-avec-fonction-indirect-153557#p94...
Mais bon, ce n'est pas le sujet, je vais regarder ...
Je te remercie de jeter un oeil, je continue avec mes compétences et vais me documenter
Peux-tu expliquer comment tu vois la duplication des opérations périodiques ?
- Quel est l'élément déclencheur ? un bouton en début de période ? (cf money que j'utilisais jadis)
- Et comment vois-tu prendre en compte les éventuelles modification de périodicité ? voire la suppression de la périodicité ?
Pour l'élément déclencheur: peu importe... Un bouton ? Oui pourquoi pas ?
L'idée serait que cela affiche les montants et divers libellés pour le mois d'après.
Pour modifier ou supprimer la périodicité, je voudrais juste avoir à changer la période de récurrence en " colonne I " et quand la dernière échéance serait arrivée, je laisserai la cellule de la " colonne I " à blanc.
J'ai regardé le sujet qui traite des virements. Sacré boulot !! J’essayerai de voir si je peux l'adapter mais je ne connais rien en programmation...
Je suis assez surbooké d'ici mardi prochain, je reviendrai peu sur ce fil d'ici là.
Merci de ton aide
Pour les transactions régulières, et pour simplifier à la fois les feuilles de compte mais aussi le script et la façon de lancer "pour le mois d'après", je te proposerais bien ceci
Un onglet séparé avec les prochaines échéances, le compte concerné (colonne A) et une case à cocher (colonne I).
Quand la case est cochée, la date change et la ligne est copiée dans le compte concerné. Si c'est en plus un virement régulier, la ligne est copiée 2 fois dans chaque onglet concerné.
Si tu es ok, je partirai de cette idée. Et on pourra simplifier la feuille des comptes.
Merci, ça me semble correspondre à ce que je cherche !
Pourquoi tu dis que l'opération est copiée 2 fois ??
Pourquoi tu dis que l'opération est copiée 2 fois ??
si il y a virement ! une fois dans le compte origine, une fois en destination
je ne l'ai pas encore pris en compte ci-dessous
/!\ Compte Joint n'est pas écrit de la même façon dans l'onglet et dans la liste !!! supprime l'espace final dans la liste ou ajoute le au nom de l'onglet
proposition sur la base de la copie d'écran ci-dessus
function onEdit(event) {
var f1 = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if (f1.getName() == 'Prochaines échéances' && r.getColumn() == 9){
if (r.getValue()==true){
var f2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(f1.getRange('A'+r.getRow()).getValue());
var origine = ["B","C","D","E","H"];
var destination = ["D","E","F","G","C"];
var derL = getLastDataRow(f2) + 1;
for (var i = 0; i < origine.length; i++){
f2.getRange(destination[i] + derL).setValue(f1.getRange(origine[i]+r.getRow()).getValue());
}
f1.getRange('G'+r.getRow()).setValue(f1.getRange('H'+r.getRow()).getValue())
r.setValue(false)
Browser.msgBox('Transaction enregistrée dans ' + f1.getRange('A'+r.getRow()).getValue() + ' ligne ' + derL)
}
}
}
function getLastDataRow(sheet) {
var lastRow = sheet.getLastRow();
var range = sheet.getRange("C" + lastRow);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
}Merci à toi,
je regarde ça dans l'am. je te redis. Merci de m'aider
Si tu veux trier une feuille
function trier(feuille,colonne){
var range = feuille.getRange(2, 1, feuille.getLastRow() - 1, feuille.getLastColumn());
range.sort({column: colonne, ascending: true});
}exemple
trier(f1,8)à ajouter après ou avant
Browser.msgBox('Transaction enregistrée dans ' + f1.getRange('A'+r.getRow()).getValue() + ' ligne ' + derL)
J'ai créé un nouvel onglet "Prochaines échéances" et ajouté les colonnes comme ta copie d'écran.
Je viens de copier:coller ton script dans l'éditeur (cf copie d'écran)
J'ai ensuite ajouter 3 opérations récurrentes et exécuté le script, mais je ne vois pas de résultat. Dois-je remplir autre chose ailleurs ??
Il faut enlever l'ancienne fonction myFunction
Il ne doit plus rester que onEdit (le script que je t'ai donné) qui doit se superposer sur tout le reste
J'ai regardé le sujet qui traite des virements. Sacré boulot !!
mais à mon sens trop complexe ! il fallait en effet
- pouvoir reporter les modifications apportées comme le montant, la date, le compte receveur, donc synchroniser les 2 opérations
- cela imposait par conséquent la mise en place d'un n° de virement inter-compte pour retrouver la trace
du coup, ma question :
- quelle est la proportion de virements réguliers / périodiques ? 50% ? 80 %
- ne faut-il pas simplement traiter au travers de l'onglet "Prochaines échéances" qui deviendrait "Prochaines échéances et virements" avec un code unique en plus de mensuel/bimestriel/etc. => ce serait plus simple à gérer
Tu as raison, je dois faire une dizaine de virements de compte à compte. je suis pour un intégration dans le même onglet.
Merci encore
Je viens d'essayer ce script et voilà la réponse...
Pourtant je ne vois aucune trace de macros.gs:2
Je suis désolé, ça doit être une erreur de manip de ma part
La macro est ce que l'on appelle une macro/script événementielle : elle se déclenche seule dès que tu cocheras la case en colonne I du nouvel onglet.
C'est formidable !!! Et en plus, ça affiche la date de la prochaine échéance. Une tuerie
Je vais pouvoir supprimer les colonnes J et M de mes onglets de compte alors ?
En effet, si tu peux intégrer les virements sur le même principe, je prends ! Pour ce cas, ne ne pas prévoir de récurrence. Je pense qu'un nouvel onglet avec deux lignes (1 pour le compte émetteur et 1 pour le receveur) devrait être suffisant. Pourquoi pas une sorte de formulaire à remplir (enfin ça c'est pour l'esthétique, un onglet serait déjà parfait).
J'ai aussi un autre problème. Comme tu as pu le constater, l'idée est aussi de faire un budget mensuel/annuel qui reprend toutes les catégories de dépenses/recettes (selon la nomenclature européenne) histoire de situer et de se forcer à épargner. J'ai donc un onglet spécifique "Budget". J'ai commencé à calculer les sommes mais je n'ai intégré que 3 comptes (les principaux) mais idéalement, il faudrait que j'arrive à inclure tous les comptes (j'en ai 11). J’utilise la formule "SOMME.SI.ENS" avec des critères de nom de comptes, de mois et de catégorie. Seulement, la formule finale va faire 3 pages et n'est pas optimale... Je comptais utiliser un système avec des sommes matricielles d'excel (somme sur plusieurs onglets) mais je ne trouve pas d'équivalent sur sheet. Une solution serait de rapatrier toutes les opérations (de tous les comptes) sur un seul et même onglet. Mais je trouve qu'un onglet par compte limite les erreurs et est plus visuel.
Je joins mon nouveau fichier pour que tu puisses voir précisément où j'en suis. J'ai rajouté une colonne en D "M+1" qui permet d'envoyer une recette ou une dépense dans le calcul de budget du mois suivant? Je suis payé en fin de mois et je fais démarrer, en général, mon budget mensuel à partir de cette date.
https://docs.google.com/spreadsheets/d/1iaQWdFxlPnFn66_4_0phahVhC4mAnv9VravJulwtHnM/edit?usp=sharing
Enfin, y-t-il un moyen pour créer autant d'onglets que de comptes? Dans l'hypothèse où l'on garde 1 onglet par compte. L'idéal serait de nommer les comptes sur la page accueil et que google créé les onglets correspondants. Ça, c'est la cerise sur le gâteau.
Et pour finir, je te remercie du temps que tu m’octroies, c'est très sympa de ta part de partager tes connaissances.