User story: accounts receivable aging

The story

Every Monday the controller asks for the AR aging. Priya wants it ready by 9 AM.

The script

import pandas as pd
from datetime import date

ar = pd.read_excel("ar_open.xlsx")        # cols: customer, invoice_no, due_date, balance

today = pd.Timestamp(date.today())
ar["due_date"] = pd.to_datetime(ar["due_date"])
ar["days_overdue"] = (today - ar["due_date"]).dt.days.clip(lower=0)

bins   = [-1, 0, 30, 60, 90, 99999]
labels = ["Current", "1-30", "31-60", "61-90", "90+"]
ar["bucket"] = pd.cut(ar["days_overdue"], bins=bins, labels=labels)

pivot = ar.pivot_table(index="customer", columns="bucket",
                        values="balance", aggfunc="sum",
                        fill_value=0, observed=True)
pivot["Total"] = pivot.sum(axis=1)
pivot = pivot.sort_values("Total", ascending=False)

pivot.to_excel("ar_aging.xlsx")

Highlight worst offenders

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

wb = load_workbook("ar_aging.xlsx")
ws = wb.active

red = PatternFill("solid", fgColor="FECACA")
yellow = PatternFill("solid", fgColor="FEF3C7")

for row in ws.iter_rows(min_row=2):
    over90 = row[5].value or 0   # column F = "90+"
    over60 = row[4].value or 0
    if over90 > 0:
        for cell in row: cell.fill = red
    elif over60 > 0:
        for cell in row: cell.fill = yellow

wb.save("ar_aging.xlsx")