How can I identify all transfers from account A to account B in ledger-cli?

Question

I would like to identify all the direct transfers from account A to account B. That is, all transactions with postings that include a positive amount for account B and a negative amount for account A.

What is the most practical way to do this with ledger-cli?

Simon Michael · Answer 1 · 2024-08-30T19:54:29.720

That's an interesting simple report that's not so easy to obtain!

With typical data patterns, it may be good enough to chain two reports like so:

$ ledger print --raw Checking | ledger -f - --permissive reg Liabilities something something
...
$ hledger print Checking | hledger -f- -I areg -w120 Liabilities amt:'>0'
Transactions in Liabilities and subaccounts:
2012-01-08 Chase:Slate | Paying off credit card     As:US:Bo:Checking                           140.36 USD    140.36 USD
2012-02-11 Chase:Slate | Paying off credit card     As:US:Bo:Checking                           725.96 USD    866.32 USD
2012-03-09 Chase:Slate | Paying off credit card     As:US:Bo:Checking                           580.00 USD   1446.32 USD

A more robust report can be achieved with hledger and sqlite:

$ hq() { (hledger print -O sql; echo "$1") | sqlite3 -column -header; }  # run sqlite queries on hledger data
$ echo; hq "select * from postings where txnidx in \
  (select txnidx from postings where account regexp 'Liabilities' and amount > 0 and txnidx in \
    (select txnidx from postings where account regexp 'Checking' and amount < 0))"
> > 
id  txnidx  date1       date2  status  code  description                           comment  account                      amount   commodity  credit  debit   posting_status  posting_comment
--  ------  ----------  -----  ------  ----  ------------------------------------  -------  ---------------------------  -------  ---------  ------  ------  --------------  ---------------
    147     2012-01-08         *             Chase:Slate | Paying off credit card           Liabilities:US:Chase:Slate   140.36   USD                140.36                                 
    147     2012-01-08         *             Chase:Slate | Paying off credit card           Assets:US:BofA:Checking      -140.36  USD        140.36                                         
    163     2012-02-11         *             Chase:Slate | Paying off credit card           Liabilities:US:Chase:Slate   725.96   USD                725.96                                 
    163     2012-02-11         *             Chase:Slate | Paying off credit card           Assets:US:BofA:Checking      -725.96  USD        725.96                                         
    177     2012-03-09         *             Chase:Slate | Paying off credit card           Liabilities:US:Chase:Slate   580      USD                580                                    
    177     2012-03-09         *             Chase:Slate | Paying off credit card           Assets:US:BofA:Checking      -580     USD        580

And there's a way to do it with tackler too.

[2024: here are some related FAQ answers for hledger.]

How can I identify all transfers from account A to account B in ledger-cli?

1 Answers1