Turn Spreadsheets into Visual Dashboards

Want to visually represent your data from spreadsheets in a dashboard?

I built a free, lightweight Python script that is designed to automatically generate interactive dashboards from your data files (CSV or Excel). Instead of manually writing code to build charts, you simply provide:

• A CSV or XLSX file containing your dataset.
• A short instruction or description of the type of charts/visuals you want.
• (Optional) The name of the output HTML file.

The script then uses the power of an LLM (Large Language Model) combined with Plotly + Dash to:

• Analyze your dataset.
• Dynamically generate Python plotting code tailored to your file.
• Render a fully interactive dashboard in your browser, where you can explore charts, hover for tooltips, zoom in/out, and filter your data.

Benefits

• No coding required – just describe what you want, and the script does the heavy lifting.
• Supports multiple data formats (CSV and Excel).
• Customizable output – you control the look and type of charts through natural language instructions.
• Interactive dashboards – unlike static charts, you can drill down into your data dynamically.
• Time-saving – great for analysts, small businesses, or anyone who needs to quickly visualize data without building a full BI solution.

Python Script:

import os
import sys
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import openai
from openai import OpenAI
import json
import traceback
import webbrowser
import threading
import itertools
import time

# --------------------------
# CONFIG: Set your API Key
# --------------------------
API_KEY = os.getenv("OPENAI_API_KEY")
client = OpenAI(api_key=API_KEY)
openai.api_key = API_KEY

# --------------------------
# Spinner helper for terminal
# --------------------------
class Spinner:
    def __init__(self, message="Processing"):
        self.message = message
        self._running = False
        self._thread = None

    def start(self):
        self._running = True
        self._thread = threading.Thread(target=self._spin)
        self._thread.start()

    def _spin(self):
        for c in itertools.cycle('|/-\\'):
            if not self._running:
                break
            print(f'\r{self.message}... {c}', end='', flush=True)
            time.sleep(0.1)
        print('\r', end='', flush=True)

    def stop(self):
        self._running = False
        if self._thread is not None:
            self._thread.join()

# --------------------------
# Helper functions
# --------------------------
def sanitize_dataframe(df: pd.DataFrame) -> pd.DataFrame:
    """Convert dict/list columns to strings to avoid unhashable errors."""
    df_copy = df.copy()
    for col in df_copy.columns:
        if df_copy[col].apply(lambda x: isinstance(x, (dict, list))).any():
            df_copy[col] = df_copy[col].astype(str)
    return df_copy

def generate_dashboard_code(df: pd.DataFrame, instructions: str = "", error_feedback: str = None) -> str:
    """
    Ask the LLM to generate Python code for a dashboard given a DataFrame.
    If error_feedback is provided, the LLM will attempt to fix the previous code.
    """
    df = sanitize_dataframe(df)
    schema = df.dtypes.apply(lambda x: str(x)).to_dict()
    row_count = len(df)

    # Safely handle categorical summary
    cat_summary = {}
    if any(df.select_dtypes(include=["object", "category"]).columns):
        cat_summary = {
            col: df[col].unique()[:5].tolist()
            for col in df.select_dtypes(include=["object", "category"]).columns
        }

    # Safely handle numeric summary
    if any(df.select_dtypes(include="number").columns):
        num_summary = df.describe(include="number").to_dict()
    else:
        num_summary = {}

    sample_rows = df.head(5).to_dict(orient="records")

    description = f"""
Dataset schema: {schema}
Row count: {row_count}
Sample rows: {sample_rows}
Categorical example values: {cat_summary}
Numeric summary stats: {num_summary}
"""

    error_note = (
        f"\nPrevious code failed with error:\n{error_feedback}\nPlease fix it and ensure it runs correctly with 'df'."
        if error_feedback
        else ""
    )

    prompt = f"""
You are a Python data visualization assistant.
Generate Python code using matplotlib, plotly, or dash that creates an HTML dashboard visualization of the DataFrame 'df'.
Use 'df' directly; do not invent totals.
If you generate pie charts in subplots, use specs=[{{'type':'domain'}}].

{description}

Extra instructions: {instructions}
{error_note}

Requirements:
- Output must be a variable called 'html_output' containing HTML
- Do not call plt.show() or app.run_server()
- Be fully self-contained and executable
"""

    response = openai.chat.completions.create(
        model="gpt-4.1",
        messages=[
            {"role": "system", "content": "You generate raw Python code with no markdown formatting or comments. Return only executable code."},
            {"role": "user", "content": prompt},
        ],
        response_format={"type": "text"}
    )

    return response.choices[0].message.content


def create_dashboard(df: pd.DataFrame, instructions: str = "", output_file: str = "dashboard.html", max_retries: int = 3):
    """
    Generate dashboard HTML by running the code from the LLM safely.
    Retries if execution fails, sending the traceback back to the LLM.
    """
    df = sanitize_dataframe(df)
    error_feedback = None

    for attempt in range(1, max_retries + 1):
        spinner = Spinner("Generating dashboard code with LLM")
        spinner.start()
        try:
            code = generate_dashboard_code(df, instructions, error_feedback)
        finally:
            spinner.stop()

        print(f"\n--- Attempt {attempt} ---\nGenerated code:\n{code}\n")
        
        local_vars = {"df": df, "pd": pd, "px": px, "go": go}
        try:
            exec(code, globals(), local_vars)
            html_output = local_vars.get("html_output", "No dashboard generated")
            with open(output_file, "w", encoding="utf-8") as f:
                f.write(html_output)
            print(f"✅ Dashboard saved to {output_file}")

            # Auto-open in default browser
            abs_path = os.path.abspath(output_file)
            webbrowser.open(f"file://{abs_path}")
            print(f"🌐 Dashboard opened in browser: {abs_path}")

            return html_output
        except Exception:
            error_feedback = traceback.format_exc()
            print(f"⚠️ Attempt {attempt} failed with error:\n{error_feedback}\n")

    print("❌ All attempts failed. Generated code could not be executed.")
    return "Dashboard generation failed."


# --------------------------
# Main: handle command-line arguments
# --------------------------
if __name__ == "__main__":
    if len(sys.argv) < 3:
        print("Usage: python generate_charts.py   [output_file]")
        sys.exit(1)

    file_path = sys.argv[1]
    instructions = sys.argv[2]
    output_file = sys.argv[3] if len(sys.argv) >= 4 else "dashboard.html"

    # Load file depending on extension
    if file_path.lower().endswith(".csv"):
        df = pd.read_csv(file_path)
    elif file_path.lower().endswith((".xlsx", ".xls")):
        df = pd.read_excel(file_path)
    else:
        print("Unsupported file type. Please provide a CSV or Excel file.")
        sys.exit(1)

    # Sanitize
    df = sanitize_dataframe(df)

    # Generate dashboard
    create_dashboard(df, instructions=instructions, output_file=output_file, max_retries=3)