case study · scheduled etl pipeline

The efficient frontier of coffee & cotton.

Harry Markowitz's insight (1952): a portfolio's risk is not the average of its parts — when assets barely move together, blending them cancels risk out. This study applies that lens to two real ICE commodities and locates the mix that minimizes risk. It runs on a pipeline built end to end for the study, which reruns after every market close.

Yahoo FinanceICE futures · KC=F / CT=F→ Python ETLextract · transform · load→ SQLite · CSV · JSONanalytical store + exports→ This pageself-contained front-end
Orchestrated by a scheduled GitHub Actions workflow — rerun after each ICE close, auto-commit, auto-deploy.
Pythonpandas · numpySQL · SQLiteGitHub Actionsinteractive SVG
real data · ICE futures  ·  coffee KC=F · cotton CT=F  ·  2023-07-31 → 2026-07-30  ·  last refresh 2026-07-30 23:30

Latest 10 sessions

closing prices in US¢/lb · regenerated on every pipeline run
date coffee (KC)Δ cotton (CT)Δ
2026-07-30324.50-0.40%81.10+3.95%
2026-07-29325.80-4.01%78.02-1.50%
2026-07-28339.40+4.58%79.21-0.16%
2026-07-27324.55+3.43%79.34+1.01%
2026-07-24313.80+1.42%78.55-1.62%
2026-07-23309.40-2.29%79.84-0.04%
2026-07-22316.65-4.61%79.87+1.15%
2026-07-21331.95-0.73%78.96+2.06%
2026-07-20334.40+1.81%77.37+0.39%
2026-07-17328.45+2.23%77.07-0.80%
1

The two assets

Realized over the period — annualized return and risk (volatility), plus how much they co-move.
Coffee (Arabica)KC · ICE
Return p.a.30.0%
Risk (vol) p.a.38.5%

The higher-return, higher-risk asset — a sustained rally over the window, with the widest daily swings.

Cotton (No.2)CT · ICE
Return p.a.0.8%
Risk (vol) p.a.22.5%

Traded sideways: minimal return over the period, but the lower-volatility series.

Correlationdaily returns
0.05

Effectively zero — the two markets move independently. This is the condition under which diversification reduces portfolio risk.

2

Every possible mix — the risk × return frontier

Each point on the curve is a coffee/cotton blend, from 0% to 100% coffee (hover any point for its exact mix). The curve bends left: combining the two reaches lower risk than either asset alone.
35%17%25%23%15%29%5%35%-5%41%Risk — annualized volatility (%)Annualized return (%)0% coffee / 100% cotton — risk 22.5% · return 0.8%5% coffee / 95% cotton — risk 21.6% · return 2.2%10% coffee / 90% cotton — risk 20.8% · return 3.7%15% coffee / 85% cotton — risk 20.2% · return 5.2%20% coffee / 80% cotton — risk 19.9% · return 6.6%25% coffee / 75% cotton — risk 19.8% · return 8.1%30% coffee / 70% cotton — risk 20.0% · return 9.6%35% coffee / 65% cotton — risk 20.4% · return 11.0%40% coffee / 60% cotton — risk 20.9% · return 12.5%45% coffee / 55% cotton — risk 21.8% · return 13.9%50% coffee / 50% cotton — risk 22.7% · return 15.4%55% coffee / 45% cotton — risk 23.9% · return 16.9%60% coffee / 40% cotton — risk 25.2% · return 18.3%65% coffee / 35% cotton — risk 26.6% · return 19.8%70% coffee / 30% cotton — risk 28.1% · return 21.2%75% coffee / 25% cotton — risk 29.6% · return 22.7%80% coffee / 20% cotton — risk 31.3% · return 24.2%85% coffee / 15% cotton — risk 33.0% · return 25.6%90% coffee / 10% cotton — risk 34.8% · return 27.1%95% coffee / 5% cotton — risk 36.6% · return 28.6%100% coffee / 0% cotton — risk 38.5% · return 30.0%Efficient frontierdominated — same risk, less returnCotton alone — risk 22.5% · return 0.8%100% Cotton0.8% ret · 22.5% riskCoffee alone — risk 38.5% · return 30.0%100% Coffee30.0% ret · 38.5% riskMinimum-variance mix — 24% coffee / 76% cotton · risk 19.8% · return 7.9%Minimum-variance mix24% coffee · 19.8% risk
View the frontier as a table
CoffeeCottonRisk (vol p.a.)Return p.a.
0%100%22.5%0.8%
5%95%21.6%2.2%
10%90%20.8%3.7%
15%85%20.2%5.2%
20%80%19.9%6.6%
25%75%19.8%8.1%
30%70%20.0%9.6%
35%65%20.4%11.0%
40%60%20.9%12.5%
45%55%21.8%13.9%
50%50%22.7%15.4%
55%45%23.9%16.9%
60%40%25.2%18.3%
65%35%26.6%19.8%
70%30%28.1%21.2%
75%25%29.6%22.7%
80%20%31.3%24.2%
85%15%33.0%25.6%
90%10%34.8%27.1%
95%5%36.6%28.6%
100%0%38.5%30.0%

Finding — the diversification gain

24% / 76%coffee / cotton (min-variance mix)
19.8%portfolio risk (vs 22.5% cotton alone)
−12%lower risk than cotton alone
+7.1 ppmore return than cotton alone

Holding 24% coffee and 76% cotton carries 19.8% risk — below cotton on its own (22.5%) — while returning 7.9% versus cotton's 0.8%. Adding a slice of the riskier asset made the portfolio both safer and more profitable than the safe asset alone: because the two barely correlate (0.05), their day-to-day shocks offset. That is diversification, quantified on real market data.

3

Why the inputs look the way they do

The two series behind the frontier: the price paths that set each asset's return & risk, and the rolling correlation that keeps them independent.

Price paths — coffee vs cotton (shared US¢/lb scale)

Coffee's rally drives its high return and high risk; cotton stays flat and low-volatility.
CoffeeCotton
46235625014438Jul/23Mar/24Oct/24May/25Dec/25Jul/26

Rolling 63-day correlation of daily returns

Hovers near zero throughout — the diversification assumption holds over time.
0.300.150.01-0.13-0.270Oct/23May/24Dec/24Jun/25Jan/26Jul/26
4

How this study was built

A production-shaped data pipeline: automated capture, a relational store, and a self-contained web page rendered from the same data.
Pythonpandas · numpy · yfinance

End-to-end ETL (pipeline.py): extracts 3 years of daily ICE futures, computes returns, annualized volatility, drawdown, rolling correlation and the closed-form minimum-variance weights; renders this page as pure SVG (build_dashboard.py).

SQL · SQLiteanalytical store

The pipeline loads prices, daily metrics and correlation into a relational store with an analytical view; queries.sql documents 10 production-style queries — joins, window functions, time aggregations, CASE logic.

Interactive SVG datavizno chart library

Charts are rendered as SVG — server-side by build_dashboard.py for the initial paint, then enhanced with hand-written JS: a frontier explorer, a date-range filter and a hover crosshair. No chart library, no runtime dependency, and the frontier ships a table twin for accessibility.

GitHub Actionsscheduled automation

A cron workflow runs the pipeline after every ICE close (weekdays), commits the refreshed data and redeploys this page via GitHub Pages — the study refreshes with no server to maintain.

Method. Annualized return = mean daily return × 252; risk = standard deviation of daily returns × √252; ρ = realized correlation of the two daily-return series. The frontier sweeps the coffee weight from 0→100% using the two-asset portfolio variance with the real ρ; the minimum-variance weight is the closed-form two-asset solution. The efficient frontier is the branch above that point — every mix below it is dominated (same risk, less return). All figures computed from real ICE futures prices (Yahoo Finance). Educational study — not investment advice.