bean-query: Transfers between accounts

[Ken Mankoff]

I'd like to list all transfers between two accounts. For example, to estimate monthly credit card payments.

I've tried,

bean-query bc.bean "SELECT date, payee, narration WHERE account ~ 'Assets:foo:bar' AND account ~ 'Liabilities:foo:baz'"

But it returns nothing (because account is one thing, and cannot be an AND of two things). Can someone help me with this query? I tried ChatGPT and it was not helpful.

Thanks,

   -k.

[Alen Šiljak]

Oohhh, I had this question a couple of years ago. 

https://money.stackexchange.com/questions/154316/how-can-i-identify-all-transfers-from-account-a-to-account-b-in-ledger-cli

Simon Michael did a good job getting close to solution but these systems are inherently not made for such a thing, unfortunately. If (or when) there is a JOIN statement in bean-query, it would be possible to construct a query that selects postings that are to either of the desired accounts, along with their transaction id, then pivot on the transaction id. This would still not be ideal but would be close.

The other, more realistic option at the moment, would be to do it in two steps using a query and a print, then use the new file for a separate query.

Anyway, still an interesting and unresolved (in an elegant matter) question.

[Ken Mankoff]

Hi Alen,

Thanks for that link. The FAQ link there points to hledger, and a quick

beancount2ledger bean.bc > ledgerfile
hledger -f ledgerfile aregister checking cc

gives the report I was looking for.

Thanks,

-k.

On 2025-07-03 at 11:32 -07, 'Alen Šiljak' via Beancount
<bean...@googlegroups.com> wrote...

> -- You received this message because you are subscribed to a topic in
> the Google Groups "Beancount" group. To unsubscribe from this topic,
> visit
> https://groups.google.com/d/topic/beancount/RCp-S1qlKdM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> beancount+...@googlegroups.com. To view this discussion visit
> https://groups.google.com/d/msgid/beancount/609ffcb9-e9bc-4074-b0e9-8aadc7ddf3a3n%40googlegroups.com.

[Alen Šiljak]

Thanks for the tip! I did not notice the edit that was added last year. So, hledger now has this built in. That's good to know. Cheers!

[Red S]

FINDFIRST and other_accounts are useful for this.

SELECT * WHERE account ~ "Assets:Banks:Checking" AND STR(FINDFIRST('Liabilities:CreditCard.*', other_accounts)) != 'None'

​

[Ken Mankoff]

Hi Red,

Thanks!! That also solves it, better - no exporting to ledger.

This is a nice tool, combined with Emacs & Org Mode:

#+NAME: cc_payments
#+begin_src bash :exports both :results table
bean-query -f csv bc.bean "SELECT date, position, payee WHERE account ~ 'Checking' AND STR(FINDFIRST('Liabilities:CC:*', other_accounts)) != 'None'" | sed s/USD//g
#+end_src

Puts out at table

#+RESULTS: cc_payments
| date | position | payee |
| 2024-01-17 | -22 | Visa |
| 2024-01-18 | -44.56 | MC |
| 2024-01-29 | -12.33 | Visa |
| 2024-02-20 | -9.55 | MC |

And then below I can plot it and summarize it:

#+BEGIN_SRC jupyter-python :exports both :var tbl=cc_payments
import pandas as pd
df = pd.DataFrame(index = pd.to_datetime([_[0] for _ in tbl[1:]]),
data = [_[1] for _ in tbl[1:]],
columns = ['amt'])
df = df.resample('MS').sum()
print("Average: ", df.mean().values.round(2))
(-1*df).plot(drawstyle='steps-pre')
df
#+END_SRC

Table of monthly amounts
Graphic

-k.

[Martin Blais]

Just write a script already.

I provide a Python library with an open data type for a reason.

Or ask the AI - our new overlords - to do it for you.

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/874ivtp0tc.fsf%40gmail.com.

[Daniele Nicolodi]

The "modern" bean-query way to do this is:

SELECT
date,
payee,
narration
FROM
#postings
WHERE
'Assets:Foo:Bar' IN entry.accounts AND
'Liabilities:Foo:Baz' IN entry.accounts

The "old, discouraged, and deprecated" way to do it is:

SELECT
date,
payee,
narration
WHERE

has_account('Assets:Foo:Bar') AND
has_account('Liabilities:Foo:Baz')

[Ken Mankoff]

Hi Daniele,

Thanks for the 'modern' suggestion.

But based on the strongly worded suggestion from Martin, I'll also share a script that loads all transactions into a pandas dataframe (thanks to ChatGPT). Further processing on this dataframe lets me do anything I want...

-k.


#!/usr/bin/env python
"""
Extract (date, from, to, amount, currency, description) rows from Beancount ledger with date as a pandas‑friendly datetime index.
"""

import sys
from pathlib import Path
from typing import List, Tuple

import pandas as pd
from beancount.loader import load_file
from beancount.core.data import Transaction, Posting

Row = Tuple[str, str, str, float, str, str] # (date, from, to, amount, currency, description)

def pair_postings(date: str, description: str, postings: List[Posting]) -> List[Row]:
"""
For all postings of the same currency in a transaction, pair debits (negative)
with credits (positive) and emit (date, from, to, amount, currency, description) rows.
"""
pos = [p for p in postings if p.units and p.units.number > 0]
neg = [p for p in postings if p.units and p.units.number < 0]

rows: List[Row] = []
while pos and neg:
p_from = neg[0]
p_to = pos[0]

amt = min(abs(p_from.units.number), p_to.units.number)
currency = p_from.units.currency

rows.append((date, p_from.account, p_to.account, amt, currency, description))

# Decrement amounts
p_from = p_from._replace(units=p_from.units._replace(number=p_from.units.number + amt))
p_to = p_to._replace(units=p_to.units._replace(number=p_to.units.number - amt))

if p_from.units.number == 0:
neg.pop(0)
else:
neg[0] = p_from

if p_to.units.number == 0:
pos.pop(0)
else:
pos[0] = p_to

return rows


def ledger_to_dataframe(path: Path | str) -> pd.DataFrame:
entries, errors, _ = load_file(str(path))
if errors:
for e in errors:
print(e, file=sys.stderr)

rows: List[Row] = []
for entry in entries:
if not isinstance(entry, Transaction):
continue

description = entry.payee or ""
if entry.narration:
if description:
description += " "
description += entry.narration

by_currency: dict[str, List[Posting]] = {}
for p in entry.postings:
if p.units:
by_currency.setdefault(p.units.currency, []).append(p)

for plist in by_currency.values():
rows.extend(pair_postings(entry.date, description, plist))

df = pd.DataFrame(rows, columns=["date", "from", "to", "amount", "currency", "description"])
df["date"] = pd.to_datetime(df["date"]).dt.normalize()
df["amount"] = df["amount"].astype(float)
df = df.set_index('date')
return df



if __name__ == "__main__":
if len(sys.argv) != 2:
sys.exit("Usage: python txns_to_dataframe.py /path/to/ledger.beancount")
df = ledger_to_dataframe(Path(sys.argv[1]).expanduser())
print(df.head())