Setting up Python QuickBooks Integration: 4 Easy Steps

It is a common practice for most businesses today to rely on data-driven decision-making. Businesses collect a large volume of data and leverage it to perform an in-depth analysis of their customers and products, allowing them to plan future Growth, Product, and Marketing strategies accordingly. In order to perform a proper analysis, the first step is to extract data from all platforms being used by the business and converting it into a form suitable for analysis.

One of the most popular platforms that is used by numerous small businesses today to manage their financial data is QuickBooks. This article will provide you with an understanding of how you can set up QuickBooks Python integration and then use Python to standardize the extracted data for analysis.

Table of Contents

• Introduction to Python
• Understanding the Key Features of Python
• Introduction to QuickBooks
• Understanding the Key Features of QuickBooks
• Steps to Set up Python QuickBooks Integration
  • Python QuickBooks Integration Step 1: Installing the Required Python Packages
  • Python QuickBooks Integration Step 2: Setting up Python Virtual Environment
  • Python QuickBooks Integration Step 3: Configuring the Singer Tap
  • Python QuickBooks Integration Step 4: Standardizing the Data
• Conclusion

Introduction to Python

Python is one of the most popular general-purpose programming languages that was released in 1991 and was created by Guido Van Rossum. It can be used for a wide variety of applications such as Server-side Web Development, System Scripting, Data Science and Analytics, Software Development, etc. 

Python is an Interactive, Interpreted, Object-Oriented programming language that incorporates Exceptions, Modules, Dynamic Typing, Dynamic Binding, Classes, High-level Dynamic Data Types, etc. It can also be used to make system calls to almost all well-known Operating Systems.

More information about Python can be found here.

Understanding the Key Features of Python

Some of the most well-known features of Python are as follows:

• Free and Open-Source: Python is available free of cost for everyone and can easily be downloaded and installed from the official website. Open-Source means that the source code is openly available. This gives users with enough knowledge the ability to make changes to the code as per business use cases and product requirements.
• Easy to Code and Read: Python is considered to be a very beginner-friendly language and hence, most people with basic programming knowledge can easily learn the Python syntax in a few hours. 
• High-Level: While using Python, developers do not need to have any information on the System Architecture or manage memory usage manually. All this is automatically handled by the Python Interpreter.
• Portable: A Python code written on one system can easily be transferred to another system and can run without any issues.
• Interpreted: Python code is processed by the Interpreter at runtime. This means that users do not need to compile the code and then run it like other programming languages such as Java, C/C++, etc.
• Object-Oriented: Python also has support for the Object-Oriented Programming Paradigm which allows users to write readable and reusable code.

Introduction to QuickBooks

QuickBooks is a popular accounting software that was developed by the leading financial software developer, Intuit. It is used to manage business finances primarily by small and mid-sized businesses. QuickBooks allows users to manage Sales and expenses, file taxes, generate reports as per their requirement, etc. In addition, the accountancy software offers numerous in-built reports that can easily be customized and gives businesses the ability to take complete control of their finances. QuickBooks is used by businesses to perform the following operations:

• Track income and expenses.
• Keep track of daily transactions.
• Generate reports for planning.
• Prepare billings.
• Manage payroll.

More information about QuickBooks can be found here.

Understanding the Key Features of QuickBooks

The key features of QuickBooks are as follows:

• Cloud Accounting: QuickBooks allows users to access their accounts, manage their business anywhere on their mobile, computer, or tablet.
• Invoicing: Users can create custom and professional invoices, sales receipts, and estimates seamlessly within minutes using QuickBooks.
• Cash Flow Management: QuickBooks houses functionality that allows businesses to save bills from vendors and schedule recurring payments for when they are due.
• Accounting Reports: Embedded Analytics to instantly understand how your business is performing with customizable dashboards and reports.
• Online Banking: With the QuickBooks Online Software banking integration feature, statements and transactions will automatically be updated.

Steps to Set up Python QuickBooks Integration

Users can set up Python QuickBooks integration by using Singer. Singer is a community-maintained library of Open-source extractors and loaders that simplifies the process of data extraction and consolidation. At the core of Singer are Taps (data extraction scripts) and Targets (data loading scripts). Taps extract data from any source and deliver the data in a JSON-based format over stdout. Targets consume data from the Taps and perform some operations with it, like loading it into a file, API, database, etc.

The Singer project has 150-200 Taps and Targets that can be used to move JSON-formatted data between databases, Web APIs, files, queues, etc. You can set up Python QuickBooks integration by implementing the following steps:

• Python QuickBooks Integration Step 1: Installing the Required Python Packages
• Python QuickBooks Integration Step 2: Setting up Python Virtual Environment
• Python QuickBooks Integration Step 3: Configuring the Singer Tap
• Python QuickBooks Integration Step 4: Standardizing the Data

Python QuickBooks Integration Step 1: Installing the Required Python Packages

In order to set up the Python QuickBooks integration, you will need to install the following Python packages:

• tap-quickbooks: A Singer Tap (data extraction script) that extracts data from the QuickBooks API. More info on GitHub. 
• target-csv: A Singer Target (data ingestion script) that consumes JSON data and converts it to CSV format. The CSV file format is widely used for storing tabular data. More info on GitHub.
• singer-discover: An Open-source utility that selects streams and fields from a Singer catalog. More info on GitHub.
• pandas: An Open-source Python Library for Data Analysis and Manipulation. More info on their official website.
• gluestick: An Open-source Python package containing utility functions for ETL. More info on PyPi.

Python QuickBooks Integration Step 2: Setting up Python Virtual Environment

The primary purpose of setting up a Python Virtual Environment is to create an isolated environment for Python projects. Singer Taps tend to have many dependency conflicts with each other. You can avoid issues caused by inconsistencies between dependencies by wrapping them all up into a project-specific virtual environment.

• Install the virtualenv library: 

pip3 install virtualenv

• Create the virtual environment:

virtualenv tap-quickbooks

• Activate the virtual environment:

source tap-quickbooks/bin/activate

• Install Jupyter Lab:

pip3 install jupyterlab

• Install tap-quickbooks:

pip install git+https://github.com/hotgluexyz/tap-quickbooks.git

• Install target-csv:

pip install git+https://github.com/hotgluexyz/target-csv.git

• Install gluestick:

pip install gluestick

• Install pandas:

pip install pandas

• Install the dependencies:

pip install singer-python==5.3.1 prompt_toolkit==1.0.14 requests==2.20.0 xmltodict==0.11.0 jsonpath-ng==1.4.3 pytz==2018.4 attrs==20.2.0 ipykernel

• Make the virtual environment available to Jupyter Lab by installing ipykernel:

python -m ipykernel install --user --name=tap-quickbooks

• Create a workspace for this:

mkdir quickbooks-integration

• Enter the directory:

cd quickbooks-integration

Python QuickBooks Integration Step 3: Configuring the Singer Tap 

First off, you’re going to need your QuickBooks OAuth credentials, i.e., Client ID and Client Secret to set up the Python QuickBooks integration. You can get the OAuth credentials in your application’s Keys and OAuth section under Development or Production.

Here is an example of your account’s Client ID and Client Secret:

Next, you need to create a Singer configuration that will specify your OAuth credentials and some Singer-specific settings. Here is an example configuration file:

{
  "client_id": "your_client_id",
  "client_secret": "your_client_secret",
  "refresh_token": "abcdef123456",
  "start_date": "2020-10-06T00:00:00Z",
  "api_type": "BULK",
  "select_fields_by_default": true,
  "sandbox": true,
  "realmId": "1234567890"
}

Substitute the values with your credentials, and save this to a file called config.json in your local directory.

Singer Taps offer a discover command which prints a JSON object that reveals the data that is available from QuickBooks. You will have to save the output in a catalog.json file. Run the following discover command:

tap-quickbooks --config config.json --discover > catalog.json

The output of the command will be in the following format:

less catalog.json

{
    "streams": [
        {
            "stream": "Invoice",
            "tap_stream_id": "Invoice",
            "schema": {
                "type": "object",
                "additionalProperties": false,
                "properties": {
                    "AllowIPNPayment": {
                        "type": [
                            "boolean",
                            "null"
                        ]
                    },
…

From here, you will have to select the objects that you intend to sync. You can use the Singer-discover utility for this. Run the following command to switch the singer-python version to the version of singer-discover:

pip install https://github.com/chrisgoddard/singer-discover/archive/master.zip singer-python==5.4.1 prompt_toolkit==1.0.14

Next, create the catalog that you just selected.

singer-discover --input catalog.json --output properties.json

This will launch an interactive utility to select the streams (objects) you want from QuickBooks. Choose Invoice and press enter. This will prompt you with the option to choose specific fields. Accept the default options and press enter.

This should give you the following output:

INFO Catalog configuration starting...
? Select Streams  [Invoice]
? Select fields from stream: `Invoice`  done (18 selections)
INFO Catalog configuration saved.

Finally, you can now pull data from QuickBooks using the files you’ve just generated, using the following commands:

pip install singer-python==5.3.1

Retrieve the Invoice data from QuickBooks and save it as a CSV file.

tap-quickbooks --config config.json --properties properties.json | target-csv  > state.json

This will create two output files:

• The CSV containing the data from QuickBooks (for example, Invoice-20210605T387782.csv)
• A JSON file, state.json that tells tap-quickbooks what it last synced. This can be used in the future to avoid syncing the same data again.

You have now pulled the required data from Quickbooks and completed your Python QuickBooks integration. The process can now be automated to ensure continuous and automatic sync by wrapping it in a Docker container. You can also check QuickBooks Power BI Integration platform.

Python QuickBooks Integration Step 4: Standardizing the Data

The data extracted using tap-quickbooks will be in the following format:

The data has to be standardized before any useful analysis can be performed on it. This can be done by first loading the data into a Jupyter Notebook. You can start a Jupyter Lab instance by running the following command:

jupyter lab

This will start a Jupyter Lab instance in the current directory and launch the browser.

You will now see tap-quickbooks available under the Notebook sections. Create a new Jupyter Notebook with a suitable name. You can use the gluestick and pandas Python libraries to load and explore the data. The goal here is to tranform the output from tap-quickbooks easily.

import gluestick as gs
import pandas as pd

# Read input data
input_data = gs.read_csv_folder(".", index_cols={'Invoice': 'Id'})

# View the input data
input_df = input_data['Invoice']
input_df.head()

Now that you have the data in a Pandas data frame, you can transform it based on the requirements of your analysis.

invoices = input_df[["CustomerRef__name", "TotalAmt", "Balance", "DueDate"]]
invoices.head()

Conclusion

This article provided you with a step-by-step guide on how you can set up Python QuickBooks integration seamlessly. There are certain limitations associated with this method. This includes the fact that this method is not considered to be suitable for someone with not enough technical knowledge of Python. If the limitations are not a concern to your requirements, then the manual Python QuickBooks integration method is the best option for you, but if it is, then you should consider using automated Data Integration platforms like Hevo.

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence tools, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Pricing details for Hevo can be found here. Give Hevo a try by signing up for the 14-day free trial today.