6.3 Project 2 — End-to-End Python Automation Pipeline

6.3 Project 2 — End-to-End Market Price Automation

In this project, we will combine several Python skills learned throughout this course into a single automated workflow.

The system will collect publicly available market-price data, maintain a historical Excel ledger, generate a PDF report, and distribute the report through email.

1. Complete Automation Workflow

Scheduled Execution
        ↓
Fetch Public Market Data
        ↓
Validate HTTP Response
        ↓
Parse Price Information
        ↓
Validate Extracted Data
        ↓
Create Today's Record
        ↓
Update Excel Ledger
        ↓
Calculate Daily Changes
        ↓
Generate PDF Report
        ↓
Send Email
        ↓
Write Execution Log
        ↓
Complete

This is an example of an end-to-end automation pipeline because the output of one stage becomes the input of the next stage.

2. Responsible Web Data Collection

This project uses publicly accessible data only. Before collecting information from a website, check its terms of service, robots.txt directives, licensing conditions, and applicable policies.

Do not attempt to bypass authentication, CAPTCHA systems, paywalls, access controls, rate limits, or other technical restrictions.

For production financial applications, prefer an official API or licensed market-data provider whenever one is available.

3. Project Objectives

By completing this project, you will learn how to:

• Retrieve data from a public web source.
• Use HTTP requests programmatically.
• Parse structured information from HTML.
• Validate downloaded data.
• Store historical records in Excel.
• Use openpyxl for spreadsheet automation.
• Calculate daily price changes.
• Generate PDF reports programmatically.
• Send automated email messages.
• Attach generated files to emails.
• Implement logging.
• Handle failures safely.
• Schedule the entire workflow.

4. Technologies Used

5. Recommended Project Structure

market_automation/
│
├── .venv/
│
├── data/
│   └── market_prices.xlsx
│
├── reports/
│   └── daily_report.pdf
│
├── logs/
│   └── automation.log
│
├── src/
│   ├── fetch_data.py
│   ├── excel_manager.py
│   ├── report_generator.py
│   └── email_sender.py
│
├── config/
│
├── main.py
│
├── requirements.txt
│
└── README.md

For learning purposes, all components can initially be kept in one Python file. Once the workflow works, separate the components into modules.

6. Install Required Packages

pip install requests beautifulsoup4 pandas openpyxl reportlab

Create a dependency file:

pip freeze > requirements.txt

The modules smtplib, email, logging, pathlib, and several other utilities used in this project are part of Python's standard library.

7. Separate Configuration from Code

A production automation should not contain passwords directly in the source code.

For example, sensitive configuration can be supplied through environment variables.

import os

EMAIL_HOST = os.getenv(
    "EMAIL_HOST"
)

EMAIL_PORT = int(
    os.getenv(
        "EMAIL_PORT",
        "587"
    )
)

EMAIL_USER = os.getenv(
    "EMAIL_USER"
)

EMAIL_PASSWORD = os.getenv(
    "EMAIL_PASSWORD"
)

EMAIL_PASSWORD = "MyRealPassword123"

8. Choosing the Market Data Source

For this project, use a publicly accessible source that provides the required price information and permits the intended use.

A suitable source should provide information such as:

• Instrument name or symbol
• Current or latest available price
• Date or timestamp
• Optional change information

9. Define the Data Model

Before writing the scraper, decide what information the Excel ledger should contain.

10. Create Reliable Project Paths

from pathlib import Path

BASE_DIR = (
    Path(__file__)
    .resolve()
    .parent
)

DATA_DIR = (
    BASE_DIR / "data"
)

REPORT_DIR = (
    BASE_DIR / "reports"
)

LOG_DIR = (
    BASE_DIR / "logs"
)

DATA_DIR.mkdir(
    exist_ok=True
)

REPORT_DIR.mkdir(
    exist_ok=True
)

LOG_DIR.mkdir(
    exist_ok=True
)

This approach is more reliable than depending on the current working directory.

11. Configure Logging

import logging

LOG_FILE = (
    LOG_DIR / "automation.log"
)

logging.basicConfig(
    filename=LOG_FILE,
    level=logging.INFO,
    format=(
        "%(asctime)s "
        "%(levelname)s "
        "%(message)s"
    )
)

logging.info(
    "Automation pipeline started."
)

Logging becomes especially important when the script runs unattended.

12. Retrieve Web Data with requests

import requests

url = (
    "https://example.com/"
)

response = requests.get(
    url,
    timeout=20
)

response.raise_for_status()

html = response.text

The timeout prevents a network request from waiting indefinitely.

The method raise_for_status() raises an exception for unsuccessful HTTP responses.

13. Identify Your HTTP Client Appropriately

Some public web servers expect a normal HTTP client identification header.

headers = {
    "User-Agent":
        "DataAnalyticsEducationalProject/1.0"
}

response = requests.get(
    url,
    headers=headers,
    timeout=20
)

Do not use headers to impersonate another browser or to circumvent restrictions.

14. Parse HTML with BeautifulSoup

from bs4 import BeautifulSoup

soup = BeautifulSoup(
    html,
    "html.parser"
)

print(
    soup.title
)

BeautifulSoup converts the HTML document into a structure that can be searched and navigated.

15. Extract a Price from HTML

Suppose the permitted page contains:

<span class="price">125.40</span>

It can be selected with:

price_element = soup.select_one(
    ".price"
)

if price_element is None:

    raise ValueError(
        "Price element not found."
    )

price_text = (
    price_element.get_text(
        strip=True
    )
)

print(
    price_text
)

16. Convert Extracted Price to a Number

Web pages often contain currency symbols, commas, or whitespace.

import re

cleaned = re.sub(
    r"[^0-9.\-]",
    "",
    price_text
)

price = float(
    cleaned
)

print(
    price
)

Always validate the resulting number before storing it.

17. Validate the Extracted Price

if price < 0:

    raise ValueError(
        "Invalid negative price."
    )

Additional validation rules can include:

• Price must be numeric.
• Price must not be negative where negative values are impossible.
• Symbol must be present.
• Timestamp must be valid.
• Unexpectedly large movements should be flagged.

18. Create a Daily Record

from datetime import date

record = {
    "Date": date.today().isoformat(),
    "Symbol": "ABC",
    "Name": "Example Instrument",
    "Price": price
}

print(
    record
)

A dictionary provides a convenient intermediate representation before inserting the record into pandas or Excel.

19. Create a DataFrame

df_today = pd.DataFrame(
    [record]
)

print(
    df_today
)

A DataFrame is useful for validation, transformation, calculations, and report generation.

20. Create the Excel Ledger

Define the workbook location:

excel_file = (
    DATA_DIR
    / "market_prices.xlsx"
)

If the workbook does not exist, create it:

if not excel_file.exists():

    df_today.to_excel(
        excel_file,
        index=False,
        sheet_name="Prices"
    )

21. Read Existing Excel Data

if excel_file.exists():

    history = pd.read_excel(
        excel_file,
        sheet_name="Prices"
    )

else:

    history = pd.DataFrame()

Combine historical data with today's record:

history = pd.concat(
    [
        history,
        df_today
    ],
    ignore_index=True
)

22. Prevent Accidental Duplicate Records

If the automation runs twice on the same day, it may accidentally insert the same record twice.

For a simple one-record-per-day design:

history = history.drop_duplicates(
    subset=[
        "Date",
        "Symbol"
    ],
    keep="last"
)

For intraday data, use a timestamp or unique observation ID instead of simply using the date.

23. Sort the Historical Ledger

history["Date"] = pd.to_datetime(
    history["Date"]
)

history = history.sort_values(
    [
        "Symbol",
        "Date"
    ]
)

24. Calculate Daily Price Change

Use groupby() and shift() when multiple instruments are being tracked.

history["Previous_Price"] = (
    history
    .groupby("Symbol")["Price"]
    .shift(1)
)

Calculate the absolute change:

history["Change"] = (
    history["Price"]
    - history["Previous_Price"]
)

25. Calculate Percentage Change

history["Change_%"] = (
    history["Change"]
    / history["Previous_Price"]
) * 100

The first observation for an instrument has no previous price, so its change will normally be missing.

26. Save the Updated Excel Ledger

history.to_excel(
    excel_file,
    index=False,
    sheet_name="Prices"
)

At this point, the Excel workbook contains the historical collection of automated observations.

27. Format the Excel Workbook with openpyxl

pandas is excellent for data processing, while openpyxl provides more direct workbook-level control.

from openpyxl import load_workbook

workbook = load_workbook(
    excel_file
)

worksheet = workbook[
    "Prices"
]

worksheet.freeze_panes = "A2"

worksheet.auto_filter.ref = (
    worksheet.dimensions
)

workbook.save(
    excel_file
)

28. Improve Excel Readability

for column in worksheet.columns:

    max_length = 0

    column_letter = (
        column[0].column_letter
    )

    for cell in column:

        value = (
            ""
            if cell.value is None
            else str(cell.value)
        )

        max_length = max(
            max_length,
            len(value)
        )

    worksheet.column_dimensions[
        column_letter
    ].width = (
        min(max_length + 2, 30)
    )

workbook.save(
    excel_file
)

29. Prepare the Daily Report Data

The PDF should summarize the latest information rather than dump the entire historical spreadsheet into the report.

latest_date = (
    history["Date"].max()
)

daily_data = history[
    history["Date"] == latest_date
].copy()

print(
    daily_data
)

30. Generate a PDF Report

The ReportLab library can create PDF documents directly from Python.

from reportlab.lib.pagesizes import A4
from reportlab.platypus import (
    SimpleDocTemplate,
    Paragraph,
    Spacer,
    Table,
    TableStyle
)
from reportlab.lib import colors
from reportlab.lib.styles import (
    getSampleStyleSheet
)

31. Create the PDF Document

pdf_file = (
    REPORT_DIR
    / "daily_market_report.pdf"
)

document = SimpleDocTemplate(
    str(pdf_file),
    pagesize=A4
)

styles = getSampleStyleSheet()

story = []

32. Add a Report Title

title = Paragraph(
    "Daily Market Price Report",
    styles["Title"]
)

story.append(
    title
)

story.append(
    Spacer(1, 12)
)

33. Add a Summary

summary = Paragraph(
    f"Report Date: "
    f"{latest_date.date()}",
    styles["Normal"]
)

story.append(
    summary
)

story.append(
    Spacer(1, 12)
)

34. Add Market Data to the PDF

Convert the DataFrame into a table structure.

report_columns = [
    "Symbol",
    "Name",
    "Price",
    "Previous_Price",
    "Change",
    "Change_%"
]

table_data = [
    report_columns
]

for _, row in daily_data.iterrows():

    table_data.append(
        [
            row.get("Symbol", ""),
            row.get("Name", ""),
            row.get("Price", ""),
            row.get(
                "Previous_Price",
                ""
            ),
            row.get("Change", ""),
            row.get("Change_%", "")
        ]
    )

35. Format the PDF Table

table = Table(
    table_data,
    repeatRows=1
)

table.setStyle(
    TableStyle(
        [
            (
                "BACKGROUND",
                (0, 0),
                (-1, 0),
                colors.lightgrey
            ),

            (
                "GRID",
                (0, 0),
                (-1, -1),
                0.5,
                colors.grey
            ),

            (
                "ALIGN",
                (2, 1),
                (-1, -1),
                "RIGHT"
            ),

            (
                "VALIGN",
                (0, 0),
                (-1, -1),
                "MIDDLE"
            )
        ]
    )
)

story.append(
    table
)

36. Build the PDF

document.build(
    story
)

The report is now available at:

reports/daily_market_report.pdf

37. Send the PDF by Email

Python's email package can construct the message, while smtplib communicates with the SMTP server.

import smtplib

from email.message import EmailMessage

38. Create the Email Message

message = EmailMessage()

message["Subject"] = (
    "Daily Market Price Report"
)

message["From"] = EMAIL_USER

message["To"] = (
    "recipient@example.com"
)

message.set_content(
    "Please find today's "
    "market price report attached."
)

39. Attach the PDF

with open(
    pdf_file,
    "rb"
) as file:

    pdf_data = file.read()

message.add_attachment(
    pdf_data,
    maintype="application",
    subtype="pdf",
    filename=pdf_file.name
)

40. Send the Email through SMTP

with smtplib.SMTP(
    EMAIL_HOST,
    EMAIL_PORT
) as server:

    server.starttls()

    server.login(
        EMAIL_USER,
        EMAIL_PASSWORD
    )

    server.send_message(
        message
    )

41. Put Email Logic into a Function

def send_email(
    pdf_file
):

    message = EmailMessage()

    message["Subject"] = (
        "Daily Market Price Report"
    )

    message["From"] = EMAIL_USER

    message["To"] = (
        "recipient@example.com"
    )

    message.set_content(
        "Today's automated "
        "market report is attached."
    )

    with open(
        pdf_file,
        "rb"
    ) as file:

        message.add_attachment(
            file.read(),
            maintype="application",
            subtype="pdf",
            filename=pdf_file.name
        )

    with smtplib.SMTP(
        EMAIL_HOST,
        EMAIL_PORT
    ) as server:

        server.starttls()

        server.login(
            EMAIL_USER,
            EMAIL_PASSWORD
        )

        server.send_message(
            message
        )

42. Divide the Automation into Functions

A professional automation should avoid putting every operation into one giant block of code.

def fetch_market_data():
    pass


def validate_data(data):
    pass


def update_excel(data):
    pass


def generate_pdf(data):
    pass


def send_email(pdf_file):
    pass


def main():
    pass

Each function should have one clear responsibility.

43. Connect the Pipeline

def main():

    logging.info(
        "Pipeline started."
    )

    data = (
        fetch_market_data()
    )

    validate_data(
        data
    )

    update_excel(
        data
    )

    pdf_file = (
        generate_pdf(
            data
        )
    )

    send_email(
        pdf_file
    )

    logging.info(
        "Pipeline completed."
    )


if __name__ == "__main__":

    main()

44. Add Global Error Handling

def main():

    try:

        logging.info(
            "Pipeline started."
        )

        data = (
            fetch_market_data()
        )

        validate_data(
            data
        )

        update_excel(
            data
        )

        pdf_file = (
            generate_pdf(
                data
            )
        )

        send_email(
            pdf_file
        )

        logging.info(
            "Pipeline completed."
        )

    except Exception as error:

        logging.exception(
            "Pipeline failed: %s",
            error
        )

        raise


if __name__ == "__main__":

    main()

logging.exception() records the exception and its traceback, making troubleshooting significantly easier.

45. Handle Temporary Network Failures

Network requests can fail temporarily. A production pipeline can use controlled retries.

import time
import requests


def fetch_with_retry(
    url,
    attempts=3
):

    for attempt in range(
        attempts
    ):

        try:

            response = (
                requests.get(
                    url,
                    timeout=20
                )
            )

            response.raise_for_status()

            return response

        except requests.RequestException:

            if (
                attempt
                == attempts - 1
            ):
                raise

            time.sleep(5)

Retries should be limited and should not be used to circumvent server rate limits.

46. Validate Before Updating the Ledger

One of the most important rules in automation is:

For example:

def validate_price(
    price
):

    if price is None:

        raise ValueError(
            "Price is missing."
        )

    if not isinstance(
        price,
        (int, float)
    ):

        raise TypeError(
            "Price must be numeric."
        )

    if price < 0:

        raise ValueError(
            "Price cannot be negative."
        )

    return True

47. Add a Basic Anomaly Check

A sudden large change may indicate a genuine event, but it may also indicate a scraping or parsing problem.

def check_price_change(
    current,
    previous
):

    if previous in (
        None,
        0
    ):

        return True

    change_pct = (
        (
            current - previous
        )
        / previous
    ) * 100

    if abs(
        change_pct
    ) > 50:

        logging.warning(
            "Large price movement: "
            "%.2f%%",
            change_pct
        )

    return True

The threshold is an example only. A real system should define thresholds according to the specific dataset and business context.

48. Complete Miniature Pipeline

The following example demonstrates the architecture without depending on a particular market website.

from pathlib import Path
from datetime import date
import logging

import pandas as pd


BASE_DIR = (
    Path(__file__)
    .resolve()
    .parent
)

DATA_DIR = (
    BASE_DIR / "data"
)

REPORT_DIR = (
    BASE_DIR / "reports"
)

LOG_DIR = (
    BASE_DIR / "logs"
)

DATA_DIR.mkdir(
    exist_ok=True
)

REPORT_DIR.mkdir(
    exist_ok=True
)

LOG_DIR.mkdir(
    exist_ok=True
)


logging.basicConfig(
    filename=(
        LOG_DIR
        / "automation.log"
    ),
    level=logging.INFO,
    format=(
        "%(asctime)s "
        "%(levelname)s "
        "%(message)s"
    )
)


def fetch_market_data():

    # Replace this educational
    # placeholder with data obtained
    # from an authorized public
    # source or official API.

    return {
        "Date": date.today(),
        "Symbol": "ABC",
        "Name": "Example Instrument",
        "Price": 125.40
    }


def update_excel(record):

    excel_file = (
        DATA_DIR
        / "market_prices.xlsx"
    )

    new_data = pd.DataFrame(
        [record]
    )

    if excel_file.exists():

        history = pd.read_excel(
            excel_file
        )

        history = pd.concat(
            [
                history,
                new_data
            ],
            ignore_index=True
        )

    else:

        history = new_data

    history = (
        history
        .drop_duplicates(
            subset=[
                "Date",
                "Symbol"
            ],
            keep="last"
        )
    )

    history.to_excel(
        excel_file,
        index=False
    )

    return history


def main():

    logging.info(
        "Pipeline started."
    )

    record = (
        fetch_market_data()
    )

    history = (
        update_excel(
            record
        )
    )

    logging.info(
        "Excel ledger updated."
    )

    print(
        history.tail()
    )

    logging.info(
        "Pipeline completed."
    )


if __name__ == "__main__":

    main()

Once this basic version works, add the PDF and email stages.

49. Extend the Pipeline to Multiple Instruments

A realistic daily report may track multiple instruments.

records = [

    {
        "Symbol": "AAA",
        "Name": "Instrument A",
        "Price": 125.40
    },

    {
        "Symbol": "BBB",
        "Name": "Instrument B",
        "Price": 82.15
    },

    {
        "Symbol": "CCC",
        "Name": "Instrument C",
        "Price": 214.70
    }

]

Convert the collection into a DataFrame:

df = pd.DataFrame(
    records
)

50. Create a Daily Summary

gainers = daily_data[
    daily_data["Change"] > 0
]

losers = daily_data[
    daily_data["Change"] < 0
]

unchanged = daily_data[
    daily_data["Change"] == 0
]

print(
    "Gainers:",
    len(gainers)
)

print(
    "Losers:",
    len(losers)
)

print(
    "Unchanged:",
    len(unchanged)
)

51. Useful Daily Report Metrics

52. Find the Largest Daily Movers

largest_gainers = (
    daily_data
    .sort_values(
        "Change_%",
        ascending=False
    )
    .head(5)
)

largest_losers = (
    daily_data
    .sort_values(
        "Change_%"
    )
    .head(5)
)

These tables can be included in the PDF report.

53. Create a More Useful Email Body

email_body = f"""
Daily Market Price Report

Report Date:
{latest_date.date()}

Instruments:
{len(daily_data)}

Gainers:
{len(gainers)}

Losers:
{len(losers)}

The detailed PDF report is
attached.
"""

message.set_content(
    email_body
)

The email itself should provide a concise summary while the PDF contains the detailed report.

54. What Can Go Wrong?

55. Design for Partial Failure

A professional pipeline should consider what happens if one stage fails.

For example:

Fetch Data
    ↓
Success
    ↓
Update Excel
    ↓
Success
    ↓
Generate PDF
    ↓
Success
    ↓
Email
    ↓
Failure

In this situation, the Excel and PDF outputs may already exist. The system should record the email failure rather than silently claiming that the entire pipeline succeeded.

56. Protect the Historical Ledger

A useful design principle is:

Download
   ↓
Validate
   ↓
Prepare
   ↓
Write

Do not write directly into the historical ledger before validation.

57. Create a Backup Before Updating Excel

For important historical files, create a backup before replacing the workbook.

import shutil

backup_file = (
    DATA_DIR
    / "market_prices_backup.xlsx"
)

if excel_file.exists():

    shutil.copy2(
        excel_file,
        backup_file
    )

For a production system, use a proper versioned backup strategy rather than retaining only one backup file.

58. Use Date-Based Report Names

report_date = (
    date.today()
    .isoformat()
)

pdf_file = (
    REPORT_DIR
    / f"market_report_{report_date}.pdf"
)

This prevents today's report from unnecessarily overwriting yesterday's report.

59. Recommended Report Archive

reports/
│
├── 2026-08-21/
│   └── market_report.pdf
│
├── 2026-08-22/
│   └── market_report.pdf
│
└── 2026-08-23/
    └── market_report.pdf

An archive structure makes historical reports easier to locate and audit.

60. Schedule the Pipeline

Once the pipeline works manually, connect it to the scheduling techniques from Lesson 6.1.

Windows

Task Scheduler
      ↓
Python Interpreter
      ↓
main.py

Linux / macOS

Cron
 ↓
Python Interpreter
 ↓
main.py

61. Windows Daily Automation

Suppose the project is stored at:

C:\Automation\market_automation\

The virtual environment's Python interpreter could be:

C:\Automation\market_automation\.venv\Scripts\python.exe

The main program:

C:\Automation\market_automation\main.py

Configure Task Scheduler to execute the virtual environment's interpreter with main.py as the argument.

62. Linux Cron Example

Suppose the project is stored at:

/home/user/market_automation/

A virtual environment interpreter might be:

/home/user/market_automation/.venv/bin/python

A daily 8:00 AM Cron entry could be:

0 8 * * * /home/user/market_automation/.venv/bin/python /home/user/market_automation/main.py >> /home/user/market_automation/logs/cron.log 2>&1

Use the correct paths and timezone configuration for the target machine.

63. Final Architecture

                OPERATING SYSTEM
                       │
                 Daily Scheduler
                       │
                       ▼
                  main.py
                       │
          ┌────────────┼────────────┐
          │            │            │
          ▼            ▼            ▼
       Fetch        Validate      Logging
       Data           Data
          │            │
          └──────┬─────┘
                 │
                 ▼
             pandas
                 │
                 ▼
          Excel Ledger
                 │
                 ▼
           Daily Analysis
                 │
                 ▼
           PDF Generator
                 │
                 ▼
            Email Sender
                 │
                 ▼
              Report

64. End-to-End Project Checklist

1. Select an authorized public market-data source.
2. Verify applicable usage rules.
3. Create the project structure.
4. Create a virtual environment.
5. Install dependencies.
6. Configure logging.
7. Implement data retrieval.
8. Validate HTTP responses.
9. Parse the required data.
10. Validate extracted values.
11. Create the daily record.
12. Read the historical Excel ledger.
13. Add the new record.
14. Prevent accidental duplicates.
15. Calculate price changes.
16. Save the updated workbook.
17. Generate a PDF report.
18. Attach the PDF to an email.
19. Send the email through an approved SMTP configuration.
20. Handle errors.
21. Log execution results.
22. Test the complete pipeline manually.
23. Schedule daily execution.
24. Verify the scheduled execution.

65. Interview Questions

66. Examination Questions — MCQs

67. Practical Examination Tasks

68. Advanced Extensions

Once the basic project works, add more advanced capabilities.

• Track multiple instruments.
• Use an official market-data API.
• Store data in SQLite or PostgreSQL.
• Generate charts inside the PDF.
• Add rolling averages.
• Add historical trend analysis.
• Generate an HTML report.
• Add email failure notifications.
• Add structured JSON logging.
• Use a secrets manager.
• Add automated tests.
• Add data-quality checks.
• Deploy the pipeline to a server.
• Use a workflow orchestrator for larger systems.

69. Advanced PDF — Add a Chart

Generate a chart with Matplotlib first:

import matplotlib.pyplot as plt

plt.figure(
    figsize=(8, 4)
)

plt.plot(
    history["Date"],
    history["Price"]
)

plt.title(
    "Historical Price"
)

plt.xlabel(
    "Date"
)

plt.ylabel(
    "Price"
)

plt.xticks(
    rotation=45
)

plt.tight_layout()

chart_file = (
    REPORT_DIR
    / "price_trend.png"
)

plt.savefig(
    chart_file,
    dpi=200
)

plt.close()

70. Add the Chart to the PDF

from reportlab.platypus import Image

chart = Image(
    str(chart_file),
    width=450,
    height=250
)

story.append(
    Spacer(1, 20)
)

story.append(
    chart
)

The final PDF can therefore contain both tabular information and visual analytics.

71. Production-Oriented Architecture

             Scheduler
                 │
                 ▼
             main.py
                 │
        ┌────────┴────────┐
        │                 │
        ▼                 ▼
 Configuration         Logging
        │
        ▼
 Data Acquisition
        │
        ▼
 Data Validation
        │
        ▼
 Data Processing
        │
   ┌────┴────┐
   ▼         ▼
Excel      Analytics
Ledger        │
   │          ▼
   │       PDF Report
   │          │
   └────┬─────┘
        ▼
      Email
        │
        ▼
   Execution Log

72. Security Checklist

• ☐ Never hard-code email passwords.
• ☐ Never commit credentials to Git.
• ☐ Use environment variables or a secrets manager.
• ☐ Use HTTPS where supported.
• ☐ Validate external data.
• ☐ Do not execute downloaded content as code.
• ☐ Respect source access policies.
• ☐ Use timeouts on network requests.
• ☐ Avoid unrestricted retry loops.
• ☐ Restrict file permissions where appropriate.
• ☐ Protect generated reports if they contain sensitive information.

73. Debugging Strategy

Do not debug the entire pipeline simultaneously.

Test each stage independently.

Test 1
Fetch data
   ↓
Works?

Test 2
Parse data
   ↓
Works?

Test 3
Validate data
   ↓
Works?

Test 4
Update Excel
   ↓
Works?

Test 5
Generate PDF
   ↓
Works?

Test 6
Send email
   ↓
Works?

Test 7
Run complete pipeline
   ↓
Works?

74. Monitoring an Automated Pipeline

After deployment, do not assume that automation will work forever.

Monitor:

• Execution success rate
• Network failures
• Unexpected source changes
• Missing records
• Invalid values
• Excel write failures
• PDF generation failures
• Email delivery failures
• Disk-space problems

75. Final Capstone Challenge

Build the complete system independently.

Minimum Requirements

1. Select an authorized public market-data source.
2. Collect at least five instruments.
3. Execute the process automatically once per day.
4. Store historical observations in Excel.
5. Calculate previous price and percentage change.
6. Generate a PDF summary.
7. Include at least one chart.
8. Email the PDF automatically.
9. Implement exception handling.
10. Implement logging.
11. Prevent duplicate daily observations.
12. Protect credentials.
13. Document the complete setup.

76. Project Evaluation Rubric

77. Interview Scenario

Strong Investigation Approach

1. Check the raw HTTP response.
2. Verify the HTML structure.
3. Verify the CSS selector.
4. Print or log the extracted text.
5. Validate the parsed numerical value.
6. Compare the source value with the stored value.
7. Check whether the website changed its structure.
8. Review recent code or dependency changes.
9. Inspect historical logs.
10. Add stronger validation before writing future records.

This scenario demonstrates why validation, logging, and source monitoring are essential in automation.

78. Long-Answer Examination Questions

1. Explain the architecture of an end-to-end Python automation pipeline.
2. Explain how requests and BeautifulSoup can be combined for permitted HTML data extraction.
3. Explain how pandas and openpyxl can work together in an Excel automation project.
4. Describe the steps involved in generating a PDF report using Python.
5. Explain how Python can send an email with a PDF attachment.
6. Why is exception handling particularly important in unattended automation?
7. Explain why API-based data collection may be preferable to HTML scraping.
8. Describe at least five security considerations for an automated email-reporting system.
9. Explain how a Python automation pipeline can be scheduled to run every day.
10. Design a monitoring strategy for a daily data automation system.

79. Quick Reference

80. Self-Assessment Checklist

Before completing the Python for Data Science & Automation course, make sure you can:

• ☐ Explain an end-to-end automation pipeline.
• ☐ Retrieve data using requests.
• ☐ Parse HTML using BeautifulSoup.
• ☐ Validate external data.
• ☐ Use pandas for data processing.
• ☐ Maintain a historical Excel ledger.
• ☐ Use openpyxl for workbook operations.
• ☐ Calculate price changes.
• ☐ Generate PDF reports.
• ☐ Add tables to PDF reports.
• ☐ Add charts to PDF reports.
• ☐ Send email with attachments.
• ☐ Configure SMTP securely.
• ☐ Use environment variables for credentials.
• ☐ Implement logging.
• ☐ Implement exception handling.
• ☐ Handle network failures.
• ☐ Prevent duplicate records.
• ☐ Schedule the pipeline.
• ☐ Troubleshoot unattended execution.
• ☐ Document an automation project.
• ☐ Design a maintainable automation architecture.