beancount.plugins.zerosum

713 views
Skip to first unread message

redst...@gmail.com

unread,
May 4, 2015, 1:06:54 PM5/4/15
to bean...@googlegroups.com
Hello,
I wrote a new plugin, called zerosum, that I've attached here. Description (also in the plugin file) is below. Feedback welcome. See next two postings in this thread for the actual plugin (I'm unable to attach it using google's interface ??), and a question about performance.

Description:
--------------------------------
Plugin for accounts that should sum up to zero. Determines transactions
that when taken together, sum up to zero, and move them to a specified
account. The remaining entries are the 'unmatched' ones, that need attention
from the user.

Motivation:
-----------

Real-world transfers frequently occur between accounts. For example, between a
checking account and an investment account. When double entry bookkeeping is
used to track such transfers, we end up with two problems:

    a) when account statements are converted to double-entry format, the user
    has to manually match the transfers on account statements from the two
    institutions involved, and remove one of the entries since they are
    redundant.

    b) even when (a) is done, the transfer might take a day or more to
    complete: the two accounts involved would then reflect the transfer on
    different dates.

Since the money is truly missing from all the physical accounts for the period
of transfer, they can be accounted for as shown in this example:

2005-01-01 Transfer
 
Assets:Bank_of_Ameriplus  -20 USD
 
ZeroSumAccount:Transfers


2005-01-03 Transfer
 
Assets:TB_Trading  20 USD
 
ZeroSumAccount:Transfers


Doing so has a few advantages:

    a) on 2005-01-02, your assets are accurately represented:
    Bank_of_Ameriplus is short by $20, TB_Trading still doesn't have it, and
    the ZeroSumAccount:Transfers account captures that the money is still
    yours, but is "in flight."

    b) One can convert each bank's transactions directly into double-entry
    ledger statements. No need to remove the transaction from one of the
    banks. When you look at your journal files for each account, they match
    your account statements exactly.

    c) Import/conversion (from say, a bank .csv or .ofx) is easier, because
    your import scripts don't have to figure out where a transfer goes, and
    can simply assign transfers to  ZeroSumAccount:Transfers

    d) If there is a problem, your ZeroSumAccount:Transfers will sum to a
    non-zero value. Errors can therefore be found easily.


What this plugin does:
----------------------

Account statements from institutions can be directly converted to double-entry
format, with transfers simply going to a special transfers account (eg:
Assets:ZeroSumAccount:Transfers).

In this plugin, we identify sets of postings in the specified ZeroSum accounts
that sum up to zero, and move them to a specified target account. This target
account will always sum up to zero and needs no further attention. The
postings remaining in the original ZeroSum accounts were the ones that could
not be matched, and potentially need attention.

The plugin operates on postings (not transactions) in the ZeroSum accounts.
This way, transactions with multiple postings to a ZeroSum account are still
matched without special handling.

The following examples will be matched and moved by this plugin:

    Example 1:
    ----------
    Input:
        2005-01-01 Transfer
         
Assets:Bank_of_Ameriplus  -20 USD
         
ZeroSumAccount:Transfers


       
2005-01-03 Transfer
         
Assets:TB_Trading  20 USD
         
ZeroSumAccount:Transfers

    Output:
        2005-01-01 Transfer
         
Assets:Bank_of_Ameriplus  -20 USD
         
ZeroSumAccount-Matched:Transfers


       
2005-01-03 Transfer
         
Assets:TB_Trading  20 USD
         
ZeroSumAccount-Matched:Transfers


    Example 2 (Only input shown):
    -----------------------------
    2005-01-01 Transfer
     
Assets:Bank_of_Ameriplus  -20 USD
     
ZeroSumAccount:Transfers   10 USD
     
ZeroSumAccount:Transfers   10 USD


   
2005-01-03 Transfer
     
Assets:TB_Trading_A  10 USD
     
ZeroSumAccount:Transfers


   
2005-01-04 Transfer
     
Assets:TB_Trading_B  10 USD
     
ZeroSumAccount:Transfers


The following examples will NOT be matched:

    Example A:
    ----------
    2005-01-01 Transfer
     
Assets:Bank_of_Ameriplus  -20 USD
     
ZeroSumAccount:Transfers   10 USD
     
ZeroSumAccount:Transfers   10 USD


   
2005-01-03 Transfer
     
Assets:TB_Trading  20 USD
     
ZeroSumAccount:Transfers


    Example B:
    ----------
    2005-01-01 Transfer
     
Assets:Bank_of_Ameriplus  -20 USD
     
ZeroSumAccount:Transfers


   
2005-01-03 Transfer
     
Assets:TB_Trading_A  10 USD
     
ZeroSumAccount:Transfers


   
2005-01-03 Transfer
     
Assets:TB_Trading_B  10 USD
     
ZeroSumAccount:Transfers



The plugin does not append/remove the original set of input transaction
entries. It only changes the accounts to which postings are made. The plugin
also automatically adds "Open" directives for the target accounts to which
matched transactions are moved.

Invoking the plugin:
--------------------
First, an example:

    plugin "beancount.plugins.zerosum" "{
     'zerosum_accounts' : {
     'Assets:Zero-Sum-Accounts:Bank-Account-Transfers' : ('Assets:ZSA-Matched:Bank-Account-Transfers', 30),
     'Assets:Zero-Sum-Accounts:Credit-Card-Payments'   : ('Assets:ZSA-Matched:Credit-Card-Payments'  ,  6),
     'Assets:Zero-Sum-Accounts:Temporary'              : ('Assets:ZSA-Matched:Temporary'             , 90),
      }
     }"


As the example shows, the argument is a dictionary where the keys are the set
of accounts on which the plugin should operate. The values are
(target_account, date_range), where the target_account is the account to which
the plugin should move matched postings, and the date_range is the range over
which to check for matches for that account.


References:

redst...@gmail.com

unread,
May 4, 2015, 1:08:29 PM5/4/15
to bean...@googlegroups.com, redst...@gmail.com
__author__ = "Red Street"


import collections


from beancount.core.amount import ZERO
from beancount.core import data
from beancount.core import account
from beancount.core import getters
from beancount.core import position
from beancount.core import flags
from beancount.ops import holdings
from beancount.ops import prices
from beancount.parser import options
from beancount.parser import printer

__plugins__
= ('zerosum',)

# replace the account on a given posting with a new account
def account_replace(posting, new_account):
   
# create a new posting with the new account, then remove old and add new
   
# from parent transaction
    new_posting
= posting._replace(account=new_account)
    new_posting
.entry.postings.remove(posting)
    new_posting
.entry.postings.append(new_posting)


ZerosumError = collections.namedtuple('ZerosumError', 'source message entry')


# TODO:
# - if account metadata has 'zerosumaccountcheck' set to true, then check it
# - take plugin params from metadata (including date_range)
# - add 'Matched' accounts to definition list automatically from plugin
# - create a beancount-link between matches for debugging?




def zerosum(entries, options_map, config):
   
"""Insert entries for unmatched transactions in zero-sum accounts.


    Args:
      entries: a list of entry instances


      options_map: a dict of options parsed from the file (not used)


      config: A configuration string, which is intended to be a Python dict
      mapping zerosum account name -> (matched zerosum account name,
      date_range). See example for more info.


    Returns:
      A tuple of entries and errors.


    """





    config_obj
= eval(config, {}, {})
   
if not isinstance(config_obj, dict):
       
raise RuntimeError("Invalid plugin configuration: should be a single dict.")


    zs_accounts_list
= config_obj.pop('zerosum_accounts', {})


    errors
= []
    new_accounts
= []
    zerosum_txns_count
= 0
    match_count
= 0
    multiple_match_count
= 0
    EPSILON_DELTA
= 0.0099
   
for zs_account,account_config in zs_accounts_list.items():


        date_range
= account_config[1]
        zerosum_txns
= []
        non_zerosum_entries
= []
       
# this loop bins each entry into either zerosum_txns or non_zerosum_entries
       
for entry in entries:
            outlist
= (zerosum_txns
                       
if (isinstance(entry, data.Transaction) and
                           any
(posting.account == zs_account for posting in entry.postings))
                       
else non_zerosum_entries)
            outlist
.append(entry)
        zerosum_txns_count
+= len(zerosum_txns)


       
# algorithm: iterate through zerosum_txns (zerosum transactions). For each
       
# transaction, for each of its postings involving zs_account, try to
       
# find a match across all the other zerosum_txns. If a match is found,
       
# replace the account name in the the pair of postings.


       
# This would be easier if we could ignore transactions and just
       
# iterate across posting. But we cannot do so because postings are
       
# tuples, and therefore immutable: we have to replace a posting with a
       
# newly created posting in order to make a change to its account when
       
# a matching pair is found. If we were iterating across postings, we
       
# would be adding/removing from the posting list we are iterating
       
# through, which is not a good idea.


       
for txn in zerosum_txns:
           
for posting in txn.postings:
               
if posting.account == zs_account: # not strictly needed
                   
# find matches
                    matches
= [p for t in zerosum_txns for p in t.postings
                           
if (p.account == zs_account and
                            abs
(p.position.number + posting.position.number) < EPSILON_DELTA and
                            abs
((p.entry.date - posting.entry.date).days) <= date_range)
                           
]
                   
# replace accounts in the pair


                   
if len(matches) >=1:
                        match_count
+= 1
                       
if len(matches) > 1:  #TODO: pick posting with closest date
                            multiple_match_count
+= 1
                        account_replace
(posting,    account_config[0])
                        account_replace
(matches[0], account_config[0])
                       
if account_config[0] not in new_accounts:
                            new_accounts
.append(account_config[0])


   
# TODO: should ideally track account specific earliest date
    new_open_entries
= create_open_directives(new_accounts, entries)


   
print("Zerosum: {} transactions considered, {} matching pairs found (including {} multiple matches). {} new accounts added.".format(zerosum_txns_count, match_count, multiple_match_count, len(new_open_entries)))
   
   
# it's important to preserve and return 'entries', which was the input
   
# list. This way, we won't inadvertantly add/remove entries from the
   
# original list of entries.
   
return(new_open_entries + entries, errors)






# code from Martin Blais' unrealized.py plugin
def create_open_directives(new_accounts, entries):
    meta
= data.new_metadata('<zerosum>', 0)
   
# Ensure that the accounts we're going to use to book the postings exist, by
   
# creating open entries for those that we generated that weren't already
   
# existing accounts.
    earliest_date
= entries[0].date
    open_entries
= getters.get_account_open_close(entries)
    new_open_entries
= []
   
for account_ in sorted(new_accounts):
       
if account_ not in open_entries:
            meta
= data.new_metadata(meta.filename, 0)
            open_entry
= data.Open(meta, earliest_date, account_, None, None)
            new_open_entries
.append(open_entry)
   
return(new_open_entries)



redst...@gmail.com

unread,
May 4, 2015, 1:08:56 PM5/4/15
to bean...@googlegroups.com, redst...@gmail.com
One area I could use help on is run time performance, which is not the greatest, for several reasons, one of the main ones being that I build up the set of postings to search over for each match. 
Gathering the list of postings to work over once at the beginning, and then modifying them would be faster, but because postings are immutable, they cannot be modified "in-place." Which means that if I gathered the list of postings once, I would have to modify a list I was iterating through, which I would rather not do.

Stefan Monnier

unread,
May 4, 2015, 6:26:25 PM5/4/15
to bean...@googlegroups.com
> 2005-01-01 Transfer
> Assets:Bank_of_Ameriplus -20 USD
> ZeroSumAccount:Transfers


> 2005-01-03 Transfer
> Assets:TB_Trading 20 USD
> ZeroSumAccount:Transfers

There's something unsatisfactory in the fact that we lose the explicit
connection between the two accounts.


Stefan

redst...@gmail.com

unread,
May 4, 2015, 6:40:16 PM5/4/15
to bean...@googlegroups.com, mon...@iro.umontreal.ca
There's something unsatisfactory in the fact that we lose the explicit 
connection between the two accounts. 
 
This can be solved in many ways. For example, by using beancount's link mechanism to provide a link between the transactions.

For me personally, there's something unsatisfactory with modeling the transfer using a single transaction with two postings on the same date, because that doesn't represent reality. I also find it unsatisfactory for my account files to not correspond exactly to the statements I receive from the institutions. i.e., having to remove one of them, and modifying the other transaction. This solves both problems, and provides automation while doing so.

This plugin simply provides an idea and a specific implementation for matching transactions. Ultimately, whether or not this specific implementation is what works for you depends on your personal preference and your situation. One can use the matching mechanism and code as a way to implement one's own preferences.

Martin Blais

unread,
May 4, 2015, 10:23:56 PM5/4/15
to redst...@gmail.com, bean...@googlegroups.com, Stefan Monnier
On Mon, May 4, 2015 at 6:40 PM, <redst...@gmail.com> wrote:
There's something unsatisfactory in the fact that we lose the explicit 
connection between the two accounts. 
 
This can be solved in many ways. For example, by using beancount's link mechanism to provide a link between the transactions.

I like this idea.



For me personally, there's something unsatisfactory with modeling the transfer using a single transaction with two postings on the same date, because that doesn't represent reality. I also find it unsatisfactory for my account files to not correspond exactly to the statements I receive from the institutions. i.e., having to remove one of them, and modifying the other transaction. This solves both problems, and provides automation while doing so.

I agree too, the problem of storing a settlement date needs to be addressed.
This has been discussed previously here:



This plugin simply provides an idea and a specific implementation for matching transactions. Ultimately, whether or not this specific implementation is what works for you depends on your personal preference and your situation. One can use the matching mechanism and code as a way to implement one's own preferences.

Experimentation in plugins is always welcome.


 

--
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/dbdc7e65-0e0d-464c-84e5-6efb9542b3d9%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

redst...@gmail.com

unread,
May 6, 2015, 6:40:12 PM5/6/15
to bean...@googlegroups.com, redst...@gmail.com
I've found the zerosum to be very useful in two more scenarios:

- reimbursements: I book these to a reimbursement account at the time of purchase, and at the time the reimbursement arrives. Assets:Reimbursement:Workplace account tracks reimbursements. When the zerosum plugin is used, it moves all matches to a different account, and so, only the outstanding reimbursements (the ones not yet paid) are left behind in Assets:Reimbursements:Workplace


2010-01-01 * "Office Store" "Pens and pencils"
     
Liabilities:Credit Card      -25 USD
     
Assets:Reimbursements:Workplace


2010-02-03 * "Reimbursement"
     
Assets:Bank:Checking                        25 USD
     
Assets:Reimbursements:Workplace






- mail in rebates: the Assets:Rebates account tracks all mail-in rebates. When the zerosum plugin is used, it moves all matches to a different account, and so, only the outstanding rebates are in Assets:Rebates

2010-01-01 * "Smartphone"
     
Liabilities:Credit Card      -250 USD
     
Assets:Rebates              100 USD
     
Expenses:Phone


2010-05-04 * "Rebate check"
     
Assets:Bank:Checking                        100 USD
     
Assets:Rebates



redst...@gmail.com

unread,
May 12, 2015, 8:33:49 PM5/12/15
to bean...@googlegroups.com, redst...@gmail.com
More documentation: I'm attaching the minimum beancount file including the plugin configuration directives for a small example, and the output of bean-query.

plugin "beancount.plugins.zerosum" "{
 'zerosum_accounts' : {
    'Assets:Reimbursements:Workplace' : ('Assets:Reimbursements-Received:Workplace',   40),
    'Assets:Rebates'                  : ('Assets:Zerosum-Matched:Rebates',            180),
  }
 }"



2000-01-01 open Liabilities:Credit-Card         USD
2000-01-01 open Assets:Reimbursements:Workplace USD
2000-01-01 open Assets:Bank:Checking            USD
2000-01-01 open Expenses:Electronics            USD
2000-01-01 open Assets:Rebates                  USD

2010-01-01 * "Office Store" "Pens and pencils"

     
Liabilities:Credit-Card            -25 USD
     
Assets:Reimbursements:Workplace

2010-01-01 * "Office Store" "Writing pad"
     
Liabilities:Credit-Card            -7 USD
     
Assets:Reimbursements:Workplace


2010-02-03 * "Reimbursement"
     
Assets:Bank:Checking                25 USD
     
Assets:Reimbursements:Workplace

2010-01-01 * "Smartphone"
     
Liabilities:Credit-Card           -250 USD
     
Assets:Rebates                     100 USD
     
Expenses:Electronics


2010-05-04 * "Rebate check"
     
Assets:Bank:Checking               100 USD
     
Assets:Rebates

2010-01-01 * "Camera"
     
Liabilities:Credit-Card           -300 USD
     
Assets:Rebates                      50 USD
     
Expenses:Electronics


bean-query output:

$ bean-query zerosum-example.beancount
Zerosum: 2/4 postings matched. 0 multiple matches. 2 new accounts added.
Input file: "Beancount"
Ready with 13 directives (14 postings in 6 transactions).

beancount
> balances
                account                  sum_posi
---------------------------------------- --------
Assets:Bank:Checking                      125 USD
Assets:Rebates                             50 USD
Assets:Reimbursements-Received:Workplace
Assets:Reimbursements:Workplace             7 USD
Assets:Zerosum-Matched:Rebates
Liabilities:Credit-Card                  -582 USD
Expenses:Electronics                      400 USD
beancount
>


As you can see, the received reimbursement got moved into the specified target account (Assets:Reimbursements-Received:Workplace), while the one not received (for $7) remains in its original account. Same for the rebate. Target accounts always sum up to zero.

24x7...@gmail.com

unread,
May 12, 2015, 10:49:07 PM5/12/15
to bean...@googlegroups.com, redst...@gmail.com
Thanks a lot! This is very useful.

-cb

Martin Blais

unread,
May 12, 2015, 11:09:06 PM5/12/15
to redst...@gmail.com, bean...@googlegroups.com
Good stuff, redstreet0.

Would you consider creating a bitbucket repository to share your code out there?
I could add a link to it from the Beancount docs.

Note: You can create a separate Python package name, e.g. redstreet.zerosum, for example, or whatever you prefer. Anything that can exists on your PYTHONPATH can be imported via a Beancount plugin.

I've updated the Settlement document with some ideas about merging separate transactions similar to this; the process of automatically merging transactions is the dual from that which is the handling of two dates on a single transaction. I think they are related:

At some point I'll provide a solution to deal with this style of input as part of Beancount itself.






--
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.

redst...@gmail.com

unread,
May 16, 2015, 8:38:21 PM5/16/15
to bean...@googlegroups.com, redst...@gmail.com
Would you consider creating a bitbucket repository to share your code out there?
I could add a link to it from the Beancount docs.

 

Note: You can create a separate Python package name, e.g. redstreet.zerosum, for example, or whatever you prefer. Anything that can exists on your PYTHONPATH can be imported via a Beancount plugin.

I've updated the Settlement document with some ideas about merging separate transactions similar to this; the process of automatically merging transactions is the dual from that which is the handling of two dates on a single transaction. I think they are related:

At some point I'll provide a solution to deal with this style of input as part of Beancount itself.

True, I think they are closely related in many ways. But it seems to me like the zerosum idea is separate from identifying two entries on two separate (but close) dates that correspond to one single real-world transaction: the zerosum accounts, for instance, can be used to track a loan to a friend that got paid back 95 days later, or a rebate that arrived 200 days later. These are two separate real-world transactions, but are related to each other less strongly than a bank transfer that appears on both sides.

Martin Blais

unread,
Jun 2, 2015, 11:35:02 PM6/2/15
to redst...@gmail.com, bean...@googlegroups.com
I've created a new doc for user contributions and added this link to it:


--
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.
Reply all
Reply to author
Forward
0 new messages