Extracting Data From A Particular Financial Statement

[Tim Bui]

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

[David Tauriello]

Search with the dts.id for the filing on the relationship endpoint and treat the concept.local-name for the fact (DepreciationDepletionAndAmortization in the example below) as relationship.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 the relationship.* 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

[Brian Fitzgerald]

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.

[David Tauriello]

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

[Tim Bui]

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

[Brian Fitzgerald]

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

[Tim Bui]

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!

[Brian Fitzgerald]

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.

[Tim Bui]

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

[David Tauriello]

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.* – the network.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)

[Tim Bui]

Thank you, David! Got it. It’s working

[Tim Bui]

Thank you so much for helping, Brian!

[David Tauriello]

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 with label earlier this year), so a query like this will return ‘cash flow’ statements for multiple dts.ids:

/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.

[Tim Bui]

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!

[David Tauriello]

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.

[Tim Bui]

Got it. Thank you, David!

[Author]

Posts