Streamlining Sales Reporting with Excel and Python for Retail

Automate Sales Report Generation and Analysis

Code Example:

import pandas as pd
import openpyxl

# Sample sales data (from POS system)
data = {
    'date': pd.date_range(start='2023-01-01', periods=30, freq='D'),
    'sales': [200, 250, 300, 150, 400, 220, 300, 350, 270, 280, 310, 250, 260, 220, 280, 350, 380, 330, 400, 450, 410, 380, 320, 310, 350, 330, 360, 380, 370, 390],
    'items_sold': [20, 25, 30, 15, 40, 22, 30, 35, 27, 28, 31, 25, 26, 22, 28, 35, 38, 33, 40, 45, 41, 38, 32, 31, 35, 33, 36, 38, 37, 39]
}

# Load data into a pandas DataFrame
sales_df = pd.DataFrame(data)

# Generate daily sales summary
sales_df['daily_revenue'] = sales_df['sales'] * sales_df['items_sold']

# Save the report to Excel
excel_path = 'sales_report.xlsx'
sales_df.to_excel(excel_path, index=False)

# Open the Excel file to add additional formatting
wb = openpyxl.load_workbook(excel_path)
ws = wb.active

# Add some formatting
ws['A1'].font = openpyxl.styles.Font(bold=True)
ws['B1'].font = openpyxl.styles.Font(bold=True)
ws['C1'].font = openpyxl.styles.Font(bold=True)
ws['D1'].font = openpyxl.styles.Font(bold=True)

# Save the formatted report
wb.save(excel_path)

print(f"Sales report generated and saved to {excel_path}")

Use Pandas for Data Manipulation and Advanced Reporting

Code Example:

# Calculate weekly totals for sales and revenue
sales_df['week'] = sales_df['date'].dt.isocalendar().week
weekly_sales = sales_df.groupby('week').agg({
    'sales': 'sum',
    'items_sold': 'sum',
    'daily_revenue': 'sum'
}).reset_index()

# Display weekly summary
print(weekly_sales)

# Use pivot table for further reporting (e.g., monthly comparison)
monthly_sales = sales_df.groupby(sales_df['date'].dt.to_period('M')).agg({
    'sales': 'sum',
    'items_sold': 'sum',
    'daily_revenue': 'sum'
}).reset_index()

# Save the monthly summary report
monthly_sales_path = 'monthly_sales_report.xlsx'
monthly_sales.to_excel(monthly_sales_path, index=False)
print(f"Monthly sales report saved to {monthly_sales_path}")

Save Hours in Report Creation with Customized Solutions from Lillqvist Strat

Integrate with POS System

To automate the integration with your POS system, we can connect the sales data directly from the POS database into the reporting system. This eliminates the need for manual data entry or CSV exports.

import pymysql

# Connect to the POS database (MySQL example)
connection = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    db='pos_database'
)

# Query sales data from POS
query = "SELECT * FROM sales_data WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-30'"
sales_from_pos = pd.read_sql(query, connection)

# Perform the same analysis as before with POS data
sales_from_pos['daily_revenue'] = sales_from_pos['sales'] * sales_from_pos['items_sold']

# Save report directly from POS data
sales_from_pos.to_excel('pos_sales_report.xlsx', index=False)

# Close database connection
connection.close()

print("POS sales report generated and saved.")

---

Why Choose Lillqvist Strat?

By integrating Python, Pandas, and Excel into your sales reporting workflow, Lillqvist Strat offers customized solutions that save you hours of work. Our expertise in automating data retrieval, report generation, and analysis allows you to focus on strategic business decisions instead of manual tasks.

Lillqvist Strat can streamline your POS integration, automate report generation, and provide advanced data insights, helping your business become more efficient and data-driven. Save time, reduce errors, and scale your business with our tailored automation solutions.

---