User story: a refreshing dashboard sheet

The story

Marcus's manager wants a single Excel file she can open every morning and see: today's revenue, week-on-week, top 5 products, top 5 stores, anomaly count.

The structure

• Sheet 1: Data — the raw transactions, refreshed nightly.
• Sheet 2: Dashboard — 5 =PY() cells, each producing one tile.

The =PY() cells

# Tile 1: Yesterday's revenue
=PY(
df = xl("Data!A1:F100000", headers=True)
df["date"] = pd.to_datetime(df["date"])
y = df[df["date"].dt.date == (pd.Timestamp.today().date() - pd.Timedelta(days=1))]
f"${y['revenue'].sum():,.0f}"
)

# Tile 2: Week-on-week
=PY(
df = xl("Data!A1:F100000", headers=True)
df["date"] = pd.to_datetime(df["date"])
this7 = df[df["date"] >= pd.Timestamp.today() - pd.Timedelta(days=7)]["revenue"].sum()
prev7 = df[(df["date"] < pd.Timestamp.today() - pd.Timedelta(days=7)) &
           (df["date"] >= pd.Timestamp.today() - pd.Timedelta(days=14))]["revenue"].sum()
f"{(this7/prev7 - 1)*100:+.1f}%"
)

# Tile 3: Top 5 products
=PY(
df = xl("Data!A1:F100000", headers=True)
df.groupby("product")["revenue"].sum().nlargest(5)
)

# Tile 4: Top 5 stores
=PY(
df = xl("Data!A1:F100000", headers=True)
df.groupby("store")["revenue"].sum().nlargest(5)
)

# Tile 5: Anomaly count
=PY(
df = xl("Data!A1:F100000", headers=True)
daily = df.groupby(pd.to_datetime(df["date"]).dt.date)["revenue"].sum()
mu, sd = daily.mean(), daily.std()
int(((daily - mu).abs() > 2*sd).sum())
)

Lay it out

Put the small tiles (revenue, WoW, anomaly count) in big bold cells across the top. Put the Top 5 lists in the middle. Add a single chart at the bottom: =PY() returning a line chart of the last 30 days.