Install and load the data
You need pandas, openpyxl (to read .xlsx) and matplotlib: pip install pandas openpyxl matplotlib. Read the sheet with parse_dates so the date column becomes real datetimes, which makes monthly grouping trivial.
import pandas as pd
import matplotlib.pyplot as plt
df = pd.read_excel("sales.xlsx", sheet_name="Data", parse_dates=["date"])
print(df.dtypes)Aggregate before plotting
Charts with thousands of points are unreadable. Group first: monthly totals for a trend, totals per category for a comparison. resample('MS') groups by month start and keeps empty months as zero.
monthly = df.set_index("date")["amount"].resample("MS").sum()
by_region = df.groupby("region")["amount"].sum().sort_values()A clean line and bar chart
Use the object-oriented API (fig, ax) rather than plt.plot calls: it gives full control and avoids charts overwriting each other in loops. Remove the top and right borders, label the axes, and format thousands with a separator.
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(12, 4.5))
ax1.plot(monthly.index, monthly.values, marker="o")
ax1.set_title("Monthly revenue")
ax1.yaxis.set_major_formatter(lambda v, _: f"{v:,.0f}")
ax2.barh(by_region.index, by_region.values)
ax2.set_title("Revenue by region")
for ax in (ax1, ax2):
ax.spines[["top", "right"]].set_visible(False)
fig.tight_layout()
fig.savefig("report.png", dpi=200)Charts that communicate
Sort bars by value, not alphabetically. Start bar axes at zero; line charts may start elsewhere if the variation is the message. Use one highlight color for the point you want to make and grey for the rest. A title that states the conclusion ("Revenue up 18% since March") is read far more than a neutral one.
Automate it
Wrap the code in a function that takes the file path and run it every Monday with the Windows Task Scheduler or cron. Combine it with openpyxl to insert the PNG into an Excel report (see our guide on automating Excel reports). FormulAI can generate the full script if you describe your columns and the chart you want.