How to Build Interactive PDF Reports with Claude Code (And Finally Retire Those Excel Macros)

Excel macros have had a good run. Twenty years of VLOOKUP anxiety, broken references, and the perpetual fear of “accidentally enabling content” have built careers and caused migraines in equal measure. But the combination of Claude Code and Python’s PDF generation ecosystem is making a compelling case that you don’t have to live this way anymore.

This tutorial walks through a complete workflow: you describe what you want, Claude Code writes the Python, and out comes a polished PDF report with charts, conditional formatting, and live database data — no macro editor in sight. The approach uses Claude’s code generation capabilities alongside libraries like ReportLab and matplotlib, orchestrated through Claude’s API. It’s not magic. It’s Python you didn’t have to write yourself, which is close enough.

Before we get into the fun part: financial reports carry real stakes. Any automated output needs validation, compliance review, and human sign-off before it touches anyone who matters. This tutorial covers the technical workflow — governance is your team’s job, and it’s not optional.

What You’ll Actually Build

By the end of this guide, you’ll have a working pipeline that pulls data from a database (or CSV), feeds it to a Python script generated by Claude Code, and produces a multi-page PDF with a summary table, a bar chart of monthly revenue, conditional row formatting based on performance thresholds, and a header/footer with your company logo and report date. The whole thing runs from a single command. You update the data, re-run the script, get a new PDF. That’s it.

Requirements

You’ll need Python 3.9 or later, an Anthropic API key (grab one at console.anthropic.com), and the following packages installed: anthropic, reportlab, matplotlib, pandas, and sqlalchemy if you’re connecting to a database. Install them in one shot with pip install anthropic reportlab matplotlib pandas sqlalchemy. For this tutorial, the model in use is Claude Sonnet 4.6 — it handles code generation tasks well and won’t burn through your budget on a few API calls.

Note 💡

Claude’s context window sits at 200,000 tokens, which means you can paste in a substantial chunk of your existing data schema or a sample dataset directly in your prompt and Claude will understand the structure before writing a single line of code.

Step 1 — Ask Claude to Scaffold the Project

Start with a Claude Code session (claude.ai/code) or the API. Your first prompt sets the architecture. Be specific about libraries, output format, and data source — vague prompts produce vague code.

You are an expert Python developer. Create a complete Python script that generates a multi-page PDF financial report using ReportLab. The script should:

1. Accept a pandas DataFrame as input with columns: department, monthly_revenue, target, variance_pct
2. Generate a title page with report name, date, and subtitle
3. On page 2: render a summary table with conditional row formatting — green background if variance_pct >= 0, red if variance_pct < -5, yellow if between -5 and 0
4. On page 3: render a horizontal bar chart using matplotlib showing monthly_revenue per department, embedded as an image in the PDF
5. Include page numbers and a footer with "CONFIDENTIAL — Internal Use Only" on every page
6. Save the output to a file path passed as a command-line argument

Use ReportLab's Platypus framework (SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image). Use matplotlib to generate the chart, save it as a temporary PNG, embed it, then delete the temp file. Add comments explaining each section.

Claude will return a complete, runnable script. Read it before you run it — not because Claude is untrustworthy, but because understanding what you’re executing is just good practice, especially with financial data in the pipeline.

Step 2 — Connect to a Real Data Source

Static CSV files are fine for testing, but the actual value comes from live database connections. Ask Claude to extend the script with a database layer.

Extend the previous script to add a data loading function. The function should:

1. Accept a SQLAlchemy connection string as input
2. Run this SQL query: SELECT department, SUM(revenue) as monthly_revenue, MAX(target) as target, ROUND((SUM(revenue) - MAX(target)) / MAX(target) * 100, 2) as variance_pct FROM financial_data WHERE report_month = :month GROUP BY department ORDER BY department
3. Accept the report_month parameter (format: 'YYYY-MM') from command line
4. Return a pandas DataFrame
5. Include error handling: if the database is unreachable, fall back to loading from a CSV file named 'fallback_data.csv' in the same directory and log a warning

Use SQLAlchemy's text() for the parameterized query. Add a --db-url and --month argument to the argparse setup.

Pro tip ✅

Pass your actual table schema to Claude in the prompt — copy-paste the CREATE TABLE statement or a df.dtypes output. Claude will write column names and data types correctly the first time instead of you fixing them in round two.

Step 3 — Add Conditional Formatting That Actually Means Something

ReportLab’s TableStyle is powerful but verbose. Claude handles the boilerplate so you can focus on the logic. Here’s how to ask for precisely the formatting behavior you want.

In the summary table section, replace the static TableStyle with a function called build_table_style(dataframe) that:

1. Starts with a base style: header row has dark navy background (#1B2A4A) with white bold text, font size 10, all cells have 1pt grey border (#CCCCCC), alternating row background #F8F9FA for even rows
2. Iterates through each data row and applies:
   - Background #D4EDDA (light green) if variance_pct >= 0
   - Background #F8D7DA (light red) if variance_pct < -5
   - Background #FFF3CD (light yellow) if -5 <= variance_pct < 0
3. In the variance_pct column, formats the cell text as "+X.X%" for positive values and "X.X%" for negative, and makes negative values bold red text
4. Returns a TableStyle object

Also add a legend box below the table explaining the three color codes.

Step 4 — Embed a Live Chart

The matplotlib integration is where reports stop looking like they came from 2003. Here’s the prompt for a chart that’s actually useful, not just decorative.

Write a function called generate_revenue_chart(dataframe, output_path) that:

1. Creates a horizontal bar chart using matplotlib with figsize=(10, 6)
2. Bars colored based on variance_pct: green (#2ECC71) if >= 0, red (#E74C3C) if < -5, orange (#F39C12) otherwise
3. Adds value labels at the end of each bar showing the revenue formatted as "$X,XXX,XXX"
4. Adds a vertical dashed line at the average revenue value, labeled "Avg: $X,XXX,XXX"
5. Title: "Department Revenue vs Target — {month}" where month is passed as a parameter
6. Clean styling: no top/right spines, light grey grid on x-axis only, font Helvetica or DejaVu Sans
7. Saves to output_path as PNG at 150 DPI, tight layout
8. Returns the output_path

Then in the main PDF generation code, call this function, embed the PNG using ReportLab's Image() with width=500 and proportional height, then delete the temp file using pathlib.Path.unlink().

Pro tip ✅

Always generate charts to a temp directory (use Python’s tempfile.mkdtemp()) rather than the working directory. It keeps things clean and avoids overwriting anything if two report runs happen simultaneously.

Step 5 — Set Up the Refresh Workflow

A one-time script is useful. A scheduled, parameterized pipeline is actually valuable. Here’s how to ask Claude to wire it all together into something you can hand off to a cron job or a task scheduler.

Create a shell script called run_monthly_report.sh that:

1. Activates a Python virtual environment at ./venv
2. Sets the DATABASE_URL from an environment variable (not hardcoded)
3. Derives the previous month in YYYY-MM format automatically using Python one-liner: python3 -c "from datetime import date; from dateutil.relativedelta import relativedelta; print((date.today().replace(day=1) - relativedelta(months=1)).strftime('%Y-%m'))"
4. Runs the Python report script with --db-url, --month, and --output flags
5. Names the output file "finance_report_{YYYY-MM}_{YYYYMMDD_HHMMSS}.pdf" in a ./reports/ directory
6. Logs success or failure to ./logs/report_runner.log with timestamps
7. On failure, sends a plain-text email alert using the mail command to a hardcoded address (placeholder: reports-alert@company.com)

Make it POSIX-compatible (no bash-isms).

Now write a Python script called validate_report.py that:

1. Takes the generated PDF path as input
2. Opens it with PyPDF2 and checks: page count is exactly 3, each page has content (non-zero content stream), the text "CONFIDENTIAL" appears on every page, the title page contains the current month name
3. Returns exit code 0 if all checks pass, exit code 1 with descriptive error messages if any fail
4. This script is meant to run as a post-generation check in the shell script above — add it as a step after PDF generation, before logging success

Warning ⚠️

Automated financial reports need a human review step before distribution, full stop. Build the validation script above as a sanity check, but it catches structural issues — not numerical errors. Someone who understands the numbers needs to look at the output before it goes to a CFO or a regulator.

Step 6 — Iterate Fast with Claude in the Loop

The real productivity gain isn’t the first script Claude writes — it’s how quickly you can iterate. When something looks off, paste the error or describe the visual problem directly into Claude Code.

The table in my PDF is getting cut off at the right margin. I'm using SimpleDocTemplate with leftMargin=72, rightMargin=72. The table has 4 columns and I set colWidths=[150, 120, 120, 100]. The page size is A4 (595 x 842 points). The table content includes currency values formatted like "$1,234,567". 

Diagnose why the table overflows and rewrite the column width calculation to fit within the available width, accounting for the margins. Also add a check at runtime that raises a descriptive ValueError if the total column width exceeds available page width, so this is caught before PDF generation.

Pro tip ✅

When debugging ReportLab layout issues with Claude, always include your page size in points (A4 = 595×842, Letter = 612×792), your margin values, and the exact column widths you’re using. Claude can do the arithmetic and catch overflows before you do another test render.

Step 7 — Handle Multiple Report Templates

Once the base pipeline works, you’ll want multiple report types without maintaining separate scripts for each. Claude can build a template system.

Refactor the report generation code into a class-based architecture called ReportGenerator. The class should:

1. Accept a config dictionary at initialization with keys: title, subtitle, logo_path (optional), color_scheme (dict with keys: header, positive, negative, warning, neutral), page_size (default A4), include_pages (list, e.g. ["summary_table", "revenue_chart", "top10_table"])
2. Expose a generate(dataframe, output_path, month) method that builds only the pages listed in include_pages, in that order
3. Store page builder methods as _build_summary_table(), _build_revenue_chart(), _build_top10_table() — each returns a list of ReportLab flowables
4. Allow color_scheme to override all the hardcoded colors from the previous implementation
5. Include a classmethod from_yaml(yaml_path) that loads the config from a YAML file

Write two example YAML config files: one for an executive summary (2 pages, minimal, dark navy scheme) and one for a detailed ops report (3 pages, more data, green scheme).

Pro tip ✅

Store your YAML config files in version control alongside the script. When the finance team wants a different layout next quarter, the change is a two-line YAML edit, not a Python debugging session.

When to Stick With Your BI Tool

This approach earns its keep for teams with specific, repeatable report structures that don’t change much month to month, where the data pipeline is already somewhat automated, and where you want PDF as the actual final artifact (for archival, email distribution, or regulatory filing). It’s also genuinely good for one-person or small teams who can’t justify enterprise BI licensing.

It’s the wrong call when you need interactive drill-down, real-time dashboards, or audit trails baked into the reporting layer. Tableau, Power BI, and Looker exist for good reasons. If your compliance team needs a documented, vendor-supported audit trail for every report, a custom Python script — however elegant — is going to create headaches. Use the right tool. Sometimes that’s still Excel.

What You Can Ship This Week

The full pipeline described here — database connection, conditional formatting, embedded charts, validation check, shell script wrapper — is maybe two to three hours of work if you follow the prompts above closely and already have your data source accessible. The biggest time investment is setting up your Python environment and getting the database connection string right, which Claude can’t do for you (and shouldn’t — credentials stay out of prompts, always).

What Claude Code genuinely changes here isn’t the technology. ReportLab has been around since 2000. What changes is the activation energy: from “I’d have to learn ReportLab to do this” to “I described what I wanted and got working code in four minutes.” That’s a real shift, and for finance teams whose Python skills range from nonexistent to rusty, it’s the difference between this project happening or not.

Start with Step 1, get a PDF rendering, then layer in the database and formatting. Ship something ugly that works before you perfect the color scheme. The Excel macro that took three days to debug isn’t sitting in judgment.

promptyze

See Full Bio