I'm tracking retail sales with hledger like so:
2024/09/08 Retail
Revenue:Retail:Food -1 "honey" @ $10.00
Revenue:Retail:Food -1 "tomato" @ $2.00
Revenue:Retail:Food -1 "lettuce" @ $2.00
Revenue:Retail:Misc -1 "canvas art" @ $10.00
Assets:Cash $24.00
2024/09/09 Retail
Revenue:Retail:Food -1 "tomato" @ $4.00
Assets:Cash $4.00
When I run reports, I'm getting totals in terms of all commodities (e.g. -1 honey, -1 tomato
, etc.) so I use hledger's -B/--cost
flag to get the totals in terms of dollars. This works great for computing total revenue. However, I'd like to compute how much I'm making on each commodity. I can't just do commodity totals times price because they're fluctuating in price based on season, condition, etc. (in the case of produce). How can I compute the total value of each commodity sold? In the above ledger, I'd expect the total for tomato
to be $6.00.
With current hledger, the simplest way is to add that information in the account name:
2024/09/08 Retail
Revenue:Retail:Food:Honey -1 honey @ $10.00
Revenue:Retail:Food:Tomato -1 tomato @ $2.00
Revenue:Retail:Food:Lettuce -1 lettuce @ $2.00
Revenue:Retail:Misc -1 "canvas art" @ $10.00
Assets:Cash $24.00
2024/09/09 Retail
Revenue:Retail:Food:Tomato -1 tomato @ $4.00
Assets:Cash $4.00
Or, you could run multiple reports, something like:
(IFS=$'\n'; for c in `hledger comm`; do echo $c:; hledger bal -BN "cur:$c"; done)
Or, you could use hledger master (just pushed), where I have added --pivot=comm
(and --pivot=amt
and --pivot=cost
). Thanks for the idea.
$ hledger bal -Y --pivot comm -B
Balance changes in 2024, converted to cost:
|| 2024
============++=========
$ || $28.00
canvas art || $-10.00
honey || $-10.00
lettuce || $-2.00
tomato || $-6.00
------------++---------
|| 0
2 Likes
Wow! Thanks @simonmic!!!
I'll give that pivot report a shot on master.
In my research for solving this problem, I was looking into CSV or SQL output and then forming a query to solve my problem in SQLite. I discovered that cost commodity is included in JSON output, but not CSV or SQL. Might be worth considering including those columns in CSV and SQL outputs, or allowing configuration for which columns to include.
1 Like
Works perfectly. Thank you so much! Just sent some sponsor money on GitHub.
1 Like