Quickbooks API & Dashboard
As a finance executive, I’m forever working with numbers, metrics and statistics - constantly gathering, preparing and then shaping them into a form that will have actionable meaning for their intended audiences. Given this, I've always been a proponent for a central source of truth that stores all the key pieces of data that managers/management can rely on without conjecture or bias. One such central store is a management dashboard and the reason for this project.
The Stack
One source of data that I’m exceptionally familiar with is Quickbooks Online (QBO) and according to statistics from Intuit Investor Day 2019, so are over 4.5 million users to a greater or lesser degree. As such, I’ll be working with the Intuit Developer team to connect Quickbooks to our dashboard and I’ll be using Celery along with Rabbit MQ as the broker, to handle the task of refreshing access tokens.
And the usual suspects: Django, Python, CSS, HTML and a tinge of JavaScript and Chart.js.
Throughout this project, we’ll be working with a sandbox company - Majestic Construction. Once you establish an Intuit Developer account, you can create apps and test them against a sandbox company of your creation which is brilliant! Can’t say enough about the Intuit Team.
Engineering
OAuth, or more specifically OAuth2, was the initial engineering challenge however the Intuit Developer Team made it quick business with their SDK which can be found here: Intuit OAuth2 Python Client. From there, coding through the OAuth2 workflow was a breeze having garnered inspiration from the Team’s sample app: Intuit Developer SampleOAuth2.
Once the workflow was coded, it was simply a matter of interacting with the QBO API. I took two approaches for this project: directly working through Intuit’s API documentation and working with a Python 3 library which can be found here: python-quickbooks.
Working with Intuit’s access and refresh token policy took some thought as the update process needed to be outside the normal request/response cycle. After some initial design ideas and testing, I decided on Celery to handle the task(s) and Celerybeat to handle the scheduling of said task(s). There is always a trade-off in these situations, but in the end I went with Celery because its functionality fit and its well supported by a large and diverse community of users/contributors.
Lastly, even though I didn’t set out to build a production-ready application, rather a quasi-proof-of-concept, several architectural decisions hit me in the face while piecing it together; but rather than getting into a discussion of them all, the one that was most obvious was whether to cache the api calls or not. Yes, adding that layer would definitely boost performance but in the spirit of my objective, and time, I left it out.
Under the Hood
Just a quick note before we move forward: I attempt to stay true to the source documentation from whatever framework, library or API that's under discussion so you’ll find such evidence in most of my code commentary.
Celery
Celery will handle the access and refresh token policies, and as such we need to create a refresh function wrapped in a Celery task that addresses these requirements.
To use celery within a Django project, we need to define an instance of the Celery library within our project folder
Then, staying within our project folder, we need to import our app into the __init__.py file. This ensures that the app is loaded when Django starts so that the @shared_task decorator (mentioned shortly) will use it:
Since our Celery tasks will probably live in reusable apps, they can't depend on the project so we can’t directly import the app instance that we created earlier. Therefore, we'll use the @shared_task decorator which allows us to create tasks without having any concrete app instance. We'll place this code within our app :
Celery Beat & RabbitMQ
Celery uses celery beat to schedule periodic tasks to run at regular intervals that are then executed by the available Celery workers. To do so in Django, we need to add the following to our Django settings.py file:
There is a bit going on here and much of it requires a little study of the “math” so I’ve provided some links to various articles in the Resources section below. Since I chose RabbitMQ as the message broker, AMQP is the messaging protocol and as such exposes a url for messages to pass through. CELERY_BROKER_URL is set to localhost for development and we’ll need to adjust that once we lift the app to Heroku. BROKER_POOL_LIMIT, CELERY_ACCEPT_CONTENT and CELERY_TASK_SERIALIZER are well covered in the Celery Documentation.
CELERY_BEAT_SCHEDULE gives the name 'refresh-token' to the task within the context of the schedule, defines 'qboAPP.tasks.refresh' as the task to run and lastly it schedules the task to run every 3480 seconds or every 58 minutes.
Python Quickbooks
As I mentioned earlier, I utilized two approaches to interact with the API. Off the bat, I dove into python-quickbooks simply because of my affinity for Python. I created a services.py file to hold some of the logic, because sifting through the API's response object can be a bit meaty and this helps my views remain lean. With this, we’ll take the Purchase object (refer to python-quickbooks documentation) and grab the attributes we are interested in:
Quickbooks API
The Profit and Loss object as per Intuit’s documentation, was a bit of a beast to decipher at first because I wanted to get to totals by month. Essentially, Intuit mirrored a run-of-the-mill profit and loss statement with its attendant accounts, sub-accounts, their respective totals and subtotals in the form of a dictionary complete with nested dictionaries and nested lists. Once it became clear to me what was happening, it was simply a matter of putting the logic to code in the services.py file:
And in views.py, we’ll take our functions qbo_request_purchases and qbo_request_income and place them in our API call below. Additionally, we need to slice out the "data" and "labels" for our Profit and Loss Chart.js chart:
Let’s see it!
As I mentioned earlier, the app is deployed to Heroku. I could spend some time discussing how I was able to get Django, Celery and RabbitMQ to play nice together on Heroku but frankly, the process was quite smooth and there are plenty of resources on web.
The application can be found here: QBO Dashboard. The username is guest@guest.com and the password is guest. Enjoy.
*UPDATE*
I disconnected the backend due to the constant maintenance requirements and so have instead included screenshots from the application: HOME PAGE, PROFIT & LOSS , PURCHASES.
Resources:
Celery: Introduction to Celery, Celery Beat: Periodic Tasks, RabbitMQ: What is RabbitMQ?, AMQP: AMQP Model Explained
Hold the line!