{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# European Regas Slots in the Money\n",
    "\n",
    "This script is used to look at the Moneyness of currently available European Regas Slots\n",
    "\n",
    "For a full explanation of how to import our Regas Slots or DES Hub Netbacks datasets, 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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries for importing data\n",
    "import json\n",
    "import os\n",
    "import sys\n",
    "import pandas as pd\n",
    "from base64 import b64encode\n",
    "from urllib.parse import urljoin\n",
    "from pprint import pprint\n",
    "import requests\n",
    "from io import StringIO\n",
    "import time\n",
    "import numpy as np\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\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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",
   "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 dowloaded them already.  Instructions for downloading your credentials can be found here:\n",
    "\n",
    "https://www.sparkcommodities.com/api/request/authentication.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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",
   "metadata": {},
   "source": [
    "# 2. Global Date Input\n",
    "\n",
    "Input which release date to fetch data & generate the slot moneyness table for. This input has 2 available options:\n",
    "- \"latest\" = generates the slot moneyness table for the latest data release for all datasets. This is usually the last business day\n",
    "- \"yyyy-mm-dd\" = input a date in the 'yyyy-mm-dd' format to choose a custom release date to generate the slot moneyness table for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = 'latest'\n",
    "#date = '2026-06-24'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Fetching Regas Slots Data\n",
    "\n",
    "Here we define the functions used to call the Regas Slots data from the Access Regas Slot Availability API. For more information on the Access Regas Slot Availability API, please visit the API website or download our Access Regas Slot API code sample:\n",
    "\n",
    "https://www.sparkcommodities.com/api/lng-access/slot-availability.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetchinbg reference data for slots\n",
    "\n",
    "def fetch_slots_reference_data(access_token, format='csv'):\n",
    "\n",
    "    uri = \"/beta/terminal-slots/terminals/\"\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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Call reference-data function\n",
    "slots_ref = fetch_slots_reference_data(access_token, format='csv')\n",
    "slots_ref.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fetching slot availability data for defined date\n",
    "\n",
    "def fetch_slots(access_token, date, format='csv'):\n",
    "\n",
    "    uri = \"/beta/terminal-slots/releases/{}\".format(date)\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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calling the above function\n",
    "slots_df = fetch_slots(access_token, date=date, format='csv')\n",
    "slots_df['ReleaseDate'] = pd.to_datetime(slots_df['ReleaseDate'])\n",
    "slots_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. Fetching & Computing Moneyness DataFrame\n",
    "\n",
    "## 4.1. Fetching DES Hub Netbacks data\n",
    "\n",
    "Here we define the functions used to call the DES Hub Netbacks data from the Access DES Hub Netbacks API. For more information on the Access DES Hub Netbacks API, please visit the API website or download our Access DES Hub Netbacks code sample:\n",
    "\n",
    "https://www.sparkcommodities.com/api/lng-access/des-hub-netbacks.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Defining the reference-data calling function\n",
    "\n",
    "def fetch_deshub_reference_data(access_token, format='csv'):\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\n",
    "\n",
    "\n",
    "# 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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Defining the function\n",
    "\n",
    "def fetch_historical_deshub_release(access_token, unit, start=None, end=None,\n",
    "                                    terminal_uuid=None, limit=None, offset=None,\n",
    "                                    format='csv'):\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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generating start and end dates\n",
    "\n",
    "if date == 'latest':\n",
    "    end = datetime.datetime.now().strftime('%Y-%m-%d')\n",
    "    prev_bday = (datetime.datetime.now() - pd.offsets.BDay(1)).strftime('%Y-%m-%d')\n",
    "else:\n",
    "    end = date\n",
    "    prev_bday = (datetime.datetime(end) - pd.offsets.BDay(1)).strftime('%Y-%m-%d')\n",
    "\n",
    "# Call Data\n",
    "deshub_df = fetch_historical_deshub_release(access_token, unit='usd-per-mmbtu',\n",
    "                                start=prev_bday, end=end, format='csv')\n",
    "\n",
    "deshub_df = deshub_df[deshub_df['ReleaseDate'] == deshub_df['ReleaseDate'].max()]\n",
    "\n",
    "deshub_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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 = deshub_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 deshub_df.columns]\n",
    "    if missing:\n",
    "        raise KeyError('sunk_terminal_costs[{!r}] references missing columns: {}'.format(terminal, missing))\n",
    "    mask = deshub_df['TerminalName'] == terminal\n",
    "    if mask.any():\n",
    "        sunk_add_back.loc[mask] += deshub_df.loc[mask, cols].fillna(0).sum(axis=1)\n",
    "\n",
    "deshub_df['NetbackTTFBasisSunk'] = deshub_df['NetbackTTFBasis'] + sunk_add_back\n",
    "deshub_df['NetbackOutrightSunk'] = deshub_df['NetbackOutright'] + sunk_add_back\n",
    "\n",
    "deshub_df[['ReleaseDate', 'TerminalName', 'DeliveryMonthIndex', 'NetbackTTFBasis',\n",
    "         'NetbackTTFBasisSunk']].head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# one row per (ReleaseDate, DeliveryMonth), one column per terminal\n",
    "terminal_list = list(deshub_df['TerminalName'].unique())\n",
    "\n",
    "id_cols = ['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', 'DeliveryMonthName']\n",
    "value_col = 'NetbackTTFBasisSunk'\n",
    "\n",
    "deshub_final = (\n",
    "    deshub_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",
   "metadata": {},
   "source": [
    "## 4.2. Fetching Cargo Prices\n",
    "\n",
    "Here we define the functions used to call the SparkNWE/SWE DES LNG data from the Cargo Contracts API. For more information on the Cargo Contracts API, please visit the API website or download our Cargo Contracts code sample:\n",
    "\n",
    "https://www.sparkcommodities.com/api/lng-cargo/contracts.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# calling all historical 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,
   "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'\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",
   "metadata": {},
   "source": [
    "## 4.3. Moneyness Calculations\n",
    "\n",
    "Moneyness is calculated as the difference between DES Hub Netbacks and SparkNWE/SWE markers. For more Moneyness Analytics, visit our other Analytics Gallery code samples:\n",
    "\n",
    "https://www.sparkcommodities.com/api/code-examples/analytics-examples.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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 (optional)\n",
    "terminal_country_dict = {\n",
    "    'Netherlands': ['Gate', 'EemsEnergyTerminal'],\n",
    "    'UK':          ['Isle of Grain', 'South Hook', 'Dragon'],\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",
    "    'Finland':     ['Inkoo'],\n",
    "    'Lithuania':   ['Klaipeda'],\n",
    "}\n",
    "\n",
    "# Reverse Mapping\n",
    "terminal_to_country = {t: c for c, terms in terminal_country_dict.items() for t in terms}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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": "markdown",
   "metadata": {},
   "source": [
    "# 5. Generating final \"Slots In the Money\" Dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preview Moneyness Dataframe\n",
    "money_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preview Slots Dataframe\n",
    "slots_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Matching money_df to the slots_df format\n",
    "\n",
    "# create the same \"M+X\" column names as slots_df\n",
    "format_cols = [f'M+{i}' for i in range(1, 13)]\n",
    "id_cols = ['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', 'DeliveryMonthName']\n",
    "money_terminals = [c for c in money_df.columns if c not in id_cols]\n",
    "\n",
    "# remove release date and index by terminal\n",
    "slots_display = slots_df.set_index('TerminalName').drop(columns='ReleaseDate')\n",
    "\n",
    "# 1. reshape money_df to match approximate structure of slots_df\n",
    "money_lookup = (money_df.set_index('DeliveryMonthIndex')[money_terminals].T)\n",
    "money_lookup.index.name = 'TerminalName'\n",
    "\n",
    "# creating an exact mapping of Moneyness values to slots_df, so it can be referenced for conditonal formatting\n",
    "gmap = (\n",
    "    money_lookup\n",
    "    .reindex(index=slots_display.index)\n",
    "    .reindex(columns=format_cols)\n",
    "    .apply(pd.to_numeric, errors='coerce')\n",
    ")\n",
    "\n",
    "gmap.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5.1. Final Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reformat slots_df by dropping release date and indexing by TerminalName\n",
    "slots_display = slots_df.set_index('TerminalName').drop(columns='ReleaseDate')\n",
    "\n",
    "# Creating MultiIndex so that table is organised into Countries and Terminals\n",
    "slots_display['Country'] = slots_display.index.map(terminal_to_country).fillna('Other')\n",
    "slots_display = (slots_display\n",
    "                 .set_index('Country', append=True)\n",
    "                 .reorder_levels(['Country', 'TerminalName'])\n",
    "                 .sort_index())\n",
    "\n",
    "# aligning gmap to Country/Terminal MultiIndex\n",
    "gmap = gmap.reindex(slots_display.index.get_level_values('TerminalName'))\n",
    "gmap.index = slots_display.index\n",
    "\n",
    "# creating colourmap params for the table conditional formatting\n",
    "vmax = np.nanmax(np.abs(gmap.values))        # finding max moneyness value to scale colourmap\n",
    "gamma = 0.5                                  # colour change rate: <1 = faster colour change near 0\n",
    "t = (gmap / vmax).clip(-1, 1)                # normalising colourmap values\n",
    "gmap_bent = np.sign(t) * np.abs(t) ** gamma  # applying colour change rate to the colourmap\n",
    "\n",
    "# Creating list of columns to show in table\n",
    "display_cols = ['Total', 'M+0'] + format_cols\n",
    "\n",
    "# Creating Styled DataFrame of available slots on chosen release date + moneyness\n",
    "slot_money = (\n",
    "    slots_display[display_cols].style\n",
    "    .background_gradient(\n",
    "        cmap='coolwarm',        # Choose colourmap \n",
    "        subset=format_cols,     # which columns to apply conditional formatting to\n",
    "        gmap=gmap_bent,         # apply colour mapping params \n",
    "        axis=None,\n",
    "        vmin=-1, vmax=1,        \n",
    "    )\n",
    "    .format(precision=0)        # how many decimal points\n",
    ")\n",
    "\n",
    "print(\"Slots Moneyness - \" + date)\n",
    "\n",
    "slot_money"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5.2. Optional Formatting using 'plottable'\n",
    "\n",
    "This section uses the Python package 'plottable' to apply style formatting to the above table - this is the formatting used in the Analytics Gallery thumbnail. 'plottable' is not a standard Python package and needs to be downloaded separately - as such, the code below is commented out to avoid script breaks. \n",
    "\n",
    "For more information on 'plottable', please refer to their documentation:\n",
    "\n",
    "https://plottable.readthedocs.io/en/latest/index.html#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"import matplotlib.pyplot as plt\n",
    "import matplotlib\n",
    "from plottable import ColumnDefinition, Table\n",
    "\n",
    "# reformat slots_df by dropping release date and indexing by TerminalName\n",
    "slots_display2 = slots_df.set_index('TerminalName').drop(columns='ReleaseDate')\n",
    "\n",
    "# Adding Country column and sorting accordingly\n",
    "slots_display2['Country'] = slots_display2.index.map(terminal_to_country).fillna('Other')\n",
    "slots_display2 = (slots_display2.reset_index()\n",
    "                 .sort_values(['Country', 'TerminalName'])\n",
    "                 .reset_index(drop=True))\n",
    "\n",
    "# blank repeated country labels so it reads like a grouped index\n",
    "slots_display2['Country'] = slots_display2['Country'].mask(\n",
    "    slots_display2['Country'].duplicated(), '')\n",
    "slots_display2 = slots_display2.set_index('Country')   # Country becomes the first column\n",
    "\n",
    "# moneyness lookup keyed by TerminalName & ignoring all terminals without Moneyness values\n",
    "id_cols = ['ReleaseDate', 'DeliveryMonth', 'DeliveryMonthIndex', 'DeliveryMonthName']\n",
    "money_terminals = [c for c in money_df.columns if c not in id_cols]\n",
    "gmap_plot = (\n",
    "    money_df.set_index('DeliveryMonthIndex')[money_terminals].T\n",
    "    .reindex(index=slots_df['TerminalName'].unique())   # add slot terminals missing from money -> NaN rows\n",
    "    .reindex(columns=format_cols)\n",
    "    .apply(pd.to_numeric, errors='coerce')\n",
    ")\n",
    "\n",
    "# defining colourmap with non-linear scaling: steeper colour change near zero\n",
    "rg_cmap = matplotlib.colormaps['coolwarm_r']    # chosen colourmap\n",
    "vmax = np.nanmax(np.abs(gmap_plot.values))      # finding max moneyness value to scale colourmap\n",
    "gamma = 0.5                                     # colour change rate: <1 = faster colour change near 0\n",
    "\n",
    "# defining function to create non-linear colourmap scaling\n",
    "def moneyness_color(v):\n",
    "    t = np.clip(v / vmax, -1, 1)          # linear normalise to [-1, 1]\n",
    "    t = np.sign(t) * np.abs(t) ** gamma   # bend the curve around 0\n",
    "    return rg_cmap((t + 1) / 2)           # map [-1, 1] -> [0, 1] -> colour\n",
    "\n",
    "# Building Table\n",
    "fig, ax = plt.subplots(figsize=(30, 12))\n",
    "\n",
    "# creating table columns & associated parameters\n",
    "col_defs = (\n",
    "    [ColumnDefinition(name='Country',\n",
    "                      textprops={'ha': 'left', 'weight': 'bold'},\n",
    "                      width=1.0, border='right')]\n",
    "    + [ColumnDefinition(name='TerminalName',\n",
    "                        textprops={'ha': 'left', 'weight': 'bold'},\n",
    "                        width=1.6, border='right')]\n",
    "    + [ColumnDefinition(name='Total',\n",
    "                        textprops={'ha': 'center', 'weight': 'bold'}, border='right')]\n",
    "    + [ColumnDefinition(name='M+0',\n",
    "                        textprops={'ha': 'center'}, border='right')]\n",
    "    + [ColumnDefinition(name=c, title=c, textprops={'ha': 'center'},\n",
    "                        group='Slots Moneyness - ' + date) for c in format_cols]\n",
    ")\n",
    "\n",
    "# creating table\n",
    "table = Table(\n",
    "    slots_display2[['TerminalName', 'Total', 'M+0'] + format_cols],\n",
    "    ax=ax,\n",
    "    column_definitions=col_defs,\n",
    "    row_dividers=True,\n",
    "    col_label_divider=True,\n",
    "    footer_divider=True,\n",
    "    textprops={'fontsize': 12},\n",
    "    row_divider_kw={'linewidth': 1, 'linestyle': (0, (1, 5))},\n",
    "    col_label_divider_kw={'linewidth': 1, 'linestyle': '-'},\n",
    "    column_border_kw={'linewidth': 1, 'linestyle': '-'},\n",
    ")\n",
    "\n",
    "# Styling cells: bold non-zero counts & colour M+1..M+12 by moneyness\n",
    "color_cols = set(format_cols)\n",
    "for c in ['Total', 'M+0'] + format_cols:\n",
    "    for cell in table.columns[c].cells:\n",
    "        # bold any non-zero slot count\n",
    "        if isinstance(cell.content, (int, float, np.integer, np.floating)) and cell.content != 0:\n",
    "            cell.text.set_fontweight('bold')\n",
    "        # colour the delivery-month cells from the moneyness gmap\n",
    "        if c in color_cols:\n",
    "            val = gmap_plot.loc[slots_display2['TerminalName'].iloc[cell.row_idx], c]\n",
    "            if pd.notna(val):\n",
    "                cell.rectangle_patch.set_facecolor(moneyness_color(val))\n",
    "\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": 2
}