How to Build a Data Dashboard Without Manual Excel Work

pip install pandas openpyxl sqlalchemy

import pandas as pd
import sqlite3
from datetime import datetime

class DataSource:
    """Load and validate data from different source types."""

    @staticmethod
    def from_excel(filepath, sheet_name=0):
        """Load from Excel file."""
        df = pd.read_excel(filepath, sheet_name=sheet_name)
        print(f"Loaded {len(df)} rows from {filepath}")
        return df

    @staticmethod
    def from_csv(filepath):
        """Load from CSV file."""
        df = pd.read_csv(filepath)
        print(f"Loaded {len(df)} rows from {filepath}")
        return df

    @staticmethod
    def from_database(db_path, query):
        """Load from SQLite database."""
        conn = sqlite3.connect(db_path)
        df = pd.read_sql(query, conn)
        conn.close()
        print(f"Loaded {len(df)} rows from database")
        return df

    @staticmethod
    def from_api(url, headers=None, params=None):
        """Load from REST API."""
        import requests
        response = requests.get(url, headers=headers, params=params, timeout=30)
        response.raise_for_status()
        df = pd.DataFrame(response.json().get("results", response.json()))
        print(f"Loaded {len(df)} rows from API")
        return df

def load_all_sources():
    """Load data from all configured sources."""
    # Sales data from Excel (exported weekly by finance)
    sales = DataSource.from_excel("data/weekly_sales.xlsx")

    # Product catalog from database
    products = DataSource.from_database(
        "data/catalog.db",
        "SELECT id, name, category, cost_price FROM products",
    )

    # Customer data from CSV export
    customers = DataSource.from_csv("data/customers.csv")

    return {"sales": sales, "products": products, "customers": customers}

def clean_sales(df):
    """Clean sales data."""
    # Standardise column names
    df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")

    # Parse dates
    df["date"] = pd.to_datetime(df["date"], errors="coerce")

    # Remove rows with missing critical fields
    df = df.dropna(subset=["date", "amount", "product_id"])

    # Fix types
    df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
    df["quantity"] = pd.to_numeric(df["quantity"], errors="coerce").fillna(1).astype(int)

    # Remove duplicates
    df = df.drop_duplicates(subset=["order_id"], keep="last")

    # Remove negative amounts (returns handled separately)
    df = df[df["amount"] > 0]

    print(f"Cleaned sales: {len(df)} rows")
    return df


def clean_products(df):
    """Clean product catalog."""
    df.columns = df.columns.str.strip().str.lower()
    df = df.dropna(subset=["id", "name"])
    df = df.drop_duplicates(subset=["id"])
    df["cost_price"] = pd.to_numeric(df["cost_price"], errors="coerce").fillna(0)
    return df


def clean_customers(df):
    """Clean customer data."""
    df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")
    df = df.dropna(subset=["customer_id"])
    df = df.drop_duplicates(subset=["customer_id"], keep="last")
    return df

def build_dashboard_dataset(sources):
    """Combine all sources into a single enriched dataset."""
    sales = clean_sales(sources["sales"])
    products = clean_products(sources["products"])
    customers = clean_customers(sources["customers"])

    # Enrich sales with product details
    enriched = sales.merge(
        products[["id", "name", "category", "cost_price"]],
        left_on="product_id",
        right_on="id",
        how="left",
        suffixes=("", "_product"),
    )

    # Enrich with customer details
    enriched = enriched.merge(
        customers[["customer_id", "region", "segment"]],
        on="customer_id",
        how="left",
    )

    # Calculate derived metrics
    enriched["margin"] = enriched["amount"] - (enriched["cost_price"] * enriched["quantity"])
    enriched["margin_pct"] = (enriched["margin"] / enriched["amount"] * 100).round(1)
    enriched["month"] = enriched["date"].dt.to_period("M").astype(str)
    enriched["week"] = enriched["date"].dt.isocalendar().week.astype(int)

    print(f"Dashboard dataset: {len(enriched)} rows, {len(enriched.columns)} columns")
    return enriched

def generate_dashboard_views(df):
    """Generate pre-aggregated views for dashboard consumption."""
    views = {}

    # Revenue by month
    views["monthly_revenue"] = (
        df.groupby("month")
        .agg(
            revenue=("amount", "sum"),
            orders=("order_id", "count"),
            margin=("margin", "sum"),
            avg_order=("amount", "mean"),
        )
        .reset_index()
        .round(2)
    )

    # Revenue by category
    views["category_performance"] = (
        df.groupby("category")
        .agg(
            revenue=("amount", "sum"),
            orders=("order_id", "count"),
            margin=("margin", "sum"),
            avg_margin_pct=("margin_pct", "mean"),
        )
        .reset_index()
        .sort_values("revenue", ascending=False)
        .round(2)
    )

    # Revenue by region
    views["regional_breakdown"] = (
        df.groupby("region")
        .agg(
            revenue=("amount", "sum"),
            customers=("customer_id", "nunique"),
            orders=("order_id", "count"),
        )
        .reset_index()
        .sort_values("revenue", ascending=False)
        .round(2)
    )

    # Top products
    views["top_products"] = (
        df.groupby("name")
        .agg(
            revenue=("amount", "sum"),
            units=("quantity", "sum"),
            margin=("margin", "sum"),
        )
        .reset_index()
        .sort_values("revenue", ascending=False)
        .head(20)
        .round(2)
    )

    # Daily trend (for line chart)
    views["daily_trend"] = (
        df.groupby(df["date"].dt.date)
        .agg(revenue=("amount", "sum"), orders=("order_id", "count"))
        .reset_index()
        .rename(columns={"date": "day"})
        .round(2)
    )

    return views

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

def export_dashboard_excel(views, output_path):
    """Export all dashboard views to Excel."""
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        for name, df in views.items():
            sheet_name = name.replace("_", " ").title()[:31]  # Excel sheet name limit
            df.to_excel(writer, sheet_name=sheet_name, index=False)

    print(f"Dashboard data exported: {output_path}")

import os

def export_dashboard_csv(views, output_dir):
    """Export each view as a separate CSV for dashboard ingestion."""
    os.makedirs(output_dir, exist_ok=True)

    for name, df in views.items():
        path = os.path.join(output_dir, f"{name}.csv")
        df.to_csv(path, index=False)

    print(f"Exported {len(views)} CSV files to {output_dir}")

def export_dashboard_db(views, db_path):
    """Write dashboard views to database tables for live querying."""
    conn = sqlite3.connect(db_path)

    for name, df in views.items():
        df.to_sql(name, conn, if_exists="replace", index=False)

    conn.close()
    print(f"Written {len(views)} tables to {db_path}")

def run_dashboard_pipeline():
    """Complete pipeline: load → clean → combine → aggregate → export."""
    print(f"Pipeline started: {datetime.now().isoformat()}")

    # Load
    sources = load_all_sources()

    # Build enriched dataset
    dataset = build_dashboard_dataset(sources)

    # Generate views
    views = generate_dashboard_views(dataset)

    # Export
    timestamp = datetime.now().strftime("%Y%m%d")
    export_dashboard_excel(views, f"dashboard_data_{timestamp}.xlsx")
    export_dashboard_csv(views, "dashboard_output")
    export_dashboard_db(views, "dashboard.db")

    print(f"Pipeline complete: {len(dataset)} records processed into {len(views)} views")

if __name__ == "__main__":
    run_dashboard_pipeline()

Before:
  Finance team exports 3 CSVs → opens Excel → vlookup across sheets →
  pivot tables → copy to PowerPoint → present → repeat next week

After:
  Cron job runs at 6 AM Monday →
  Python loads all 3 sources → cleans → aggregates →
  writes to database → dashboard tool refreshes automatically →
  team opens dashboard at 9 AM → done