Zum Hauptinhalt springen

Abfragen mit SQL

Meistern Sie die Beancount-Abfragesprache (BQL) für fortgeschrittene Finanzdatenanalyse. Lernen Sie SQL-ähnliche Syntax, um Ihre Buchhaltungsdaten abzufragen und zu analysieren.

Beancount bietet eine leistungsstarke, SQL-ähnliche Abfragesprache (BQL), mit der Sie Ihre Finanzdaten präzise aufschlüsseln, analysieren und auswerten können. Ob Sie einen schnellen Bericht erstellen, einen Eintrag debuggen oder komplexe Analysen durchführen möchten – die Beherrschung von BQL ist der Schlüssel, um das volle Potenzial Ihres Klartext-Buchhaltungsjournals auszuschöpfen. Dieser Leitfaden führt Sie durch Struktur, Funktionen und bewährte Praktiken. 🔍

beancount.io BQL-Abfrageeditor, der SQL-ähnliche Abfragen gegen ein Beancount-Journal im Browser ausführt

Live-Journal erkunden →

Abfragestruktur und -ausführung​

Der Kern von BQL ist seine vertraute, von SQL inspirierte Syntax. Abfragen werden mit dem Befehlszeilenwerkzeug bea query ausgeführt, das Ihre Journaldatei verarbeitet und die Ergebnisse direkt im Terminal ausgibt. Jede Abfrage in diesem Leitfaden wurde mit Beancount 3.2.3 und beanquery 0.2.0 ausgeführt.

Grundformat einer Abfrage​

Eine BQL-Abfrage besteht aus drei Hauptklauseln: SELECT, FROM und WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: Gibt an, welche Datenspalten Sie abrufen möchten.
  • FROM: Filtert ganze Transaktionen bevor sie verarbeitet werden.
  • WHERE: Filtert die einzelnen Posten-Zeilen nachdem die Transaktion ausgewählt wurde.

Zwei-Ebenen-Filtersystem​

Den Unterschied zwischen den Klauseln FROM und WHERE zu verstehen, ist entscheidend für genaue Abfragen. BQL verwendet einen zweistufigen Filterprozess.

  1. Transaktionsebene (FROM) Diese Klausel wirkt auf ganze Transaktionen. Wenn eine Transaktion der FROM-Bedingung entspricht, wird die gesamte Transaktion (einschließlich aller ihrer Buchungen) an die nächste Stufe weitergegeben. Dies ist die primäre Methode zum Filtern von Daten, da sie die Integrität des doppelten Buchführungssystems bewahrt. Beispielsweise wählt FROM year = 2024 alle Transaktionen aus, die im Jahr 2024 stattfanden.

  2. Posten-Ebene (WHERE) Diese Klausel filtert die einzelnen Buchungen innerhalb der von der FROM-Klausel ausgewählten Transaktionen. Dies ist nützlich für die Darstellung und um sich auf bestimmte Teile einer Transaktion zu konzentrieren. Beachten Sie jedoch, dass das Filtern auf dieser Ebene die Integrität einer Transaktion in der Ausgabe "brechen" kann, da Sie möglicherweise nur eine Seite eines Eintrags sehen. Sie könnten beispielsweise alle Buchungen auf Ihr Konto Expenses:Groceries auswählen.

Konkret gibt PRINT FROM year = 2024 ganze Transaktionen zurück (beide Seiten jedes Eintrags), während SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" eine Zeile pro passender Buchung zurückgibt. Bei einem Journal mit zwei Broker-Käufen liefert die erste Abfrage die vollständigen Einträge und die zweite genau die beiden Broker-Zeilen.

Datenmodell​

Um Ihre Daten effektiv abzufragen, müssen Sie verstehen, wie Beancount sie strukturiert. Ein Journal ist eine Liste von Direktiven, aber BQL konzentriert sich hauptsächlich auf Transaction-Einträge.

Transaktionsstruktur​

Jede Transaction ist ein Container mit Attributen auf oberster Ebene und einer Liste von Posting-Objekten.

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

Verfügbare Spaltentypen​

Sie können jedes Attribut der Transaktion oder ihrer Buchungen mit SELECT auswählen.

  1. Transaktionsattribute Diese Spalten sind für jede Buchung innerhalb einer einzelnen Transaktion gleich.

    SELECT
    date,        -- The date of the transaction (datetime.date)
    year,        -- The year of the transaction (int)
    month,       -- The month of the transaction (int)
    day,         -- The day of the transaction (int)
    flag,        -- The transaction flag, e.g., "*" or "!" (str)
    payee,       -- The payee (str)
    narration,   -- The description or memo (str)
    tags,        -- A set of tags, e.g., #trip-2024 (set[str])
    links        -- A set of links, e.g., ^expense-report (set[str])

  2. Posten-Attribute Diese Spalten sind spezifisch für jede einzelne Buchungszeile.

    SELECT
    account,           -- The account name (str)
    position,          -- The full amount, including units and cost (Position)
    units(position),   -- The number and currency of the posting (Amount)
    cost(position),    -- The cost basis of the posting (Amount)
    price,             -- The price used in the posting (Amount)
    weight,            -- The position converted to its cost basis (Amount)
    balance            -- The running total of units in the account (Inventory)

    units und cost sind Funktionen, die position als Argument nehmen. price, weight und balance sind einfache Spalten.

Abfragefunktionen​

BQL enthält eine Reihe von Funktionen für Aggregation und Datentransformation, ähnlich wie SQL.

Aggregationsfunktionen​

Aggregationsfunktionen fassen Daten über mehrere Zeilen hinweg zusammen. In Verbindung mit GROUP BY liefern sie gruppierte Zusammenfassungen.

-- Count the number of postings
SELECT COUNT(*)
 
-- Sum all postings into one Inventory; currencies and lots are kept, not converted
SELECT SUM(position)
-- one row, e.g. (-2300.00 USD, 10 HOOL {150.00 USD, 2024-09-05}, 5 HOOL {160.00 USD, 2024-11-02})
 
-- Total one account in a single currency explicitly (positions without a price keep their currency)
SELECT SUM(CONVERT(position, 'USD')) WHERE account ~ "Assets:Checking"
-- one row, e.g. (2580.00 USD)
 
-- Find the date of the first and last transaction
SELECT FIRST(date), LAST(date)
 
-- Find the minimum and maximum position values
SELECT MIN(position), MAX(position)
 
-- Group by account to get a sum for each
SELECT account, SUM(position) GROUP BY account

Positions-/Bestandsfunktionen​

Die Spalte position ist ein zusammengesetztes Objekt. Diese Funktionen ermöglichen es Ihnen, bestimmte Teile davon zu extrahieren oder ihren Marktwert zu berechnen.

-- Extract just the number and currency from a position
SELECT UNITS(position)
 
-- Show the total cost of a position
SELECT COST(position)
 
-- Show each posting at its cost value (a column; there is no WEIGHT() function)
SELECT account, weight WHERE account ~ "Assets:Investments"
 
-- Calculate the market value using the latest price data
-- (needs a price directive for the holding; otherwise the position is returned unchanged)
SELECT VALUE(position)

Sie können diese für aussagekräftige Berichte kombinieren. Zum Beispiel, um die Gesamtkosten und den aktuellen Marktwert Ihres Anlageportfolios zu sehen. Der Marktwert benötigt eine price-Direktive für jeden Bestand (z. B. 2024-12-01 price HOOL 175.00 USD). Beide Aggregate geben ein Inventory pro Konto zurück, sodass jede Währung weiterhin separat aufgeführt wird.

SELECT
    account,
    COST(SUM(position)) AS total_cost,
    VALUE(SUM(position)) AS market_value
FROM
    account ~ "Assets:Investments"
GROUP BY
    account
-- one row per account, e.g. Assets:Broker:HOOL | (2300.00 USD) | (2625.00 USD)

Erweiterte Funktionen​

Über grundlegende SELECT-Anweisungen hinaus bietet BQL spezielle Befehle für häufige Finanzberichte.

Saldenberichte​

Die Anweisung BALANCES erzeugt eine Bilanz oder Gewinn- und Verlustrechnung für einen bestimmten Zeitraum.

-- Generate a simple balance sheet as of the start of 2024
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"
 
-- Generate an income statement for the 2024 fiscal year
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

Journalberichte​

Die Anweisung JOURNAL zeigt die detaillierten Aktivitäten für ein oder mehrere Konten, ähnlich einer herkömmlichen Journalansicht.

-- Show all activity in your checking account at its original cost
JOURNAL "Assets:Checking" AT COST
 
-- Show all 401k transactions, displaying only the units (shares)
JOURNAL "Assets:.*:401k" AT UNITS

Druckoperationen​

Die Anweisung PRINT ist ein Debugging-Werkzeug, das vollständige, passende Transaktionen in ihrem ursprünglichen Beancount-Dateiformat ausgibt. Sie akzeptiert nur einen Eintragsfilter. Eine WHERE-Klausel ist hier ein Syntaxfehler. Um die Ausgabe auf eine Seite jedes Eintrags zu beschränken, verwenden Sie SELECT mit einem Buchungsfilter. Es gibt eine Zeile pro passender Buchung zurück.

-- Print all 2024 transactions in full (every posting of each matching entry)
PRINT FROM year = 2024
 
-- Show only the investment postings of 2024 transactions
SELECT date, narration, account, position
FROM year = 2024
WHERE account ~ "Assets:Investments"
 
-- Find a transaction by its unique ID (generated by some tools)
-- Returns the matching entry, or no rows when nothing carries that ID
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

Filterausdrücke​

Sie können anspruchsvolle Filter erstellen mit logischen Operatoren (AND, OR), regulären Ausdrücken (~) und Vergleichen.

Zeichenkettenliterale verwenden einfache Anführungszeichen. Doppelte Anführungszeichen begrenzen den regulären Ausdruck nach ~.

-- Find all travel expenses from the second half of 2024
-- SELECT * returns date, flag, payee, narration and position per matching posting
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"
 
-- Find all transactions related to a vacation or business
SELECT * FROM
    'vacation-2024' IN tags OR
    'business-trip' IN links

Leistungsüberlegungen ⚙️​

bea query ist auf Effizienz ausgelegt, aber das Verständnis seines Betriebsablaufs kann Ihnen helfen, schnellere Abfragen auf großen Journalen zu schreiben.

  1. Daten laden: Beancount parst zunächst Ihre gesamte Journaldatei und sortiert alle Transaktionen chronologisch. Dieser gesamte Datensatz wird im Speicher gehalten.
  2. Abfrageoptimierung: Die Abfrage-Engine wendet Filter in einer bestimmten Reihenfolge für maximale Effizienz an: FROM (Transaktionen) → WHERE (Buchungen) → Aggregationen. Das Filtern auf FROM-Ebene ist am schnellsten, da es den Datensatz früh reduziert.
  3. Speichernutzung: Alle Operationen finden im Speicher statt. Position-Objekte und Inventory-Aggregationen sind optimiert, aber sehr große Ergebnismengen können erheblichen Arbeitsspeicher verbrauchen. BQL verwendet keine temporären Dateispeicher.

Bewährte Praktiken​

Befolgen Sie diese Tipps, um saubere, effektive und wartbare Abfragen zu schreiben.

  1. Abfrageorganisation Formatieren Sie Ihre Abfragen für Lesbarkeit, insbesondere komplexe. Verwenden Sie Zeilenumbrüche und Einrückungen, um Klauseln zu trennen.

    -- A clean, readable query for all 2024 expenses
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;

  2. Debugging Wenn eine Abfrage nicht wie erwartet funktioniert, führen Sie zuerst eine kleine Stichprobe mit LIMIT aus. Um einen Filter zu testen, verwenden Sie SELECT DISTINCT, um zu sehen, welche eindeutigen Werte er findet.

    -- Preview the first rows while iterating
SELECT date, account, position LIMIT 5;
 
-- Test which accounts match a regular expression
SELECT DISTINCT account
WHERE account ~ "^Assets:.*";

  3. Saldenprüfungen Sie können BQL verwenden, um die balance-Prüfungen in Ihrem Journal gegenzuprüfen. Diese Abfrage sollte den genauen Betrag zurückgeben, der in Ihrer letzten Saldenprüfung für dieses Konto angegeben wurde.

    -- Verify the final balance of your checking account
SELECT account, sum(position)
FROM close ON 2025-01-01 -- Use the date from your balance directive
WHERE account = "Assets:Checking";

Abfragen mit SQL

Quelle: https://beancount.io/de/docs/Basics/beancount-query-language