Commodities Index Analysis Dashboard
The Commodities Index Dashboard Analysis project was conducted during my Risk Analytics internship (converted to contract staff) at GIC Pte Ltd within the Risk Performance & Management Department (RPMD).
Presented this project to external guests during Temasek Polytechnic yearly InfoTech Day.
It is an end-to-end analytics project where I built an interactive dashboard for granular commodities index analysis. Spanning the full pipeline from automated data extraction to visualization, the solution cut data refresh time by 3x, reduced human error risk, and was adopted by the team Vice President. It also served as a reference model for the Technology Group team to industrialize similar tools across other market indices.
Tools: Tableau, SQL, Python, VBA, Bloomberg API, Excel
The Problem
The enterprise reporting team produces reports and commentaries on market indices to support stakeholder decisions, but their existing workflow for commodities had three friction points:
Lack of granular information: analysts couldn't easily drill down from the total commodity index into its underlying components and constituents.
Manual updating: refreshing data took 30+ minutes per cycle and was prone to human error as it was done solely on an Excel file.
Large and unstructured data: the existing format wasn't built for visualization, making chart-building slow and inefficient. The large dataset often result in delays and long-waiting time for the calculations and chart to be built in Excel.
The Objective
Build a dashboard for granular commodities analysis that would (a) automate the data pipeline, (b) deliver intuitive interactive visualizations, and (c) serve as a reference model for the Technology Group to industrialize similar tools across other market indices.
What I Built
An end-to-end analytics solution covering the full pipeline from raw data to interactive dashboard:
Automated data pipeline
Extracted data from internal databases using SQL queries
Pulled index returns at multiple levels of granularity via Bloomberg API
Used VBA to auto-generate analysis-ready tables at the click of a button
Collaborated with Technology Team to develop a Python script to merge data across internal databases, resolving naming inconsistencies and futures rolling-period complexities
Proof of Concept (POC)
Validated that an external benchmark could be substituted for the internal commodity index to enable granular analysis.
The POC confirmed component and constituent returns rolled up correctly to the total-level index, with explainable anomalies traced to known market events.
Interactive Tableau dashboards
Three dashboards designed for different analytical depths:
Overview: overall index performance, trend of total return, filterable by timespan, frequency, and date granularity
Component & Constituent Breakdown: granular drill-down and side-by-side comparison
Trend Analysis: cumulative impact and period-by-period performance, with constituents color-coded as shades of their parent component
Built using Tableau calculated fields to enable on-the-fly metrics across multiple timespans (MTD, QTD, YTD, and custom date ranges).
Documentation
Authored a user guide and POC documentation to support handover, future maintenance, and knowledge transfer to the Technology Group.
Key Challenges
Mid-project database migration
SQL queries had to be rebuilt against a new database within a tight window.
I partnered with the Technology Group to build a Python-based workaround that resolved cross-database mapping issues.
Visualization tool limitations
Tableau couldn't display returns for different time periods (MTD, QTD, YTD) side-by-side in a single view.
I worked around this by creating separate filtered sheets that all pulled from one shared data source, giving users the full comparison while keeping the dashboard simple to update later.
example (may not be the actual calculation):
Learning the commodities methodology from scratch
I had no prior exposure to index return computation, rolling periods, or futures contracts. I manage to closed the gap through self-directed research and actively consulting with my team.
Business Impact
✔️ 3x faster data refresh: from 30+ minutes to under 10 minutes per monthly cycle
✔️ Reduced human error risk in a workflow that directly feeds stakeholder decision-making
✔️ Dashboard adopted by the team Vice President for ongoing commodities analysis
✔️ Served as a reference model for the Technology Group to industrialize similar dashboards across other market indices
✔️ Performance contributed to a 5-month contract extension beyond the original internship
What I'd Improve
Having Multiple Sheets
The overview dashboard relied on multiple sheets to replicate a tabular format, which is harder to maintain long-term. A cleaner solution would consolidate this for better future-proofing.
Color Palette
Color-coding components vs constituents occasionally made dashboards feel visually busy; a more restrained palette would improve scannability. However, this was experimented with and was unable to be done as the data was too granular i.e. too many component and constituents which made it difficult to stick with a few colours.
Conclusion
"My biggest takeaway was learning how to deliver work that genuinely makes someone else's job easier. This experience was extremely fulfilling."
This project was equally fun, challenging, and insightful to work on. From learning the commodities methodology from scratch to navigating a mid-project database migration, the obstacles along the way taught me far more than the technical skills gained across SQL, Python, VBA, Tableau, and Bloomberg API.
What stuck with me was the value of self-directed learning, asking the right questions, and designing solutions with future maintainers in mind. I'm grateful to have been given the opportunity to contribute to something meaningful for the team and organisation.
This definitely served as a great learning experience and stepping stone for future projects and workplace experiences ahead.