Aller directement à la fin des métadonnées
Aller au début des métadonnées

The below originally submitted by JoeMurray to Finance and Accounting but moved here for ease of reference:

Intuit QuickBooks offers the US version in 8 countries, and specific versions for the Canadian and UK markets (and Australia)

There are two main approaches that have been discussed to integrating CiviCRM with QuickBooks. In one, QuickBooks is used to track all clients and invoices. In the second, QuickBooks is used only for financial accounting, but not for invoicing and billing.

Update March 11, 2011: QuickBooks supports importing transactions using the IIF format (http://support.quickbooks.intuit.com/support/Articles/HOW12778). (While other import formats like Excel are supported, they are only for importing lists and not transactions.) Unfortunately, it is not possible to correlate a payment transaction with an invoice transaction via an IIF import:

Imported IIF files do not create links between transactions. For example, if one transaction is an invoice and another transaction is a payment for the invoice, the transactions will not be linked after importing the data. In this example you must open the payment in the Receive Payments window and apply it to the invoice after importing the data. (See http://support.quickbooks.intuit.com/support/Articles/INF12341)

QuickBooks also support a qbXML format for transactions (see https://developer.intuit.com/qbsdk-current/Common/newOSR/index.html). However, it appears that a special program is required then to get the data into QuickBooks (https://idnforums.intuit.com/messageview.aspx?catid=15&threadid=11886). This may be a barrier to adoption for non-technical people.

The notes below were initially written by Joe Murray based on JMA Consulting's experience in integrating online donation systems with various clients' billing systems over the past decade in Canada. They provide use case analysis and a high level architecture for data flows. They are not based on a review of any QuickBooks APIs. The accounting precepts have been verbally reviewed by Canadian accountants.

1. QuickBooks Has Full Client List and Does Invoices for all Payments

QuickBooks provides good support for businesses that track sales to specific clients, who have accounts with the business that are debited when sales invoices are issued and credited when payments are made against the account. Examples would be consulting companies, or companies that take orders and deliver later. This accounting style is not appropriate, however, for certain types of businesses that have more anonymous sales, for example, stores where payments are made at Point Of Sale.

While payments can be recorded as general ledger transactions, it is usually not as desirable as creating client accounts and recording the payments against the client. The wrinkle is that this requires invoices to be created against which the payments can be applied. This is a little counter-intuitive in the case of donations, and even in cases where a CiviCRM page has been used to accept payment for tickets or memberships from new people.

In terms of data flows, transactions from CiviCRM need to be matched against QuickBooks clients to see if the payer already exists, and if so, their info should be updated, an invoice for the payment amount created, and then a payment recorded against it. (At least that's one workflow that makes sense.) This can be done either in batch via a csv export and import, or potentially in realtime via API calls from CiviCRM to QuickBooks at time of payment.

In the case of paper cheques received by mail or other offline payments (eg manually processed credit or debit card payments), I would suggest it is best to record them in CiviCRM, then have the payments go into QuickBooks in the same way as the electronic payments.

The simplest workflow would involve creating CiviCRM contacts for all existing QuickBooks clients during initial deployment, and never entering new client or their payments directly into QuickBooks. This is likely not feasible in all cases. For cases where it doesn't work, it is necessary to support bidirectional client list and payment synchronization. In this case, the client list from QuickBooks needs to be migrated over to CiviCRM regularly (I'm assuming there is no hook available in QuickBooks for add / edit client operations, and thus a periodic batch operation that could be automated via cron is needed). Subsequent to client list migration, payments recorded as received can be migrated to CiviCRM. This will require the purpose of the payment received to be synchronized between QuickBooks and CiviCRM, eg QuickBooks products or services with CiviCRM tickets, memberships and donations.

Some thinking on the best way to do this in a general way is required. The trouble is in correlating these back and forth. It would be best if one could be made master and the other a slave. As the events will be varying regularly as new ones are added, it would be convenient if QuickBooks could be the slave. It would also be nice if there were some standard way of setting up the accounting for these revenues, along the lines of the standard Chart of Accounts Sharmeesha has mentioned is available in the US, with similar Charts of Accounts likely available in other jurisdictions.

An additional subject of some discussion is how to handle in CiviCRM those QuickBooks invoices that have been issued but for which payment has not been received. Assuming they have been encoded in a standard way that allows their contents to be correlated with CiviCRM objects (a big assumption), then it should be possible to set them up with a status along the lines of Pending (used for Pay Later), or Pledged (probably more appropriate). I would suggest a new reserved status for Unpaid invoice might be appropriate here.

2. QuickBooks Used for Financial Accounting But Not Client and Invoice Tracking for CiviCRM Activities

This is a much simpler use case. CiviCRM is the master for all client and transaction data, and only summary data for revenue of different types needs to be migrated either by batch or in real time to QuickBooks. In both cases work on standardized Charts of Accounts for this kind of revenue in each jurisdiction would be useful.

In batch mode, QuickBooks is updated after the end of each financial reporting period (usually each month) with the total of the revenue accrued for each type of revenue (eg events, memberships, donations, capital accounts donations, etc.). Transaction reversals need to be updated in CiviCRM. If, as is likely, there are reversals that affect affect revenue recorded in prior reporting periods (eg a September payment is reversed in October after the September books have been reconciled and closed), it is not normally necessary to separate these out since most of them will not materially affect the financial statements for the two periods. Basically, reversed transactions are usually few and far between and don't represent a  significant proportion of the income. However, if there is a materially large transaction that is reversed, eg a quarter of the revenue for the year, then it would be best from an accounting perspective to have a separate entry recorded in QuickBooks' general ledger for that particular reversal with notes attached to it. As this requires accounting judgement, is a very rare exception, and will always require manual intervention in QuickBooks, it is not necessary to have our automated system handle it. A manual general ledger entry can look after making this notation.

If it is necessary, which I don't think it normally would be, for revenue for the current accounting period to be accurate up to the moment in QuickBooks as well as in QuickBooks, then CiviCRM hooks could be used to invoke QuickBooks APIs assuming they exist to change the revenue total for the month after each CiviCRM financial transaction.

I've checked with a couple of accountants and they are sure that both methods are in accordance with Generally Accepted Accounting Practices in Canada and they believe other jurisdictions. The important point is that all transactions be auditable, which does not all have to be done in any particular piece of software like QuickBooks. So long as there is a record, even a paper one, for all the monies, that is sufficient. CiviCRM can provide that if it is used as the billing system, and all payment processor reversals are entered into it.

Étiquette
  • Aucun
  1. Sep 15, 2014

    Hi Joe,

    Is this the current document for Quickbooks Integration? It appears that the options for working with Quickbooks (standalone instances) where CiviCRM is the holder of all transactional data is to export from CiviCRM, convert to .iif and then import. Has anyone done a step by step tutorial on exporting from CiviCRM and converting?

    Thanks,
    Andrew


Creative Commons License
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-Share Alike 3.0 United States Licence.