2

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?

1 Answer 1

4

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.

5
  • Thanks, Simon! That's an interesting concept. However, the balance assertions seem to destroy all the fun! I'd have to remove them to get the result. :( Otherwise, it's an answer worth being added to the cookbook! Dec 23, 2022 at 22:35
  • Also, this is a great argument for sqlite integration for reporting! Dec 23, 2022 at 22:45
  • You're right, I have updated the example to silence balance assertion failures. Dec 23, 2022 at 22:53
  • Phew! Thanks! There were still some strange "unbalanced remainder" errors but at least I could run the commands separately and create an intermediate file, which I could then fix. The transactions with balance assertions were still causing issues, somehow. Dec 23, 2022 at 23:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .