Integrate with accounting software

[alanna]

User story

As a fiscal host, I want to be able to use Open Collective alongside Quickbooks, Xero, and other accounting software. I need a way to link the data.

Pretty much all entities need to use accounting software to track their books. Open Collective fulfils part of this function, but not all of it, so Hosts will generally have both systems running in parallel. Currently it's a painstaking manual process.

Best solution for this problem

Open Collective does not want to become a fully-featured accounting tool, so we should have a way to export data that can be uploaded to accounting software. Most ingest CSVs, but it would need to be in a standard format.

We have created a custom version of this through Metabase for OpenMined - what would it take to make this a feature any host can use?

[Betree]

This is the template Xero provides for importing data into their system: BankStatementImportTemplate.csv. It could be interesting to check other platforms too when we'll write the specs for the revamped CSV exports.

[alanna]

I have got connected to an organization who recently built a quickbooks integration for their open source software, and I'm seeing if I can connect the dev who built that to one of our devs to get some more info.

[BenJam]

bringing some of the conversations in from the thread re. prioritisation for the next sprint. We need to clarify what the goals of 'integrating' are a little more. Some goals might be:

• compile accounts from within QuickBooks given the transaction details from Open Collective
• reconcile activity from open collective with activity in our bank account i.e. 'this $25 contribution to project x is this $23.50 received into our bank account'
• reconcile activity from our bank account in open collective i.e. 'this $2,000 transfer into our bank account should be assigned to project X from contributor Y'

Maybe @poohlaga can help scope this one out as she's using QB for OSC's accounts now...

[BenJam]

the thing I'm taking home from exploring this is quickbooks is opaque.

it's not obvious how QuickBooks wants you to import transactions into it and what the approach is for reconciling items that are added against bank account transactions.

The options for importing data are limited to:

Or, of course, there's the option to import transactions like a bank account, and reconcile the actual bank account against transaction data, but that process is pretty opaque too. If we were importing transactions from Open Collective then there are a couple of changes we would need to make but, honestly, I dont know whether that's the approach we would take.

frankly, I can't progress this further without any help from someone who actually uses and understands how QB works...

[poohlaga]

IMHO, if I wanted to reconcile my bank and Open Collective data I would

1. create/link a new 'bank account' via the manual upload option in QB (screenshot below)
2. get an export from OC in the (Bank Data format) and upload that into QuickBooks
3. reconcile the two accounts in QB at the end of each month

This would help ensure everything was paid out and I can see this being helpful for hosts. We can try it out for OSC.

[alanna]

OFX is the standard format - if we can use it over QBO or QBX I think we should, so our exports will also be ingestible by Xero and other tools.

We should have a meeting with us and Scott to talk this through in detail.

[iamronen]

@poohlaga

1. Would you need to do that operation with a "bank account" in Quickbooks twice - once for the ops account and once for the primary host account?
2. Would we first need to sort out that separation on the platform? to be able to do two separate exports?
3. If we don't sort that out on the platform, do you have a reasonable manual workaround for that?

[BenJam]

QFX is a proprietary variant of OFX used in Intuit's products. In Intuit products, OFX is used for Direct Connect and QFX for Web Connect

yesterday I found that importing transactions into QB required that

• we dont use ISO formatted datetimestamps
• we record credits and debits in two columns

it also looked like you'd need two files to import, one from the perspective of the main account and one from the host's own ops account.

[alanna]

@iamronen and @BenJam have been digging into this trying to get to the heart of the need.

Zooming out and setting aside details and assumptions about how people use accounting software, the key thing is essentially: How do we ensure the numbers on OC are synced with numbers in our bank account?

The way the world has answered that question in terms of "how do we ensure the numbers in our books match the numbers in our bank account?" is accounting software like Xero/Quickbooks. This is why I've brought up this issue in relation to the OCF situation, because at its root that problem is about OC numbers and bank numbers getting out of sync.

I think trying to re-create Quickbooks/Xero functionality in OC is the wrong way to go, so the natural conclusion is OC syncing with accounting tools could be the answer.

[alanna]

Copying comments from slack:

@iamronen asked: can you say more about what IS needed? saying "integration is the way" isn't saying much... the way to what? This may be obvious to you, but not to others.

1. What is it that a fiscal host admin needs to do and is currently unable to do?
2. If there was an "integration" what would they be extracting from OC And what would they be doing with it on Quickbooks?
3. What is the outcome of this work they would be doing on Quickbooks?
4. Given that there is no integration, how are they currently doing whatever it is they need to be doing?

My answers

1. Every host is a legal entity. Every legal entity must keep accounting records. That means recording every transaction against tracking codes and being able to export account reports like P&L, balance sheet, GST reports, annual reports, audit data, etc, reconciled against bank activity. 99% of entities achieve this through using accounting software like Quickbooks or Xero.
2. Right now Hosts have two parallel systems: accounting software and OC, both related to their bank accounts in different ways. Integration means making it easier to bring these together.
3. Usually required reports for government agencies (taxes, charities registers) which are in turn relied upon by funders for grant due diligence, required by boards of directors for oversight, etc.
4. Literally running two parallel systems and lots and lots of data entry, or complicated and brittle automations like the ones A-team has developed for OSC and OCF. Or just opting not to become a fiscal host at all because of the lack of this integration. “How do we integrate this with our accounting processes” is one of the guaranteed questions every potential fiscal host will ask, and some of them nope right out because they can immediately see the work that is required to run these parallel systems, which are semi redundant but neither fully sufficient for what they want to achieve, which is on the one hand the value prop of OC, and on the other hand is meeting accounting requirements. The latter is legally non-negotiable so either they opt for the messy manual integration or opt out of OC. Our own hosts are suffering because we’ve opted to try to make it work but it’s painful.

[alanna]

We also don't want to make too many assumptions about how hosts are set up, e.g. OSC and OCF have Ops/Main bank accounts, but OCNZ and Gift Collective just have one bank account. I don't know about OCEU, Accountable, etc. It's valid to just design this for OSC and OCF first, but if there's a way to make it more general, we should.

[iamronen]

I've been trying to make sense of the problem space ... I am applying common sense and may be missing professional terms ... but I've built up this visual description of the problem space ... adding it here in the hope that it may help others:

While starting at this I formulated these thoughts:

Financial Integrity

Financial integrity is achieved by matching up records from numerous financial ledgers (different kinds of accounts) and making sure they:

1. Match up
2. Add up
3. That there are no unaccounted for transactions

Validation Operations

1: Reconciliation

Reconciliation takes place between two entries:

1. A reliable source-of-record transaction- a trusted documentation of a financial transaction
2. A second reconciled ledger transaction that needs to correlate to the source-of-record as a sign of validity.

Example: when a top-up is transferred to a payments processor:

1. The trasnaction in the bank account from which money is being transferred is the source of record.
2. The transaction in the payment processor is confirmed by referencing the source-of-record and making sure that the amount minus transaction fee matches.

2: Internal Integrity

The internal integrity of a ledger can be determined regardless of other ledgers. It is essentially the balance matches the sum of all the transactions.

Example: the amount of money available to a collective equals the amount of money it raised minus the expenses it paid.

3: Alien Transactions

Alien transactions can evade reconciliation and internal integrity validations:

1. Reconciliation of a ledger in reference a source-of-record will not expose transactions that should not be there in the first place.
2. The reconciled ledger will have its own integrity regardless - the balance will reflect the sum of the transactions and will hide transactions which should not be there.

To check for alien transactions:

1. Transform the ledger being reviewed into a checklist and watch to see if after reconciliation there are no transactions left.
2. Run a kind of checksum that need to add up across ledgers. For example: BOPS + BMAIN = Sum(PS) + FH

[BenJam]

closing in favour of developing conversation happening over coda. We'll bring this back into GitHub when it's better developed as a practical process.