Stock Portfolio Analysis Pipeline

Problem

Modern Portfolio Theory is taught with closed-form min-variance solutions and a few neat illustrations, but real construction needs an honest view of the risk-return cloud — including all the suboptimal allocations a manager could land on if they wing it. Without that, a single 'optimal' portfolio looks correct in isolation but tells you nothing about how sensitive it is.

Approach

Built an end-to-end pipeline: pull two years of daily prices for 8 financial stocks from Yahoo Finance into a normalized SQLite schema, compute annualized returns and the covariance matrix in pandas, then run 5,000 Monte Carlo allocations in risk-return space to map the empirical frontier. Solved the analytical min-variance and max-Sharpe portfolios in closed form for comparison.

Key Decision

Stored prices and returns as separate tables instead of recomputing returns on every analysis run. The recompute path was cheap for 8 tickers but would have become the bottleneck the moment the universe grew. Normalizing the schema upfront kept the analytical layer SQL-native and made it trivial to swap in new tickers without touching the optimizer code.

Result

Full efficient frontier mapped across 5,000 portfolios and the Sharpe-optimal allocation identified analytically. The interactive widget on this page runs the same MPT math live in your browser — drag the risk-free rate slider and the max-Sharpe portfolio rebalances in real time.

ETL Pipeline Design

Historical price data for 8 financial stocks was pulled from Yahoo Finance and ingested into a normalized SQLite database through an automated pipeline. The schema tracks prices, returns, and computed statistics separately to support analytical queries without recomputation.

Modern Portfolio Theory Implementation

Portfolio optimization was implemented from first principles using MPT — computing expected returns, the covariance matrix of asset returns, and portfolio variance analytically. This forms the mathematical foundation for the efficient frontier calculation.

Monte Carlo Simulation

5,000 random portfolio weight allocations were simulated and plotted in risk-return space to map the efficient frontier empirically. Each simulation computes annualized return, volatility, and Sharpe ratio, allowing visual identification of the optimal risk-adjusted allocation.

Sharpe Ratio Optimization

The Sharpe-optimal portfolio — the point on the efficient frontier with the highest risk-adjusted return — was identified from the simulation results. This is the standard metric used by portfolio managers to compare strategies on a risk-normalized basis.