Stop wasting time on Excel. Learn how to write a simple Python script to fetch, clean, and email your daily business metrics.
Stop the Manual Grind
If you’re still copying and pasting data into Excel every morning to create reports, you’re wasting valuable time. Let’s automate this process with Python and Pandas.
The Setup
First, install the required packages:
pip install pandas openpyxl smtplib
Fetching Your Data
Whether it’s from a database, API, or CSV file, Pandas makes it easy:
import pandas as pd
import requests
# From CSV
df = pd.read_csv('sales_data.csv')
# From API
response = requests.get('https://api.example.com/metrics')
df = pd.DataFrame(response.json())
# From database
import sqlalchemy
engine = sqlalchemy.create_engine('postgresql://user:pass@localhost/db')
df = pd.read_sql('SELECT * FROM sales WHERE date = CURRENT_DATE', engine)
Data Cleaning
Clean and transform your data with Pandas:
# Remove duplicates
df = df.drop_duplicates()
# Handle missing values
df['revenue'] = df['revenue'].fillna(0)
# Convert types
df['date'] = pd.to_datetime(df['date'])
# Calculate metrics
df['profit_margin'] = (df['revenue'] - df['cost']) / df['revenue'] * 100
Creating the Report
Generate a formatted Excel report:
from openpyxl.styles import Font, PatternFill
# Create summary statistics
summary = df.groupby('category').agg({
'revenue': 'sum',
'units_sold': 'sum',
'profit_margin': 'mean'
}).round(2)
# Write to Excel with formatting
with pd.ExcelWriter('daily_report.xlsx', engine='openpyxl') as writer:
summary.to_excel(writer, sheet_name='Summary')
df.to_excel(writer, sheet_name='Raw Data', index=False)
# Format the summary sheet
workbook = writer.book
worksheet = workbook['Summary']
# Style headers
for cell in worksheet[1]:
cell.font = Font(bold=True)
cell.fill = PatternFill(start_color="366092", fill_type="solid")
Automating Email Delivery
Send the report via email automatically:
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
def send_report(to_email, filename):
msg = MIMEMultipart()
msg['From'] = 'reports@company.com'
msg['To'] = to_email
msg['Subject'] = f'Daily Sales Report - {pd.Timestamp.now().date()}'
# Attach the file
with open(filename, 'rb') as f:
part = MIMEBase('application', 'octet-stream')
part.set_payload(f.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', f'attachment; filename={filename}')
msg.attach(part)
# Send email
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login('your@email.com', 'your_password')
server.send_message(msg)
server.quit()
send_report('manager@company.com', 'daily_report.xlsx')
Schedule It
Use cron (Linux/Mac) or Task Scheduler (Windows) to run this script daily:
# Cron example - runs at 9 AM every day
0 9 * * * /usr/bin/python3 /path/to/report_script.py
Conclusion
With this automated system, you’ll save hours each week and ensure consistent, accurate reporting. Your morning coffee just got a lot more enjoyable!
