Adding new transactions and modifying existing transactions based on imported data

[Jeremy Maitin-Shepard]

I'd like to have a system for automatically integrating downloaded
bank/credit card transaction data with a ledger/beancount transaction journal.

In some cases there may already be a manually entered transaction that needs
to be matched up with a downloaded transaction, but in most cases the
transaction will be new and I'll use some automatic system for guessing the
associated expense category, similar to reckon.

In order to make the system not be brittle, I'd like to store some metadata
with each posting that has been matched with downloaded data (e.g. the
downloaded CSV data) in order to know which downloaded entries have already
been integrated into the journal. Additionally, for transactions that
transfer money between two of my accounts, the same transaction will appear
in the downloaded data for each of the two accounts.

Adding new entries is naturally fairly straightforward, but in the case that
I'm matching a downloaded transaction with an existing journal entry, either
because it was manually entered or already imported automatically from a
different account, I'd like to be able to update the file in place in order
to add new metadata. Is this something I can do with beancount without
having to write my own parser?

[Martin Blais]

On Sun, Jun 21, 2015 at 7:25 PM, Jeremy Maitin-Shepard <jer...@jeremyms.com> wrote:

I'd like to have a system for automatically integrating downloaded
bank/credit card transaction data with a ledger/beancount transaction journal.

Easy. 

In some cases there may already be a manually entered transaction that needs
to be matched up with a downloaded transaction, but in most cases the
transaction will be new and I'll use some automatic system for guessing the
associated expense category, similar to reckon.

In order to make the system not be brittle, I'd like to store some metadata
with each posting that has been matched with downloaded data (e.g. the
downloaded CSV data) in order to know which downloaded entries have already
been integrated into the journal.  Additionally, for transactions that
transfer money between two of my accounts, the same transaction will appear
in the downloaded data for each of the two accounts.

Adding new entries is naturally fairly straightforward, but in the case that
I'm matching a downloaded transaction with an existing journal entry, either
because it was manually entered or already imported automatically from a
different account, I'd like to be able to update the file in place in order
to add new metadata.  Is this something I can do with beancount without
having to write my own parser?

--
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/loom.20150622T011324-634%40post.gmane.org.
For more options, visit https://groups.google.com/d/optout.

[Martin Blais]

On Sun, Jun 21, 2015 at 7:25 PM, Jeremy Maitin-Shepard <jer...@jeremyms.com> wrote:

I'd like to have a system for automatically integrating downloaded
bank/credit card transaction data with a ledger/beancount transaction journal.

You can either setup a LedgerHub import file or write your own script that outputs Beancount syntax.

In some cases there may already be a manually entered transaction that needs
to be matched up with a downloaded transaction, but in most cases the
transaction will be new and I'll use some automatic system for guessing the
associated expense category, similar to reckon.

Sounds great. LedgerHub does not do automated categorization so far because I don't think it saves much time, but please experiment.

In order to make the system not be brittle, I'd like to store some metadata
with each posting that has been matched with downloaded data (e.g. the
downloaded CSV data) in order to know which downloaded entries have already
been integrated into the journal. 

You can use Beancount's metadata to store anything you like. It supports most of the basic datatypes Beancount does, e.g. you can store a date in there, an integer, an amount, etc. and... a string.

 

Additionally, for transactions that
transfer money between two of my accounts, the same transaction will appear
in the downloaded data for each of the two accounts.

We're still discussing how to handle this automatically in this proposal document:

http://furius.ca/beancount/doc/proposal-settlement

The goal is to 

a) deal with single (merged) transactions that have different dates in each account, and

b) deal wtih two transactions that need to be paired up and merged into one, perhaps automatically.

Very little concrete work has been done so far, but the doc describes some ideas and I think this will eventually be implemented.

Any ideas not already in the doc are welcome (you can reply to this or add a comment to the doc).

Adding new entries is naturally fairly straightforward, but in the case that
I'm matching a downloaded transaction with an existing journal entry, either
because it was manually entered or already imported automatically from a
different account, I'd like to be able to update the file in place in order
to add new metadata.  Is this something I can do with beancount without
having to write my own parser?

Can you edit the text file that is the result of the import code?

Yes.

Can those two separate downloaded transactions be automatically matched up and cancel each other?

Not at the moment. This is the subject of the (b) part above. Not done yet (neither is it in Ledger).  Personally I think it could eventually be done nearly automatically, but experimentation hasn't started yet.

(You should never have to write your own parser.)

I hope this helps, (sorry for the fat finger on previous email.)

[Jeremy Maitin-Shepard]

Thanks for the advice!

Martin Blais <blais <at> furius.ca> writes:
> Additionally, for transactions that
> transfer money between two of my accounts, the same transaction will appear
> in the downloaded data for each of the two accounts.
> We're still discussing how to handle this automatically in this proposal
document:
>
> http://furius.ca/beancount/doc/proposal-settlement

A mechanism for storing the date that the transaction posts to each account
would certainly be nice. The limbo/transfer accounts idea certainly sounds
like a reasonable approach.

> Can you edit the text file that is the result of the import code?
> Yes.

What I mean is that my import script may detect that a transaction that was
downloaded matches a transaction already present in the text format journal.
What I'd like to do is automatically add some metadata to the relevant
posting (since downloaded transaction data is per-account). For instance I
might have manually entered somewhere in the journal:

2015-06-22 *
Assets:BankAccount
Expenses:Groceries 50 USD

I'd like the import script to edit the journal file in place to be something
like:

2015-06-22 *
Assets:BankAccount
imported_from: "some identifier goes here"
Expenses:Groceries 50 USD

I see that each transaction has an associated line number, but there doesn't
seem to be a line number associated with each posting. Therefore, it seems
I would have to do a bit of parsing in order to find the correct place in
the file to insert the metadata line. Granted, this parsing should not be
too difficult given that I would know the starting line number for the
transaction and the account name.

[Martin Blais]

On Mon, Jun 22, 2015 at 2:30 PM, Jeremy Maitin-Shepard <jer...@jeremyms.com> wrote:

> Can you edit the text file that is the result of the import code?
> Yes.

What I mean is that my import script may detect that a transaction that was
downloaded matches a transaction already present in the text format journal.

LedgerHub has some limited functionality for doing that. If it detects a "similar" transaction (by some definition of similar, I think it tolerates a difference of 3 days on the date and checks for a posting with the same number on it) already exists in the Beancount input file, it will output it as commented out. Works pretty well for me, but sometimes _some_ of the transactions I've modified too much will appear uncommented in the stream. I think it would be sufficient to comment out all transactions until the last one that has been detected as a duplicate.

Some code here:

https://bitbucket.org/blais/ledgerhub/src/87caa595fbc532fe44ce22b46c296468ca803235/lib/python/ledgerhub/driver/dups.py?at=default

 What I'd like to do is automatically add some metadata to the relevant
posting (since downloaded transaction data is per-account).  For instance I
might have manually entered somewhere in the journal:

2015-06-22 *
  Assets:BankAccount
  Expenses:Groceries   50 USD

I'd like the import script to edit the journal file in place to be something
like:

2015-06-22 *
  Assets:BankAccount
    imported_from: "some identifier goes here"
  Expenses:Groceries   50 USD

I see that each transaction has an associated line number, but there doesn't
seem to be a line number associated with each posting.  Therefore, it seems
I would have to do a bit of parsing in order to find the correct place in
the file to insert the metadata line.  Granted, this parsing should not be
too difficult given that I would know the starting line number for the
transaction and the account name.

I think I see what you are trying to do. Here are a few thoughts:

- Without doing any parsing yourself you could figure out the line number: use Beancount itself to parse the input, the transaction has the starting line number. Then iterate through the data structure it provides you with.. the list of postings to figure out which one you want to insert after and you should be able to count the lines that way (make sure you count metadata lines as well). This should work most of the time (multi-line strings would need special handling).

- In theory the parser could be modified to add (filename, line-no) to each posting's metadata (but that seems to me a bit overkill).

- If you really do want to splice out the transaction yourself, you could write a Python script to find it, extract its input, and you can use beancount.parser.parse_string() to convert it to a data structure to inspect it.

- Instead of modifying your input file, this should be a feature of your importer code. I would welcome such an addition to LedgerHub (if triggered by an option). Then when you insert the extracted transactions they would have the id already printed out.

- I don't want to squash your enthusiasm, but I think you're going a bit overkill on the import features. In most cases you're going to import all postings of a particular account from the same source and it should be easy with a little bit of grepping on numbers to find out which saved file (if you save them like I do) the import came from. I would also bet you'll never actually end up needing doing that (do you have a particular usage for this in mind?). If you don't have some really specific need to implement this, I would let balance assertions be your guardrails, they're probably good enough.

[jere...@gmail.com]

On Tuesday, June 23, 2015 at 8:47:51 AM UTC-7, Martin Blais wrote:

On Mon, Jun 22, 2015 at 2:30 PM, Jeremy Maitin-Shepard <jer...@jeremyms.com> wrote:

> Can you edit the text file that is the result of the import code?
> Yes.

What I mean is that my import script may detect that a transaction that was
downloaded matches a transaction already present in the text format journal.

LedgerHub has some limited functionality for doing that. If it detects a "similar" transaction (by some definition of similar, I think it tolerates a difference of 3 days on the date and checks for a posting with the same number on it) already exists in the Beancount input file, it will output it as commented out. Works pretty well for me, but sometimes _some_ of the transactions I've modified too much will appear uncommented in the stream. I think it would be sufficient to comment out all transactions until the last one that has been detected as a duplicate.

Some code here:

https://bitbucket.org/blais/ledgerhub/src/87caa595fbc532fe44ce22b46c296468ca803235/lib/python/ledgerhub/driver/dups.py?at=default

Thanks, I'll take a look.
 

I think I see what you are trying to do. Here are a few thoughts:

- Without doing any parsing yourself you could figure out the line number: use Beancount itself to parse the input, the transaction has the starting line number. Then iterate through the data structure it provides you with.. the list of postings to figure out which one you want to insert after and you should be able to count the lines that way (make sure you count metadata lines as well). This should work most of the time (multi-line strings would need special handling).

- In theory the parser could be modified to add (filename, line-no) to each posting's metadata (but that seems to me a bit overkill).

I decided to look into the parser code yesterday and saw that it was quite trivial to add this, since the function for processing postings already receives the filename and lineno, so it was just a matter of adding it to the metadata.
 

- If you really do want to splice out the transaction yourself, you could write a Python script to find it, extract its input, and you can use beancount.parser.parse_string() to convert it to a data structure to inspect it.

- Instead of modifying your input file, this should be a feature of your importer code. I would welcome such an addition to LedgerHub (if triggered by an option). Then when you insert the extracted transactions they would have the id already printed out.

I imagined that it would include the metadata when outputting a transaction, but there would be two cases in which this would not be sufficient: the transaction might have been manually entered, or the transaction may have been generated by the importer, but based on downloaded data for the *other* account.  In either case I would need to add the metadata to one of the postings of an existing transaction.

- I don't want to squash your enthusiasm, but I think you're going a bit overkill on the import features. In most cases you're going to import all postings of a particular account from the same source and it should be easy with a little bit of grepping on numbers to find out which saved file (if you save them like I do) the import came from. I would also bet you'll never actually end up needing doing that (do you have a particular usage for this in mind?). If you don't have some really specific need to implement this, I would let balance assertions be your guardrails, they're probably good enough.

You may certainly be right.  My goal is to automate the process enough that I actually do it (which given my laziness is a high barrier), but since I don't really have experience using such a workflow, I might be considering the wrong things.

The reason for adding the imported_from metadata, which might in fact contain the entire CSV line, since there isn't any unique id per transaction in my data, is not for my own manual reference purposes.  Rather it is so that the import tool can determine automatically which records in the CSV file have already been handled (and should therefore be ignored).  This way I don't have to rely on some external or less robust method of tracking it, such as just matching based only on date and amount, or storing the information outside of the text journal file.

The issue with duplicates is that for any transfer transaction (like paying a credit card bill or moving money between my own accounts), the transaction will appear exactly once on each of the two account statements.  These are fairly frequent so I'd like not to have to deal with them as exceptions.

[Martin Blais]

I think I see what you are trying to do. Here are a few thoughts:

- Without doing any parsing yourself you could figure out the line number: use Beancount itself to parse the input, the transaction has the starting line number. Then iterate through the data structure it provides you with.. the list of postings to figure out which one you want to insert after and you should be able to count the lines that way (make sure you count metadata lines as well). This should work most of the time (multi-line strings would need special handling).

- In theory the parser could be modified to add (filename, line-no) to each posting's metadata (but that seems to me a bit overkill).

I decided to look into the parser code yesterday and saw that it was quite trivial to add this, since the function for processing postings already receives the filename and lineno, so it was just a matter of adding it to the metadata.

You got it:

https://bitbucket.org/blais/beancount/commits/65005cb55f20ea94e4d1204e54e3b12264807263

(No difference in performance, so why not.)

 

 

- If you really do want to splice out the transaction yourself, you could write a Python script to find it, extract its input, and you can use beancount.parser.parse_string() to convert it to a data structure to inspect it.

- Instead of modifying your input file, this should be a feature of your importer code. I would welcome such an addition to LedgerHub (if triggered by an option). Then when you insert the extracted transactions they would have the id already printed out.

I imagined that it would include the metadata when outputting a transaction, but there would be two cases in which this would not be sufficient: the transaction might have been manually entered, or the transaction may have been generated by the importer, but based on downloaded data for the *other* account.  In either case I would need to add the metadata to one of the postings of an existing transaction.

- I don't want to squash your enthusiasm, but I think you're going a bit overkill on the import features. In most cases you're going to import all postings of a particular account from the same source and it should be easy with a little bit of grepping on numbers to find out which saved file (if you save them like I do) the import came from. I would also bet you'll never actually end up needing doing that (do you have a particular usage for this in mind?). If you don't have some really specific need to implement this, I would let balance assertions be your guardrails, they're probably good enough.

You may certainly be right.  My goal is to automate the process enough that I actually do it (which given my laziness is a high barrier), but since I don't really have experience using such a workflow, I might be considering the wrong things.

Here are parameters which I think are helpful to getting into the habit:

- Making sure your text editor has working completion on account names (emacs and vim are supported)

- Start small, start with just one account. I recommend starting with a credit card account, because there are many transactions and no cost basis issues. 

- Get into the habit of doing it once per week. e.g., I update my ledger on Saturday mornings for all the basic accounts.

See if you like it for a while like this.  If you do, then add checking & savings account. Do that for a while. Then later on add investments.

The reason for adding the imported_from metadata, which might in fact contain the entire CSV line, since there isn't any unique id per transaction in my data, is not for my own manual reference purposes.  Rather it is so that the import tool can determine automatically which records in the CSV file have already been handled (and should therefore be ignored).  This way I don't have to rely on some external or less robust method of tracking it, such as just matching based only on date and amount, or storing the information outside of the text journal file.

Ouf... that's going to make for really ugly input. 

Most CSV downloads have really long lines with ugly formats.

The issue with duplicates is that for any transfer transaction (like paying a credit card bill or moving money between my own accounts), the transaction will appear exactly once on each of the two account statements.  These are fairly frequent so I'd like not to have to deal with them as exceptions.

One day that settlement proposal will be implemented and you will have features to make this a bit easier.

I would just dedup manually for now and get started anyway. If you have good editor-fu it'll cost you an extra 10 seconds per week probably, and minute for minute you would never recoup the time you'll spend hacking your cool idea.

Just my 2c,