Salta al contingut principal

BQL de Beancount: consulta el teu llibre major

Consulta el teu llibre major de Beancount amb BQL, un llenguatge tipus SQL: sintaxi SELECT, columnes, funcions d'agregació i d'inventari, i informes executats amb bea query.

Beancount inclou un potós Llenguatge de Consulta (BQL) similar a SQL que et permet tallar, esmicolar i analitzar les teves dades financeres amb precisió. Tant si vols generar un informe ràpid, depurar un assentament o realitzar anàlisis complexes, dominar BQL és la clau per desbloquejar tot el potencial del teu llibre comptable de text pla. Aquesta guia et guiarà per la seva estructura, funcions i bones pràctiques. 🔍

Editor de consultes BQL de beancount.io executant consultes similars a SQL contra un llibre de Beancount al navegador

Explora el llibre en directe →

Estructura i execució de consultes​

El nucli de BQL és la seva sintaxi familiar inspirada en SQL. Executa consultes amb bea query: bea --file <ledger> query "SELECT …" imprimeix la taula al teu terminal, i bea query sense arguments obre l'intèrpret interactiu. Totes les consultes d'aquesta guia es van executar amb Beancount 3.2.3 i beanquery 0.2.0.

Format bàsic de consulta​

Una consulta BQL es compon de tres clàusules principals: SELECT, FROM i WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: Especifica quines columnes de dades vols recuperar.
  • FROM: Filtra transaccions senceres abans que es processin.
  • WHERE: Filtra les línies individuals d'apunts després que la transacció hagi estat seleccionada.

Sistema de filtratge en dos nivells​

Entendre la diferència entre les clàusules FROM i WHERE és crucial per escriure consultes precises. BQL utilitza un procés de filtratge de dos nivells.

  1. Nivell de transacció (FROM) Aquesta clàusula actua sobre transaccions senceres. Si una transacció coincideix amb la condició FROM, la transacció sencera (incloent tots els seus apunts) es passa a la següent etapa. Aquesta és la manera principal de filtrar dades, ja que preserva la integritat del sistema de partida doble. Per exemple, filtrar FROM year = 2024 selecciona totes les transaccions que van ocórrer el 2024.

  2. Nivell d'apunt (WHERE) Aquesta clàusula filtra els apunts individuals dins de les transaccions seleccionades per la clàusula FROM. Això és útil per a la presentació i per centrar-se en parts específiques d'una transacció. No obstant això, tingues en compte que filtrar en aquest nivell pot "trencar" la integritat d'una transacció a la sortida, ja que podries veure només un costat d'un assentament. Per exemple, podries seleccionar tots els apunts del teu compte Expenses:Groceries.

Concretament, PRINT FROM year = 2024 retorna transaccions senceres (ambdós costats de cada assentament), mentre que SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" retorna una fila per cada apunt coincident. En un llibre amb dues compres de broker, la primera retorna els assentaments complets i la segona retorna exactament les dues files del broker.

Model de dades​

Per consultar les teves dades de manera efectiva, has d'entendre com les estructura Beancount. Un llibre és una llista de directives, però BQL se centra principalment en els assentaments Transaction.

Estructura de la transacció​

Cada Transaction és un contenidor amb atributs de nivell superior i una llista d'objectes Posting.

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

Tipus de columnes disponibles​

Pots fer SELECT de qualsevol dels atributs de la transacció o dels seus apunts.

  1. Atributs de transacció Aquestes columnes són les mateixes per a cada apunt dins d'una sola transacció.

    SELECT
    date,        -- La data de la transacció (datetime.date)
    year,        -- L'any de la transacció (int)
    month,       -- El mes de la transacció (int)
    day,         -- El dia de la transacció (int)
    flag,        -- La marca de la transacció, p. ex., "*" o "!" (str)
    payee,       -- El beneficiari (str)
    narration,   -- La descripció o nota (str)
    tags,        -- Un conjunt d'etiquetes, p. ex., #trip-2024 (set[str])
    links        -- Un conjunt d'enllaços, p. ex., ^expense-report (set[str])

  2. Atributs d'apunt Aquestes columnes són específiques de cada línia d'apunt individual.

    SELECT
    account,           -- El nom del compte (str)
    position,          -- L'import complet, incloent unitats i cost (Position)
    units(position),   -- El nombre i la divisa de l'apunt (Amount)
    cost(position),    -- La base de cost de l'apunt (Amount)
    price,             -- El preu utilitzat a l'apunt (Amount)
    weight,            -- La posició convertida a la seva base de cost (Amount)
    balance            -- El total acumulat d'unitats al compte (Inventory)

    units i cost són funcions que prenen position. price, weight i balance són columnes simples.

Funcions de consulta​

BQL inclou un conjunt de funcions per a l'agregació i la transformació de dades, igual que SQL.

Funcions d'agregació​

Les funcions d'agregació resumeixen dades de múltiples files. Quan s'utilitzen amb GROUP BY, proporcionen resums agrupats.

-- Compta el nombre d'apunts
SELECT COUNT(*)
 
-- Suma tots els apunts en un sol Inventory; les divises i lots es mantenen, no es converteixen
SELECT SUM(position)
-- una fila, p. ex. (-2300.00 USD, 10 HOOL {150.00 USD, 2024-09-05}, 5 HOOL {160.00 USD, 2024-11-02})
 
-- Totalitza un compte en una sola divisa explícitament (les posicions sense preu mantenen la seva divisa)
SELECT SUM(CONVERT(position, 'USD')) WHERE account ~ "Assets:Checking"
-- una fila, p. ex. (2580.00 USD)
 
-- Troba la data de la primera i l'última transacció
SELECT FIRST(date), LAST(date)
 
-- Troba els valors mínim i màxim de posició
SELECT MIN(position), MAX(position)
 
-- Agrupa per compte per obtenir una suma per a cadascun
SELECT account, SUM(position) GROUP BY account

Funcions de posició/inventari​

La columna position és un objecte compost. Aquestes funcions et permeten extreure'n parts específiques o calcular-ne el valor de mercat.

-- Extreu només el nombre i la divisa d'una posició
SELECT UNITS(position)
 
-- Mostra el cost total d'una posició
SELECT COST(position)
 
-- Mostra cada apunt al seu valor de cost (una columna; no hi ha funció WEIGHT())
SELECT account, weight WHERE account ~ "Assets:Investments"
 
-- Calcula el valor de mercat utilitzant les dades de preu més recents
-- (necessita una directiva price per a la participació; en cas contrari, la posició es retorna sense canvis)
SELECT VALUE(position)

Pots combinar aquestes per obtenir informes potents. Per exemple, per veure el cost total i el valor de mercat actual de la teva cartera d'inversions. El valor de mercat necessita una directiva price per a cada participació (per exemple 2024-12-01 price HOOL 175.00 USD). Ambdós agregats retornen un Inventory per compte, així que cada divisa encara es llista per separat.

SELECT
    account,
    COST(SUM(position)) AS total_cost,
    VALUE(SUM(position)) AS market_value
FROM
    account ~ "Assets:Investments"
GROUP BY
    account
-- una fila per compte, p. ex. Assets:Broker:HOOL | (2300.00 USD) | (2625.00 USD)

Les directives de preu utilitzades per les consultes de valor de mercat poden provenir d'entrades manuals, d'un obtenidor de cotitzacions local o de Preus en directe en un carregador compatible. Els feeds gestionats no canvien la sintaxi de la consulta. Les eines upstream locals necessiten fitxers de preus locals, i les consultes històriques encara necessiten preus en o abans de la data sol·licitada.

Funcions avançades​

Més enllà de les sentències bàsiques SELECT, BQL ofereix ordres especialitzades per a informes financers comuns.

Informes de balanç​

La sentència BALANCES genera un balanç de situació o un compte de resultats per a un període específic.

-- Genera un balanç de situació simple a data d'inici del 2024
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"
 
-- Genera un compte de resultats per a l'exercici fiscal 2024
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

Informes de diari​

La sentència JOURNAL mostra l'activitat detallada d'un o més comptes, similar a una vista tradicional de llibre major.

-- Mostra tota l'activitat del teu compte corrent al seu cost original
JOURNAL "Assets:Checking" AT COST
 
-- Mostra totes les transaccions del 401k, mostrant només les unitats (accions)
JOURNAL "Assets:.*:401k" AT UNITS

Operacions d'impressió​

La sentència PRINT és una eina de depuració que mostra transaccions completes coincidents en el seu format original de fitxer Beancount. Només accepta un filtre d'assentaments. Una clàusula WHERE aquí és un error de sintaxi. Per restringir la sortida a un costat de cada assentament, utilitza SELECT amb un filtre d'apunts. Retorna una fila per cada apunt coincident.

-- Imprimeix totes les transaccions del 2024 completes (cada apunt de cada assentament coincident)
PRINT FROM year = 2024
 
-- Mostra només els apunts d'inversió de les transaccions del 2024
SELECT date, narration, account, position
FROM year = 2024
WHERE account ~ "Assets:Investments"
 
-- Troba una transacció pel seu ID únic (generat per algunes eines)
-- Retorna l'assentament coincident, o cap fila quan res porta aquell ID
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

Expressions de filtratge​

Pots construir filtres sofisticats utilitzant operadors lògics (AND, OR), expressions regulars (~) i comparacions.

Els literals de cadena utilitzen cometes simples. Les cometes dobles delimiten l'expressió regular després de ~.

-- Troba totes les despeses de viatge del segon semestre del 2024
-- SELECT * retorna date, flag, payee, narration i position per cada apunt coincident
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"
 
-- Troba totes les transaccions relacionades amb unes vacances o un viatge de feina
SELECT * FROM
    'vacation-2024' IN tags OR
    'business-trip' IN links

Consideracions de rendiment ⚙️​

bea query està dissenyat per a l'eficiència, però entendre el seu flux operatiu et pot ajudar a escriure consultes més ràpides en llibres grans.

  1. Càrrega de dades: Beancount primer analitza tot el teu fitxer de llibre i ordena totes les transaccions cronològicament. Aquest conjunt de dades sencer es manté a la memòria.
  2. Optimització de consultes: El motor de consultes aplica els filtres en un ordre específic per obtenir la màxima eficiència: FROM (transaccions) -> WHERE (apunts) -> Agregacions. Filtrar a nivell FROM és el més ràpid perquè redueix el conjunt de dades aviat.
  3. Ús de memòria: Totes les operacions es fan a la memòria. Els objectes Position i les agregacions Inventory estan optimitzats, però conjunts de resultats molt grans poden consumir una RAM significativa. BQL no utilitza emmagatzematge temporal en disc.

Bones pràctiques​

Segueix aquests consells per escriure consultes netes, efectives i mantenibles.

  1. Organització de consultes Formata les teves consultes per a la llegibilitat, especialment les complexes. Utilitza salts de línia i indentació per separar les clàusules.

    -- Una consulta neta i llegible per a totes les despeses del 2024
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;

  2. Depuració Si una consulta no funciona com s'esperava, executa primer una mostra petita amb LIMIT. Per provar un filtre, utilitza SELECT DISTINCT per veure quins valors únics coincideixen.

    -- Previsualitza les primeres files mentre iteres
SELECT date, account, position LIMIT 5;
 
-- Prova quins comptes coincideixen amb una expressió regular
SELECT DISTINCT account
WHERE account ~ "^Assets:.*";

  3. Assercions de saldo Pots utilitzar BQL per verificar les assercions de balance del teu llibre. Aquesta consulta hauria de retornar l'import exacte especificat a la teva darrera comprovació de saldo per a aquell compte.

    -- Verifica el saldo final del teu compte corrent
SELECT account, sum(position)
FROM close ON 2025-01-01 -- Utilitza la data de la teva directiva balance
WHERE account = "Assets:Checking";

Comparteix aquesta pàgina

Comparteix a WhatsAppComparteix a LinkedInComparteix a XComparteix a FacebookComparteix a Threads

Aquesta pàgina t'ha estat útil?

BQL de Beancount: consulta el teu llibre major

Font: https://beancount.io/ca/docs/Basics/beancount-query-language