By 9:07 AM….. all my weekly reports were generated….. formatted….. and sitting in my manager's inbox. It used to take me four hours every Monday morning. Coffee is getting cold. Spreadsheets multiplying like rabbits. Copy….. paste….. format….. repeat until I wanted to throw my laptop out the window.
Then I spent one weekend learning about Python automation.
Four hours became four minutes. And I spent those four hours doing actual work instead of being a human copy-paste machine.
Here is the truth nobody tells you about programming… the real power is not in building the next groundbreaking app. The real power is in automating the soul-crushing repetitive garbage that eats your life one hour at a time.
These seven Python libraries turned me into a wizard at work. Not because I am smarter than everyone else… but because I stopped doing things manually like some kind of medieval peasant.
Openpyxl….. Because Excel Should Work For You
I had 47 Excel files to update every week.
Same columns. Same formulas. Same formatting. Just different data. I would open each one… update the numbers… recalculate totals… save… move to the next one. Three hours. Every. Single. Week.
Then someone told me about openpyxl.
You know what changed? Everything.
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from pathlib import Path
import pandas as pd
class ExcelAutomation:
def __init__(self, template_path):
self.template_path = template_path
def batch_update_reports(self, data_dict, output_folder):
"""
Update multiple Excel files with new data automatically
"""
output_folder = Path(output_folder)
output_folder.mkdir(exist_ok=True)
for report_name, data in data_dict.items():
# Load template
wb = load_workbook(self.template_path)
ws = wb.active
# Update data starting from row 2 (assuming row 1 is headers)
for row_idx, row_data in enumerate(data, start=2):
for col_idx, value in enumerate(row_data, start=1):
cell = ws.cell(row=row_idx, column=col_idx)
cell.value = value
# Auto-format numbers
if isinstance(value, (int, float)):
cell.number_format = '#,##0.00'
# Auto-calculate totals
last_row = ws.max_row
total_row = last_row + 1
ws.cell(row=total_row, column=1).value = "TOTAL"
ws.cell(row=total_row, column=1).font = Font(bold=True)
# Sum numeric columns
for col in range(2, ws.max_column + 1):
col_letter = get_column_letter(col)
formula = f"=SUM({col_letter}2:{col_letter}{last_row})"
total_cell = ws.cell(row=total_row, column=col)
total_cell.value = formula
total_cell.font = Font(bold=True)
total_cell.fill = PatternFill(start_color="FFFF00", fill_type="solid")
# Auto-adjust column widths
for column in ws.columns:
max_length = 0
column_letter = get_column_letter(column[0].column)
for cell in column:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
adjusted_width = min(max_length + 2, 50)
ws.column_dimensions[column_letter].width = adjusted_width
# Save with timestamp
output_path = output_folder / f"{report_name}_{pd.Timestamp.now().strftime('%Y%m%d')}.xlsx"
wb.save(output_path)
print(f"Generated report..... {output_path.name}")
def create_summary_dashboard(self, reports_folder, output_file):
"""
Consolidate multiple reports into one dashboard
"""
reports_folder = Path(reports_folder)
all_data = []
# Read all Excel files in folder
for excel_file in reports_folder.glob("*.xlsx"):
df = pd.read_excel(excel_file)
df['Source_File'] = excel_file.stem
all_data.append(df)
# Combine all data
combined_df = pd.concat(all_data, ignore_index=True)
# Create workbook with multiple sheets
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
# Summary sheet
combined_df.to_excel(writer, sheet_name='All_Data', index=False)
# Pivot analysis
if 'Category' in combined_df.columns and 'Amount' in combined_df.columns:
pivot = combined_df.pivot_table(
values='Amount',
index='Category',
aggfunc=['sum', 'mean', 'count']
)
pivot.to_excel(writer, sheet_name='Summary_Analysis')
# Format the workbook
workbook = writer.book
for sheet_name in workbook.sheetnames:
worksheet = workbook[sheet_name]
# Format header row
for cell in worksheet[1]:
cell.font = Font(bold=True, color="FFFFFF")
cell.fill = PatternFill(start_color="366092", fill_type="solid")
cell.alignment = Alignment(horizontal='center')
print(f"Dashboard created..... {output_file}")
class SmartExcelReader:
@staticmethod
def read_messy_excel(file_path, expected_columns=None):
"""
Read Excel files even when they are formatted weirdly
"""
# Try to find where the actual data starts
df_preview = pd.read_excel(file_path, nrows=20, header=None)
# Find the row with the most non-null values (likely the header)
non_null_counts = df_preview.count(axis=1)
header_row = non_null_counts.idxmax()
# Read the actual data
df = pd.read_excel(file_path, header=header_row)
# Clean column names
df.columns = df.columns.str.strip().str.replace('\n', ' ')
# Remove completely empty rows
df = df.dropna(how='all')
# Remove rows where all values are the same (often formatting rows)
df = df[df.nunique(axis=1) > 1]
return df
@staticmethod
def extract_data_from_formatted_report(file_path, section_marker):
"""
Extract specific sections from heavily formatted Excel reports
"""
wb = load_workbook(file_path)
ws = wb.active
extracted_data = []
capturing = False
for row in ws.iter_rows(values_only=True):
# Start capturing when we find the marker
if any(section_marker in str(cell) for cell in row if cell):
capturing = True
continue
# Stop when we hit an empty row or another section
if capturing:
if all(cell is None for cell in row):
break
# Only capture rows with actual data
if any(cell is not None for cell in row):
extracted_data.append(row)
return pd.DataFrame(extracted_data)
# Example usage - the code that saved me 3 hours per week
automation = ExcelAutomation("report_template.xlsx")
# Simulate weekly data
weekly_data = {
"Sales_Report": [
["Product A", 1200, 450, 750],
["Product B", 800, 320, 480],
["Product C", 1500, 600, 900]
],
"Inventory_Report": [
["Item X", 500, 320, 180],
["Item Y", 750, 500, 250],
["Item Z", 1000, 800, 200]
]
}
# This one command replaces 3 hours of manual work
# automation.batch_update_reports(weekly_data, "output_reports")
print("Excel automation configured")
print("What used to take 3 hours now takes 3 minutes")The first time I ran this….. I just sat there staring at my screen. Forty-seven reports. Generated. Formatted. Perfect. In the time it takes to make coffee.
I felt like I had discovered fire.
Schedule….. The Library That Makes You Look Superhuman
My manager thought I was working at 6 AM.
I was not. A Python script was.
Every morning at 6 AM, automated reports would generate and email themselves. By the time my manager got to the office at 8… everything was already done. He thought I was the most dedicated employee in the company.
I was the only one who knew about the schedule library.
import schedule
import time
from datetime import datetime
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import pandas as pd
class AutomatedTaskScheduler:
def __init__(self):
self.tasks = []
def add_daily_report(self, time_str, report_function, *args):
"""
Schedule a daily report to run automatically
"""
schedule.every().day.at(time_str).do(report_function, *args)
print(f"Scheduled daily report at {time_str}")
def add_weekly_report(self, day, time_str, report_function, *args):
"""
Schedule a weekly report
day can be..... 'monday', 'tuesday', etc.
"""
getattr(schedule.every(), day.lower()).at(time_str).do(report_function, *args)
print(f"Scheduled weekly report for {day} at {time_str}")
def run_scheduler(self):
"""
Keep the scheduler running
"""
print("Scheduler started. Press Ctrl+C to stop.")
while True:
schedule.run_pending()
time.sleep(60) # Check every minute
class EmailAutomation:
def __init__(self, smtp_server, smtp_port, sender_email, password):
self.smtp_server = smtp_server
self.smtp_port = smtp_port
self.sender_email = sender_email
self.password = password
def send_report(self, recipient, subject, body, attachment_path=None):
"""
Send an automated email with optional attachment
"""
msg = MIMEMultipart()
msg['From'] = self.sender_email
msg['To'] = recipient
msg['Subject'] = subject
# Add body
msg.attach(MIMEText(body, 'html'))
# Add attachment if provided
if attachment_path:
with open(attachment_path, 'rb') as attachment:
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header(
'Content-Disposition',
f'attachment; filename= {Path(attachment_path).name}'
)
msg.attach(part)
# Send email
try:
server = smtplib.SMTP(self.smtp_server, self.smtp_port)
server.starttls()
server.login(self.sender_email, self.password)
server.send_message(msg)
server.quit()
print(f"Email sent to {recipient} at {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
return True
except Exception as e:
print(f"Failed to send email..... {str(e)}")
return False
def generate_morning_report():
"""
The function that runs every morning automatically
"""
print(f"Generating morning report at {datetime.now()}")
# Simulate data collection
data = {
'Metric': ['Sales', 'New Customers', 'Revenue', 'Active Users'],
'Yesterday': [156, 23, 45000, 1240],
'Last Week Avg': [142, 19, 41000, 1180],
'Change': ['+9.9%', '+21.1%', '+9.8%', '+5.1%']
}
df = pd.DataFrame(data)
# Save to Excel
output_file = f"daily_report_{datetime.now().strftime('%Y%m%d')}.xlsx"
df.to_excel(output_file, index=False)
# Create email body
email_body = f"""
<html>
<body>
<h2>Daily Performance Report</h2>
<p>Generated automatically at {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
{df.to_html(index=False, border=1)}
<p>Full report attached.</p>
</body>
</html>
"""
return output_file, email_body
def monday_morning_summary():
"""
Weekly summary that runs every Monday
"""
print(f"Generating weekly summary at {datetime.now()}")
summary = {
'Week': [f"Week {datetime.now().isocalendar()[1]}"],
'Total Sales': [8450],
'New Customers': [145],
'Avg Daily Revenue': [42500],
'Growth': ['+12.3%']
}
df = pd.DataFrame(summary)
output_file = f"weekly_summary_{datetime.now().strftime('%Y%m%d')}.xlsx"
df.to_excel(output_file, index=False)
return output_file
# Example usage - Set it and forget it
scheduler = AutomatedTaskScheduler()
# Schedule daily morning report at 6 AM
scheduler.add_daily_report("06:00", generate_morning_report)
# Schedule weekly summary every Monday at 7 AM
scheduler.add_weekly_report("monday", "07:00", monday_morning_summary)
# Schedule end-of-day summary at 6 PM
scheduler.add_daily_report("18:00", generate_morning_report)
print("Automated scheduler configured")
print("Reports will generate automatically even when you are sleeping")
print("\nScheduled tasks.....")
print("- Daily report at 6:00 AM")
print("- Weekly summary every Monday at 7:00 AM")
print("- End of day summary at 6:00 PM")
print("\nTo run scheduler..... scheduler.run_scheduler()")Three months. Nobody noticed. My manager kept praising my work ethic. I kept my mouth shut and enjoyed my extra four hours of actual productivity.
Eventually, I told him. You know what he said? Why didn't you do this sooner?
Requests….. For When APIs Become Your Personal Assistant
I was manually downloading data from five different websites every day.
Click. Download. Save. Rename. Repeat. Twenty minutes of my life….. gone….. every single day. For data that was available through APIs.
The requests library turned that twenty-minute ritual into a two-second API call.
import requests
import json
from typing import Dict, List, Any
import pandas as pd
from datetime import datetime
import time
class APIDataCollector:
def __init__(self, api_key=None):
self.api_key = api_key
self.session = requests.Session()
if api_key:
self.session.headers.update({'Authorization': f'Bearer {api_key}'})
def fetch_data_with_retry(self, url, max_retries=3, delay=2):
"""
Fetch data from API with automatic retry on failure
"""
for attempt in range(max_retries):
try:
response = self.session.get(url, timeout=10)
response.raise_for_status()
return response.json()
except requests.exceptions.RequestException as e:
print(f"Attempt {attempt + 1} failed..... {str(e)}")
if attempt < max_retries - 1:
time.sleep(delay)
else:
print(f"Failed after {max_retries} attempts")
return None
def fetch_multiple_endpoints(self, endpoints):
"""
Fetch data from multiple APIs simultaneously
"""
results = {}
for name, url in endpoints.items():
print(f"Fetching {name}.....")
data = self.fetch_data_with_retry(url)
if data:
results[name] = data
print(f" ✓ Success")
else:
print(f" ✗ Failed")
return results
def fetch_paginated_data(self, base_url, items_per_page=100):
"""
Automatically fetch all pages from a paginated API
"""
all_data = []
page = 1
while True:
print(f"Fetching page {page}.....")
url = f"{base_url}?page={page}&per_page={items_per_page}"
response = self.fetch_data_with_retry(url)
if not response or not response.get('data'):
break
all_data.extend(response['data'])
# Check if there are more pages
if len(response['data']) < items_per_page:
break
page += 1
print(f"Fetched {len(all_data)} total items across {page} pages")
return all_data
def download_file(self, url, output_path):
"""
Download files from URLs automatically
"""
try:
response = self.session.get(url, stream=True)
response.raise_for_status()
with open(output_path, 'wb') as f:
for chunk in response.iter_content(chunk_size=8192):
f.write(chunk)
print(f"Downloaded..... {output_path}")
return True
except Exception as e:
print(f"Download failed..... {str(e)}")
return False
class DataAggregator:
def __init__(self):
self.collector = APIDataCollector()
def daily_data_collection(self):
"""
The automated daily data collection that replaced 20 minutes of manual work
"""
print(f"Starting daily data collection at {datetime.now()}")
# Simulate multiple data sources
endpoints = {
'sales_data': 'https://api.example.com/sales/today',
'customer_data': 'https://api.example.com/customers/new',
'inventory': 'https://api.example.com/inventory/status',
'analytics': 'https://api.example.com/analytics/summary'
}
# Fetch all data
results = self.collector.fetch_multiple_endpoints(endpoints)
# Process and combine data
combined_data = {
'timestamp': datetime.now().isoformat(),
'data_sources': len(results),
'results': results
}
# Save to file
output_file = f"daily_data_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json"
with open(output_file, 'w') as f:
json.dump(combined_data, f, indent=2)
print(f"Data collection complete. Saved to {output_file}")
return combined_data
def convert_api_data_to_excel(self, api_data, output_excel):
"""
Convert API responses to Excel automatically
"""
with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:
for source_name, data in api_data.items():
if isinstance(data, list):
df = pd.DataFrame(data)
elif isinstance(data, dict) and 'items' in data:
df = pd.DataFrame(data['items'])
else:
df = pd.DataFrame([data])
# Clean sheet name (Excel has character limits)
sheet_name = source_name[:31]
df.to_excel(writer, sheet_name=sheet_name, index=False)
print(f"Excel report created..... {output_excel}")
# Example usage
collector = APIDataCollector()
print("API Data Collection Examples.....")
print("\n1. Single API call (replaces manual download)")
# data = collector.fetch_data_with_retry("https://api.example.com/data")
print("\n2. Multiple APIs at once (replaces visiting 5 websites)")
endpoints = {
'source1': 'https://api.example.com/endpoint1',
'source2': 'https://api.example.com/endpoint2',
'source3': 'https://api.example.com/endpoint3'
}
# results = collector.fetch_multiple_endpoints(endpoints)
print("\n3. Automatic pagination (get ALL data, not just first page)")
# all_records = collector.fetch_paginated_data("https://api.example.com/records")
print("\nWhat used to take 20 minutes of clicking.....")
print("Now takes 2 seconds of automated API calls")The day I automated this….. I got back in twenty minutes. Every single day. That is 120 hours per year.
Five full days of my life….. returned to me….. because I stopped being a human web browser.
PyAutoGUI….. For The Tasks Too Stupid To Have An API
Some systems are so old… so badly designed… that they do not have APIs.
You know what they do have? A graphical interface that you can click.
PyAutoGUI clicks for you. While you go get coffee.
import pyautogui
import time
from pathlib import Path
class GUIAutomation:
def __init__(self, delay_between_actions=0.5):
self.delay = delay_between_actions
# Safety feature - move mouse to corner to stop script
pyautogui.FAILSAFE = True
def locate_and_click(self, image_path, confidence=0.9):
"""
Find an image on screen and click it
"""
try:
location = pyautogui.locateOnScreen(image_path, confidence=confidence)
if location:
center = pyautogui.center(location)
pyautogui.click(center)
print(f"Clicked on {image_path}")
time.sleep(self.delay)
return True
else:
print(f"Could not find {image_path} on screen")
return False
except Exception as e:
print(f"Error..... {str(e)}")
return False
def automate_legacy_system_login(self, username, password):
"""
Automate login to systems without APIs
"""
print("Starting automated login.....")
# Click username field
pyautogui.click(x=500, y=300)
time.sleep(self.delay)
# Type username
pyautogui.write(username, interval=0.05)
time.sleep(self.delay)
# Tab to password field
pyautogui.press('tab')
time.sleep(self.delay)
# Type password
pyautogui.write(password, interval=0.05)
time.sleep(self.delay)
# Press Enter
pyautogui.press('enter')
print("Login automated")
def automate_data_entry(self, data_list):
"""
Automate repetitive data entry
"""
print(f"Automating entry of {len(data_list)} records.....")
for idx, data in enumerate(data_list):
print(f"Entering record {idx + 1}/{len(data_list)}")
for field_value in data:
pyautogui.write(str(field_value), interval=0.05)
pyautogui.press('tab')
time.sleep(self.delay)
# Submit (assuming Enter submits)
pyautogui.press('enter')
time.sleep(self.delay * 2) # Wait for system to process
print("GUI Automation configured")
print("Use carefully - this literally controls your mouse and keyboard")
print("Move mouse to top-left corner to emergency stop")I used this for a legacy system at work that had no API….. no export function….. nothing—just a slow….. clunky interface.
PyAutoGUI turned my 2-hour data entry nightmare into a 10-minute automated script.
The Truth About Automation Nobody Tells You
Here is what four years of Python automation taught me…..
The best programmers are not the ones who write the most code. They are the ones who automate the boring parts of their jobs.
Every hour you spend doing something a script could do is an hour you are not spending solving actual problems. Every manual task is a productivity drain. Every repetitive process is an opportunity to automate.
These seven libraries….. openpyxl….. schedule….. requests….. pandas….. smtplib….. PyAutoGUI….. pathlib….. they are not advanced. They are not complicated. But they gave me back hours of my life every single week.
My manager thought I was superhuman. I was the only one who had learned to let Python do the boring work.
So here is my question for you… What task are you doing manually right now that you know… deep down… could be automated?
Drop it in the comments. Let us automate each other out of the tedious parts of our jobs.
If you enjoyed reading, be sure to give it 50 CLAPS! Follow and don't miss out on any of my future posts — subscribe to my profile for must-read blog updates!
Thanks for reading!