Notebook

Crude Onshore Inventories Notebook¶

This notebook will give a tutorial on how to query the onshore crude inventories data through pythonSDK.

Content:

Import Libraries
Extract storage location ids
Crude Inventories Search endpoint
Crude Onshore Inventories Time Series
Case study
Benchmarking result

Import Libraries¶

In [1]:

import vortexasdk as v
from datetime import datetime
import pandas as pd
import matplotlib.pyplot as plt
import plotly.express as px
import json
from pprint import pprint
import plotly.graph_objects as go

c:\Users\OuiWeinJien\anaconda3\lib\site-packages\pandas\core\arrays\masked.py:60: UserWarning: Pandas requires version '1.3.6' or newer of 'bottleneck' (version '1.3.5' currently installed).
  from pandas.core import (

1. Crude Inventories Search endpoint¶

In [2]:

cushing = 'cde783a902c7837b814dfa5988ed74fb14841c2999d6984952c7cbfc543d5073'
china = [p.id for p in v.Geographies().search('China').to_list() if p.name=='China']

2024-07-30 10:32:15,377 vortexasdk.client — WARNING — You are using vortexasdk version 0.72.5, however version 0.72.6 is available.
You should consider upgrading via the 'pip install vortexasdk --upgrade' command.

In [7]:

data_df = v.OnshoreInventoriesSearch().search(
        crude_confidence=['confirmed', 'probable'],
        location_ids = china,
        time_min=datetime(2023, 3, 1),
        time_max=datetime(2023, 3, 14)
        ).to_df(columns = 'all')

In [11]:

data_df.columns

Out[11]:

Index(['measurement_id', 'tank_id', 'tank_details.id', 'tank_details.lat',
       'tank_details.lon', 'tank_details.name', 'tank_details.leaf',
       'tank_details.ref_type', 'tank_details.storage_terminal_name',
       'tank_details.storage_terminal_id', 'tank_details.storage_type',
       'tank_details.radius', 'tank_details.capacity_bbl',
       'tank_details.capacity_cbm', 'tank_details.capacity_ton',
       'tank_details.crude_confidence', 'tank_details.last_updated',
       'tank_details.location_id', 'tank_details.location_details.0.id',
       'tank_details.location_details.0.label',
       'tank_details.location_details.0.layer',
       'tank_details.location_details.1.id',
       'tank_details.location_details.1.label',
       'tank_details.location_details.1.layer',
       'tank_details.location_details.2.id',
       'tank_details.location_details.2.label',
       'tank_details.location_details.2.layer',
       'tank_details.location_details.3.id',
       'tank_details.location_details.3.label',
       'tank_details.location_details.3.layer',
       'tank_details.location_details.4.id',
       'tank_details.location_details.4.label',
       'tank_details.location_details.4.layer',
       'tank_details.location_details.5.id',
       'tank_details.location_details.5.label',
       'tank_details.location_details.5.layer',
       'tank_details.location_details.6.id',
       'tank_details.location_details.6.label',
       'tank_details.location_details.6.layer',
       'tank_details.location_details.7.id',
       'tank_details.location_details.7.label',
       'tank_details.location_details.7.layer',
       'tank_details.location_details.8.id',
       'tank_details.location_details.8.label',
       'tank_details.location_details.8.layer',
       'tank_details.location_details.9.id',
       'tank_details.location_details.9.label',
       'tank_details.location_details.9.layer',
       'tank_details.location_details.10.id',
       'tank_details.location_details.10.label',
       'tank_details.location_details.10.layer',
       'tank_details.location_details.11.id',
       'tank_details.location_details.11.label',
       'tank_details.location_details.11.layer',
       'tank_details.location_details.12.id',
       'tank_details.location_details.12.label',
       'tank_details.location_details.12.layer',
       'tank_details.location_details.13.id',
       'tank_details.location_details.13.label',
       'tank_details.location_details.13.layer',
       'tank_details.location_details.14.id',
       'tank_details.location_details.14.label',
       'tank_details.location_details.14.layer',
       'tank_details.location_details.15.id',
       'tank_details.location_details.15.label',
       'tank_details.location_details.15.layer',
       'tank_details.location_details.16.id',
       'tank_details.location_details.16.label',
       'tank_details.location_details.16.layer',
       'tank_details.location_details.17.id',
       'tank_details.location_details.17.label',
       'tank_details.location_details.17.layer',
       'tank_details.location_details.18.id',
       'tank_details.location_details.18.label',
       'tank_details.location_details.18.layer',
       'tank_details.location_details.19.id',
       'tank_details.location_details.19.label',
       'tank_details.location_details.19.layer',
       'tank_details.corporate_entity_id',
       'tank_details.corporate_entity_details.id',
       'tank_details.corporate_entity_details.label',
       'tank_details.corporate_entity_details.layer', 'measurement_timestamp',
       'publish_timestamp', 'fill_bbl', 'fill_tons', 'fill_cbm',
       'reference_data_version', 'tank_details.location_details.20.id',
       'tank_details.location_details.20.label',
       'tank_details.location_details.20.layer'],
      dtype='object')

In [5]:

data_list = v.OnshoreInventoriesSearch().search(
        crude_confidence=['confirmed', 'probable'],
        location_ids = china,
        time_min=datetime(2023, 3, 1),
        time_max=datetime(2023, 3, 14)).to_list()

In [6]:

pprint(data_list[0].dict())

{'fill_bbl': 53429,
 'fill_cbm': 8494.569851999999,
 'fill_tons': 7305.2937409999995,
 'measurement_id': '000e025d8be0f76d3fe6a23bbd6867436ff751ad8fe9d85f30846afcfc077d19',
 'measurement_timestamp': '2023-03-02T10:13:39',
 'publish_timestamp': '2023-03-03T18:00:01',
 'reference_data_version': 'current-1718644569619',
 'tank_details': {'capacity_bbl': 318884,
                  'capacity_cbm': 50698,
                  'capacity_ton': 43600,
                  'corporate_entity_details': {'id': 'f78558ef7c9b9f162545a56b330111c274d578a2933f46ec82ee004ca4f449bd',
                                               'label': 'PETROCHINA',
                                               'layer': 'commercial_owner'},
                  'corporate_entity_id': 'f78558ef7c9b9f162545a56b330111c274d578a2933f46ec82ee004ca4f449bd',
                  'crude_confidence': 'confirmed',
                  'id': '1432c91cc383424b9fa048a7cc3fbe87747aa5e575a3e2c65479da8a7fd630d0',
                  'last_updated': '2022-05-16T16:16:01.000Z',
                  'lat': 38.7419159,
                  'leaf': True,
                  'location_details': [{'id': 'b310103de98ae56631562a9fe1d13d826babe3fafd5e639f81370d944afb7f8f',
                                        'label': 'Tianjin',
                                        'layer': 'state_or_province'},
                                       {'id': 'a63890260e29d859390fd1a23c690181afd4bd152943a04c00cd6a5ecf3f7d1e',
                                        'label': 'North China',
                                        'layer': 'alternative_region'},
                                       {'id': 'b5fafce6e20de2dc307fb7e0b89978ee91a49a7b6ec6f5461daf2633f3c56674',
                                        'label': 'China (excl. HK & Macau)',
                                        'layer': 'alternative_region'},
                                       {'id': 'c7d632f2816340f9daf323ca62d1396f1bc9c7f7bce5cd358e84715c32cc763f',
                                        'label': 'Alternative Regions',
                                        'layer': 'root'},
                                       {'id': 'f2d80ac4c87a2006e3ae3af0b345b7129fc8efefc780ccbfeaae8313c28caf76',
                                        'label': 'State or Province',
                                        'layer': 'root'},
                                       {'id': '934c47f36c16a58d68ef5e007e62a23f5f036ee3f3d1f5f85a48c572b90ad8b2',
                                        'label': 'China',
                                        'layer': 'country'},
                                       {'id': '07ac020aa082d5d116f829ef28a40e9876c0644aeb4f0480202c1a86bb8adf7f',
                                        'label': 'Far East',
                                        'layer': 'alternative_region'},
                                       {'id': 'e6955a2c59dc90833986fe0894cf6718dddaa7816bb51bc955cdd3eb4470e554',
                                        'label': 'Asia',
                                        'layer': 'region'},
                                       {'id': '693f4f6d59b709e8114e339f2d6c0c3083a453278226e58ea7473c09b2de6d40',
                                        'label': 'Geographic Regions',
                                        'layer': 'root'},
                                       {'id': '5600764fc92f8cce630f3994e7af4d900945d54b29f6e4a07fde0c5e17f7d738',
                                        'label': 'Northeast Asia (NEA)',
                                        'layer': 'shipping_region_v2'},
                                       {'id': '9247125b0b7d8c78d68fb91a5e1ec0cd1b1c4f7276e3385dbe197004b6081229',
                                        'label': 'East of Suez',
                                        'layer': 'alternative_region'},
                                       {'id': 'f5cfd8a913cf02382c275541bec8506be42365e21ea7f82a2655362a210bc73e',
                                        'label': 'Shipping Regions',
                                        'layer': 'root'},
                                       {'id': '86bf8a497ce81e6d4768e3297a22a029b3ebf95de037586789232bea76ab4116',
                                        'label': 'Wider Northeast Asia (WNEA)',
                                        'layer': 'wider_shipping_region'},
                                       {'id': 'a3d6e07472c8d1db2562af0af9d2c49e4d2df6d2cd65a99f55074828df731ba2',
                                        'label': 'Wider Shipping Region',
                                        'layer': 'root'},
                                       {'id': '715b6aab56723cb55c3ddbdc12a2851b379919010f119cf98765d4debbdb793d',
                                        'label': 'Pacific Basin',
                                        'layer': 'basin'},
                                       {'id': 'a37c3441872e24e9d0b95c61d98d7a32307abe48e968cf7d3ed352094008e872',
                                        'label': 'Basins',
                                        'layer': 'root'},
                                       {'id': '956b03529510459d298c4e537d23c6fbb22ffd8414af933b3475310c661f337a',
                                        'label': 'Non-OPEC',
                                        'layer': 'alternative_region'},
                                       {'id': 'e72bcaa8ab1867a6db9e844007653b494d8f325adfe430b8e29e14ed4dc27029',
                                        'label': 'Tianjin [CN]',
                                        'layer': 'port'},
                                       {'id': 'e72bcaa8ab1867a6db9e844007653b494d8f325adfe430b8e29e14ed4dc27029',
                                        'label': 'Tianjin [CN]',
                                        'layer': 'storage'},
                                       {'id': '42fbc2520c8c4ac8a6700e097c2ad844d039d1b247e41d46475d772748c6472c',
                                        'label': 'PetroChina Huabei '
                                                 'Petrochemical Company',
                                        'layer': 'storage_terminal'}],
                  'location_id': 'e72bcaa8ab1867a6db9e844007653b494d8f325adfe430b8e29e14ed4dc27029',
                  'lon': 116.1261422,
                  'name': 'RNQ017',
                  'radius': 28,
                  'ref_type': 'asset_tank',
                  'storage_terminal_id': '42fbc2520c8c4ac8a6700e097c2ad844d039d1b247e41d46475d772748c6472c',
                  'storage_terminal_name': 'PetroChina Huabei Petrochemical '
                                           'Company',
                  'storage_type': 'refinery'},
 'tank_id': '1432c91cc383424b9fa048a7cc3fbe87747aa5e575a3e2c65479da8a7fd630d0'}

2. Crude Onshore Inventories Time Series endpoint¶

In [7]:

shandong = [p.id for p in v.StorageTerminals().search(term ='Shandong').to_list() if p.name=='Shandong Province']

In [8]:

df = v.OnshoreInventoriesTimeseries().search(
        crude_confidence=['confirmed', 'probable'],
        location_ids = shandong,
        time_min=datetime(2022, 1, 1),
        time_max=datetime.today(),
        timeseries_frequency = 'day',
        timeseries_unit = 'b'
).to_df(columns = 'all').set_index('key')

df2 = v.OnshoreInventoriesTimeseries().search(
        crude_confidence=['confirmed', 'probable'],
        location_ids = china,
        time_min=datetime(2022, 1, 1),
        time_max=datetime.today(),
        timeseries_frequency = 'day',
        timeseries_unit = 'b'
).to_df(columns = 'all').set_index('key')

In [9]:

# Create traces
from plotly.subplots import make_subplots
fig = make_subplots(specs=[[{"secondary_y": True}]])
fig.add_trace(go.Scatter(
    x=df.index, y=df['value'],
    line_color='rgb(0,100,80)',
    name='Shandong'),
    secondary_y=False
)

fig.add_trace(go.Scatter(
    x=df2.index, y=df2['value'],
    line_color='rgb(255,100,80)',
    name='china'),
    secondary_y=True
)

fig.update_layout(
    title_text="Shandong vs China Crude Inventories (bbls)"
)

# Set y-axes titles
fig.update_yaxes(title_text="Shandong volume", secondary_y=False)
fig.update_yaxes(title_text="China volume", secondary_y=True)

fig.show()

*Shandong Inventories decline but remain a crucial outlier*

Shandong crude stocks have been falling consistently sisnce mid last year, but are still well above year-earlier levels.

This contrasts with total China inventories now falling close to historical norms and global inventories currently at lows.

Shandong's independent refiners can continue to draw at current rates for the next 6 months and still remain above historical avearage.

Case study: US Cushing Stockpile vs Brent prices¶

In [6]:

import yfinance as yf
price_history = yf.Ticker('BZ=Fdsdsds').history(period='2y', # valid periods: 1d,5d,1mo,3mo,6mo,1y,2y,5y,10y,ytd,max
                                   interval='2d', # valid intervals: 1m,2m,5m,15m,30m,60m,90m,1h,1d,5d,1wk,1mo,3mo
                                   actions=False)

BZ=FDSDSDS: Invalid input - interval=2d is not supported. Valid intervals: [1m, 2m, 5m, 15m, 30m, 60m, 90m, 1h, 1d, 5d, 1wk, 1mo, 3mo]

In [7]:

len(price_history)

Out[7]:

In [12]:

df3 = v.OnshoreInventoriesTimeseries().search(
        crude_confidence=['confirmed', 'probable'],
        location_ids = cushing,
        time_min=datetime(2022, 1, 1),
        time_max=datetime.today(),
        timeseries_frequency = 'day',
        timeseries_unit = 'b'
).to_df(columns = 'all').set_index('key')

In [13]:

# Create traces
from plotly.subplots import make_subplots
fig = make_subplots(specs=[[{"secondary_y": True}]])
fig.add_trace(go.Scatter(
    x=df3.index, y=df3['value'],
    line_color='rgb(0,100,80)',
    name='US Crude stockpiles'),
    secondary_y=False
)

fig.add_trace(go.Scatter(
    x=price_history.index, y=price_history['Open'],
    line_color='rgb(255,100,80)',
    name='Brent Price'),
    secondary_y=True
)

fig.update_layout(
    title_text="US Cushing stockpiles vs Brent Price"
)

# Set y-axes titles
fig.update_yaxes(title_text="Cushing volume (b)", secondary_y=False)
fig.update_yaxes(title_text="Brent Price ($)", secondary_y=True)

fig.show()

*Vortexa data as a leading indicator of inventories data as it publishes more than a weekly basis, which can be useful for price analysis.*

Inverse correlation is found for cushing volume vs Brent prices. As Vortexa publishes measurement readings for each tank whenever we receive it, the frequency in data is more competitive than publicly available data such as Energy Information Administration (EIA).