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 2 months, 2 weeks ago.

  • Author
    Posts
  • #120179

    Tim Bui
    Participant

    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

  • #120531

    David Tauriello
    Keymaster

    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

  • #120604

    Brian Fitzgerald
    Participant

    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.

    • #120613

      David Tauriello
      Keymaster

      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

  • #120608

    Tim Bui
    Participant

    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

  • #120778

    Brian Fitzgerald
    Participant

    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

  • #120823

    Tim Bui
    Participant

    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!

  • #121102

    Brian Fitzgerald
    Participant

    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.

    • #129969

      Tim Bui
      Participant

      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

      • #130800

        David Tauriello
        Keymaster

        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)

      • #130825

        Tim Bui
        Participant

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

  • #121168

    Tim Bui
    Participant

    Thank you so much for helping, Brian!

  • #143867

    David Tauriello
    Keymaster

    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.

  • #147970

    Andre Kellerman
    Participant

    Wow! Useful topic!

  • #131503

    Tim Bui
    Participant

    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!

  • #131983

    David Tauriello
    Keymaster

    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.

  • #131990

    Tim Bui
    Participant

    Got it. Thank you, David!

You must be logged in to reply to this topic.

Comment