{ "cells": [ { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## XBRL US API - FERC 714 Part III - Schedule 2. Planning Area Hourly Demand\n", "\n", "### Authenticate for access token \n", "Click in the gray code cell below, then click the Run button above to execute the cell. Type your XBRL US Web account email, account password, Client ID, and secret as noted, pressing the Enter key on the keyboard after each entry.\n", "\n", "XBRL US limits records returned for a query to improve efficiency; this script loops to collect all data from the Public Filings Database for a query. **Non-members might not be able to return all data for a query** - join XBRL US for comprehensive access - https://xbrl.us/join." ] }, { "cell_type": "code", "execution_count": null, "metadata": { "cellView": "form", "tags": [ "hide-cell" ] }, "outputs": [], "source": [ "# @title\n", "import os, re, sys, json\n", "import requests\n", "import pandas as pd\n", "from IPython.display import display, HTML\n", "import numpy as np\n", "import getpass\n", "from datetime import datetime\n", "import urllib\n", "from urllib.parse import urlencode\n", "\n", "\n", "class tokenInfoClass:\n", " access_token = None\n", " refresh_token = None\n", " email = None\n", " username = None\n", " client_id = None\n", " client_secret = None\n", " url = 'https://api.xbrl.us/oauth2/token'\n", " headers = {\"Content-Type\": \"application/x-www-form-urlencoded\"}\n", "\n", "def refresh(info):\n", " refresh_auth = {\n", " 'client_id': info.client_id,\n", " 'client_secret' : info.client_secret,\n", " 'grant_type' : 'refresh_token',\n", " 'platform' : 'ipynb',\n", " 'refresh_token' : info.refresh_token\n", " }\n", " refreshres = requests.post(info.url, data=refresh_auth, headers=info.headers)\n", " refresh_json = refreshres.json()\n", " info.access_token = refresh_json.get('access_token')\n", " info.refresh_token = refresh_json.get('refresh_token')\n", " print('Your access token(%s) is refreshed for 60 minutes. If it expires again, run this cell to generate a new token and continue to use the query cells below.' % (info.access_token))\n", " return info\n", "\n", "tokenInfo = tokenInfoClass()\n", "\n", "# Helper to prompt only if value is missing\n", "def prompt_if_missing(value, prompt_text, secret=False):\n", " if value:\n", " return value\n", " if secret:\n", " return getpass.getpass(prompt=prompt_text)\n", " return input(prompt_text)\n", "\n", "# Load credentials (if .json exists)\n", "creds = {}\n", "if os.path.exists('creds.json'):\n", " try:\n", " with open('creds.json', 'r') as f:\n", " creds = json.load(f)\n", " print(\"Loaded .json\")\n", " except Exception as e:\n", " print(\"Warning: failed to read from .json:\", e)\n", "\n", "if creds:\n", " # Try nested prod object first\n", " selected = None\n", " if isinstance(creds.get('prod'), dict):\n", " selected = creds['prod']\n", " \n", " # Next, try prod-prefixed keys\n", " if not selected:\n", " selected = {}\n", " keys = ['email', 'password', 'client_id', 'client_secret']\n", " for k in keys:\n", " prefixed_key = 'prod' + k\n", " if creds.get(prefixed_key):\n", " selected[k] = creds.get(prefixed_key)\n", " # fall back to top-level key if prod variant not found\n", " elif creds.get(k):\n", " selected[k] = creds.get(k)\n", " \n", " # Verify we have all required keys\n", " if not all(selected.get(k) for k in ('email', 'password', 'client_id', 'client_secret')):\n", " # Fill in missing values from prompts\n", " selected = {\n", " 'email': selected.get('email'),\n", " 'password': selected.get('password'),\n", " 'client_id': selected.get('client_id'),\n", " 'client_secret': selected.get('client_secret')\n", " }\n", " \n", " # Assign values, prompting for any missing ones\n", " tokenInfo.email = prompt_if_missing(selected.get('email'), 'Enter your XBRL US Web account email: ')\n", " tokenInfo.password = prompt_if_missing(selected.get('password'), 'Password: ', secret=True)\n", " tokenInfo.client_id = prompt_if_missing(selected.get('client_id'), 'Client ID: ', secret=True)\n", " tokenInfo.client_secret = prompt_if_missing(selected.get('client_secret'), 'Secret: ', secret=True)\n", "\n", " print('Using credentials from .json as available.')\n", "else:\n", " # No creds.json — prompt the user\n", " tokenInfo.email = input('Enter your XBRL US Web account email: ')\n", " tokenInfo.password = getpass.getpass(prompt='Password: ')\n", " tokenInfo.client_id = getpass.getpass(prompt='Client ID: ')\n", " tokenInfo.client_secret = getpass.getpass(prompt='Secret: ')\n", "\n", "body_auth = {'username' : tokenInfo.email,\n", " 'client_id': tokenInfo.client_id,\n", " 'client_secret' : tokenInfo.client_secret,\n", " 'password' : tokenInfo.password,\n", " 'grant_type' : 'password',\n", " 'platform' : 'ipynb' }\n", "\n", "#print(body_auth)\n", "\n", "payload = urlencode(body_auth)\n", "res = requests.request(\"POST\", tokenInfo.url, data=payload, headers=tokenInfo.headers)\n", "auth_json = res.json()\n", "\n", "if 'error' in auth_json:\n", " print(\"\\n\\nThere was a problem generating the access token: %s Run the first cell again and enter the credentials.\" % (auth_json.get('error_description', auth_json)))\n", "else:\n", " tokenInfo.access_token = auth_json.get('access_token')\n", " tokenInfo.refresh_token = auth_json.get('refresh_token')\n", " if tokenInfo.access_token and tokenInfo.refresh_token:\n", " print (\"\\n\\nYour access token expires in 60 minutes. After it expires, it should be regenerated automatically. If not, run the cell rerun the first query cell. \\n\\nFor now, skip ahead to the section 'Make a Query'.\")\n", " else:\n", " print(\"\\n\\nAuthentication completed but tokens were not returned. Response: {}\".format(auth_json))\n", "\n", "#print(vars(tokenInfo))\n", "if tokenInfo.access_token and tokenInfo.refresh_token:\n", " print('\\n\\naccess token: ' + tokenInfo.access_token + ' refresh token: ' + tokenInfo.refresh_token)\n", "else:\n", " print('\\n\\nNo access token was generated. Check the messages above for errors.')" ] }, { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "### Make a query \n", "After the access token confirmation appears above, you can modify the query below and use the **_Cell >> Run_** menu option with the cell **immediately below this text** to run the query for updated results. \n", "\n", "The sample results are for one concept reported hourly for two fiscal years of data by an entity filing data on Form 714 for 2022. **To get more entities, paste the query for _a list of Form 714 filer entity codes_ (below) into a browser and login to XBRL US**, then copy value(s) as a comma-separated list into the corresponding line below.\n", " \n", "Refer to XBRL API documentation at https://xbrlus.github.io/xbrl-api/#/Facts/getFactDetails for other endpoints and parameters to filter and return. " ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "### Example Queries (these can open in a browser for quick reference)\n", "# query taxonomies published for a specific year\n", "# https://api.xbrl.us/api/v1/dts/search?fields=dts.id,dts.version.sort(DESC),dts.taxonomy-name.sort(DESC)&dts.version=2023\n", "\n", "# query unique Statements in the 2023 FERC Form 714 Taxonomy\n", "# https://api.xbrl.us/api/v1/dts/731060/network/search?network.link-name=presentationLink&fields=network.role-description.sort(ASC),dts.id&unique\n", "\n", "# query for a list of Form 714 filer entity codes:\n", "# https://api.xbrl.us/api/v1/report/search?report.document-type=714&fields=report.entity-name.sort(ASC),entity.code&unique\n", "\n", "\n", "### Define the parameters for the filter and fields to be returned\n", "\n", "# Define endpoint (common values: fact, entity, report, cube, label, concept, relationship - see https://xbrlus/github.io/xbrl-api for additional endpoint options)\n", "\n", "endpoint = 'fact'\n", "\n", "# Define parameters of the query\n", "\n", "document_types = [\n", " '714'\n", " ]\n", "\n", "concepts = [\n", " 'PlanningAreaHourlyDemandMegawatts'\n", " ]\n", "\n", "entity_codes = [\n", " 'C000030','C000029',\n", " ]\n", "\n", "report_years = [\n", " '2021','2022'\n", " ]\n", "\n", "# Define data fields to return (multi-sort based on order)\n", "fields = [ \n", "\t\t'entity.name.sort(ASC)',\n", "\t\t'entity.code',\n", "\t\t'period.instant.sort(ASC)',\n", "\t\t'fact.value',\n", "\t\t'report.year-focus',\n", "\t\t'period.fiscal-year',\n", "\t\t'concept.local-name',\n", "\t\t'report.html-url'\n", " ]\n", "\n", "# Set unique rows as True of False (True drops any duplicate rows)\n", "unique = True\n", "\n", "# Limit the number of rows displayed by the notebook (does not impact the data frame)\n", "rows_to_display = 10 # Set as '' to display all rows in the notebook\n", "\n", "# Below is the list of what's being queried using the search endpoint.\n", " \n", "params = { \n", " 'report.document-type': ','.join(document_types),\n", " 'concept.local-name': ','.join(concepts),\n", " 'entity.code': ','.join(entity_codes),\n", " 'report.year-focus': ','.join(report_years),\n", " 'fact.ultimus': 'TRUE',\n", " 'fields': ','.join(fields)\n", " }\n", "\n", "\n", "### Execute the query with loop for all results \n", "### THIS SECTION DOES NOT NEED TO BE EDITED\n", "\n", "search_endpoint = 'https://api.xbrl.us/api/v1/' + endpoint + '/search'\n", "if unique:\n", " search_endpoint += \"?unique\"\n", "orig_fields = params['fields']\n", "offset_value = 0\n", "res_df = []\n", "count = 0\n", "query_start = datetime.now()\n", "printed = False\n", "run_query = True\n", "print(\"Run the cell below to execute the query.\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "cellView": "form", "tags": [ "hide-cell" ] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "On Mon Nov 18 15:37:43 2024 info@xbrl.us (client ID: 69e1257c ...) started the query and\n", "up to 5000 records are found so far ...\n", "up to 10000 records are found so far ...\n", "up to 15000 records are found so far ...\n", "up to 20000 records are found so far ...\n", "up to 25000 records are found so far ...\n", "up to 30000 records are found so far ...\n", "up to 35000 records are found so far ...\n", "up to 40000 records are found so far ...\n", " - this set contained fewer than the 5000 possible, only 38 records.\n", "\n", "At Mon Nov 18 15:37:50 2024, the query finished with 35038 rows returned in 0:00:06.730763 for \n", "https://api.xbrl.us/api/v1/fact/search?unique&report.document-type=714&concept.local-name=PlanningAreaHourlyDemandMegawatts&entity.code=C000030,C000029&report.year-focus=2021,2022&fact.ultimus=TRUE&fields=entity.name.sort(ASC),entity.code,period.instant.sort(ASC),fact.value,report.year-focus,period.fiscal-year,concept.local-name,report.html-url,fact.offset(35000)\n" ] }, { "data": { "text/html": [ "
| \n", " | entity.name | \n", "entity.code | \n", "period.instant | \n", "fact.value | \n", "report.year-focus | \n", "period.fiscal-year | \n", "concept.local-name | \n", "report.html-url | \n", "
|---|---|---|---|---|---|---|---|---|
| 0 | \n", "ISO New England Inc. | \n", "C000029 | \n", "2021-01-01 01:00:00 | \n", "12,192.00 | \n", "2021 | \n", "2020 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/148344/3?filename=XBRL_4_48_20220601073855_92735.html | \n", "
| 1 | \n", "ISO New England Inc. | \n", "C000029 | \n", "2021-01-01 02:00:00 | \n", "11,740.00 | \n", "2021 | \n", "2020 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/148344/3?filename=XBRL_4_48_20220601073855_92735.html | \n", "
| 2 | \n", "ISO New England Inc. | \n", "C000029 | \n", "2021-01-01 03:00:00 | \n", "11,476.00 | \n", "2021 | \n", "2020 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/148344/3?filename=XBRL_4_48_20220601073855_92735.html | \n", "
| 3 | \n", "ISO New England Inc. | \n", "C000029 | \n", "2021-01-01 04:00:00 | \n", "11,399.00 | \n", "2021 | \n", "2020 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/148344/3?filename=XBRL_4_48_20220601073855_92735.html | \n", "
| 4 | \n", "ISO New England Inc. | \n", "C000029 | \n", "2021-01-01 05:00:00 | \n", "11,508.00 | \n", "2021 | \n", "2020 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/148344/3?filename=XBRL_4_48_20220601073855_92735.html | \n", "
| ... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
| 35033 | \n", "PJM Interconnection, LLC | \n", "C000030 | \n", "2022-12-31 20:00:00 | \n", "85,172.38 | \n", "2022 | \n", "2022 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/180486/3?filename=XBRL_4_83_20230612090954_112777.html | \n", "
| 35034 | \n", "PJM Interconnection, LLC | \n", "C000030 | \n", "2022-12-31 21:00:00 | \n", "82,952.54 | \n", "2022 | \n", "2022 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/180486/3?filename=XBRL_4_83_20230612090954_112777.html | \n", "
| 35035 | \n", "PJM Interconnection, LLC | \n", "C000030 | \n", "2022-12-31 22:00:00 | \n", "80,383.59 | \n", "2022 | \n", "2022 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/180486/3?filename=XBRL_4_83_20230612090954_112777.html | \n", "
| 35036 | \n", "PJM Interconnection, LLC | \n", "C000030 | \n", "2022-12-31 23:00:00 | \n", "77,960.75 | \n", "2022 | \n", "2022 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/180486/3?filename=XBRL_4_83_20230612090954_112777.html | \n", "
| 35037 | \n", "PJM Interconnection, LLC | \n", "C000030 | \n", "2023-01-01 00:00:00 | \n", "75,490.48 | \n", "2022 | \n", "2022 | \n", "PlanningAreaHourlyDemandMegawatts | \n", "https://ecollection.ferc.gov/api/DownloadDocument/180486/3?filename=XBRL_4_83_20230612090954_112777.html | \n", "