{ "cells": [ { "cell_type": "markdown", "id": "b0e8ab17", "metadata": {}, "source": [ "# DES Hub Netbacks - Historical Moneyness Forward Curve\n", "\n", "Calling the Spark v1.0 DES Hub Netbacks endpoint and persisting SparkNWE / SparkSWE forward curves to produce a daily DES LNG forward curve per terminal. Moneyness = DES Hub Netback (Variable Regas Costs Only) minus the relevant regional marker.\n", "\n", "For a full explanation of how to import our DES Hub Netbacks or SparkNWE data, please refer to our Python Jupyter Notebook Code Samples:\n", "\n", "https://www.sparkcommodities.com/api/code-examples/jupyter.html\n" ] }, { "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__\n", "\n", "or refer to our API website for more information about this endpoint: https://www.sparkcommodities.com/api/lng-access/des-hub-netbacks.html" ] }, { "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", " Get a new access_token. Access tokens are the thing that applications use to make\n", " API requests. Access tokens must be kept confidential in storage.\n", "\n", " # Procedure:\n", "\n", " Do a POST query with `grantType` and `scopes` in the body. A basic authorization\n", " HTTP header is required. The \"Basic\" HTTP authentication scheme is defined in\n", " RFC 7617, which transmits credentials as `clientId:clientSecret` pairs, encoded\n", " using base64.\n", " \"\"\"\n", "\n", " # Note: for the sake of this example, we choose to use the Python urllib from the\n", " # standard lib. One should consider using https://requests.readthedocs.io/\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", " \"scopes\": \"read:access,read:prices\"\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. Reference Data\n", "\n", "The reference-data endpoint lists every DES Hub Netbacks terminal, along with its `TerminalUUID` and the Gas Hubs used as the front-month and forward-curve price sources. Use the UUID whenever you want to filter subsequent calls to a single terminal." ] }, { "cell_type": "code", "execution_count": null, "id": "a81712ef", "metadata": {}, "outputs": [], "source": [ "## Defining the reference-data calling function\n", "\n", "def fetch_deshub_reference_data(access_token, format='json'):\n", "\n", " uri = \"/v1.0/lng/access/des-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_deshub_reference_data(access_token, format='csv')\n", "ref_data" ] }, { "cell_type": "markdown", "id": "5b147ac3", "metadata": {}, "source": [ "## 3. Historical Data\n", "\n", "The historical endpoint returns DES Hub Netbacks releases for any date range (max 365 days per call)." ] }, { "cell_type": "code", "execution_count": null, "id": "de266a8e", "metadata": {}, "outputs": [], "source": [ "## Defining the function\n", "\n", "def fetch_historical_price_releases(access_token, unit, start=None, end=None,\n", " terminal_uuid=None, limit=None, offset=None,\n", " format='json'):\n", "\n", " query_params = \"?unit={}\".format(unit)\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", " if terminal_uuid is not None:\n", " query_params += \"&terminal-uuid={}\".format(terminal_uuid)\n", " if limit is not None:\n", " query_params += \"&limit={}\".format(limit)\n", " if offset is not None:\n", " query_params += \"&offset={}\".format(offset)\n", "\n", " uri = \"/v1.0/lng/access/des-hub-netbacks/{}\".format(query_params)\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", " for c in list(data.columns)[8:]:\n", " data[c] = pd.to_numeric(data[c], errors='coerce')\n", " data['ReleaseDate'] = pd.to_datetime(data['ReleaseDate'])\n", " data['DeliveryMonth'] = pd.to_datetime(data['DeliveryMonth'])\n", "\n", " return data" ] }, { "cell_type": "markdown", "id": "cfebef96", "metadata": {}, "source": [ "## N.B. Historical Data Limits\n", "\n", "A single call to the historical endpoint is limited to 365 days of data. If you need a longer range, use the loop function below, which calls the 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": [ "def loop_historical_data(access_token, hist_unit, hist_start, hist_end):\n", "\n", " # input validation\n", " start_dt = datetime.datetime.strptime(hist_start, '%Y-%m-%d')\n", " end_dt = datetime.datetime.strptime(hist_end, '%Y-%m-%d')\n", "\n", " if start_dt >= end_dt:\n", " raise ValueError(\n", " \"hist_start ({}) must be strictly before hist_end ({})\".format(hist_start, hist_end)\n", " )\n", "\n", " hist_diff = (end_dt - start_dt).days\n", " t = 0\n", " starts = []\n", " ends = []\n", "\n", " # window size - 1 year per call\n", " w = 365\n", "\n", " while t < hist_diff:\n", " # initialising dataframe\n", " if t == 0:\n", " # bound the first window by hist_end so we never request beyond it\n", " diff_end = min(start_dt + pd.Timedelta(days=w), end_dt).strftime('%Y-%m-%d')\n", " hist_df = fetch_historical_price_releases(\n", " access_token, unit=hist_unit, start=hist_start, end=diff_end, format='csv'\n", " )\n", " starts.append(hist_start)\n", " ends.append(diff_end)\n", "\n", " # appending additional historical data\n", " else:\n", " diff_start_dt = start_dt + pd.Timedelta(days=t + 1)\n", " diff_start = diff_start_dt.strftime('%Y-%m-%d')\n", " # bound every subsequent window by hist_end\n", " diff_end = min(diff_start_dt + pd.Timedelta(days=w), end_dt).strftime('%Y-%m-%d')\n", "\n", " historical_addition = fetch_historical_price_releases(\n", " access_token, unit=hist_unit, start=diff_start, end=diff_end, format='csv'\n", " )\n", " try:\n", " hist_df = pd.concat([hist_df, historical_addition])\n", " except Exception as e:\n", " # fallback if the first slice came back empty\n", " print(e)\n", " hist_df = historical_addition.copy()\n", " starts.append(diff_start)\n", " ends.append(diff_end)\n", "\n", " t += w\n", "\n", " hist_df = hist_df.drop_duplicates().reset_index(drop=True)\n", "\n", " return hist_df" ] }, { "cell_type": "code", "execution_count": null, "id": "93293cb1", "metadata": {}, "outputs": [], "source": [ "# Input start and end dates\n", "today = datetime.datetime.now().strftime('%Y-%m-%d')\n", "#hist_start = (datetime.datetime.now() - datetime.timedelta(days=365)).strftime('%Y-%m-%d')\n", "hist_start = '2026-02-01'\n", "\n", "# Call Data\n", "hist_df = loop_historical_data(access_token, hist_unit='usd-per-mmbtu',\n", " hist_start=hist_start, hist_end=today)\n", "\n", "hist_df.head(5)" ] }, { "cell_type": "markdown", "id": "084d744e", "metadata": {}, "source": [ "## 4. Sinking Regas Costs Components\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 = hist_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 hist_df.columns]\n", " if missing:\n", " raise KeyError('sunk_terminal_costs[{!r}] references missing columns: {}'.format(terminal, missing))\n", " mask = hist_df['TerminalName'] == terminal\n", " if mask.any():\n", " sunk_add_back.loc[mask] += hist_df.loc[mask, cols].fillna(0).sum(axis=1)\n", "\n", "hist_df['NetbackTTFBasisSunk'] = hist_df['NetbackTTFBasis'] + sunk_add_back\n", "hist_df['NetbackOutrightSunk'] = hist_df['NetbackOutright'] + sunk_add_back\n", "\n", "hist_df[['ReleaseDate', 'TerminalName', 'DeliveryMonthIndex', 'NetbackTTFBasis',\n", " 'NetbackTTFBasisSunk']].head(10)\n" ] }, { "cell_type": "markdown", "id": "36eff6fe", "metadata": {}, "source": [ "### Pivot into a wide DataFrame - one column per terminal\n", "\n", "For the moneyness merge downstream we want a wide-format table: one row per (ReleaseDate, DeliveryMonth) with a column per terminal containing its `NetbackTTFBasisSunk`." ] }, { "cell_type": "code", "execution_count": null, "id": "b8dfd892", "metadata": {}, "outputs": [], "source": [ "# one row per (ReleaseDate, DeliveryMonth), one column per terminal\n", "terminal_list = list(hist_df['TerminalName'].unique())\n", "\n", "id_cols = ['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', 'DeliveryMonthName']\n", "value_col = 'NetbackTTFBasisSunk'\n", "\n", "deshub_final = (\n", " hist_df\n", " .pivot_table(index=id_cols, columns='TerminalName', values=value_col, aggfunc='first')\n", " .reset_index()\n", ")\n", "deshub_final.columns.name = None\n", "\n", "# restore terminal-appearance ordering (pivot_table sorts alphabetically by default)\n", "deshub_final = deshub_final[id_cols + terminal_list]\n", "deshub_final.head(5)" ] }, { "cell_type": "markdown", "id": "9cd3c12e", "metadata": {}, "source": [ "## 5. Classification Dictionaries\n", "\n", "Maps each terminal (keyed by its v1.0 `TerminalName`) to either `nwe` or `swe`, which in turn controls which Spark regional marker gets subtracted in the moneyness calculation.\n", "\n", "A second dictionary maps terminals to countries for optional country-level aggregation." ] }, { "cell_type": "code", "execution_count": null, "id": "4b3bee7f", "metadata": {}, "outputs": [], "source": [ "# Region mapping \n", "terminal_region_dict = {\n", " 'Gate': 'nwe',\n", " 'Isle of Grain': 'nwe',\n", " 'Zeebrugge': 'nwe',\n", " 'South Hook': 'nwe',\n", " 'Dunkerque': 'nwe',\n", " 'Le Havre': 'nwe',\n", " 'Montoir': 'nwe',\n", " 'EemsEnergyTerminal': 'nwe',\n", " 'Brunsbuttel': 'nwe',\n", " 'Deutsche Ostsee': 'nwe',\n", " 'Deutsche Ostsee Phase 1': 'nwe',\n", " 'Wilhelmshaven 1': 'nwe',\n", " 'Wilhelmshaven 2': 'nwe',\n", " 'Stade': 'nwe',\n", " 'Fos Cavaou': 'swe',\n", " 'Adriatic': 'swe',\n", " 'OLT Toscana': 'swe',\n", " 'Piombino': 'swe',\n", " 'Ravenna': 'swe',\n", " 'Spain TVB': 'swe',\n", "}\n", "\n", "# Country mapping\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',\n", " 'Wilhelmshaven 1', 'Wilhelmshaven 2', 'Stade'],\n", " 'Italy': ['Adriatic', 'OLT Toscana', 'Piombino', 'Ravenna'],\n", " 'Spain': ['Spain TVB'],\n", "}" ] }, { "cell_type": "markdown", "id": "53659b47", "metadata": {}, "source": [ "## 6. SparkNWE & SparkSWE - Data Import\n", "\n", "Calling the SparkNWE and SparkSWE front-month + forward-curve cargo contracts (TTF basis) to compare against the terminal DES Hub netbacks. Moneyness = DES Hub Netback (Var Regas Only) - regional marker." ] }, { "cell_type": "code", "execution_count": null, "id": "91545e77", "metadata": {}, "outputs": [], "source": [ "# Defining function to call contracts endpoint to import cargo data\n", "def fetch_cargo_releases(access_token, ticker, limit=4, offset=None):\n", "\n", " query_params = \"?limit={}\".format(limit)\n", " if offset is not None:\n", " query_params += \"&offset={}\".format(offset)\n", "\n", " uri = \"/v1.0/contracts/{}/price-releases/{}\".format(ticker, query_params)\n", " print(uri)\n", "\n", " content = do_api_get_query(uri=uri, access_token=access_token)\n", "\n", " return content['data']\n", "\n", "\n", "# call front month and forward curve, combine into one DataFrame per release date\n", "def cargo_focurve(access_token, ticker, limit):\n", "\n", " f_tick = ticker + '-b-f'\n", " fo_tick = ticker + '-b-fo'\n", "\n", " f_data = fetch_cargo_releases(access_token, f_tick, limit)\n", " fo_data = fetch_cargo_releases(access_token, fo_tick, limit)\n", "\n", " release_dates = []\n", " period_start = []\n", " contract_ids = []\n", " spark = []\n", "\n", " # single-release convenience path (latest only)\n", " if limit == 1:\n", " release_date = f_data[0][\"releaseDate\"]\n", " contract_ids.append(f_data[0]['contractId'])\n", " release_dates.append(release_date)\n", "\n", " period_start_at = f_data[0]['data'][0]['dataPoints'][0][\"deliveryPeriod\"][\"startAt\"]\n", " period_start.append(period_start_at)\n", " spark.append(f_data[0]['data'][0]['dataPoints'][0]['derivedPrices']['usdPerMMBtu']['spark'])\n", "\n", " for release in fo_data:\n", " for data_point in release['data'][0]['dataPoints']:\n", " contract_ids.append(release['contractId'])\n", " release_dates.append(release[\"releaseDate\"])\n", " period_start.append(data_point[\"deliveryPeriod\"][\"startAt\"])\n", " spark.append(data_point['derivedPrices']['usdPerMMBtu']['spark'])\n", "\n", " # full historical path: align each front month release with the most recent\n", " # forward curve release on or before it\n", " else:\n", " cl = 0\n", "\n", " for l in range(limit):\n", " try:\n", " release_date = f_data[l][\"releaseDate\"]\n", " except (IndexError, KeyError):\n", " break\n", "\n", " contract_ids.append(f_data[l]['contractId'])\n", " release_dates.append(release_date)\n", " release_datetime = datetime.datetime.strptime(release_date, '%Y-%m-%d')\n", "\n", " front_period_start_at = f_data[l]['data'][0]['dataPoints'][0][\"deliveryPeriod\"][\"startAt\"]\n", " period_start.append(front_period_start_at)\n", " spark.append(f_data[l]['data'][0]['dataPoints'][0]['derivedPrices']['usdPerMMBtu']['spark'])\n", "\n", " # guard against running off the end of fo_data\n", " if cl >= len(fo_data):\n", " continue\n", "\n", " fo_datetime = datetime.datetime.strptime(fo_data[cl]['releaseDate'], '%Y-%m-%d')\n", "\n", " # assessment date - front month AND forward curve both published today\n", " if release_datetime == fo_datetime:\n", " for data_point in fo_data[cl]['data'][0]['dataPoints']:\n", " contract_ids.append(fo_data[cl]['contractId'])\n", " release_dates.append(release_date)\n", " period_start.append(data_point[\"deliveryPeriod\"][\"startAt\"])\n", " spark.append(data_point['derivedPrices']['usdPerMMBtu']['spark'])\n", " cl += 1\n", "\n", " # non-assessment date - persist most recent published forward curve\n", " # but drop the data point that overlaps with today's front month\n", " else:\n", " for data_point in fo_data[cl]['data'][0]['dataPoints']:\n", " period_start_at = data_point[\"deliveryPeriod\"][\"startAt\"]\n", " if period_start_at != front_period_start_at:\n", " contract_ids.append(fo_data[cl]['contractId'])\n", " release_dates.append(release_date)\n", " period_start.append(period_start_at)\n", " spark.append(data_point['derivedPrices']['usdPerMMBtu']['spark'])\n", "\n", " hist_cargo_df = pd.DataFrame({\n", " 'Release Date': release_dates,\n", " 'ticker': contract_ids,\n", " 'Period Start': period_start,\n", " 'Price': spark,\n", " })\n", "\n", " hist_cargo_df['Price'] = pd.to_numeric(hist_cargo_df['Price'])\n", " hist_cargo_df['Release Date'] = pd.to_datetime(hist_cargo_df['Release Date']).dt.tz_localize(None)\n", "\n", " return hist_cargo_df" ] }, { "cell_type": "code", "execution_count": null, "id": "6f3a9010", "metadata": {}, "outputs": [], "source": [ "# calling the data\n", "nwe_fo_df = cargo_focurve(access_token, 'sparknwe', limit=5000)\n", "swe_fo_df = cargo_focurve(access_token, 'sparkswe', limit=5000)" ] }, { "cell_type": "code", "execution_count": null, "id": "b803bec7", "metadata": {}, "outputs": [], "source": [ "# Merging the NWE and SWE datasets into a single dataframe\n", "cargo_df = pd.merge(nwe_fo_df, swe_fo_df, on=['Release Date', 'Period Start'], how='left')\n", "cargo_df = cargo_df.rename(columns={\n", " 'Price_x': 'SparkNWE',\n", " 'Price_y': 'SparkSWE',\n", "})\n", "cargo_df = cargo_df.drop(columns=['ticker_x', 'ticker_y'])\n", "\n", "# cut off data prior to daily front-month assessments for NWE (switchover 2023-11-23)\n", "switch_date = '2023-11-23' # Do not edit (value should be 2023-11-23)\n", "date_limit = cargo_df[cargo_df['Release Date'] == switch_date].index[-1]\n", "cargo_df = cargo_df.iloc[:date_limit + 1].copy()\n", "\n", "# Persist SparkSWE forward curves across non-assessment days (SWE fo is weekly, NWE is daily) \n", "# Groupby 'Period Start' to make sure data fills match against Period Start\n", "# limit=14 covers potential bank holiday gaps.\n", "cargo_df['Period Start'] = pd.to_datetime(cargo_df['Period Start'])\n", "cargo_df = cargo_df.sort_values(['Period Start', 'Release Date']).reset_index(drop=True)\n", "cargo_df['SparkSWE'] = cargo_df.groupby('Period Start')['SparkSWE'].ffill(limit=14)\n", "\n", "# Order by release date\n", "cargo_df = cargo_df.sort_values(['Release Date', 'Period Start'], ascending=[False, True]).reset_index(drop=True)" ] }, { "cell_type": "markdown", "id": "f0920c79", "metadata": {}, "source": [ "## 7. Final Moneyness Table\n", "\n", "Moneyness = DES Hub Netback (w/ or w/o Sunk Costs) - Spark regional marker, where the marker is SparkNWE (NWE terminals) or SparkSWE (SWE terminals)." ] }, { "cell_type": "code", "execution_count": null, "id": "cdbcd586", "metadata": {}, "outputs": [], "source": [ "# Vectorised moneyness calculation.\n", "\n", "# only consider terminals mapped to a region\n", "new_terminal_list = [t for t in terminal_list if t in terminal_region_dict]\n", "unmapped = [t for t in terminal_list if t not in terminal_region_dict]\n", "if unmapped:\n", " print('Skipping terminals not mapped to a region in terminal_region_dict: {}'.format(unmapped))\n", "\n", "# merge DES Hub netbacks with NWE/SWE data \n", "money_merge = pd.merge(\n", " deshub_final,\n", " cargo_df[['Release Date', 'Period Start', 'SparkNWE', 'SparkSWE']],\n", " left_on=['ReleaseDate', 'DeliveryMonth'],\n", " right_on=['Release Date', 'Period Start'],\n", " how='left',\n", ")\n", "\n", "# initialise output\n", "money_df = money_merge[['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', 'DeliveryMonthName']].copy()\n", "\n", "# compute moneyness per terminal\n", "nwe_terms = [t for t in new_terminal_list if terminal_region_dict[t] == 'nwe']\n", "swe_terms = [t for t in new_terminal_list if terminal_region_dict[t] == 'swe']\n", "\n", "# Subtract either NWE or SWE marker depending on terminal\n", "for t in nwe_terms:\n", " money_df[t] = money_merge[t] - money_merge['SparkNWE']\n", "for t in swe_terms:\n", " money_df[t] = money_merge[t] - money_merge['SparkSWE']\n", "\n", "# round to 3 d.p.\n", "money_df[new_terminal_list] = money_df[new_terminal_list].round(3)" ] }, { "cell_type": "code", "execution_count": null, "id": "7aa61e82", "metadata": {}, "outputs": [], "source": [ "# Filtering data to view latest forward curve\n", "latest = money_df['ReleaseDate'].unique()[-1]\n", "money_df[money_df['ReleaseDate'] == latest]" ] }, { "cell_type": "markdown", "id": "cfca71e4", "metadata": {}, "source": [ "# Average by country" ] }, { "cell_type": "code", "execution_count": null, "id": "c5f7a2d3", "metadata": {}, "outputs": [], "source": [ "# Function to average by country\n", "\n", "id_cols = ['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', 'DeliveryMonthName']\n", "\n", "def aggregate_by_country(df, country_dict, id_cols=id_cols, decimals=3):\n", " source = df # keep original (has terminal columns)\n", " out = df[id_cols].copy() # output starts with just id cols\n", "\n", " # Iterate through countries\n", " for country, terminals in country_dict.items():\n", " # Only use terminals actually present as columns\n", " present = [t for t in terminals if t in source.columns] # check ORIGINAL\n", " if not present:\n", " # No terminals available for this country in this df — emit NaN column\n", " out[country] = np.nan\n", " continue\n", " # Average over all relevant terminals for country\n", " out[country] = source[present].mean(axis=1, skipna=True).round(decimals)\n", " return out\n", "\n", "\n", "# Country-level moneyness (averaged from money_df)\n", "country_money = aggregate_by_country(money_df, terminal_country_dict)\n", "\n", "# Country-level netback (averaged from deshub_final, i.e. NetbackTTFBasisSunk)\n", "country_df = aggregate_by_country(deshub_final, terminal_country_dict)" ] }, { "cell_type": "markdown", "id": "e127e2ca", "metadata": {}, "source": [ "# 8. Plotting" ] }, { "cell_type": "markdown", "id": "b4d47bfd", "metadata": {}, "source": [ "## 8.1. Plotting Front Month Moneyness Evolution for several terminals" ] }, { "cell_type": "code", "execution_count": null, "id": "1ddf59ec", "metadata": {}, "outputs": [], "source": [ "# Front month and forward curve dataframes for terminals\n", "frontmonth_term = money_df[money_df['DeliveryMonthIndex'] == 'M+1'].copy()\n", "latest_term = money_df[money_df['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", "\n", "ax.hlines(0, frontmonth_term['ReleaseDate'].iloc[0], latest_term['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20), color='grey', zorder=0)\n", "\n", "# User input for which countries to plot, and associated plot colours\n", "my_terminals = ['Brunsbuttel', 'EemsEnergyTerminal', 'Zeebrugge', 'Montoir', 'Spain TVB', 'Gate']\n", "colours = ['black', '#0086ad', 'forestgreen', '#00c2c7', 'firebrick', 'orange']\n", "\n", "print(\"Latest Assessment: \" + str(frontmonth_term['ReleaseDate'].iloc[0]))\n", "\n", "# Iterating through all chosen terminals\n", "cz = 0\n", "for t in my_terminals:\n", " # Print latest Moneyness value\n", " print(t + ' = ' + str(frontmonth_term[t].iloc[-1]))\n", "\n", " # Plot front month and forward curve\n", " ax.scatter(frontmonth_term['ReleaseDate'].iloc[-1], frontmonth_term[t].iloc[-1], color=colours[cz], marker='o', s=80)\n", " ax.plot(frontmonth_term['ReleaseDate'], frontmonth_term[t], linewidth=2.0, label=t, color=colours[cz])\n", " ax.plot(latest_term['DeliveryMonth'].iloc[1:], latest_term[t].iloc[1:], linewidth=2.0, marker='s', linestyle='--', color=colours[cz])\n", " \n", " cz+=1\n", "\n", "# Conditional <0 red shading\n", "negrange = [frontmonth_term['ReleaseDate'].iloc[0]-pd.Timedelta(days=1), latest_term['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20)]\n", "ax.fill_between(negrange, 0, -30, color='red', alpha=0.05)\n", "\n", "# Axis params\n", "plt.xlim([frontmonth_term['ReleaseDate'].iloc[0]-pd.Timedelta(days=1), latest_term['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20)])\n", "plt.ylim(money_df[my_terminals].min().min(), money_df[my_terminals].max().max())\n", "plt.tight_layout()\n", "ax.legend(loc='upper right', frameon=True, edgecolor='#dddddd', fontsize=9.5)\n", "\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "e66ffc59", "metadata": {}, "source": [ "## 8.2. Plotting Country-Averaged Moneyness" ] }, { "cell_type": "code", "execution_count": null, "id": "9a797f7b", "metadata": {}, "outputs": [], "source": [ "# Front month and forward curve dataframes for countries\n", "frontmonth_country = country_money[country_money['DeliveryMonthIndex'] == 'M+1'].copy()\n", "latest_country = country_money[country_money['ReleaseDate'] == latest].copy()" ] }, { "cell_type": "code", "execution_count": null, "id": "e7497d6c", "metadata": {}, "outputs": [], "source": [ "fig, ax= plt.subplots(figsize=(14,7))\n", "\n", "ax.hlines(0, frontmonth_country['ReleaseDate'].iloc[0], latest_country['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20), color='grey', zorder=0)\n", "\n", "# User input for which countries to plot, and associated plot colours\n", "my_countries = ['UK', 'France', 'Italy', 'Germany', 'Spain', 'Netherlands']\n", "colours = ['#0086ad', '#00c2c7', 'forestgreen', 'black', 'firebrick', 'orange']\n", "\n", "print(\"Latest Assessment: \" + str(frontmonth_country['ReleaseDate'].iloc[0]))\n", "\n", "# Iterating through all chosen countries\n", "cz = 0\n", "for t in my_countries:\n", " # Print latest Moneyness value\n", " print(t + ' = ' + str(frontmonth_country[t].iloc[-1]))\n", "\n", " # Plot front month and forward curve\n", " ax.scatter(frontmonth_country['ReleaseDate'].iloc[-1], frontmonth_country[t].iloc[-1], color=colours[cz], marker='o', s=80)\n", " ax.plot(frontmonth_country['ReleaseDate'], frontmonth_country[t], linewidth=2.0, label=t, color=colours[cz])\n", " ax.plot(latest_country['DeliveryMonth'].iloc[1:], latest_country[t].iloc[1:], linewidth=2.0, marker='s', linestyle='--', color=colours[cz])\n", " \n", " cz+=1\n", "\n", "# Conditional <0 red shading\n", "negrange = [frontmonth_country['ReleaseDate'].iloc[0]-pd.Timedelta(days=1), latest_country['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20)]\n", "ax.fill_between(negrange, 0, -30, color='red', alpha=0.05)\n", "\n", "# Axis params\n", "plt.xlim([frontmonth_country['ReleaseDate'].iloc[0]-pd.Timedelta(days=1), latest_country['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20)])\n", "plt.ylim(country_money[my_countries].min().min(), country_money[my_countries].max().max())\n", "plt.tight_layout()\n", "ax.legend(loc='lower right', frameon=True, edgecolor='#dddddd', fontsize=11.5)\n", "\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "3180a1cd", "metadata": {}, "source": [ "## 8.3. Plotting Custom Month Historical Evolution" ] }, { "cell_type": "code", "execution_count": null, "id": "e038654a", "metadata": {}, "outputs": [], "source": [ "# Choose Delivery Month of interest\n", "month = 'Sep26'\n", "\n", "month_term = money_df[money_df['DeliveryMonthName'] == month].copy()\n", "month_term.head(3)" ] }, { "cell_type": "code", "execution_count": null, "id": "7598391c", "metadata": {}, "outputs": [], "source": [ "fig, ax= plt.subplots(figsize=(14,7))\n", "\n", "ax.hlines(0, month_term['ReleaseDate'].iloc[0], month_term['ReleaseDate'].iloc[-1]+pd.Timedelta(days=20), color='grey', zorder=0)\n", "\n", "# User input for which countries to plot, and associated plot colours\n", "my_terminals = ['Brunsbuttel', 'EemsEnergyTerminal', 'Zeebrugge', 'Montoir', 'Spain TVB', 'Gate']\n", "colours = ['black', '#0086ad', 'forestgreen', '#00c2c7', 'firebrick', 'orange']\n", "\n", "print(\"Latest Assessment: \" + str(month_term['ReleaseDate'].iloc[0]))\n", "\n", "# Iterating through all chosen terminals\n", "cz = 0\n", "for t in my_terminals:\n", " # Print latest Moneyness value\n", " print(t + ' = ' + str(month_term[t].iloc[-1]))\n", "\n", " # Plot historical evolution\n", " ax.scatter(month_term['ReleaseDate'].iloc[-1], month_term[t].iloc[-1], color=colours[cz], marker='o', s=80)\n", " ax.plot(month_term['ReleaseDate'], month_term[t], linewidth=2.0, label=t, color=colours[cz])\n", " \n", " cz+=1\n", "\n", "# Conditional <0 red shading\n", "negrange = [month_term['ReleaseDate'].iloc[0] -pd.Timedelta(days=5), month_term['ReleaseDate'].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([month_term['ReleaseDate'].iloc[0]-pd.Timedelta(days=1), month_term['ReleaseDate'].iloc[-1]+pd.Timedelta(days=5)])\n", "plt.ylim(month_term[my_terminals].min().min(), month_term[my_terminals].max().max())\n", "plt.tight_layout()\n", "ax.legend(loc='upper right', frameon=True, edgecolor='#dddddd', fontsize=9.5)\n", "plt.title('European Terminal Moneyness - ' + month)\n", "\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "43af2fa7", "metadata": {}, "source": [ "## 8.4. Plotting Forward Curve Evolution for a specific terminal" ] }, { "cell_type": "code", "execution_count": null, "id": "bfdd3a91", "metadata": {}, "outputs": [], "source": [ "my_terminal = 'Montoir'\n", "term_df = money_df[['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', my_terminal]].copy().dropna()\n", "\n", "available_rels = list(term_df['ReleaseDate'].unique())\n", "print('Earliest Date: ' + str(available_rels[0]))\n", "print('Latest Date: ' + str(available_rels[-1]))" ] }, { "cell_type": "code", "execution_count": null, "id": "240a6b5a", "metadata": {}, "outputs": [], "source": [ "# Manually define which release dates to compare forward curves for\n", "#plot_dates = ['2025-01-02', '2025-03-03', '2025-05-02', '2025-07-01', latest]\n", "\n", "# Alternative: auto-pick N release dates evenly spaced between the earliest and latest available forward curves\n", "n_curves = 4\n", "plot_dates = [str(d)[:10] for d in \n", " np.sort(term_df['ReleaseDate'].unique())[np.linspace(0, term_df['ReleaseDate'].nunique()-1, n_curves).round().astype(int)]]\n", "\n", "# Checking each release date is valid & has an associated forward curve\n", "fo_curves = []\n", "for d in plot_dates:\n", " curve = term_df[term_df['ReleaseDate'] == d][['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', my_terminal]]\n", " if len(curve) == 0:\n", " print('No data for release date {} - skipping'.format(d))\n", " continue\n", " fo_curves.append(curve) \n", "if not fo_curves:\n", " raise ValueError('No release dates from plot_dates found in term_df')\n", "\n", "# setting ranges of alphas, linewidths and markersizes for each curve\n", "alphas = np.linspace(0.8, 1, len(fo_curves))\n", "linewidths = np.linspace(0.7, 2, len(fo_curves))\n", "msizes = np.linspace(6, 12, len(fo_curves))\n", "\n", "# Setting up axis\n", "fig, ax= plt.subplots(figsize=(16,7))\n", "\n", "# anchor x-axis to the latest plotted curve's earliest delivery month\n", "x_start = fo_curves[-1]['DeliveryMonth'].iloc[0]\n", "\n", "# Plotting forward curves, but only for contract months related to latest curve\n", "for f, fo in enumerate(fo_curves):\n", " release_label = str(fo['ReleaseDate'].iloc[0])[:10] # get release date to label curve\n", " fo = fo[fo['DeliveryMonth'] >= x_start] # only retrieve months that match those seen in the latest curve\n", " \n", " if fo.empty: # in case curve ends before x_start\n", " print('No overlap with x_start for release {} - skipping'.format(release_label))\n", " continue\n", " \n", " ax.plot(fo['DeliveryMonth'], fo[my_terminal], linestyle='--', marker='s',\n", " label=release_label, linewidth=linewidths[f], alpha=alphas[f], markersize=msizes[f])\n", "\n", "# Plot y=0 line to make the In the Money/Out the Money boundary clear\n", "ax.hlines(0, x_start-pd.Timedelta(days=40), term_df['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20), color='grey')\n", "\n", "# Shading <0 area of the chart\n", "negrange = [x_start -pd.Timedelta(days=20), term_df['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20)]\n", "ax.fill_between(negrange, 0, -30.0, color='red', alpha=0.05)\n", "\n", "plt.title('Moneyness Forward Curve Historical Evolution - ' + my_terminal)\n", "plt.xlim([x_start-pd.Timedelta(days=20), term_df['DeliveryMonth'].iloc[-1]+pd.Timedelta(days=20)])\n", "plt.ylim(min(term_df[my_terminal]), max(term_df[my_terminal]))\n", "plt.tight_layout()\n", "ax.legend(loc='lower right', frameon=True, edgecolor='#dddddd', fontsize=11.5)\n", "plt.show()" ] } ], "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 }