How to Automate Monthly Financial Report Generation

Monthly financial reports should not take a week to compile. I built a system to automate monthly financial report generation that pulls data from your accounting system, calculates P&L and balance sheet figures, and produces formatted reports ready for review.

Close the books. Generate the report. Send it to stakeholders. All automated.

What You Need Before Starting

• Python 3.8+
• Categorized transaction data (from system 351)
• Account balances database
• Report template

Step 1: Pull Monthly Financial Data

import sqlite3
from datetime import datetime

def get_monthly_financials(conn, month):
    income = conn.execute("""
        SELECT category, SUM(amount) FROM transactions
        WHERE strftime('%Y-%m', date) = ? AND amount > 0
        GROUP BY category ORDER BY SUM(amount) DESC
    """, (month,)).fetchall()

    expenses = conn.execute("""
        SELECT category, SUM(ABS(amount)) FROM transactions
        WHERE strftime('%Y-%m', date) = ? AND amount < 0
        GROUP BY category ORDER BY SUM(ABS(amount)) DESC
    """, (month,)).fetchall()

    return {
        "income": [{"category": r[0], "amount": r[1]} for r in income],
        "expenses": [{"category": r[0], "amount": r[1]} for r in expenses],
        "total_income": sum(r[1] for r in income),
        "total_expenses": sum(r[1] for r in expenses)
    }

Step 2: Calculate Key Metrics

def calculate_metrics(financials, prior_month_data=None):
    net_income = financials["total_income"] - financials["total_expenses"]
    margin = (net_income / financials["total_income"] * 100) if financials["total_income"] else 0

    metrics = {
        "total_revenue": financials["total_income"],
        "total_expenses": financials["total_expenses"],
        "net_income": net_income,
        "profit_margin": round(margin, 1)
    }

    if prior_month_data:
        metrics["revenue_change"] = round(
            (financials["total_income"] - prior_month_data["total_income"]) / prior_month_data["total_income"] * 100, 1
        ) if prior_month_data["total_income"] else 0

    return metrics

Step 3: Generate the P&L Report

def generate_pnl_report(financials, metrics, month):
    report = f"# Profit & Loss Statement\n## {month}\n\n"
    report += "## Revenue\n\n"
    for item in financials["income"]:
        report += f"| {item['category']} | ${item['amount']:,.2f} |\n"
    report += f"\n**Total Revenue: ${metrics['total_revenue']:,.2f}**\n\n"

    report += "## Expenses\n\n"
    for item in financials["expenses"]:
        report += f"| {item['category']} | ${item['amount']:,.2f} |\n"
    report += f"\n**Total Expenses: ${metrics['total_expenses']:,.2f}**\n\n"

    report += f"## Bottom Line\n\n"
    report += f"**Net Income: ${metrics['net_income']:,.2f}**\n"
    report += f"**Profit Margin: {metrics['profit_margin']}%**\n"

    return report

Step 4: Add AI Commentary

import anthropic

client = anthropic.Anthropic()

def add_financial_commentary(metrics, financials):
    prompt = f"""Write a 3-sentence financial commentary for this month's results.
Revenue: ${metrics['total_revenue']:,.2f}
Expenses: ${metrics['total_expenses']:,.2f}
Net Income: ${metrics['net_income']:,.2f}
Margin: {metrics['profit_margin']}%
Top expense categories: {', '.join(e['category'] for e in financials['expenses'][:3])}

Be specific. Note trends. Suggest one thing to watch."""

    message = client.messages.create(
        model="claude-sonnet-4-20250514",
        max_tokens=300,
        messages=[{"role": "user", "content": prompt}]
    )
    return message.content[0].text

Step 5: Schedule Monthly Generation

def run_monthly_report(conn, month=None):
    if not month:
        month = datetime.now().strftime("%Y-%m")
    financials = get_monthly_financials(conn, month)
    metrics = calculate_metrics(financials)
    report = generate_pnl_report(financials, metrics, month)
    commentary = add_financial_commentary(metrics, financials)
    report += f"\n## Commentary\n\n{commentary}\n"

    from pathlib import Path
    Path(f"reports/pnl-{month}.md").write_text(report)
    print(f"Monthly report generated for {month}")

# Schedule: 3rd of each month at 8am
# 0 8 3 * * python3 /path/to/monthly_report.py

What to Build Next

Add trend charts comparing the last 12 months side by side. A single month's numbers are informative. The trend line tells the real story about business health.

Related Reading

• AI-Powered Reporting That Actually Gets Read - financial reports people use
• Building Your First Automation: A Complete Guide - automation fundamentals
• The Feedback Loop That Powers Everything - financial feedback loops