xbrl_us_api-Extensions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## XBRL US API - Python example  \n",
    "This notebook contains example Python code to use the XBRL US API (https://xbrl.us/home/use/xbrl-api/)    \n",
    "  \n",
    "**Made by:** [Ties de Kok](https://www.tiesdekok.com) and [Beth Blankespoor](https://foster.uw.edu/faculty-research/directory/elizabeth-blankespoor/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, re, sys, json\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Api documentation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://xbrlus.github.io/xbrl-api/#/Facts/getFactDetails"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate an access token\n",
    "The access token will expire after 60 minutes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**You have two options:**\n",
    "\n",
    "**Option 1:** obtain your own credentials\n",
    "\n",
    "1. Obtain credentials here: https://xbrl.us/home/use/xbrl-api-community/#provisioning\n",
    "\n",
    "If you use this script on your own computer I recommend using the JSON file as described in option 1a. If you are using Binder I recommend using option 1b.\n",
    "\n",
    ">**Option 1a:**\n",
    ">2. Update 'login_cred.json' with your `client_id`, `client_secret`, and `username`  \n",
    ">3. Set `CREDENTIAL_TYPE` to `LOCAL`\n",
    ">4. Input your password when asked\n",
    "\n",
    ">**Option 1b:**\n",
    ">2. Set `CREDENTIAL_TYPE` to `CLOUD`\n",
    ">3. Input your details when asked\n",
    "\n",
    "----\n",
    "\n",
    "**Option 2:** Use my temporary credentials for demo purposes only\n",
    "\n",
    "1. Set `CREDENTIAL_TYPE` to `TEMP`\n",
    "2. Input your email address when asked\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREDENTIAL_TYPE = 'TEMP'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Please type your email address here: your@email.com\n"
     ]
    }
   ],
   "source": [
    "if CREDENTIAL_TYPE == 'TEMP':\n",
    "    user_email = input(prompt=\"Please type your email address here: \")\n",
    "    access_token = requests.get('https://tdekok-xbrlapi.builtwithdark.com/gettoken?platform=aaa-{}'.format(user_email)).text.replace('\"', \"\")\n",
    "elif CREDENTIAL_TYPE in ['LOCAL', 'CLOUD']:\n",
    "    endpoint = 'https://api.xbrl.us'\n",
    "    endpoint_auth = endpoint + '/oauth2/token'\n",
    "    \n",
    "    if CREDENTIAL_TYPE == 'LOCAL':\n",
    "        with open('login_cred.json', 'r') as f:\n",
    "            login_cred = json.loads(f.read())\n",
    "            client_id = login_cred['client_id']\n",
    "            client_secret = login_cred['client_secret']\n",
    "            username = login_cred['username']\n",
    "    else:\n",
    "        client_id = input(prompt='Please input your client id here:')\n",
    "        client_secret = getpass.getpass(prompt = 'Please input your client secret here:')\n",
    "        username = input(prompt='Please input your username here:')\n",
    "        \n",
    "    password = getpass.getpass(prompt = 'Password: ')\n",
    "    \n",
    "    body_auth = {'grant_type' : 'password', \n",
    "                'client_id' : client_id, \n",
    "                'client_secret' : client_secret, \n",
    "                'username' : username,\n",
    "                'password' : password,\n",
    "                'platform' : 'uw-ipynb'}\n",
    "    res = requests.post(endpoint_auth, data=body_auth)\n",
    "    auth_json = res.json()\n",
    "    access_token = auth_json['access_token']\n",
    "else:\n",
    "    print('Invalid credential type! Use TEMP, LOCAL, or CLOUD. See the instructions above.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make a query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define the fields you would like returned"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "fields = ['entity.cik',\n",
    "         'entity.name.sort(ASC)',\n",
    "         'dts.id',\n",
    "         'fact.id',\n",
    "         'report.filing-date',\n",
    "         'period.fiscal-year',\n",
    "         'period.instant',\n",
    "         'report.document-type',\n",
    "         'concept.local-name',\n",
    "         'dimensions.count',\n",
    "         'dimension.local-name.sort(ASC)',\n",
    "         'member.local-name',\n",
    "         'fact.value',\n",
    "         'unit',\n",
    "         'fact.decimals',\n",
    "          'fact.ultimus-index',\n",
    "         'dimension.namespace',\n",
    "         'member.namespace',\n",
    "          'fact.has-dimensions'\n",
    "         ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define the keywords you'd like to search for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "Keyword_List = [\n",
    "    'intangible'\n",
    "                ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define the companies you'd like to get extensions for"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "companies_cik = ['0000789019', ## Microsoft (MSFT)\n",
    "                 '0001018724', ## Amazon (AMZN)\n",
    "                 '0001652044', ## Alphabet (GOOG)\n",
    "                 '0001326801', ## Facebook (FB)\n",
    "                 '0001065280', ## Netflix (NFLX)\n",
    "                 '0001166691', ## Comcast (CMCSA)\n",
    "                 '0001326380', ## GameStop (GME)\n",
    "                 '0000732717', ## AT&T (T)\n",
    "                ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define the years you'd like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = ['2019'] ## Use commas between for multiple years, e.g., '2018','2019'\n",
    "#years = [str(2013 + i) for i in range(8)] ## Years 2013 to 2020"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Specify if you want dimensions, no dimension, or all values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "has_dimensions = 'FALSE'  ## TRUE for require dimensions, FALSE for no dimensions, ALL for all values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Specify the report types that you want"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "report_types = ['10-K', '10-K/A']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Execute query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "search_endpoint = 'https://api.xbrl.us/api/v1/fact/search'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting the data for: \"fact.has-dimensions\" = FALSE\n",
      "Number of records retrieved:  299\n",
      "\n",
      "Number of results that meet the criteria: 14\n"
     ]
    }
   ],
   "source": [
    "params = {'concept.is-base': 'FALSE',\n",
    "     'period.fiscal-period': 'Y',\n",
    "     'period.fiscal-year': ','.join(years),\n",
    "     'unit': 'USD',\n",
    "     'entity.cik': ','.join(companies_cik),\n",
    "     'report.document-type': ','.join(report_types),\n",
    "     }  \n",
    "if has_dimensions == 'ALL':\n",
    "    dimension_options = ['TRUE', 'FALSE']\n",
    "else:\n",
    "    dimension_options = [has_dimensions]\n",
    "        \n",
    "all_res_list = []\n",
    "for dimensions_param in dimension_options:\n",
    "    print('Getting the data for: \"fact.has-dimensions\" = {}'.format(dimensions_param))\n",
    "    ### Every request will return a max of 2000 results. So we loop until all results are retrieved. \n",
    "    done_retrieving_all_results = False\n",
    "    offset = 0\n",
    "    while not done_retrieving_all_results:\n",
    "        params['fact.has-dimensions'] = dimensions_param\n",
    "        params['fields'] = ','.join(fields) + ',fact.offset({})'.format(offset) \n",
    "        res = requests.get(search_endpoint, params=params, headers={'Authorization' : 'Bearer {}'.format(access_token)})\n",
    "        \n",
    "        ## Interpret as JSON\n",
    "        res_json = res.json()\n",
    "            \n",
    "        ## Get the results\n",
    "        ### Retrieve the data list\n",
    "        res_list = res_json['data']\n",
    "        \n",
    "        ### Keep only the tags with keyword matches\n",
    "        result_to_keep = []\n",
    "        for result in res_list:\n",
    "            for keyword in Keyword_List:\n",
    "                if keyword.lower() in result['concept.local-name'].lower():\n",
    "                    result_to_keep.append(result)\n",
    "        res_list = result_to_keep\n",
    "        \n",
    "        ### Add to the results\n",
    "        all_res_list += res_list\n",
    "        \n",
    "        ## Pagination check\n",
    "        paging_dict = res_json['paging']\n",
    "        print('Number of records retrieved: ', paging_dict['count'])\n",
    "        if paging_dict['count'] >= 2000:\n",
    "            offset += paging_dict['count']\n",
    "        else:\n",
    "            done_retrieving_all_results = True\n",
    "    \n",
    "## Convert to a DataFrame\n",
    "res_df = pd.DataFrame(all_res_list)\n",
    "\n",
    "print('\\nNumber of results that meet the criteria: {}'.format(len(res_df)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Show results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns_to_hide = ['entity.cik', 'fact.id', 'fact.decimals', 'dimension.namespace', 'member.namespace']\n",
    "columns_to_show = [column for column in res_df.columns if column not in columns_to_hide]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept.local-name</th>\n",
       "      <th>dimension.local-name</th>\n",
       "      <th>dimensions.count</th>\n",
       "      <th>dts.id</th>\n",
       "      <th>entity.name</th>\n",
       "      <th>fact.has-dimensions</th>\n",
       "      <th>fact.ultimus-index</th>\n",
       "      <th>fact.value</th>\n",
       "      <th>member.local-name</th>\n",
       "      <th>period.fiscal-year</th>\n",
       "      <th>period.instant</th>\n",
       "      <th>report.document-type</th>\n",
       "      <th>report.filing-date</th>\n",
       "      <th>unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DeferredTaxLiabilitiesIndefiniteLivedIntangibles</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>365998</td>\n",
       "      <td>AT&amp;T INC.</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>17355000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-02-20</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AcquisitionsNetOfCashAcquiredAndPurchasesOfInt...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>362758</td>\n",
       "      <td>Alphabet Inc.</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2515000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>None</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AmortizationAndImpairmentOfIntangibleAssets</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>362758</td>\n",
       "      <td>Alphabet Inc.</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>925000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>None</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>DeferredTaxLiabilitiesPropertyPlantAndEquipmen...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>362298</td>\n",
       "      <td>COMCAST CORPORATION</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>29387000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PaymentsToAcquireBusinessesNetOfCashAcquiredAn...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>362131</td>\n",
       "      <td>Facebook, Inc.</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>508000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>None</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-01-30</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>IndefiniteandFiniteLivedIntangibleAssetsGross</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>322837</td>\n",
       "      <td>GameStop Corp.</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>133100000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-02-03</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>IndefiniteandFiniteLivedIntangibleAssetsNetCar...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>322837</td>\n",
       "      <td>GameStop Corp.</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>33500000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-02-03</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>DeferredTaxAssetsIntangibleAssets</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>374878</td>\n",
       "      <td>GameStop Corp.</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>21800000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-02-03</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IndefiniteandFiniteLivedIntangibleAssetsGross</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>374878</td>\n",
       "      <td>GameStop Corp.</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>133100000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-02-03</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>IndefiniteandFiniteLivedIntangibleAssetsNetCar...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>374878</td>\n",
       "      <td>GameStop Corp.</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>33500000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-02-03</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AcquisitionsNetOfCashAcquiredAndPurchasesOfInt...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>334516</td>\n",
       "      <td>MICROSOFT CORPORATION</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>2388000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>None</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DeferredTaxLiabilitiesDeferredGlobalIntangible...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>334516</td>\n",
       "      <td>MICROSOFT CORPORATION</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>2607000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AcquisitionsNetOfCashAcquiredAndPurchasesOfInt...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>400915</td>\n",
       "      <td>MICROSOFT CORPORATION</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2388000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>None</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>DeferredTaxLiabilitiesDeferredGlobalIntangible...</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>400915</td>\n",
       "      <td>MICROSOFT CORPORATION</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2607000000</td>\n",
       "      <td></td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>10-K</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   concept.local-name dimension.local-name  \\\n",
       "2    DeferredTaxLiabilitiesIndefiniteLivedIntangibles                        \n",
       "1   AcquisitionsNetOfCashAcquiredAndPurchasesOfInt...                        \n",
       "0         AmortizationAndImpairmentOfIntangibleAssets                        \n",
       "3   DeferredTaxLiabilitiesPropertyPlantAndEquipmen...                        \n",
       "4   PaymentsToAcquireBusinessesNetOfCashAcquiredAn...                        \n",
       "9       IndefiniteandFiniteLivedIntangibleAssetsGross                        \n",
       "7   IndefiniteandFiniteLivedIntangibleAssetsNetCar...                        \n",
       "6                   DeferredTaxAssetsIntangibleAssets                        \n",
       "5       IndefiniteandFiniteLivedIntangibleAssetsGross                        \n",
       "8   IndefiniteandFiniteLivedIntangibleAssetsNetCar...                        \n",
       "10  AcquisitionsNetOfCashAcquiredAndPurchasesOfInt...                        \n",
       "12  DeferredTaxLiabilitiesDeferredGlobalIntangible...                        \n",
       "11  AcquisitionsNetOfCashAcquiredAndPurchasesOfInt...                        \n",
       "13  DeferredTaxLiabilitiesDeferredGlobalIntangible...                        \n",
       "\n",
       "    dimensions.count  dts.id            entity.name  fact.has-dimensions  \\\n",
       "2                  0  365998              AT&T INC.                False   \n",
       "1                  0  362758          Alphabet Inc.                False   \n",
       "0                  0  362758          Alphabet Inc.                False   \n",
       "3                  0  362298    COMCAST CORPORATION                False   \n",
       "4                  0  362131         Facebook, Inc.                False   \n",
       "9                  0  322837         GameStop Corp.                False   \n",
       "7                  0  322837         GameStop Corp.                False   \n",
       "6                  0  374878         GameStop Corp.                False   \n",
       "5                  0  374878         GameStop Corp.                False   \n",
       "8                  0  374878         GameStop Corp.                False   \n",
       "10                 0  334516  MICROSOFT CORPORATION                False   \n",
       "12                 0  334516  MICROSOFT CORPORATION                False   \n",
       "11                 0  400915  MICROSOFT CORPORATION                False   \n",
       "13                 0  400915  MICROSOFT CORPORATION                False   \n",
       "\n",
       "    fact.ultimus-index   fact.value member.local-name  period.fiscal-year  \\\n",
       "2                    1  17355000000                                  2019   \n",
       "1                    1   2515000000                                  2019   \n",
       "0                    1    925000000                                  2019   \n",
       "3                    1  29387000000                                  2019   \n",
       "4                    1    508000000                                  2019   \n",
       "9                    2    133100000                                  2019   \n",
       "7                    2     33500000                                  2019   \n",
       "6                    1     21800000                                  2019   \n",
       "5                    1    133100000                                  2019   \n",
       "8                    1     33500000                                  2019   \n",
       "10                   2   2388000000                                  2019   \n",
       "12                   2   2607000000                                  2019   \n",
       "11                   1   2388000000                                  2019   \n",
       "13                   1   2607000000                                  2019   \n",
       "\n",
       "   period.instant report.document-type report.filing-date unit  \n",
       "2      2020-01-01                 10-K         2020-02-20  USD  \n",
       "1            None                 10-K         2020-02-04  USD  \n",
       "0            None                 10-K         2020-02-04  USD  \n",
       "3      2020-01-01                 10-K         2020-01-30  USD  \n",
       "4            None                 10-K         2020-01-30  USD  \n",
       "9      2019-02-03                 10-K         2019-04-02  USD  \n",
       "7      2019-02-03                 10-K         2019-04-02  USD  \n",
       "6      2019-02-03                 10-K         2020-03-27  USD  \n",
       "5      2019-02-03                 10-K         2020-03-27  USD  \n",
       "8      2019-02-03                 10-K         2020-03-27  USD  \n",
       "10           None                 10-K         2019-08-01  USD  \n",
       "12     2019-07-01                 10-K         2019-08-01  USD  \n",
       "11           None                 10-K         2020-07-31  USD  \n",
       "13     2019-07-01                 10-K         2020-07-31  USD  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_df.sort_values(by=['entity.name','dts.id','concept.local-name','fact.ultimus-index','dimension.local-name'], inplace=True)\n",
    "res_df[columns_to_show].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Show dimensions example (if exists)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>concept.local-name</th>\n",
       "      <th>dimension.local-name</th>\n",
       "      <th>dimensions.count</th>\n",
       "      <th>dts.id</th>\n",
       "      <th>entity.name</th>\n",
       "      <th>fact.has-dimensions</th>\n",
       "      <th>fact.ultimus-index</th>\n",
       "      <th>fact.value</th>\n",
       "      <th>member.local-name</th>\n",
       "      <th>period.fiscal-year</th>\n",
       "      <th>period.instant</th>\n",
       "      <th>report.document-type</th>\n",
       "      <th>report.filing-date</th>\n",
       "      <th>unit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [concept.local-name, dimension.local-name, dimensions.count, dts.id, entity.name, fact.has-dimensions, fact.ultimus-index, fact.value, member.local-name, period.fiscal-year, period.instant, report.document-type, report.filing-date, unit]\n",
       "Index: []"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res_df[res_df['dimensions.count'] > 1].sort_values(by=['entity.name','dts.id','concept.local-name','fact.ultimus-index',\n",
    "                'dimension.local-name'])[columns_to_show].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Send output to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_df.to_csv('XBRL_Output.csv')     \n",
    "\n",
    "## If running in Binder, click on the Jupyter icon/name in the upper left corner to see your files, \n",
    "## select the file you want, and click Download."
   ]
  }
 ],
 "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": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}