Capstone Backtest: Equal-Weight Portfolio vs SPY in Pandas | Pandas for Finance Ep14

Episode 14 of Pandas for Finance. The capstone — a real, end-to-end portfolio backtest assembled from every concept in the previous 13 episodes. Source code: https://github.com/GoCelesteAI/pandas-for-finance The setup: an equal-weight portfolio of five mega-cap tech stocks (AAPL, MSFT, GOOGL, AMZN, NVDA) versus SPY as the benchmark, with 4 percent risk-free rate for the Sharpe calculation. The output: an annualized return, vol, Sharpe, max drawdown, and final wealth multiplier for each side, plus a per-stock Sharpe leaderboard. Mega-cap tech finished at +38.92 percent annualized vs SPY's +16.46 percent — but with higher vol and a deeper drawdown. What You'll Build: - backtest.py — load the cached prices, filter to the universe, pivot wide for portfolio math, compute daily returns, build an equal-weight portfolio, and produce a full backtest report. - The pivot pattern: df[df["Ticker"].isin([...])] .pivot(index="Date", columns="Ticker", values="Adj Close") .ffill() — wide format makes every per-row portfolio operation a one-liner. - The equal-weight calculation: a pd.Series of weights times the daily-returns DataFrame, summed across the row. That's the daily portfolio return in one line. - A reusable stats(returns) function that returns annualized return, annualized vol, Sharpe, max drawdown, and final wealth multiplier — five numbers that summarize any return stream. - The per-stock Sharpe leaderboard — sort the same metrics per ticker to see which holdings drive the portfolio's risk-adjusted return. Timestamps: 0:00 - Intro — Episode 14 starts here 0:23 - Preview — equal-weight portfolio vs SPY 1:10 - Open nvim, write backtest.py 1:14 - Display options + constants 1:33 - Load + pivot to wide format 1:55 - Daily returns + equal-weight portfolio 2:18 - The stats function (Sharpe, drawdown, final wealth) 2:46 - Build the comparison report 3:30 - Save and run 3:36 - Backtest report (Portfolio +38.9% vs SPY +16.5%) 4:30 - Recap 5:18 - End screen Key Takeaways: 1. Pivot to wide for portfolio math. Filter to the universe + benchmark, then .pivot(index="Date", columns="Ticker", values="Adj Close"). Each ticker becomes a column. Every per-row operation — daily returns, weighted sums, correlation — becomes vectorized. .ffill() after the pivot fills any gaps that come from one ticker being missing on a date the others traded. 2. Equal-weight portfolio in two lines. weights = pd.Series(1 / N, index=tickers); port_ret = (returns[tickers] * weights).sum(axis=1). That's it. No loops, no broadcasting tricks — pandas aligns the weights Series to the columns of the returns DataFrame automatically. For other weight schemes (market-cap, risk-parity, momentum), only the weights line changes. 3. A reusable stats() function for any return stream. Annualized return is (1 + mean) * 252 - 1. Annualized vol is std sqrt(252). Sharpe is (ann_return - rf) / ann_vol. Max drawdown is (wealth / wealth.expanding().max() - 1).min(). Final wealth is wealth.iloc[-1]. Five lines, every metric a quant report needs. 4. Compare portfolio to benchmark in one DataFrame. pd.DataFrame({"Portfolio": stats(port_ret), "SPY": stats(bench_ret)}) — two named columns, five rows of metrics, a single print. The whole thesis of "did mega-cap tech beat the market?" answered in one table. 5. The per-stock Sharpe leaderboard tells you what's driving the portfolio. Sort the ticker-level Sharpe ratios — NVDA at +1.48 carries the equal-weight portfolio. AMZN at +0.64 is exactly SPY's Sharpe — a drag in this universe. This decomposition is how you decide whether to drop the laggards in the next iteration. This channel is run by Claude AI. Tutorials AI-produced; reviewed and published by Codegiz. Source code at codegiz.com. #Pandas #Python #Finance #Backtest #Portfolio #Sharpe #DataAnalytics #PythonForFinance #LearnPandas #ClaudeAI --- Generated by Claude AI · part of the Pandas for Finance series