Accounts and other boring stuff

2009-08-01 02:44
Written by

One of the areas that occasionally hits the forums is whether CiviCRM integrates with accounting systems. I've been giving a little thought to accounts integration lately and have now spent a bit of time poking around the Xero API and thinking about what I would do if I were to spent time trying to get CiviCRM talking to Xero. The content of this blog is mostly non-technical so if you can safely ignore the stuff about APIs if it doesn't mean anything to you.

For those (most) of you that haven't heard of Xero, it is the company that would have made me rich had I bought shares when I said I thought I should. If I had also bought oodles of US dollars at the same time and sold them 3 months ago I would now be sitting on a tropical island instead of writing this. Such things aside, Xero is an online accounting package with a RESTFUL interface which targets small to medium businesses and organisations and is aiming for World Domination. I believe they will fail as my three-year-old has a spider-man outfit and he knows how to use it.

Xero is probably representative of a bunch of different accounting applications but unlike the others I have a test account for Xero and so I'm using it as the basis for my thoughts. One nice thing about Xero is that it accesses your bank account overnight (if you authorise it) and downloads all your transactions so you just need to code them or match them with invoices. Another nice thing is that Xero has already been integrated into Paypal and if you are using Paypal it will treat it as another bank account and download all the transactions going into that account too.

Here are the functions that Xero allows through the API:
- Add Contact
- Get Contact
- Add Invoice
- Get Invoice
- Get Tracking codes (these are essentially custom fields that can be associated with invoices or payments/receipts
- Get chart of accounts

What struck me looking at this selection is that there are two main integration areas. The first is transfer of contact information between the two systems. This seems conceptually rather simple so I will leave it at that. The other area is invoices which is what I will focus on now.

In accounting systems there are two main types of transactions relating to receiving money. One is a commitment to pay: an invoice. The second is the receipt of money in order to pay that invoice. Sometimes the two happen at the same time (e.g. a credit card transaction) but in other circumstances the money is received later and for various reasons (installments, refunds, mistakes, paying for multiple invoices in single transaction) there is not always a direct match between the two.

So, what is an invoice in the context of CiviCRM? From my perspective an invoice is a participation record, a grant record, a membership record and (possibly) a pledge record. Is a contribution an invoice? It would seem that a contribution record is a payment rather than an invoice, and that for those contributions which don't relate to another type of payment commitment there is no separate invoice record.

What is the significance of thinking about invoices? Well, the first important thing to remember is that ALL CiviCRM installations are integrated with accounts systems. It's just that the integrator is often a person who sits there doing data entry or pulling their hair out trying to make things match up. However, whatever method of integration is used, the source data for payments is generally the bank account. When using Xero this source payment data is downloaded from the bank account and uploaded ready to match to invoices. In an effective system the process of coding transactions is done as part of putting invoices in the system. The person doing the accounts is then able to match up the payments against the invoices with relative ease and do debtor maintenance as appropriate (statements, reporting, follow-up).

The discussion above points to some difficulties with integrating CiviCRM with accounting systems but I'm going to assume that like most things any integration would start small and focus now on event registrations and what might be acheived by integrating with CiviEvent.

The first stage would be to upload the details of any Participation records as invoices into Xero. The upload process (an API call to a REST interface) could be scheduled as an overnight job (or triggered during an event registration) and would upload any new participation records that are Completed or set to 'pay later'. (I suspect pending credit card transactions should just be cancelled). Part of the information sent would be accounting information relating to the event and relevant tracking/ reporting information (ie. custom data fields for the event - although at the moment event custom data fields are visible to the public so that's one hiccup).

What would this achieve? The key achievement from an accounts point of view is that the person reconciling the banking does not need to code the invoices which allows both more division of labour and less manual checking. (as someone who does code CiviCRM payments I can confirm it can be pretty time consuming). It would probably be especially useful in NZ where direct bank transfers are perhaps the most common payment method and identifying all the bank transactions can be challenging. However, equally time-consuming from an accounts point of view is dissecting one $1400 transfer from Paypal into 15 differently coded payments. Being able to match the transactions directly in Xero would be time-saving in and of it's own. (Although that has to balanced against the time that it would take to code it).

So, what is the second stage? Will superman arrive in time? Will road runner fall off the cliff? Did the man in the green beanie do it? And who was that masked mouse? Find out next blog because I've made the tragic discovery that my writing on this topic is longer than I can reasonably expect anyone to read in a single sitting.