EIA Data Updates

The US Energy Information Administration (EIA) provides a wealth of data for energy markets. This update focuses on their regularly-updated oil and gas data sources including:

• Spot Prices and Some Futures Market Info
• Weekly Petroleum Status Report
• Weekly Gas Storage Report
• Oil and gas production updates
  • Monthly Production Data by Region
  • Drilling Productivity Reports
  • Shale Gas and Light Tight Oil Production Reports
• Short Term Energy Outlook
• Annual Energy Outlook

For each section, I’ve included R code to access the data as well as links to the web interface for the data. The R code relies on stadard Tidyverse packages as well as a couple of custom functions and an API key. You should be able to run this with the code provided once you get your own key here, and you’ll have to store it in a variable called KEY to get the code to work.

If you’re going to run the R code, you’ll need a few basic set-up elements to get everything to work. I’ve included the code here for your reference.

#packages used
library(RColorBrewer)
library(viridis)
library(scales) 
library(pdfetch)
library(tidyverse)
library(lubridate)
library(reshape2)
library(knitr)
library(prettydoc)
library(xml2)
library(zoo)
library(openxlsx)
library(readxl)

data_fetch<-function(key, cat){
  #key<-KEY
  #cat=476336
  ifelse(cat==999999999,
         url <- paste("https://api.eia.gov/category/?api_key=",
                      key, "&out=xml", sep="" ),
         url <- paste("https://api.eia.gov/category/?api_key=",
                      key, "&category_id=", cat, "&out=xml", sep="" )
  )
  
  #http://api.eia.gov/category/?api_key=YOUR_API_KEY_HERE&category_id=476336
  #url <- paste("https://api.eia.gov/category?api_key=",
  #             key, "&category_id=", cat, "&out=xml", sep="" )
  #https://api.eia.gov/category/?api_key=91b4dca0b858df64a2279d82f71af240&category_id=476336&out=xml
  #https://api.eia.gov/category?api_key=91b4dca0b858df64a2279d82f71af240&category_id=476336&out=xml
  
  x <- read_xml(url)
  doc <- XML::xmlParse(file=x)
  
  
  Parent_Category <- tryCatch(XML::xmlToDataFrame(,stringsAsFactors = F,nodes =
                                               XML::getNodeSet(doc, "//category/parent_category_id")),
                              warning=function(w) FALSE, error=function(w) FALSE)
  Sub_Categories <- XML::xmlToDataFrame(,stringsAsFactors = F,nodes =
                                     XML::getNodeSet(doc, "//childcategories/row"))
  Series_IDs <- XML::xmlToDataFrame(nodes =
                                 XML::getNodeSet(doc, "///childseries/row"),stringsAsFactors = F)
  Categories <- list(Parent_Category, Sub_Categories, Series_IDs)
  names(Categories) <- c("Parent_Category", "Sub_Categories", "Series_IDs")
  Categories
}

 get_children<-function(category_id=476336){
   subs<-data_fetch(KEY,cat=category_id)
   sub_cats<-subs$Sub_Categories
   #build list from sub_cats
   cat_store <- list()
   cat_count<-1
   for (cat in sub_cats$category_id) {
     #cat<-sub_cats$category_id[1]
     series<-data_fetch(KEY,cat=cat)
     cat_store[[cat_count]]<-series$Series_IDs
     cat_count<-cat_count+1
   }
   data.frame(do.call(rbind,cat_store))
 }
 #get_children()
 
 get_series<-function(category_id=476336){
   #series,name,f,units,updated
   subs<-data_fetch(KEY,cat=category_id)
   subs$Series_IDs
 }
 #get_series()
 
 pd_fix<-function(data,name){
   data<-data.frame(date=index(data), coredata(data))
   data$date<-ymd(data$date)
   data <- setNames(data, c("date",name)) 
 }
 
 EIA_to_DF<-function(series_info){
   data<- pdfetch_EIA(series_info$series_id,KEY)
   pd_fix(data,series_info$name)
   }

#Commodity Prices

Both the EIA data interface here or the API here allow you to access benchmark commodity prices we’ll talk a lot about in the first couple of classes. For most of what we do with prices, we’ll use data from the Bloomberg terminal but the EIA prices are a useful source of information that you can access from anywhere.

The first step is to access the data (click on the code button to see how to do things in R if you’re interested).

#don't need to cache this long-term - it's daily data
#GET EIA NYMEX SPOTS 
subs<-data_fetch(KEY,cat=241335)
series<-t(subs$Series_IDs$series_id)
series<- as.character(series)
names<-t(subs$Series_IDs$name)

subs<-data_fetch(KEY,cat=241347)
series<-c(series,t(subs$Series_IDs$series_id))
series<- as.character(series)
names<-c(names,t(subs$Series_IDs$name))


nymex_data<- pdfetch_EIA(series,KEY)
nymex_data <- setNames(nymex_data, names)
nymex_data<-data.frame(date=index(nymex_data), coredata(nymex_data),stringsAsFactors = F)
nymex_data$date<-as.Date(nymex_data$date,format = "%m/%d/%Y")

annual_crude<-cbind(nymex_data$date,nymex_data[,grep("Annual", colnames(nymex_data))])  #all annual columns
annual_crude<-na.omit(annual_crude)
names(annual_crude)[1]<-paste("Date")  

monthly_crude<-cbind(nymex_data$date,nymex_data[,grep("Monthly", colnames(nymex_data))])  #all monthly columns
monthly_crude<-na.omit(monthly_crude)
names(monthly_crude)[1]<-paste("Date")

weekly_crude<-cbind(nymex_data$date,nymex_data[,grep("Weekly", colnames(nymex_data))])  #all weekly_crude columns
#weekly_crude<-na.omit(weekly_crude)
names(weekly_crude)[1]<-paste("Date")
weekly_crude<-subset(weekly_crude, Date > as.Date("2007-01-01"))
weekly_crude<-na.omit(weekly_crude)

daily_crude<-cbind(nymex_data$date,nymex_data[,grep("Daily", colnames(nymex_data))])  #all weekly_crude columns
#weekly_crude<-na.omit(weekly_crude)
names(daily_crude)[1]<-paste("Date")

daily_crude$WTI_Brent_diff<- daily_crude$Europe.Brent.Spot.Price.FOB..Daily-daily_crude$Cushing..OK.WTI.Spot.Price.FOB..Daily

Then, we set up some graphing preliminaries. Again, R code provided for your interest.

#set up graph format
weekly_graphs<-function(caption_align=1){
  theme_minimal()+theme(
    plot.margin = margin(.25, .75, .25, .75, "cm"),
    legend.position = "bottom",
    legend.margin=margin(c(0,0,0,0),unit="cm"),
    legend.text = element_text(colour="black", size = 14, face = "bold"),
    plot.caption = element_text(size = 14, face = "italic",hjust=caption_align),
    plot.title = element_text(face = "bold"),
    plot.subtitle = element_text(size = 16, face = "italic"),
    panel.grid.minor = element_blank(),
    text = element_text(size = 14,face = "bold"),
    axis.title.x = element_text(size = 14,face = "bold", colour="black",margin = margin(t = 15, b = 0)),
    axis.title.y = element_text(size = 14,face = "bold", colour="black",margin = margin(r = 10)),
    axis.text = element_text(size = 14,face = "bold", colour="black",margin = margin(t = 10, b = 10)),
  )
}

weekly_small<-function(caption_align=1){
  theme_minimal()+theme(
    plot.margin = margin(.25, .75, .25, .75, "cm"),
    legend.position = "bottom",
    legend.margin=margin(c(0,0,0,0),unit="cm"),
    legend.text = element_text(colour="black", size = 9),
    plot.caption = element_text(size = 11, face = "italic",hjust=caption_align),
    plot.title = element_text(size = 16,face = "bold"),
    plot.subtitle = element_text(size = 14, face = "italic"),
    panel.grid.minor = element_blank(),
    text = element_text(size = 11,face = "bold"),
    axis.title.x = element_text(size = 11,face = "bold", colour="black",margin = margin(t = 15, b = 0)),
    axis.text = element_text(size = 11,face = "bold", colour="black",margin = margin(t = 10, b = 10)),
  )
}


#define levels chart design function
levels_chart<-function(data_sent,names,name_labels,years,title_sent="Benchmark Oil Prices",break_set="12 months",y_lab="Spot Prices ($US/bbl)")
{
  #send a data set and variable names
  #testing
    #data_sent<-daily_crude
    #names<-"Europe.Brent.Spot.Price.FOB..Daily"
    #name_labels<-"Brent"
    #years<-5   
    #break_set<-"12 months"
    #title_sent<-"Benchmark Oil Prices"
  rows_legend<-max(1,round(sum(nchar(names))/60))
  data_sent<-filter(data_sent,Date>=max(Date)-years(years))
  
  df1<-melt(data_sent,id=c("Date"),measure.vars = names)
  df1<-na.omit(df1)
  lims<-c(max(df1$Date)-years(years),max(df1$Date)+months(1))
  lims_y<-c(min(0,min(df1$value)-5),max(df1$value)+10)
  
  p<-ggplot(df1) +
    geom_line(data=filter(df1,variable!="diff"),aes(Date,value,group = variable,colour=variable),size=1.7) +
    #geom_point(size=1) +
    scale_colour_manual(NULL,values=colors_tableau10(), labels=name_labels)+
    scale_x_date(name=NULL,date_breaks = break_set, date_labels =  "%b\n%Y",limits=lims,expand=c(0,0)) +
    scale_y_continuous(expand = c(0, 0),limits=lims_y,breaks=c(-10,seq(0,lims_y[2],10))) +
    guides(colour=guide_legend(nrow=rows_legend))+
    labs(y=y_lab,x="Date",
         title=title_sent,
         caption="Data via EIA API")+
    weekly_graphs()
  p
}

Now we can make the graphs we need.

names<-c("Cushing..OK.WTI.Spot.Price.FOB..Daily","Europe.Brent.Spot.Price.FOB..Daily")
name_labels<-c("WTI (Cushing)", "Brent (Europe)")
years<-5   
break_set<-"12 months"
title_sent<-"Benchmark Oil Prices"
WTI_Brent<-levels_chart(daily_crude,names,name_labels,years,title_sent="North American and European Benchmark Oil Prices",break_set="12 months")

For example, if you access the data, you’ll see that the most recent prices for WTI and Brent are $US 79.86/bbl and $US 85.25/bbl respectively, based on the January 24, 2023 update. The most recent 5 years of data for WTI and Brent prices are shown below.

The EIA data library also provides a lot of other prices and we can use those to produce some of the other types of graphs we use in the class. For example, if you’re interested in refining margins, you can look at New York Harbour and Gulf Coast 3:2:1 crack spreads: the difference between the value of crude oil and the value of a refined product yield of 2/3 gasoline and 1/3 diesel.

# first, make the cracks
daily_crude<-daily_crude %>% mutate(
  gc_321=U.S..Gulf.Coast.Ultra.Low.Sulfur.No.2.Diesel.Spot.Price..Daily*42/3+ U.S..Gulf.Coast.Conventional.Gasoline.Regular.Spot.Price.FOB..Daily*42*2/3-Europe.Brent.Spot.Price.FOB..Daily,
  nyh_321=New.York.Harbor.Ultra.Low.Sulfur.No.2.Diesel.Spot.Price..Daily*42/3+New.York.Harbor.Conventional.Gasoline.Regular.Spot.Price.FOB..Daily*42*2/3 -Cushing..OK.WTI.Spot.Price.FOB..Daily
)

names<-c("gc_321","nyh_321")
name_labels<-c("Gulf Coast 3:2:1 Crack to Brent","NY Harbour 3:2:1 Crack to WTI")
years<-5   
break_set<-"12 months"
title<-"3:2:1 Crack Spreads"
y_label<-"3:2:1 Crack Spread ($/bbl)"

levels_chart(daily_crude,names,name_labels,years = years,title_sent = title,y_lab = y_label)

On the graph above, the last points imply that the gross profit margin from refining a barrel purchased at Brent prices on the Gulf Coast would be $US 38.51/bbl while a barrel purchased at WTI prices with refined products sold at NY Harbour prices would yield $US 38.51/bbl in gross profits, based on January 24, 2023 prices.

Weekly Petroleum Status Report

The EIA Weekly Petroleum Status Report is one of the most-watched data releases in energy markets. The report is released at 10:30 a.m. (Eastern time) on Wednesdays every week, with publication of the data to the API after 1:00 p.m. (Eastern Time) on Wednesday. You can see the full Petroleum Status Report here or you can access via the EIA API starting from here. For most of what follows, I’ll be using three data series: the full US weekly supply here and the Cushing oil stocks data here. You can download the data any way you like, but for this purpose, I’m grabbing the data with R as follows:

#first, build a list of series names we want to access
#get the information from the EIA weekly supply API
cat_store <- list()
name_store<-list()

subs<-data_fetch(KEY,cat=235081)
#take out the 4 week average data
series_ids<-subs$Series_IDs$series_id[-grep("\\b.4\\b",subs$Series_IDs$series_id)]
#make sure series ids aren't stored as factors
series_ids<- as.character(series_ids)
#same for names
names<-t(subs$Series_IDs$name[-grep("\\b.4\\b",subs$Series_IDs$series_id)])
cat_store[[1]]<-series_ids
name_store[[1]]<-names


#get the Cushing series ids and names
subs<-data_fetch(KEY,cat=235418)
series_ids<-subs$Series_IDs$series_id[]
names<-t(subs$Series_IDs$name[])
cat_store[[2]]<-series_ids
name_store[[2]]<-names

#get the gas storage series and IDs
subs<-data_fetch(KEY,cat=1709237)
series_ids<-subs$Series_IDs$series_id[]
names<-t(subs$Series_IDs$name[])
cat_store[[3]]<-series_ids
name_store[[3]]<-names

#stack all of them
series<-data.frame(do.call(c,cat_store))
name_list<-do.call(c,name_store)

#fetchez le data
all_data<- pdfetch_EIA(t(series),KEY)
all_data<-data.frame(date=index(all_data), coredata(all_data))
#fix the data names list
name_list<- gsub("\\.", " ", name_list)
name_list<- gsub("\\U S ", "US", name_list)
name_list<- gsub("  ", " ", name_list)
name_list<- gsub(", Weekly", "", name_list)
name_list<- gsub("US ", "", name_list)
name_list<- gsub("U S ", "", name_list)
name_list<- gsub("^\\s+|\\s+$", "",name_list)
#assign names
all_data <- setNames(all_data,c("date",name_list))
#build week and year indicators
all_data<-all_data%>% mutate(
week=week(date),
month=month(date),
year=year(date)
)

The weekly petroleum supply information is very detailed, and you’ll see markets move in response to its release. The most important series traders will watch are production, inventories, and trade data. I set up a basic graph to allow me to quickly compare the current release data with previous years and the trailing 5 year range. The graph code is provided under the Code icon for your reference.

ribbon_graph<-function(data_sent,series_name,unit_sent,years,alt_axis=FALSE){
#melt the data into long form
df1<-melt(data_sent,id=c("date","week","month","year"),measure.vars = series_name)
#remove NAs
df1<-na.omit(df1)
#truncate to 52 weeks for the years where there is a 53rd week.
df1$week[df1$week==53]<-52

#create range data
max_year<-max(df1$year)

#first, we want the ones we are going to graph - this year and last year
df.years <- df1 %>% 
  filter(year >= max_year-1)

#now we want the ones for the range
df.year.range <- df1 %>% 
  filter(year >= max_year - years & year < max_year) %>% 
  group_by(week) %>% 
  summarize(mean = mean(value), min = min(value), max = max(value))

y_lab<-paste(alt_axis,"\n(",unit_sent,")",sep="")
if(alt_axis==FALSE)
  y_lab<-paste(series_name,"\n(",unit_sent,")",sep="")

#We can then trick ggplot into printing a nice title for the fill on the legend, by setting fill inside aes to the intended string. Because fill is set in aes(), we control its color with scale_fill_manual.
graph<- ggplot() +
  geom_ribbon(data = df.year.range, aes(x = week, ymin = min, ymax = max, fill =  "fill1")) +
  geom_line(data = df.year.range, aes(x = week, y = mean,colour="3"),size=1.7) +
  geom_line(data = subset(df.years,year==max_year-1), aes(x = week, y = value, color = "2"),size=1.7)+ 
  geom_line(data = subset(df.years,year==max_year), aes(x = week, y = value, color = "1"),size=1.7) +
  geom_point(data = subset(df.years,year==max_year), aes(x = week, y = value, color = "1"),size=1.7) +
  #scale_fill_manual(values = '#ffccff')
  scale_fill_manual("",values = 'grey70',labels=paste(max_year-years,"-",max_year-1,"\nrange",sep =""))+     
  scale_color_viridis("",discrete=TRUE,option="C",labels=c(max_year, max_year-1,paste(max_year-years,"-",max_year-1,"\naverage",sep ="")))+
  #scale_fill_continuous()+
  scale_x_continuous(limits=c(min(df1$week),max(df1$week)),expand=c(.001,0))+
  theme_minimal()+
  theme(
    legend.position = "bottom",
    legend.margin=margin(c(0,0,0,0),unit="cm"),
    legend.text = element_text(colour="black", size = 12),
    plot.caption = element_text(size = 14, face = "italic"),
    plot.title = element_text(face = "bold"),
    plot.subtitle = element_text(size = 14, face = "italic"),
    #panel.grid.minor = element_blank(),
    text = element_text(size = 14,face = "bold"),
    axis.line = element_line(color="black", size = 1),
    axis.text.y =element_text(size = 14,face = "bold", colour="black"),
    axis.text.x=element_text(size = 14,face = "bold", colour="black",angle=90, hjust=1),
  )+
  labs(y=y_lab,x="Week",
       title=paste(series_name,sep=""),
       caption="Source: EIA API, graph by Andrew Leach.")
print(graph)
}

Crude Production

Certainly, the most-watched element in the data most weeks will be the US crude production data. The below shows the previous 5 year range of production as well as the previous 5 year average, current year, and previous year’s time series.

Crude Trade

Over the past decade, the US has moved from a major crude oil importer to a major crude oil exporter. Net imports have also decreased. Below, I’ve included both the total imports and exports as well as net imports data, with 10 year ranges and averages, as well as the current year, and previous year’s time series.

Crude Imports

Displays the most recent crude import data and historic ranges.

Crude Exports

Displays the most recent crude export data and historic ranges.

Net Imports

Displays the most recent crude net imports data and historic ranges.

Total Products Trade

Refined products like gasoline, diesel and jet fuel are traded just as crude oil is, and the US trade picture has also been changing a lot on the products front.

Products Imports

Products Exports

Net Imports of Crude Oil and Petroleum Products

Below, I’ve also included some key graphs for gasoline and diesel.

Gasoline and Diesel Trade

Each of the graphs shows the 5 year range of data as well as the 5 year average, current year, and previous year’s time series for the key refined products we’ll talk about in class.

Gasoline Imports

Gasoline Exports

Diesel Imports

Diesel Exports

Jet Imports