[Asif Saberi]

Nevermind, I used the excel function VALUE… and it works.

• This reply was modified 1 year, 10 months ago by Asif Saberi.

[Asif Saberi]

Hi David (and others): Working in excel, when I pull several years of a ‘concept’ for one entity, how do I ensure it is sorted in a certain order. Return seems random…Here is my call and its result
=VALUE(XBRL.showData(CONCATENATE(B8,B10,B11,”&period.fiscal-period=Y&concept.local-name=”,$B17,”&fact.ha”,”s-dimensions=false&fact.ultimus=true&fields=fact.value,period.fisc”,”al-year, fact.limit(),fact.sort(), fact.offset()&”),””, “”, “0”))

$20,289,000,000 2017
$38,016,000,000 2020
$25,913,000,000 2018
$48,844,000,000 2019
$34,940,000,000 2021

[david.tauriello@xbrl.us]

Hi Asif – you can use .sort(ASC|DESC) on fields that are being returned to design a sort order – see ‘sort’ in the documentation for details – https://xbrlus.github.io/xbrl-api

This functionality exists on the query tool in the side panel of XBRL Filed Data – use the arrow keys on the selected fields (which can also be re-ordered by dragging and dropping them) to sort fields.

[david.tauriello@xbrl.us]

Asif – On the Excel version of XBRL Filed Data, if you choose the ‘Data’ option above the ‘Get’ button on XBRL Filed Data, you’ll get the values for fields. I believe the Function + Data (default) setting returns query results in a ‘Dynamic Array’ (this is a Microsoft feature).

[Asif Saberi]

I am working with the API in excel and noticed that when I use Office 365 online, my XBRl lookup query is changed when I download it to my desktop as follows: “XBRL.SHOWDATA” is replaced by “_xldudf_XBRL_SHOWDATA” and the lookup fails on the desktop.
=IFERROR(_xldudf_XBRL_SHOWDATA(CONCATENATE($D$11, $D$12, $D$5, “&fields=entity.name,entity.limit(1)”),,,0),”Enter a valid ticker symbol in B1″)
Thanks in advance for any assistance.

[david.tauriello@xbrl.us]

Hi Asif – the XBRL Filed Data needs to be added to your desktop version of Excel in order to activate the custom XBRL.showData function used to retrieve data from our Public Filings Database.

Use the ‘Add-ins’ button under the insert menu to download and install this from the Office Add-ins Store.

[Asif Saberi]

It had been added. I resaved a file on the Office 365 site and then tried to open it with my desktop Excel App again. However, I went through the Add-In process again and it worked. Thanks!

[Peter Guldberg]

Is there a way to make an XBRL query refresh when you are within an Excel-VBA sub?

When you are running a VBA sub, and this changes a cell that the XBRL query is dependent on, the XBRL fields change to #BUSY!, and they do not update until the macro has actually finished.

[Timur Mirzaev]

Hi Tim,

Thank you for noticing this, as I have missed that completely. I have not seen any response on this thread, so I decided to share my take on this.

From my perspective, there might a valid reason for this discrepancy. It seems, as the tag itself says (increase/decrease), that the ‘.fact’ simply shows the sign as per the “math’s” calculation, than the accounting interpretation.
So, in the case of inventory, an increase is math’s positive, while in accounting it is negative (use of cash). The same logic goes to receivables.

For payables, an increase happens to be the same simply because the accounting effect is net cash positive (the increase in payables implies less is paid).

I hope that helps, but please let me know your thoughts.

Regards,

Timur

[Timur Mirzaev]

Hello!

The problem I have is that for fiscal years 2019 and EARLIER when I pull Cash Flow statement data, I get only full year result, rather than, say 1H or 3QCUM.

Here’s an example of my query:

https://api.xbrl.us/api/v1/fact/search?&entity.cik=0000320193&period.fiscal-period=1Q,1H,3QCUM,Y&concept.local-name=PaymentsToAcquirePropertyPlantAndEquipment&fact.has-dimensions=false&fact.ultimus=true&report.document-index=1,2,3,4,5&fields=report.period-end,entity.name,period.fiscal-year.sort(DESC),concept.local-name,fact.numerical-value,period.fiscal-period.sort(DESC),report.id,report.sec-url,fact.offset(0)

This does not seem to be a query-related issue, I believe, as the initial fiscal periods DO show the required periodic data.

Would appreciate a help on that.

Regards,
Timur

[david.tauriello@xbrl.us]

Hi Peter – an Excel user who’s worked with VBA wrote and would like to know a bit more about what you’re trying to accomplish in VBA.

Can you post a rough set of steps for this interaction?

[david.tauriello@xbrl.us]

Hi Timur – thanks for writing; the query is using report.document-index. Generally, this filter is paired with report.document-type so you can get the corresponding data (ie. report.document-type=10-K&report.document-index=1,2,3 will return the three most-recent 10-K reports without any amended filings).

Without specifying a report.document-type, the results may be inaccurate.

If you remove the report.document-index filter from your query, you should return all values for the entity and concept.

[Timur Mirzaev]

Hi, David

Thank you very much for your response. This definitely helps and does return all the values! Unfortunately for me, it retrieves even more data that I need (let alone if other concepts are added). Since I cannot use document-index, is there any other way I could limit how many years of data are being retrieved?

[david.tauriello@xbrl.us]

Hi Timur – seems like period.fiscal-year (comma-separated, like you were doing with document.type-index) might be a better option, since you’re looking by year.

The online documentation lists all available parameters for fact (and other endpoint) searches: https://xbrlus.github.io/xbrl-api/#/fact/getFactDetails

[Peter Guldberg]

Ok, let me try to explain:

I have a worksheet where I input the CIK for one specific issuer.
This pulls a list into the worksheet of all of the 10-Q and 10-K reports by that issuer.
For the oldest report the worksheet then pulls in the facts from that report that are relevant to me.

So far, no VBA, just XBRL.showData() functions in the worksheet.

I then want to use VBA to do the following steps:

1. Store the relevant facts, mentioned above, in my own DB.

2. Select the next-oldest report from the worksheet, and make the worksheet pull in the relevant facts from that report into the worksheet, still using the XBRL.showData() Function.

3. Go back to step 1.

This works fine for the first/oldest report, but once we get to the next report the XBRL.showData() function does not update. It just displays #BUSY!, because the VBA-macro is still running.
Once the VBA stops running, the function updates almost immediately.

In Google Sheets with App Script this does work as long as you insert pauses that allows the function to update.

[Author]

Posts