beancount.plugins.zerosum

[redst...@gmail.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:

https://groups.google.com/d/msg/beancount/z9sPboW4U3c/UfJbIVzwmpMJ

[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]

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]

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

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]

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:

https://docs.google.com/document/d/1x0qqWGRHi02ef-FtUW172SHkdJ8quOZD-Xli7r4Nl_k/edit

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]

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]

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]

Thanks a lot! This is very useful.

-cb

[Martin Blais]

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:

https://docs.google.com/document/d/1x0qqWGRHi02ef-FtUW172SHkdJ8quOZD-Xli7r4Nl_k/

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/0ed721f2-8e1f-439d-a901-3fc0c925a122%40googlegroups.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.

Done: https://bitbucket.org/redstreet/redstreet.zerosum

 

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:

https://docs.google.com/document/d/1x0qqWGRHi02ef-FtUW172SHkdJ8quOZD-Xli7r4Nl_k/

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]

I've created a new doc for user contributions and added this link to it:

http://furius.ca/beancount/doc/contrib

--
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/528da6bb-91d9-4284-8a81-b3fce68eb3de%40googlegroups.com.