Real-time commodity price tracking across 4 regions with automated data ingestion
A web-based price intelligence dashboard for agrochemical procurement teams — auto-collecting from World Bank, FRED, UN Comtrade, and FAOSTAT, with FX conversion, landed cost calculation, regional comparison, and configurable price alerts.
The Challenge
Agrochemical companies source raw materials from global markets — glyphosate from China, sulphur from the Middle East, phosphorus from Morocco, potash from Belarus and Canada. Procurement teams need current market prices across these regions to make informed purchasing decisions, negotiate with suppliers, and forecast costs.
The data exists in public sources: World Bank commodity price indices, the Federal Reserve Economic Data (FRED), UN Comtrade trade statistics, FAOSTAT agricultural data. But manually collecting this data weekly was consuming hours of analyst time. Currency fluctuations between USD, EUR, CNY, INR, and BRL added another layer of complexity. By the time spreadsheets were updated, the data was already stale.
The client needed a system that could automatically collect price data from multiple sources, normalize units and currencies, detect when sources go stale, and alert the team to significant price movements — all without manual intervention.
Our Approach
We designed an automated ETL (Extract-Transform-Load) pipeline that runs on scheduled intervals. Each data source has its own connector module that handles the specific API quirks, rate limits, and data formats. World Bank provides monthly commodity indices. FRED offers more granular time series. UN Comtrade has trade volume and value data by country pair.
The transformation layer standardizes everything: units converted to metric, currencies converted via the Frankfurter API (a free, reliable FX data source), timestamps normalized to UTC. We built a data freshness monitoring system that flags when a source hasn't updated within its expected interval.
The key architectural decision was separating raw data ingestion from derived analytics. Raw prices are stored as-is with full provenance. Derived metrics — landed costs, regional spreads, year-over-year changes — are computed on read. This means historical data is never rewritten, and analytics can be refined without touching the source data.
What We Built
Live Dashboard
Real-time FX rate strip showing major currency pairs. Material price cards with current price, regional breakdown, and trend indicators. Data freshness panel showing last update time for each source. Market movers section highlighting commodities with significant recent changes.
Price Explorer
Full commodity price database with filtering by material, region, and time period. Sortable tables with inline sparklines. Historical trend charts with customizable date ranges. One-click Excel export for teams who need data in spreadsheets.
Regional Comparison
Side-by-side comparison of prices across China, India, Brazil, and EU. Radar charts showing relative pricing across regions. Landed cost calculator that adds freight, duties, and handling to arrive at true procurement cost.
Alert System
Configurable price alerts: trigger when a commodity goes above or below user-defined thresholds. Data staleness alerts when sources stop updating. Email and in-app notifications keep the procurement team informed.
Admin Panel
User management with role-based access. Material database maintenance. ETL source monitoring with run history and error logs. Complete audit log of all system changes.
The Outcome
The procurement team now has real-time visibility into global commodity prices across 4 regions. Price alerts enable proactive purchasing decisions — they can buy ahead when prices are favorable or delay when markets are volatile. Hours of weekly manual data collection have been eliminated.