{ "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": {}, "outputs": [], "source": [ "print('Enter your XBRL US Web account email: ')\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", "# this pip installs package to support plotting data in this notebook when it's opened from the Web using Chrome or Firefox\n", "%pip install matplotlib -q\n", "import matplotlib.pyplot as plt\n", "import matplotlib.dates as mdates\n", "email = input()\n", "password = getpass.getpass(prompt='Password: ')\n", "clientid = getpass.getpass(prompt='Client ID: ')\n", "secret = getpass.getpass(prompt='Secret: ')\n", "\n", "body_auth = {'username' : ''.join(email), \n", " 'client_id': ''.join(clientid), \n", " 'client_secret' : ''.join(secret), \n", " 'password' : ''.join(password), \n", " 'grant_type' : 'password', \n", " 'platform' : 'ipynb' }\n", "\n", "payload = urlencode(body_auth)\n", "url = 'https://api.xbrl.us/oauth2/token'\n", "headers = {\"Content-Type\": \"application/x-www-form-urlencoded\"}\n", "\n", "res = requests.request(\"POST\", url, data=payload, headers=headers)\n", "auth_json = res.json()\n", "\n", "if 'error' in auth_json:\n", " print (\"\\n\\nThere was a problem generating an access token with these credentials. Run the first cell again to enter credentials.\")\n", "else:\n", " print (\"\\n\\nYour access token expires in 60 minutes. After it expires, run the cell immediately below this one to generate a new token and continue to use the query cell. \\n\\nFor now, skip ahead to the section 'Make a Query'.\")\n", "\n", "access_token = auth_json['access_token']\n", "refresh_token = auth_json['refresh_token']\n", "newaccess = ''\n", "newrefresh = ''\n", "#print('access token: ' + access_token + ' refresh token: ' + refresh_token)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Refresh token \n", "The cell below is only needed to refresh an expired access token after 60 minutes. When the access token no longer returns results, run the cell below to refresh the access token or re-enter credentials by running the cell above. Until the refresh token process is needed, **skip ahead to _Make a Query_**. \n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "token = token if newrefresh != '' else refresh_token \n", "\n", "refresh_auth = {'client_id': ''.join(clientid), \n", " 'client_secret' : ''.join(secret), \n", " 'grant_type' : 'refresh_token', \n", " 'platform' : 'ipynb', \n", " 'refresh_token' : ''.join(token) }\n", "refreshres = requests.post(url, data=refresh_auth)\n", "refresh_json = refreshres.json()\n", "access_token = refresh_json['access_token']\n", "refresh_token = refresh_json['refresh_token']#print('access token: ' + access_token + 'refresh token: ' + refresh_token)\n", "print('Your access token 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.')\n", "print(access_token)" ] }, { "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": [ { "name": "stdout", "output_type": "stream", "text": [ "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", " - this set contained fewer than the 5000 possible, only 4562 records.\n", "\n", "At Fri Dec 1 22:15:23 2023, the query finished with 34562 rows returned in 0:00:09.100299 for \n", "https://api.xbrl.us/api/v1/fact/search?report.document-type=714&concept.local-name=PlanningAreaHourlyDemandMegawatts&entity.code=C000038,C000029&period.fiscal-year=2021,2022&fact.ultimus=TRUE&fields=entity.name.sort(ASC),entity.code,period.instant.sort(ASC),fact.value,period.fiscal-year,concept-local-name,report.html-url,fact.offset(30000)\n" ] }, { "data": { "text/html": [ "
\n", " | entity.name | \n", "entity.code | \n", "period.instant | \n", "fact.value | \n", "period.fiscal-year | \n", "report.html-url | \n", "
---|---|---|---|---|---|---|
0 | \n", "ISO New England Inc. | \n", "C000029 | \n", "2021-01-11 00:00:00 | \n", "12,710.00 | \n", "2021 | \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-11 01:00:00 | \n", "12,184.00 | \n", "2021 | \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-11 02:00:00 | \n", "11,906.00 | \n", "2021 | \n", "https://ecollection.ferc.gov/api/DownloadDocument/148344/3?filename=XBRL_4_48_20220601073855_92735.html | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
34559 | \n", "New York Independent System Operator - Regulatory Affairs | \n", "C000038 | \n", "2022-12-31 22:00:00 | \n", "16,020.00 | \n", "2022 | \n", "https://ecollection.ferc.gov/api/DownloadDocument/178946/3?filename=NYISO-20221231_112044.html | \n", "
34560 | \n", "New York Independent System Operator - Regulatory Affairs | \n", "C000038 | \n", "2022-12-31 23:00:00 | \n", "15,440.00 | \n", "2022 | \n", "https://ecollection.ferc.gov/api/DownloadDocument/178946/3?filename=NYISO-20221231_112044.html | \n", "
34561 | \n", "New York Independent System Operator - Regulatory Affairs | \n", "C000038 | \n", "2023-01-01 00:00:00 | \n", "14,851.00 | \n", "2022 | \n", "https://ecollection.ferc.gov/api/DownloadDocument/178946/3?filename=NYISO-20221231_112044.html | \n", "