Automated Report Generation with Python: Data to PDF

Automated report generation means a script or service pulls data from your systems, calculates the metrics, builds charts, lays them out in a PDF and emails it to the right people on a schedule, with no one copying numbers into spreadsheets. This tutorial builds an automated report generation pipeline in Python using pandas, matplotlib, FPDF2 and smtplib, with copy-ready code.

Why automate report generation?

Consider a hypothetical finance manager at a manufacturer. Every Friday she spends three hours pulling numbers from four systems, pasting into Excel, building charts, converting to PDF and emailing leadership. That is around 150 hours a year on one report.

McKinsey Global Institute research (2012) estimated that knowledge workers spend about 19 percent of their time searching for and gathering information. Repeated report building is a big part of that, and it is the kind of structured, repetitive task automation handles well.

What does automated reporting actually mean?

Think of a very reliable assistant following an exact checklist:

1. Open the data (spreadsheet, database or accounting export)
2. Do the calculations (totals, averages, comparisons)
3. Create the charts
4. Lay everything out in a professional PDF
5. Send it to the right people
6. Repeat at the same time every day, week or month

1. 1Collect DataPull from Excel, CSV, or database
2. 2ProcessCalculate totals & metrics
3. 3VisualiseGenerate charts & graphs
4. 4Create PDFFormat into professional report
5. 5DistributeEmail to stakeholders

Is Python report generation right for you?

What's your current situation?

• Someone spends hours each week on the same reportStrong candidate for automation
• Reports pull from Excel/CSV files or a databaseGood fit for this tutorial
• Data lives in complex ERP systems across entitiesLikely needs integration work first
• Report format changes every timeStandardise the report first

This tutorial suits you if:

• The same report is produced weekly, monthly or quarterly
• Data lives in Excel, CSV files or a database
• You or a colleague are comfortable following technical steps
• You want to understand how the automation works

Consider other options if:

• Data sits in SAP, Oracle or a custom ERP that needs integration
• Users need interactive, real-time dashboards (a BI tool such as Power BI fits better)
• Strict compliance requirements apply to the report's content and distribution

What will you build?

1. Part 1Data collectionConnect to data sources and pull information
2. Part 2Chart generationCreate visualisations automatically
3. Part 3PDF creationBuild branded reports
4. Part 4Email distributionSend reports to the right people
5. Part 5SchedulingRun automatically on a schedule

How do you set up Python for report automation?

Setup takes 15 to 20 minutes and is done once. In a managed corporate environment, ask IT for approval or a prepared environment first.

Step 1: Install Python

• Windows: python.org/downloads/windows. During install, tick "Add Python to PATH".
• Mac: python.org/downloads/macos

Check it worked: open Command Prompt or Terminal and run python --version (or python3 --version on Mac).

Step 2: Install a code editor

Visual Studio Code is free and works on Windows, Mac and Linux. PyCharm Community is an alternative.

Step 3: Create a project folder

# Windows
mkdir C:\reports-automation
cd C:\reports-automation

# Mac/Linux
mkdir ~/reports-automation
cd ~/reports-automation

What is the Python report generation stack?

In plain English: pandas reads and summarises data, matplotlib draws charts, FPDF2 builds the PDF, schedule is the alarm clock, and smtplib is the postman.

Install everything with one command:

pip install pandas openpyxl matplotlib fpdf2 jinja2 schedule python-dotenv

If that fails, try pip3. Official documentation: pandas, matplotlib, FPDF2, Jinja2, schedule, python-dotenv.

FPDF2 balances simplicity and capability. ReportLab is more powerful but heavier. WeasyPrint is excellent for HTML-to-PDF but needs system libraries that can be awkward on Windows servers.

Step 1: How do you load and aggregate report data?

This step opens the data, filters to the reporting period and calculates totals, the same things you would do in Excel.

from datetime import datetime, timedelta



logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

class ReportDataPipeline:
    """Handles data extraction and transformation for reports."""

    def __init__(self, data_source: str):
        self.data_source = data_source
        self.raw_data = None
        self.processed_data = None

    def load_data(self) -> pd.DataFrame:
        """Load data from CSV or Excel."""
        try:
            if self.data_source.endswith('.csv'):
                self.raw_data = pd.read_csv(self.data_source)
            elif self.data_source.endswith(('.xlsx', '.xls')):
                self.raw_data = pd.read_excel(self.data_source)
            else:
                # For databases, use SQLAlchemy with pd.read_sql
                raise ValueError(f"Unsupported data source: {self.data_source}")

            logging.info(f"Loaded {len(self.raw_data)} rows from {self.data_source}")
            return self.raw_data

        except Exception as e:
            logging.error(f"Failed to load data: {e}")
            raise

    def filter_date_range(self, date_column: str, days: int = 30) -> pd.DataFrame:
        """Filter data to a specific date range."""
        end_date = datetime.now()
        start_date = end_date - timedelta(days=days)

        self.raw_data[date_column] = pd.to_datetime(self.raw_data[date_column])
        mask = (self.raw_data[date_column] >= start_date) & \
               (self.raw_data[date_column] <= end_date)

        self.processed_data = self.raw_data[mask].copy()
        logging.info(f"Filtered to {len(self.processed_data)} rows in date range")
        return self.processed_data

    def calculate_summary_metrics(self, value_column: str,
                                  group_column: str = None) -> dict:
        """Calculate key business metrics."""
        data = self.processed_data if self.processed_data is not None \
            else self.raw_data

        metrics = {
            'total': data[value_column].sum(),
            'average': data[value_column].mean(),
            'count': len(data),
            'max': data[value_column].max(),
            'min': data[value_column].min(),
        }

        if group_column:
            metrics['by_group'] = data.groupby(group_column)[value_column].sum().to_dict()

        return metrics

What the code does

• ReportDataPipeline is the report-making machine.
• load_data() opens a CSV or Excel file.
• filter_date_range() keeps only the reporting period, such as the last 30 days.
• calculate_summary_metrics() returns total, average, count, max, min and optional group totals.

pipeline = ReportDataPipeline("sales_data.csv")
pipeline.load_data()
pipeline.filter_date_range("order_date", days=30)
metrics = pipeline.calculate_summary_metrics("revenue", "region")

A pitfall learned the hard way

Keep the logging. When a scheduled report fails at 3am on a Sunday, logs tell you where and why. Our engineers have lost hours to a single malformed date in one row. Logging from the start saves that time.

Step 2: How do you generate charts automatically?

Charts make reports scannable. This class creates line charts for trends, bar charts for comparisons and pie charts for proportions, all returned as in-memory images.

from io import BytesIO


matplotlib.use('Agg')  # No display needed on servers



class ReportChartGenerator:
    """Generates charts for PDF reports."""

    def __init__(self, style: str = 'seaborn-v0_8-whitegrid'):
        plt.style.use(style)
        # Brand colours: adjust to suit
        self.colors = ['#2563eb', '#10b981', '#f59e0b', '#ef4444', '#8b5cf6']

    def _to_buffer(self, fig) -> BytesIO:
        buffer = BytesIO()
        fig.savefig(buffer, format='png', dpi=150, bbox_inches='tight')
        buffer.seek(0)
        plt.close(fig)
        return buffer

    def create_line_chart(self, data: pd.DataFrame, x_column: str,
                          y_column: str, title: str) -> BytesIO:
        fig, ax = plt.subplots(figsize=(10, 5))
        ax.plot(data[x_column], data[y_column], color=self.colors[0], linewidth=2)
        ax.fill_between(data[x_column], data[y_column], alpha=0.3, color=self.colors[0])
        ax.set_title(title, fontsize=14, fontweight='bold', pad=20)
        ax.set_xlabel(x_column.replace('_', ' ').title())
        ax.set_ylabel(y_column.replace('_', ' ').title())
        if pd.api.types.is_datetime64_any_dtype(data[x_column]):
            ax.xaxis.set_major_formatter(mdates.DateFormatter('%d %b'))
            plt.setp(ax.get_xticklabels(), rotation=45)
        ax.grid(True, alpha=0.3)
        fig.tight_layout()
        return self._to_buffer(fig)

    def create_bar_chart(self, data: dict, title: str,
                         xlabel: str = '', ylabel: str = '') -> BytesIO:
        fig, ax = plt.subplots(figsize=(10, 5))
        categories, values = list(data.keys()), list(data.values())
        bars = ax.bar(categories, values, color=self.colors[:len(categories)])
        for bar, value in zip(bars, values):
            ax.annotate(f'{value:,.0f}',
                        xy=(bar.get_x() + bar.get_width() / 2, bar.get_height()),
                        xytext=(0, 3), textcoords="offset points",
                        ha='center', va='bottom', fontsize=10)
        ax.set_title(title, fontsize=14, fontweight='bold', pad=20)
        ax.set_xlabel(xlabel)
        ax.set_ylabel(ylabel)
        plt.setp(ax.get_xticklabels(), rotation=45, ha='right')
        fig.tight_layout()
        return self._to_buffer(fig)

    def create_pie_chart(self, data: dict, title: str) -> BytesIO:
        fig, ax = plt.subplots(figsize=(8, 8))
        ax.pie(list(data.values()), labels=list(data.keys()), autopct='%1.1f%%',
               colors=self.colors[:len(data)], startangle=90)
        ax.set_title(title, fontsize=14, fontweight='bold', pad=20)
        return self._to_buffer(fig)

Settings worth knowing: figsize sets chart size in inches, self.colors holds brand colours, and dpi=150 balances sharpness and file size.

Memory management

Each chart is saved to a BytesIO buffer and the figure is closed. Without plt.close(), jobs that generate dozens of charts can exhaust memory, and that failure appears in production well before it appears on a laptop.

Step 3: How do you build the PDF?

This class adds a header and footer on each page, titles, metric lines, charts, tables and paragraphs.

from fpdf import FPDF

class BusinessReportPDF(FPDF):
    """Custom PDF class for business reports."""

    def __init__(self, company_name: str = "Your Organisation"):
        super().__init__()
        self.company_name = company_name
        self.set_auto_page_break(auto=True, margin=15)

    def header(self):
        self.set_font('Helvetica', 'B', 12)
        self.cell(0, 10, self.company_name, align='L')
        self.set_font('Helvetica', '', 10)
        self.cell(0, 10, datetime.now().strftime('%d %B %Y'), align='R',
                  new_x="LMARGIN", new_y="NEXT")
        self.line(10, 25, 200, 25)
        self.ln(10)

    def footer(self):
        self.set_y(-15)
        self.set_font('Helvetica', 'I', 8)
        self.cell(0, 10, f'Page {self.page_no()}/{{nb}}', align='C')

    def add_title(self, title: str):
        self.set_font('Helvetica', 'B', 24)
        self.cell(0, 20, title, align='C', new_x="LMARGIN", new_y="NEXT")
        self.ln(10)

    def add_subtitle(self, subtitle: str):
        self.set_font('Helvetica', 'B', 14)
        self.set_fill_color(240, 240, 240)
        self.cell(0, 10, subtitle, fill=True, new_x="LMARGIN", new_y="NEXT")
        self.ln(5)

    def add_metric(self, label: str, value: str):
        self.set_font('Helvetica', '', 10)
        self.cell(60, 8, label)
        self.set_font('Helvetica', 'B', 14)
        self.cell(0, 8, value, new_x="LMARGIN", new_y="NEXT")

    def add_chart(self, chart_buffer: BytesIO, width: int = 180):
        with tempfile.NamedTemporaryFile(suffix='.png', delete=False) as tmp:
            tmp.write(chart_buffer.getvalue())
            tmp_path = tmp.name
        self.image(tmp_path, x=15, w=width)
        self.ln(10)
        os.unlink(tmp_path)

    def add_table(self, headers: list, data: list, col_widths: list = None):
        if col_widths is None:
            col_widths = [190 / len(headers)] * len(headers)
        self.set_font('Helvetica', 'B', 10)
        self.set_fill_color(50, 50, 50)
        self.set_text_color(255, 255, 255)
        for header, width in zip(headers, col_widths):
            self.cell(width, 8, header, border=1, fill=True, align='C')
        self.ln()
        self.set_font('Helvetica', '', 9)
        self.set_text_color(0, 0, 0)
        for row_idx, row in enumerate(data):
            self.set_fill_color(245, 245, 245) if row_idx % 2 == 0 \
                else self.set_fill_color(255, 255, 255)
            for cell, width in zip(row, col_widths):
                self.cell(width, 7, str(cell), border=1, fill=True, align='C')
            self.ln()
        self.ln(5)

    def add_paragraph(self, text: str):
        self.set_font('Helvetica', '', 10)
        self.multi_cell(0, 6, text)
        self.ln(5)

PDF coordinates are in millimetres from the top-left. self.ln(5) moves down 5mm, like pressing Enter.

Step 4: How do you put the pipeline together?

def generate_sales_report(data_file: str, output_path: str) -> str:
    """Generate a complete sales report PDF."""
    pipeline = ReportDataPipeline(data_file)
    pipeline.load_data()
    pipeline.filter_date_range('order_date', days=30)
    metrics = pipeline.calculate_summary_metrics('revenue', 'region')

    charts = ReportChartGenerator()
    daily = pipeline.processed_data.groupby(
        pipeline.processed_data['order_date'].dt.normalize()
    )['revenue'].sum().reset_index()
    daily.columns = ['date', 'revenue']

    trend_chart = charts.create_line_chart(daily, 'date', 'revenue',
                                           'Daily Revenue (Last 30 Days)')
    region_chart = charts.create_bar_chart(metrics['by_group'],
                                           'Revenue by Region', ylabel='Revenue (AUD)')

    pdf = BusinessReportPDF(company_name="Example Manufacturing")
    pdf.alias_nb_pages()
    pdf.add_page()
    pdf.add_title("Monthly Sales Report")
    pdf.add_paragraph(f"Generated {datetime.now():%d %B %Y at %H:%M}. Period: last 30 days.")

    pdf.add_subtitle("Key metrics")
    pdf.add_metric("Total revenue", f"${metrics['total']:,.2f}")
    pdf.add_metric("Total orders", f"{metrics['count']:,}")
    pdf.add_metric("Average order", f"${metrics['average']:,.2f}")
    pdf.ln(10)

    pdf.add_subtitle("Revenue trend")
    pdf.add_chart(trend_chart)
    pdf.add_page()
    pdf.add_subtitle("Regional performance")
    pdf.add_chart(region_chart)

    pdf.add_subtitle("Top 10 orders")
    top = pipeline.processed_data.nlargest(10, 'revenue')[
        ['order_id', 'customer', 'region', 'revenue']].values.tolist()
    pdf.add_table(['Order ID', 'Customer', 'Region', 'Revenue'],
                  [[r[0], r[1], r[2], f"${r[3]:,.2f}"] for r in top],
                  col_widths=[30, 70, 40, 40])

    os.makedirs(output_path, exist_ok=True)
    output_file = os.path.join(output_path, f"sales_report_{datetime.now():%Y%m%d}.pdf")
    pdf.output(output_file)
    logging.info(f"Report generated: {output_file}")
    return output_file

To run it: save the code in report_generator.py, provide a CSV with order_date, revenue, region, order_id and customer columns, and run python report_generator.py.

Step 5: How do you email reports automatically?

from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

from dotenv import load_dotenv

load_dotenv()

def send_email(recipients: list, subject: str, body: str, attachment_path: str = None) -> bool:
    smtp_server = os.getenv('SMTP_SERVER')
    smtp_port = int(os.getenv('SMTP_PORT', '587'))
    sender = os.getenv('SENDER_EMAIL')
    password = os.getenv('SENDER_PASSWORD')

    msg = MIMEMultipart()
    msg['From'] = sender
    msg['To'] = ', '.join(recipients)
    msg['Subject'] = subject
    msg.attach(MIMEText(body, 'plain'))

    if attachment_path:
        with open(attachment_path, 'rb') as f:
            part = MIMEApplication(f.read(), _subtype='pdf')
            part.add_header('Content-Disposition', 'attachment',
                            filename=os.path.basename(attachment_path))
            msg.attach(part)

    try:
        with smtplib.SMTP(smtp_server, smtp_port) as server:
            server.starttls(context=ssl.create_default_context())
            if password:
                server.login(sender, password)
            server.sendmail(sender, recipients, msg.as_string())
        logging.info(f"Email sent to {', '.join(recipients)}")
        return True
    except Exception as e:
        logging.error(f"Failed to send email: {e}")
        return False

Email settings

Store settings in a .env file in the project folder, never in code:

SMTP_SERVER=smtp.yourcompany.com.au
SMTP_PORT=587
SENDER_EMAIL=reports@yourcompany.com.au
SENDER_PASSWORD=

Security: never commit .env to version control. Add it to .gitignore.

In an organisation, ask IT for:

• A dedicated service mailbox (such as reports@) instead of a personal account
• The approved SMTP relay or mail API. Microsoft is retiring basic authentication for SMTP in Exchange Online, so Microsoft 365 tenants often use Microsoft Graph or an authenticated relay instead
• Allowed recipients and any data classification rules for attachments

Step 6: How do you schedule report generation?

For a quick start, the schedule library keeps a script running:

def run_weekly_report():
    try:
        report_file = generate_sales_report('/path/to/sales_data.csv', '/path/to/reports/')
        send_email(['manager@company.com.au', 'finance@company.com.au'],
                   f"Weekly Sales Report - {datetime.now():%d %b %Y}",
                   "Please find this week's report attached.",
                   report_file)
    except Exception as e:
        logging.error(f"Weekly report failed: {e}")
        send_email(['it@company.com.au'], "ALERT: weekly report failed",
                   f"The weekly sales report failed: {e}")

schedule.every().monday.at("07:00").do(run_weekly_report)

if __name__ == "__main__":
    logging.info("Report scheduler started")
    while True:
        schedule.run_pending()
        time.sleep(60)

For production, use cron (Linux/Mac) or Task Scheduler (Windows) so the job runs even after restarts. Cron entry for Monday 7am:

0 7 * * 1 /usr/bin/python3 /path/to/report_generator.py

What are the common pitfalls in automated reporting?

1. Data quality assumptions

Validate before generating:

def validate_data(df: pd.DataFrame) -> tuple[bool, list]:
    issues = []
    if df.empty:
        issues.append("Dataset is empty")
    if df.isnull().sum().sum() > len(df) * 0.1:
        issues.append("More than 10% missing values")
    duplicates = df.duplicated().sum()
    if duplicates > 0:
        issues.append(f"{duplicates} duplicate rows found")
    return len(issues) == 0, issues

2. Time zones

Organisations with sites across Australian states cross several time zones and daylight saving rules. Store in UTC and convert for display:

from zoneinfo import ZoneInfo

utc_time = datetime.now(ZoneInfo('UTC'))
local_time = utc_time.astimezone(ZoneInfo('Australia/Sydney'))

3. File paths

Use pathlib for paths that work on Windows and Linux:

from pathlib import Path

output_dir = Path('/path/to/reports')
output_dir.mkdir(parents=True, exist_ok=True)
output_file = output_dir / f"report_{datetime.now():%Y%m%d}.pdf"

Is automated report generation worth it?

	Manual process	Automated	Impact
Staff time per report	Hours each cycle	Review only	Time back
Copy and paste errors	Common	Removed at source	Fewer errors
Delivery	Depends on who is available	Same time every cycle	Consistent
Maintenance	None, but repeated effort	Occasional updates when sources change	Plan for it

Work out your own case: hours per report multiplied by frequency and loaded hourly rate, against build time and ongoing maintenance. Once the pipeline exists, adding reports reuses the same code for weekly sales, monthly finance summaries, daily operations snapshots, board packs and customer statements.

When should you use a BI tool instead of Python PDFs?

Our data, BI and analytics team often combines both: a single version of the numbers in a data platform, with dashboards and scheduled PDFs on top. For an example, see our Tier 1 infrastructure carbon reporting case study.

How do you fix common Python setup problems?

• "Python is not recognized" or "command not found": reinstall with "Add Python to PATH" (Windows) or use python3 (Mac).
• "ModuleNotFoundError: No module named 'pandas'": run pip install pandas.
• "Permission denied" when saving: save to a folder you own.
• "SMTP Authentication Error": check credentials and whether your mail provider allows SMTP authentication.
• Charts cut off in the PDF: adjust figsize and call pdf.add_page() before sections that do not fit.
• "FileNotFoundError": use the full file path.

What is the implementation roadmap?

1. Day 1Setup and testInstall Python and an editor, run a test script
2. Day 2-3Data pipelineLoad your real files and validate them
3. Day 4-5Charts and PDFProduce your first automated report
4. Day 6-7Email and scheduleSet up automatic delivery
5. OngoingExpandAdd reports on the same foundation

1. Start with the most time-consuming report.
2. Document the current process: sources, calculations, recipients.
3. Build incrementally: data, then charts, then PDF, then email.
4. Test with real data, including edge cases.
5. Plan for failure with logging, validation and alerts.

Further learning: Real Python, pandas getting started and Automate the Boring Stuff.

How Solve8 can help

When reports pull from several ERPs, entities or sites, our engineers build the data connections and reporting layer so the numbers arrive on time and match across every report, running on your infrastructure in Australia. See data, BI and analytics and process automation, or talk to us.