Skip to main content

AR ageing in Python — ageing buckets and DSO with pandas

To build an accounts receivable ageing in Python, load the invoices, receipts and customers into pandas, take what is owed at a date (invoiced to date less received to date), find the invoices still open, give each a due date from the customer's payment terms, and group them by days past due. The 45 lines of pandas below do this on a DATA DARE receivables practice set — a manufacturer with sixty trade customers — and the results agree with the answers in the set's Question Book.

The sets live now

The steps

  1. Load the invoices, the receipts and the customers. In this set they are sheets of one Excel workbook.
  2. Take what is owed at the date: everything invoiced up to it, less everything received up to it.
  3. Find the open invoices — those with no receipt by the date — and give each a due date: the invoice date plus the customer's payment terms.
  4. Group the open invoices by days past due: not yet due, 1-30, 31-60, 61-90 and over 90. That is the ageing.
  5. Work out DSO from what is owed and the last three months of sales, and measure how many days customers really take to pay.

The code

Download the L1 receivables set (DD-ARC-001) and run this in the folder that holds its Excel workbook. It needs pandas and openpyxl (pip install pandas openpyxl). Change as_at to age the book at any other date.

Python (pandas)
import numpy as np
import pandas as pd

pd.options.display.float_format = "{:,.2f}".format
workbook = "DD-ARC-001_Receivables.xlsx"
as_at = pd.Timestamp("2025-12-31")

invoices  = pd.read_excel(workbook, sheet_name="FactInvoices")
receipts  = pd.read_excel(workbook, sheet_name="FactReceipts")
customers = pd.read_excel(workbook, sheet_name="DimCustomer")

# 1. What customers owe at the date: invoiced up to it, less received up to it.
inv = invoices[invoices["InvoiceDate"] <= as_at]
rec = receipts[receipts["ReceiptDate"] <= as_at]
owed = inv["Amount"].sum() - rec["Amount"].sum()
print(f"Owed at {as_at:%d %b %Y}: {owed:,.2f}")

# 2. Open invoices: no receipt against them by the date.
#    Each one falls due on its invoice date plus the customer's payment terms.
open_inv = inv[~inv["InvoiceNumber"].isin(rec["InvoiceNumber"])]
open_inv = open_inv.merge(customers[["CustomerCode", "PaymentTermsDays"]], on="CustomerCode")
terms = pd.to_timedelta(open_inv["PaymentTermsDays"], unit="D")
open_inv["DaysPastDue"] = (as_at - (open_inv["InvoiceDate"] + terms)).dt.days

# 3. The ageing table: not yet due, then 1-30, 31-60, 61-90 and over 90 days past due.
edges = [-np.inf, 0, 30, 60, 90, np.inf]
labels = ["Not yet due", "1-30", "31-60", "61-90", "Over 90"]
open_inv["Bucket"] = pd.cut(open_inv["DaysPastDue"], edges, labels=labels)
print(open_inv.groupby("Bucket", observed=False)["Amount"].agg(["count", "sum"]), "\n")
overdue = open_inv.loc[open_inv["DaysPastDue"] > 0, "Amount"].sum()
print(f"Overdue: {overdue:,.2f}  ({overdue / owed:.2%} of what is owed)")

# 4. DSO: what is owed / sales of the last three months x the days in those three months.
start = as_at - pd.DateOffset(months=3)
sales_3m = inv.loc[inv["InvoiceDate"] > start, "Amount"].sum()
print(f"DSO: {owed / sales_3m * (as_at - start).days:.2f} days")

# 5. How fast customers really pay: the year's receipts against their invoice dates.
r25 = receipts[receipts["ReceiptDate"].dt.year == 2025]
r25 = r25.merge(invoices[["InvoiceNumber", "InvoiceDate"]], on="InvoiceNumber")
r25 = r25.merge(customers[["CustomerCode", "PaymentTermsDays"]], on="CustomerCode")
r25["Days"] = (r25["ReceiptDate"] - r25["InvoiceDate"]).dt.days
late = r25["Days"] > r25["PaymentTermsDays"] + 30
print(f"Average days to get paid in 2025: {r25['Days'].mean():.2f}")
print(f"Paid more than 30 days after the due date: {late.mean():.2%} of receipts")

What it prints

At 31 December 2025, exactly as the script prints it:

Output
Owed at 31 Dec 2025: 11,897,817.94
             count           sum
Bucket
Not yet due    797 10,081,234.28
1-30            85  1,205,961.86
31-60           12    143,293.28
61-90            2     24,056.34
Over 90         38    443,272.18

Overdue: 1,816,583.66  (15.27% of what is owed)
DSO: 60.84 days
Average days to get paid in 2025: 62.54
Paid more than 30 days after the due date: 10.99% of receipts

How to read the result

  • Customers owed 11,897,817.94 on 31 December 2025. The ageing adds up to exactly that: 10,081,234.28 not yet due and 1,816,583.66 overdue.
  • 15.27% of the book is overdue, and 443,272.18 across 38 invoices is more than 90 days past due — the amount most at risk.
  • DSO is 60.84 days: at the pace of the last three months of sales, the book holds about two months of sales.
  • Customers took 62.54 days on average to pay in 2025, and 10.99% of receipts came more than 30 days after the due date.
  • What is owed, the overdue amount and share, the over-90 bucket, DSO, days to get paid and the late share are all questions in the set, so each figure can be checked in its Question Book.

Mistakes this data will catch

  • Ageing from the invoice date instead of the due date. A customer on 90-day terms is not overdue at 45 days.
  • Counting receipts dated after the age-at date. A payment received on 3 January does not reduce what was owed on 31 December.
  • Taking a full year of sales for DSO at a month-end. Use the three months ending on the date and their real number of days.
  • Putting zero days in the wrong bucket. An invoice due today is not overdue, so the first bucket edge is 0.

The next levels

The L2 set (DD-ARC-002) comes as CSV, JSON and Parquet files and adds disputes, remittance advice to match against receipts, the general-ledger control account and collection targets. The same steps carry over; matching the remittances is the new part.

Python or Power BI

Python suits the month-end ageing that goes to the credit team as a file. Power BI suits the collections dashboard, where the age-at date is a slicer and every customer can be drilled into.

The course

  • Basic Python Course Free recording Basic Python takes you from never having written a line of code to scripts that do real work on your own files. You will cover the language itself, then move quickly to the part th...
  • Python for Finance — Automation & Controls Coming soon Python for Finance — Automation & Controls is about the monthly tasks that eat a week and shouldn't. You will automate reconciliations, build controls that check data instead of tr...
  • Python for Finance — Advanced Analytics & Reporting Coming soon Python for Finance — Advanced Analytics & Reporting is where the scripts stop being utilities and start producing analysis. You will work with datasets larger than Excel will hold,...
  • Python + AI for Finance Workflows Coming soon Python + AI for Finance Workflows is about putting a language model inside a process, not chatting with one. You will build workflows that read documents, extract what matters, che...
  • Data Transformation in Python — At Scale Free recording One export of 6.18 million dirty rows, cleaned in Python and reconciled to a total you can defend. For Power Query users who have never written Python. Amounts stored as text, thre...

Data Drill

Updated 16 Nov 2026 · findi360