Vous travaillez sur la comptabilité de trésorerie et chaque jour, vous recevez un fichier recensant les opérations bancaires. Pour pouvoir attribuer les mouvements bancaires à chaque compte de tiers, vous devez passer en revue votre relevé lignes par lignes. Le problème, c’est que nous avons tous des clients qui font des virements avec des libellés difficilement identifiables comme « X0130Banque/Glu« …
Par chance, les clients utilisent toujours la même typologie lorsqu’ils émettent un virement bancaire. Mais comment analyser vos relevés bancaires rapidement sous Excel ? Pour répondre à cette question, nous allons apprendre à Excel à analyser vos relevés bancaires en fonction d’une table que vous mettrez à jour au fur et à mesure du temps.
Sommaire
Par où commencer ?
Le plus important dans ce projet est de faire en sorte que le code soit simple et rapide. Pour y parvenir, nous utiliserons un dictionnaire de données sous VBA. Cette analyse se fait en deux temps :
- Une table de transco est d’abord stockée dans la mémoire interne du PC.
- On demande ensuite à Excel d’analyser votre extraction bancaire afin d’indiquer en face le compte client attribué.
À noter : cette article vous explique comment coder une application Excel capable de décoder vos relevés bancaires. Pour plus de facilité, il vous est cependant possible de télécharger cette ressource directement :
Création du dictionnaire VBA
Pour que ce code fonctionne, vous devez au préalable activer la référence « Microsoft Office xx.x Object Library » depuis le menu VBA.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
'Version 1.00 Option Explicit 'accélère le code vba en demandant de préciser toutes les variables Dim celcour As Range, cell As Range 'variables Dim libelle As String, mot As String, compte As String Dim a As Integer, x As Integer, y As Integer Dim nombre As Double Public dico As Object Sub mettre_a_jour() 'code permettant la mise à jour du fichier sélectionné 'à noter : si le mot existe dans le dictionnaire, mais que le code ci-dessous ne le trouve pas 'alors il existe certainement des espaces en trop dans la table de transco 'il est donc important de veiller à supprimer les espaces inutiles avant et après le mot à rechercher dans la table de transco 'sans toucher aux espaces entre deux (si le mot est une combinaison de deux mots ou plus) Application.ScreenUpdating = False On Error Resume Next '____________________________________ 'création du dictionnaire Set dico = CreateObject("scripting.Dictionary") 'création du dictionnaire DICO Set celcour = Sheets("Table").Range("D5") 'définition de la cellule active : premier mot du dictionnaire Do Until celcour = "" 'on continue jusqu'à ce que la cellule active soit vide mot = celcour 'mot compte = celcour.Offset(0, -2) 'valeur à mettre en face du mot If dico.exists(mot) Then 'vérifie si le mot existe dans le dictionnaire Else dico.Add mot, compte 's'il n'existe pas : ajout du mot et de sa valeur associée dans le Dico End If Set celcour = celcour.Offset(1, 0) 'on passe à la cellule suivante Loop |
Pour résumer, Excel stock dans sa mémoire interne un libellé type et y associe en face un numéro de compte que vous aurez défini dans votre table de transco. Cette étape est indispensable pour apprendre à Excel à analyser vos relevés bancaires.
Lecture des libellés de vos relevés bancaires par Excel
Maintenant que Excel est en mesure d’attribué un compte en fonction des libellés, nous devons lui apprendre à analyser vos relevés bancaires. Ici, la procédure est assez simple : Excel va découper le texte morceau par morceau en retirant un caractère sur la gauche, puis chacun des caractères sur la droite. S’il ne trouve pas le texte dans votre table de transco, il réitérera la tâche en retirant un caractère de plus sur la gauche et ainsi de suite selon la même méthode. Voyons le code :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
'____________________________________ 'Comparaison du texte au dictionnaire Set celcour = Sheets("Journal de banque").Range("C5") 'on définit la première cellule que l'on souhaite comparer au dictionnaire Do Until celcour = "" 'on continue jusqu'à ce qu'il n'y ait plus de cellule remplie 'l'analyse s'effectue ainsi : on retire y caractères sur la gauche du mot 'puis on compare le mot dans le dictionnaire en retirant un par un les caractères sur la droite du mot 'si il ne trouve pas on retire un second caractère sur la gauche du mot et on recommence à retirer un par un les caractères sur la droite 'l'analyse s'arrête si le mot est trouvé ou si le nombre de caractères à retirer sur la gauche du mot est égale à la longueur du mot libelle = celcour 'le mot = la valeur de la cellule active y = 0 'y correspond au nombre de caractères que l'on va retirer sur la gauche du mot a = Len(libelle) 'longueur du mot Do Until a = y x = 0 'x correspond au nombre de caractères que l'on va retirer sur la droite du mot libelle = Right(libelle, a - y) 'le mot = sa longueur moins le nombre de caractères à retirer : 0 au début Do Until a = x 'on continue jusqu'à ce qu'il n'y ait plus de caractères mot = Left(libelle, a - x) 'le mot = sa longueur moins le nombre de caractères à retirer : 0 au début If dico.exists(mot) Then celcour.Offset(0, 2) = dico.Item(mot) 'si le mot existe on affiche sa valeur associée dans la cellule GoTo jump 'on quitte la boucle et on va au jump à la fin du code vba Else x = x + 1 's'il ne trouve pas le mot, on incrémente x pour réduire la taille du mot End If Loop y = y + 1 's'il ne trouve pas le mot, on incrémente x pour réduire la taille du mot Loop 'si le mot n'est pas trouvé dans le dictionnaire, on met des X dans la cellule à remplir celcour.Offset(0, 2) = "XXXX" jump: Set celcour = celcour.Offset(1, 0) 'on passe à la cellule à analyser suivante Loop |
Terminer le code
Votre fichier est maintenant à jour et vous constatez qu’un certain nombre de libellés n’ont pas été mis à jour : cela est normal. L’idée est de faire vivre votre table de transco en ajoutant les nouveaux clients au fur et à mesure que vous rencontrez différents libellés dans vos relevés bancaires.
Cependant, même si cela n’est pas visible, il s’avère qu’Excel continue de stocker des données dans sa mémoire interne, à savoir le dictionnaire que vous avez généré plus tôt. Imaginez que vous lancez ce code plusieurs fois par jour… Votre ordinateur finira par ralentir, car il n’aura plus assez de mémoire pour fonctionner vite. Pour éviter ce problème, vous devez indiquer à Excel d’oublier le dictionnaire qu’il vient d’apprendre :
|
1 2 3 4 5 6 |
'____________________________________ Set dico = Nothing 'on remet le dictionnaire à zéro pour libérer la mémoire du PCC MsgBox "Merci de vérifier la mise à jour du fichier et de compléter le cas échéant la table de transco.", vbInformation, "Information" End Sub |
Et la suite ?
Avec ce code, vous êtes désormais en mesure d’analyser vos relevés bancaires de manière simple et rapide. Le temps gagné vous permettra d’analyser plus en détail vos comptes clients et de relancer les retardataires !
Notez que les usages de ce fichier sont multiples et dépendront des améliorations que vous y apporterez :
- Ouvrir un fichier en externe, le mettre en forme puis attribuer un compte en fonction des libellés rencontrés
- Créer un double dictionnaire et attribuer un comptable chargé de la saisie pour chaque type de compte
- Et bien d’autres fonctionnalités…
Bonjour,
Merci pour votre retour, n’hésitez pas si vous avez la moindre question !
Cordialement.
Bonjour je découvre et je vous en remercie