Every now and then, I have a higher-than-usual spending month even though I don't remember going crazy with any specific thing.

I've tracked my expenses and income for almost a decade now -- initially with Apple Numbers spreadsheets, and finally migrating all of it to ledger-cli in July of this year. While ledger-cli helps to understand how much is flowing in and out, it's not straightforward to pinpoint what caused the expenses in a month to blow up.

So I figured: why not export the data and analyze it to answer this question myself?

If you're interested in tracking your finances and are considering some form of plain text accounting (e.g., ledger-cli, hledger, beancount), I hope this post is an interesting showcase.

Requirements: ledger-cli and DuckDB.

Spotting the spikes

While checking transactions and totals with ledger-cli is simple, identifying an unusual spike in spending is not as easy, because you can't compare expense categories month-by-month.

In order to identify that, I needed a breakdown like the one in the picture above. This made it easier to eyeball any given category month-by-month and identify spikes.

For example, in the sample report above, the spending in the Expenses:Misc category is much higher than usual in 2025.02 and 2025.08, accounting for most of the spike in those two months.

Plaintext accounting in 30 seconds

Most people don't track expenses/incomes at all. Some people do it with pen-and-paper or with spreadsheets. Those who want flexibility do it in a format that's easy to write, easy to read, and easy to extend. There are multiple ways to do it, but plaintext will likely outlast all of them.

A transaction in ledger-cli looks like:

2025-11-23 bread and milk - supermarket
    Expenses:Groceries          10.50 EUR
    Assets:Checking            -10.50 EUR

It notes the date, the payee, where the money went, where the money came from, and the amounts. ledger-cli also ensures that each transaction balances out to 0, which is the foundation of double-entry accounting. There can also be multiple sources and destinations, as well as mixed currencies, allowing for more complex transactions, but we won't be using those today.

The ledger-cli tool then provides you with multiple commands to slice and dice these transactions and answer your questions.

Example ledger file

As an example, let's use the following ledger file (main.journal):

; declare currencies
commodity EUR
    format 1,000.00 EUR

; declare accounts
account Assets:Cash
account Assets:Checking
account Equity:OpeningBalances
account Expenses:Bills
account Expenses:Food
account Expenses:Groceries
account Liabilities:CreditCard

; init balances
2025-10-01 opening balances
    Assets:Checking         2000 EUR
    Assets:Cash              300 EUR
    Liabilities:CreditCard     0 EUR
    Equity:OpeningBalances

; last month's expenses
2025-10-01 supermarket
    Expenses:Groceries      150 EUR
    Assets:Checking

2025-10-02 hotdog
    Expenses:Food           6.50 EUR
    Assets:Cash

2025-10-03 electricity bill
    Expenses:Bills          100 EUR
    Assets:Checking

2025-10-04 ice cream
    Expenses:Food           10 EUR
    Liabilities:CreditCard

; this month's expenses
2025-11-01 supermarket
    Expenses:Groceries      120 EUR
    Assets:Checking

2025-11-03 electricity bill
    Expenses:Bills          200 EUR
    Assets:Checking

2025-11-04 ice cream
    Expenses:Food           50 EUR
    Assets:Cash

Declaring currencies and accounts upfront is not required, but it really helps with keeping a tidy and error-free ledger file (more on this later).

ledger-cli essentials

Before diving into the custom report generation, you should learn some of what ledger-cli can do out of the box.

After noting down your expenses, you may wonder how much money you have left in your asset accounts:

λ ledger --file main.journal balance assets
        1,673.50 EUR  Assets
          243.50 EUR    Cash
        1,430.00 EUR    Checking
--------------------
        1,673.50 EUR

Or how much you owe on your credit card:

λ ledger --file main.journal balance liabilities:creditcard
          -10.00 EUR  Liabilities:CreditCard

Perhaps you want to review your food transactions, to decide if you need to go easier on ice cream:

λ ledger --file main.journal register expenses:food
25-Oct-02 hotdog             Expenses:Food          6.50 EUR    6.50 EUR
25-Oct-04 ice cream          Expenses:Food         10.00 EUR   16.50 EUR
25-Nov-04 ice cream          Expenses:Food         50.00 EUR   66.50 EUR

Note how the last column gives you the running total.

The balance and register commands are the bread-and-butter of ledger-cli, but to dive deeper into our expenses, we'll need the csv export functionality.

Month-by-Month report

As mentioned above, comparing month-by-month is not something ledger-cli currently provides natively. Therefore, I came up with a small script that dumps the transactions and does the aggregation in the month-by-month format shown earlier.

I keep this script in the same folder as main.journal and run it via ./report.sh (remember to chmod +x it).

#!/usr/bin/env bash

(
    echo "date,code,payee,account,commodity,amount,cleared,notes"
    ledger csv Expenses \
        --file main.journal \
        --date-format %F \
        --period 'since 1 year ago'
) |
    duckdb -nullvalue '' -c "
        create table t as
            select strftime(date, '%Y.%m') as ym, account, amount
            from read_csv_auto('/dev/stdin');

        -- per month, category
        (
            pivot t on ym
            using round(sum(amount), 2)
            order by account
        )
        union all
        -- per month totals
        (
            pivot (select 'Total' as account, ym, amount from t) on ym
            using round(sum(amount), 2)
        )
    "

With the example main.journal above, the output of the report looks like this:

How it works

Let's go over the script.

The first part, left-hand side of the pipe (|), is simply exporting the relevant transactions as CSV to stdout.

• echo ... writes the CSV header, according to ledger-cli's default CSV export format
• ledger csv Expenses exports transactions under the Expenses account as CSV
  • --file specifies which file to read from
  • --date-format %F specifies the ISO date format -- this is important to get DuckDB's CSV parser to identify it as an actual date
  • --period 'since 1 year ago' uses the --period expression syntax to specify the relevant period

The second part, after the pipe, is the SQL query that does the pivoting month-by-month. Standard SQL requires you to specify the pivoting values you care about which will each become its own column, but DuckDB has a much friendlier pivot statement that does it for you.

It takes the form of pivot tbl on col using agg(value), where:

• tbl is the table you want to pivot.
• col is the label column whose values you want to transform into multiple columns.
• value is the value column you want to aggregate for each column/row.
• agg is the aggregation function you want to use (e.g., sum, max, avg).
• Any remaining columns (not specified with on or using) will be kept and grouped.

Specifically in the query:

• create table t as creates a new table with the columns we care about, already transforming the date into the YYYY.MM.
• the first pivot creates the per-category month-by-month breakdown.
• the second pivot creates the monthly totals by replacing the original value of account with a value that is equal for all rows (Total) -- once aggregated, the values will not be separated per spending category.

Final advice

1. Read up on double-entry accounting if you're going to start with plaintext accounting. Knowing how to model your transactions properly prevents confusion down the road.
2. Keep it simple and use a single transactions file. I've tried multiple files, but in the end, errors due to transaction ordering became too common.
3. At the very least, use --strict and --pedantic. These make it so that you can only use accounts, tags, and commodities that have been explicitly declared -- basically free protection against typos.
4. Make use of a ledgerrc file to make ledger-cli commands less repetitive. Here's what mine currently looks like:

# file: ~/.config/ledger/ledgerrc
--date-format %F     # ISO date (YYYY-MM-DD)
--sort date          # sort by date
--effective          # use effective transaction date if there is one
--strict             # forbid unknown accounts (good against typos)
--pedantic           # elevate --strict warnings to errors
--file main.journal  # use `main.journal` if no --file is specified