Build an Automated Reporting System

import pandas as pd
import anthropic
from pathlib import Path
from datetime import datetime

def load_data(folder: str) -> pd.DataFrame:
    """Load and combine all CSV files in folder."""
    files = list(Path(folder).glob("*.csv"))
    print(f"Loading {len(files)} files...")
    dfs = []
    for f in files:
        df = pd.read_csv(f)
        df["source"] = f.name
        dfs.append(df)
    combined = pd.concat(dfs, ignore_index=True)
    print(f"Loaded {len(combined):,} total rows")
    return combined

def analyze_data(df: pd.DataFrame) -> dict:
    """Generate key summaries."""
    return {
        "overview": {
            "total_rows": len(df),
            "total_revenue": df["revenue"].sum(),
            "avg_deal": df["revenue"].mean(),
        },
        "by_region": df.groupby("region")["revenue"].agg(["sum","mean","count"]),
        "by_rep": df.groupby("rep")["revenue"].sum().sort_values(ascending=False),
        "monthly": df.groupby("month")["revenue"].sum()
    }

def get_ai_insights(summaries: dict) -> str:
    """Get Claude's analysis of the data."""
    ov = summaries["overview"]
    prompt = f"""Analyze this sales performance data.

Overview:
- Total records: {ov['total_rows']:,}
- Total revenue: ${ov['total_revenue']:,.0f}
- Average deal: ${ov['avg_deal']:,.0f}

By Region:
{summaries['by_region'].to_string()}

Top Reps:
{summaries['by_rep'].head(5).to_string()}

Monthly Trend:
{summaries['monthly'].to_string()}

Provide:
1. Executive summary (2-3 sentences)
2. 3 key findings with specific numbers
3. 2-3 actionable recommendations"""

    client = anthropic.Anthropic()
    response = client.messages.create(
        model="claude-opus-4-5",
        max_tokens=1000,
        messages=[{"role": "user", "content": prompt}]
    )
    return response.content[0].text

def export_report(summaries: dict, insights: str, output_file: str):
    """Export multi-sheet Excel report."""
    with pd.ExcelWriter(output_file, engine="xlsxwriter") as writer:
        # Sheet 1: AI Insights
        pd.DataFrame({"AI Analysis": [insights]}).to_excel(
            writer, sheet_name="AI Insights", index=False
        )
        # Sheet 2: By Region
        summaries["by_region"].to_excel(writer, sheet_name="By Region")
        # Sheet 3: By Rep
        summaries["by_rep"].to_excel(writer, sheet_name="By Rep")
        # Sheet 4: Monthly
        summaries["monthly"].to_excel(writer, sheet_name="Monthly")
    print(f"Report saved to {output_file}")

# ── Run the pipeline ──────────────────────────────
if __name__ == "__main__":
    df = load_data("data/")
    summaries = analyze_data(df)
    insights = get_ai_insights(summaries)
    timestamp = datetime.now().strftime("%Y-%m-%d")
    export_report(summaries, insights, f"report_{timestamp}.xlsx")
    print("Done. Report ready.")

# Run every Monday at 7am
0 7 * * 1 /usr/bin/python3 /path/to/auto_report.py