Aller au contenu principal

Beancount Query Language (BQL) : interroger le grand livre

Interrogez votre grand livre Beancount avec BQL, un langage de type SQL : syntaxe SELECT, colonnes, fonctions d'agrégation et d'inventaire, et rapports exécutés avec bea query.

Beancount dispose d'un puissant langage de requête de type SQL (BQL) qui vous permet de découper, analyser et explorer vos données financières avec précision. Que vous souhaitiez générer un rapport rapide, déboguer une écriture ou effectuer des analyses complexes, maîtriser BQL est essentiel pour exploiter tout le potentiel de votre grand livre comptable en texte brut. Ce guide vous présente sa structure, ses fonctions et ses bonnes pratiques. 🔍

Éditeur de requêtes BQL de beancount.io exécutant des requêtes de type SQL sur un grand livre Beancount dans le navigateur

Explorer le grand livre en direct →

Structure et exécution des requêtes​

Le cœur de BQL réside dans sa syntaxe familière, inspirée de SQL. Exécutez les requêtes avec bea query : bea --file <ledger> query "SELECT …" affiche le tableau dans votre terminal, et bea query sans argument ouvre le shell interactif. Toutes les requêtes de ce guide ont été exécutées avec Beancount 3.2.3 et beanquery 0.2.0.

Format de requête de base​

Une requête BQL est composée de trois clauses principales : SELECT, FROM et WHERE.

SELECT <cible1>, <cible2>, ...
FROM <expression-de-filtre-d-écritures>
WHERE <expression-de-filtre-d-écritures-comptables>;
  • SELECT : Spécifie les colonnes de données que vous souhaitez récupérer.
  • FROM : Filtre les transactions entières avant qu'elles ne soient traitées.
  • WHERE : Filtre les lignes d'écriture individuelles après que la transaction a été sélectionnée.

Système de filtrage à deux niveaux​

Comprendre la différence entre les clauses FROM et WHERE est essentiel pour écrire des requêtes précises. BQL utilise un processus de filtrage à deux niveaux.

  1. Niveau transaction (FROM) Cette clause agit sur des transactions entières. Si une transaction correspond à la condition FROM, la transaction entière (y compris toutes ses écritures) est transmise à l'étape suivante. C'est la manière principale de filtrer les données, car elle préserve l'intégrité du système comptable en partie double. Par exemple, filtrer FROM year = 2024 sélectionne toutes les transactions survenues en 2024.

  2. Niveau écriture (WHERE) Cette clause filtre les écritures individuelles à l'intérieur des transactions sélectionnées par la clause FROM. C'est utile pour la présentation et pour se concentrer sur des contreparties spécifiques d'une transaction. Cependant, sachez que filtrer à ce niveau peut « casser » l'intégrité d'une transaction dans le résultat, car vous ne verrez peut-être qu'un seul côté d'une écriture. Par exemple, vous pourriez sélectionner toutes les écritures du compte Expenses:Groceries.

Concrètement, PRINT FROM year = 2024 renvoie des transactions entières (les deux contreparties de chaque écriture), tandis que SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" renvoie une ligne par écriture correspondante. Sur un grand livre comportant deux achats chez un courtier, la première renvoie les écritures complètes et la seconde renvoie exactement les deux lignes du courtier.

Modèle de données​

Pour interroger efficacement vos données, vous devez comprendre comment Beancount les structure. Un grand livre est une liste de directives, mais BQL se concentre principalement sur les écritures Transaction.

Structure des transactions​

Chaque Transaction est un conteneur avec des attributs de premier niveau et une liste d'objets Posting.

Transaction
├── date
├── flag
├── payee
├── narration
├── tags
├── links
└── Postings[]
    ├── account
    ├── units
    ├── cost
    ├── price
    └── metadata

Types de colonnes disponibles​

Vous pouvez SELECT n'importe lequel des attributs de la transaction ou de ses écritures.

  1. Attributs de transaction Ces colonnes sont identiques pour chaque écriture au sein d'une même transaction.

    SELECT
    date,        -- La date de la transaction (datetime.date)
    year,        -- L'année de la transaction (int)
    month,       -- Le mois de la transaction (int)
    day,         -- Le jour de la transaction (int)
    flag,        -- Le drapeau de la transaction, par exemple "*" ou "!" (str)
    payee,       -- Le bénéficiaire (str)
    narration,   -- La description ou le mémo (str)
    tags,        -- Un ensemble d'étiquettes, par exemple #trip-2024 (set[str])
    links        -- Un ensemble de liens, par exemple ^expense-report (set[str])

  2. Attributs d'écriture Ces colonnes sont spécifiques à chaque ligne d'écriture individuelle.

    SELECT
    account,           -- Le nom du compte (str)
    position,          -- Le montant complet, incluant les unités et le coût (Position)
    units(position),   -- Le nombre et la devise de l'écriture (Amount)
    cost(position),    -- Le coût de base de l'écriture (Amount)
    price,             -- Le prix utilisé dans l'écriture (Amount)
    weight,            -- La position convertie à son coût de base (Amount)
    balance            -- Le total cumulé des unités dans le compte (Inventory)

    units et cost sont des fonctions qui prennent position en argument. price, weight et balance sont des colonnes simples.

Fonctions de requête​

BQL inclut une suite de fonctions pour l'agrégation et la transformation des données, tout comme SQL.

Fonctions d'agrégation​

Les fonctions d'agrégation résument les données sur plusieurs lignes. Utilisées avec GROUP BY, elles fournissent des synthèses regroupées.

-- Compter le nombre d'écritures
SELECT COUNT(*)
 
-- Additionner toutes les écritures en un seul Inventory ; les devises et les lots sont conservés, non convertis
SELECT SUM(position)
-- une ligne, par exemple (-2300.00 USD, 10 HOOL {150.00 USD, 2024-09-05}, 5 HOOL {160.00 USD, 2024-11-02})
 
-- Totaliser un compte dans une seule devise explicitement (les positions sans prix conservent leur devise)
SELECT SUM(CONVERT(position, 'USD')) WHERE account ~ "Assets:Checking"
-- une ligne, par exemple (2580.00 USD)
 
-- Trouver la date de la première et de la dernière transaction
SELECT FIRST(date), LAST(date)
 
-- Trouver les valeurs minimale et maximale de position
SELECT MIN(position), MAX(position)
 
-- Regrouper par compte pour obtenir une somme pour chacun
SELECT account, SUM(position) GROUP BY account

Fonctions de position/inventaire​

La colonne position est un objet composite. Ces fonctions vous permettent d'en extraire des parties spécifiques ou de calculer sa valeur de marché.

-- Extraire uniquement le nombre et la devise d'une position
SELECT UNITS(position)
 
-- Afficher le coût total d'une position
SELECT COST(position)
 
-- Afficher chaque écriture à sa valeur de coût (une colonne ; il n'existe pas de fonction WEIGHT())
SELECT account, weight WHERE account ~ "Assets:Investments"
 
-- Calculer la valeur de marché en utilisant les données de prix les plus récentes
-- (nécessite une directive price pour le titre détenu ; sinon la position est renvoyée inchangée)
SELECT VALUE(position)

Vous pouvez combiner ces fonctions pour produire des rapports puissants. Par exemple, pour voir le coût total et la valeur de marché actuelle de votre portefeuille d'investissement. La valeur de marché nécessite une directive price pour chaque titre détenu (par exemple 2024-12-01 price HOOL 175.00 USD). Les deux agrégats renvoient un Inventory par compte, donc chaque devise est toujours listée séparément.

SELECT
    account,
    COST(SUM(position)) AS total_cost,
    VALUE(SUM(position)) AS market_value
FROM
    account ~ "Assets:Investments"
GROUP BY
    account
-- une ligne par compte, par exemple Assets:Broker:HOOL | (2300.00 USD) | (2625.00 USD)

Les directives de prix utilisées par les requêtes de valeur de marché peuvent provenir d'écritures manuelles, d'un récupérateur de cotations local ou de Live Prices dans un chargeur compatible. Les flux gérés ne modifient pas la syntaxe des requêtes. Les outils locaux en amont nécessitent des fichiers de prix locaux, et les requêtes historiques nécessitent toujours des prix à la date demandée ou avant celle-ci.

Fonctionnalités avancées​

Au-delà des instructions SELECT de base, BQL propose des commandes spécialisées pour les rapports financiers courants.

Rapports de solde​

L'instruction BALANCES génère un bilan ou un compte de résultat pour une période donnée.

-- Générer un bilan simple au début de 2024
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"
 
-- Générer un compte de résultat pour l'exercice 2024
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

Rapports de journal​

L'instruction JOURNAL affiche l'activité détaillée d'un ou plusieurs comptes, de manière similaire à une vue de grand livre traditionnelle.

-- Afficher toute l'activité de votre compte courant à son coût d'origine
JOURNAL "Assets:Checking" AT COST
 
-- Afficher toutes les transactions 401k, en ne montrant que les unités (parts)
JOURNAL "Assets:.*:401k" AT UNITS

Opérations d'impression​

L'instruction PRINT est un outil de débogage qui affiche les transactions complètes correspondantes dans leur format de fichier Beancount d'origine. Elle n'accepte qu'un filtre d'écritures. Une clause WHERE constitue ici une erreur de syntaxe. Pour restreindre la sortie à une contrepartie de chaque écriture, utilisez plutôt SELECT avec un filtre d'écritures. Cela renvoie une ligne par écriture correspondante.

-- Afficher toutes les transactions de 2024 en entier (chaque écriture de chaque transaction correspondante)
PRINT FROM year = 2024
 
-- Afficher uniquement les écritures d'investissement des transactions de 2024
SELECT date, narration, account, position
FROM year = 2024
WHERE account ~ "Assets:Investments"
 
-- Trouver une transaction par son ID unique (généré par certains outils)
-- Renvoie l'écriture correspondante, ou aucune ligne si rien ne porte cet ID
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

Expressions de filtrage​

Vous pouvez construire des filtres sophistiqués à l'aide d'opérateurs logiques (AND, OR), d'expressions régulières (~) et de comparaisons.

Les littéraux de chaîne utilisent des guillemets simples. Les guillemets doubles délimitent l'expression régulière après ~.

-- Trouver toutes les dépenses de voyage du second semestre 2024
-- SELECT * renvoie date, flag, payee, narration et position par écriture correspondante
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"
 
-- Trouver toutes les transactions liées à des vacances ou à un voyage d'affaires
SELECT * FROM
    'vacation-2024' IN tags OR
    'business-trip' IN links

Considérations de performance ⚙️​

bea query est conçu pour être efficace, mais comprendre son flux opérationnel peut vous aider à écrire des requêtes plus rapides sur de grands livres.

  1. Chargement des données : Beancount parse d'abord l'intégralité de votre fichier de grand livre et trie toutes les transactions par ordre chronologique. L'ensemble de ce jeu de données est conservé en mémoire.
  2. Optimisation des requêtes : Le moteur de requête applique les filtres dans un ordre spécifique pour une efficacité maximale : FROM (transactions) -> WHERE (écritures) -> Agrégations. Le filtrage au niveau FROM est le plus rapide, car il réduit le jeu de données dès le départ.
  3. Utilisation de la mémoire : Toutes les opérations se déroulent en mémoire. Les objets Position et les agrégations Inventory sont optimisés, mais de très grands ensembles de résultats peuvent consommer une RAM importante. BQL n'utilise pas de stockage temporaire sur disque.

Bonnes pratiques​

Suivez ces conseils pour écrire des requêtes claires, efficaces et maintenables.

  1. Organisation des requêtes Formatez vos requêtes pour la lisibilité, en particulier les plus complexes. Utilisez des sauts de ligne et l'indentation pour séparer les clauses.

    -- Une requête claire et lisible pour toutes les dépenses de 2024
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;

  2. Débogage Si une requête ne fonctionne pas comme prévu, exécutez d'abord un petit échantillon avec LIMIT. Pour tester un filtre, utilisez SELECT DISTINCT pour voir les valeurs uniques qu'il fait correspondre.

    -- Prévisualiser les premières lignes pendant l'itération
SELECT date, account, position LIMIT 5;
 
-- Tester quels comptes correspondent à une expression régulière
SELECT DISTINCT account
WHERE account ~ "^Assets:.*";

  3. Assertions de balance Vous pouvez utiliser BQL pour vérifier les assertions de balance de votre grand livre. Cette requête devrait renvoyer le montant exact spécifié dans votre dernière vérification de balance pour ce compte.

    -- Vérifier le solde final de votre compte courant
SELECT account, sum(position)
FROM close ON 2025-01-01 -- Utilisez la date de votre directive balance
WHERE account = "Assets:Checking";

Partager cette page

Partager sur LinkedInPartager sur XPartager sur WhatsAppPartager sur BlueskyPartager sur Facebook

Cette page vous a-t-elle été utile ?

Beancount Query Language (BQL) : interroger le grand livre

Source : https://beancount.io/fr/docs/Basics/beancount-query-language