{ "cells": [ { "cell_type": "markdown", "id": "3aed9a45", "metadata": {}, "source": [ "# FOB Hub Netbacks vs Cargo NEA/NWE Netbacks\n", "\n", "Comparing FOB Hub Netbacks vs Cargo Netbacks to assess the profitibality of utilising regas capacity vs selling DES\n", "\n", "For a full explanation of how to import our FOB Hub Netbacks or Cargo Netbacks data, please refer to our Python Jupyter Notebook Code Samples:\n", "\n", "https://www.sparkcommodities.com/api/code-examples/jupyter.html" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Have any questions?\n", "\n", "If you have any questions regarding our API, or need help accessing specific datasets, please contact us at:\n", "\n", "__data@sparkcommodities.com__" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Importing Data\n", "\n", "Here we define the functions that allow us to retrieve the valid credentials to access the Spark API.\n", "\n", "This section can remain unchanged for most Spark API users." ] }, { "cell_type": "code", "execution_count": null, "id": "91bf114a", "metadata": {}, "outputs": [], "source": [ "import json\n", "import os\n", "import sys\n", "import pandas as pd\n", "import numpy as np\n", "from base64 import b64encode\n", "from urllib.parse import urljoin\n", "from pprint import pprint\n", "from io import StringIO\n", "import datetime\n", "\n", "try:\n", " from urllib import request, parse\n", " from urllib.error import HTTPError\n", "except ImportError:\n", " raise RuntimeError(\"Python 3 required\")" ] }, { "cell_type": "code", "execution_count": null, "id": "9eb7cb84", "metadata": {}, "outputs": [], "source": [ "API_BASE_URL = \"https://api.sparkcommodities.com\"\n", "\n", "\n", "def retrieve_credentials(file_path=None):\n", " \"\"\"\n", " Find credentials either by reading the client_credentials file or reading\n", " environment variables\n", " \"\"\"\n", " if file_path is None:\n", " client_id = os.getenv(\"SPARK_CLIENT_ID\")\n", " client_secret = os.getenv(\"SPARK_CLIENT_SECRET\")\n", " if not client_id or not client_secret:\n", " raise RuntimeError(\n", " \"SPARK_CLIENT_ID and SPARK_CLIENT_SECRET environment vars required\"\n", " )\n", " else:\n", " # Parse the file\n", " if not os.path.isfile(file_path):\n", " raise RuntimeError(\"The file {} doesn't exist\".format(file_path))\n", "\n", " with open(file_path) as fp:\n", " lines = [l.replace(\"\\n\", \"\") for l in fp.readlines()]\n", "\n", " if lines[0] in (\"clientId,clientSecret\", \"client_id,client_secret\"):\n", " client_id, client_secret = lines[1].split(\",\")\n", " else:\n", " print(\"First line read: '{}'\".format(lines[0]))\n", " raise RuntimeError(\n", " \"The specified file {} doesn't look like to be a Spark API client \"\n", " \"credentials file\".format(file_path)\n", " )\n", "\n", " print(\">>>> Found credentials!\")\n", " \n", " return client_id, client_secret\n", "\n", "\n", "def do_api_post_query(uri, body, headers):\n", " \"\"\"\n", " OAuth2 authentication requires a POST request with client credentials before accessing the API.\n", " This POST request will return an Access Token which will be used for the API GET request.\n", " \"\"\"\n", " url = urljoin(API_BASE_URL, uri)\n", "\n", " data = json.dumps(body).encode(\"utf-8\")\n", "\n", " # HTTP POST request\n", " req = request.Request(url, data=data, headers=headers)\n", " try:\n", " response = request.urlopen(req)\n", " except HTTPError as e:\n", " print(\"HTTP Error: \", e.code)\n", " print(e.read())\n", " sys.exit(1)\n", "\n", " resp_content = response.read()\n", "\n", " # The server must return HTTP 201. Raise an error if this is not the case\n", " assert response.status == 201, resp_content\n", "\n", " # The server returned a JSON response\n", " content = json.loads(resp_content)\n", "\n", " return content\n", "\n", "\n", "def do_api_get_query(uri, access_token, format='json'):\n", " \"\"\"\n", " After receiving an Access Token, we can request information from the API.\n", " Supports both JSON (default) and CSV responses.\n", " \"\"\"\n", " url = urljoin(API_BASE_URL, uri)\n", "\n", " if format == 'json':\n", " headers = {\n", " \"Authorization\": \"Bearer {}\".format(access_token),\n", " \"Accept\": \"application/json\",\n", " }\n", " elif format == 'csv':\n", " headers = {\n", " \"Authorization\": \"Bearer {}\".format(access_token),\n", " \"Accept\": \"text/csv\",\n", " }\n", " else:\n", " raise ValueError(\"The format parameter only takes 'csv' or 'json' as inputs\")\n", "\n", " print(\"Fetching {}\".format(url))\n", "\n", " # HTTP GET request\n", " req = request.Request(url, headers=headers)\n", " try:\n", " response = request.urlopen(req)\n", " except HTTPError as e:\n", " print(\"HTTP Error: \", e.code)\n", " print(e.read())\n", " sys.exit(1)\n", "\n", " resp_content = response.read()\n", "\n", " # The server must return HTTP 200. Raise an error if this is not the case\n", " assert response.status == 200, resp_content\n", "\n", " # Storing response based on requested format\n", " if format == 'json':\n", " content = json.loads(resp_content)\n", " elif format == 'csv':\n", " content = resp_content\n", "\n", " return content\n", "\n", "\n", "def get_access_token(client_id, client_secret):\n", "\n", " payload = \"{}:{}\".format(client_id, client_secret).encode()\n", " headers = {\n", " \"Authorization\": b64encode(payload).decode(),\n", " \"Accept\": \"application/json\",\n", " \"Content-Type\": \"application/json\",\n", " }\n", " body = {\n", " \"grantType\": \"clientCredentials\",\n", " }\n", "\n", " content = do_api_post_query(uri=\"/oauth/token/\", body=body, headers=headers)\n", "\n", " print(\n", " \">>>> Successfully fetched an access token {}****, valid {} seconds.\".format(\n", " content[\"accessToken\"][:5], content[\"expiresIn\"]\n", " )\n", " )\n", "\n", " return content[\"accessToken\"]" ] }, { "cell_type": "markdown", "id": "25180e07", "metadata": {}, "source": [ "## N.B. Credentials\n", "\n", "Here we call the above functions, and input the file path to our credentials.\n", "\n", "N.B. You must have downloaded your client credentials CSV file before proceeding. Please refer to the API documentation if you have not downloaded them already. Instructions for downloading your credentials can be found here:\n", "\n", "https://app.sparkcommodities.com/data-integrations/api" ] }, { "cell_type": "code", "execution_count": null, "id": "f1df3ec6", "metadata": {}, "outputs": [], "source": [ "# Insert file path to your client credentials here\n", "client_id, client_secret = retrieve_credentials(file_path=\"/tmp/client_credentials.csv\")\n", "\n", "# Authenticate:\n", "access_token = get_access_token(client_id, client_secret)" ] }, { "cell_type": "markdown", "id": "3b61a1ae", "metadata": {}, "source": [ "# 2. Fetching FOB Hub Netbacks\n", "\n", "Here we define the functions used to call the FOB Hub Netbacks API data. For more information on the FOB Hub Netbacks API, please visit the API website or download our dedicated code sample:\n", "\n", "https://www.sparkcommodities.com/api/lng-access/fob-hub-netbacks.html\n" ] }, { "cell_type": "code", "execution_count": null, "id": "a81712ef", "metadata": {}, "outputs": [], "source": [ "## Defining the reference-data calling function\n", "\n", "def fetch_fobhub_reference_data(access_token, format='json'):\n", "\n", " uri = \"/v1.0/lng/access/fob-hub-netbacks/reference-data/\"\n", " print(uri)\n", "\n", " content = do_api_get_query(uri=uri, access_token=access_token, format=format)\n", "\n", " if format == 'json':\n", " data = content\n", " elif format == 'csv':\n", " if len(content) == 0:\n", " data = content\n", " print('No Data to Show')\n", " else:\n", " data = content.decode('utf-8')\n", " data = pd.read_csv(StringIO(data))\n", "\n", " return data" ] }, { "cell_type": "code", "execution_count": null, "id": "6e454199", "metadata": {}, "outputs": [], "source": [ "# fetching in CSV format (easier to browse the terminal list)\n", "ref_data = fetch_fobhub_reference_data(access_token, format='csv')\n", "ref_data" ] }, { "cell_type": "code", "execution_count": null, "id": "de266a8e", "metadata": {}, "outputs": [], "source": [ "## Defining the data calling function\n", "\n", "def fetch_fobhub_releases(access_token, unit, fob_port_uuid, via_point, start=None, end=None, limit=None, offset=None, terminal_uuid=None, format='json'):\n", "\n", " query_params = \"?unit={}\".format(unit)\n", "\n", " query_params += \"&fob-port-uuid={}\".format(fob_port_uuid)\n", "\n", " if via_point is not None:\n", " query_params += \"&via-point={}\".format(via_point)\n", "\n", " if start is not None:\n", " query_params += \"&start={}\".format(start)\n", " if end is not None:\n", " query_params += \"&end={}\".format(end)\n", " \n", " if terminal_uuid is not None:\n", " query_params += \"®as-terminal-uuid={}\".format(terminal_uuid)\n", " \n", "\n", " uri = \"/v1.0/lng/access/fob-hub-netbacks/{}\".format(query_params)\n", "\n", " content = do_api_get_query(\n", " uri=uri, access_token=access_token, format=format\n", " )\n", "\n", " if format == 'json':\n", " data = content\n", " elif format == 'csv':\n", " # if there's no data to show, returns raw response (empty string) and \"No Data to Show\" message\n", " if len(content) == 0:\n", " data = content\n", " print('No Data to Show')\n", " else:\n", " data = content.decode('utf-8')\n", " data = pd.read_csv(StringIO(data)) # automatically converting into a Pandas DataFrame when choosing CSV format\n", "\n", " return data\n" ] }, { "cell_type": "markdown", "id": "24298c09", "metadata": {}, "source": [ "## 3. Fetching Cargo Netbacks\n", "\n", "Here we define the functions used to call the Cargo Netbacks API data. For more information on the Cargo Netbacks API, please visit the API website or download our dedicated code sample:\n", "\n", "https://www.sparkcommodities.com/api/lng-cargo/netbacks.html" ] }, { "cell_type": "code", "execution_count": null, "id": "71444ce2", "metadata": {}, "outputs": [], "source": [ "# defining netbacks function\n", "\n", "def fetch_netback(access_token, ticker, release=None, via=None, laden=None, ballast=None, start=None, end=None, percent_hire=None, format='json'):\n", " \n", " query_params = \"?fob-port={}\".format(ticker)\n", " if release is not None:\n", " query_params += \"&release-date={}\".format(release)\n", " if start is not None:\n", " query_params += \"&start={}\".format(start)\n", " if end is not None:\n", " query_params += \"&end={}\".format(end)\n", " if via is not None:\n", " query_params += \"&via-point={}\".format(via)\n", " if laden is not None:\n", " query_params += \"&laden-congestion-days={}\".format(laden)\n", " if ballast is not None:\n", " query_params += \"&ballast-congestion-days={}\".format(ballast)\n", " if percent_hire is not None:\n", " query_params += \"&percent-hire={}\".format(percent_hire)\n", " \n", " \n", " content = do_api_get_query(\n", " uri=\"/v1.0/netbacks/{}\".format(query_params),\n", " access_token=access_token, format=format,\n", " )\n", " \n", " if format == 'json':\n", " data = content\n", " elif format == 'csv':\n", " # if there's no data to show, returns raw response (empty string) and \"No Data to Show\" message\n", " if len(content) == 0:\n", " data = content\n", " print('No Data to Show')\n", " else:\n", " data = content.decode('utf-8')\n", " data = pd.read_csv(StringIO(data)) # automatically converting into a Pandas DataFrame when choosing CSV format\n", "\n", " return data\n" ] }, { "cell_type": "markdown", "id": "cfebef96", "metadata": {}, "source": [ "## 4. Combined extended historical data call function\n", "\n", "A single call to the historical endpoint for both datasets is limited to 365 days of data. If you need a longer range, use the loop function below, which calls the chosen endpoint one year at a time and concatenates the results into a single Pandas DataFrame." ] }, { "cell_type": "code", "execution_count": null, "id": "104e3455", "metadata": {}, "outputs": [], "source": [ "\n", "def loop_historical_data(access_token, endpoint, fob_port_uuid=None, via_point=None, unit=None, start=None, end=None, terminal_uuid=None):\n", " \n", " hist_diff = (datetime.datetime.strptime(end, '%Y-%m-%d') - datetime.datetime.strptime(start, '%Y-%m-%d')).days\n", " t = 0\n", "\n", " starts = []\n", " ends = []\n", "\n", " w = 365\n", "\n", " while t < hist_diff:\n", " # initialising dataframe\n", " if t == 0 and hist_diff>w:\n", " diff_end = datetime.datetime.strftime(datetime.datetime.strptime(start, '%Y-%m-%d') + pd.Timedelta(days=w), '%Y-%m-%d')\n", " if endpoint == 'fob-hub-netbacks':\n", " hist_df = fetch_fobhub_releases(access_token, fob_port_uuid=fob_port_uuid, via_point=via_point,unit=unit, start=start, end=diff_end, format='csv')\n", " elif endpoint == 'cargo-netbacks':\n", " hist_df = fetch_netback(access_token, ticker=fob_port_uuid, via=via_point, start=start, end=diff_end, format='csv')\n", "\n", " starts.append(start)\n", " ends.append(diff_end)\n", " \n", " elif t==0 and hist_diff<=w:\n", " if endpoint == 'fob-hub-netbacks':\n", " hist_df = fetch_fobhub_releases(access_token, fob_port_uuid=fob_port_uuid, via_point=via_point,unit=unit, start=start, end=end, format='csv')\n", " elif endpoint == 'cargo-netbacks':\n", " hist_df = fetch_netback(access_token, ticker=fob_port_uuid, via=via_point, start=start, end=end, format='csv')\n", "\n", " # appending additional historical data\n", " else:\n", " if t < hist_diff-w:\n", " diff_start = datetime.datetime.strftime(datetime.datetime.strptime(start, '%Y-%m-%d') + pd.Timedelta(days=t+1), '%Y-%m-%d')\n", " diff_end = datetime.datetime.strftime(datetime.datetime.strptime(diff_start, '%Y-%m-%d') + pd.Timedelta(days=w), '%Y-%m-%d')\n", " if endpoint == 'fob-hub-netbacks':\n", " historical_addition = fetch_fobhub_releases(access_token, fob_port_uuid=fob_port_uuid, via_point=via_point,unit=unit, start=diff_start, end=diff_end, format='csv')\n", " elif endpoint == 'cargo-netbacks':\n", " historical_addition = fetch_netback(access_token, ticker=fob_port_uuid, via=via_point, start=diff_start, end=diff_end, format='csv')\n", " \n", " try:\n", " hist_df = pd.concat([hist_df,historical_addition])\n", " #exception if hist_df is empty\n", " except:\n", " hist_df = historical_addition.copy()\n", " starts.append(start)\n", " ends.append(diff_end)\n", " else:\n", " diff_start = datetime.datetime.strftime(datetime.datetime.strptime(start, '%Y-%m-%d') + pd.Timedelta(days=t+1), '%Y-%m-%d')\n", " diff_end = datetime.datetime.strftime(datetime.datetime.strptime(diff_start, '%Y-%m-%d') + pd.Timedelta(days=hist_diff-t), '%Y-%m-%d')\n", "\n", " if endpoint == 'fob-hub-netbacks':\n", " historical_addition = fetch_fobhub_releases(access_token, fob_port_uuid=fob_port_uuid, via_point=via_point,unit=unit, start=diff_start, end=diff_end, format='csv')\n", " elif endpoint == 'cargo-netbacks':\n", " historical_addition = fetch_netback(access_token, ticker=fob_port_uuid, via=via_point, start=diff_start, end=diff_end, format='csv')\n", "\n", " hist_df = pd.concat([hist_df, historical_addition])\n", " starts.append(start)\n", " ends.append(diff_end)\n", " \n", " #looping by year\n", " t += w\n", "\n", " if endpoint=='fob-hub-netbacks':\n", " for c in list(hist_df.columns)[13:]:\n", " hist_df[c] = pd.to_numeric(hist_df[c])\n", " hist_df['LoadDate'] = pd.to_datetime(hist_df['LoadDate'])\n", " elif endpoint == 'cargo-netbacks':\n", " for c in list(hist_df.columns)[4:]:\n", " hist_df[c] = pd.to_numeric(hist_df[c])\n", " hist_df['ReleaseDate'] = pd.to_datetime(hist_df['ReleaseDate'])\n", "\n", " return hist_df" ] }, { "cell_type": "markdown", "id": "345da19a", "metadata": {}, "source": [ "# 4.1 Time-range Input" ] }, { "cell_type": "code", "execution_count": null, "id": "a97e1891", "metadata": {}, "outputs": [], "source": [ "# define today as the end date\n", "today = datetime.datetime.now().strftime('%Y-%m-%d')\n", "hist_end=today\n", "\n", "# Define start date by time interval or manually\n", "#hist_start = (datetime.datetime.now() - datetime.timedelta(days=365)).strftime('%Y-%m-%d')\n", "hist_start = '2026-04-01'" ] }, { "cell_type": "markdown", "id": "8fa60d1b", "metadata": {}, "source": [ "# 4.2 Using the function to call FOB Hub Netbacks" ] }, { "cell_type": "code", "execution_count": null, "id": "a713a40e", "metadata": {}, "outputs": [], "source": [ "# Fetching a FoB Port UUID\n", "sabine_uuid = ref_data[ref_data['FOBPortName'] == 'Sabine Pass']['FOBPortUUID'].iloc[0]\n", "\n", "# Fetching data using UUID\n", "fobhub_df = loop_historical_data(access_token, endpoint='fob-hub-netbacks', unit='usd-per-mmbtu', fob_port_uuid=sabine_uuid, via_point=None, \n", " start=hist_start, end=hist_end)\n", "\n", "print('Fetched {} rows from {} to {}'.format(len(fobhub_df), hist_start, hist_end))\n", "fobhub_df.head(5)" ] }, { "cell_type": "markdown", "id": "084d744e", "metadata": {}, "source": [ "## 4.3 Sinking Regas Costs Components for FOB Hub Netbacks\n", "\n", "Output columns added to `hist_df`:\n", "- `NetbackTTFBasisSunk`\n", "- `NetbackOutrightSunk`\n" ] }, { "cell_type": "code", "execution_count": null, "id": "9d997060", "metadata": {}, "outputs": [], "source": [ "# Global sunk slot components - added back for every terminal\n", "sunk_slot_cols = ['SlotLtCapacityEstimate', 'SlotUnloadStorageRegas', 'SlotBerth', 'SlotBerthUnloadStorageRegas']\n", "\n", "# Per-terminal extra sunk costs - layered on top of the global sunk costs defined above\n", "# Value is a list of cost columns to treat as sunk for that terminal only\n", "# Edit this dict to suit your analysis. Unlisted terminals use the global set.\n", "\n", "sunk_terminal_costs = {\n", " # 'Isle of Grain': ['EntryCapacity', 'EntryVariable'],\n", " # 'South Hook': ['EntryCapacity'],\n", "}\n", "\n", "# Sink global sunk costs across all terminals\n", "sunk_add_back = fobhub_df[sunk_slot_cols].fillna(0).sum(axis=1)\n", "\n", "# Sink terminal-specific sunk costs\n", "for terminal, cols in sunk_terminal_costs.items():\n", " if not cols:\n", " continue\n", " missing = [c for c in cols if c not in fobhub_df.columns]\n", " if missing:\n", " raise KeyError('sunk_terminal_costs[{!r}] references missing columns: {}'.format(terminal, missing))\n", " mask = fobhub_df['RegasTerminalName'] == terminal\n", " if mask.any():\n", " sunk_add_back.loc[mask] += fobhub_df.loc[mask, cols].fillna(0).sum(axis=1)\n", "\n", "fobhub_df['NetbackTTFBasisSunk'] = fobhub_df['NetbackTTFBasis'] + sunk_add_back\n", "fobhub_df['NetbackOutrightSunk'] = fobhub_df['NetbackOutright'] + sunk_add_back\n", "\n", "fobhub_df[['ReleaseDate', 'RegasTerminalName', 'LoadMonthIndex', 'NetbackTTFBasis',\n", " 'NetbackTTFBasisSunk']].head(10)\n" ] }, { "cell_type": "markdown", "id": "36eff6fe", "metadata": {}, "source": [ "### Pivot into a wide DataFrame - one column per terminal\n", "\n", "Here we pivot the dataframe so that each terminal is one column. We create two dataframes - one with all Regas costs included, and one with defined Sunk costs excluded." ] }, { "cell_type": "code", "execution_count": null, "id": "b8dfd892", "metadata": {}, "outputs": [], "source": [ "# one row per (ReleaseDate, DeliveryMonth), one column per terminal\n", "terminal_list = list(fobhub_df['RegasTerminalName'].unique())\n", "\n", "id_cols = ['ReleaseDate', 'LoadDate', 'LoadMonthName', 'LoadMonthIndex']\n", "\n", "# All regas costs included\n", "fobhub_full = (\n", " fobhub_df\n", " .pivot_table(index=id_cols, columns='RegasTerminalName', values='NetbackTTFBasis', aggfunc='first')\n", " .reset_index()\n", ")\n", "\n", "# Defined sunk costs excluded\n", "fobhub_sunk = (\n", " fobhub_df\n", " .pivot_table(index=id_cols, columns='RegasTerminalName', values='NetbackTTFBasisSunk', aggfunc='first')\n", " .reset_index()\n", ")\n", "\n", "# Reset column names\n", "fobhub_full.columns.name = None\n", "fobhub_sunk.columns.name = None\n", "\n", "# restore terminal-appearance ordering (pivot_table sorts alphabetically by default)\n", "fobhub_full = fobhub_full[id_cols + terminal_list]\n", "fobhub_sunk = fobhub_sunk[id_cols + terminal_list]\n", "\n", "fobhub_full.head(5)" ] }, { "cell_type": "markdown", "id": "fc87b14e", "metadata": {}, "source": [ "## 4.4 Calling Cargo Netbacks" ] }, { "cell_type": "code", "execution_count": null, "id": "df3c35ae", "metadata": {}, "outputs": [], "source": [ "# Calling US netbacks via COGH and via Panama\n", "sabcogh_df = loop_historical_data(access_token, endpoint='cargo-netbacks', fob_port_uuid=sabine_uuid, via_point='cogh', \n", " start=hist_start, end=hist_end)\n", "\n", "sabpanama_df = loop_historical_data(access_token, endpoint='cargo-netbacks', fob_port_uuid=sabine_uuid, via_point='panama', \n", " start=hist_start, end=hist_end)\n", "\n", "# Combining into one dataframe\n", "cargo_df = pd.merge(\n", " sabcogh_df[[\"ReleaseDate\", \"LoadMonthIndex\", 'NeaNetbackOutright', 'NeaNetbackTtfBasis','DeltaNeaNwe']],\n", " sabpanama_df[[\"ReleaseDate\", \"LoadMonthIndex\", 'NeaNetbackOutright', 'NeaNetbackTtfBasis', 'DeltaNeaNwe']],\n", " how=\"outer\",\n", " left_on=[\"ReleaseDate\", \"LoadMonthIndex\"],\n", " right_on=[\"ReleaseDate\", \"LoadMonthIndex\"],\n", " suffixes=(\"_SabCOGH\", \"_SabPanama\"),\n", ")\n", "\n", "cargo_df['LoadMonthIndexStr'] = 'M+' + cargo_df['LoadMonthIndex'].astype(str)\n", "cargo_df.head(3)" ] }, { "cell_type": "markdown", "id": "7f4b98ad", "metadata": {}, "source": [ "## 4.5 Creating combined Dataframe" ] }, { "cell_type": "code", "execution_count": null, "id": "fa33f717", "metadata": {}, "outputs": [], "source": [ "# All regas costs included\n", "combined_terminals_full = pd.merge(\n", " fobhub_full,\n", " cargo_df[[\"ReleaseDate\", \"LoadMonthIndexStr\", 'NeaNetbackTtfBasis_SabCOGH', 'NeaNetbackTtfBasis_SabPanama']],\n", " how=\"left\",\n", " left_on=[\"ReleaseDate\", \"LoadMonthIndex\"],\n", " right_on=[\"ReleaseDate\", \"LoadMonthIndexStr\"],\n", ")\n", "\n", "combined_terminals_full['LoadDate'] = pd.to_datetime(combined_terminals_full['LoadDate'])\n", "\n", "# Defined sunk costs excluded\n", "combined_terminals_sunk = pd.merge(\n", " fobhub_sunk,\n", " cargo_df[[\"ReleaseDate\", \"LoadMonthIndexStr\", 'NeaNetbackTtfBasis_SabCOGH', 'NeaNetbackTtfBasis_SabPanama']],\n", " how=\"left\",\n", " left_on=[\"ReleaseDate\", \"LoadMonthIndex\"],\n", " right_on=[\"ReleaseDate\", \"LoadMonthIndexStr\"],\n", ")\n", "\n", "combined_terminals_sunk['LoadDate'] = pd.to_datetime(combined_terminals_sunk['LoadDate'])" ] }, { "cell_type": "markdown", "id": "9cd3c12e", "metadata": {}, "source": [ "## 5. Creating equivalent Country-Averaged datasets\n", "\n", "Country averaging FOB Hub Netbacks data as an alternative, \"zoomed-out\" dataset. \n", "\n", "__N.B.__ Section 5 can be deleted if you intend to only use terminal specific data." ] }, { "cell_type": "code", "execution_count": null, "id": "4b3bee7f", "metadata": {}, "outputs": [], "source": [ "# Country mapping dictionary\n", "terminal_country_dict = {\n", " 'Netherlands': ['Gate', 'EemsEnergyTerminal'],\n", " 'UK': ['Isle of Grain', 'South Hook'],\n", " 'Belgium': ['Zeebrugge'],\n", " 'France': ['Dunkerque', 'Le Havre', 'Montoir', 'Fos Cavaou'],\n", " 'Germany': ['Brunsbuttel', 'Deutsche Ostsee', 'Deutsche Ostsee Phase 1',\n", " 'Wilhelmshaven 1', 'Wilhelmshaven 2', 'Stade'],\n", " 'Italy': ['Adriatic', 'OLT Toscana', 'Piombino', 'Ravenna'],\n", " 'Spain': ['Spain TVB'],\n", "}" ] }, { "cell_type": "markdown", "id": "75bd1ec4", "metadata": {}, "source": [ "### Country-level Averages\n", "\n", "`countries_full` and `countries_sunk` average the per-terminal columns in `fobhub_full` / `fobhub_sunk` up to country level using `terminal_country_dict`." ] }, { "cell_type": "code", "execution_count": null, "id": "b33d4fe2", "metadata": {}, "outputs": [], "source": [ "# Columns to initialise DataFrame with\n", "id_cols = ['ReleaseDate', 'LoadDate', 'LoadMonthName', 'LoadMonthIndex']\n", "\n", "# Iterating over terminals to see which have a country assigned to them, and filtering accordingly\n", "country_to_present_terminals = {}\n", "partial_warnings = []\n", "\n", "for country, terms in terminal_country_dict.items():\n", " present = [t for t in terms if t in fobhub_full.columns]\n", " missing = [t for t in terms if t not in fobhub_full.columns]\n", " if not present:\n", " partial_warnings.append(' - {}: SKIPPED (no mapped terminals in fobhub_full). Missing: {}'.format(country, missing))\n", " continue\n", " if missing:\n", " partial_warnings.append(' - {}: averaging {} (missing from data: {})'.format(country, present, missing))\n", " country_to_present_terminals[country] = present\n", "\n", "if partial_warnings:\n", " print('Country mapping notes:')\n", " for w in partial_warnings:\n", " print(w)\n", "\n", "# Aggregating by country for all terminals\n", "def _aggregate_by_country(df, country_map, id_cols):\n", " out = df[id_cols].copy()\n", " for country, terms in country_map.items():\n", " out[country] = df[terms].mean(axis=1, skipna=True)\n", " return out\n", "\n", "countries_full = _aggregate_by_country(fobhub_full, country_to_present_terminals, id_cols)\n", "countries_sunk = _aggregate_by_country(fobhub_sunk, country_to_present_terminals, id_cols)\n", "\n", "# round to 3 d.p. for readability\n", "country_cols = list(country_to_present_terminals.keys())\n", "countries_full[country_cols] = countries_full[country_cols].round(3)\n", "countries_sunk[country_cols] = countries_sunk[country_cols].round(3)\n", "\n", "countries_full.head()" ] }, { "cell_type": "markdown", "id": "cad3b109", "metadata": {}, "source": [ "### Combining the Countries dataframes with the Cargo Netbacks to make one aligned dataframe for ease of comparison & plotting" ] }, { "cell_type": "code", "execution_count": null, "id": "25b21c5b", "metadata": {}, "outputs": [], "source": [ "countries_full = pd.merge(\n", " countries_full,\n", " cargo_df[[\"ReleaseDate\", \"LoadMonthIndexStr\", 'NeaNetbackTtfBasis_SabCOGH', 'NeaNetbackTtfBasis_SabPanama']],\n", " how=\"left\",\n", " left_on=[\"ReleaseDate\", \"LoadMonthIndex\"],\n", " right_on=[\"ReleaseDate\", \"LoadMonthIndexStr\"],\n", ")\n", "\n", "countries_sunk = pd.merge(\n", " countries_sunk,\n", " cargo_df[[\"ReleaseDate\", \"LoadMonthIndexStr\", 'NeaNetbackTtfBasis_SabCOGH', 'NeaNetbackTtfBasis_SabPanama']],\n", " how=\"left\",\n", " left_on=[\"ReleaseDate\", \"LoadMonthIndex\"],\n", " right_on=[\"ReleaseDate\", \"LoadMonthIndexStr\"],\n", ")\n", "\n", "countries_sunk.head(5)" ] }, { "cell_type": "markdown", "id": "e127e2ca", "metadata": {}, "source": [ "# 6. Plotting \n", "\n", "## 6.1. Front Month Moneyness Evolution & Latest Forward Curve for several terminals" ] }, { "cell_type": "code", "execution_count": null, "id": "1ddf59ec", "metadata": {}, "outputs": [], "source": [ "# Creating front month and forward curve dataframes, for both \"full regas\" and \"sunk costs excl.\" datasets\n", "front_full = combined_terminals_full[combined_terminals_full['LoadMonthIndex'] == 'M+1'].copy()\n", "front_sunk = combined_terminals_sunk[combined_terminals_sunk['LoadMonthIndex'] == 'M+1'].copy()\n", "\n", "latest = combined_terminals_full['ReleaseDate'].unique()[-1]\n", "latest_full = combined_terminals_full[combined_terminals_full['ReleaseDate'] == latest].copy()\n", "latest_sunk = combined_terminals_sunk[combined_terminals_sunk['ReleaseDate'] == latest].copy()" ] }, { "cell_type": "code", "execution_count": null, "id": "ef7402a6", "metadata": {}, "outputs": [], "source": [ "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "\n", "sns.set_style('whitegrid')\n", "\n", "fig, ax= plt.subplots(figsize=(14,7))\n", "fig.subplots_adjust(top=0.78, bottom=0.10, left=0.06, right=0.96)\n", "\n", "# User input for which countries to plot, and associated plot colours\n", "my_terminals = ['Deutsche Ostsee', 'Brunsbuttel', 'Gate', 'Isle of Grain', 'South Hook', 'Fos Cavaou', 'Montoir', 'OLT Toscana', 'Spain TVB']\n", "colours = ['orange', 'sienna', 'grey', '#0086ad', '#011f4b', '#00c2c7', 'salmon', 'mediumseagreen', 'firebrick']\n", "\n", "print(\"Latest Assessment: \" + str(front_full['ReleaseDate'].iloc[0]))\n", "\n", "# Iterating over all terminals & plotting\n", "cz = 0\n", "for t in my_terminals:\n", "\n", " print(t + ' = ' + str(front_sunk[t].iloc[-1]))\n", " \n", " ax.scatter(front_sunk['ReleaseDate'].iloc[-1], front_sunk[t].iloc[-1], \n", " color=colours[cz], marker='o', s=80)\n", " ax.plot(front_sunk['ReleaseDate'], front_sunk[t], \n", " linewidth=2.5, label=my_terminals[cz], color=colours[cz])\n", " ax.plot(latest_sunk['LoadDate'].iloc[1:], latest_sunk[t].iloc[1:], \n", " linewidth=2.0, marker='s', linestyle='--', color=colours[cz])\n", "\n", " if cz == 0:\n", " # Plot US via Panama NE-Asia Netback\n", " ax.scatter(front_sunk['ReleaseDate'].iloc[-1], front_sunk['NeaNetbackTtfBasis_SabPanama'].iloc[-1], \n", " color='#4F41F4', marker='o', s=80)\n", " ax.plot(front_sunk['ReleaseDate'], front_sunk['NeaNetbackTtfBasis_SabPanama'], \n", " linewidth=2.5, label='NEA via Panama', color='#4F41F4')\n", " ax.plot(latest_sunk['LoadDate'].iloc[1:], latest_sunk['NeaNetbackTtfBasis_SabPanama'].iloc[1:], \n", " linewidth=2.0, marker='s', linestyle='-', color='#4F41F4')\n", " \n", " # Plot US via COGH NE-Asia Netback\n", " #ax.scatter(front_sunk['ReleaseDate'].iloc[-1], front_sunk['NeaNetbackTtfBasis_SabPanama'].iloc[-1], \n", " # color='#4F41F4', marker='o', s=80)\n", " #ax.plot(front_sunk['ReleaseDate'], front_sunk['NeaNetbackTtfBasis_SabPanama'], \n", " # linewidth=2.5, label='NEA via COGH', color='#4F41F4')\n", " #ax.plot(latest_full['LoadDate'].iloc[1:], latest_sunk['NeaNetbackTtfBasis_SabPanama'].iloc[1:], \n", " # linewidth=2.0, marker='s', linestyle='-', color='#4F41F4')\n", " \n", " cz+=1\n", "\n", "plt.tight_layout()\n", "\n", "ax.legend(loc='lower right', frameon=True, edgecolor='#dddddd', fontsize=10.5)\n", "\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "60f412b2", "metadata": {}, "source": [ "## 6.2. European Regas vs Asia Arb\n", "\n", "Taking the difference of each terminals FOB Hub Netback and the Cargo Netback to Asia (in this case, via Panama) to calculate a \"Regas Arb\" vs Asia" ] }, { "cell_type": "code", "execution_count": null, "id": "5819fae8", "metadata": {}, "outputs": [], "source": [ "fig, ax= plt.subplots(figsize=(14,7))\n", "fig.subplots_adjust(top=0.78, bottom=0.10, left=0.06, right=0.96)\n", "\n", "# User input for which countries to plot, and associated plot colours\n", "my_terminals = ['Deutsche Ostsee', 'Brunsbuttel', 'Gate', 'Isle of Grain', 'South Hook', 'Fos Cavaou', 'Montoir', 'OLT Toscana', 'Spain TVB']\n", "colours = ['orange', 'sienna', 'grey', '#0086ad', '#011f4b', '#00c2c7', 'salmon', 'mediumseagreen', 'firebrick']\n", "\n", "print(\"Latest Assessment: \" + str(front_full['ReleaseDate'].iloc[0]))\n", "\n", "# Iterating over all terminals to plot\n", "cz = 0\n", "for t in my_terminals:\n", "\n", " print(t + ' = ' + str(front_sunk[t].iloc[-1]))\n", " \n", " ax.scatter(front_sunk['ReleaseDate'].iloc[-1], front_sunk[t].iloc[-1] - front_sunk['NeaNetbackTtfBasis_SabPanama'].iloc[-1], \n", " color=colours[cz], marker='o', s=80)\n", " ax.plot(front_sunk['ReleaseDate'], front_sunk[t] - front_sunk['NeaNetbackTtfBasis_SabPanama'], \n", " linewidth=2.5, label=my_terminals[cz], color=colours[cz])\n", " ax.plot(latest_sunk['LoadDate'].iloc[1:], latest_sunk[t].iloc[1:] - latest_sunk['NeaNetbackTtfBasis_SabPanama'].iloc[1:], \n", " linewidth=2.0, marker='s', linestyle='--', color=colours[cz])\n", " \n", " cz+=1\n", "\n", "# Plot y=0 line to make the In the Money/Out the Money boundary clear\n", "ax.hlines(0, front_sunk['ReleaseDate'].iloc[0], latest_sunk['LoadDate'].iloc[-1]+pd.Timedelta(days=20), color='grey', zorder=0)\n", "\n", "# Conditional <0 red shading\n", "negrange = [front_sunk['ReleaseDate'].iloc[0] -pd.Timedelta(days=5), latest_sunk['LoadDate'].iloc[-1]+pd.Timedelta(days=5)]\n", "ax.fill_between(negrange, 0, -30, color='red', alpha=0.05)\n", "\n", "# Axis params\n", "plt.xlim([front_sunk['ReleaseDate'].iloc[0]-pd.Timedelta(days=1), latest_sunk['LoadDate'].iloc[-1]+pd.Timedelta(days=5)])\n", "plt.ylim(-4, 2)\n", "plt.tight_layout()\n", "ax.legend(loc='lower right', frameon=True, edgecolor='#dddddd', fontsize=9.5)\n", "\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "faa5ccd8", "metadata": {}, "source": [ "### Below are several additional options:\n", "\n", "- Use the \"countries_full\" and \"countries_sunk\" dataframes to plot for country averaged FOB Hub Netbacks (although \"front_\" and \"latest_\" dataframes will need to be defined above for the country-averaged data)\n", "- use the \"front_full\" and \"latest_full\" dataframes to plot the FOB Hub Netbacks with all regas costs included" ] } ], "metadata": { "kernelspec": { "display_name": "base", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.9" } }, "nbformat": 4, "nbformat_minor": 5 }