Home › Forums › The XBRL API › Extracting Data From A Particular Financial Statement
This topic contains 16 replies, has 4 voices, and was last updated by Andre Kellerman 4 months, 2 weeks ago.
-
AuthorPosts
-
Wednesday, January 9, 2019 at 5:22 PM #120179
Some companies report depreciation & amortization expenses (DDA) on both the Income Statement and the Cash Flow Statement, while some others only report DDA on the CF statement and not on the income statement. If I use the concept “DepreciationAndAmortization”, I do not know if the data came from the Inc Statement or the CF statement.
Is there a way to have the API to return the concept name that has the source of the statement (Inc Statement or CF Statement)?
This distinction is important because it affects the perception of profitability.
A simple example: two companies A and B below have identical financial results. But company A elects to include DDA expenses within the COGS and reports DDA Exp only on the CF statement. Company B elects to exclude DDA expenses from COGS and has a line DDA Exp on the income statement.Both report DDA Exp on the CF statement. When using the API to call the concept “DDA”, the same number $20 comes back for both.
Without knowing that the DDA Exp from the income statement of Company A is NULL,
One would have a wrong perception that company A’s gross profit is lower than that of company B ($50 vs $70)CompanyA CompanyB
Sales $100 $100
COGS $50 $30
Gross Profit $50 $70
DDA Exp $20
SGA Exp $20 $20
Oper Inc $30 $30 -
Monday, January 14, 2019 at 12:37 PM #120531
Search with the
dts.id
for the filing on the relationship endpoint and treat theconcept.local-name
for the fact (DepreciationDepletionAndAmortization in the example below) asrelationship.target-name
.For the query, note that network detail – including the
network.role-description
(report location for the fact) is returned by default. I’m also returning all attributes of therelationship.*
in the event other details about the relationship are helpful:/relationship/search?dts.id=306447&network.link-name=presentationLink&relationship.target-name=DepreciationDepletionAndAmortization&fields=relationship.*
See page 27 of the documentation for details on the Relationship Object https://xbrl.us/xbrl-api-documentation
-
Tuesday, January 15, 2019 at 8:08 AM #120604
Is it possible to return all the concepts for a given financial statement (I think we use network to identify a financial statement)? So, for a given company, I would identify the filing by dts.id and then like to return all of the concepts used in a specified financial statement.
-
Tuesday, January 15, 2019 at 10:12 AM #120613
Brian – yes, the
network.id
for presentation links (like those returned in the query above) can be used with the relationship endpoint to isolate a specific section(s) of the filing.The query below returns elements for a statement and disclosure from the report, filtered to show only line items (
relationship.target-is-abstract=false
) and sorted by section and line item as they appear in each./relationship/search?network.id=37096851,37096836&relationship.target-is-abstract=false&network.link-name=presentationLink&fields=network.id.sort(ASC),relationship.tree-sequence.sort(ASC),relationship.*
here’s the filing: https://www.sec.gov/cgi-bin/viewer?action=view&cik=320193&accession_number=0000320193-18-000145&xbrl_type=v
-
-
Tuesday, January 15, 2019 at 8:38 AM #120608
Brian,
The website XBRLXL.com created by Jim Truscott might provide what you are looking for: a complete list of all of the concepts for all of the financial statements.
Jim created a wonderful Excel spreadsheet named X Sheet to get all financial statements verbatim easily. All you have to do is put in either the ticker or the CIK on the “Search” tab and hit “Data Refresh All” to get different sheets within Excel populated with either annual or quarterly number.
The basic X Sheet is free. Jim also created other products with standardized concepts for a very, very modest fee. You can check out his website at https://xbrlxl.com/. He also has posted several instructional videos on youtube to explain how to use his products (https://www.youtube.com/user/FundXJim)
He was incredibly helpful in answering my questions. I use his Excel spreadsheets often.
Tim
-
Wednesday, January 16, 2019 at 10:26 PM #120778
Very helpful, thanks to both of you. I was able to get the API to return all tags used in a specified financial statement with this query:
/relationship/search?dts.id={dtsid}&relationship.target-is-abstract=false&network.link-name=presentationLink&network.role-description=1001000 - Statement - Condensed Consolidated Statements of Operations (Unaudited)&fields=relationship.tree-sequence.sort(ASC),relationship.*
I tweaked David’s suggestion by adding a network.role-description to the query, in this case giving me an income statement. Thanks again
-
Thursday, January 17, 2019 at 10:02 AM #120823
Hi Brian,
I too learned a lot from David and tweaked his codes to get what I need. Could you share with me your full code to get the specified financial statement (what goes before /relationship/search?…) Thanks!
-
Monday, January 21, 2019 at 10:56 AM #121102
Tim – I used the formula on the template spreadsheet to return a CIK from a ticker, then use this one to get the dts.id from the CIK (using: max(showData(…)):
https://api.xbrl.us/api/v1/report/search?entity.cik={cik}&fields=dts.id
that gave me the dts.id to use as the search parameter in this one:
https://api.xbrl.us/api/v1/relationship/search?dts.id={dtsid}&relationship.target-is-abstract=false&network.link-name=presentationLink&network.role-description=1003000 - Statement - Condensed Consolidated Balance Sheets (Unaudited)&fields=relationship.tree-sequence.sort(ASC),relationship.*
that returns all the tags used for specified financial statement [here I used: 1003000 – Statement – Condensed Consolidated Balance Sheets (Unaudited)]
I am sure there is an easier to do it, but this works.-
Monday, March 25, 2019 at 9:37 AM #129969
Hi Brian,
Not being a technical person and not knowing much about Google Sheet, would you please help me again with downloading the financial statements?
1. Get CIK:
I put the ticker in B2 and use this code to get the CIK(put code in B3): =left(ImportXML(“https://www.sec.gov/cgi-bin/browse-edgar?ticker=”&A2&”&action=getcompany”,”//div/span/a[1]”),10)2. Get dtd.id:
I put your code in cell B3
https://api.xbrl.us/api/v1/report/search?entity.cik=B2&fields=dts.id
=showdata(B3), but I get “No information returned”3. Get financial statements:
https://api.xbrl.us/api/v1/relationship/search?dts.id={dtsid}&relationship.target-is-abstract=false&network.link-name=presentationLink&network.role-description=1003000 – Statement – Condensed Consolidated Balance Sheets (Unaudited)&fields=relationship.tree-sequence.sort(ASC),relationship.*
– what does the curly brackets {dtsid} do?
– where do I change the code to get Income statement or cash flow statement?Thank you for your help, Brian!
Tim
-
Thursday, March 28, 2019 at 4:28 PM #130800
Tim – on the ‘no information returned’ issue, check that your formula is correct. Spreadsheet programs (Google Sheets, Excel) require a specific syntax when you want to use data contained in a cell – our templates use the CONCATENATE formula pretty consistently to join strings together see: https://support.google.com/docs/answer/3094123 – this creates a URL that is recognized by the XBRL US showData() function in our add-on that enables data to flow into Google cells.
I read Brian’s
{curly brackets}
as a variable – in this case, it’s the dts.id that’s populated by data in another cell or worksheet.Try this query in your Google Sheet –
/relationship/search?dts.id=316055&relationship.target-is-abstract=false&network.link-name=presentationLink&fields=network.role-description.sort(ASC),relationship.*
– thenetwork.role-description
column lists statements and disclosures from this Ford Motor Company report. You can use the text from any of these reports with Brian’s code to get line details for any statement or disclosure (remember to carry the dts.id into Brian’s query – not every company reports the same statements and disclosures) -
Thursday, March 28, 2019 at 6:01 PM #130825
Thank you, David! Got it. It’s working
-
-
-
Tuesday, January 22, 2019 at 9:16 AM #121168
Thank you so much for helping, Brian!
-
Tuesday, June 25, 2019 at 9:44 AM #143867
Hi Tim – I’m writing to let you know that the
relationship
endpoint has been updated to support simple text string search (as was done withlabel
earlier this year), so a query like this will return ‘cash flow’ statements for multipledts.id
s:/relationship/search?dts.id=292503,306447&network.role-description=cash flow&network.link-name=presentationLink&relationship.target-is-abstract=false&fields=dts.id.sort(ASC),relationship.id.sort(ASC),network.role-description,relationship.target-name,relationship.tree-sequence.sort(ASC),relationship.source-namespace
We’re in the process of updating documentation for this change.
-
Thursday, August 1, 2019 at 3:40 AM #147970
Wow! Useful topic!
-
Tuesday, April 2, 2019 at 10:09 AM #131503
Hi David,
Using the code below, I can get the Ticker and Facts. However, is there a code where I can reference to a CIK and get all of the concept.id for each financial statement for a particular year? I can get all of the concept names per CIK from Jim Truscott’s XBRLXL website. But I don’t know how to get all of the concept.id.(CONCATENATE(A1&”/report/fact/search?entity.cik=”&TEXTJOIN(“,”,TRUE,GetTickers!B10:B100)&”&fact.has-dimensions=false&fact.ultimus-index=1&concept.local-name=”&GetTickers!B8&”&period.fiscal-period=”&GetTickers!D1&”&period.fiscal-year=”&TEXTJOIN(“,”,TRUE,GetTickers!A2:C2)&”&fields=entity.cik,period.fiscal-year,period.fiscal-period,concept.local-name.sort(ASC),concept.id,fact.value,entity.name,entity.ticker”)
By the way, thank you for pointing me to get the Quarterly Financial Data Set from the SEC website. Nevertheless, to make this data set useful, I think I need the concept.ID from XBRL US to group and manage data because concept.id is much more fiscal year and company specific.
As always, thank you for your help, David!
-
Saturday, April 6, 2019 at 8:22 AM #131983
Tim – in the forumula you posted, remove
fact.has-dimensions=false
to return all facts. See page 13 of API Documentation for details on this parameter. -
Saturday, April 6, 2019 at 10:15 AM #131990
Got it. Thank you, David!
-
AuthorPosts
You must be logged in to reply to this topic.
Search Forums
Recent Topics
-
Getting error – 'Bad or expired token'
by
Sriram Srinivasan
-
Retrieving Market Cap as a concept.local-name item from a report.id
by
Jason Mellone
-
Google Sheet – margin time series not working
by
Kevin Monahan
-
Why does XBRL return differing values for same concept and how to address it
by
Teji Abraham
-
Code for publicly traded companies
by
Tim Bui
Recent Replies
-
Getting error – 'Bad or expired token' 3 weeks, 3 days ago
-
Getting error – 'Bad or expired token' 3 weeks, 4 days ago
-
Getting started with the XBRL Google Sheet and add-on 1 month, 1 week ago
-
Getting started with the XBRL Google Sheet and add-on 1 month, 1 week ago
-
Retrieving Market Cap as a concept.local-name item from a report.id 1 month, 3 weeks ago
XBRL API Resources
- The XBRL API
- XBRL Data Community
- XBRL API Interactive Documentation
- XBRL API Documentation (PDF)
- Getting Started with Google Sheets
- Sample API Queries (works with Insomina) or Excel Power Query Template
these options require account provision
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 info@xbrl.us 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.
Who's using this free data?
Join XBRL US
- Individual Options - Basic, Power User & Sole Practitioner
- For Your Team - Startup, Non-Profit, Academic & Corporate options
- Member Benefits Comparison Table
Upcoming XBRL US Events




Comment
You must be logged in to post a comment.