Building Custom Features Leveraging QuickBooks Data via API Integration

Custom Reporting: Average Monthly Revenue by Customer

Throughout this article, we will develop a software solution designed to provide insights into customer performance by calculating the average monthly revenue generated by each customer. This report enables businesses to identify high-performing clients, uncover trends, and make data-driven decisions to optimize revenue strategies.

The reporting methodology will calculate the average monthly revenue over the entire duration of the customer relationship. The starting point for the calculation will be the month of the first issued invoice, while the endpoint will be the month of the last issued invoice. This approach focuses on providing an average revenue figure across the full timespan of cooperation, rather than per-month insights.

For example, if a customer has two invoices—one issued in January 2024 and the other (the last) in December 2024—the average will be calculated over a 12-month period. The total revenue from the invoices will be summed and then divided by 12 (the total number of months) to determine the average monthly revenue.

GitHub Repository

There is one solution presented in this article and complete implementation of solution shown in this article can be cloned from GitHub repository: https://github.com/Gradient-s-p/custom-quickbooks-report.

Ensuring That Necessary Data Is Available

To build the desired custom report successfully, we need the following data:

• Invoicing data, specifically the invoice date, totals, and the connection to the customer the invoice is issued to.
• Customer data, specifically the first name, last name, and address for B2C services, or the company name and address for B2B services.

Before proceeding with this article, ensure that you have signed up for and logged in to your Intuit Developer account by following the guidelines here.

Invoice Data API

By reviewing the API documentation, we can locate the endpoint for querying invoices, which is documented here. The documentation includes an interactive test feature that allows us to query all invoices. This functionality is demonstrated in the following screenshot:

Sample query is:

select * from Invoice

Result is:

{
 "QueryResponse": {
  "Invoice": [
   {
    "AllowIPNPayment": false,
    "AllowOnlinePayment": false,
    "AllowOnlineCreditCardPayment": false,
    "AllowOnlineACHPayment": false,
    "domain": "QBO",
    "sparse": false,
    "Id": "146",
    "SyncToken": "0",
    "MetaData": {
     "CreateTime": "2025-01-09T01:40:36-08:00",
     "LastModifiedByRef": {
      "value": "9341453764932177"
     },
     "LastUpdatedTime": "2025-01-09T01:40:36-08:00"
    },
    "CustomField": [],
    "DocNumber": "1039",
    "TxnDate": "2025-01-09",
    "CurrencyRef": {
     "value": "EUR",
     "name": "Euro"
    },
    "ExchangeRate": 1.02941,
    "LinkedTxn": [],
    "Line": [
     {
      "Id": "1",
      "LineNum": 1,
      "Description": "Weekly Gardening Service",
      "Amount": 1000,
      "DetailType": "SalesItemLineDetail",
      "SalesItemLineDetail": {
       "ItemRef": {
        "value": "6",
        "name": "Gardening"
       },
       "UnitPrice": 100,
       "Qty": 10,
       "ItemAccountRef": {
        "value": "45",
        "name": "Landscaping Services"
       },
       "TaxCodeRef": {
        "value": "NON"
       }
      }
     },
     {
      "Amount": 1000,
      "DetailType": "SubTotalLineDetail",
      "SubTotalLineDetail": {}
     }
    ],
    "TxnTaxDetail": {
     "TotalTax": 0
    },
    "CustomerRef": {
     "value": "58",
     "name": "Mr Abc Bcd Cde"
    },
    "CustomerMemo": {
     "value": "Thank you for your business and have a great day!"
    },
    "BillAddr": {
     "Id": "96",
     "City": "Berlin",
     "Country": "Germany"
    },
    "ShipAddr": {
     "Id": "96",
     "City": "Berlin",
     "Country": "Germany"
    },
    "FreeFormAddress": false,
    "SalesTermRef": {
     "value": "3",
     "name": "Net 30"
    },
    "DueDate": "2025-02-08",
    "TotalAmt": 1000,
    "HomeTotalAmt": 1029.41,
    "ApplyTaxAfterDiscount": false,
    "PrintStatus": "NeedToPrint",
    "EmailStatus": "NotSet",
    "Balance": 1000,
    "HomeBalance": 1029.41
   },
   // ... other Invoice records
  ],
  "startPosition": 1,
  "maxResults": 33,
  "totalCount": 33
 },
 "time": "2025-01-09T06:12:09.174-08:00"
}

At the end of the response, there are attributes such as startPosition, maxResults, and totalCount. These attributes are useful for pagination when importing all invoices. One possible approach is to use the following query:

select count(*) from Invoice

Result is:

{
 "QueryResponse": {
  "totalCount": 33
 },
 "time": "2025-01-09T06:49:56.354-08:00"
}

After getting the count, we can decide on page size, and execute queries as following:

select * from Invoice maxResults 5 startPosition 1
select * from Invoice maxResults 5 startPosition 6
select * from Invoice maxResults 5 startPosition 11

With these results, it is clear that we can retrieve all the necessary invoice data for our task using the QuickBooks API.

Customer Data API

In the invoice responses, we can locate Customer Reference data, which includes the qualified customer name and its ID in the value attribute. Additional data related to the invoiced service, such as the billing or delivery address, is also provided. For our purposes, this may be sufficient to connect a customer with their address.

It is important to note, however, that the billing and delivery addresses in the invoice response reflect the information assigned to the customer at the time the invoice was created. These addresses may differ from the customer's current address when generating the report discussed in this article.

To obtain up-to-date customer and address information, we should use the Customer Data API, which can be explored here. Querying customer data can also be tested in the following documentation section:

After fetching the necessary invoices, we can use the associated Customer IDs to execute queries and retrieve specific customer data:

select * from Customer Where id IN ('1', '2', '4')

With the result:

{
 "QueryResponse": {
  "Customer": [
   {
    "Taxable": true,
    "BillAddr": {
     "Id": "2",
     "Line1": "4581 Finch St.",
     "City": "Bayshore",
     "CountrySubDivisionCode": "CA",
     "PostalCode": "94326",
     "Lat": "INVALID",
     "Long": "INVALID"
    },
    "ShipAddr": {
     "Id": "2",
     "Line1": "4581 Finch St.",
     "City": "Bayshore",
     "CountrySubDivisionCode": "CA",
     "PostalCode": "94326",
     "Lat": "INVALID",
     "Long": "INVALID"
    },
    "Job": false,
    "BillWithParent": false,
    "Balance": 239,
    "BalanceWithJobs": 239,
    "CurrencyRef": {
     "value": "USD",
     "name": "United States Dollar"
    },
    "PreferredDeliveryMethod": "Print",
    "IsProject": false,
    "ClientEntityId": "0",
    "domain": "QBO",
    "sparse": false,
    "Id": "1",
    "SyncToken": "0",
    "MetaData": {
     "CreateTime": "2024-12-06T16:48:43-08:00",
     "LastUpdatedTime": "2024-12-13T13:39:32-08:00"
    },
    "GivenName": "Amy",
    "FamilyName": "Lauterbach",
    "FullyQualifiedName": "Amy's Bird Sanctuary",
    "CompanyName": "Amy's Bird Sanctuary",
    "DisplayName": "Amy's Bird Sanctuary",
    "PrintOnCheckName": "Amy's Bird Sanctuary",
    "Active": true,
    "V4IDPseudonym": "0020984269d61f380843669fe5bd7044f5ff0d",
    "PrimaryPhone": {
     "FreeFormNumber": "(650) 555-3311"
    },
    "PrimaryEmailAddr": {
     "Address": "Birds@Intuit.com"
    }
   },
   {
    "Taxable": false,
    "BillAddr": {
     "Id": "3",
     "Line1": "12 Ocean Dr.",
     "City": "Half Moon Bay",
     "CountrySubDivisionCode": "CA",
     "PostalCode": "94213",
     "Lat": "37.4307072",
     "Long": "-122.4295234"
    },
    "Job": false,
    "BillWithParent": false,
    "Balance": 85,
    "BalanceWithJobs": 85,
    "CurrencyRef": {
     "value": "USD",
     "name": "United States Dollar"
    },
    "PreferredDeliveryMethod": "Print",
    "IsProject": false,
    "ClientEntityId": "0",
    "domain": "QBO",
    "sparse": false,
    "Id": "2",
    "SyncToken": "0",
    "MetaData": {
     "CreateTime": "2024-12-06T16:49:28-08:00",
     "LastUpdatedTime": "2024-12-13T12:56:01-08:00"
    },
    "GivenName": "Bill",
    "FamilyName": "Lucchini",
    "FullyQualifiedName": "Bill's Windsurf Shop",
    "CompanyName": "Bill's Windsurf Shop",
    "DisplayName": "Bill's Windsurf Shop",
    "PrintOnCheckName": "Bill's Windsurf Shop",
    "Active": true,
    "V4IDPseudonym": "0020987f7bbe58b72a4343bc45437a189f23f2",
    "PrimaryPhone": {
     "FreeFormNumber": "(415) 444-6538"
    },
    "PrimaryEmailAddr": {
     "Address": "Surf@Intuit.com"
    }
   },
   {
    "Taxable": false,
    "BillAddr": {
     "Id": "5",
     "Line1": "321 Channing",
     "City": "Palo Alto",
     "CountrySubDivisionCode": "CA",
     "PostalCode": "94303",
     "Lat": "37.443231",
     "Long": "-122.1561846"
    },
    "Job": false,
    "BillWithParent": false,
    "Balance": 0,
    "BalanceWithJobs": 0,
    "CurrencyRef": {
     "value": "USD",
     "name": "United States Dollar"
    },
    "PreferredDeliveryMethod": "Print",
    "IsProject": false,
    "ClientEntityId": "0",
    "domain": "QBO",
    "sparse": false,
    "Id": "4",
    "SyncToken": "0",
    "MetaData": {
     "CreateTime": "2024-12-06T16:52:08-08:00",
     "LastUpdatedTime": "2024-12-06T16:52:08-08:00"
    },
    "GivenName": "Diego",
    "FamilyName": "Rodriguez",
    "FullyQualifiedName": "Diego Rodriguez",
    "DisplayName": "Diego Rodriguez",
    "PrintOnCheckName": "Diego Rodriguez",
    "Active": true,
    "V4IDPseudonym": "002098bfad66f678f04c12815ec338f55556e5",
    "PrimaryPhone": {
     "FreeFormNumber": "(650) 555-4477"
    },
    "PrimaryEmailAddr": {
     "Address": "Diego@Rodriguez.com"
    }
   }
  ],
  "startPosition": 1,
  "maxResults": 3
 },
 "time": "2025-01-10T03:18:11.010-08:00"
}

The up-to-date billing address is now available, and the presence of the companyName field allows us to distinguish between business customers (B2B) and individual customers (B2C). With this data in hand, all prerequisites for building the custom reporting tool are fulfilled.

Developer Application and Authorization

In this article, we will build a command-line utility to generate the desired report. Instead of fully implementing OAuth2, as required by QuickBooks, we will leverage their available playground for authorization.

For a complete authorization process, we would need a web application capable of handling authorization requests, and managing access and refresh tokens. This is likely a topic for one of the future articles.

To ensure successful operation, you need to create your own Developer Application and use the QuickBooks playground to retrieve an access token, which will be required later. Guidelines on how to complete this process are provided here.

Once everything is correctly configured, you should be able to retrieve an access token, as shown in the following screenshot.

Structuring Code and Making the First API Call

In the empty source code directory, we will create a directory named "customersreport", where all incorporated classes will be placed. This directory will include the following three classes:

• QuickBooksClient: A class responsible for handling all communication with the QuickBooks API, including data fetching.
• DataProcessor: A class that processes and maps data necessary for building the final report.
• PdfReport: A class dedicated to creating the final PDF report based on the provided data.

In the root of our project, we will prepare the following files:

• main.py: The entry point of our program.
• .env and .env.template: Files for storing environment variables.

Additionally, it is recommended to access the repository shared in this article (or here) to download the requirements.txt file, which contains all the dependencies needed for the project. Save this file in the root directory of the project.

Install all dependencies by running the following command in the root directory of the project:

pip install -r requirements.txt

Since every developer and running environment may have different settings, the .env file is included in .gitignore to ensure it is not shared between instances. Instead, the .env.template file is shared and serves as a reference, listing all the settings required to run the program.

The contents of these files are as follows:

.env.template

QUICKBOOKS_BASE_URL=
COMPANY_ID=
ACCESS_TOKEN=

.env

QUICKBOOKS_BASE_URL=https://sandbox-quickbooks.api.intuit.com
QUICKBOOKS_COMPANY_ID=9341453764932151
QUICKBOOKS_ACCESS_TOKEN=eyJlbmMiOiJBMTI4Q0JDLUhTMjU2IiwiYWxnIjoiZGlyIn0..IqlJruvUM5jejkARctj...

(This file contains the actual values, which are unique to each environment and should not be shared.)

Short description of environment variables:

• QUICKBOOKS_BASE_URL: The QuickBooks API URL (sandbox and production URLs are different).
• QUICKBOOKS_COMPANY_ID: The Company ID, often referred to as the Realm ID in QuickBooks documentation.
• QUICKBOOKS_ACCESS_TOKEN: The token used to authorize access to the necessary endpoints in the QuickBooks API.

To make the initial API request, we prepare the following class:

customersreport/quickbooks_client.py

import requests


class QuickbooksClient:
    def __init__(self, base_url, company_id, access_token):
        self.access_token = access_token
        self.base_url = base_url
        self.company_id = company_id

    def load_invoices(self):
        url = f"{self.base_url}/v3/company/{self.company_id}/query"

        try:
            response = requests.get(
                url,
                headers={
                    'Accept': 'application/json',
                    'Authorization': f'Bearer {self.access_token}'
                },
                params={
                    'query': 'select * from Invoice'
                }
            )
            return response.json()

        except requests.exceptions.RequestException as e:
            print('Error:', e)
            return None

To invoke this class and log the results of fetching invoices, we prepare the main.py file as the entry point of our project.

main.py

import os
from dotenv import load_dotenv
from customersreport.quickbooks_client import QuickbooksClient

load_dotenv()


def main():
    quickbooks_client = QuickbooksClient(
        base_url=os.environ.get("QUICKBOOKS_BASE_URL"),
        company_id=os.environ.get("QUICKBOOKS_COMPANY_ID"),
        access_token=os.environ.get("QUICKBOOKS_ACCESS_TOKEN")
    )
    print(quickbooks_client.load_invoices())


if __name__ == '__main__':
    main()

Running this project with the following command, if everything is correctly configured, should log the invoices issued by the company:

python main.py

# On some operating systems, the Python interpreter binary might be named python3, so the command should be as follows:
python3 main.py

Resulting invoices are:

python3 main.py 
{'QueryResponse': {'Invoice': [{'AllowIPNPayment': False, 'AllowOnlinePayment': False, 'AllowOnlineCreditCardPayment': False, 'AllowOnlineACHPayment': False, 'domain': 'QBO', 'sparse': False, 'Id': '146', 'SyncToken': '0', 'MetaData': {'CreateTime': '2025-01-09T01:40:36-08:00', 'LastUpdatedTime': '2025-01-09T01:40:36-08:00'}, 'CustomField': [], 'DocNumber': '1039', 'TxnDate': '2025-01-09', 'CurrencyRef': {'value': 'EUR', 'name': 'Euro'}, 'ExchangeRate': 1.02941, 'LinkedTxn': [], 'Line': [{'Id': '1', 'LineNum': 1, 'Description': 'Weekly Gardening Service', 'Amount': 1000.0, 'DetailType': 'SalesItemLineDetail', 'SalesItemLineDetail': {'ItemRef': {'value': '6', 'name': 'Gardening'}, 'UnitPrice': 100, 'Qty': 10, 'TaxCodeRef': {'value': 'NON'}}}, {'Amount': 1000.0, 'DetailType': 'SubTotalLineDetail', 'SubTotalLineDetail': {}}], 'TxnTaxDetail': {'TotalTax': 0}, 'CustomerRef': {'value': '58', 'name': 'Mr Abc Bcd Cde'}, 'CustomerMemo': {'value': 'Thank you for your business and have a great day!'}, 'BillAddr': {'Id': '96', 'City': 'Berlin', 'Country': 'Germany'}, 'ShipAddr': {'Id': '96', 'City': 'Berlin', 'Country': 'Germany'}, 'SalesTermRef': {'value': '3', 'name': 'Net 30'}, 'DueDate': '2025-02-08', 'TotalAmt': 1000.0, 'HomeTotalAmt': 1029.41, 'ApplyTaxAfterDiscount': False, 'PrintStatus...

Completing Project File Structure

Having built the necessary modules to successfully communicate with the API, we can now focus on the specific goal of this project: creating a customer report with customers sorted by their monthly average revenue contribution to the company. The report will be generated as a PDF and saved to the filesystem.

To achieve this, the following two classes are created:

• PdfReportBuilder: This class generates the final PDF report based on the provided data.
• CustomersReport: This class uses the QuickBooksClient to retrieve the necessary data, processes and formats it, and prepares it for the PdfReportBuilder.

The basic code stubs for these classes are as follows:

customersreport/pdf_report_builder.py

import os
from fpdf import FPDF


class PdfReportBuilder:
    def create_report(self, file_name, data):
        cwd = os.getcwd()
        report_path = os.path.join(cwd, "reports")

        fpdf = FPDF()
        fpdf.add_page()
        fpdf.set_font("Arial", size=12)
        fpdf.text(25, 25, "Test Content!")
        fpdf.output(f"{report_path}/{file_name}.pdf")

customersreport/customers_report.py

import os

from customersreport.pdf_report_builder import PdfReportBuilder
from customersreport.quickbooks_client import QuickbooksClient


class CustomersReport:
    def __init__(self):
        self.quickbooks_client = QuickbooksClient(
            base_url=os.environ.get("QUICKBOOKS_BASE_URL"),
            company_id=os.environ.get("QUICKBOOKS_COMPANY_ID"),
            access_token=os.environ.get("QUICKBOOKS_ACCESS_TOKEN")
        )

        self.pdf_report_builder = PdfReportBuilder()

    def get_customers_report(self):
        self.pdf_report_builder.create_report("Test-File", [])

And to ensure that the CustomersReport class serves as the entry point, the content of main.py has also been updated.

main.py

from dotenv import load_dotenv

from customersreport.customers_report import CustomersReport

load_dotenv()


def main():
    customers_report = CustomersReport()
    customers_report.get_customers_report()


if __name__ == '__main__':
    main()

In line with our intent to save reports on the file system, we should create a new directory named "reports" in the root of the project. This directory should be included in .gitignore, as it will be used to store the resulting PDF reports that should not be tracked trough git with other environments.

We should save a .gitkeep file inside the reports directory, and this file should be tracked by Git. The purpose of this is to ensure that the reports directory is delivered through Git, but without any actual report files.

.gitignore

.idea
.env
reports/*
!reports/.gitkeep

The finalized code structure for this project is outlined in the dependency graph shown in the following illustration.

Customer's Report

Having API tested, development structure defined, it is time for final implementation of customer's report data. By our plan, we need to extend our QuickbooksClient class, to perform all necessary fetching API calls.

customersreport/quickbooks_client.py

import requests


class QuickbooksClient:
    def __init__(self, base_url, company_id, access_token):
        self.access_token = access_token
        self.base_url = base_url
        self.company_id = company_id

    def get_invoices_count(self):
        url = f"{self.base_url}/v3/company/{self.company_id}/query"
        params = {
            'query': 'select count(*) from Invoice'
        }

        return self.do_request(url, params)

    def load_invoices(self, page_size, start_position):
        url = f"{self.base_url}/v3/company/{self.company_id}/query"
        params = {
            'query': f"select * from Invoice maxResults {page_size} startPosition {start_position}"
        }

        return self.do_request(url, params)

    def get_customer_data(self, customer_ids):
        url = f"{self.base_url}/v3/company/{self.company_id}/query"

        ids_query = "(" + ", ".join([f"'{customer_id}'" for customer_id in customer_ids]) + ")"

        params = {
            "query": f"select * from Customer where id IN {ids_query}"
        }

        return self.do_request(url, params)

    def do_request(self, url, params):
        try:
            response = requests.get(
                url,
                headers={
                    'Accept': 'application/json',
                    'Authorization': f'Bearer {self.access_token}'
                },
                params=params
            )
            response_json = response.json()
            return response_json

        except requests.exceptions.RequestException as e:
            print('Error:', e)
            return None

With QuickbooksClient ready, implementation of data processing layer is in place. To remind, this class is supposed to prepare data for PDF report.

customersreport/customers_report.py

import datetime
import os

from customersreport.pdf_report_builder import PdfReportBuilder
from customersreport.quickbooks_client import QuickbooksClient


class CustomersReport:
    def __init__(self):
        self.quickbooks_client = QuickbooksClient(
            base_url=os.environ.get("QUICKBOOKS_BASE_URL"),
            company_id=os.environ.get("QUICKBOOKS_COMPANY_ID"),
            access_token=os.environ.get("QUICKBOOKS_ACCESS_TOKEN")
        )

        self.pdf_report_builder = PdfReportBuilder()

    def get_customers_report(self):
        report_data = self.prepare_customer_report_data()
        self.pdf_report_builder.create_report(datetime.datetime.today(), report_data)

    def prepare_customer_report_data(self):
        invoices = self.get_all_invoices()
        customers = self.group_invoices_by_customers(invoices)
        self.calculate_customer_revenue_averages(customers)
        self.update_customers_informative_data(customers)
        report_data = self.map_to_report_data(customers)

        return report_data

    def get_all_invoices(self):
        invoices = []

        invoice_count_response = self.quickbooks_client.get_invoices_count()
        invoice_count = invoice_count_response["QueryResponse"]["totalCount"]

        page_size = 50
        for i in range(1, invoice_count, page_size):
            invoices_page_response = self.quickbooks_client.load_invoices(page_size, i)
            invoices += invoices_page_response["QueryResponse"]["Invoice"]

        return invoices

    def group_invoices_by_customers(self, invoices):
        customers = {}

        for invoice in invoices:
            customer_id = invoice["CustomerRef"]["value"]
            if customer_id not in customers:
                customers[customer_id] = {
                    "id": customer_id,
                    "invoices": []
                }

            customers[customer_id]["invoices"].append(invoice)

        return [customers[key] for key in customers.keys()]

    def diff_month(self, d1, d2):
        return (d1.year - d2.year) * 12 + d1.month - d2.month

    def calculate_customer_revenue_averages(self, customers):
        for customer in customers:
            revenue = 0
            invoices = customer["invoices"]

            earliest_invoice_date = datetime.date.fromisoformat(invoices[0]["DueDate"])
            latest_invoice_date = datetime.date.fromisoformat(invoices[0]["DueDate"])

            for invoice in invoices:
                invoice_date = datetime.date.fromisoformat(invoice["DueDate"])

                if "HomeTotalAmt" in invoice:
                    revenue += invoice["HomeTotalAmt"]
                else:
                    revenue += invoice["TotalAmt"]

                if earliest_invoice_date > invoice_date:
                    earliest_invoice_date = invoice_date
                elif latest_invoice_date < invoice_date:
                    latest_invoice_date = invoice_date

            cooperation_months = self.diff_month(latest_invoice_date, earliest_invoice_date) + 1
            customer["averageRevenue"] = revenue / cooperation_months

        customers.sort(key=lambda cust: cust["averageRevenue"], reverse=True)

    def update_customers_informative_data(self, customers):
        customer_ids = [customer["id"] for customer in customers]
        customer_api_data_response = self.quickbooks_client.get_customer_data(customer_ids)
        customer_api_data = customer_api_data_response["QueryResponse"]["Customer"]

        for customer in customers:
            customer["details"] = {}

            customer_info = next(c for c in customer_api_data if c["Id"] == customer["id"])

            if "CompanyName" in customer_info:
                customer["details"]["isCompany"] = True
            else:
                customer["details"]["isCompany"] = False

            customer["details"]["name"] = customer_info["FullyQualifiedName"]
            customer["details"]["address"] = customer_info["BillAddr"]

    def map_to_report_data(self, customers):
        return [self.map_single_customer(customer) for customer in customers]

    def map_single_customer(self, customer):
        report_item = {
            "name": customer["details"]["name"],
            "address": customer["details"]["address"],
            "is_company": customer["details"]["isCompany"],
            "revenue_average": customer["averageRevenue"],
            "invoices_count": len(customer["invoices"])
        }

        return report_item

As the result of processing, report data is as follows:

[{
    'name': 'Cool Cars',
    'address': {
        'Id': '4',
        'Line1': '65 Ocean Dr.',
        'City': 'Half Moon Bay',
        'CountrySubDivisionCode': 'CA',
        'PostalCode': '94213',
        'Lat': '37.4300318',
        'Long': '-122.4336537'
    },
    'is_company': True,
    'revenue_average': 2369.52,
    'invoices_count': 1
}, {
    'name': 'Mr Abc Bcd Cde',
    'address': {'Id': '96', 'City': 'Berlin', 'Country': 'Germany'},
    'is_company': True,
    'revenue_average': 1029.41,
    'invoices_count': 1
}, {
    'name': 'Paulsen Medical Supplies',
    'address': {
        'Id': '18',
        'Line1': '900 Main St.',
        'City': 'Middlefield',
        'CountrySubDivisionCode': 'CA',
        'PostalCode': '94303',
        'Lat': '37.445013',
        'Long': '-122.1391443'
    },
    'is_company': True,
    'revenue_average': 954.75,
    'invoices_count': 1
}, {
    'name': 'John Melton',
    'address': {
        'Id': '13',
        'Line1': '85 Pine St.',
        'City': 'Menlo Park',
        'CountrySubDivisionCode': 'CA',
        'PostalCode': '94304',
        'Lat': '37.4451342',
        'Long': '-122.1409626'
    },
    'is_company': False,
    'revenue_average': 750.0,
    'invoices_count': 1
},
...
{
    'name': 'Travis Waldron',
    'address': {
        'Id': '27',
        'Line1': '78 First St.',
        'City': 'Monlo Park',
        'CountrySubDivisionCode': 'CA',
        'PostalCode': '94304',
        'Lat': '37.4585825',
        'Long': '-122.1352789'
    },
    'is_company': False,
    'revenue_average': 599.27,
    'invoices_count': 3
}, {
    'name': 'Dukes Basketball Camp',
    'address': {
        'Id': '6',
        'Line1': '25 Court St.',
        'City': 'Tucson',
        'CountrySubDivisionCode': 'AZ',
        'PostalCode': '85719',
        'Lat': '32.2841116',
        'Long': '-110.9744298'
    },
    'is_company': True,
    'revenue_average': 490.1,
    'invoices_count': 2
},,
...
{
    'name': 'Freeman Sporting Goods:55 Twin Lane',
    'address': {
        'Id': '9',
        'Line1': '370 Easy St.',
        'City': 'Middlefield',
        'CountrySubDivisionCode': 'CA',
        'PostalCode': '94482',
        'Lat': '37.4031672',
        'Long': '-122.0642815'
    },
    'is_company': True,
    'revenue_average': 73.8,
    'invoices_count': 3
}]

PDF Report Builder

As the final step, we need to implement the PDF Report Builder so that we can successfully build a PDF document with the results of our calculations.

customersreport/pdf_report_builder.py

import os
from fpdf import FPDF


class PdfReportBuilder:
    def create_report(self, file_name, data):
        cwd = os.getcwd()
        report_path = os.path.join(cwd, "reports")

        fpdf = FPDF(orientation='L')
        fpdf.add_page()
        fpdf.set_font("Arial", size=12)

        cell_height = fpdf.font_size * 3
        fpdf.cell(fpdf.w, cell_height,
                  txt="Customers Report by Monthly Average Revenue")
        fpdf.ln(cell_height)

        self.build_table(fpdf, data)

        fpdf.output(f"{report_path}/{file_name}.pdf")

    def build_table(self, fpdf, data):
        records = [
            ['Name', 'Address', 'Company?', 'Monthly Revenue Average', 'Invoices']
        ]

        for item in data:
            record = []
            record.append(item['name'])
            address = item['address']
            record.append(self.build_address_line(address))
            record.append('Yes' if item['is_company'] else 'No')
            record.append(f"{item['revenue_average']:.2f}")
            record.append(f"{item['invoices_count']}")

            for i, val in enumerate(record):
                record[i] = f"{val[0: 20]}..." if len(val) > 23 else val

            records.append(record)

        col_width = fpdf.w / (len(records[0]) + 0.5)
        row_height = fpdf.font_size
        spacing = 3

        for row in records:
            for item in row:
                fpdf.cell(col_width, row_height * spacing,
                         txt=item, border=1, )
            fpdf.ln(row_height * spacing)

    def build_address_line(self, address):
        keys = ['Line1', 'City', 'PostalCode', 'CountrySubDivisionCode']

        result = []
        for key in keys:
            if key in address:
                result.append(address[key])

        return ', '.join(result)

Resulting report from this code is:

Get an Offer

Contact Us or Schedule a Meeting with us to get an offer for our development & consulting services regarding your current or next QuickBooks or Python project.