Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Build a Weekly Sales Report in Excel with Python

Build the Monday-morning sales report from daily transactions: the week just ended against the previous week and the same ISO week last year, like-for-like stores only, year to date, an eight-week trend with sparklines, the biggest movers, and a one-page Excel layout with arrows and conditional formatting.
Report layouts & templatesPivots & summaries

The weekly sales report is the one everyone reads: how did last week go, compared with the week before and with the same week last year? It looks simple, and it is where most comparison mistakes live — a new store counted as growth, a calendar week compared with a different weekday mix, a year-to-date figure that runs a day further than last year's. This page builds the report from daily sales with pandas, makes each comparison explicit, and writes a one-page Excel summary with trend sparklines. It is part of Building Finance and Operations Reports.

Prerequisites

Bash
pip install pandas xlsxwriter

daily_sales.csv has one row per store, category and day since January 2025: date, store, region, category and sales value. Eight stores in three regions; Cambridge opened in March 2026. The report is for the week ending Sunday 27 September 2026, ISO week 39.

Three comparisons, three questions Three panels. Week over week compares the week with the one before and answers whether things are moving. Same week last year compares with ISO week 39 of 2025 and answers whether the business is growing, net of seasonality. Year to date compares the sum since week 1 with the same number of weeks last year. Week over week W39 vs W38 is it moving? noisy, seasonal Same week last year 2026-W39 vs 2025-W39 is it growing? same weekday mix Year to date W1–W39 vs W1–W39 the big picture same number of weeks

Step 1 — Put every day in an ISO week

Calendar dates do not line up across years: 27 September was a Saturday in 2025 and a Sunday in 2026. Comparing "the week ending 27 September" with the same dates last year would compare different weekdays, and weekends sell a third more. ISO weeks fix that: week 39 is Monday to Sunday in both years, so both contain the same weekday mix:

Python
import pandas as pd

sales = pd.read_csv("daily_sales.csv", parse_dates=["Date"])
iso = sales["Date"].dt.isocalendar()
sales["Year"], sales["Week"] = iso["year"], iso["week"]

YEAR, WEEK = 2026, 39
this_week = sales[(sales["Year"] == YEAR) & (sales["Week"] == WEEK)]
print(this_week["Date"].min().date(), "to", this_week["Date"].max().date(),
      "| last year:", *sales.loc[(sales["Year"] == YEAR - 1) & (sales["Week"] == WEEK), "Date"].agg(["min", "max"]).dt.date)

Use isocalendar()["year"], not the calendar year: the first days of January often belong to the last ISO week of the previous year. Group Excel Data by Fiscal Year and ISO Week goes through the edge cases, including 53-week years.

Step 2 — Like-for-like stores

A store that opened this year adds sales that did not exist last year; including it in the year-on-year comparison shows growth the existing business did not achieve. Like-for-like (LFL) comparisons only include stores that traded in both weeks being compared:

Python
weekly = sales.groupby(["Store", "Region", "Year", "Week"], as_index=False)["Sales"].sum()

def traded(year, week):
    w = weekly[(weekly["Year"] == year) & (weekly["Week"] == week)]
    return set(w.loc[w["Sales"] > 0, "Store"])

lfl = traded(YEAR, WEEK) & traded(YEAR - 1, WEEK)
print("like-for-like stores:", sorted(lfl))
print("excluded from year-on-year:", sorted(traded(YEAR, WEEK) - lfl))

Cambridge appears in the total and in the week-over-week comparison, but not in the year-on-year growth. Stricter LFL rules also exclude stores that were closed for refurbishment during either period, or that opened less than a year ago; express them as extra conditions on this set.

Step 3 — The comparison table

One row per store with this week, last week, the same week last year, and the percentage changes. The totals row computes changes from its own sums, not by averaging store percentages:

Python
def week_sales(year, week):
    w = weekly[(weekly["Year"] == year) & (weekly["Week"] == week)]
    return w.set_index("Store")["Sales"]

table = pd.DataFrame({
    "Region": weekly.drop_duplicates("Store").set_index("Store")["Region"],
    "This week": week_sales(YEAR, WEEK),
    "Last week": week_sales(YEAR, WEEK - 1),
    "Last year": week_sales(YEAR - 1, WEEK),
})
table.loc[~table.index.isin(lfl), "Last year"] = float("nan")
table["WoW %"] = (table["This week"] / table["Last week"] - 1) * 100
table["YoY % (LFL)"] = (table["This week"] / table["Last year"] - 1) * 100
table = table.sort_values(["Region", "This week"], ascending=[True, False])

lfl_rows = table[table.index.isin(lfl)]
total = pd.Series({
    "Region": "",
    "This week": table["This week"].sum(),
    "Last week": table["Last week"].sum(),
    "Last year": lfl_rows["Last year"].sum(),
    "WoW %": (table["This week"].sum() / table["Last week"].sum() - 1) * 100,
    "YoY % (LFL)": (lfl_rows["This week"].sum() / lfl_rows["Last year"].sum() - 1) * 100,
}, name="Total")
numbers = ["This week", "Last week", "Last year", "WoW %", "YoY % (LFL)"]
print(pd.concat([table, total.to_frame().T]).astype({c: float for c in numbers}).round(1).to_string())

The total is down 6.2% on last week and 3.3% on the same week last year, like-for-like, while most stores moved by a few percent. Exeter stands out: down about 25% on both comparisons, and on its own responsible for 19,000 of the 32,000 drop from last week. The daily figures show why:

Python
exeter = sales[(sales["Store"] == "Exeter") & (sales["Year"] == YEAR) & (sales["Week"] == WEEK)]
print(exeter.groupby("Date")["Sales"].sum().round(0).to_string())

Three days at about a third of normal sales at the start of the week, then a normal Thursday to Sunday: a local disruption rather than a trend, and exactly the kind of thing the report should surface so someone can add the explanation before the meeting.

Step 4 — Year to date, on the same number of weeks

Year to date must cover the same span in both years: weeks 1 to 39 this year against weeks 1 to 39 last year, like-for-like stores only:

Python
ytd = weekly[(weekly["Week"] <= WEEK) & weekly["Store"].isin(lfl)].groupby("Year")["Sales"].sum()
print(f"YTD LFL {YEAR}: {ytd[YEAR]:,.0f}  {YEAR - 1}: {ytd[YEAR - 1]:,.0f}  change {(ytd[YEAR] / ytd[YEAR - 1] - 1) * 100:+.1f}%")

Here "like-for-like stores" is the set from the current week, a simplification. Cambridge only opened in March, so it is excluded from the whole year-to-date comparison; that is the conservative choice.

Step 5 — The eight-week trend and the biggest movers

Numbers for one week invite over-reaction. An eight-week trend per store puts the week in context, and a short list of category movers says what drove the change:

Python
recent = weekly[(weekly["Year"] == YEAR) & weekly["Week"].between(WEEK - 7, WEEK)]
trend = recent.pivot_table(index="Store", columns="Week", values="Sales", aggfunc="sum").reindex(table.index)

by_cat = sales[sales["Year"] == YEAR].groupby(["Category", "Week"])["Sales"].sum().unstack()
movers = pd.DataFrame({"This week": by_cat[WEEK], "Last week": by_cat[WEEK - 1]})
movers["Change"] = movers["This week"] - movers["Last week"]
print(movers.sort_values("Change").round(0).to_string())
Total sales, weeks 32 to 39 of 2026 A column chart of total weekly sales for all stores over the last eight weeks, in thousands. Sales ease gradually through late summer from about 552 thousand to 518 thousand, then drop to 486 thousand in week 39; more than half of that last drop is Exeter's three weak days. 0k 200k 400k 600k W32 W33 W34 W35 W36 W37 W38 W39 551.8 547.6 537.7 537.5 527.6 517.8 517.8 485.8

Step 6 — Write the one-page report

The Excel layout follows how people read it: the headline totals at the top, the store table with coloured changes and a sparkline per store, and the category movers below. Percentages are written as fractions with a percent format so that sorting and conditional formatting work:

Python
import xlsxwriter

wb = xlsxwriter.Workbook(f"weekly_sales_{YEAR}-W{WEEK:02d}.xlsx")
ws = wb.add_worksheet("Week 39")
title = wb.add_format({"bold": True, "font_size": 14})
head = wb.add_format({"bold": True, "bg_color": "#EEF2FF", "bottom": 1, "text_wrap": True, "valign": "vcenter"})
money = wb.add_format({"num_format": "#,##0"})
pct = wb.add_format({"num_format": "+0.0%;-0.0%;0.0%"})
tot_money = wb.add_format({"num_format": "#,##0", "bold": True, "top": 1})
tot_pct = wb.add_format({"num_format": "+0.0%;-0.0%;0.0%", "bold": True, "top": 1})
na = wb.add_format({"font_color": "#888888", "align": "right"})

ws.write("A1", f"Weekly sales — week {WEEK}, {this_week['Date'].min():%d %b} to {this_week['Date'].max():%d %b %Y}", title)
ws.write("A2", f"Year-on-year and YTD are like-for-like: excludes {', '.join(sorted(traded(YEAR, WEEK) - lfl))}.")
ws.write("A3", f"YTD LFL change: {(ytd[YEAR] / ytd[YEAR - 1] - 1):+.1%}")

cols = ["Store", "Region", "This week", "Last week", "Last year", "WoW", "YoY (LFL)", "8-week trend"]
ws.write_row(4, 0, cols, head)
ws.set_row(4, 30)
first = 5
for i, (store, r) in enumerate(table.iterrows()):
    row = first + i
    ws.write(row, 0, store)
    ws.write(row, 1, r["Region"])
    ws.write_number(row, 2, r["This week"], money)
    ws.write_number(row, 3, r["Last week"], money)
    if pd.notna(r["Last year"]):
        ws.write_number(row, 4, r["Last year"], money)
        ws.write_number(row, 6, r["YoY % (LFL)"] / 100, pct)
    else:
        ws.write(row, 4, "new store", na)
        ws.write(row, 6, "n/a", na)
    ws.write_number(row, 5, r["WoW %"] / 100, pct)
    for j, v in enumerate(trend.loc[store]):
        ws.write_number(row, 10 + j, v)                      # sparkline data, hidden columns
last = first + len(table) - 1
tot = last + 1
ws.write(tot, 0, "Total", wb.add_format({"bold": True, "top": 1}))
ws.write(tot, 1, "", wb.add_format({"top": 1}))
for c, key in [(2, "This week"), (3, "Last week"), (4, "Last year")]:
    ws.write_number(tot, c, total[key], tot_money)
ws.write_number(tot, 5, total["WoW %"] / 100, tot_pct)
ws.write_number(tot, 6, total["YoY % (LFL)"] / 100, tot_pct)
ws.write(tot, 7, "", wb.add_format({"top": 1}))

for c in (5, 6):
    rng_ = xlsxwriter.utility.xl_range(first, c, last, c)
    ws.conditional_format(rng_, {"type": "icon_set", "icon_style": "3_arrows",
                                 "icons": [{"criteria": ">=", "type": "number", "value": 0.02},
                                           {"criteria": ">=", "type": "number", "value": -0.02}]})
    ws.conditional_format(rng_, {"type": "cell", "criteria": "<", "value": -0.1,
                                 "format": wb.add_format({"font_color": "#9B1C1C", "bold": True})})
for i in range(len(table)):
    row = first + i
    ws.add_sparkline(row, 7, {"range": xlsxwriter.utility.xl_range(row, 10, row, 17),
                              "markers": True, "last_point": True})
ws.set_column("A:B", 11)
ws.set_column("C:E", 11)
ws.set_column("F:G", 10)
ws.set_column("H:H", 16)
ws.set_column("K:R", None, None, {"hidden": True})

mrow = tot + 3
ws.write(mrow, 0, "Category movers vs last week", wb.add_format({"bold": True}))
ws.write_row(mrow + 1, 0, ["Category", "This week", "Last week", "Change"], head)
for i, (cat, m) in enumerate(movers.sort_values("Change").iterrows(), start=mrow + 2):
    ws.write(i, 0, cat)
    ws.write_number(i, 1, m["This week"], money)
    ws.write_number(i, 2, m["Last week"], money)
    ws.write_number(i, 3, m["Change"], wb.add_format({"num_format": "+#,##0;[Red]-#,##0"}))
ws.fit_to_pages(1, 1)
ws.set_landscape()
wb.close()
print("wrote", wb.filename)
The store table in the report The top of the weekly report's store table. Each store has this week's sales, last week, the same week last year, week-over-week and like-for-like year-on-year changes. Exeter's changes of about minus 25 percent are highlighted in red; Cambridge shows new store instead of a last-year figure. Store This week Last week Last year WoW YoY (LFL) Norwich 74,584 76,247 74,339 -2.2% +0.3% Cambridge 49,776 52,856 new store -5.8% n/a Bristol 77,485 78,612 74,300 -1.4% +4.3% Exeter 56,728 75,769 76,557 -25.1% -25.9% Total 485,850 517,831 451,012 -6.2% -3.3%

The icon set uses a ±2% dead band so that small wobbles show a sideways arrow instead of alarming red ones, and anything worse than −10% is also set in bold red. Add Sparklines to an Excel Report with xlsxwriter covers sparkline options; the hidden columns K to R hold their data.

Common pitfalls

  • Comparing calendar dates across years. Use ISO weeks so both periods have the same weekdays.
  • New stores in growth figures. Restrict year-on-year and YTD to like-for-like stores and say which were excluded.
  • Averaging percentages. Compute the total's change from summed sales, not from the stores' percentages.
  • YTD over different spans. Cut both years at the same week number.
  • Week 53. Years such as 2026 have an ISO week 53; last year has none, so compare W53 with W52 or leave it out, and say which.
  • Reporting before the week is complete. Run the report after the last day's data has landed; check the latest date in the data rather than assuming.

Conclusion

Tag every day with its ISO year and week, restrict year-on-year and year-to-date comparisons to stores that traded in both periods, and compute week-over-week, same-week-last-year and YTD changes with totals calculated from sums. Add an eight-week trend and category movers for context, then write a one-page Excel report with percent formats, arrow icons with a dead band, and sparklines.

Frequently asked questions

How do I compare a week with the same week last year? Use ISO weeks: dt.isocalendar() gives year and week, and ISO week 39 of 2026 and of 2025 both run Monday to Sunday, so they contain the same days of the week.

What does like-for-like mean in a sales report? Only stores that traded in both periods are included in the comparison, so openings and closures do not look like growth or decline. The excluded stores are still shown in the current week's total.

How do I add arrows for up and down in Excel from Python? Use a conditional format of type icon_set with the 3_arrows style, with number thresholds so that small changes show a sideways arrow.

Why does my YTD growth look too high on Mondays? If this year's data runs to today and last year's to the full week, the periods differ. Cut both years at the same complete ISO week.

More on report layouts & templates

More on report layouts & templates →