Returning Tagged As Reported XBRL using an API

This is where you can see how a Company Reported their financial data but with tags to allow standardization, comparison and aggregation.

This is available to see in the Beryl Section Sheet. The API call brings back data as seperate records for each year but this is obviously more difficult to read and not how you would see it an report so the Power Query in the example sheet flips the years up onto the y axis of the Table as Columns. This can have the unwanted effect of inserting new columns into the sheet with each new call, as potentially each new column has different name depending on the dates of the years in question coming back from the filing.

To counteract that and to stop it getting messy basically!, we moved the Input Boxes from the top row to the first column. You can have the boxes wherever you want really. This sheet is an example, a starting point. As long as they build valid Queries, they can go wherever makes sense to you. You might want to create a separate opening sheet with all the parameters in for example.

This Call uses the items/section Endpoint to return an As Reported Filing Section.

By choosing the Relative option "123", it will bring back all the primary periods for Report and so will the results will come back looking like the face of the Income Statement if you say choose that Section.

Which section gets returned is determined by which Section you choose. A drop down in the example Section sheet shows you the sections you can choose as denoted by their codes but you can actually view any section that has XBRL Values in the report by selecting it by number. Just visit the Filing Sections sheet see which Sections are available for a filing.

Using an API Call to get an Overview of the XBRL contained in Filing

We have a very simple call that enables you to quickly see which sections of data are available as XBRL in a filing. This is particularly helpful when used in conjunction with items/sections Endpoint. That Endpoint brings back all the XBRL values disclosed in a chosen section along with all the presentational data to display it as it was reported (e.g. show me in the Income Statement for 2019. What you are looking at here when you do that is all the same XBRL used by the SEC to construct their Interactive Data pages for a filing.

Use the Endpoint sections as shown, choose a company in the Co Box and select a Date for the filing. You can also choose further along the row in the Annual? and Quarter Boxes whether you want to see the Annual Filing (e.g. 10-K or 20-F - just select "yr", otherwise "q") or choose which quarter you wish to see the filing for. By default it will bring back the annual filing.

The results, as shown above, give you a list of all the Sections by name and by Tag. Unlike (in theory!), XBRL Values, Sections returned from the SEC data are not tagged, very bizarrely, with a set of standard tags. So we have stepped in to supply our own Standard Section Tags and to make things even easier, especially when summoning a section, we've turned these into a series of short codes. You can see these displayed in the Code column when you a return list of Sections. Each Section is also numbered according to its display order (No). This is useful when summoning a more obscure Section that does not have a Code. Line_Total next to it will give you a handle on whether a Section is likely to contain any useful XBRL (Total lines of XBRL contained in a Section). You can also quickly verify that the data returned for a Section is as it should be by following the "Link" to the same page shown in SEC Interactive Data for a filing. It should be as the data source is the same - the XBRL filed at the SEC.

Getting Tickers and Other Company Details Back with an XBRL API Data Call

 

There are three relevant Endpoints when trying to find a company to download.

1. cos/try (as shown above) enables you to search on a company name. Input at least five letters into the Company Box and it will return a list if matching companies when you refresh the query (Alt F5) as shown below.

2. cos/ will search by Ticker or CIK to return an exact match. In the case of a Ticker, it may return more than one result as it will show all the different companies that have used that Ticker since Companies have filed XBRL. The most common reason why two companies may have used the same Ticker would be where a company has morphed into a different legal entity so two companies, two CIK's. Don't worry when bringing back results we handle this by mapping the same Ticker to both companies to bring back a seamless series of results. Note: CIK's never change whereas Tickers for a company can.

3. cos/industry. You can use a SIC code with this Endpoint to bring back all companies with the same SIC. You could copy then SIC code for a target company into the industry box, select this Endpoint and the query will return all the companies operating in the same industry.

XBRL API Quarterly Data Call

As explained in our Getting Started guide, We have set up an Excel sheet to make it really easy to start quickly pulling data back using Beryl, our API. All you have to do is fill in the coloured boxes in any of the sheets and run the query to bring back a time series for your chosen data point.

An API call for quarterly data works in much the same way as for annual values. However to bring back Quarterlies, three more boxes come into play. except this time they are black.

 Black Boxes  - these either represent fixed elements of the API path or ones that have a small set of finite values so we have rigged them up as drop downs in Excel.

 Black Box  - Annual? Default value is "yr" but you can select "q" to see quarterly values instead.

 Black Box  - Quarter. Selecting "q" brings this box into play where you can select which quarter 1,2, or 3. You can also specify "123" to get all quarterlies for a year. NOTE: "12" etc will not bring back 1st and 2nd Quarters. Only "123" works for multiple quarters in a year. If this is set to "4", and the next box is set to "ytd", it will bring back the annual figures.

 Black Box  - YTD. Setting this to "ytd" as opposed to blank will bring back cumulative figures for the year - the Year To Date.

Orange Boxes work just like they do for annual amounts. So if you set the date to 2019, it will bring back the quarter you selected (e.g. 2) for the reporting year ending in that year. And if you set Years to 5, you will get a times series of quarter 2's going back 5 years.

Getting started with an XBRL API Data Call

(if you've stumbled across this article and want to know more, email us and I can add you to our beta programme)

We have set up an Excel sheet to make it really easy to start quickly pulling data back using Beryl, our API, or indeed, the XBRL US version.

All you have to do is fill in the coloured boxes in any of the sheets and run the query to bring back a time series for your chosen data point.

For our API you will need a token which you can get from our website by registering or signing in, which then goes in the Red Box.

To run the query, move to a cell in the grey area where the results are. Right click and choose refresh query or use the keyboard shortcut ALT F5. In Excel the API call is made using Power Query.

Green Box - Tag. Choose a data item by entering an XBRL tag. We have constructed a data set of the most commonly used tags that you are likely to need called the X Financials. These are listed on a sheet at the back of the Workbook. If you want to see all the tags you could possibly ever use! then got to an online XBRL Taxonomy (there are quite a few free ones around). I use the Workiva Wdesk Taxonomy Analyser. You can also just enter "All" in this box and it will bring back data for all the items used the companies you select.

Green Box - "All". If you don't know which tag to select or need to get familiar with the tags in use, you can put "All" in the Green Box as the Tag.. Be aware that if a value lies off the main axis (sorry getting a bit technical here), then these values will not get returned with this call. This only applies to more obscure values that might have been disclosed in a more complex table in the notes. A full explanation of axis and dimensions is explained in another article. Values on these axis will be made available in future. We want to make sure we do this in a meaningful way that doesn't bombard you with incomprehensive incomparable data

Orange Box - Date. Choose a year for the values that you want returned. It will get values from filings with a reporting period ending in that year. So specifying 20191231 will return values from a 10-K with a year end up to and including that date. If you just enter 2019, it will do the same. If you enter 20190630, it will bring back values for filing periods ending between 20190630 and 20180701.

Orange Box - Years. The other (optional) Orange Box, Years, determines how many years of values are bought back. So the Date box specifies the starting year and Years how many periods are returned in the series. If no Years are specified, it will bring back one year of values.

Blue Box - Cos. Enables you to specify which companies you want values returned for. This is where the beauty of modern Excel kicks in. Add a ticker to the bottom of the list and the Excel Table (The Blue Box) automatically expands to include it. Companies have to be specified by ticker, but don't worry if you haven't got the ticker at hand, you can quickly look them up using our API on the Beryl Cos sheet. You can always reduce the number of companies returned by pulling the Table back up to its original size using the triangular (ish) handle at the bottom right corner of the Table.

Also read - A Walk Around the Results Returned by an API Data Call (coming soon).

How does the new Excel Linked Data Type Stocks stack up?

Late in 2018 Microsoft rolled out a new concept - Linked Data Types. The first two types to be taken out of this shiny new box are Stocks and Geography. They are in effect connections to external cloud data sources with the initial connections curated using Microsoft Bing AI.

So are they any good? Well not surprisingly my interest is in the Stocks Data Type.

More info on how it all works and an introduction to what you can do with this Data Type can be found in the following links:

Office 365 Support - Excel data types: Stocks and geography

Strategic Finance - Excel: Stocks Data Type

Office Watch - Is Excel Stocks Data Type Premature?

I've been playing with this data on and off for a few weeks now and, going a bit deeper, I wanted to look at the good and the bad.

My interest was in using it as a front end stock selector for bringing back detailed XBRL financials from one of the APIs out there that enable you to do just that (try out the XBRL US API via our Excel template). These invariably work off a Ticker (or other identifier). But you don't always know the ticker so if the Stocks Data Type could magic up an instant ticker this would be a pretty nifty solution. And you can see it doing exactly that in this video.

So I tried this out on a handy but somewhat out of date list of 1000 bigger companies, essentially by converting this list of names into Linked Stocks (Select All/Click Stocks Data Type on the Data Ribbon)

I had a problem in roughly 300 cases (30%). 10% was due to the fact that some of these companies were no longer listed (merged/acquired etc). So lesson 1, Stocks only works with live companies (well I suppose it is stocks after all!). So I was down to a problem with 20%. For half of these (10% of the whole), it offered me some alternatives via the Data Selector Pane that pops up if no good match is found. What was surprising was it often offered me a choice when I felt it didn't need to e.g. Fox Corp; what was the clear cut choice was on the list and it should have gone straight for it, no quibbles, like it had for the other 80% of live companies.

And I got there in the end with the final 10% using a couple of strategies:

• Feeding it a ticker instead (kinda defeated the point as this was supposed to be the output!)*
• Removing words (taking it out "The", "Company", "Corporation", "LP" was particularly fruitful - funny kind of intelligence as you'd have thought removing noisy words would be the very first thing Bing did)

*Note you can be sure to get the correct quote more often by prefixing the ticker with the exchange followed by a colon or a two digit country code.

I was perplexed why for these 100 companies, it gave me no choices to pick at all. Take Deere & Co - I fed it John Deere and it give me not a single option.

Another thing to note with Bing is that it loves a bit of context. Feed it Ford and it gets stuck - is it Ford Motor Company or Forward Industries (Ticker = Ford) but put together a table of Auto Industry companies and add Ford underneath it and it uses its intelligence to automatically convert it to a new row containing Ford Motor Company.

All of this was done with data from their new supplier - Refinitiv (formerly known as Thomson Reuters before the Image Consultants got to work). They had to rather quickly change from their previous suppliers, Morningstar.due to data issues.

What's good about it is the functionality works really well. I like it. What lets it down at the moment is the AI and sometimes the data. The good thing is that the AI ,by its nature, will get better. When I first started asking Alexa to play songs by the British band James, she would torture me with songs by James Arthur. After admonishing her for several months for her crass stupidity, she finally learnt the error of her ways. I bet in a years time, Bing won't be quite so easily defeated by John Deere.

Missing data

Some surprises here given the quality of the source.

For example the employees figure for Fox was missing but I could easily find it elsewhere. Note you won't always realise the data is missing if you just look at the cards as these only show the values that are actually present.

A lot of data was missing for oil companies which was odd. e.g. Enterprise Products Partners has no industry classification. Peculiar as the industry classification system used by Refinitiv is TRBC (Thomson Reuters Business Classification) i.e. their own so you wouldn't have thought that any large companies would be unclassified.

Usage limitations?

A word of CAUTION. You will notice what looks like a thin little disclaimer appears when you first add a Linked Stock Data Type:

Financial market information is provided as is and not for trading purposes or advice.

Annoying enough that you have to click to remove this desktop hogging message but that's only the half of it. Its not a "stocks may go down as well as up" type of message or not just that old chestnut of how they can never be held responsible if you, heaven forbid, decide to trade on the bits of dodgy data that they never got round to cleaning up, that you yourself should have spotted.

As it says, click on the link and you will learn more about our data sources. And you will learn what not for trading purposes or advice really means. It means you LEGALLY can't use it to do these things, well certainly if you do it for a living or carry out any kind of financial function.

So this is all a bit confusing given that these professions are by far and away the biggest users of Excel. Do they tip toe round this functionality? Ordered not to use it by Legal on pain of death?

Whether this has any legal legs is I imagine debatable, given that you are not informed about this up front and this functionality is now baked into a product that to my knowledge has never had any hidden restrictions over what you could do with it before. I only looked because I'm in the financial information business so went sniffing around for this classic little stitch up.

So in theory, all you can do is look at it. So you have to question the point of it being in Excel at all. In practice I suspect you can get away with doing an awful lot more with it but you would have to question whether you could, say share a workbook with Stock Types in it or data derived thereof. Could this per se constitute advice?

XBRL US API

Now here's a great way to access reported XBRL, as filed with the SEC. XBRL US have been databasing filed XBRL since day one and last year they published an accessible restful API designed to make their extensive database available to a wider community.

Now out of beta and with a small but burgeoning community, the XBRL US API provides an excellent way to get hold of individual pieces of XBRL via relatively straight forward http API requests. Pretty much everything is there, although it can take some time and experimentation to get all the pieces together you want, given the breadth and complexity of the dataset.

But never fear, there are two handy templates to get you up and running from day one, one for Excel (which we at Fundamental X have provided) and one for Google Sheets.

The Excel sheet shows you how you can quickly use Power Query as a front end to SEC filed XBRL. See how to immediately get going in this video.

Things to note:

• As filed, warts and all. So all the errors and missing data is present (or not!)
• Some limits to the free version (see pricing below)
• Authentication token required with each request and needs updating every hour
• Initial registration and Authentication setup a little tortuous (4 bits of info required)*

*Easier with the Official Google Sheets template as just email and password required via in app login (although new login required each time token expires) and with the Excel template setup is required only once with the option to generate new Authentication tokens automatically.

Pricing (individuals) - see also XBRL US Membership

1. Free - Registration required - Limits: Max 1000** records returned per request; Non-commercial use*.
2. Individual Membership - $55 a year - Limits: Max 2000** records returned per request; Non-commercial use*.
3. Power User Membership - $250 a year - Limits: No record limits; Non-commercial use*.
4. Sole Practitioner Membership - $500 a year - Limits: No record limits; Commercial use allowed*.

*If you are an investment professional who trades on behalf or advises others then you would need to purchase Sole Practitioner Membership. Different rates apply for corporates. Commercial use also allows you to share the data with your clients, with attribution. Redistribution is not possible.

**The record limit may not necessarily be a problem. It may just require you to make more individual requests and frame them more carefully. There may also be a cap on the number of requests made within certain timescales with Google Sheets.