[Peter W Reed]

Thanks Tim. An excellent reference too.

To respond to your reply earlier this week. Here is a short explanation for my approach to getting terminology mapping and 10-Q/K results. I hope to get the code clean enough to post on a public site, github.

The first part of all the URLs below is “www.sec.gov/”. My approach requires 4 steps. Once programmed it isn’t as ugly as the description reads I hope.

1) “cgi-bin/browse-edgar?action=getcompany&CIK={ticker}&type={Report-Type}”

2) Scrape the web page from step #1 for the CIK and Accession Number(s). Replace the hyphens in the Accession Number with null, “”. The result I call “accNoHyph”

3) The general syntax of the url for the company’s reports is: “Archives/edgar/data/{cik}/{accNoHyph}/{report}.htm”.
a) For example, Victory Secret 10-Q Statement of Equity is r = “Archives/edgar/data/1856437/000185643721000018/R6.htm”

4) Everything else is accomplished using BeautifulSoup. I assume you have familiarity with that module.
a) first action after downloading page in (3) is: td_find = r.find_all(“td”, {“class”: “pl”})

b) Next search and match with (i) re.search(re.compile(r’defref_us-gaap_(\w*)’),str(x)):. Then (ii) match = re.search(re.compile(r’defref_us-gaap_(\w*)’),str(x)). The group() function for re is used to get the “us-gaap” tag.

exception to (b) The attributes on the cover page (R1) in XBRL terminology is “dei”. All other reports {R2-R7} contain “us-gaap” attributes.

c) A sample result for (4b) symbol VSCO is below, note both concept terminology and XBRL terminology for this element is present:

Total Comprehensive Income (Loss)

A child tag has the result for “Total Comprehensive Income (Loss)” which is (358).

Notes for each numbered item above. Remember “www.sec.gov/” is the prefix:

1) Victoria Secret URL is “/cgi-bin/browse-edgar?action=getcompany&CIK=VSCO&type=10-Q”

2) Scrape the web page from step #1 for the CIK and Accession Number(s). Replace the hyphens in the Accession Number with null, “”. The result I call “accNoHyph”. The Accession numbers reference a specific quarter or annual report.

3) Victory Secret report on Statement of Equity is “Archives/edgar/data/1856437/000185643721000018/R6.htm”
The {report} attribute is R1 to R7. R1 is the 10Q/K cover page. The remaining reports {R2-R7} are the cash flow, balance, and income statements. When you process R1 you need to modify the search in step 4 from “us-gaap” to “us-dei”.

4) When you process R1 you need to modify the search in step 4 from “us-gaap” to “dei”. “dei” is the XBRL standard tag prefix for company info, e.g. “dei_DocumentPeriodEndDate”.

5) The header row returned in step 4 contains the name of the report, for example “… Income Statement…”. And very importantly the phrase ‘in millions’. Two styles of numbers are used those with and those without the millions trailing six zeros.

[Peter W Reed]

Apologize for a broken link. I hope this works better. This is reference material posted on the Alphavantage website.

You might also find their Academy of particular interest on AI, Django, and other topics of interest.

[Tim Bui]

Thank you for the detailed explanation, Peter! I am going through the steps now so learn now. I am grateful!

[Trevor Carley]

When is FERC Form 1 and 3 data expected in the API?

[david.tauriello@xbrl.us]

Hi Trevor – thanks for writing and apologies for the delay in responding. We’re nearly complete in our testing and expect to start loading this data set in our database in the coming weeks.

[Samuel Pun]

Hi David,
I’m using Excel XBRL add-in. I can log in and I can grab data by using the panel and creating the query using the panel. However, when using the actual API query in Excel, it doesn’t seem to work. It doesn’t even recognise the formula since it shows #NAME?. Any ideas?
Thanks,
Sam

[Arthur Olevskiy]

Hi David,

Thank you for your assistance here. It really helps to move forward in building the app.

So, I came with question. How to get most recent fat value? I’m trying to use ultimus and ultimus-index, but sometimes I still getting not recent value of report facts.

Here is my path to get fact values.
1. Firstly, of all I’m collecting reports for specific CIK.
2. Secondly, using report.id and api /api/v1/report/{report.id}/fact/search I’m collecting facts of report. Here is my query, I will skip fields part:
10-Q https://api.xbrl.us/api/v1/report/333770/fact/search?fact.ultimus=true
10-K https://api.xbrl.us/api/v1/report/323207/fact/search?fact.ultimus=true
In these two examples I’m using ultimus, however I’m still getting not recent values and those values also has ultimus-index = 1

Is there a better way to get recent fact values for specific report?
How can I get fiscal year for report object, so I can compare report fiscal year and fact fiscal year)

Thanks in advance for all answers.
Arthur

[david.tauriello@xbrl.us]

Hi Arthur – first, apologies for the delay in responding – this post was flagged in our system (probably because it includes ‘https://’

The fact.ultimus parameter is used on the /fact endpoint. When you specify a /report, you will return all facts in the report without regard to fact.ultimus.

Use your /report query to return the dts.id value for the report, then use a separate /fact query to show fact.ultimus-index detail.

/fact/search?dts.id=428275&fact.ultimus=false&fact.has-dimensions=false&fields=entity.id,report.id,concept.local-name,fact.value,fact.ultimus-index,period.fiscal-period,period.fiscal-year.sort(DESC)

In the query above, IntangibleAssetsNetExcludingGoodwill for the year 2020 was reported several more times:

/fact/search?entity.id=6252&concept.local-name=IntangibleAssetsNetExcludingGoodwill&fact.has-dimensions=false&period.fiscal-period=Y&period.fiscal-year=2020&fields=concept.local-name,fact.numerical-value,fact.ultimus-index.sort(DESC),report.id,period.fiscal-period,period.fiscal-year

Let me know if you have additional questions about this.

[david.tauriello@xbrl.us]

Hi Sam – a couple of things to check:

1. If you’re re-using queries from Google Sheets, the function is slightly different in Excel (=showData vs. =XBRL.showData)
2. Excel limits the length of formulas to 255 characters. Our query generator breaks these up, but you may be using one that goes beyond this limit. To get around this, drop your formula in a cell (A1) and use =XBRL.showData(A1, “”, “”, “1”) to return the data. NOTE: the subsequent parameters are required in Excel, even if they’re empty.

If neither of these helps, post the forumula you’re using in a reply here.

[PX NY]

During an XBRL.us API webinar hosted summer 2021, the report.event-items field was showcased for querying 8-K’s and their categorized items.

I’ve used in Excel the sample query from the webinar:

=XBRL.showData("https://api.xbrl.us/api/v1/fact/search?<strong>report.event-items=5.02</strong>&fields=report.entity-name,report.event-items,report.filing-date,report.sec-url,report.limit(15)")

It returns a #VALUE error, even with just one field to report.

Is the report.event-items query field currently activated for searches via Excel or Google Sheets?

Thanks.

[david.tauriello@xbrl.us]

Hi PX NY – sorry for the delay in responding; report.event-items works with searches on the /report endpoint:

/report/search?entity.cik=0000037996&report.document-type=8-K, 8-K/A&fields=report.document-type,report.entity-name,report.event-items,report.sec-url

One change from the webinar is that you cannot query for a specific event type – you’ll need to use entity, or SIC Code or other report filter parameter.

[Length Power]

Let me show tesla’s company facts data with restful api:
https://data.sec.gov/api/xbrl/companyfacts/CIK0001318605.json

You can see all labels in ‘facts —- us-gaap’,do all listed companies share same label names ?
Does apple and tesla have the same labels ?Can every company create its own customerized label names?

[david.tauriello@xbrl.us]

Hi Length Power – thanks for writing and apologies for the delay in responding.

Every company can create custom labels that readers see when they are reviewing a report. The XBRL elements the company has chosen for its financial report can either be base elements (from a US GAAP or IFRS Taxonomy) or extension elements created by the company if they determine that no base element meets the need in their reporting.

If you use the XBRL API, try the Simple Statement and Disclosure template – you can review the label and element decisions made by a company in reporting its financials (https://xbrl.us/wp-content/uploads/2021/07/simple-statement-disclosure.xlsx).

It’s really the underlying elements (not the front-side labels) that should be considered if you’re looking to compare company financials.

[Effahid Vizrain]

Hi
I’m trying to rebuild various financial reports using filed xbrl and ixbrl files and struggling with duplicate concepts.

(Apologies, while this strictly isn’t related to us xbrl, the intention behind this exercise is to allow US XBRL/IXBRL files to be parsed ultimately)

In the screenshot below, Called up share capital, retained earnings and shareholders’ funds are all tagged as “Equity”. While I can visually identify which values belong to which concept, I need to be able to tell programmatically which values belong to which concept. For example, being able to categorise 795,500 as Called Up Share Capital when the concept used is “Equity”.

The ns6 namespace for each concept has this value btw
http://xbrl.frc.org.uk/fr/2019-01-01/core

Any pointers highly appreciated

[david.tauriello@xbrl.us]

Hi Effahid – thanks for writing and apologies for the delay in responding. The URL you posted for the screenshot didn’t have an image.

From your description, it sounds like ‘called up’, ‘retained earnings’ and ‘shareholders’ funds’ are reported as dimensions of equity. If you look at the iXBRL, you might see a contextref attribute that is common to these elements, and element names that are unique.

[Author]

Posts