From spreadsheets to systems: The evolution of commodity analytics

By Jon Hill, Managing Director, Commodities and Global Markets

Introduction

Commodities trading begins with understanding the physical world: predicting renewable generation, tracking ship movements, forecasting gas pipeline flows and interpreting thousands of other signals from real-world infrastructure and markets. Go far enough back and fundamental analytics was about one person with Excel or Python on their desktop, manually downloading data, running local data scrapes or querying internal databases.

Solo to shared

The past few years have seen an explosion in sophistication and scale – in data, technology, and models. At Macquarie, we started this journey eight years ago, recognising that our fundamental analysts were bound by the limits of the desktop – its storage and compute – and tended to work as individuals. Our strategy can be summarised as a journey from ‘solo to shared’, comprising three elements:

• Shared data – from the desktop to a well-catalogued cloud-hosted data lake which could scale and cope with terabytes of weather or market data each day, as well as having any dataset onboarded only once, managed centrally and available to all
• Shared analytics platforms – cloud-hosted and web accessible data science, machine learning and quant platforms that would be widely available with access to compute at scale
• Shared knowledge – projects, tools and methods, supporting collaboration and shared code, along with the sharing of know-how on technology platforms and fundamental modelling approaches

Today, this ‘shared’ approach is very much in evidence. While we still have legacy solutions, our fundamental analytics runs on cloud-hosted data science platforms, accessing data within our data lake, scheduled to run on whatever period is needed, and operating at a scale far beyond what was possible in the desktop-bound past.

One consequence of the adoption of technology is the increasing dependencies on people and teams beyond the individual fundamental analyst, which has changed the way our analysts work. Developing and supporting cloud-hosted platforms, particularly in a regulated environment, benefits from a dedicated technology team with the right engineering expertise. Similarly, managing hundreds of data pipelines and streaming infrastructure can be delegated to specialist teams, allowing analysts to focus on desk-specific activities rather than platform development and maintenance.

While initial development can be more complex as it involves collaboration across different teams, the outcome is more robust and scalable as it is based on the combined expertise of analysts, traders and engineers, and built on scalable platforms. The long-term benefits are also significant, with support and ownership distributed across specialist teams, allowing analysts and traders to focus on generating business value rather than maintaining infrastructure.

Scale and sophistication

One analytics solution developed by the data science team for a trading desk comprises more than 200 machine learning models (both current production and challenger) which consume each of the 81 member cycles from the European Centre for Medium Weather Forecasting (ECMWF) and NOAA's GFS (the US National Oceanic and Atmospheric Administration's Global Forecast System) ensemble forecasts looking out 15 days at hourly intervals, along with a range of gas storage, production, pricing and contract data. Streamlit webapps are used to present forecasts and back-cast results, while notifications are pushed via Teams, refreshing every 12 hours.

This level of sophistication, utilising data feeds, data lakes, analytics platforms, deep business knowledge, machine learning expertise and UX capabilities is now the norm, and far beyond what was usual only a few years ago in the ‘desktop-bound’ world.

The increase in analytical sophistication has occurred against a backdrop of profound change in the underlying commodity markets themselves. Five years ago, many power systems looked fundamentally different from today. ERCOT, the Texas power grid, for example, has seen dramatic growth in renewable generation and battery storage, with solar generation capacity rising from around 7 GW in 2021 to around 35 GW by mid-2026, contributing up to a third of power generation at peak and changing both operational dynamics and price behaviour. Climate-related shifts in weather patterns are increasing the importance of accurate forecasting, while geopolitical events can rapidly influence gas, metals and energy markets across multiple regions.

At the same time, richer data sources, from shipping movements to infrastructure telemetry, allow analysts to observe the physical world in ever greater detail. The result is that commodity analytics is not simply becoming more sophisticated because technology allows it, but because the markets themselves increasingly demand it.

Probabilistic forecasting

Another dimension of increasing sophistication is the use of probabilistic forecasts. Once the preserve of market risk, these aim to provide a full distribution of possible future outcomes, rather than a single expected ‘deterministic’ price, demand, power generation and so on. They achieve this by varying the inputs to the model – most commonly running each of the member cycles from ensemble weather forecasts separately. Additional scenario shocks can also be applied. For example, if the grid operator forecasts 10 GW of outages, analysts may re-run the model across a range of outage assumptions around that baseline to understand how sensitive price forecasts are to outages.

Running each of the 81 member cycles from ECMWF and GFS together with, for example, 10 outage values results in 810 model runs. Adding additional shocks, or varying estimates, especially at granular levels of network topology – congestion of power transmission at specific points, constraints on specific gas pipeline segments – can quickly scale to thousands or tens of thousands of scenarios.

Time, compute, data and cost form challenges at such scale – what compute can you bring to bear on the problem, to solve within what timescale and at what cost? Fundamental analysts now need to understand the economics of their solutions to a far greater extent than in the old world of local compute.

Solutions and support

Advances in technology have enabled fundamental analytics teams to develop a greater range of solutions far faster than previously possible. Many years ago, our first wind power generation forecast machine learning model, for one European market, took around nine months of elapsed time to develop to a point where it was performing sufficiently well for production. Today, we would expect  someone with strong quantitative and technical skills, regardless of seniority and with no prior commodities experience, to have a comparable renewable forecasting model running within a couple of weeks, with most of that time spent learning, sourcing and preparing the data, validation, and integration with existing systems.

Similarly, our first image classification model, looking for anomalies in live thermal images of a specific type of industrial plant, was a major undertaking over many months developing the underlying capabilities needed for such a solution – data labelling, annotation schemes, GPU access, etc. With these capabilities in place and the knowledge gained of how to approach this type of problem, it now takes a team member a couple of weeks to annotate a training dataset, train an image classification model, connect it to live images and progress it to production with notifications enabled.

This doesn't mean the problems are trivial. Rather, it reflects how much of the foundational capability has become reusable, allowing analysts to focus on the business problem rather than rebuilding infrastructure from scratch. The upshot of these advances, compounded further by developments in AI-assisted coding, is an explosion in the number, type and sophistication of solutions being built and deployed by analytics individuals and teams.

As with any system though, removing a constraint moves the bottleneck elsewhere. In commodities analytics, although some number of solutions feed into autonomous, systematic trading, most of the analytics arrive on the screen of a trader to provide signals, market colour or some call to action. As data, models and compute become increasingly abundant, a new challenge emerges: ensuring that the right information reaches the right people at the right time.

UX – The new frontier

A system that looks out 45 days across 100 gas hubs, driven by 81 weather ensemble cycles and including storage, contracts, production and outages, can easily serve up to half a million data points, all being updated through the trading day. The trader or anayst who once had a dozen spreadsheet models to pay attention to, now has a seemingly limitless number of dashboards, applications and notifications with countless widgets, filters and drop-downs vying for their attention.

Another angle often discussed is that it is easier to provide accurate predictions when things are at seasonal norms and conditions are benign and well reflected in the training data. However, that’s when the forecasts are least useful, as trading desks have the experience to know what normal is and what to expect. The question is, how do the models fare when we’re looking at extremes of weather, of outages, of storage? How do desks get a sense of how much trust they can put in the forecasts? And how can that intuition be served in a way that can be understood in seconds at the start of the day?

Fundamental analysts have focused traditionally on the maths, modelling, technical skills and commodities knowledge needed to understand markets, build models and develop solutions. Where ‘user experience’ is discussed, it’s the interface – what drop-downs would you like – or technology – are we doing this in React or Streamlit? As fundamental analytics solutions continue to grow in numbers and sophistication, analysts and their teams will have an increasing focus on the experience of users, surfacing only information that’s actionable. A possible end goal is a machine that understands your domain of interest and can match that to data and events which are of direct relevance to you, as a trader, and serves that information up to you at the time you need it.

Conclusion

Looking at fundamental analytics today, the challenge is no longer simply building models. The opportunity lies in combining domain knowledge, data engineering, machine learning and user-centric design to create analytics that help traders make better decisions. The tools have become dramatically more powerful, but the goal remains unchanged: turning information into insight and insight into action.