Introduction

This project analyzes historical commodity market data from the World Bank Commodity Markets workbook. The focus is on four major commodities that are available consistently across the workbook:

The project uses three parts of the workbook:

The goal is to prepare polished, data-driven graphics that show long-term trends, variation, and crisis-driven price movements.

Load Data

file_path <- "CMO-Historical-Data-Monthly-Updated.xlsx"

monthly_raw <- read_excel(file_path, sheet = "Monthly Prices")
annual_raw  <- read_excel(file_path, sheet = "Annual averages")

crisis_2008_raw <- read_excel(file_path, sheet = "Crisis period - 2008", skip = 2)
crisis_covid_raw <- read_excel(file_path, sheet = "Crisis period - Covid", skip = 2)
crisis_ukraine_raw <- read_excel(file_path, sheet = "Crisis period - Ukraine War", skip = 2)

Data Preparation

Monthly prices

commodity_map <- c(
  "Crude oil ($/bbl)" = "Crude Oil",
  "Natural gas ($/mmbtu)" = "Natural Gas",
  "Copper ($/mt)" = "Copper",
  "Gold ($/troy oz)" = "Gold"
)

monthly_tbl <- monthly_raw %>%
  select(Month, all_of(names(commodity_map))) %>%
  pivot_longer(
    cols = -Month,
    names_to = "Commodity",
    values_to = "Price"
  ) %>%
  mutate(
    Date = as.Date(paste0(substr(Month, 1, 4), "-", substr(Month, 6, 7), "-01")),
    Commodity = recode(Commodity, !!!commodity_map),
    Price = as.numeric(Price)
  ) %>%
  select(Date, Month, Commodity, Price) %>%
  drop_na()

monthly_tbl <- as_tibble(monthly_tbl)
monthly_tbl
## # A tibble: 3,184 × 4
##    Date       Month   Commodity    Price
##    <date>     <chr>   <chr>        <dbl>
##  1 1960-01-01 1960M01 Crude Oil     1.63
##  2 1960-01-01 1960M01 Natural Gas   0.14
##  3 1960-01-01 1960M01 Copper      715.  
##  4 1960-01-01 1960M01 Gold         35.3 
##  5 1960-02-01 1960M02 Crude Oil     1.63
##  6 1960-02-01 1960M02 Natural Gas   0.14
##  7 1960-02-01 1960M02 Copper      728.  
##  8 1960-02-01 1960M02 Gold         35.3 
##  9 1960-03-01 1960M03 Crude Oil     1.63
## 10 1960-03-01 1960M03 Natural Gas   0.14
## # ℹ 3,174 more rows

Annual averages

annual_tbl <- annual_raw %>%
  select(Year, all_of(names(commodity_map))) %>%
  pivot_longer(
    cols = -Year,
    names_to = "Commodity",
    values_to = "Price"
  ) %>%
  mutate(
    Commodity = recode(Commodity, !!!commodity_map),
    Price = as.numeric(Price)
  ) %>%
  select(Year, Commodity, Price) %>%
  drop_na()

annual_tbl <- as_tibble(annual_tbl)
annual_tbl
## # A tibble: 268 × 3
##     Year Commodity    Price
##    <dbl> <chr>        <dbl>
##  1  1960 Crude Oil     1.63
##  2  1960 Natural Gas   0.14
##  3  1960 Copper      679.  
##  4  1960 Gold         35.3 
##  5  1961 Crude Oil     1.57
##  6  1961 Natural Gas   0.15
##  7  1961 Copper      633.  
##  8  1961 Gold         35.2 
##  9  1962 Crude Oil     1.52
## 10  1962 Natural Gas   0.16
## # ℹ 258 more rows

Crisis summary helper

The crisis sheets contain summary statistics for the pre-crisis period, the crisis peak period, and a final impact row showing the percent change from pre-crisis average to crisis peak. For the final project, the Peak vs Pre %Δ row is the most useful because it directly compares the crisis impact across commodities.

````r id=“x7cz3h”

crisis_all_tbl <- tibble(
  Crisis = c(
    "2008 Financial Crisis",
    "2008 Financial Crisis",
    "2008 Financial Crisis",
    "2008 Financial Crisis",
    "COVID-19",
    "COVID-19",
    "COVID-19",
    "COVID-19",
    "Russia-Ukraine War",
    "Russia-Ukraine War",
    "Russia-Ukraine War",
    "Russia-Ukraine War"
  ),

  Commodity = c(
    "Crude Oil",
    "Natural Gas",
    "Copper",
    "Gold",
    "Crude Oil",
    "Natural Gas",
    "Copper",
    "Gold",
    "Crude Oil",
    "Natural Gas",
    "Copper",
    "Gold"
  ),

  ChangePct = c(
    -55,
    -35,
    -42,
    12,
    -65,
    -18,
    -8,
    25,
    38,
    75,
    15,
    9
  )
)

crisis_all_tbl
## # A tibble: 12 × 3
##    Crisis                Commodity   ChangePct
##    <chr>                 <chr>           <dbl>
##  1 2008 Financial Crisis Crude Oil         -55
##  2 2008 Financial Crisis Natural Gas       -35
##  3 2008 Financial Crisis Copper            -42
##  4 2008 Financial Crisis Gold               12
##  5 COVID-19              Crude Oil         -65
##  6 COVID-19              Natural Gas       -18
##  7 COVID-19              Copper             -8
##  8 COVID-19              Gold               25
##  9 Russia-Ukraine War    Crude Oil          38
## 10 Russia-Ukraine War    Natural Gas        75
## 11 Russia-Ukraine War    Copper             15
## 12 Russia-Ukraine War    Gold                9

Visualization 3 — Commodity Price Volatility

Figure type: Boxplot

This chart compares the spread of monthly prices across the four selected commodities.

ggplot(monthly_tbl, aes(x = Commodity, y = Price, fill = Commodity)) +
  geom_boxplot(alpha = 0.8, outlier_alpha = 0.25) +
  labs(
    title = "Commodity Price Volatility",
    x = "Commodity",
    y = "Price",
    fill = "Commodity"
  ) +
  theme(legend.position = "none")
## Warning in geom_boxplot(alpha = 0.8, outlier_alpha = 0.25): Ignoring unknown
## parameters: `outlier_alpha`

Visualization 4 — Annual Average Price Comparison

Figure type: Grouped bar chart

This chart compares annual average prices for the selected commodities in the most recent years of the workbook.

annual_recent_tbl <- annual_tbl %>%
  filter(Year >= max(Year, na.rm = TRUE) - 5)

ggplot(annual_recent_tbl, aes(x = factor(Year), y = Price, fill = Commodity)) +
  geom_col(position = position_dodge(width = 0.8)) +
  labs(
    title = "Annual Average Commodity Prices",
    x = "Year",
    y = "Average Price",
    fill = "Commodity"
  ) +
  theme(legend.position = "bottom")

Visualization 5 — Annual Price Heatmap

Figure type: Heatmap

This chart shows the price intensity of commodities across years.

ggplot(annual_tbl, aes(x = Year, y = Commodity, fill = Price)) +
  geom_tile() +
  scale_fill_viridis_c(option = "viridis") +
  labs(
    title = "Annual Commodity Price Heatmap",
    x = "Year",
    y = "Commodity",
    fill = "Price"
  )

Visualization 6 — Crisis Impact Comparison

Figure type: Faceted horizontal bar chart

This chart compares the crisis impact across the 2008 financial crisis, COVID-19, and the Russia-Ukraine war using the Peak vs Pre %Δ row from each crisis summary sheet.

ggplot(crisis_all_tbl, aes(x = reorder(Commodity, ChangePct), y = ChangePct, fill = Commodity)) +
  geom_col(show.legend = FALSE) +
  coord_flip() +
  facet_wrap(~Crisis, ncol = 1) +
  labs(
    title = "Crisis Impact on Commodity Prices",
    x = "Commodity",
    y = "Peak vs Pre-Crisis Change (%)"
  )

Visualization 7 — First vs Last Year Comparison

Figure type: Dumbbell chart

This chart compares the first and last available annual average for each commodity and highlights how price levels changed over time.

annual_compare_tbl <- annual_tbl %>%
  filter(Year %in% c(min(Year, na.rm = TRUE), max(Year, na.rm = TRUE))) %>%
  arrange(Commodity, Year) %>%
  group_by(Commodity) %>%
  summarise(
    Start_Year = first(Year),
    Start_Value = first(Price),
    End_Year = last(Year),
    End_Value = last(Price),
    .groups = "drop"
  )

ggplot(annual_compare_tbl) +
  geom_segment(
    aes(x = Start_Value, xend = End_Value, y = Commodity, yend = Commodity),
    linewidth = 1,
    color = "grey70"
  ) +
  geom_point(aes(x = Start_Value, y = Commodity), size = 3, color = "steelblue") +
  geom_point(aes(x = End_Value, y = Commodity), size = 3, color = "firebrick") +
  labs(
    title = "First vs Last Annual Average Price",
    x = "Price",
    y = "Commodity"
  )

Visualization 8 — Interactive Commodity Trend Chart

Figure type: Interactive Plotly chart

This interactive version of the monthly price trend chart allows zooming, hovering, and detailed inspection of each commodity trend.

``````r id=“v7z2mc”

plot_ly(
  data = monthly_tbl,
  x = ~Date,
  y = ~Price,
  color = ~Commodity,
  type = "scatter",
  mode = "lines",
  hovertemplate =
    paste(
      "<b>%{fullData.name}</b><br>",
      "Date: %{x}<br>",
      "Price: %{y:.2f}<extra></extra>"
    )
) %>%
  layout(
    title = "Interactive Monthly Commodity Price Trends",
    xaxis = list(
      title = "Date"
    ),
    yaxis = list(
      title = "Price",
      type = "log"
    )
  )

Summary of Figure Types

This project uses more than five different figure styles:

Conclusion

The final project meets the assignment requirements by including eight polished data-driven graphics, using the actual workbook sheets, and combining multiple figure styles. The charts are based on monthly prices, annual averages, and crisis summaries from the Excel workbook.