Reporting on expenses/liabilities/income per month
397 views
Skip to first unread message
Jason Chu
Nov 30, 2015, 6:23:19 PM11/30/15
to Beancount
I am currently a gnucash user considering the switch to beancount.
Apart from just tracking where all my money is, I also create barchart reports broken down by month so that I can feel good (or bad) about my current situation. I plot things like liabilities over time, fixed costs over time (you'd think those wouldn't change...), discretionary expenses over time, etc. That way I can visualize how I've been doing over the year.
I know bean-query could give me monthly reports, but I couldn't figure out how to query across multiple months in a single query. I think the pivot keyword would cover my case, but it doesn't exist yet.
How do other people do this?
Jacques Gagnon
Nov 30, 2015, 6:42:56 PM11/30/15
to Beancount
I'm currently bashing this ;) until I have some time to make something more elegant ;)
It take no parameter and output something like this:
YYYY/MM expenses loans income balance
It's a bit on the slow side however.
Hope this help!
Martin Blais
Dec 1, 2015, 12:10:00 AM12/1/15
to Beancount
I don't do this yet, but have had many many thoughts about it. You can run a query to extract a table of year, month, account, sum(position) and then process that.
See these threads and links for more info:
https://groups.google.com/d/msg/beancount/otu6I7NX5YQ/DXvDYKJUEwAJ--
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 post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/d6ae5732-fc09-47df-98f1-7342e2d08c41%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Martin Blais
Dec 1, 2015, 1:08:28 AM12/1/15
to Beancount
On Mon, Nov 30, 2015 at 6:23 PM, Jason Chu <xen...@gmail.com> wrote:
I am currently a gnucash user considering the switch to beancount.Apart from just tracking where all my money is, I also create barchart reports broken down by month so that I can feel good (or bad) about my current situation. I plot things like liabilities over time, fixed costs over time (you'd think those wouldn't change...), discretionary expenses over time, etc. That way I can visualize how I've been doing over the year.
BTW, another thing to look at is code from this experiment:
This script also plots total assets + liabilities over time (by week or by month).
I've been meaning to add a post-tax version as well (i.e. what's your liquidation value? mainly to account for a fair valuation of pre-tax & after-tax 401k and roth accounts), and to render both assets and liabilities separately on the same graph, not done yet, starting to implement with this using metadata on Open directives actually.
In this other experiment I calculate rolling averages of a subset of accounts:
I've used this for evaluating current compensation which varies over time (due to stock grants and other unevenness) by restricting to an income account from an employer.
I know bean-query could give me monthly reports, but I couldn't figure out how to query across multiple months in a single query. I think the pivot keyword would cover my case, but it doesn't exist yet.How do other people do this?
--
Jacques Gagnon
Dec 1, 2015, 10:44:21 AM12/1/15
to Beancount
I've been playing a bit with bean-query last night, this give a monthly summary:
'SELECT year, month, sum(position) WHERE (account ~ "Expenses" OR account ~ "Liabilities:Loans" OR account ~ "Income") GROUP BY year, month ORDER BY year, month'
I not sure if it's possible to show more detail however with a single query.
On Monday, November 30, 2015 at 6:23:19 PM UTC-5, Jason Chu wrote:
Jacques Gagnon
Dec 1, 2015, 10:56:57 AM12/1/15
to bean...@googlegroups.com
I think we would need support for CASE statement to build more complex report within a single query.
Ex: sum(CASE WHEN account ~ "Expenses" THEN position ELSE 0 END)
--
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 post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/efd262f9-4a26-4498-916e-dc77c1205b07%40googlegroups.com.
Martin Blais
Dec 3, 2015, 12:26:52 AM12/3/15
to Beancount
You can either use multiple queries, or you can do this:
SELECT year, month, root(account, 1) as r, sum(position)
WHERE (account ~ "Expenses" OR account ~ "Liabilities:Loans" OR account ~ "Income")
GROUP BY year, month, r
ORDER BY year, month, r
There are three functions on account names:
- PARENT(account)
- LEAF(account)
- ROOT(account, num-components)
I implemented ROOT() just now for you the default branch.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAPgTVrbds-4nDo10yXuWHcKJEx-ubzSd16H%2BURP_UCttdY0cKw%40mail.gmail.com.
Jacques Gagnon
Dec 3, 2015, 6:01:51 PM12/3/15
to bean...@googlegroups.com
Merci Martin!
This is speeding up my monthly balance script a lot.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNqHJ2ErBh6%2BVLDMqAJ_sykLd%3DLMh3J7pD5hGRbx00%2BqA%40mail.gmail.com.
Martin Blais
Dec 4, 2015, 12:35:04 AM12/4/15
to Beancount
I quickly hashed out a pure Python equivalent, to demonstrate how to programmatically calls the query API.
See attachment.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAPgTVrbbGACORb2Jgy%2B0C%3D9UeOw13sZPBkwrTZpXTE6G6rqnKg%40mail.gmail.com.
Jason Chu
Dec 4, 2015, 2:25:49 AM12/4/15
to bean...@googlegroups.com
This looks like it will totally work for me. Thanks!
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNj000mnqjhR_YEcstG5rgc%2BOD-%2B4dbwh2cOfExMZr7Rw%40mail.gmail.com.
Reply all
Reply to author
Forward
0 new messages