#!/usr/bin/env python
# coding: utf-8

# # Crude Onshore Inventories Notebook
# 
# This notebook will give a tutorial on how to query the onshore crude inventories data through pythonSDK.
# 
# 
# Content:
# 1. [Import Libraries](#Import-Libraries)
# 2. [Extract storage location ids](#Extract-storage-location-ids)
# 3. [Crude Inventories Search endpoint](#1.-Crude-Inventories-Search-endpoint)
# 4. [Crude Onshore Inventories Time Series](#2.-Crude-Onshore-Inventories-Time-Series-endpoint)
# 5. [Case study](#Case-study:-US-Cushing-Stockpile-vs-Brent-prices)
# 6. [Benchmarking result](#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


# ## 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']


# 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


# 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())


# ## 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)


# In[7]:


len(price_history)


# 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).