Saturday, October 13, 2018 at 11:25 AM #112974
So – if I understand this correctly, the report object does not have periods. Only a period-end. Only facts have fiscal period fields.
If I want to find the latest fiscal period that a specific CIK has filed for that means I will first have to find a report.id for the latest 10-Q or 10-K, and then use that report.id to query for a specific fact, and then getting the period.fiscal-period from that fact. Since there will be facts from both the current and the previous period I will also have to make sure that I get the most recent fact and use the fiscal-period from that.
Is there an easier way to try and find which fiscal period a report relates to? – Maybe if an entity had a fiscal year-end field?
Monday, October 15, 2018 at 11:20 AM #113051
Hi Peter – thanks for your good question. There are a couple of parameters and techniques you can use to return the ‘latest’ data. To find the ‘latest fiscal period’ that a specific CIK has filed, simply use ‘report.is-most-current=true’
You can also use the .sort(ASC/DESC) switch with nearly all ‘fields’ that are returned. Applying sort to the period.instant and report.filing.date orders the period (for facts returned in a report).
This will return the Assets reported by the CIK in the most current report, with the fact.values sorted by period. Remove the report.is-most-current parameter to see all values for the concept Assets.
Monday, October 15, 2018 at 12:43 PM #113062
Hi David – thanks a lot for your detailed answer.
I can see how that will get me facts from what is the latest report.
However – my problem is that I want to find the answer to “What is the latest fiscal period that this CIK has filed a report for?” – or similarly: “Which fiscal period is the latest report that this CIK has filed, for?”
Because only facts have a fiscal-period, and reports have facts from several different fiscal periods, that makes it somewhat difficult.
It would seem to me that a report could have a fiscal-period field, that could hold this value.
Although – I can see how in your example, since the period.instant is sorted, the first fact that it returns should have a fiscal-period field that contains what I want. It just seems a somewhat round-about way to get it 🙂
Monday, October 15, 2018 at 4:07 PM #113087
Peter – thanks for the clarification; using only the /report endpoint, returning fields report.period-end and report.document-type lets you know ‘what is the latest fiscal period’ for a CIK (quarter or annual as of balance date)
Monday, October 15, 2018 at 6:17 PM #113100
No, that is the whole problem – that does not tell me what the fiscal period is. It only tells me the end date and whether it is a 10-Q or a 10-K. With only that information I cannot distinguish a Q2 report from a Q3 report (or Q1 for that matter). For example the latest report from NKE is Q1 2019, but the period-end is 2018-08-31.
However – in the meantime I found out that there are two facts named DocumentFiscalPeriodFocus and DocumentFiscalYearFocus. I can use those with the following query, to get the info I need:
Tuesday, October 16, 2018 at 5:02 PM #113160
Peter – thanks, for helping me to see the issue clearly and posting your solution. Our team is aware of your interest in getting this from the report endpoint.
Monday, November 5, 2018 at 5:33 PM #115421
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.
Tuesday, November 6, 2018 at 5:23 PM #115691
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.
Tuesday, November 6, 2018 at 5:40 PM #115699
For Macys profit loss in the 2nd Quarter filing you have the following:
value : $164,000,000.00
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.
value : -$6,302,000,000.00
At some point you have to have a cutover for the fiscal year and we make it in that year.
Wednesday, November 7, 2018 at 5:52 PM #115937
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:
(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?”
Wednesday, November 7, 2018 at 11:08 PM #115974
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:
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)
Thursday, November 8, 2018 at 3:32 PM #116128
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.
You must be logged in to reply to this topic.
XBRL API Resources
- Create a Google Account (without Gmail)
- Install XBRL API Access (Google Sheet Add-on)
- Make a Copy of Google Sheet Templates:
Using the XBRL API with the Public Filings Database
Unless otherwise agreed to in writing, any and all use of the XBRL API to authenticate and retrieve data from the XBRL US Database of Public Filings implies user consent and agreement with the XBRL US API Agreement. If you are unable to agree to these terms, do not use the XBRL API.
Ready to work with the API in Excel's Power Query, or with your own system or app?
Contact us at email@example.com to have your existing XBRL US Web account provisioned to generate client ID/Secret pairs to work with the XBRL API in a REST client or other application, including Excel's Power Query.
NOTE: You do not need to generate client ID/Secret pairs if you use the Google Add-on and Google Sheet exclusively to access data - the XBRL API Authentication Add-on handles this automatically.
Your account needs to be provisioned before you can login and generate client ID/secret pairs.
Login or register for a free account.
Join XBRL US
- Individual Options - Basic, Power User & Sole Practitioner
- For Your Team - Startup, Non-Profit, Academic & Corporate options
- Member Benefits Comparison Table