I Replaced 4 Excel Files with 30 Lines of Python — Here's What It Cost and What I Gained

🎯 Situation

Every Monday morning, an operations analyst at one of our clients opened four Excel files — one per region — copied the data into a master spreadsheet, cleaned up the column names that never matched, removed the blank rows at the top of each file, and formatted the result before loading it into Power BI manually.

It took about 90 minutes. Every single week. For two years.

One Python script changed all of that.

⚠️ Challenge

The manual process had three real problems that compounded over time.

None of these were catastrophic individually. Together, they represented a fragile, human-dependent process sitting at the foundation of the company's weekly reporting.

🔍 The Script

Here's the actual code — slightly simplified, but close to what we deployed. It uses pandas, Python's standard data manipulation library, and glob to find files automatically:

import pandas as pd
import glob

# Read all 4 Excel files from a folder
files = glob.glob('data/*.xlsx')
df = pd.concat([pd.read_excel(f) for f in files], ignore_index=True)

# Standardize column names
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')

# Clean and filter
df['date'] = pd.to_datetime(df['date'], errors='coerce')
df['revenue'] = pd.to_numeric(df['revenue'], errors='coerce').fillna(0)
df = df[df['date'].notna()]

# Aggregate by month and region
summary = df.groupby([df['date'].dt.to_period('M'), 'region'])['revenue'].sum().reset_index()
summary.columns = ['month', 'region', 'total_revenue']

# Export clean file for Power BI
summary.to_csv('output/monthly_revenue.csv', index=False)
print(f"Done. {len(summary)} rows exported.")

That's 28 lines. What it does:

• Finds all Excel files automatically — drop a new file in the folder and it picks it up next run
• Standardizes column names — lowercase, no spaces, no trailing whitespace — regardless of who created the file
• Handles dirty data — non-numeric revenue values become 0, unparseable dates are removed cleanly
• Aggregates and exports — one clean CSV that Power BI reads on a scheduled refresh

✓️ What It Cost and What It Gained

Writing and testing this script took about 3 hours. That's a one-time investment.

• Time saved: 90 min/week × 52 weeks = 78 hours/year returned to the analyst
• Error rate: dropped to zero for consolidation errors — the script either runs correctly or fails loudly
• Resilience: the analyst can be on vacation. The report still runs.
• Onboarding: a new analyst needs to understand one script, not a 12-step Excel process
• Extensibility: adding a 5th region means dropping a new file in the folder — nothing else changes

The only ongoing cost: if the source file structure changes significantly (new columns, renamed sheets), the script needs a small update. In two years, that happened once.