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. 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