Mapping XBRL report instances to JSON

user_14274387 0
Viewed 968

I'm looking to transform XBRL report instances, specifically financial reports such as those produced by the SEC, into Python dictionaries or JSON. I've spent time developing code using bs4 (beautiful soup), but ideally I'd like to leverage the open source Arelle library.

My understanding is there is a plug-in for the Arelle software package called "saveLoadableOIM". There is general guidance published by XBRL.org; however, it stops short of practical implementation.

http://www.xbrl.org/Specification/xbrl-json/CR-2020-05-06/xbrl-json-CR-2020-05-06.html

I have found the documentation for command prompt usage of Arelle to be out-of-date & inapplicable to Python 3.x. Could anyone provide guidance on how to operate Arelle through the python command prompt; and, specifically, how to convert a SEC xBRL report instance into JSON? I'd like a model that's adaptive to future changes in the standard taxonomies, particularly GAAP:

https://www.sec.gov/info/edgar/edgartaxonomies.shtml

It would be particularly helpful to have sample code for mapping the following XBRL report instance of a MSFT 10-K into JSON:

https://www.sec.gov/Archives/edgar/data/789019/000156459018019062/msft-20180630.xml

If there are limitations in the existing Arelle library, I'd like to understand what these are.

xbrl python-3.x python json
2 Answers

I invoke Arelle under Python 3 with:

python3 $HOME/Arelle/arelleCmdLine.py

This is on Linux, and assumes I have Arelle checked out in my home directory as Arelle.

To load a plugin, use --plugins and give it the name of a file under the Arelle/arelle/plugin directory (without the .py extension). For example, --plugins=saveLoadableOIM. You can then add --help and you should see additional options included in the help message.

This works for me:

python3 $HOME/Arelle/arelleCmdLine.py --plugins=saveLoadableOIM --saveLoadableOIM=msft.json -f https://www.sec.gov/Archives/edgar/data/789019/000156459018019062/msft-20180630.xml

Example of extracting data using the awesome jq:

jq '[.facts[] | select( .dimensions.concept | test(":GrossProfit$") )] | sort_by(.dimensions.period)[-1]' msft.json

This gets the most recent GrossProfit value:

{
  "value": "20343000000",
  "decimals": -6,
  "dimensions": {
    "concept": "us-gaap:GrossProfit",
    "entity": "cik:0000789019",
    "period": "2018-04-01T00:00:00/2018-07-01T00:00:00",
    "unit": "iso4217:USD"
  }
}

I should note that the xBRL-JSON specification is not yet finalised, and it's likely that the format of this JSON may change slightly over time. I'd expect Arelle to be updated to the final version once it's available.

user_44875690

I had to solve the same problem and developed a universal xBRL-to-JSON converter that automatically adapts to any changes in the US GAAP taxonomy.

The API extracts all xBRL financial statements from 10-K (and 10-Q) filings, standardises and converts them to JSON. Works like a charm with pandas dataframes.

You can provide either the

  1. URL to the xBRL file, e.g. MSFT 10-K filing https://www.sec.gov/Archives/edgar/data/789019/000156459018019062/msft-20180630.xml

  2. URL to the HTML file of the filing, e.g. https://www.sec.gov/Archives/edgar/data/789019/000156459018019062/msft-10k_20180630.htm

  3. Accession number of the filing, e.g. 0001564590-18-019062

Standardised financial statements include:

  • StatementsOfIncome
  • StatementsOfIncomeParenthetical
  • StatementsOfComprehensiveIncome
  • StatementsOfComprehensiveIncomeParenthetical
  • BalanceSheets
  • BalanceSheetsParenthetical
  • StatementsOfCashFlows
  • StatementsOfCashFlowsParenthetical
  • StatementsOfShareholdersEquity
  • StatementsOfShareholdersEquityParenthetical

Variants such as ConsolidatedStatementsofOperations or ConsolidatedStatementsOfLossIncome are automatically standardised to their root name, e.g. StatementsOfIncome.

Example

The API supports the GET HTTP method. In other words, you can just make a simple GET request by providing the xBRL file URL and the API returns the fully converted JSON.

Convert xBRL File to JSON

GET request to:

https://api.sec-api.io/xbrl-to-json?
xbrl-url=https://www.sec.gov/Archives/edgar/data/789019/000156459018019062/msft-20180630.xml&
token=YOUR_API_KEY

Converted JSON

Note: shorted version to fit in here.

{
  "StatementsOfIncome": {
    "RevenueFromContractWithCustomerExcludingAssessedTax": [
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "value": "274515000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "value": "260174000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "value": "265595000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ProductMember"
        },
        "value": "220747000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ProductMember"
        },
        "value": "213883000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ProductMember"
        },
        "value": "225847000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ServiceMember"
        },
        "value": "53768000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ServiceMember"
        },
        "value": "46291000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ServiceMember"
        },
        "value": "39748000000"
      },

      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:IPhoneMember"
        },
        "value": "137781000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:IPhoneMember"
        },
        "value": "142381000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:IPhoneMember"
        },
        "value": "164888000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:MacMember"
        },
        "value": "28622000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:MacMember"
        },
        "value": "25740000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:MacMember"
        },
        "value": "25198000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:IPadMember"
        },
        "value": "23724000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:IPadMember"
        },
        "value": "21280000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:IPadMember"
        },
        "value": "18380000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:WearablesHomeandAccessoriesMember"
        },
        "value": "30620000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:WearablesHomeandAccessoriesMember"
        },
        "value": "24482000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "aapl:WearablesHomeandAccessoriesMember"
        },
        "value": "17381000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:AmericasSegmentMember"
        },
        "value": "124556000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:AmericasSegmentMember"
        },
        "value": "116914000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:AmericasSegmentMember"
        },
        "value": "112093000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:EuropeSegmentMember"
        },
        "value": "68640000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:EuropeSegmentMember"
        },
        "value": "60288000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:EuropeSegmentMember"
        },
        "value": "62420000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:GreaterChinaSegmentMember"
        },
        "value": "40308000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:GreaterChinaSegmentMember"
        },
        "value": "43678000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:GreaterChinaSegmentMember"
        },
        "value": "51942000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:JapanSegmentMember"
        },
        "value": "21418000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:JapanSegmentMember"
        },
        "value": "21506000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:JapanSegmentMember"
        },
        "value": "21733000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:RestOfAsiaPacificSegmentMember"
        },
        "value": "19593000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:RestOfAsiaPacificSegmentMember"
        },
        "value": "17788000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "us-gaap:StatementBusinessSegmentsAxis",
          "value": "aapl:RestOfAsiaPacificSegmentMember"
        },
        "value": "17407000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "country:US"
        },
        "value": "109197000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "country:US"
        },
        "value": "102266000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "country:US"
        },
        "value": "98061000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "country:CN"
        },
        "value": "40308000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "country:CN"
        },
        "value": "43678000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "country:CN"
        },
        "value": "51942000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "aapl:OtherCountriesMember"
        },
        "value": "125010000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "aapl:OtherCountriesMember"
        },
        "value": "114230000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:StatementGeographicalAxis",
          "value": "aapl:OtherCountriesMember"
        },
        "value": "115592000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2020-06-28",
          "endDate": "2020-09-26"
        },
        "value": "64698000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2020-03-29",
          "endDate": "2020-06-27"
        },
        "value": "59685000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-12-29",
          "endDate": "2020-03-28"
        },
        "value": "58313000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2019-12-28"
        },
        "value": "91819000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-06-30",
          "endDate": "2019-09-28"
        },
        "value": "64040000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-03-31",
          "endDate": "2019-06-29"
        },
        "value": "53809000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-12-30",
          "endDate": "2019-03-30"
        },
        "value": "58015000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2018-12-29"
        },
        "value": "84310000000"
      }
    ],
    "CostOfGoodsAndServicesSold": [
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ProductMember"
        },
        "value": "151286000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ProductMember"
        },
        "value": "144996000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ProductMember"
        },
        "value": "148164000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ServiceMember"
        },
        "value": "18273000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ServiceMember"
        },
        "value": "16786000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "segment": {
          "dimension": "srt:ProductOrServiceAxis",
          "value": "us-gaap:ServiceMember"
        },
        "value": "15592000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "value": "169559000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "value": "161782000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2017-10-01",
          "endDate": "2018-09-29"
        },
        "value": "163756000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2019-09-29",
          "endDate": "2020-09-26"
        },
        "segment": [
          {
            "dimension": "us-gaap:DerivativeInstrumentRiskAxis",
            "value": "us-gaap:ForeignExchangeContractMember"
          },
          {
            "dimension": "us-gaap:ReclassificationOutOfAccumulatedOtherComprehensiveIncomeAxis",
            "value": "us-gaap:ReclassificationOutOfAccumulatedOtherComprehensiveIncomeMember"
          },
          {
            "dimension": "us-gaap:StatementEquityComponentsAxis",
            "value": "us-gaap:AccumulatedGainLossNetCashFlowHedgeParentMember"
          }
        ],
        "value": "-584000000"
      },
      {
        "decimals": "-6",
        "unitRef": "usd",
        "period": {
          "startDate": "2018-09-30",
          "endDate": "2019-09-28"
        },
        "segment": [
          {
            "dimension": "us-gaap:DerivativeInstrumentRiskAxis",
            "value": "us-gaap:ForeignExchangeContractMember"
          },
          {
            "dimension": "us-gaap:ReclassificationOutOfAccumulatedOtherComprehensiveIncomeAxis",
            "value": "us-gaap:ReclassificationOutOfAccumulatedOtherComprehensiveIncomeMember"
          },
          {
            "dimension": "us-gaap:StatementEquityComponentsAxis",
            "value": "us-gaap:AccumulatedGainLossNetCashFlowHedgeParentMember"
          }
        ],
        "value": "-482000000"
      }
    ]
  }
}
user_99866570
Related