{ "cells": [ { "attachments": {}, "cell_type": "markdown", "metadata": {}, "source": [ "## XBRL US API - FERC schedule by entity \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": [ "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 from 10+ years of data for companies filing data on the _Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion_ , and may take several minutes to recreate. \n", "\n", "Modify the **_XBRL\\_Elements_** to return data from a different FERC Form 1 Schedule, and/or change the **_entity\\_codes_** to shorten the list. You can also change the base taxonomy corresponding to the Form by year or Form type. The queries to get lists are in the commented lines above each variable.\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": [ "On Mon Nov 18 16:45:12 2024 info@xbrl.us (client ID: 69e1257c ...) started the query and\n", "up to 5000 records are found so far ...\n", " - this set contained fewer than the 5000 possible, only 4173 records.\n", "\n", "At Mon Nov 18 16:45:28 2024, the query finished with 4173 rows returned in 0:00:15.380693 for \n", "https://api.xbrl.us/api/v1/cube/search?unique&cube.description=200+-+Schedule+-+Summary+of+Utility+Plant+and+Accumulated+Provisions+for+Depreciation,+Amortization+and+Depletion&entity.code=C000533,C001111&fields=period.fiscal-year.sort(DESC),period.fiscal-period.sort(DESC),entity.code,report.entity-name,report.id,cube.description.sort(ASC),cube.tree-sequence.sort(ASC),cube.primary-local-name,fact.value,unit,dimensions.count,dimension-pair.sort(ASC)\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
period.fiscal-yearperiod.fiscal-periodentity.codereport.entity-namereport.idcube.descriptioncube.tree-sequencecube.primary-local-namefact.valueunitdimensions.countdimension-pair
020242QC000533Kentucky Power Company753929200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion3UtilityPlantInServiceClassified3,285,598,514.00USD0
120242QC000533Kentucky Power Company753929200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion3UtilityPlantInServiceClassified3,285,598,514.00USD1[{'UtilityTypeAxis': 'ElectricUtilityMember'}]
220242QC001111Baltimore Gas and Electric Company753841200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion3UtilityPlantInServiceClassified10,068,795,285.00USD1[{'UtilityTypeAxis': 'ElectricUtilityMember'}]
320242QC001111Baltimore Gas and Electric Company753841200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion3UtilityPlantInServiceClassified1,244,861,502.00USD1[{'UtilityTypeAxis': 'CommonUtilityMember'}]
420242QC001111Baltimore Gas and Electric Company753841200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion3UtilityPlantInServiceClassified15,404,286,687.00USD0
.......................................
41682010YC001111Baltimore Gas and Electric Company418249200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion16UtilityPlantNet4,541,951,837.00USD0
41692010YC000533Kentucky Power Company420966200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion34AccumulatedProvisionForDepreciationAmortizationAndDepletionOfPlantUtility568,441,518.00USD0
41702010YC000533Kentucky Power Company420967200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion34AccumulatedProvisionForDepreciationAmortizationAndDepletionOfPlantUtility568,441,518.00USD0
41712010YC001111Baltimore Gas and Electric Company418248200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion34AccumulatedProvisionForDepreciationAmortizationAndDepletionOfPlantUtility2,659,775,623.00USD0
41722010YC001111Baltimore Gas and Electric Company418249200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion34AccumulatedProvisionForDepreciationAmortizationAndDepletionOfPlantUtility2,659,775,623.00USD0
" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Define the parameters for the filter and fields to be returned, \n", "# run the loop to return results\n", "\n", "endpoint = 'cube'\n", "\n", "# Define the parameters of the query\n", "\n", "# query all 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 1 Taxonomy\n", "# https://api.xbrl.us/api/v1/dts/730705/network/search?network.link-name=presentationLink&fields=network.role-description.sort(ASC),dts.id&unique\n", "\n", "XBRL_Elements = [\"200 - Schedule - Summary of Utility Plant and Accumulated Provisions for Depreciation, Amortization and Depletion\"]\n", "\n", "# query for a list of Form 1 filer entity codes:\n", "# https://api.xbrl.us/api/v1/report/search?report.document-type=1&fields=report.entity-name.sort(ASC),entity.code&unique\n", "entity_codes = [\n", "'C000533','C001111' \n", "]\n", "\n", "# Define data fields to return (multi-sort based on order)\n", "\n", "fields = [ # this is the list of the characteristics of the data being returned by the query\n", "\t\t'period.fiscal-year.sort(DESC)',\n", "\t\t'period.fiscal-period.sort(DESC)',\n", "\t\t'entity.code',\n", "\t\t'report.entity-name',\n", "\t\t'report.id',\n", "\t\t'cube.description.sort(ASC)',\n", "\t\t'cube.tree-sequence.sort(ASC)',\n", "\t\t'cube.primary-local-name',\n", "\t\t'fact.value',\n", "\t\t'unit',\n", "\t\t'dimensions.count',\n", "\t\t'dimension-pair.sort(ASC)'\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", " 'cube.description': ','.join(XBRL_Elements), \n", " 'entity.code': ','.join(entity_codes),\n", " # uncomment this line to run by report.id AND comment the prior line for entity.code 'report.id': ','.join(full_list),\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", "\n", "while True:\n", " if not printed:\n", " print(\"On\", query_start.strftime(\"%c\"), tokenInfo.email, \"(client ID:\", str(tokenInfo.client_id.split('-')[0]), \"...) started the query and\")\n", " printed = True\n", " retry = 0\n", " while retry < 3:\n", " res = requests.get(search_endpoint, params=params, headers={'Authorization' : 'Bearer {}'.format(tokenInfo.access_token)})\n", " res_json = res.json()\n", " if 'error' in res_json:\n", " if res_json['error_description'] == 'Bad or expired token':\n", " tokenInfo = refresh(tokenInfo)\n", " else: \n", " print('There was an error: {}'.format(res_json['error_description']))\n", " run_query = False\n", " break\n", " else: \n", "\t\t break\n", " retry +=1\n", " if retry >= 3:\n", " print(\"Can't refresh the access token. Run the first query block, then rerun the query.\")\n", " run_query = False\n", "\n", " if not run_query:\n", " break\n", "\n", " print(\"up to\", str(offset_value + res_json['paging']['limit']), \"records are found so far ...\")\n", "\n", " res_df += res_json['data']\n", "\n", " if res_json['paging']['count'] < res_json['paging']['limit']:\n", " print(\" - this set contained fewer than the\", res_json['paging']['limit'], \"possible, only\", str(res_json['paging']['count']), \"records.\")\n", " break\n", " else: \n", " offset_value += res_json['paging']['limit'] \n", " if 100 == res_json['paging']['limit']:\n", " params['fields'] = orig_fields + ',' + endpoint + '.offset({})'.format(offset_value)\n", " if offset_value == 10 * res_json['paging']['limit']:\n", " break \n", " elif 500 == res_json['paging']['limit']:\n", " params['fields'] = orig_fields + ',' + endpoint + '.offset({})'.format(offset_value)\n", " if offset_value == 4 * res_json['paging']['limit']:\n", " break \n", " params['fields'] = orig_fields + ',' + endpoint + '.offset({})'.format(offset_value)\n", "\n", "if not 'error' in res_json:\n", " current_datetime = datetime.now().replace(microsecond=0)\n", " time_taken = current_datetime - query_start\n", " index = pd.DataFrame(res_df).index\n", " total_rows = len(index)\n", " your_limit = res_json['paging']['limit']\n", " limit_message = \"If the results below match the limit noted above, you might not be seeing all rows, and should consider upgrading (https://xbrl.us/access-token).\\n\"\n", " \n", " if your_limit == 100:\n", " print(\"\\nThis non-Member account has a limit of \" , 10 * your_limit, \" rows per query from our Public Filings Database. \" + limit_message)\n", " elif your_limit == 500:\n", " print(\"\\nThis Basic Individual Member account has a limit of \", 4 * your_limit, \" rows per query from our Public Filings Database. \" + limit_message)\n", " \n", " print(\"\\nAt \" + current_datetime.strftime(\"%c\") + \", the query finished with \", str(total_rows), \" rows returned in \" + str(time_taken) + \" for \\n\" + urllib.parse.unquote(res.url))\n", " \n", " df = pd.DataFrame(res_df)\n", " # the format truncates the HTML display of numerical values to two decimals; .csv data is unaffected\n", " pd.options.display.float_format = '{:,.2f}'.format\n", " display(HTML(df.to_html(max_rows=rows_to_display)))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# If you run this program locally, you can save the output to a file on your computer (modify D:\\results.csv to your system)\n", "df.to_csv(r\"D:\\results.csv\",sep=\",\")\n", "\n", "# Google Colab users - comment out the line above and uncomment the code below to save the data frame as a .csv in your Google Drive\n", "\n", "#from google.colab import drive\n", "#drive.mount('drive')\n", "#df.to_csv('data.csv')\n", "#!cp data.csv \"drive/My Drive/\"" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "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": "" } }, "nbformat": 4, "nbformat_minor": 2 }