[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