xbrl_us_api_acfr_cube.ipynb

{
	"cells": [
		{
			"attachments": {},
			"cell_type": "markdown",
			"metadata": {},
			"source": [
				"## XBRL US API - ACFR statements by report  \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",
				"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",
				"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 from a set of ACFR reports posted to the XBRL US Public Filings Database.  To test for results quickly, modify the **_report\\_ids_** to shorten the list, and change the **_XBRL\\_Elements_** to return different data from an ACFR statement.\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",
						" - this set contained fewer than the 5000 possible, only 3877 records.\n",
						"\n",
						"At Fri Feb 23 13:23:48 2024, the query finished with   3877   rows returned in 0:00:00.551165 for \n",
						"https://api.xbrl.us/api/v1/cube/search?cube.description=&report.id=677268,677267&fields=report.id,cube.description.sort(ASC),cube.tree-sequence.sort(ASC),report.entity-name,dimensions.count,dimension-pair,cube.primary-local-name,fact.value,unit&unique=\n"
					]
				},
				{
					"data": {
						"text/html": [
							"<table border=\"1\" class=\"dataframe\">\n",
							"  <thead>\n",
							"    <tr style=\"text-align: right;\">\n",
							"      <th></th>\n",
							"      <th>report.id</th>\n",
							"      <th>cube.description</th>\n",
							"      <th>cube.tree-sequence</th>\n",
							"      <th>report.entity-name</th>\n",
							"      <th>dimensions.count</th>\n",
							"      <th>dimension-pair</th>\n",
							"      <th>cube.primary-local-name</th>\n",
							"      <th>fact.value</th>\n",
							"      <th>unit</th>\n",
							"    </tr>\n",
							"  </thead>\n",
							"  <tbody>\n",
							"    <tr>\n",
							"      <th>0</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>25</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>CashAndCashEquivalents</td>\n",
							"      <td>341642278</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>1</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>35</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'BusinessTypeActivitiesMember'}]</td>\n",
							"      <td>CashAndCashEquivalentsAndInvestments</td>\n",
							"      <td>54416999</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>2</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>35</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>CashAndCashEquivalentsAndInvestments</td>\n",
							"      <td>169033296</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>35</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>CashAndCashEquivalentsAndInvestments</td>\n",
							"      <td>341642278</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>4</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>35</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'GovernmentalActivitiesMember'}]</td>\n",
							"      <td>CashAndCashEquivalentsAndInvestments</td>\n",
							"      <td>142774529</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>5</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>35</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'PrimaryGovernmentActivitiesMember'}]</td>\n",
							"      <td>CashAndCashEquivalentsAndInvestments</td>\n",
							"      <td>197191528</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>6</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>69</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'BusinessTypeActivitiesMember'}]</td>\n",
							"      <td>Receivables</td>\n",
							"      <td>136545900</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>7</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>69</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>Receivables</td>\n",
							"      <td>79212224</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>8</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>69</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'GovernmentalActivitiesMember'}]</td>\n",
							"      <td>Receivables</td>\n",
							"      <td>28888693</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>9</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>69</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'PrimaryGovernmentActivitiesMember'}]</td>\n",
							"      <td>Receivables</td>\n",
							"      <td>165434593</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>10</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>176</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'BusinessTypeActivitiesMember'}]</td>\n",
							"      <td>InternalBalance</td>\n",
							"      <td>2558565</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>11</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>176</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>InternalBalance</td>\n",
							"      <td>0</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>12</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>176</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'GovernmentalActivitiesMember'}]</td>\n",
							"      <td>InternalBalance</td>\n",
							"      <td>-2558565</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>13</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>176</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'PrimaryGovernmentActivitiesMember'}]</td>\n",
							"      <td>InternalBalance</td>\n",
							"      <td>0</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>14</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>185</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>Inventory</td>\n",
							"      <td>6623814</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>15</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>188</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>PrepaidDeposits</td>\n",
							"      <td>3898995</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>16</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>202</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'BusinessTypeActivitiesMember'}]</td>\n",
							"      <td>RestrictedCashAndInvestments</td>\n",
							"      <td>7721016</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>17</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>202</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>RestrictedCashAndInvestments</td>\n",
							"      <td>172608982</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>18</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>202</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'GovernmentalActivitiesMember'}]</td>\n",
							"      <td>RestrictedCashAndInvestments</td>\n",
							"      <td>0</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>19</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>202</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'PrimaryGovernmentActivitiesMember'}]</td>\n",
							"      <td>RestrictedCashAndInvestments</td>\n",
							"      <td>7721016</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>20</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>255</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'BusinessTypeActivitiesMember'}]</td>\n",
							"      <td>InvestmentsInJointVentures</td>\n",
							"      <td>0</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>21</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>255</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'ComponentUnitDiscretelyPresentedMember'}]</td>\n",
							"      <td>InvestmentsInJointVentures</td>\n",
							"      <td>6854571</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>22</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>255</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'GovernmentalActivitiesMember'}]</td>\n",
							"      <td>InvestmentsInJointVentures</td>\n",
							"      <td>0</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>23</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>255</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'PrimaryGovernmentActivitiesMember'}]</td>\n",
							"      <td>InvestmentsInJointVentures</td>\n",
							"      <td>0</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>24</th>\n",
							"      <td>677267</td>\n",
							"      <td>100000 - Statement - Net Position</td>\n",
							"      <td>316</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'BusinessTypeActivitiesMember'}]</td>\n",
							"      <td>AccumulatedDepreciation</td>\n",
							"      <td>-217894158</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>...</th>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"      <td>...</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3852</th>\n",
							"      <td>677268</td>\n",
							"      <td>805050 - Annual Form F65 MI - Component Unit - Statement of Operations</td>\n",
							"      <td>196</td>\n",
							"      <td>County of Ogemaw</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'TypeOfGovernmentUnitAxis': 'PrimaryGovernmentActivitiesMember'}]</td>\n",
							"      <td>NetPosition</td>\n",
							"      <td>5231988</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3853</th>\n",
							"      <td>677267</td>\n",
							"      <td>808535 - Annual Form F65 MI - Pension Plan Information and Totals</td>\n",
							"      <td>1</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>0</td>\n",
							"      <td></td>\n",
							"      <td>PensionPlanName</td>\n",
							"      <td>MERS</td>\n",
							"      <td></td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3854</th>\n",
							"      <td>677267</td>\n",
							"      <td>808535 - Annual Form F65 MI - Pension Plan Information and Totals</td>\n",
							"      <td>2</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>0</td>\n",
							"      <td></td>\n",
							"      <td>PensionExpense</td>\n",
							"      <td>40831764</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3855</th>\n",
							"      <td>677267</td>\n",
							"      <td>808535 - Annual Form F65 MI - Pension Plan Information and Totals</td>\n",
							"      <td>3</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>0</td>\n",
							"      <td></td>\n",
							"      <td>PensionEmployerContribution</td>\n",
							"      <td>28790184</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3856</th>\n",
							"      <td>677267</td>\n",
							"      <td>808535 - Annual Form F65 MI - Pension Plan Information and Totals</td>\n",
							"      <td>4</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>0</td>\n",
							"      <td></td>\n",
							"      <td>PensionActualEmployeeContribution</td>\n",
							"      <td>1286075</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3857</th>\n",
							"      <td>677267</td>\n",
							"      <td>808535 - Annual Form F65 MI - Pension Plan Information and Totals</td>\n",
							"      <td>5</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'DefinedBenefitPlanAxis': 'OtherDefinedBenefitPlanMember'}]</td>\n",
							"      <td>PensionActualRateOfReturnPriorOneYear</td>\n",
							"      <td>0.07</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3858</th>\n",
							"      <td>677267</td>\n",
							"      <td>808535 - Annual Form F65 MI - Pension Plan Information and Totals</td>\n",
							"      <td>6</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>0</td>\n",
							"      <td></td>\n",
							"      <td>PensionActuariallyDeterminedContribution</td>\n",
							"      <td>32359819</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3859</th>\n",
							"      <td>677267</td>\n",
							"      <td>808535 - Annual Form F65 MI - Pension Plan Information and Totals</td>\n",
							"      <td>8</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'DefinedBenefitPlanAxis': 'OtherDefinedBenefitPlanMember'}]</td>\n",
							"      <td>PensionFundedRatio</td>\n",
							"      <td>0.28</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3860</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>5</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>TotalPensionLiability</td>\n",
							"      <td>546154990</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3861</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>5</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>TotalPensionLiability</td>\n",
							"      <td>547291279</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3862</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>7</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>PensionActuariallyDeterminedContribution</td>\n",
							"      <td>2623955</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3863</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>8</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>PensionPlanFiduciaryNetPosition</td>\n",
							"      <td>155938389</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3864</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>8</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>PensionPlanFiduciaryNetPosition</td>\n",
							"      <td>157810344</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3865</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>11</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>PensionPlanActiveEmployees</td>\n",
							"      <td>429</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3866</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>13</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>PensionPlanRetireesAndBeneficiariesCurrentlyReceivingNormalAndDisabilityBenefits</td>\n",
							"      <td>1733</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3867</th>\n",
							"      <td>677267</td>\n",
							"      <td>808700 - Form 5572 Michigan - Pension</td>\n",
							"      <td>21</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'PensionPlanNameAxis': 'Michigan Municipal Employees' Retirement System'}]</td>\n",
							"      <td>PensionAssumedInvestmentRateOfReturn</td>\n",
							"      <td>0.07</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3868</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>6</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBPlanTotalLiability</td>\n",
							"      <td>195997848</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3869</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>6</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBPlanTotalLiability</td>\n",
							"      <td>266371398</td>\n",
							"      <td>USD</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3870</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>7</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBFundedRatio</td>\n",
							"      <td>0</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3871</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>12</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBPlanActiveEmployees</td>\n",
							"      <td>222</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3872</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>14</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBPlanRetiredEmployeesAndBeneficiaries</td>\n",
							"      <td>1273</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3873</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>21</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBAssumedLongTermRateOfReturn</td>\n",
							"      <td>0.07</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3874</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>22</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBDiscountRate</td>\n",
							"      <td>0.02</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3875</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>26</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBAssumedInflationRateNextYear</td>\n",
							"      <td>0.03</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"    <tr>\n",
							"      <th>3876</th>\n",
							"      <td>677267</td>\n",
							"      <td>808750 - Form 5572 Michigan - OPEB</td>\n",
							"      <td>27</td>\n",
							"      <td>Flint, Michigan</td>\n",
							"      <td>1</td>\n",
							"      <td>[{'OPEBPlanNameAxis': 'OPEB Plan'}]</td>\n",
							"      <td>OPEBAssumedHealthCareCostTrendRate</td>\n",
							"      <td>0.08</td>\n",
							"      <td>pure</td>\n",
							"    </tr>\n",
							"  </tbody>\n",
							"</table>"
						],
						"text/plain": [
							"<IPython.core.display.HTML object>"
						]
					},
					"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",
				"offset_value = 0\n",
				"res_df = []\n",
				"\n",
				"# Define which endpoint to use\n",
				"endpoint = 'cube' #taxonomy presentation linkbase + facts\n",
				"\n",
				"# Define the parameters of the query\n",
				"\n",
				"# query for ACFR reports, sort by year descending and name ascending\n",
				"# https://api.xbrl.us/api/v1/report/search?report.source-name=GRIP&fields=report.entity-name,report.period-focus,report.year-focus,report.filing-date,report.id,report.entry-url,report.source-name\n",
				"\n",
				"report_ids = [# ACFR report ids\n",
				"'677268',# County of Ogemaw: https://xbrlus.github.io/acfr/ixviewer/ix.html?doc=../samples/100/Ogemaw-20210930-Annual-Accounts.htm\n",
				"'677267' # Flint, Michigan: https://xbrlus.github.io/acfr/ixviewer/ix.html?doc=../samples/107/FLINTF652021.htm \n",
				"#'677269',# William Rainey Harper College: https://xbrlus.github.io/acfr/ixviewer/ix.html?doc=../samples/106/HARPER2021.htm\n",
				"#'677270',# Oakton Community College: https://xbrlus.github.io/acfr/ixviewer/ix.html?doc=../samples/77/OAKTON2021.html\n",
				"#'677271' # College of DuPage: https://xbrlus.github.io/acfr/ixviewer/ix.html?doc=../samples/82/COD2021.htm\n",
				"]\n",
				"\n",
				"# query for unique Statements in the 2022 GRIP Taxonomy\n",
				"# https://api.xbrl.us/api/v1/dts/821819/network/search?network.link-name=presentationLink&fields=network.role-description.sort(ASC),dts.id&unique\n",
				"\n",
				"XBRL_Elements = [\n",
				"\t\t# remove the Statement between the quotes to get all data for report.ids below\n",
				"\t\t#'200110 - Statement - Activities - General Revenues and Changes in Net Position'\n",
				"\t\t] \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'report.id',\n",
				"\t\t'cube.description.sort(ASC)',\n",
				"\t\t'cube.tree-sequence.sort(ASC)',\n",
				"\t\t'report.entity-name',\n",
				"\t\t'dimensions.count',\n",
				"\t\t'dimension-pair',\n",
				"\t\t'cube.primary-local-name',\n",
				"\t\t'fact.value',\n",
				"\t\t'unit'\n",
				"        ]\n",
				"\n",
				"params = { # this is the list of what's being queried against the endpoint\n",
				"         'cube.description': ','.join(XBRL_Elements),\n",
				"         'report.id': ','.join(report_ids),\n",
				"         'fields': ','.join(fields),\n",
				"         'unique': ''\n",
				"         }\n",
				"\n",
				"# Create query and loop for all results - code below does not need to be changed\n",
				"search_endpoint = 'https://api.xbrl.us/api/v1/' + endpoint + '/search'\n",
				"orig_fields = params['fields']\n",
				"\n",
				"count = 0\n",
				"query_start = datetime.now()\n",
				"printed = False\n",
				"while True:\n",
				"    if not printed:\n",
				"        printed = True\n",
				"    res = requests.get(search_endpoint, params=params, headers={'Authorization' : 'Bearer {}'.format(access_token)})\n",
				"    res_json = res.json()\n",
				"    if 'error' in res_json:\n",
				"        print('There was an error: {}'.format(res_json['error_description']))\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",
				"\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(\n",
				"\t\tmax_rows=50,\n",
				"    )))"
			]
		},
		{
			"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=\",\")"
			]
		}
	],
	"metadata": {
		"interpreter": {
			"hash": ""
		},
		"kernelspec": {
			"display_name": "",
			"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": 5
}