{ "cells": [ { "cell_type": "markdown", "id": "59d19f73", "metadata": {}, "source": [ "# Spark API - Global Route Cost Comparison\n", "\n", "This script compares route costs between global load and discharge ports. \n", "\n", "This script uses elements from our API code samples. If you'd like a more basic and informative example of how to pull data via the Spark API, please visit our Github or API website:\n", "\n", "- Github: https://github.com/spark-commodities/api-code-samples/blob/master/jupyter_notebooks/\n", "- API Website: https://www.sparkcommodities.com/api/code-examples/jupyter.html\n", "\n", "\n", "### 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:\n", "https://www.sparkcommodities.com/api/lng-freight/routes.html\n", "\n", "__N.B. This script requires a Freight subscription__\n" ] }, { "cell_type": "markdown", "id": "9e00ae34", "metadata": {}, "source": [ "## 1. Importing Data" ] }, { "cell_type": "code", "execution_count": null, "id": "d9ea2c58", "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 pprint import pprint\n", "from urllib.parse import urljoin\n", "import datetime\n", "from io import StringIO\n", "\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", "\n", "\n", "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", " print(\n", " \">>>> Client_id={}, client_secret={}****\".format(client_id, client_secret[:5])\n", " )\n", "\n", " return client_id, client_secret\n", "\n", "\n", "def do_api_post_query(uri, body, headers):\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", " \"\"\"\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", " # 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", " #status = response.status\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` 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\": \"Basic {}\".format(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\"]\n", "\n", "\n", "\n", "def list_routes(access_token):\n", " \"\"\"\n", " Fetch available routes. Return contract ticker symbols\n", "\n", " # Procedure:\n", "\n", " Do a GET query to /v1.0/routes/ with a Bearer token authorization HTTP header.\n", " \"\"\"\n", " content = do_api_get_query(uri=\"/v1.0/routes/\", access_token=access_token)\n", "\n", " print(\">>>> All the routes you can fetch\")\n", " tickers = []\n", " for contract in content[\"data\"]['routes']:\n", " tickers.append(contract[\"uuid\"])\n", " \n", " reldates = content[\"data\"]['sparkReleaseDates']\n", " \n", " dicto1 = content[\"data\"]\n", " \n", " return tickers, reldates, dicto1\n", "\n" ] }, { "cell_type": "markdown", "id": "1e890e9e", "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." ] }, { "cell_type": "code", "execution_count": null, "id": "51b8a89c", "metadata": { "scrolled": false }, "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)\n", "print(access_token)\n", "\n", "# Fetch all contracts:\n", "tickers, reldates, dicto1 = list_routes(access_token)" ] }, { "cell_type": "markdown", "id": "e0b82541", "metadata": {}, "source": [ "## Fetching Routes Reference Data\n", "\n", "Fetches information on all available routes and their associated tickers" ] }, { "cell_type": "code", "execution_count": null, "id": "912d9c4f", "metadata": {}, "outputs": [], "source": [ "## Fetching all routes \n", "\n", "def fetch_route_data(access_token, ticker, release, congestion=None):\n", " \"\"\"\n", " For a route, fetch then display the route details\n", "\n", " # Procedure:\n", "\n", " Do GET queries to https://api.sparkcommodities.com/v1.0/routes/{route_uuid}/\n", " with a Bearer token authorization HTTP header.\n", " \"\"\"\n", "\n", " query_params = \"?release-date={}\".format(release)\n", " if congestion is not None:\n", " query_params += \"&congestion-days={}\".format(congestion)\n", "\n", " uri = \"/v1.0/routes/{}/{}\".format(ticker, query_params)\n", " #print(uri)\n", "\n", " content = do_api_get_query(\n", " uri=\"/v1.0/routes/{}/{}\".format(ticker, query_params),\n", " access_token=access_token,\n", " )\n", "\n", " my_dict = content[\"data\"]\n", "\n", " return my_dict\n" ] }, { "cell_type": "markdown", "id": "899d4cae", "metadata": {}, "source": [ "### Routes Reference Table\n", "\n", "Sorting all the data from the saved dictionary into an easy-to-read Pandas DataFrame" ] }, { "cell_type": "code", "execution_count": null, "id": "b0ba8777", "metadata": {}, "outputs": [], "source": [ "primary_uuid =[]\n", "load_uuid = []\n", "discharge_uuid = []\n", "load_port = []\n", "discharge_port = []\n", "via_list = []\n", "load_region = []\n", "discharge_region = []\n", "\n", "for route in dicto1['routes']:\n", " primary_uuid.append(route['uuid'])\n", " via_list.append(route['via'])\n", " \n", " load_uuid.append(route['loadPort']['uuid'])\n", " load_port.append(route['loadPort']['name'])\n", " load_region.append(route['loadPort']['region'])\n", " \n", " discharge_uuid.append(route['dischargePort']['uuid'])\n", " discharge_port.append(route['dischargePort']['name'])\n", " discharge_region.append(route['dischargePort']['region'])\n" ] }, { "cell_type": "code", "execution_count": null, "id": "704321f1", "metadata": { "scrolled": true }, "outputs": [], "source": [ "import pandas as pd\n", "\n", "route_df = pd.DataFrame({\n", " 'UUID': primary_uuid,\n", " 'Load Location': load_port,\n", " 'Discharge Location': discharge_port,\n", " 'Via': via_list,\n", " 'Load Region': load_region,\n", " 'Discharge Region': discharge_region,\n", " 'Load UUID': load_uuid,\n", " 'Discharge UUID': discharge_uuid\n", "})\n", "\n", "route_df.head()" ] }, { "cell_type": "markdown", "id": "63ede98d", "metadata": {}, "source": [ "# Load & Discharge Port Inputs\n", "\n", "Input the list of Load and Discharge ports you'd like to include in the table. Cross reference with the Routes Reference table above to make sure the route combinations you input are assessed by Spark.\n", "\n", "__Looking for a a Route Cost we don't currently provide?__ Contact us at info@sparkcommodities.com to request any new routes." ] }, { "cell_type": "code", "execution_count": null, "id": "7e8590b3", "metadata": {}, "outputs": [], "source": [ "# Defining which ports to use in our table\n", "my_loads = ['Sabine Pass', 'Atlantic LNG', 'Bethioua', 'Hammerfest', 'Bonny LNG','Yamal', 'Ras Laffan', 'Bintulu','Gorgon', 'GLNG']\n", "my_discharges = ['Bahia','Gate','Swinoujscie','Fos Cavaou','Rovigo','Dahej','Rayong','Dapeng','Yung An','Tianjin','Incheon','Futtsu']\n" ] }, { "cell_type": "markdown", "id": "c3c0279d", "metadata": {}, "source": [ "### Defining Function to pull data and save as Pandas DataFrame\n", "\n", "The function takes 4 parameters:\n", "- __loads:__ the list of Load Ports you'd like to use in your table\n", "- __discharges:__ the list of Discharge Ports you'd like to use in your table\n", "- __month:__ which month's prices you'd like to retrieve (0= spot, 1=M+1, etc.)\n", "- __release:__ which published price release you'd like to use. Default is 'latest', which calls the latest route costs. Input a date (e.g. '2025-04-03') to call historical prices" ] }, { "cell_type": "code", "execution_count": null, "id": "94913c92", "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "\n", "def routes_table(loads,discharges, month, release='latest'):\n", " # initialising DataFrame\n", " table_df = pd.DataFrame(columns=discharges)\n", " table_df['FoB Ports'] = loads\n", " table_df = table_df.set_index('FoB Ports')\n", "\n", " # Iterating through load, discharge combinations and retrieving relevant data\n", " for l in loads:\n", " for d in discharges:\n", " a = route_df[(route_df['Load Location'] == l)\\\n", " & (route_df['Discharge Location'] == d)]\n", "\n", " # retrieving the route UUID, making sure the via point is COGH where applicable\n", " try:\n", " vias = a['Via'].to_list()\n", " if len(a) == 1:\n", " tick = a['UUID'].iloc[0]\n", " else: \n", " if 'cogh' in vias:\n", " tick = a[a['Via'] == 'cogh']['UUID'].iloc[0]\n", " else:\n", " tick = a[(a['Via'] != 'panama') & \\\n", " (a['Via'] != 'suez') & \\\n", " (a['Via'] != 'magellan-straits')]['UUID'].iloc[0]\n", " \n", " # calling route data with UUID\n", " if release == 'latest':\n", " my_dict = fetch_route_data(access_token, tick, release=reldates[0])\n", " else:\n", " my_dict = fetch_route_data(access_token, tick, release=release)\n", "\n", " table_df.loc[l,d] = float(my_dict['dataPoints'][month]['costsInUsdPerMmbtu']['total'])\n", " \n", " except:\n", " table_df.loc[l,d] = np.nan\n", " \n", " return table_df" ] }, { "cell_type": "markdown", "id": "88add60b", "metadata": {}, "source": [ "## Calling the Data\n", "\n", "As an example, here we call the latest spot route costs for our chosen Load and Discharge ports." ] }, { "cell_type": "code", "execution_count": null, "id": "07c23292", "metadata": {}, "outputs": [], "source": [ "table_df = routes_table(my_loads,my_discharges, month=0, release = 'latest')\n", "table_df" ] }, { "cell_type": "markdown", "id": "6ae1e620", "metadata": {}, "source": [ "# Optional Formatting using 'plottable'\n", "\n", "This section uses the Python package 'plottable' to apply style formatting to our Routes table. '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. Remove the {\"\"\"} at the beginning/end of the code to uncomment the code and run this section.\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, "id": "c5376282", "metadata": {}, "outputs": [], "source": [ "\"\"\"\n", "\n", "import matplotlib.pyplot as plt\n", "from plottable import ColumnDefinition, Table\n", "from plottable.cmap import normed_cmap\n", "import matplotlib\n", "\n", "# retrieving the columns ranked by standard deviation, and choosing one to use for the colourmap scaling\n", "ci = list(table_df.std().sort_values().index)[-2]\n", "\n", "# initiating figure\n", "fig, ax = plt.subplots(figsize=(20, 8))\n", "\n", "\n", "# defining columns of the table\n", "col_defs = (\n", " [\n", " ColumnDefinition(\n", " name=\"FoB Ports\",\n", " textprops={\"ha\": \"left\", \"weight\": \"bold\"},\n", " width=1.5,\n", " border='right',\n", " )\n", " ]\n", " + [\n", " ColumnDefinition(\n", " name=col,\n", " title=col,\n", " cmap=normed_cmap(table_df[ci], cmap=matplotlib.cm.coolwarm, num_stds=2.5),\n", " textprops={\"ha\": \"center\"},\n", " group=\"Discharge Locations\",\n", " )\n", " for col in my_discharges\n", " ])\n", "\n", "# creating table\n", "table = Table(\n", " table_df,\n", " row_dividers=True,\n", " col_label_divider=True,\n", " column_definitions=col_defs,\n", " footer_divider=True,\n", " ax=ax,\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", "plt.show()\n", "\n", "\"\"\"" ] } ], "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.11.5" } }, "nbformat": 4, "nbformat_minor": 5 }