Compare by month or year

[dstep...@gmail.com]

Hi Martin,

Great work on Beancount!  

Is there a way to compare balances by month and/or by year.  Ie: say I wanted to know how much I spent on each category for each of the last 24 months?

Also, is there a way of converting balances to one currency?

Thanks,

Dvid

[Martin Blais]

On Tue, Jun 23, 2015 at 10:49 AM, <dstep...@gmail.com> wrote:

Hi Martin,

Great work on Beancount!  

Is there a way to compare balances by month and/or by year.  Ie: say I wanted to know how much I spent on each category for each of the last 24 months?

Not yet, but I've been thinking about that and wanting it for a long while.

I think the most generic and flexible way to implement that would be via a combination of bean-query generating a table like this:

  Month  Account  Balance

  ...         ...            ...

and combining that with a separate tool that would pivot Balance on Month vs. Account, to produce something like this:

                Month

                6/15      7/15     8/15

   Account 

   Expenses:Restaurant   ... ... ...

Also, is there a way of converting balances to one currency?

At which date?

I began to prototype a CONVERT() function in bean-query.

https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/query/query_env.py#cl-234

I think it works, but it needs a bit more work.

There are a small number of complications with this that need to be handled for this to work "well":

- Which date is used

- What to do if a direct conversion isn't available

Just so you know, bean-query is a bit of a proof-of-concept, it hasn't been tested nearly as much as the rest of the Beancount code. It has been really useful to get something working so far, a lot of ideas have come out of this experimentation. It stands to get a complete from-scratch review at some point with a lot of improvements, and thorough testing.  I want to implement the booking improvements first.

 

Thanks,

Dvid

--
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/ec9fc401-cdc4-4cc3-b925-fc0c81419556%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Martin Blais]

Here's a tracking ticket for this item:

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

[Nathan Grigg]

I think the most generic and flexible way to implement that would be via a combination of bean-query generating a table like this:

  Month  Account  Balance

  ...         ...            ...

and combining that with a separate tool that would pivot Balance on Month vs. Account, to produce something like this:

                Month

                6/15      7/15     8/15

   Account 

   Expenses:Restaurant   ... ... ...

I use pandas for this. It handles group-by, pivots, etc.

Also, is there a way of converting balances to one currency?

At which date?

I began to prototype a CONVERT() function in bean-query.

https://bitbucket.org/blais/beancount/src/tip/src/python/beancount/query/query_env.py#cl-234

I think it works, but it needs a bit more work.

There are a small number of complications with this that need to be handled for this to work "well":

- Which date is used

- What to do if a direct conversion isn't available

I find I have two main use cases. 

1. Convert on the date of the transaction. This is useful for expenses and for tracking how much money you put into an investment account. 

2. Convert accumulated balance every time it is updated. This means you will have rows of output that come only from price changes. This is useful for tracking balance (at market value) of one or more accounts.

Right now, I have a two-part process. The first extracts rows (defined by Python functions) into a pandas DataFrame, and the second operates on that DataFrame to make graphs and tables. It would be nice to replace the first with bean-query, but I need currency conversion.

Nathan

[dstep...@gmail.com]

Nathan - Would you mind sharing your pandas code? I was actually doing that myself, then I thought I'd ask if someone had already implemented it.  It sounds like you are doing exactly what I was planning on implementing.  Are you using a hierarchical multi-index?

For the conversion, I was thinking along the lines of what companies do for financial statements.  Balance sheet marked to market at the end of the period, expenses based on the day that they were incurred.

[Nathan Grigg]

On Jun 23, 2015, at 10:07 PM, dstep...@gmail.com wrote:

Nathan - Would you mind sharing your pandas code? I was actually doing that myself, then I thought I'd ask if someone had already implemented it.  It sounds like you are doing exactly what I was planning on implementing.  Are you using a hierarchical multi-index?

Using bean-query to extract to csv with some post processing, assuming all expenses are same currency:

    bean-query $filename 'Select date, account, number where account ~ "^Expenses:"’ \

        | sed 2d | perl -pe 's/^ *| *$//g' | perl -pe 's/ +/,/g' > ledger.csv

Then in Python:

    >>> import pandas as pd

    >>> df = pd.read_csv('ledger.csv')

    >>> df.date = df.date.str.slice(0,7)  # Just get the month.

    >>> grouped = df[df.date > '2015'].groupby(['account', 'date']).sum().number

    >>> pivot = grouped.unstack().fillna(0)  # Moves the last multi-index (date) to columns.

    >>> pivot.head(1)                                       

    date              2015-01 2015-02 2015-03 2015-04 2015-05 2015-06

    account                                                          

    Expenses:Auto:Gas   94.64   76.95     110    68.4  148.32   38.45

The groupby creates a multi-index and unstack() turns it into a pivot table.

Nathan

[Martin Blais]

On Wed, Jun 24, 2015 at 10:51 AM, Nathan Grigg <nat...@nathangrigg.net> wrote:

On Jun 23, 2015, at 10:07 PM, dstep...@gmail.com wrote:

Nathan - Would you mind sharing your pandas code? I was actually doing that myself, then I thought I'd ask if someone had already implemented it.  It sounds like you are doing exactly what I was planning on implementing.  Are you using a hierarchical multi-index?

Using bean-query to extract to csv with some post processing, assuming all expenses are same currency:

    bean-query $filename 'Select date, account, number where account ~ "^Expenses:"’ \

        | sed 2d | perl -pe 's/^ *| *$//g' | perl -pe 's/ +/,/g' > ledger.csv

Hopefully when I add CSV format support for output of bean-query you won't have to do this anymore.

Then in Python:

    >>> import pandas as pd

    >>> df = pd.read_csv('ledger.csv')

    >>> df.date = df.date.str.slice(0,7)  # Just get the month.

    >>> grouped = df[df.date > '2015'].groupby(['account', 'date']).sum().number

    >>> pivot = grouped.unstack().fillna(0)  # Moves the last multi-index (date) to columns.

    >>> pivot.head(1)                                       

    date              2015-01 2015-02 2015-03 2015-04 2015-05 2015-06

    account                                                          

    Expenses:Auto:Gas   94.64   76.95     110    68.4  148.32   38.45

The groupby creates a multi-index and unstack() turns it into a pivot table.

This is great Nathan, thanks for sharing your code! :-)

Note: I'm explicitly avoiding using pandas from the Beancount source mainly because I've had a lot of trouble with it. My own use of Pandas so far is bittersweet: it does a lot of very useful things that I'd rather not rebuild manually (general in-memory filtering & aggregations) but its outputs are often confounding, e.g. whether a DataFrame object produced by one of its functions has an index or not. It's also another dependency I have preferred to avoid so far.

A more general aggregator will probably want to use a beancount.core.inventory.Inventory object in order to support accounts with mixed contents.  I'd like to build a pivot tool that supports that datatype (using Inventory.from_string() to initialize contents from a CSV cell).