VBA, EXCEL : analyser le Fichier des Ecritures Comptables (FEC)

La communication du Fichier des Ecritures Comptables (FEC) à l’administration fiscale lors d’une vérification de comptabilité (contrôle fiscal) est une obligation introduite pour les exercices ouverts à compter du 1er janvier 2014. Le FEC est une extraction normalisée qui reprend l’ensemble des écritures comptables d’un contribuable. Cette obligation est une opportunité pour l’entreprise. En effet, la normalisation de cette extraction de données favorise l’usage de l’analyse des données comptables.

L’article A 47 A-1 du Livre des procédures fiscales modifié par l’arrêté du 29 juillet 2013 définit le contenu du Fichier des Ecritures Comptables (FEC). Les champs de données sont les suivants :

INFORMATION NOM DU CHAMP TYPE DE CHAMP
1. Le code journal de l’écriture comptable JournalCode Alphanumérique
2. Le libellé journal de l’écriture comptable JournalLib Alphanumérique
3. Le numéro sur une séquence continue de l’écriture comptable EcritureNum Alphanumérique
4. La date de comptabilisation de l’écriture comptable EcritureDate Date
5. Le numéro de compte, dont les trois premiers caractères doivent correspondre à des chiffres respectant les normes du plan comptable français CompteNum Alphanumérique
6. Le libellé de compte, conformément à la nomenclature du plan comptable français CompteLib Alphanumérique
7. Le numéro de compte auxiliaire (à blanc si non utilisé) CompAuxNum Alphanumérique
8. Le libellé de compte auxiliaire (à blanc si non utilisé) CompAuxLib Alphanumérique
9. La référence de la pièce justificative PieceRef Alphanumérique
10. La date de la pièce justificative PieceDate Date
11. Le libellé de l’écriture comptable EcritureLib Alphanumérique
12. Le montant au débit Debit Numérique
13. Le montant au crédit Credit Numérique
14. Le lettrage de l’écriture comptable (à blanc si non utilisé) EcritureLet Alphanumérique
15. La date de lettrage (à blanc si non utilisé) DateLet Date
16. La date de validation de l’écriture comptable ValidDate Date
17. Le montant en devise (à blanc si non utilisé) Montantdevise Numérique
18. L’identifiant de la devise (à blanc si non utilisé) Idevise Alphanumérique

Si les informations ” débit ” et ” crédit ” ne sont pas présentes dans le système informatisé comptable de l’entreprise, les informations 12 et 13 peuvent être respectivement remplacées par ” montant ” et ” sens “, sur le modèle suivant :

INFORMATION NOM DU CHAMP TYPE DE CHAMP
12. Le montant Montant Numérique
13. Le sens Sens Alphanumérique

L’application VBA qui suit propose d’exploiter le FEC à des fin d’analyse sous Excel. A cette fin, cette application convertit le FEC du format texte vers un classeur Excel créé à cet effet et effectue quelques ajouts facilitant les travaux d’analyse de données.

'ANA_FEC v1.0, 02/2016
'Programmé par Benoît RIVIERE

Sub ExploitationFEC()
    Dim NomFichier As Variant 'composé du lecteur+chemin+fichier+ext
    Dim oFSO As Scripting.FileSystemObject 'permet de lire le détail d'un nom de fichier
    Dim NbLignes As Long
    
    'Sélection du fichier à importer
    NomFichier = Application.GetOpenFilename("Fichiers Texte (*.txt),*.txt,Tous les fichiers (*.*),*.* ", 1, "Sélectionnez le Fichier des Ecritures Comptables (FEC) à importer", , False)
    '---si l'utilisateur annule l'importation => sortie de la procédure
    If NomFichier = False Then Exit Sub
    
    'Ouverture du fichiers des écritures (format texte) et conversion des colonnes
    Workbooks.OpenText Filename:=NomFichier, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 4), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True
    
    'Nombre de lignes d'écritures (y compris la ligne d'entête)
    NbLignes = Range("A1", Selection.End(xlDown)).Cells.Count
    
    'Remplacement des espaces dans les champs Débit/Crédit (FEC TIGRE)
    Columns("L:M").Select
    Selection.Replace What:="               ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
    'Ajout de champs additionnels
    '---Solde de la ligne d'écriture
    Range("S1").Formula = "Solde"
    Range("S2").FormulaR1C1 = "=RC[-7]-RC[-6]"
    '------Duplication de la formule jusqu'à la dernière ligne
    Range("S2").AutoFill Destination:=Range("S2:S" & NbLignes)
    '---aaaa/mm
    Range("T1").Formula = "aaaamm"
    Range("T2").FormulaR1C1 = "=LEFT(RC[-16],4)&""/""&LEFT(RIGHT(RC[-16],4),2)"
    Range("T2").AutoFill Destination:=Range("T2:T" & NbLignes)
    '---Cpte1, Cpte2 et Cpte3
    Range("U1").Formula = "Cpte1"
    Range("V1").Formula = "Cpte2"
    Range("W1").Formula = "Cpte3"
    Range("U2").FormulaR1C1 = "=LEFT(RC[-16],1)"
    Range("V2").FormulaR1C1 = "=LEFT(RC[-17],2)"
    Range("W2").FormulaR1C1 = "=LEFT(RC[-18],3)"
    Range("U2:W2").AutoFill Destination:=Range("U2:W" & NbLignes)

    'Mise en place des filtres de données automatiques
    Range("A1").AutoFilter
    
    'Insertion des sous.totaux de filtres sur champs D/C/solde
    '---Insertion d'une ligne vierge
    Range("A1").EntireRow.Insert
    '---Formule de calcul sur le champ Débit...
    Range("L1").FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & NbLignes + 1 & "]C)"
    '---...Puis duplication de la formule sur les champs Crédit et Solde
    Range("L1").Copy
    Range("M1").Select
    ActiveSheet.Paste
    Range("S1").Select
    ActiveSheet.Paste
    
    'Formatage
    '---Formatage des champs D/C et solde
    Range("L:M,S:S").NumberFormat = "#,##0.00"
    '---Ajustement des colonnes à leur contenu
    Cells.Columns.AutoFit

    'Sauvegarde du classeur au format Excel
    Set oFSO = New Scripting.FileSystemObject
    ActiveWorkbook.SaveAs Filename:=oFSO.GetBaseName(NomFichier), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Quelques explications du code-source de l’application :

'Sélection du fichier à importer
NomFichier = Application.GetOpenFilename("Fichiers Texte (*.txt),*.txt,Tous les fichiers (*.*),*.* ", 1, "Sélectionnez le Fichier des Ecritures Comptables (FEC) à importer", , False)
'---si l'utilisateur annule l'importation => sortie de la procédure
If NomFichier = False Then Exit Sub

Cette ligne permet à l’utilisateur du programme de sélectionner un fichier FEC (format texte) et de récupérer le nom du fichier (arborescence comprise) dans la variable NomFichier. La commande .GetOpenFilename réalise cette opération. Si l’utilisateur ne sélectionne pas de fichier (annulation en appuyant sur la touche Echap), la variable NomFichier prendra la valeur booléenne False. L’expression If NomFichier = False Then Exit Sub permet de sortir de la procédure en cours sans réaliser les instructions qui suivent (en l’occurrence l’importation du FEC).

'Ouverture du fichiers des écritures (format texte) et conversion des colonnes
Workbooks.OpenText Filename:=NomFichier, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 4), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1)), TrailingMinusNumbers:=True

La macro VBA ouvre le FEC (commande .OpenText), l’importe dans Excel dès la première ligne (StartRow:=1). Les dix-huit champs de données définis par la Loi sont retranscrits dans les colonnes A à R. Les deux séparateurs prévus par la réglementation sont la tabulation (Tab:=True) et le signe | (OtherChar:=”|”).

Range("U2").FormulaR1C1 = "=LEFT(RC[-16],1)"

Ajout de champs additionnels : par exemple, ressortir le chiffre le plus à gauche (LEFT) du numéro de compte (classe)…

'Mise en place des filtres de données automatiques
Range("A1").AutoFilter

Met en place des filtres automatiques (.AutoFilter, équivalent au menu Données / Filtrer).

Range("A1").EntireRow.Insert

Insère (.Insert) une ligne avant la ligne une (A1).

Range("L1").FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & NbLignes + 1 & "]C)"

La commande SUBTOTAL(fonction,plage de cellules) calcule la somme des montants non filtrés (fonction n° 9). Cette commande correspond à SOUS.TOTAL(fonction; plage de cellules) d’Excel.

Range("L1").Copy
Range("M1").Select
ActiveSheet.Paste

Cette partie du code copie (.Copy) le contenu de la cellule L1 puis le colle en M1 (.Paste), automatisant ainsi le copier/coller.

Range("L:M,S:S").NumberFormat = "#,##0.00"

Formate les colonnes L, M et S au format numérique (.NumberFormat) avec séparateur de milliers et deux décimales.

Cells.Columns.AutoFit

Ajuste automatiquement la largeur des colonnes à leur contenu (.AutoFit).

Dim oFSO As Scripting.FileSystemObject 'permet de lire le détail d'un nom de fichier

[...]

'Sauvegarde du classeur au format Excel
Set oFSO = New Scripting.FileSystemObject
ActiveWorkbook.SaveAs Filename:=oFSO.GetBaseName(NomFichier), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

La fonction oFSO.GetBaseName(NomFichier) renvoie le nom du fichier compris dans la chaîne NomFichier. La commande .SaveAs équivaut au menu Fichier / Enregistrer sous… d’Excel ; le paramètre FileFormat:=xlOpenXMLWorkbook permet d’enregistrer le classeur au format *.xlsx.

La fonction .GetBaseName fait partie de la bibliothèque Microsoft Scripting Runtime. Cette bibliothèque doit être activée avant de lancer le programme (Menu Outils / Références… de l’EDI VBA).

Points d’amélioration :

  • ajout de contrôles de cohérence (équilibre des écritures, des lettrages…) avec les fonctions SOMME.SI et SOMME.SI.ENS (respectivement sumif et sumifs en VBA) ; ces contrôles gagneraient à être matérialisés par des formats conditionnels (FormatConditions en VBA) et des filtres par couleur.
  • ajouter un bouton pour lancer l’exécution de la macro.

Cette application ne teste pas la validité du FEC. La DGFiP propose en libre téléchargement un logiciel permettant de tester la validité du FEC.

Afin d’approfondir le sujet, le lecteur est invité à lire le guide pratique intitulé Le fichier des écritures comptables et l’archivage des comptabilités informatisées (Ordre des Experts-Comptables) disponible en téléchargement.

Tous savoir sur le Fichier des Ecritures Comptables (FEC)… / la conversion de fichier (Texte <-> Excel) / l’analyse de données

Approfondir le sujet : Programmer en VBA / Découvrir la série d’articles Maîtriser Excel

Share Button
Guide Pratique - Fichier des Ecritures Comptables (FEC) et Contrôle de Comptabilités Informatisées
Guide Pratique - Fichier des Ecritures Comptables (FEC) et Contrôle de Comptabilités Informatisées
Guide-pratique-Fichier-des-ecritures-comptables-FEC-et-controle-de-comptabilites-informatisees.pdf
Version: 2014
2.4 MiB
2784 téléchargements
Détails...
The following two tabs change content below.
Benoît RIVIERE
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

6 commentaires

  1. Pingback: VBA, EXCEL : analyser le Fichier des Ecritures ...

  2. Bonjour Mr RIVIERE

    C’est tellement passionnant de lire vos publications car l’analyse des données que vous aviez su valorisé par vos différents travaux ouvrent des opportunités sans limite.

    Je souhaiterai svp avoir votre avis d’expert concernant mon parcours.

    j’ai 37 ans et Comptable général avec 8 ans d’expériences en entreprise et utilise intensément Excel et avec une expérience en liasse fiscale et de consolidation et je suis entrai de finaliser mon DSCG.
    Je souhaiterai intégrer un cabinet comptable dès janvier 2017 à la fin de mon CDD.
    Ma question est de savoir quel est le positionnement d’un comptable général confirmé lors de l’intégration dans le cabinet comptable ( collaborateur junior, mis à jour des connaissances? est-il valorisé ? bien qu’ayant pas de vécu ” cabinet COMPTABLE”.

    mon projet c’est d’entrer en stage d’expertise comptable dès l’année prochaine.

    Merci de vos commentaires et me serons très utiles.

    Iram NZINZI

  3. Bonjour,
    je travaille dans cabinet d’expertise comptable, ça fait un bon moment que je cherche à transférer une feuille de caisse sous forme (date, espèces, chèques, carte bleu, autres) en écritures comptables avec les comptes du plan comptable 580000, 5111000, 5112000, 5113000 en débit et les compte de classe 7 et la tva en crédit. on ma dit qu’on pouvait le faire avec EXCEL autrement dit avec un macro et un fichier VBA.
    SOS j’aimerais bien que quelqu’un pourrait m’aider à le faire
    cordialement

  4. Bonsoir Iram,
    Merci beaucoup pour votre message d’encouragement.
    Il n’y a aucune raison que vous ne puissiez valoriser votre expérience pour intégrer un cabinet. Vos compétences et votre passion pour le métier vous aideront à vous distinguer des autres et à réussir.
    Bonne continuation,
    Bien cordialement,
    Benoît RIVIERE

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.