Invoice Automation: Excel to PDF Invoices With Python

What invoice automation means for a small business

Invoice automation is any setup where the repeating parts of invoicing happen without manual typing: pulling line items, calculating totals and tax, creating the PDF, and later sending and tracking it. You do not need expensive software to start. One spreadsheet and one script cover the creation step, which is where most of the time goes.

The workflow in four steps

1. One sheet of lines. Make a sheet called Invoices with the columns Invoice, Client, Description, Qty and Price. Repeat the invoice number for every line of the same invoice.
2. Run the script. It groups lines by invoice number, calculates amounts, adds tax and writes one PDF per invoice.
3. Check the totals. Open one PDF and compare it with the sheet.
4. Send and track. Email the PDFs and record payment status in a tracker such as our bill tracker template.

Step 1: the Excel layout

Save it as invoices.xlsx in the same folder as the script.

Step 2: the Python script

Install the two libraries once with pip install openpyxl reportlab, then save this as invoices.py:

from openpyxl import load_workbook
from reportlab.lib.pagesizes import A4
from reportlab.pdfgen import canvas

def read_invoices(path):
    wb = load_workbook(path, data_only=True)
    ws = wb["Invoices"]
    invoices = {}
    for no, client, desc, qty, price in ws.iter_rows(min_row=2, values_only=True):
        if not no:
            continue
        inv = invoices.setdefault(no, {"client": client, "lines": []})
        inv["lines"].append((desc, qty, price))
    return invoices

def make_pdf(no, inv, tax_rate=0.0):
    c = canvas.Canvas(f"{no}.pdf", pagesize=A4)
    w, h = A4
    y = h - 60
    c.setFont("Helvetica-Bold", 18); c.drawString(50, y, f"Invoice {no}")
    y -= 24; c.setFont("Helvetica", 11); c.drawString(50, y, f"Bill to: {inv['client']}")
    y -= 36; c.setFont("Helvetica-Bold", 10)
    for x, t in ((50, "Description"), (330, "Qty"), (390, "Price"), (470, "Amount")):
        c.drawString(x, y, t)
    c.setFont("Helvetica", 10)
    subtotal = 0
    for desc, qty, price in inv["lines"]:
        y -= 18
        amount = qty * price
        subtotal += amount
        c.drawString(50, y, str(desc)); c.drawString(330, y, str(qty))
        c.drawString(390, y, f"{price:,.2f}"); c.drawString(470, y, f"{amount:,.2f}")
    tax = subtotal * tax_rate
    y -= 30; c.drawString(390, y, "Subtotal"); c.drawString(470, y, f"{subtotal:,.2f}")
    y -= 16; c.drawString(390, y, "Tax"); c.drawString(470, y, f"{tax:,.2f}")
    y -= 18; c.setFont("Helvetica-Bold", 11); c.drawString(390, y, "Total"); c.drawString(470, y, f"{subtotal + tax:,.2f}")
    c.save()
    return subtotal + tax

if __name__ == "__main__":
    for no, inv in read_invoices("invoices.xlsx").items():
        total = make_pdf(no, inv, tax_rate=0.08)
        print(no, inv["client"], f"{total:,.2f}")

Run python invoices.py. With the sample sheet and an 8% tax rate it prints INV-001 Acme Ltd 1,490.40 and INV-002 Beta LLC 972.00, which you can confirm by hand: (1,200 + 180) x 1.08 = 1,490.40 and 900 x 1.08 = 972.00. Change tax_rate to your own rate, or 0 if you do not charge tax.

Step 3 and beyond: send, track and remind

• Email: Python’s built-in smtplib can attach each PDF, or you can send them from your normal mail app.
• Payment tracking: add Status and Paid date columns to the sheet and use a filter to see who owes you.
• Recurring invoices: copy last month’s lines, change the invoice number and run the script again.
• Online payments: add a payment link to the PDF text if you use a payment provider.

When to use a template or software instead

If you send fewer than ten invoices a month, a good template is faster than any script. Our free invoice template for Excel does the totals for you. Move to accounting software when you need tax reports, bank feeds or automatic reminders. If you want a custom flow that reads your orders and produces invoices automatically, we build these.

Frequently asked questions

More free tools: All free tools · Invoice template for Excel · Bill tracker template · Free Excel templates