The case

A folder sales/ with one Excel file per month (2024-01.xlsx, 2024-02.xlsx…), columns date, customer, product, amount. Every month someone merges them by hand, builds a summary and a chart. Libraries needed:

Shell
python -m venv .venv
pip install pandas openpyxl matplotlib scikit-learn

1Merge the files

Python
from pathlib import Path
import pandas as pd

files = Path("sales").glob("*.xlsx")
sales = pd.concat(pd.read_excel(f) for f in files)

2Clean and summarise

Remove empty rows and duplicates, then one row per month and one column per product:

Python
sales = sales.dropna(subset=["amount"]).drop_duplicates()
sales["month"] = pd.to_datetime(sales["date"]).dt.strftime("%Y-%m")

summary = sales.pivot_table(index="month", columns="product",
                            values="amount", aggfunc="sum", fill_value=0)
summary.to_excel("monthly_summary.xlsx")

3The chart

Python
import matplotlib.pyplot as plt

summary.plot(kind="bar", stacked=True, figsize=(10, 5),
             title="Sales by month")
plt.tight_layout()
plt.savefig("sales.png")

4Make it automatic

Save everything as report.py and schedule it with Windows Task Scheduler (or cron) on the first day of each month. The report is ready before anyone opens Excel.

5Forecast next month

With the history already clean, a first predictive model takes a few lines: it learns how each month's total depends on the three months before.

Python
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_absolute_error

monthly = sales.groupby("month")["amount"].sum().to_frame("total")
for k in (1, 2, 3):
    monthly[f"lag{k}"] = monthly["total"].shift(k)
monthly = monthly.dropna()

X, y = monthly[["lag1", "lag2", "lag3"]], monthly["total"]
# the last 6 months are kept aside to test the model
model = LinearRegression().fit(X[:-6], y[:-6])

error = mean_absolute_error(y[-6:], model.predict(X[-6:]))
baseline = mean_absolute_error(y[-6:], X["lag1"][-6:])
print(f"Model error: {error:.0f}  |  'same as last month': {baseline:.0f}")

last = [y.iloc[-1], y.iloc[-2], y.iloc[-3]]
forecast = model.predict(pd.DataFrame([last], columns=X.columns))[0]
print("Next month:", round(forecast))
  • Always compare with the simplest rule (same as last month): if the model does not beat it, it is not useful yet.
  • You need at least 2–3 years of data; with seasonality add the month as a feature or use dedicated models (statsmodels, Prophet).

The next step

Forecasts per product or customer, alerts when sales drop, and AI assistants that answer questions on your data. Use AI tools to write code faster, but read and understand it before running it on company data.