Balance comptable ventilée par période à partir d'un FEC

Le 06/05/2024 à 16:30

Bonjour,

En comptabilité nous éditons un fichier des écritures comptables (FEC) qui est un format standardisé et normé. Il est au format .txt

Je souhaiterai, à partir de cet état, reconstituer une balance ventilée par mois, qui reprendrait les totaux des mouvements de chaque compte (en ligne) pour chaque période (en colonne).

J'ai édité un FEC simplifié avec 1 écriture par mois pendant 3 mois (ci-joint), et j'ai reconstitué le résultat que je souhaiterai obtenir (ci-joint également).

Je sèche, pourriez-vous m'aider s'il vous plait ?

merci par avance,

Cordialement,

11test-fec.txt (827.00 Octets)
10resultat-a-ibtenir.xlsx (9.62 Ko)
Le 06/05/2024 à 17:09

Bonjour,

soit il faudra un code VBA, soit il faudra importer le fichier puis agencer les données, éventuellement avec la fonction Transpose pour certaines lignes.

Le 06/05/2024 à 18:26

Aïe plus compliqué qu'il n'y parait alors.. En tout cas je n'ai pas compris la deuxième proposition et je ne maitrise pas le VBA... :(

Le 06/05/2024 à 19:05

Bonsoir à tous,

Un essai avec Power query.

Cordialement

8resultat-a-ibtenir.xlsx (18.89 Ko)
Le 06/05/2024 à 19:41

Merci ! En apparence c'est exactement ce que je souhaite obtenir. Je rentre dans le détail de la requête des que possible et vous revient

Encore merci pour votre proposition

Le 06/05/2024 à 21:00

Hey,

Une autre proposition mais similaire à Zebulon2 que je salue

@+

3resultat-a-ibtenir.xlsx (18.10 Ko)
Le 06/05/2024 à 22:28

Bonsoir,

@zebulon : En utilisant un FEC véritable, j'obtiens le message d'erreur suivant :

Expression.Error : Il y a trop d'éléments dans l'énumération pour terminer l'opération.
Détails :
[List]

@baroute78 : merci pour votre proposition ! même chose que ci-dessus à l'étape "Pivot"

Le 07/05/2024 à 02:23

re,

il faut sans doute utiliser un index juste avant de pivoter. Est il possible d'obtenir le fichier FEC utilisé en anonymisant les données si besoin ?

Cordialement

Edit

Bonsoir Baroute78,

Requête modifiée

6resultat-a-ibtenir.xlsx (19.06 Ko)
Le 07/05/2024 à 14:39

Bonjour Zebulon, merci pour votre aide et votre Edit.

J'ai retenté avec un FEC et le résultat obtenu ne condense pas les lignes par n° de compte. Je voudrais que chaque compte n'apparaisse qu'une fois et que le total de janvier, par exemple, comporte tous les mouvements du compte sur tout le mois de janvier, et non pas une ligne par opération.

Il manque un dernière étape à mon avis mais je ne connais pas la fonction à utiliser pour arriver au résultat espéré

Ci-joint un FEC anonymisé (j'avais rajouté une étape de modification du type en Date dans votre requête)

4fec20240430.zip (126.14 Ko)
Le 07/05/2024 à 19:42

Bonsoir,

En s'appuyant sur la requête de Baroute78.

Cordialement

Edit : fichier amendé

4cr30.xlsx (42.54 Ko)
Le 07/05/2024 à 19:59

Hello,

Une proposition amenant au même résultat que Zebulon2.

Egalement j'ai fait un arrondi à deux chiffres après la virgule sur les colonnes de mois car on avait des valeurs avec des 2^-14. Si il ne faut pas il faut supprimer la dernière étape de ma requête. Si des mois s'ajoutent, l'arrondi se fera automatiquement sur les nouvelles colonnes rajoutées.

@+

5resultat-a-ibtenir-1.xlsx (41.88 Ko)

Edit

let
    Source = Csv.Document(File.Contents("C:\Users\NathD\Downloads\test-fec.txt"),[Delimiter="    ", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    AddRecords = Table.AddColumn(Headers, "Records", each [
Compte = if Text.Length([CompAuxNum])>=1 then  [CompAuxNum] else [CompteNum],
Libellé = if Text.Length([CompAuxNum])>=1 then "Compte " & [CompAuxNum] else [CompteLib],
Montant = Number.From([Debit]) - Number.From([Credit])
])[[EcritureDate],[Records]],

    ExpandRecords = Table.ExpandRecordColumn(AddRecords, "Records", Record.FieldNames(Record.Combine(AddRecords[Records]))),

    TransformDate = Table.TransformColumns(ExpandRecords , {"EcritureDate" , each Date.ToText(Date.From(_) , "MMMM")}),

    PivotSum = Table.Pivot(TransformDate, List.Distinct(TransformDate[EcritureDate]), "EcritureDate", "Montant", List.Sum),

    Arrondi = Table.TransformColumns(PivotSum, List.Transform(List.Skip(Table.ColumnNames(PivotSum),2) , (x) => { x, each Number.Round(_, 2), type number} ))
in
    Arrondi
Le 07/05/2024 à 21:12

Bonsoir, je vous remercie infiniment pour vos propositions !!!

Pour ma culture personnelle et pour satisfaire ma soif d'apprendre, auriez-vous la pédagogie de me traduire "en bon français" les principales étapes de votre requête s'il vous plait ? (à partir de "ADDRECORDS")

Le 07/05/2024 à 21:29

Hello

Yes c'est tout a fait possible 

let
    Source = Csv.Document(File.Contents("C:\Users\NathD\Downloads\test-fec.txt"),[Delimiter="    ", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

//La fonction Table.AddColumn avec la syntaxe "[...]" permet de créer plusieurs colonnes dans la même opération plutôt que de créer trois étapes et ajouter une colonne par une colonne. A la fin de cette étape cette partie "[[EcritureDate],[Records]]" signifie que je ne garde que les champs EcritureDate et Records que je viens de créer
    AddRecords = Table.AddColumn(Headers, "Records", each [
Compte = if Text.Length([CompAuxNum])>=1 then  [CompAuxNum] else [CompteNum],
Libellé = if Text.Length([CompAuxNum])>=1 then "Compte " & [CompAuxNum] else [CompteLib],
Montant = Number.From([Debit]) - Number.From([Credit])
])[[EcritureDate],[Records]],

//Cette étape permet de récupérer en mode "Table" les différentes colonnes créées précédemment et cette partie "Record.FieldNames(Record.Combine(AddRecords[Records]))" permet de récupérer le nom des trois colonnes en dynamique. Si on ajoute un record dans l'étape précédente pour créer une 4ème colonne, et bien cette colonne sera récupérée automatiquement.
    ExpandRecords = Table.ExpandRecordColumn(AddRecords, "Records", Record.FieldNames(Record.Combine(AddRecords[Records]))),

//Modification des valeurs du champ écriture date pour avoir le format "janvier", "février" etc. Date.From() convertit le texte en une date exploitable puis la fonction Date.ToText permet de convertir des dates dans le format désiré. Ici "MMMM" est le code pour le mois complet. Si j'avais mis "yyyy-MM" j'aurai eu un retour dans ce style "2024-01"
    TransformDate = Table.TransformColumns(ExpandRecords , {"EcritureDate" , each Date.ToText(Date.From(_) , "MMMM")}),

//Plutôt explicite, permet de pivoter le champ de période en sommant la colonne montant
    PivotSum = Table.Pivot(TransformDate, List.Distinct(TransformDate[EcritureDate]), "EcritureDate", "Montant", List.Sum),

//Pour convertir avec deux chiffres après la virgule, soit on fait la manipulation avec l'assistant qui va générer le code en dur. Cependant si la période de MAI est intégré la conversion ne sera pas faite car pas pris en compte au moment du développement.
La fonction ci-dessous permet la conversion avec les arrondis de manière dynamique
    Arrondi = Table.TransformColumns(PivotSum, List.Transform(List.Skip(Table.ColumnNames(PivotSum),2) , (x) => { x, each Number.Round(_, 2), type number} ))
in
    Arrondi

N'hésite pas si besoin et bonne soirée

@+

Lundi à 16:59

Bonjour,

Un grand grand merci pour ces détails ! Bon autant vous dire que je me suis perdue dans les explications et que je ne parviendrais pas à tout comprendre... mais encore un grand merci pour votre dévouement

Lundi à 17:20

Hey merci pour le retour :)

Rechercher des sujets similaires à "balance comptable ventilee periode partir fec"