Accounts and other boring stuff

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.

Comments

Hi,

No matter what superhero you got on your team, sync the CRM and the accounting is tricky, and paypal doesn't help.

It also depends on how much detail you want on the accounting side. Is membership fee 1000 good enough or do you need to get the detail that Ms Rose paid 86 and Smith got the premium one at 200 and ...?

I'd be tempted to think that adding a thin layer to generate custom invoices when needed on civi, and only dealing with summaries and monthly paypal transfers is good enough to keep the accountant happy.

No matter what, that's a very important topic, and having a product two letters away from Zero is a good start ?

X+

Why would you want to integrate Civicrm to a proprietary accounting system when there are free and open source ones out there, such as xtuple postbooks, and weberp to name a couple. I have started evaluating and compiling a lot of information on the subject of open source accounting for SMB here:
http://fblauer.com/wordpress/?page_id=12
I hope this helps.

I've spent a bit of time thinking about all the issues around accounts & CiviCRM and I have a couple more blogs to post... One of the reasons that this is dear to my heart is that I am the Treasurer for our organisation so I actually do the accounts.

I'd certainly like to hear other people's thoughts about how they transfer data from and to their accounting systems. I have seen other posts querying whether CiviCRM does integrate with an accounting package and I'm interested what people perceive this integration might look like.

I guess I replied in the wrong place. Here was my comment:
Why would you want to integrate Civicrm to a proprietary accounting system when there are free and open source ones out there, such as xtuple postbooks, ledgersmb, and weberp to name a few. I have started evaluating and compiling a lot of information on the subject of open source accounting for SMB here:
http://fblauer.com/wordpress/?page_id=12
I hope this helps.

Thank you, that is a useful link. There are also some good links on this for Wikipedia.

My aim in writing this blog (and others to come I'm afraid) is mostly to flesh out the ideas around integration. I hope that using Xero as an example doesn't detract from that as I would think that the issues would be similar with the packages you refer to.

Having said that, I spent time looking into what accounting system to use at the same time that I investigated CiviCRM. In the end I concluded that I didn't want to use an Open Source accounting system or payroll system because there are country specific regulatory compliance issues which I didn't want to become a specialist in. Also, I didn't particularly want to take responsibility for the security issues when dealing with applications that can interact directly with my bank account. Finally, the flexibility that is an advantage in many areas of Open source can be a disadvantage when dealing with Accounting Applications as rigidity is often there for a reason and it's good to give your accountant and auditor something they are familiar with if you want to keep the accounting bill down.

This is a decision each organisation must make for themselves and the reasons that made sense for me may not make sense to others.

Here are some of the key integration issues I have identified working on this stuff:
(1) The actual CRM data (donor, participant, beneficiary) system of record is CiviCRM.
(2) The atomic donation system of record is CiviCRM. This is a key point... if I am asking how much money Person A gave, I look in CiviCRM. I don't look in the accounting system.
(3) The aggregate donation system of record is the accounting system. If I want to know how much money was donated last month to the organization, I ask the accounting system.
(4) They key entities in accounting systems that need to be sync'd are the invoices & the payments. As soon as you sync invoices (i.e. promises to pay that are booked to the general ledger as accounts payable) you are in for a world of complicated hurt (i.e. some folks book some types of pledges as accounts payable, others don't touch the GL until a real payment comes in).

Simplifying assumptions:
(0) CRM data in the accounting system is minimal- names and unique ID to connect back to the CRM - and it can be inaccurate (last year's donation from Jim Smith remains a donation from Jim Smith even after he changes his name to Jim Jones)
(1) Atomic donation data and aggregate donation data do not have to match exactly across systems. If CiviCRM tells me I got $15K last month and my accounting system tells me I got $14K last month (because a check bounced) that should be OK.
(2) Invoices (promises to pay) stay in the fundraising system. They only hit the accounting system when there is a GL impact (i.e. a pledge recorded as a receivable or a payment is received).

Therefore:
(1) Integration is one way: CiviCRM --> Accounting. Any Accounting --> CiviCRM integration (bounced checks) is done by hand or future integration.
(2) You generate a list of payments from CiviCRM and send them to accounting. Detailed financial reporting in the accounting system can become problematic since the financial system lacks some details.

Wrinkles: Paypal.
(1) You'll probably have to first take the payments from CiviCRM and verify they match paypal before integrating the paypal payments into accounting

What CiviCRM can do to smooth integration:
(1) Create a very clear payment entity that maps to accounting system for every monetary transaction. A contribution is a payment. A pledge fulfillment is a payment. A completed membership / event registration is a payment. (enforce the assumption that receivables generated in CiviCRM - pledge, membership w/o a check, event w/o a check never hit the GL -- somebody else can do that integration)
(2) Make sure payments can easily be reconciled with PayPal. If you can verify that all paypal payments recorded in CiviCRM actually happened, you can go straight from CiviCRM to accounting without worrying about the paypal data (The PayPal GL transaction is therefore just an account to account transfer -- no transaction data).

Thank you for your thoughtful reply. I was afraid that it might be a topic that didn't draw much interest but I'm glad I did get some discussion.

I note that your response is almost the opposite of mine!

I think there are some key reasons why and central to this is the diverse usage of CiviCRM.

The first big difference between our approaches is the assumption about where payment data comes from. I have to admit I hadn't given any thought until after I posted this blog as to how to deal with cash and cheque payments. The reason is that about 50% of the payments we receive are credit card payments and the other 50% are payments made directly into our bank account. I guess prevalent payment methods vary a bit by country.

Secondly, I note that you refer to donations. I don't deal much with donations. I generally deal with event registrations and when people register and don't pay we do follow up - either until we determine we should cancel the registration or until they pay. About 20% of our revenue comes out of this follow up (ie. people who attend but don't get around to paying) which is why we do a lot of matching and comparing and reporting to be sure we have matched the payments correctly.

The third area, however, doesn't relate to how we use CiviCRM. It relates to what we expect from an accounting package - and specifically how payment information gets into it. I'm wondering if you can clarify your experience because in all the packages I have given serious consideration to the payment data comes directly from the bank accounts (either by automated download or manual download followed by an upload). Hence, the idea of uploading payment records into an accounting package just seems wrong to me. I'm wondering if you could clarify.

(If using the Xero / Paypal combo then the paypal payments are downloaded in the same way and it is effectively another bank account)

Warning: I am not an accountant and this stuff gets ultra complex quickly

There are a couple of axis that are important here:

Small vs. Large
Small organizations experience little in the way of credit card charge backs or bounced checks, hence they don't incur a big data entry burden to handle that rare occurrence. Large organizations need a business process way to deal with it. And whatever CiviCRM does to support accounting integration should support the needs of big and small.

Transaction Validity
Credit card and bank transfers tend to be valid immediately (unless there is a charge back).

So I totally agree that for a small organization dealing with credit card transactions, you can use a much simpler model. But whatever CiviCRM does to make accounting integration easier should support the more generalized case.

Payments & bank statements
Many accounting packages use a flow like this:
(1) Sales Order (unapproved --> approved)
(2) Invoice (pending --> fulfilled)
(3) Payment (pending --> reconciled)

I simplified the statuses, but the general model is you have three entities (sales order, invoice, payment) and each entity has a variety of statuses.

My model assumes that you pull in payments and then reconcile them transaction by transaction with your bank statement (if you get a lot of checks, the bank statement import doesn't really have enough information to tie them to a CiviCRM payment- if you do a lot of credit cards, you usually have the name and name & amount is good enough).

So your matching a transaction in a GL revenue account to a transaction in a GL bank account.

I have not tested this workflow beyond a couple of higher end accounting systems.

Bottom line:
From a product development perspective, I would create a very smooth integration workflow for quickbooks. I'd then abstract that integration. Implement it, and that would be that.

I just want to note that I have changed my mind with regards to this comment:

(I suspect pending credit card transactions should just be cancelled).

On reflection abandoned credit card transactions for event registrations usually happen due to technical difficulties and we follow them up just as we would for any pending registration and usually receive payment. The main problem is that generally when people abandon a credit card payment they go back in and instead of being able to 'pick up' the previous registration where they left off and complete it a new one is created. Ideally this behaviour would be changed (resulting in multiple registrations for the same event) to allow the original registration to be completed - in which case pending registration 'invoices' would be uploaded.

I'm finding this discussion interesting since a client of mine that was thinking of integrating its QuickBooks accounting system into Drupal Ubercart (QuickBooks is one of the most common North American accounting packages for small organizations, both for profit and non-profit.) The initial spec was to continue with the accounting package being the system of record for the client and invoice list. But they've asked for another look at this, and are considering moving all of the details about clients and invoices out of their accounting system and into a billing system, whether CiviCRM, Ubercart, or a more specialized system like Freshbooks is still to be determined. On this approach, the accounting system does not track clients and invoices. Instead, it would just get batch totals with the aggregates for each income line - events, grants, rent, etc. - and wouldn't be responsible for the fine details of Accounts Receivables.

Hello,

Thanks for your comments. This is another quite different approach. I suspect, however, that it may be a pretty common one.

I guess the question it raises is how much accuracy is required. If you don't use the bank account downloads as a central part of your process how much work does it cause if when you upload all your totals your bank account balance doesn't match? How much tolerance is there for discrepancy?

The other thing I note is when you mention just uploading aggregates you mention grants and rent as well. Is your intent that they would all be managed in the same (non-quickbooks) system? Or would there be multiple systems all contributing pieces of information?

What is the advantage of using Freshbooks over Quickbooks online?