Data Automation & Cleaning
Beginner
Automated Accounts Receivable (A/R) & Payable Aging Bucket Report (0–30, 31–60, 61–90, 90+ Days) in Python
Categorize unpaid customer invoices into 0–30, 31–60, 61–90, and 90+ Days Past Due aging buckets with customer-wise pivot summaries in Python Pandas.
#pandas#numpy
Problem Statement
Finance controllers and MSME owners manually update Excel VLOOKUPs every Monday to track overdue debtor invoices and bad-debt provision buckets. This script generates an instant aging pivot table.
Formula / Core Logic
Overdue_Days = (Report_Date - Invoice_Due_Date).days
Bins invoices via pd.cut() into ['Current', '1-30 Days', '31-60 Days', '61-90 Days', '90+ Days'].
Installation
pip install pandas numpy
accounts-receivable-aging-bucket-report-0-30-60-90-python.py
import pandas as pd
def generate_ar_aging_summary(df, report_date='2026-11-01'):
# Expected cols: Customer, Invoice_ID, Due_Date, Balance_Due
df = df.copy()
ref_dt = pd.to_datetime(report_date)
df['Due_Date'] = pd.to_datetime(df['Due_Date'])
df['Days_Past_Due'] = (ref_dt - df['Due_Date']).dt.days
bins = [-99999, 0, 30, 60, 90, 999999]
labels = ['0_Not_Due', '1_30_Days', '31_60_Days', '61_90_Days', '90_Plus_Days']
df['Aging_Bucket'] = pd.cut(df['Days_Past_Due'], bins=bins, labels=labels)
pivot = pd.pivot_table(df, values='Balance_Due', index='Customer', columns='Aging_Bucket', aggfunc='sum', fill_value=0.0, observed=False)
pivot['Total_Receivable'] = pivot.sum(axis=1)
return pivot
Get the Complete 100+ Python Quant & Automation Bundle
Instant lifetime access to all production-ready scripts, valuation models, and trading bots ($19).
Buy Full Bundle on Gumroad ($19) →Frequently Asked Questions
Q: Can this aging report also calculate debtor drawing power (DP) for bank CC limits?
Yes! Sum the `0_Not_Due` to `61_90_Days` columns and exclude `90_Plus_Days` (since banks exclude debtors >90 days when computing Drawing Power).