[Peter Guldberg]

So – having worked quite a bit with this for the last two weeks, it turns out that for some of these filers that end their fiscal year in the beginning of the year insted of at the end of the year (typically retailers), the period.fiscal-year fields for the fact objects are set wrong. For all of the facts.

Two examples are HD (cik 0000354950) and M (cik 0000794367).
HD’s most recent report is report.id 222652. That report is for Q2 2018. Their 2018 fiscal year ends on February 3rd 2019. But all of the facts that are for the period ended July 29th 2018, have a fiscal.period-year field of 2019. It should be 2018.

The same thing goes for Macy’s report.id 222913.

For both of them the DocumentFiscalYearFocus fact is set correctly at 2018.

I realize this is very specific, but it also seems a little serious that for some filers all of the facts have wrong fiscal years.

Obviosuly let me know if I can clarify anything.

[David Tauriello]

Thanks for your feedback on counts – I’ve noted this as a feature request. As you’ve discovered, sorting on concept.balance-type is likely not reliable because the return will either be debit or credit. Using the sort switch on a field that will always have unique values for each row returned ensures the ENDPOINT.offset() works as expected.

Anyone using the XBRL API to return records from our Database of Public Filings will need to be authenticated (either through XBRL US or with a Google Account) – we use this to set maximums per page and for all pages returned to a user. See https://xbrl.us/benefits for details and let me know if you have questions about XBRL US Membership.

[David Tauriello]

We’ve exposed the XBRL US Database of Public Filings via the XBRL API so anyone – regardless of their affiliation with XBRL US – can see and learn about how to use standardized financial data for their own private, non-commercial research and development.

We believe in the integrity of our ‘as-filed’ database and are working to incorporate XBRL data from sources other than the SEC, but we’re not set up to be a ‘data provider’. Several XBRL US Members already do a great job of providing enterprise-level support for XBRL data.

We can provide any any member organization (company or institution) a snapshot of our database and are working on making the XBRL API available, as well (we’re still configuring it around the edges). We can also make documentation available for the database and our load process, if needed.

I think the video you’re referencing is this one https://youtu.be/2Oe9ZqXVGME from a webinar a couple of years ago. The .zip files are in the SEC’s EDGAR database, so I’m not sure you’d be able to connect via the XBRL API. FWIW, we ‘listen’ to the RSS the SEC provides for the database, and programmatically expand these .zips into our database as part of our load process. I’m not completely sure whether the latest version of Raptor reads the .zips or extracts them (I think it’s the latter).

[Campbell Pryde]

Peter,

The fiscal year uses the year in which it is reported. This is common practice amongst all data aggregators. Companies however differ in their interpretation and some retailers call it fiscal year 2019 and others 2018. The logic we apply is if it is more than 15 days into 2019 it is fiscal year 2019. If you look up the Calendar year it will be 2018.

As an example from Walmarts disclosure:
Our discussion is as of and for the fiscal years ended January 31, 2018 (“fiscal 2018”), January 31, 2017 (“fiscal 2017”) and January 31, 2016 (“fiscal 2016”). During fiscal 2018, we generated total revenues of $500.3 billion, which was primarily comprised of net sales of $495.8 billion.

Macys on the other hand does the opposite:
Unless the context requires otherwise, references to “Macy’s” or the “Company” are references to Macy’s and its subsidiaries and references to “2017,” “2016,” “2015,” “2014” and “2013” are references to the Company’s fiscal years ended February 3, 2018, January 28, 2017, January 30, 2016, January 31, 2015 and February 1, 2014, respectively. Fiscal year 2017 included 53 weeks; fiscal years 2016, 2015, 2014 and 2013 included 52 weeks.

To deal with this we have to calendars in the API, fiscal year and calendar year. To get the numbers aligned with fiscal year (Walmart case) use period.fiscal-year, or use period-year. For the same fact is a walmart or Macys filing you will get a different year depending if it is calendar or fiscal aligned.

[Campbell Pryde]

For Macys profit loss in the 2nd Quarter filing you have the following:
Concept.local-name: ProfitLoss
value : $164,000,000.00
calendar-period:2Q
period.year:2018
fiscal-period: 2Q
fiscal-year: 2019

The same is true of Walmart. In the case of Microsoft with a June Year end you get this for the filing made as their 2nd Q ending 31 Dec 2017.

Concept.local-name: NetIncomeLoss
value : -$6,302,000,000.00
calendar-period:4Q
period.year:2017
fiscal-period: 2Q
fiscal-year: 2018

At some point you have to have a cutover for the fiscal year and we make it in that year.

[Saar Sabo]

Ok I just let the user enter their password and username and Api codes

But I still do not understand how I can know how much fields or offset There in the request
If the user is not a member
So he can only see 100 fields
But can be for example 231 fields How can I know how many there are?
For example type of command ENDPOINT.fields.length
Or ENDPOINT.offset.length
something similar
Then I can know how many requests I have to make
To import all information

[Campbell Pryde]

There is no real marker other than the amendment flag, in the filing.

The restatement flag is based on subsequent filings that are an amendment to the original. However, this is a convenience we have added so you know that a filing has been subsequently updated. When looking at data the ultimus index on the fact is the key attribute as this indicates if the fact has been subsequently updated.

A restated filing may not be the complete filing, and can be a portion of the original filing, so ignoring restated filings without an new values in a subsequent filing should probably be avoided.

[Saar Sabo]

Hah
I realized there was no way right now
And that you write it down for future development
This is really important for the development of external applications

[Peter Guldberg]

Hi Campbell, thanks a lot for coming back and for all the detail, I really appreciate it.

I do agree that there is a lot of inconsistency in this area and it makes sense to enforce a rule that is applied to everyone.

My specific problem here is that when I have a report.id that is for the most recent report, how do I get the facts that apply to this most recent period? – A report has the same facts/concepts from many different periods because of comparisons. Walmarts latest report id 223693 has 4 different ProfitLoss facts, even when you filter out those with dimensions. 2Q 2018, 2Q 2019, 1H 2018 and 1H 2019. To get the 2Q 2019 fact I need to know that those are the fiscal-year and fiscal-period I need to filter for.

I cannot filter using report.period-end because both the 2Q 2019 and 1H 2019 facts have that period.end, and also the period.end of the facts do not necessarily match the report.period-end. Macy’s have 2018-08-04 as report.period-end, but period.end as 2018-08-05 for the facts.

So – the only way I have found so far is to use the two facts DocumentFiscalPeriodFocus and DocumentFiscalYearFocus. I thought those would hold the fiscal year and period that would correspond to the period.fiscal-year and period.fiscal-period of the most recent facts in the report.

However, this is where the problem comes in, if we use Walmart and Macy’s as an example, we have the following:

Walmart:
report.id: 223693
report.period-end: 2018-07-31
concept.local-name: DocumentFiscalYearFocus
fact.value: 2019
period.fiscal-year: 2019

Macy’s:
report.id: 222913
report.period-end: 2018-08-04
concept.local-name: DocumentFiscalYearFocus
fact.value: 2018
period.fiscal-year: 2019

(why is Macy’s 2018 and Walmarts 2019?)

I suppose the explanation here could be that the DocumentFiscalYearFocus fact is meant to hold what fiscal year the management thinks they are reporting for – even though we think it is a different fiscal year. If that is the case I can’t really use it here, and then leads me to:

I need a way to query from the report end point the following: “what are the period.fiscal-year and period.fiscal-period I need to filter the facts for, to get the most recent facts that are contained in this report?”

[Peter Guldberg]

Sounds good David. That was pretty much exactly what I was hoping to hear 🙂

[Campbell Pryde]

Maybe it would be helpful to add a flag to a fact to indicate that it is the latest value reported. This would update like the ultimus but would allow you to get the latest value of assets etc reported. (Wheras ultimus indicates if the same value has been reported subsequently.

This would not help to find the latest period reported in a report. Because of the issue with fiscal year focus inconsistencies and the fact that the report does not specifically report this you have 2 choices.
1. Look at the document period end date dei:DocumentPeriodEndDate and use any facts that match this.This is a required field and MUST be populated with the latest period and associated context. This query gives you the fiscal and calender period to get the latest facts for each report filed by msft:

https://api.xbrl.us/api/v1/fact/search?entity.cik=0000789019&=&fact.ultimus=1&concept.local-name=DocumentPeriodEndDate&fields=report.filing-date.sort(ASC),entity.name,report.id,fact.value.sort(ASC),period.calendar-period,period.fiscal-year.sort(ASC),period.year,period.end,fact.id&dimensions.count=0&=

2. return the end date of all facts and sort to get the latest. Make the dimension count = 0 so that you do not get subsequent events. (1 is better)

[Peter Guldberg]

Thanks Campbell.
I have chosen a version of solution 1. I already have the report.id, so I pull the DocumentPeriodEndDate from that and look at the period.fiscal-year and period.fiscal-period for that fact. The advantage of that fact is that it must be populated and it only appears once for each report. So I know I’m getting the right one.
Those are using a different “vocabulary” (Y, 1Q, 1H, 3QCUM) than the fiscal periods for the balance sheet concepts (Y, Q1, Q2, Q3) but they match. So I can translate them to the fiscal periods I need to use for getting the balance sheet data.
It works fine for the retailers. I haven’t fully tested it yet but it looks good 🙂 – so thanks again for your help.

[Tim Bui]

I would like to import financial data into my SQL Server for calculating ratios. However, before doing that, I would like test the use of XBRL database via SQL Server. Could any of you please show me how to get some records via API and import to SQL Server to evaluate the XBRL database? Thank you

[David Tauriello]

To return non-dimensionalized monetary facts with metadata for Apple’s 2018 10-K filing in the XBRL Spreadsheet (Google Sheet):

1. Add a row to the Defined Queries section of the Config tab, with cell A as the name for the query, something like – Apple 2018 10-k facts – and paste this query in the B cell of the added row:

   /fact/search?entity.cik=0000320193&period.fiscal-year=2018&report.type=10-K&fact.has-dimensions=false&concept.is-monetary=true&fields=concept.local-name.sort(ASC),fact.*,fact.offset(0)

   NOTE: there are likely a few faster ways to query and return this data. entity.cik, period.fiscal-year and report.type are used here because they are commonly understood parameters. Also, removing the fact.has-dimensions and/or concept.is-monetary filters will return ALL 2018 facts in the filing.

2. On the Main Sheet, change the Query dropdown to match the name you created above. Once it loads, the result matches the details in this filing (which is referenced in the return as report.sec-url https://www.sec.gov/Archives/edgar/data/320193/000032019318000145/0000320193-18-000145-index.htm)

   Using the wildcard (*) on the fact.* field returns all available metadata for the facts (see the XBRL API Documentation for details for these fields). The returned rows can be copied/pasted/edited to other tabs in this spreadsheet, separate files or imported to other aplications or databases.

3. Depending on your account, you might see 100, 200 or 212 records (rows) returned. If 212 records were not returned, you’ll need to use the fact.offset(0) at the end of the query to return the additional data (see 7 Handling Paging in the XBRL API Documentation for complete details. It is essential to use the sort flag on a field that returns unique values, so the offset can reliably return the next set of data – concept.local-name works in this instance).
   • Non-members will see 100 records. To get the next 100 rows, change the fact.offset(0) from 0 to 100, then from 100 to 200.
   • Basic Individual Members will see 200 records. To get the additional records, change the fact.offset(0) from 0 to 200.
   • Power User and Sole Practitioner Individual Members as well as all Organizational Members, will see up to 2,000 records at a time, with no limit on the total number of records that will be returned by any query.

   For reference, there are 905 facts in the query above if the fact.has-dimensions and concept.is-monetary parameters are removed.

We don’t support using the XBRL API to feed another database. Organizational members should contact us for more information (info@xbrl.us).

See our Benefits page for a comparison of records and tools available to Members using our Database of Public of Filings.

[Jasa SEO Murah]

Thanks for the explanation

[Author]

Posts