Best method to generate an Income vs. Expense monthly summary table

[Alex Johnstone]

Hello,

As Martin has seen I've been working my way through all the docs after converting from YNAB to beancount. Mainly as YNAB can't do multiple currencies in one file, and it sounds like YNAB is moving to a monthly subscription model which doesn't interest me.

Anyway, whilst I get familar with beancount I was wondering if someone could point me in the right direction for generating income vs. expense reports on a monthly basis, something like this:

	Jan '15	Feb '15	Mar '15	Apr '15	May '15	Jun '15	Jul '15	Aug '15	Sep '15	Oct '15
Income
US
Salary	10	10	10	10	10	10	10	10	10	10
Expenses
Rent	1000	1001	1002	1003	1004	1005	1006	1007	1008	1009
Phone	10	11	12	13	14	15	16	17	18	19
Internet	50	51	52	53	54	55	56	57	58	59
Electricity	1	2	3	4	5	6	7	8	9	10
Water	8	9	10	11	12	13	14	15	16	17
Natural Gas	11	12	13	14	15	16	17	18	19	20

The actual format doesn't matter, just tabulated data by month.

The bean-report income gives this information but just totalized. bean-web can filter by year. 

I'm still working through the bean-query doc but would that be a better tool or should I start working on my own python script? I'd like to leverage as much built-in functionality as possible for consistency.

Thanks,

Alex

[Dave Stephens]

Here's my hacky script that does this for expenses.

I tried to add a view like that to bean-web, but didn't get that far.

#!/usr/bin/env python

import pandas as pd

import numpy as np

import os

import datetime

pd.options.display.max_rows = 100

pd.options.display.max_columns = 100

pd.options.display.width = 300

pd.options.display.float_format = '{:,.0f}'.format

try:

    os.remove('ledger.csv')

except:

    pass

bashcommand = "bean-query /home/david/.beancount/dave.beancount 'Select date, account, number where account ~ \"^Expenses:\"' | sed 2d | perl -pe 's/^ *| *$//g' | perl -pe 's/ +/,/g' > ledger.csv"

os.system(bashcommand)

df = pd.read_csv('ledger.csv',parse_dates=True)

df.date = df.date.str.slice(0,7)

df.index = df.date

grouped = df.ix['2014':,:].groupby(['account', 'date']).sum().number

pivot = grouped.unstack().fillna(0)

pivot.replace(0,np.nan).to_csv('monthly.csv')

#Aggregates first 2 levels

summary_index = [":".join(name.split(':')[0:2]) for name in pivot.index] # 

summary = pivot

summary.index = summary_index

summary = summary.reset_index().groupby('index').sum()

--
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/c4815729-8e53-4399-ac76-3e466cc08f16%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Nathan Grigg]

Here is a previous discussion that does this using Python with the pandas module. 

http://comments.gmane.org/gmane.comp.finance.beancount/546

--

[Martin Blais]

Yes.

The ideas from that thread all still stand.

There's a TODO item here:

https://bitbucket.org/blais/beancount/src/4ac5292420c98a2ce9cb758391828c98f3d34442/TODO?at=default&fileviewer=file-view-default#cl-749

The way to implement this that provides most leverage is to create a script that can aggregate arbitrary content from CSV files, but which also supports the beancount.core.inventory.Inventory data structure to perform these aggregations, and which properly renders their content.

The guts of that code should mostly live in a library, which can be also be pulled in from the SQL shell and perhaps some syntax added to run those inline.

I want this too.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/11DD5559-D7FA-4267-AF3F-BC3259E5324D%40nathangrigg.net.

[Martin Blais]

See also:

https://bitbucket.org/blais/beancount/issues/61/implement-pivot-table-tool-make-sure-it

You can subscribe to this to monitor progress/changes to this item.

[Alex Johnstone]

Thanks for the information. I'll have a play around with the script and look forward to seeing what Martin implements in beancount too.

[Alex Johnstone]

Getting familiar with bean query and this statement works good enough for individual months:

SELECT account, sum(position) WHERE account ~ "Expenses" AND year = 2015 AND month = 10 GROUP BY account ORDER by account

Using position is useful for multiple currencies, vs. number as in the script posted above.

[Martin Blais]

Yes, using position is the right thing to do.

This will produce a column with a data type of Inventory.

BTW, one existing issue is that an Inventory with multiple currencies therein will render over multiple lines. I know need to make some improvements to that: it should render on a single line by default (positions comma-separated), and I'll add a BROADCAST option to the SQL that would instead split it up in its component positions and copy (broadcast) all the other column values to the resulting rows. The user chooses. This should work for all output types. Maybe the default should always be broadcast, I'm not sure.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/3e6d31ca-13be-43f8-a546-61d1758e7395%40googlegroups.com.