Group Excel Data by Fiscal Year and ISO Week with pandas

Managers rarely think in calendar years. The business year may run from April to March, reports are compared "week 23 against week 23 last year", and a finance team numbers its months as periods 1 to 12 starting in the fiscal year's first month. An Excel export has only dates. Adding the fiscal and weekly keys correctly is easy to get almost right — and the "almost" shows up exactly at year ends, where a few days land in the wrong year and a weekly comparison quietly compares different weeks. This guide, part of Working with Dates and Times in Excel Data, builds those keys explicitly. Month and calendar-quarter grouping is covered in Group Excel Rows by Month and Quarter with pandas.

Prerequisites

pip install pandas openpyxl

Sales dates chosen to sit on the awkward boundaries — around New Year and around the April fiscal start:

import pandas as pd

sales = pd.DataFrame({
    "date": pd.to_datetime(["2025-12-29", "2026-01-01", "2026-01-04", "2026-01-05", "2026-03-31",
                            "2026-04-01", "2026-12-31", "2027-01-01", "2027-01-03", "2027-01-04"]),
    "amount": [120, 80, 45, 200, 310, 95, 60, 75, 30, 150],
})
sales.to_excel("sales.xlsx", index=False)
df = pd.read_excel("sales.xlsx")

Step 1 — Fiscal year and fiscal period from a start month

A fiscal year that starts in April runs from 1 April to 31 March and is usually named after the year it ends in, or with both years. Two lines of arithmetic cover any start month:

FY_START = 4    # April

fy_end = df["date"].dt.year + (df["date"].dt.month >= FY_START).astype(int)
df["fiscal_year"] = fy_end
df["fy_label"] = "FY" + (fy_end - 1).astype(str) + "/" + (fy_end % 100).astype(str).str.zfill(2)
df["fiscal_period"] = (df["date"].dt.month - FY_START) % 12 + 1
print(df[["date", "fy_label", "fiscal_period"]].astype(str).values.tolist()[3:7])
# [['2026-01-05', 'FY2025/26', '10'], ['2026-03-31', 'FY2025/26', '12'],
#  ['2026-04-01', 'FY2026/27', '1'], ['2026-12-31', 'FY2026/27', '9']]

31 March 2026 is period 12 of FY2025/26 and 1 April 2026 period 1 of FY2026/27. The modulo keeps the period formula valid for any start month, including January, where it reduces to the calendar month. Store the numeric fiscal_year for sorting and filtering, and the label only for display.

Step 2 — Check against pandas' anchored periods

pandas can express the same fiscal year with an anchored period, named by the month the year ends in:

fq = df["date"].dt.to_period("Q-MAR")
print(fq.astype(str).tolist()[4:6])                           # ['2026Q4', '2027Q1']
assert (fq.dt.qyear == df["fiscal_year"]).all()
df["fiscal_quarter"] = fq.dt.quarter

qyear is the fiscal year in which each quarter falls, and it agrees with the arithmetic above. The period form is handy for quarters; the explicit columns are easier to read, to explain to finance colleagues and to write back to Excel, where a Period object would become text anyway.

Step 3 — ISO weeks and the ISO year

dt.isocalendar() returns the ISO year, week and weekday, and matches Excel's ISOWEEKNUM. The ISO year is the part people forget:

iso = df["date"].dt.isocalendar()
df["iso_year"], df["iso_week"] = iso["year"], iso["week"]
df["year_week"] = df["date"].dt.strftime("%G-W%V")
print(df[["date", "year_week"]].astype(str).values.tolist()[:2])
# [['2025-12-29', '2026-W01'], ['2026-01-01', '2026-W01']]
print(df.loc[df["date"] == "2027-01-01", ["iso_year", "iso_week"]].values.tolist())   # [[2026, 53]]

29 December 2025 belongs to ISO week 1 of 2026, and 1 January 2027 to week 53 of 2026 — 2026 is one of the years with 53 ISO weeks. Grouping by calendar year and ISO week would put 1 January 2027 into "2027 week 53", a week that does not exist. Always pair iso_week with iso_year; the %G-W%V label does that in one sortable string.

Step 4 — Matching Excel's WEEKNUM

Many workbooks use WEEKNUM(date) rather than ISOWEEKNUM. With its default return type, Excel documents a different rule: weeks start on Sunday and week 1 is the week that contains 1 January, so the first and last weeks of a year are often partial. Reproduce it when a report must match the workbook's numbering:

jan1 = pd.to_datetime(df["date"].dt.year.astype(str) + "-01-01")
offset = (jan1.dt.dayofweek + 1) % 7                    # days from the Sunday on/before 1 January
df["weeknum_excel"] = ((df["date"] - jan1).dt.days + offset) // 7 + 1
print(df[["date", "iso_week", "weeknum_excel"]].astype(str).values.tolist()[:4])
# [['2025-12-29', '1', '53'], ['2026-01-01', '1', '1'], ['2026-01-04', '1', '2'], ['2026-01-05', '2', '2']]

The two schemes disagree on 29 December (ISO week 1 of 2026 versus week 53 of 2025) and on Sunday 4 January (ISO week 1 versus week 2). Neither is wrong; they are different definitions. Pick one per report, name the column after it, and never compare a week number from one scheme with the other.

Step 5 — Weekly totals with no missing weeks

groupby produces only weeks that have rows. A weekly report needs every week in the range, with zero for quiet weeks, so that charts and "same week last year" comparisons line up:

weekly = df.groupby("year_week")["amount"].sum()
start, end = df["date"].min(), df["date"].max()
mondays = pd.date_range(start - pd.Timedelta(days=start.dayofweek), end, freq="W-MON")
all_weeks = mondays.strftime("%G-W%V")
weekly = weekly.reindex(all_weeks, fill_value=0)
print(len(weekly), weekly.head(3).to_dict())
# 54 {'2026-W01': 245, '2026-W02': 200, '2026-W03': 0}

Stepping through Mondays and formatting each with %G-W%V generates exactly the ISO weeks in the range, including week 53 in years that have one. Filled weeks with a zero are genuinely empty — no sales recorded — which is different from a week whose data has not arrived yet; if that distinction matters, fill with NaN and let the report show blanks.

Step 6 — Same week last year

"Week 23 against week 23 last year" is a join on the ISO week number with the ISO year shifted by one. Build it from the gap-free weekly table so that quiet weeks compare as zero rather than disappearing:

wk = weekly.rename("amount").reset_index().rename(columns={"index": "year_week"})
wk[["iso_year", "iso_week"]] = wk["year_week"].str.extract(r"(\d{4})-W(\d{2})").astype(int)
prior = wk[["iso_year", "iso_week", "amount"]].assign(iso_year=lambda t: t["iso_year"] + 1)
yoy = wk.merge(prior, on=["iso_year", "iso_week"], how="left", suffixes=("", "_last_year"))
print(yoy.loc[yoy["iso_year"] == 2027, ["year_week", "amount", "amount_last_year"]].values.tolist())
# [['2027-W01', 150, 245.0]]

Week 1 of 2027 is compared with week 1 of 2026, even though they start on different calendar dates — which is the point of ISO weeks: each week has the same seven weekdays in both years. Week 53 has no partner in a 52-week year and gets NaN; decide whether to compare it with week 52 or leave it out, and say which in the report.

Step 7 — Fiscal summaries written back to Excel

With the keys as ordinary columns, a fiscal summary is a plain group-by, and the columns write to Excel without conversion:

summary = (df.groupby(["fiscal_year", "fy_label", "fiscal_period"], as_index=False)["amount"].sum()
             .sort_values(["fiscal_year", "fiscal_period"]))
summary.drop(columns="fiscal_year").to_excel("fiscal_summary.xlsx", index=False)
print(summary[["fy_label", "fiscal_period", "amount"]].values.tolist())
# [['FY2025/26', 9, 120], ['FY2025/26', 10, 325], ['FY2025/26', 12, 310],
#  ['FY2026/27', 1, 95], ['FY2026/27', 9, 60], ['FY2026/27', 10, 255]]

Sorting by the numeric year and period, not the label, keeps the order right even when a label format changes. Periods with no sales are missing here; reindex against the full list of periods, as in Step 5, when the report needs all twelve.

Common pitfalls

• Grouping ISO weeks by calendar year. Around New Year, days move between years. Use iso_year with iso_week.
• Fiscal year naming. Some organisations name a year by its start, others by its end. Agree the convention and put it in the column name or label.
• Retail 4-4-5 calendars. Fiscal months made of whole weeks cannot be derived from calendar months; keep a calendar table from the finance team and merge on date.

Conclusion

Fiscal and weekly keys are small calculations with sharp edges at year ends. Derive fiscal year and period from the start month with explicit arithmetic and check them against an anchored period, pair ISO weeks with the ISO year, reproduce Excel's WEEKNUM only when the workbook used it, and reindex weekly results so every week appears. Keep the keys as ordinary columns, and write them back to Excel as numbers and labels.

Frequently asked questions

How do I calculate the fiscal year for a date in pandas? With a fiscal year starting in month S, the fiscal year ending year is the calendar year plus one for months from S onwards. For an April start, 15 May 2026 belongs to the fiscal year ending in 2027, often labelled FY2026/27.

Why does 1 January show up in week 53 of the previous year? ISO weeks start on Monday, and week 1 is the week containing the year's first Thursday. Days before that Monday belong to the last ISO week of the previous year, so always group by the ISO year together with the ISO week, not the calendar year.

Is pandas' isocalendar the same as Excel's ISOWEEKNUM? Yes. dt.isocalendar().week gives the same numbers as ISOWEEKNUM. Excel's WEEKNUM with its default return type is different — weeks start on Sunday and week 1 is the week containing 1 January — so match the function your workbook used.

Why does my weekly report skip a week? groupby only creates groups for weeks that have rows. Build the full list of weeks for the range and reindex the result against it, filling missing weeks with zero.

Related

• Up one level: Working with Dates and Times in Excel Data — dates and times from Excel.
• Group Excel Rows by Month and Quarter with pandas — calendar periods.
• EOMONTH and EDATE Equivalents in pandas — month arithmetic.
• Running Totals and Year-over-Year Growth in pandas — comparing periods.