[Peter Miller]

Hello Peter Reed,

First, I would like to thank you, that you haven’t given up yet. I was investing my last two weeks of my holidays into the bulk data, which contain the .json files for all the companies.

Before I comment on your last posting, I would like to share some possible problems on the bulk data/.json files:
1:
The .json file seem to be incomplete sometimes, when multiple sub-terms are summed up to one term. For instance, in the last PG 10-K Filing the sub-terms “Buildings”, “Machinery and equipment”, “Land” and “Construction in progress” are having to by current knowledge all the US-GAAP tag “us-gaap:PropertyPlantAndEquipmentGross”. This US-GAAP tag is also used for the summary for these sub-terms named “TOTAL PROPERTY, PLANT AND EQUIPMENT”. In the final .json file I could only find the summed up value, consequently under the US-GAAP tag “PropertyPlantAndEquipmentGross”. The values of the sub-terms are not provided, probably because they share the US-GAAP tag.
2:
It might be furthermore a problem, that the filling classification (10-K, 10-Q) is sometimes not correct. I have seen data points, where between the start and end date was only 3 Months, but the filling was classified as a 10-K filing. That was a problem when no start date was reported and only an end date. I solved this issue, partly, by iterating through every US-GAAP tag and searching for filings where a start and end date was provided. When the time delta between the start and end was than >335 days and smaller than 395 days, then the month of the end date was considered as the fiscal year end of that specific year. But as you can imagine, some companies didn’t report a start and end date at any of the us-gaap tags in the .json file, and consequently no fiscal year-end month could be calculated.

All in all, I am extremely disappointed that some sub-terms, which are used to calculate a data point, are not reported in the .json files. If someone has a solution for that, please share.

Regarding your last posting:
1:
The tables at the links you have provided are not providing any information in which entity the numbers are displayed. So, for instance, in some tables the numbers are provided in millions and in others in thousands.
2:
These links aren’t available for all the fillings in the past, which are in the .json files.

In pandas 1.30 the read_xml function was included. I have no experience with .xml files, but maybe this function can be used to extract us-gaap-tag, common name and entity of all numbers included in the official XBRL file.

[Peter W Reed]

Thanks Peter for your observations. Your frustration with consistency is why I dropped getting data from Yahoo and other such sites. I thought if I went to the source I’d be better off. I also tried the two Python modules for Yahoo financials, but found they sometimes displayed bad information too.

Couple of items:
– Yes, the SEC reports I pointed to have a problem. The problem is they display “Segment” information as well as the desired “not segmented” information. The way I’ve dropped that information is naïve keeping only the first instance. The XBRL “htm.xml” file identifies segmented and not segment data. I hope we don’t have to use that file too, thanks for the warning.
-I’ve had the best success with xml parsing by using the xmlschema module. Using the .xsd file as the schema definition file.
– I’ve always assumed that shares are in 000’s and money is in 000000’s. In the reports I mentioned in my last post the “Data Type” for each tag is defined with “xbrli:SharesItemType” or “xbrli:monetaryItemType”. I was thinking that would identify the units too. I’ll look out for the exceptions.
– I’ve only been using a small subset of companies to test with so I haven’t seen the odd behaviors you mention. Mostly I’m only interested in % change. I’m hoping those oddities smooth out by normalizing with %.

[Chris Martinez]

Hi David
I carried out the suggestions (steps 3 and 4) in your recent response (#194230). Unfortunately it did not overcome the issue – I still received the same error message.
Any further suggestions?
Thanks,
Chris

[David Tauriello]

Hi Chris – I just sent an invite for google meet; if you don’t see this until after the time expires, please email me a couple of ranges that work.

Thanks,
David

[David Tauriello]

Hi Chris – thanks for meeting today to resolve the issue you had getting connected with Google Sheets. I’m hopeful you won’t have issues going forward.

For what it’s worth, we do have an Excel Add-in as well, since you mentioned being more comfortable with that application (requires Office 365). See https://xbrl.us/xbrl-api-community for details.

[Peter W Reed]

I decided to drop the parsing of the XBRL file and instead concentrate upon the scraping of the reports I mentioned in another post. To answer your question now I want to show you the way the context is shown in those reports. Below is a table (DataFrame) that comes from the report. One table for each XBRL element within the report. The 4th row is the same information as in the “contextRef”. This attribute isn’t needed for parsing the report. The value of the PeriodType is in processing. The 10-K report’s ‘duration’ parameters are a summation of all four quarters. If you want to know the 4th quarter value for that parameter, subtract the first three quarters. The 10-K ‘instant’ parameters are a snapshot of the company as of the end date, no processing should be made. Hope this helps!

<p>
0 1
0 Name: us-gaap_CommonStockDividendsPerShareDeclared
1 Namespace Prefix: us-gaap_
2 Data Type: num:perShareItemType
3 Balance Type: na
4 Period Type: duration
</p>

[David Tauriello]

Hi Tim and all on this thread – thanks for a very healthy discussion on this topic. I hope you’ll join our free webinar later this month, Working with SEC Data for Analysts for tips & strategies in working to create normalized views of US GAAP data.

[Tim Bui]

Thank you for inviting, David. The topic of the webinar seems to match with what I am trying to study. I will sign up. Regards,

[Peter W Reed]

Apologize my last reply to you was in error. I said that I hadn’t seen a change in units for money or shares. The excel based html I’m using from the edgar site indeed has such unit changes. The good news is the units are documented in by the index column. Here are the two variations I have run across – (1) “Condensed Consolidated Balance Sheets – USD ($) $ in Millions”, and (2) “Condensed Consolidated Balance Sheets – USD ($)”. My logic needs to use the absence of a “millions” unit to mean there will be six zeros that need to be stripped. The units for shares follows the same logic.

It has taken me a long time to get the excel-based Edgar data to work well. A lot of data cleansing was required. Here is a sample page –

url =”https://sec.gov/Archives/edgar/data/0000909832/000090983221000008/R{x}.htm”
where {x} takes on values from 1 to 8.

[Jeff Krimmel]

Hi,

I am trying to make sure I know how the setup in Google Sheets works.

In one cell, I create the following URL: =CONCATENATE(“https://api.xbrl.us/api/v1/fact/search?concept.local-name=Assets&period.fiscal-year=2020&Entity.cik=0000040545&fields=fact.value&#8221;)

In the cell immediately below it, I have the following:
=showData(CellAbove)

And the value I get is “No information returned”.

I am authorized, via the Google Sheets extension. And I use other XBRL API calls in Google Sheets that work fine. Those were copied and pasted from elsewhere. I’m now just trying to explore on my own, to make all of this a bit less of a black box for myself.

I must have a typo in the URL I’m creating, right? But I’m trying to follow example on page 8 of the API doc:

/api/v1/fact/search?concept.local-name=Assets&period.fiscalyear=2017&period.fiscal.period=1Q&Entity.cik=0000001&fields=fact.value

Do you know where I’m going wrong?

Thanks for the help.

[David Tauriello]

Hi Jeff – thanks for writing. yes, it’s a typo in your query – the ‘e’ in entity.cik should not be capitalized (true for all parameters).

Sincerely,
David

[Jeff Krimmel]

David, thank you so much for the helpful reply.

I’ve tried it again with this query, and I again get “No information returned”:

=showData(CONCATENATE(“https://api.xbrl.us/api/v1/fact/search?entity.cik=0000040545&period.fiscal-year=2020&concept.local-name=Assets&fields=fact.*&#8221;))

Now, when I change “fact.*” to “fact.value”, I do indeed get a list of values. Also “fact.ultimus” gives me a list of results. But “fact.*” gives me “No information returned”.

I’m sorry for the hassle on this one. Any help is greatly appreciated.

[Elisa Roselli]

Hi David,
I’m trying to install the Excel add-in. I keep getting an error “We can’t load this add-in because we couldn’t connect to the catalog”. The Excel spreadsheet shows the XBRL Filed Data tab, but it won’t let me log in (just repeats the error in the wizard window).
I am currently logged in on the xBRL web site and am a member of xBRL US. Is there a way around this?
Thanks,

Elisa Roselli

[David Tauriello]

Hi Elisa – thanks for sending along the error message. In short, ‘we couldn’t connect to the catalog’ means the issue is in getting connected to the Microsoft Store to download the add-in.

If your Microsoft Office license is held by an organization, there may be a restriction in place that prevents the use of Add-ins for security; in that case, you’ll need to work with your IT Team to request the Add-in be admin managed – this should make it available to you and anyone else in your organization.

If your Microsoft Office license is yours personally, there may be a network restriction in place, and your IT Team would need to allow traffic to and from our domain and github (where the scripts for the add-in are stored). NOTE: while you might be able to install and use the add-in if the computer is outside of the network or VPN, but it might not function once the computer is back inside the network.

We’ve had reports that while the add-in is blocked for download on the desktop, it is functional as an add-in using Excel on the web – https://www.office.com/launch/excel

I hope this helps.

[David Tauriello]

Hi Jeff – thanks for pointing this out; it’s been fixed, so your query should be good to go.

[Author]

Posts