Extracting Data From A Particular Financial Statement

[Tim BuiParticipant]

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 TaurielloKeymaster]

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 FitzgeraldParticipant]

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 TaurielloKeymaster]

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 BuiParticipant]

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 FitzgeraldParticipant]

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 BuiParticipant]

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 FitzgeraldParticipant]

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 BuiParticipant]

Thank you so much for helping, Brian!

[Author]

Posts