Transformation de fichiers Quicken vers GNU Cash
le contexte
J'utilise Quicken depuis 25 ans; d'abord la version 2001, puis la version 2006. Comme je suis sous Linux, Quicken tourne dans Wine.
La seule chose qui ne fonctionnait pas sont les backups; la fonction "Backup" se plantait, mais je pouvais toujours copier le fichier courant, ainsi que l'exporter.
Malheureusement, depuis une mise à jour de Wine, je constate des comportements bizarre; erreur au lancement (table de "Memorized transactions" tronquée), à l'affichage (transactions dupliquées), mais plus inquiétant, transactions manquantes.
C'est le temps de passer à une solution plus moderne, et surtout, native sous Linux. Mon choix c'est porté sur GNU Cash, dont l'ergonomie est assez similaire à Quicken.
Naturellement, je veux importer mon historique de transactions qui, je dois dire, est peut être plutôt complexe: 110 catégories distinctes et 45 comptes (bancaires, amortissement hypothécaire, comptes d'investissement avec des "parts" et un prix unitaire variable, etc..).
Premier essai
Note: dans le texte, je considère que le lecteur connaît quelques commandes linux de base: vim, od, sed et awk.
A priori assez simple: exporter de Quicken au format QIF, puis importer le QIF dans GNU Cash.
Le format QIF est un format texte, donc modifiable avec un simple éditeur de texte (j'utilise vim), mais aussi exploitable par des commandes de traitement de texte comme sed ou awk / nawk.
Le premier problème était dans Quicken qui plante à l'écriture du fichier d'export. J'ai fini par exporter année par année, soit 25 fichiers, pour un total de 180500 lignes. J'ai une année problématique qui semble corrompue; je ferai un export sous mon ancienne version de Wine pour celle là.
Un autre problème est que Quicken exporte au format Windows; les fins de lignes sont donc "\r\n" au lieu de "\n" pour Linux; facile à traiter avec sed:
$ sed -e 's/\r//' Export2010.QIF
Cependant, l'import n'a pas fonctionné: GNU Cash se plaint d'un format de date incorrect. Une des raisons que j'ai identifié est la présence de slash (/) dans des descriptions de compte. En effet, la description d'un compte est un champ de type D ce qui est également le préfixe pour une date.
DCda/Que Pension Plan ← Ici le champ D correspond à une description ... D10/ 1' 9 ← Mais ici, il correspond à une date ...
Pour être certain que toutes les dates sont correctes, j'ai fait un petit script awk qui les corrige:
$ cat date.awk
/^D/ {D=substr($0,2,length($0)-1);
sub("' ","/200",D);
sub("'","/20",D);
sub("/ ","/0",D);
sub(/^[1-9]\//,"0&",D);
}
$ sed -e 's/\r//' Export2010.QIF | nawk -f date.awk
...
D01/01/2009
...
D01/12/2009
...
Le format QIF
Mon idée était d'importer les fichiers QIF dans LibreOffice Calc (l'équivalent de Excel sous Linux) afin de vérifier les transactions: éliminer les doublons et recréer les transactions manquantes. Comme les fichiers QIF sont des fichiers texte, il semblait "facile" de les passer à travers un script awk pour les ressortir en CSV (comma-separated values).
Les fichiers QIF sont organisés sections ("chunks"), avec un début et une fin de section. Le nombre de sections dépend de ce qui a été sélectionné à exporter. Malheureusement le format manque de cohérence, les mêmes balises servant à plusieurs usages; ce qui rend l'exploitation par les commandes de traitement de stream (sed, awk ou nawk) difficile.
sed (stream editor) permet d'effectuer des commandes basiques (filtrage, remplacement, ...) sur un flot de caractères. Les commandes awk-like (awk, gawk = Gnu awk, et nawk = new awk), qui prennent leurs noms des 3 créateurs Alfred Aho, Peter Weinberger et Brian Kernighan, sont des commandes de traitement de flot plus élaborées, comportant un langage de programmation.
Les chunks
Un chunk débute par ! et se termine par un autre chunk ou la fin du fichier.
La balise "Autoswitch"
Cette balise est utilisée pour lister les comptes, si l'option d'exporter la liste des comptes a été sélectionnée.
$ sed -e 's/\r//' Export*.QIF | grep "^!" | sort -u !Option:AutoSwitch ← Début de la liste des comptes ... !Clear:AutoSwitch ← Fin de la liste des comptes ...
On s'attendrait donc à ne voir cette balise qu'une seule fois; et bien non! Elle apparaît 2 fois dans l'export, comme on le voit sur cet exemple:
$ egrep '^!' -n Export2010.QIF 1:!Type:Cat ← on liste les catégories 770:!Option:AutoSwitch ← début d'une liste.. 771:!Account ← c'est une liste de comptes 931:!Clear:AutoSwitch ← fin de liste 932:!Option:AutoSwitch ← de nouveau, début d'une liste.. 933:!Account ← c'est une liste de comptes... 937:!Type:Bank ← ... bancaires ... 938:!Account 942:!Type:Bank 943:!Account ... ← à partir d'ici, on trouve également des transactions 3820:!Type:CCard ← puis vient la liste des cartes de crédit 3821:!Account ... 5291:!Type:Cash ← puis la liste des cartes des comptes liquides 5292:!Account ... ← etc, mais pas de fin de liste !
Voici les identifiants et leur description (source: https://github.com/Gnucash/gnucash/blob/stable/gnucash/import-export/qif-imp/file-format.txt)
$ sed -e 's/\r//' Export*.QIF | grep "^!" | sort -u !Account ← le compte concerné !Type:Bank ← compte bancaire !Type:Cash ← argent liquide !Type:Cat ← catégories de transactions !Type:CCard ← carte de crédit !Type:Invst ← compte d'investissement !Type:Memorized ← transactions mémorisées, pour faciliter la saisie !Type:Oth A ← autre compte d'actifs !Type:Oth L ← autre compte de passifs (dettes, hypothèques) !Type:Security ← compte de placements (actions, obligations) !Type:Prices ← prix pour les compte de placements (par exemple prix unitaire de l'action)Le premier chunk,
cat, décrit les catégories de transaction, c'est-à-dire les différentes "case" dans lesquelles vous pouvez classer les sommes:
$ head -800 Export2010.QIF | sed -e 's/\r//' !Type:Cat NASS GRP ← N (name) = nom de la catégorie DAssurance Groupe ← D = description E ← type de catégorie; dépense (E = expense) ou gain (I = income) ^ ← séparateur de fin ... NAuto ← nom de la catégorie DAutomobile Expenses ← description E ← type de catégorie ^ ← séparateur de fin NAuto:Fuel ← nom de la catégorie (ici une sous-catégorie) DAuto Gas & Oil T ← T (tax): cette catégorie est reliée à un compte de taxes R5728 ← Compte de taxes relié E ^ ...Certaines catégories comporte un buget mensuel; je pense qu'il est ajouté automatiquement à la première saisie. Personnellement, je ne l'utilise pas. On voit donc 12 balises
B qui se suivent:
NAuto:Insurance DAuto Insurance B-74.17 ← B (budget mensuel): somme pour le premier mois B0.00 ← mois suivant B0.00 ← etc... B0.00 B0.00 B0.00 B0.00 B0.00 B0.00 B0.00 B0.00 B0.00 T R5760 E ^ ...Le chunk suivant,
!Account, décrit les différents comptes:
!Account N0000000-ES1 Épargne stable REER ← nom du compte TBank ← type de compte (Bank = compte bancaire) ^ ← séparateur de fin NCELI TBank DCompte d'épargne CELI ← description ^ ← séparateur de fin ... NMasterCard TCCard ← type de compte (CCard = carte de crédit) L0.00 ← Limite de crédit ^ ...Viennent ensuite les transactions qui se présente sous la forme suivante:
!Account ← On identifie le compte duquel les transaction suivent NCELI DCompte d'Épargne CELI TBank ^ !Type:Bank ← Ensuite viennent les transactions: D3/22'10 ← Date de transaction: 22 mars 2010 U0.00 ← montant T0.00 ← montant ?? (je n'ai jamais vu de différences) CX ← C = Cleared; concilliation bancaire POpening Balance ← P = Payee; dans ce cas, description L[CELI] ← Catégorie ou compte (*) ^
(*) Quand il s'agit d'une catégorie, elle apparaît directement: LInterest Inc. Quand il s'agit d'un transfert vers un autre compte, c'est le compte destination qui apparaît entre les crochets: L[CELI]
Simplification
Comme tous les exports ont été fait la même journée, la liste des catégories (lignes 1 à 770) et des comptes (lignes 771 à 933) est identique. Effectivement, les 933 premières lignes ont toutes la même signature MD5:
$ for f in Export20[012][0-9].QIF; do echo $f `sed -n '1,933p' $f|md5sum`; done Export2001.QIF 94d0383c41356700b85f5af3c6ca9c13 - Export2002.QIF 94d0383c41356700b85f5af3c6ca9c13 - ... Export2025.QIF 94d0383c41356700b85f5af3c6ca9c13 - Export2026.QIF 94d0383c41356700b85f5af3c6ca9c13 -
J'ai donc éliminé les 933 premières lignes, en gardant qu'une copie; il ne reste alors que les transactions dans les fichiers, ce qui me permet de simplifier mes scripts.
Voici donc un premier script awk qui affiche les transactions:
/\!Account/ {mode=0;acc_name="";acc_type="";acc_desc="";}# print "Mode 0 (identification du compte)"}
/\!Type:/ {mode=1;D="";N="";T="";U="";P="";C="";M="";L="";}#print "Mode 1 (description d'une transaction)"}
/^D/ {D=substr($0,2,length($0)-1);
if(mode==0) acc_desc=D; # Description du compte
if(mode==1) {sub("' ","/200",D); sub("'","/20",D); sub("/ ","/0",D); sub(/^[1-9]\//,"0&",D);} # Date
}
/^N/ {N=substr($0,2,length($0)-1); if(mode==0) acc_name=N; } # Mode 0: Nom du compte. Mode 1: type de transaction / numéro de chèque
/^T/ {T=substr($0,2,length($0)-1); if(mode==0) acc_type=T; } # Mode 0: Type de compte. Mode 1: Montant de la transaction
/^U/ {U=substr($0,2,length($0)-1);} # Mode 0: N/A. Mode 1: Montant (autre, inutilisé)
/^P/ {P=substr($0,2,length($0)-1);} # Mode 0: N/A. Mode 1: Destinataire
/^C/ {C=substr($0,2,length($0)-1);} # Mode 0: N/A. Mode 1: Reconcillié ?
/^M/ {M=substr($0,2,length($0)-1);} # Mode 0: N/A. Mode 1: Memo
/^L/ {L=substr($0,2,length($0)-1);} # Mode 0: N/A. Mode 1: Catégorie ou compte destinataire
/^\^/ {if (mode==1) {
print "Compte:"acc_type,acc_name,acc_desc;
DB=""; CB=""; if(substr(T,1,1)=="-") {DB=substr(T,2);} else {CB=T};
print D,"Type:"N,"Destinataire:"P,"Débit:"DB,"Crédit:"CB,"Checked:"C,"Memo: "M,"Categorie: "L;
}
}
C'est loin d'être élégant, mais ça donne le résultat. Avec un problème cependant: le manque de support des caractères supérieurs à 127!
Gestion des caractères accentués
Voici un exemple:
Destinataire:Ad�le ; Débit: ; Crédit:
Le fichier source contient le caractère "è" qui n'est pas bien représenté. Pourtant, à l'affichage, awk représente bien les caractères "é".
La raison est que le "è" provient du fichier source alors que le "é" provient du script awk. Or, pour awk, les caractères accentués sont au delà de 127 (soit 7F en hexa) et représentés par 2 octets; c'est la norme UTF-8. Donc le caractère "è", qui a la valeur 232 dans le fichier source (soit E8 en hexa), ne correspond pas à la norme UTF-8; dans ce cas, la norme précise qu'il doit être représenter par "�".
La commande od (octal dump) permet de voir le détail:
$ sed -e 's/\r//' Export2018.QIF.trans | awk -f trans.awk | od -ct x1 | more
...
0000060 69 6e 61 74 61 69 72 65 3a 41 64 e8 6c 65 20 3b
i n a t a i r e : A d 350 l e ;
0000100 20 44 c3 a9 62 69 74 3a 20 3b 20 43 72 c3 a9 64
D 303 251 b i t : ; C r 303 251 d
...
Le è de Adèle est représenté par 1 octet (en hexa: e8), alors que les caractères é de Crédit et Débit sont représentés par 2 octets (en hexa: c3 e9).
Note: la commande od ne sait pas non plus afficher correctement les caractères accentués et affiche à la place les valeurs en octal (303 octal = c3 hexa).
La solution passe à nouveau par sed, grâce à un petit script de transformation que j'ai nommé trans.sed. Les commandes s/\oXXX/Y/g/ signifient : substituer (s) la valeurs XXX (représentée en octal) par le caractères Y, pour toutes les occurences (g = globalement).
s/\r//g s/\o311/\o303\o211/g ← É s/\o350/\o303\o250/g ← é s/\o351/\o303\o251/g ← è s/\o352/\o303\o252/g ← ê s/\o353/\o303\o253/g ← ë s/\o340/\o303\o240/g ← à s/\o347/\o303\o247/g ← ç s/\o364/\o303\o264/g ← ô s/\o373/\o303\o273/g ← û s/\o260/um/g ← signe °; transforme N° en Num
Comme le script a été créé avec vim qui supporte UTF-8, les caractères accentués du script sont bien UTF-8, comme on le voit sur ce dump:
Note: -w13 signifie simplement "afficher sur 13 octets de large", pour que les lignes soit bien alignées.
$ od -ct x1 -w13 trans.sed ┌┈┈┈┈┈┐ ← le É est représenté par 2 octets, conformément à UTF-8
0000000 s / \ o 3 1 1 / 303 211 / g \n
73 2f 5c 6f 33 31 31 2f c3 89 2f 67 0a
0000015 s / \ o 3 5 0 / 303 250 / g \n
73 2f 5c 6f 33 35 30 2f c3 a8 2f 67 0a
0000032 s / \ o 3 5 1 / 303 251 / g \n
73 2f 5c 6f 33 35 31 2f c3 a9 2f 67 0a
...
La commande devient:
$ sed -f trans.sed Export2018.QIF.trans | awk -f trans.awk > Export2018.csv
Ou pour traiter tous les fichiers:
$ echo > Export.csv $ for F in Export*.QIF.trans; do sed -f trans.sed $F | awk -f trans.awk >> Export.csv; done
Le résultat peut maintenant être importé dans Excel ou LibreOffice Calc pour une mise en forme.
La cas des ventilations (splits)
Lorsqu'une transaction est ventilée dans plusieurs comptes elle se présente ainsi:
D... ← date U... ← montant T... ← montant CX ← concillié N... ← type de transaction P... ← Payee M... ← Mémo L... ← Catégorie S... ← Split 1: Catégorie E... ← Mémo $... ← Montant S... ← Split 2: Catégorie E... ← Mémo $... ← Montant ... ^
Dès l'apparition du S, il faut afficher la ligne.
Le cas des soldes d'ouverture
Lorsqu'un compte est créé, Quicken crée une transaction avec le solde d'ouverture. Dans le fichier QIF, elle se présente sous la forme suivante:
!Type:Cash ← type de compte !Account NCash US $ ← nom du compte TCash ^ !Type:Cash D1/ 1'14 ← date de l'ouverture U0.00 ← solde d'ouverture T0.00 POpening Balance ← Commentaire automatique L[Cash US $] ← Nom du compte ^
Transformation en double écriture
GnuCash est basé sur le principe de la double écriture. Toute écriture est constituée de deux transactions: un compte est débité et un autre compte est crédité.
Par exemple, une dépense courante comme l'achat d'une pizza avec une carte de crédit Visa s'écrira en deux transactions:
- Débiter le compte Passif:VISA
- Créditer le compte Dépenses:Alimentation
Ce principe est un peu différent de Quicken, qui utilise la notion de catégories: on débite le compte VISA en indiquant dans quelle catégorie affecter la dépense. Cependant, la double écriture est aussi tout à fait possible dans Quicken.
Durant mes 25 années d'utilisation de Quicken, je n'ai pas été toujours constant. J'ai utilisé autant les catégories que la double écriture, et ce parfois pour le même type de dépense. Précisons que je n'ai pas de formation comptable, juste 2 heures de comptabilités par semaine durant mes études en informatique; et ce n'était pas le cours où j´étais le plus attentif!
L'utilisation de la double écriture est venue d'elle-même, logiquement, par l'analyse des paiements hypothécaires. En effet, je voyais bien qu'une partie du paiement est une forme d'épargne (le capital), l'autre partie est "perdue" (les intérêts). Trois comptes devraient être impliqués: un compte débiteur (le compte courant) et deux comptes créditeur; la banque (qui perçoit les intérêts) et le compte d'actif (le logement), sur lequel va le capital. Lors d'un remboursement anticipé, la somme va intégralement sur le capital.
De plus, dans mon cas, le prêteur hypothécaire permettait de payer les taxes (taxe d'habitation et taxe scolaire) en prélevant une somme à chaque versement, ce que j'ai fait pendant plusieurs années. Dans ce cas, deux autres comptes devraient être impliqués: la ville pour la taxe d'habitation et la commission scolaire pour la taxe scolaire.
Dans mes fichiers QIF, certaines périodes utilisent la double écriture, d'autres non.
Il est assez simple de transformer les écritures simples en écritures doubles.
Écriture simple
!Account ← On identifie le compte duquel les transaction suivent NCompte courant DCompte chèque TBank ^ !Type:Bank D3/22'10 U-800.00 T-800.00 CX PBanque ABC ← Destinataire (Payee) LHypothèque ← Catégorie: hypothèque ^
Écriture double (modifications et ajouts en bleu)
!Account ← On identifie le compte débité NCompte courant DCompte chèque TBank ^ !Type:Bank D3/22'10 U-800.00 T-800.00 CX PHypothèque L[Banque ABC] ← On transfert vers le compte Banque ABC ^ !Account ← On identifie le compte crédité NBanque ABC DHypothèque #123456 TBank ^ !Type:Bank D3/22'10 U800.00 T800.00 CX PHypothèque L[Compte courant] ← On transfert provenant du compte Compte courant ^ !Account ← On doit retourner au compte précédent NCompte courant DCompte chèque TBank ^
Cependant, modifier le QIF source reste plus difficile que de modifier le résultat du awk; j'ai donc choisi d'extraire du résultat tout ce qui concerne l'hypothèque, puis d'écrire un script qui duplique les transactions.
Compatibilité avec gnucash-importer
gnucash-importer est un script Python qui ajoute à GnuCash la possibilité d'importer directement des fichiers CSV, sans passer par le menu importation. Cependant, mes fichiers n'étant pas très "propres", j'ai préféré continuer avec awk. Par contre, je m'en suis inspiré pour ordonner les colonnes:
- Pour les transactions, première écriture:
dateau format MM/DD/YYYYdescriptionde la transactioncommodity: monnaie utilisée pour la transaction (CAD, Euro, USD)account: compte sourcememovalue: montant de la transaction (positif ou négatif)
- Pour les transactions, seconde écriture:
dateau format MM/DD/YYYYdescriptionde la transactioncommodity: monnaie utilisée pour la transaction (CAD, Euro, USD)account: compte destinationmemovalue: montant de la transaction (signe opposé à la première écriture)
- Pour les ventilations:
- Colonne vide
- Colonne vide
- Colonne vide
account: compte destinationmemovalue: montant ventilé (signe opposé à la première écriture)
Comments