duckdb_ledger

February 6, 2026 · View on GitHub

A DuckDB extension that exposes ledger-cli journal files as SQL virtual tables. Query your plain-text accounting data with the full power of SQL.

Quick Start

# Install DuckDB if you don't have it
brew install duckdb   # macOS

# Install the extension (one-time)
duckdb -unsigned -c "
  INSTALL '/path/to/duckdb_ledger/build/release/extension/duckdb_ledger/duckdb_ledger.duckdb_extension';
"

# Query your journal
duckdb -unsigned -c "
  LOAD duckdb_ledger;
  SELECT payee, sum(amount) as total
  FROM ledger_postings('~/finances/main.journal')
  WHERE account LIKE 'Expenses:%'
  GROUP BY payee ORDER BY total DESC;
"

Usage

The -unsigned flag is required because this is a locally-built extension without an official signature.

Interactive session

duckdb -unsigned

LOAD duckdb_ledger;

-- Now query any ledger journal file
SELECT * FROM ledger_postings('~/finances/main.journal') LIMIT 10;
SELECT * FROM ledger_transactions('~/finances/main.journal') LIMIT 10;

One-liner from the command line

duckdb -unsigned -c "
  LOAD duckdb_ledger;
  SELECT date, payee, account, amount
  FROM ledger_postings('~/finances/main.journal')
  WHERE date >= '2024-01-01';
"

Export to CSV or Parquet

duckdb -unsigned -c "
  LOAD duckdb_ledger;
  COPY (SELECT * FROM ledger_postings('~/finances/main.journal'))
  TO 'postings.parquet' (FORMAT PARQUET);
"

Table Functions

`ledger_postings(filepath)`

Returns one row per posting. This is the most granular view of your journal.

Column	Type	Description
`txn_id`	INTEGER	Auto-incrementing transaction ID
`date`	DATE	Transaction date
`aux_date`	DATE	Auxiliary/effective date (nullable)
`status`	VARCHAR	`cleared`, `pending`, or `uncleared`
`code`	VARCHAR	Check number or reference code (nullable)
`payee`	VARCHAR	Payee/description
`txn_note`	VARCHAR	Transaction-level note (nullable)
`uuid`	VARCHAR	UUID tag if present (nullable)
`account`	VARCHAR	Full account name (e.g., `Expenses:Food`)
`amount`	DECIMAL(18,8)	Posting amount (balancing amounts computed automatically)
`commodity`	VARCHAR	Currency/commodity symbol (e.g., `$`, `EUR`)
`is_virtual`	BOOLEAN	True for virtual postings `(Account)` and `[Account]`
`is_balance_assignment`	BOOLEAN	True for balance assignments (`= \$500`)
`cost_amount`	DECIMAL(18,8)	Cost amount for `@`/`@@` postings (nullable)
`cost_commodity`	VARCHAR	Cost commodity (nullable)
`cost_is_total`	BOOLEAN	True if cost was specified with `@@` (total cost)
`post_note`	VARCHAR	Posting-level note (nullable)
`source_file`	VARCHAR	Source journal file path
`line_number`	BIGINT	Line number in source file

`ledger_transactions(filepath)`

Returns one row per transaction. Useful for transaction-level analysis.

Column	Type	Description
`txn_id`	INTEGER	Auto-incrementing transaction ID
`date`	DATE	Transaction date
`aux_date`	DATE	Auxiliary/effective date (nullable)
`status`	VARCHAR	`cleared`, `pending`, or `uncleared`
`code`	VARCHAR	Check number or reference code (nullable)
`payee`	VARCHAR	Payee/description
`note`	VARCHAR	Transaction note (nullable)
`uuid`	VARCHAR	UUID tag if present (nullable)
`num_postings`	INTEGER	Number of postings in this transaction
`source_file`	VARCHAR	Source journal file path
`line_number`	BIGINT	Line number in source file
`metadata`	VARCHAR	JSON object of all tags/metadata (nullable)

Examples

Monthly expense breakdown

SELECT
    date_trunc('month', date) AS month,
    split_part(account, ':', 2) AS category,
    sum(amount) AS total
FROM ledger_postings('finances.journal')
WHERE account LIKE 'Expenses:%'
GROUP BY month, category
ORDER BY month DESC, total DESC;

Top payees by spend

SELECT payee, sum(amount) AS total_spent
FROM ledger_postings('finances.journal')
WHERE account LIKE 'Expenses:%'
GROUP BY payee
ORDER BY total_spent DESC
LIMIT 20;

Account balances

SELECT account, commodity, sum(amount) AS balance
FROM ledger_postings('finances.journal')
GROUP BY account, commodity
HAVING sum(amount) != 0
ORDER BY account;

Uncleared transactions

SELECT date, payee, sum(amount) AS total
FROM ledger_postings('finances.journal')
WHERE status = 'uncleared' AND account LIKE 'Expenses:%'
GROUP BY txn_id, date, payee
ORDER BY date DESC;

Monthly income vs expenses

SELECT
    date_trunc('month', date) AS month,
    sum(CASE WHEN account LIKE 'Income:%' THEN -amount ELSE 0 END) AS income,
    sum(CASE WHEN account LIKE 'Expenses:%' THEN amount ELSE 0 END) AS expenses,
    sum(CASE WHEN account LIKE 'Income:%' THEN -amount ELSE 0 END)
      - sum(CASE WHEN account LIKE 'Expenses:%' THEN amount ELSE 0 END) AS savings
FROM ledger_postings('finances.journal')
GROUP BY month
ORDER BY month DESC;

Multi-currency holdings

SELECT account, commodity, sum(amount) AS balance
FROM ledger_postings('finances.journal')
WHERE account LIKE 'Assets:%'
GROUP BY account, commodity
HAVING sum(amount) != 0
ORDER BY account, commodity;

Spending trend (rolling 30-day average)

WITH daily AS (
    SELECT date, sum(amount) AS daily_total
    FROM ledger_postings('finances.journal')
    WHERE account LIKE 'Expenses:%'
    GROUP BY date
)
SELECT date, daily_total,
    avg(daily_total) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM daily
ORDER BY date DESC
LIMIT 90;

Export to CSV or Parquet

COPY (
    SELECT date, payee, account, amount, commodity
    FROM ledger_postings('finances.journal')
) TO 'postings.csv' (HEADER);

COPY (
    SELECT * FROM ledger_postings('finances.journal')
) TO 'postings.parquet' (FORMAT PARQUET);

The extension uses libledger to parse journal files. On each query, the journal is parsed during the bind phase and all data is materialized into DuckDB vectors. Balancing posting amounts are computed automatically via ledger's finalize() so every posting has a non-null amount.

Include directives (include other.journal) are resolved by ledger's parser, so you can query multi-file journals by pointing at the top-level file.

Claude Code Skill

This repo includes a /ledger-query skill for Claude Code that lets your coding agent query your financial data using natural language.

Install the skill

ln -s /path/to/duckdb_ledger/skills/ledger-query ~/.claude/skills/ledger-query

Use it

In any Claude Code session:

/ledger-query how much did I spend on groceries last month?
/ledger-query what are my account balances?
/ledger-query show monthly income vs expenses for 2024

Or just ask about your finances naturally — Claude will invoke the skill automatically based on context.

Building from Source

Prerequisites

macOS:

brew install boost gmp mpfr cmake ninja

Ubuntu/Debian:

sudo apt install libboost-all-dev libgmp-dev libmpfr-dev cmake ninja-build

Build

git clone --recursive https://github.com/your-username/duckdb_ledger.git
cd duckdb_ledger
GEN=ninja make release

This produces:

./build/release/duckdb — DuckDB binary with the extension statically linked
./build/release/extension/duckdb_ledger/duckdb_ledger.duckdb_extension — loadable extension for system DuckDB

Install into system DuckDB

duckdb -unsigned -c "INSTALL '$(pwd)/build/release/extension/duckdb_ledger/duckdb_ledger.duckdb_extension';"

After this, LOAD duckdb_ledger works in any duckdb -unsigned session.

Run tests

./build/release/test/unittest "test/sql/*"