Author Topic: Importing contributions from bank records  (Read 3034 times)

Offline Chris Burgess

  • Ask me questions
  • ****
  • Posts: 593
  • Karma: 54
Importing contributions from bank records
« on: April 04, 2013, 01:53:42 pm »
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?

Offline Michael McAndrew

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 978
  • Karma: 42
    • Third Sector Design
  • CiviCRM version: various
  • CMS version: Nearly always Drupal
  • MySQL version: 5.5
  • PHP version: 5.3
Re: Importing contributions from bank records
« Reply #1 on: April 05, 2013, 08:20:38 am »
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...

Offline Parvez

  • Moderator
  • I post occasionally
  • *****
  • Posts: 87
  • Karma: 7
Re: Importing contributions from bank records
« Reply #2 on: April 05, 2013, 09:04:44 am »
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

Offline Chris Burgess

  • Ask me questions
  • ****
  • Posts: 593
  • Karma: 54
Re: Importing contributions from bank records
« Reply #3 on: April 07, 2013, 06:01:26 pm »
Definitely interested to see this in action, would be good to build on it and contribute back rather than starting from scratch.


Offline JoeMurray

  • Moderator
  • Ask me questions
  • *****
  • Posts: 470
  • Karma: 21
    • JMA Consulting
Re: Importing contributions from bank records
« Reply #4 on: June 16, 2013, 07:07:51 am »
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

Offline pdelbar

  • I post occasionally
  • **
  • Posts: 51
  • Karma: 3
  • Web integrator, Joomla freak, CiviFan
    • delius.be
  • CiviCRM version: 4.2.6/7
  • CMS version: Joomla 2.5.7 / 3.0
  • MySQL version: 5.1
  • PHP version: 5.3
Re: Importing contributions from bank records
« Reply #5 on: August 21, 2013, 11:54:13 pm »
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.