Importing contributions from bank records

[Chris Burgess]

Contributions from regular donors are imported by hand into a client's existing DB. They want to streamline this process, ideally by feeding banking data via CSV export into CiviCRM.

If getting donors to add their External ID, email address, or CiviCRM ID as a reference to their bank payment was plausible, this would be straightforward - just import contributions via CSV and match to the donor via one of those. Done!

But it's not - so I'm thinking we'll need to extend either the contact or membership with some custom data, populate that with the donor's existing reference, and then hook into or override the contributions importer to establish the match against contact (or better, the contact's membership / contribution).

An alternate (and possibly simpler) route will be to provide the client with a process for exporting to CSV, loading into a spreadsheet which matches bank references to contacts, then exports a modified CSV with a new column for their unique CiviCRM identifier.

Have you done something like this? How did you approach it?

[Michael McAndrew]

I think veda have done some work on an extension called something like  'financial import' that might be helpful for you.  i think it understands first/just giving imports and might be adaptable to this use case.  I'll see if parvez can point us in a better direction...

[Parvez]

Hey Guys

Yes, we've done something similar. Pretty much as you've described - using some custom data to remember references etc.

If you're interested let us know and we can share some of the code.

Thanks

[Chris Burgess]

Definitely interested to see this in action, would be good to build on it and contribute back rather than starting from scratch.

[JoeMurray]

Chris, in addition to Parvez's work there is an active CiviBanking project being driven consultants in continental Europe. I'm not quite sure on how or if they will be integrating Parvez's work into their generally supported extension.

See http://wiki.civicrm.org/confluence/display/CRM/CiviBanking

[pdelbar]

Chris,

CiviBanking does (will do) exactly what you are describing. First, it features a number of import plugins (CSV, ...) which allow you to load bank statements in different formats. We are finishing a Belgian CODA importer, and others will follow. The bank transactions which are loaded are a new entity in Civi, and they are processed by a set of matching rules which are currently being developed. For instance, one rule would match payments to pending contributions. Another rule would identify likely membership payments, based on amount, communication, ...

This is the basic description, there's actually quite a bit more stuff going on. The importers may mark some transactions as irrelevant to CiviCRM, but the power of the matcher might be interesting as an enrichment for the accounting people. There will be a set of SEPA Direct Debit matchers to process feedback on batch payments. It will include a bank account store to facilitate future matching. Etc ...

Status of the development : basic architecture is in place, we have dummy and CSV importers available, CODA importer is nearly done, we will have a first version with latch capability and some form of doc on how to configure it in early September. If you are considering joining the effort, let me know, and we'll get you started.