An end-to-end automated system that collects global cotton market data from four distinct sources, feeds it through XGBoost prediction models, and surfaces everything through a live Streamlit analytics dashboard.
Each pipeline component handles a completely different source type — REST API, PDF documents, a JavaScript-rendered web app, and government report files. Every component feeds the same database.
Fetches weekly cotton export data for 8 commodity codes across two marketing years. Computes marketing year, week number, and grand totals — then stages before promoting to the main table.
Auto-detects PDF structure type (single-year, dual-year, CIF Europe, dual-index, multi-index) and routes to the correct parser. Outputs price, change, and shipment data for 20+ cotton growths per document.
Logs into CCF Group, navigates to ECharts dashboards, and extracts 70+ yarn price series via JavaScript injection. Chrome → Firefox fallback, virtual display support, and full crash recovery.
Pulls legacy and disaggregated Commitments of Traders reports. Normalises 30+ column name variants, filters for current-year data, and bulk-loads to the production reporting table.
Computes First Notice Dates for the next 6 active cotton futures contracts, accounting for US federal holidays across all years. Inserts only net-new contracts — never duplicates.
An 8-CTE SQL view aggregates grand totals, computes Shipment % of Final against a 5-year historical average, and auto-refreshes after every pipeline run to serve the latest week's KPIs.
Both models were trained on the data this pipeline collects. Feature engineering was done manually — drawing on domain knowledge of how cotton market signals interact across time horizons.
Predicts the Cotlook A Index price using a combination of lagged price signals, COT positioning, export sales momentum, and seasonal patterns.
Predicts cotton futures open interest levels, capturing changes in market participation driven by speculative positioning and fundamental export signals.
All features were hand-crafted — no automated feature selection libraries. Each group reflects a deliberate hypothesis about what drives cotton prices and open interest.
A Streamlit dashboard connects directly to the PostgreSQL database and presents all pipeline outputs alongside model predictions in a single interface — updated automatically after every pipeline run.
Actual vs. predicted Cotlook A Index plotted over time. Model confidence bands and week-ahead forecasts shown alongside raw price history.
Weekly futures open interest with XGBoost model forecast overlay. Useful for anticipating changes in market participation before they're reported.
Live weekly export sales and accumulated shipments from USDA. Includes the Shipment % of Final KPI computed by the materialized view.
CFTC Commitments of Traders data shown as net long / net short bars by trader category. Highlights speculative vs commercial positioning shifts week by week.
Interactive multi-series chart showing 70+ global yarn price series from CCF alongside the Cotlook A Index and composition prices. Users can filter by origin country, yarn type, and date range — all sourced live from the PostgreSQL database the scraper populates.
Multi-schema PostgreSQL on AWS RDS. Raw data lands in staging, gets validated, promoted to production, and then aggregated into a materialized analytics layer — all in a single automated run.
A modern Python data stack from ingestion to prediction to visualisation. Everything connects — the same data that's scraped feeds the models that feed the dashboard.
REST API calls, headless browser automation with Chrome/Firefox fallback, adaptive PDF parsing.
DataFrame pipelines, column normalisation, date arithmetic, numeric cleaning, type coercion.
Two regression models with hand-crafted feature engineering across time-series, COT, and fundamental data.
Five-view interactive dashboard. Live DB connection. Actual vs predicted charts, COT positioning, export KPIs.
Multi-schema DB on AWS eu-north-1. psycopg2 for direct control, SQLAlchemy for bulk loads.
8-CTE view auto-refreshed per run, computing Shipment % of Final against a 5-year historical benchmark.
Scheduled daily runs. Full execution logged to staging_tables.cron_jobs_logs for observability.
Production on AWS eu-north-1. Headless browser support, pyvirtualdisplay, emergency crash saves.
Every component lives in its own module. The ML and dashboard layers are clearly separated from the data pipeline.