Showdata function

[Bryn Harman]

I am super new to this. Can anybody post a simple example of the showdata function that I can try out? I have been experimenting with the API for a couple of hours and I can’t get anything to work. When I try to use the showdata function the cell shows it as text.

[David Tauriello]

Hi Bryn – it will depend on which spreadsheet application you’re using – Google Sheets or Excel.

In Google Sheets, the syntax is =ShowData("https://api.xbrl.us/api/v1/report/search?fields=entity.ticker,report.entity-name,entity.code,report.base-taxonomy,report.document-type,report.period-end,report.accepted-timestamp.sort(DESC),report.entry-url,report.limit(10)","","","TRUE")

In Excel, it’s =XBRL.showData("https://api.xbrl.us/api/v1/report/search?fields=entity.ticker,report.entity-name,entity.code,report.base-taxonomy,report.document-type,report.period-end,report.accepted-timestamp.sort(DESC),report.entry-url,report.limit(10)","","","1")

The second, third and fourth parameters in each function perform the same role; find out about these options from the formula’s help option after completing a query.

The Data Community page linked at right has several spreadsheet templates with queries you can explore and use to get data.

[Bryn Harman]

Thank you. I’m using Excel. Can you write an example formula that I can try, just so I can try to understand how the function works (i.e. for any ticker and any field like Total Assets)?

[David Tauriello]

Bryn – here’s a search for the latest Asset fact values reported in Microsoft’s 10-K reports. Everything in bold is the query and everything in italics corresponds to details about the facts – put this in the Excel formula syntax and paste it into a cell in the spreadsheet:

https://api.xbrl.us/api/v1/fact/search?report.document-type=10-K,10-K/A&entity.cik=0000789019&concept.local-name=Assets&fact.ultimus=true&fields=entity.name,fact.value,period.fiscal-year.sort(DESC),period.fiscal-period,concept.local-name,concept.is-base,report.document-type,report.sec-url

We don’t use ticker together with facts, so CIK is used here – this is the SEC’s Central Index Key assigned to each company. It should be pretty easy to use the entity endpoint to do a ticker lookup in another part of the spreadsheet, return the CIK, then pull that value into the query (there are examples of using concatenate to do this in the templates).

You can learn about available search options and outputs from the documentation or by just making a few queries from the task pane.

[Bryn Harman]

Thanks for your help and patience David. I am going to try this out shortly. I can work with the CIKs no problem.

[Bryn Harman]

OK, I tried that and it all shows up as text in the cell. When I open the “function arguments” window it says that the url is invalid. Maybe I’m totally out to lunch on this.

[Bryn Harman]

David, I got past the aforementioned issues and I managed to pull up the assets data for Microsoft for fiscal years ended in 2011 through 2022. That’s really good. What I’m trying to get to is to pull up a single data point – like total assets as at 12/31/2023 or 3/31/2024. I suspect that the other parameters in the formula have something to do with it. When I click on “help with this function” in the formula pane it takes me to help on the =COUNT() function on the Microsoft site.

[Author]

Posts