There are a lot of customized or industry-specific operating/administrative systems that are really great at what they do to help the business manage the day to day operations, but they are usually limited in the accounting (“general ledger”) side.  So the idea to “integrate” that system with QuickBooks comes up often.

In some cases, these systems have built-in integration and/or data exporting tools for QuickBooks, and if that is the case, we could help you determine if the integration works well and how would the QuickBooks-based workflows need to be adjusted to have a successful integration experience.

But, there are cases where a custom integration solution is required, and we get hired to intermediate the project between the business owners/managers and the programmers that will built an API/SDK tool to synchronize data between the system and QuickBooks.  So these are the things we think about for these type of projects:

1.  QuickBooks Online (API) vs. QuickBooks Desktop (SDK)

Generally QuickBooks desktop will be better for detailed reports, limited access permissions to QB users, and more powerful built-in features for accounting.  It will require an SDK-based middleware app to be installed in the server and login to QB to push data.

QuickBooks Online will be easier to integrate via API and can be a bit more seamless when transferring the data as its all cloud based.  Easier for remote users to work, and the Banking interface is a thousand times better to automate the transactions coming from the bank & consequently for reconciling accounts.

2.  We break down the high level strategies into 5 areas:

  • What? We need to figure out what data inside your current system do you need to capture
  • How? We need to figure out how is that data going to be imported: Transaction by transaction, itemized summary, summarized journal
  • Why? We need to figure out how this data will be used in QB for reporting purposes and/or to avoid duplicate data entry
  • When? How often will the data be synced and/or for which time periods (daily, weekly, monthly, etc.)
  • Who? Who will have access to the integration tool to push the buttons (initiate, pause, resume, etc…) and verify that the data that is being synced is complete/accurate

3. Going a bit deeper into the What/How’s we need to think hard about what data we really need to see inside QuickBooks, that will change how we sync:

  • Transaction level: every transaction comes as one transaction with itemized product information and customer information based on transaction date.  This will give you potentially 6 dimensions: Date, Customer Name (and Customer Type <- Managed in QuickBooks), Class, Location, Product Purchased/Sold, and Account (driven by Product inside QuickBooks)
  • Itemized Summary: one daily (or weekly) transaction comes in without any customer details but all products sold are itemized. This will give you potentially 4 dimensions: Date, Class, Product Purchased/Sold, and Account (driven by Product)
  • Summarized Journal: is typically a daily, weekly, monthly journal entry that is a Journal Entry designed to match Chart of Accounts, typically a few (3-8) Income Accounts and the same for Direct COGS accounts in a journal entry broken down by class to have a secondary dimension. This will give you potentially 3 dimensions: Date, Class, and Account (based on product/account mapping done within the middleware)

4. Will the synced data be reconcilable with the banks?

We need to decide how this synced data will reconcile with the bank/credit card transactions, or just keep the data separate from bank data, allowing QB user to reconcile banks and credit card independent from data being synced.
We are not programmer and know nothing about using the API or SDK to build a program that will the actual data sync. But we do know a lot about the Business Logic as pertains to how QuickBooks will allow the data to be used and reported.  We can be a consultant working between the management team and the programming team to ensure successful logic in the data integration.
Hector Garcia, CPA
Advanced QuickBooks ProAdvisor
hector@garciacpa.com
Share →

Leave a Reply

Your email address will not be published. Required fields are marked *



For More Information

Call us to 954-633-2718 or email: hector@quickbooks-training.net