Batch Excel Cleaning & Automated Chart Generation with WorkBuddy

Real-World Pain Points

Manual spreadsheet reporting typically suffers from three major friction points:

1. Inconsistent Column Structures: Different departments or export tools output conflicting column headers;
2. Dirty Data: Mixed date formats, blank cells, and numerical values formatted as text strings;
3. Time-Consuming Charting: Recreating and styling charts manually inside Excel takes hours.

With WorkBuddy, you can instruct the agent in plain English. It inspects your files, generates verified Python scripts (using pandas and matplotlib), and produces cleansed datasets and high-resolution charts in seconds.

---

The Automated Data Flow

flowchart LR
    A[Raw Excel / CSV Files] --> B[WorkBuddy Semantic Agent]
    subgraph Execution[Local Execution Sandbox]
        B --> C[Pandas Header Alignment & Date Normalization]
        C --> D[Missing Value Imputation & Outlier Trimming]
        D --> E[Multi-Sheet Merging & Pivot Aggregation]
    end
    E --> F[Standardized clean_sales.xlsx]
    E --> G[Visual Presentation chart_trend.png]

---

3-Step Execution Prompts

Step 1: Schema Discovery & Header Alignment

Place raw workbooks in ./data/raw/ and instruct WorkBuddy:

Inspect all .xlsx files in ./data/raw/:
1. Summarize row counts, column names, and display 3 sample rows from each;
2. Identify synonyms (e.g., "Revenue" vs "Gross Sales");
3. Propose a unified column schema without modifying files yet.

Step 2: Automated Cleaning & Merging

Once the schema is confirmed:

Using the approved schema:
1. Standardize all dates to YYYY-MM-DD;
2. Strip currency symbols ($ / €) and cast amounts to floats;
3. Fill missing customer locations with "Unknown";
4. Merge all rows into ./output/clean_sales_2026.xlsx.

Step 3: Visualization & Dashboard Generation

Based on ./output/clean_sales_2026.xlsx:
1. Group monthly sales volume by product category;
2. Plot a modern dual-axis chart (bar + line trend) using matplotlib;
3. Apply a clean minimalist dark theme;
4. Save as a 300dpi image at ./output/chart_trend.png.

---

Practical Troubleshooting Checklist

Issue	Cause	Fix
Large Files (>100MB) Crashing Memory	Loading entire dataset at once	Instruct WorkBuddy to use pd.read_excel(..., chunksize=10000)
Uncalculated Formulas (Empty Reads)	openpyxl default behavior	Use load_workbook(..., data_only=True) to read cached formula values
Encoding Artifacts	Mixed UTF-8 and Latin-1 CSVs	Let WorkBuddy detect encodings using Python’s chardet before reading