Quick utility for simple ledger-style queries

[Alen Šiljak]

As a poc, today I've quickly assembled and published a couple of scripts that provide a simpler, ledger-style queries for Beancount.

Over the years I've become so used to using a quick "l b card" or "l r cash -b 2025-08" to see balances and last transactions. These tend to be my most-frequent queries, for balance checks and finding missing or erroneous records.

If there are other Ledger-CLI converts who are missing this, feel free to try and suggest missing features. I will likely add more parameters and parsing for the most-common use cases I need.

At the same time, before going deeper, I'd like to ask if there is already something of this sort out there that I was unable to find.

The repo is here:

https://github.com/alensiljak/ledger2bql

[Justus Pendleton]

https://github.com/zacchiro/beangrep

It's not quite the same thing -- it isn't trying to be similar to ledger and doesn't cover many of the metadata'ish aspects of what you've done -- but still might be worth looking at.

[Alen Šiljak]

Thank you for the link! That is useful to know. 

In the meantime, I've had a few ideas and have implemented various options and filters that don't exist or are not as elegant in Ledger (i.e. date range syntax). The beauty of having a tool in a language that one is comfortable with.

I'm looking to minimize the syntax, so that quick queries for balances or transaction  listing  for a certain period, currency, payee, account, etc. can be quickly listed. With or without total or running balance.

`bal` and `reg` are the most common queries I use. The filters are mostly common for both. 

With this tool I now feel I have a query tool that provides a quick insight into transactions and balances for everyday use, like when you need to see how much you've spent on auto electricity during the holiday period, checking if a bill to @xyz was paid, when the bills were paid, how much you've spent in certain currency, etc, etc.

[Red S]

Nice tool to be a part of the ecosystem, I can see this being very helpful, thank you for writing this and sharing!

Some thoughts/feedback:

• if the PAGER is used automatically, when needed, that’ll eliminate having to pipe through less, given you want a tool with a minimal cli burden. This is a common interface for polished CLI tools in my experience. click.echo_via_pager() does this for you
• automatic date range detection conflicts for me as I look up accounts using account numbers frequently. Beancount account names can include numbers (even though they can’t start with them)
• A way to stuff command line options into environment variables would eliminate the need to keeping specifying things like --zero and --total. Eg: BQ_BAL_OPTS=--zero --total
• Is there a way to convert balances to the default currency I’m not seeing? For commodity accounts.
• Does the tool have to be constrained by ledger syntax? It seems like it’s a good idea on its own. Would calling it something else (bq for beanquick?) allow the syntax to develop on its own right, suited to Beancount better?

​

[Alen Šiljak]

Hey! Thanks a lot for the feedback! Note that it's been only about 2 days since I started writing this. I haven't really had time to use it more than 3 times on my real data. ;)

The remarks are spot on and I have addressed some, like currency exchange. However, I'm running into an issue where Beanquery does not chain conversions (somehow I thought this was the default) and direct prices/exchange rates are required between currency pairs. Perhaps I'm just not aware of other ways of converting to any currency that can be chained from existing prices.

You're right about the dates. I assumed it would shorten the queries while not interfering with anything else. But ok, using "-d" is not a big deal since the date spec happens only once per query (hopefully!).

I also agree about the ledger syntax. I've started straining away from it. There is nothing particularly tying it to Ledger, other than my habit of using (a subset) of it's syntax over the years. It will probably end up a bit different, but mainly if it is simpler, more elegant, and/or effective. This will likely lead to a name change at some point. I am anyway using an `l` shortcut to point to the executable, making queries look like `l b credit`.

It would be great to track feedback via issues on github, since I won't be able to address all of them soon but am looking forward to improving some of these items in the near future! Also, PRs are very welcome, even though I haven't explicitly added it to the readme or contributing file (yet!).

[Alen Šiljak]

I've created the item for this feedback - https://github.com/alensiljak/ledger2bql/issues/1

and have resolved the main issues, as far as I can tell. While doing that, I've had some inspiration and have completed a few more ideas tonight.

Any further feedback welcome!

[Red S]

Tried out all the updates, they all look great!

Will respond more via github.

​

[Red S]

BTW, I’m using this utility regularly already!

Being able to do essentially BQL on the command line lightning fast with virtually no cognitive overhead for simple queries was something I’d missed for a long time.

Here’s a feature proposal: I can also see a q command to run pre-defined queries being super helpful, and is also something I’ve wanted for a long time. Take:

2025-08-31 query "After-Tax Contributions for Mega Backdoor Roth" " SELECT date,payee,narration,position,balance WHERE account ~ 'Assets:.*:Transfers:Paycheck:Y401k:After-Tax' AND year=YEAR(TODAY()) AND number < 0 "

I’d love to be able to say b q Mega on the command line, where Mega is a regex. If there's a unique match, it’ll run that query. If multiple matches occur, the command can either bail (simple version), or offer a menu selector (fancy).

​

[Daniele Nicolodi]

On 04/09/25 19:32, 'Alen Šiljak' via Beancount wrote:

> The remarks are spot on and I have addressed some, like currency
> exchange. However, I'm running into an issue where Beanquery does not
> chain conversions (somehow I thought this was the default) and direct
> prices/exchange rates are required between currency pairs. Perhaps I'm
> just not aware of other ways of converting to any currency that can be
> chained from existing prices.

This is by design. There are a couple of reasons why conversions are not
implemented between commodities for which an explicit price directive
exists are not performed:

- in most cases, the product of the A:B and B:C rates is just an
approximation of the real world A:C exchange rate;

- there may be more than one indirect way to compute an indirect
exchange rate, and they almost certainly result in different rates.
Namely, it may be possible to compute the A:C ratio via either the A:B *
B:C or A:D * D:C. Beancount would have to guess which one to use.

IIRC, there is a plugin somewhere that can synthesize prices by
combining existing price entries. This would allow you to achieve what
you want.

Cheers,
Dan

[Alen Šiljak]

On Friday, 5 September 2025 at 6:50:47 pm UTC+2 dan...@grinta.net wrote:

This is by design.

Thanks for the tip. I understand the concern there and the desire to keep the core engine exact. But, the practical use cries for something that is a bit more convenient. 

For example, I have BAM, which is pegged to EUR by constitution, and I have last week's EUR/USD rate because for me that's good enough. Based on this data, it certainly is possible to find a simple way BAM -> EUR -> USD, to express any report in any of these currencies. If there was a HUF involved, and it included rates to EUR and USD, there would be multiple way to calculate. 

Taking the latest, or the first occurrence produces some result. If it was possible to see and understand the chain, that would help in some odd case. But once a user understand the principle and has correct records, this approach produces a very practical result - it uses last-known exchange rates to perform rate calculations.

This is certainly something that a middleware (a plugin?) should be able to do. I was looking already for an existing one but was unable to find anything. Hope I find something before going down the rabbit hole of writing one from scratch.

[Alen Šiljak]

On Friday, 5 September 2025 at 6:50:47 pm UTC+2 dan...@grinta.net wrote:

IIRC, there is a plugin somewhere that can synthesize prices by
combining existing price entries.

Actually, after activating the implicit_prices for the test ledger, the conversions seem mostly ok, from what I see. This is good enough.

[Daniele Nicolodi]

Here https://github.com/beancount/beancount/issues/255 there are more
details. There is also a link to code in beancount that computes the
transitive prices

https://github.com/beancount/beancount/blob/v2/beancount/core/prices.py#L138

If someone has a good idea about how to expose that I'd be happy to add
it to beanquery.

Cheers,
Dan

[Alen Šiljak]

Interesting discussion. Yes, the problem is always the same.

From what I remember, Ledger creates a graph of currencies,  then, when required, finds the shortest path between the two. Don't remember now which algorithm is used for path-finding.

Also, as Martin commented in that thread, I think a runtime search/conversion would be a better option than creating gazillions of prices with a very high likelihood of not being used at all.

Correction regarding the implicit_prices. Only the pairs with direct prices are converted. So, remains an open issue.

[Simon Michael]

On 2025-09-01 11:12, 'Alen Šiljak' via Beancount wrote:
> As a poc, today I've quickly assembled and published a couple of scripts
> that provide a simpler, ledger-style queries for Beancount.
> Over the years I've become so used to using a quick "l b card" or "l r
> cash -b 2025-08" to see balances and last transactions. These tend to be
> my most-frequent queries, for balance checks and finding missing or
> erroneous records.

That's a very nice addition to Beancount. I would vote to build it in to
the official product.

[Vasily M]

Hi Alen,

Here's the plugin that does something along these lines, from tariochbctools: https://github.com/tarioch/beancounttools/blob/master/src/tariochbctools/plugins/generate_base_ccy_prices.py

You may want to enable it multiple times for a few base currencies.

On a high level it solves the conversion issues for me. I haven't looked in detail into things like precision, it just gives reasonable results. If precision is a concern, then directly fetching the prices probably would work the best.

[redst...@gmail.com]

That's a very nice addition to Beancount. I would vote to build it in to
the official product.

Very much agree! This is a missing piece in the core product IMHO. It’d provide for a much better out of the box experience for new users, and I'd wager it would be indispensable for a majority of regular/advanced users.

Alen Šiljak and Martin might have to hash this out. I don’t know what Martin’s criteria is for core product additions is right now. Or perhaps one way is to add it is as a separate product to the offical github repo.

​

[Alen Šiljak]

Yes, I'd be happy if this were to be included in the recommended list of tools. It is using the main libraries and provides additional layer (in front of Beanquery) for simple queries from the CLI. It actually displays the generated Beanquery statement, which is handy if you don't know but want to learn the BQL. BTW, by installing the CLI, you also get Beancount and Beanquery installed, as they are the main dependencies. The name question remains (there's an issue open), since I just needed a name for the project. Depending on how it goes in the community, some combination of Beancount + CLI + query would work, to indicate that it is a simple CLI access to your Beancount data.

[Martin Blais]

No time for me to hash anything much, I'm afraid. You can blame AI and all the urgency around it. (Maybe the LLMs will write v3.) Besides this squarely belongs outside of the main repo and I barely have much time for that.

I think to add a repo to the organization the bar isn't high, just something that is in a finished enough state (put a little polish on it) and that works with the other tools that are there and that is owned & supported by someone who cares about it and voila.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/beancount/3411d56f-cb62-4230-8081-bd1640510162n%40googlegroups.com.