Using the offset parameter

[David Tauriello]

A data user writes:

I seem to have problem running any query on Facts with Offset parameter. FYI, I’m using the Excel utility. It stays ‘Busy’ for quite a while and then returns something like this-

“50a37df0-b7fc-4ac0-9ba5-8b960507781d
err: SyntaxError: Unexpected token ‘<', "

Without knowing the specific circumstances of this query, it’s difficult to say why it’s failing. In most cases with the offset parameter, this happens because the query does not sort the results in a way that creates a logical path for the offset to follow (ie. filing date for data over time, company id for facts across reports).

Here’s a valid fact query using sorts that create a logical path (reverse chronological order by filing date then report ID then …):
https://api.xbrl.us/api/v1/fact/search?concept.local-name=Assets,Liabilities&fact.has-dimensions=false&fields=report.filing-date.sort(DESC),report.id.sort(DESC),report.document-type,entity.name,period.fiscal-year.sort(DESC),period.fiscal-period,concept.local-name.sort(ASC),fact.value,report.entry-url,fact.limit(12)

Here’s a process to follow in Excel that will offset that results to the 12 facts starting at 500:

• Paste the query in cell A1
• Paste this in cell A2: =XBRL.showData(A1,””,””,”1″)
• Add ,fact.offset(500) to the very end of the query in cell A1

[Rakesh Agrawal]

Thanks David, here is the query causing the issue. Please let me know if you find anything amiss (this used to work fine btw during Trial period).

=XBRL.showData(CONCATENATE("https://api.xbrl.us/api/v1/fact/search?report.sic-code=2833,2834,2835,2836","&report.document-type=10-K&report.year-focus=2023&fact.ultimus-index=1&fields=concept.balance-type,concept.datatype,concept.id,concept.is-base,concept.is-monetary,concept.local-name,concept",".namespace,concept.period-type,dimension-pair,dimension.is-base,dimension.local-name,dimension.namespace,dimensions,dimensions.count,dimensions.id,dts.entry-point,dts.id,dts.target-namespace,entity.cik,entity.code,entity.id,entity.name,entity.scheme,fact.","accuracy-index,fact.decimals,fact.has-dimensions,fact.hash,fact.id,fact.inline-display-value,fact.inline-is-hidden,fact.inline-negated,fact.inline-scale,fact.is-extended,fact.numerical-value,fact.ultimus,fact.ultimus-index,fact.value,fact.value-link,fact.","xml-id,member.is-base,member.local-name,member.member-value,member.namespace,member.typed-value,period.calendar-period,period.end,period.fiscal-id,period.fiscal-period,period.fiscal-year,period.id,period.instant,period.start,period.year,report.accession,r","eport.creation-software,report.document-index,report.document-type,report.documentset-num,report.entry-url,report.event-items,report.filing-date,report.form-type,report.hash,report.html-url,report.id,report.is-most-current,report.period-end,report.period-","focus,report.restated,report.restated-index,report.sec-url,report.sic-code,report.source-id,report.source-name,report.submission-type,report.type,report.year-focus,unit,unit.denominator,unit.numerator,unit.qname","report.id.sort(ASC),fact.id.sort(ASC),fact.limit(500)","fact-offset(500)&unique&"),"", "", "1")

[David Tauriello]

Hi Rakesh – there’s at least one comma missing in the fields.

unit.qname,","report.id.sort(ASC),

[Rakesh Agrawal]

Thanks David, in the same vein, there was another missing comma.

fact.limit(500)","fact-offset(500)

However, these missing commas don’t seem to be causing the error. They may be having some bearing upon consistency/ accuracy of the results we get though.

Real culprit seems to be the ‘unique’ filter. If i remove it from the query and run (whether with or without the commas), it’s running fine.

Does it ring any bell as to why it shd be so?

[David Tauriello]

Hi Rakesh – more questions than answers, I’m afraid.

1. what’s the goal?
2. have you tried returning fewer fields? (fact.value, report.id,entity.name)
3. have you tried running your query with a computer language (ie. Python or R) instead of through excel? (excel will time out)

[Author]

Posts