In this high-level example, we will explore the 10-Q financial statement filing of Tilray, a Canadian corporation, for the quarterly period ending September 30, 2020
We can find information on all filings with the Securities and Exchange Commission (SEC) at the SEC Edgar company search page.
Entering "Tilray" in the Company and Person Lookup box, we see an entry for the company under Edgar Filings (see Figure 1.1 below):
Figure 1.1
Clicking on "Tilray" under Edgar Filings, we are shown the most recent filings with the SEC by Tilray (see Figure 1.2 below):
Figure 1.2
As we can see, there are many different kinds of reports Tilray is required to file with the SEC, displayed with the most recently filed at the top. However, we are only interested in the latest 10-Q report. We can filter the results to see only Tilray's 10-Q filings by entering "10-Q" in the Filing Type box in the Filter Results section, and clicking the "Search" button (see Figure 1.3 below):
Figure 1.3
If we click the white "Documents" button in the Format column for Tilray's most recent 10-Q report, we are taken to the SEC's Filing Detail page for their September 30, 2020 report, which is a list of the individual component files that went in to the company's 10-Q filing (see Figure 1.4 below):
Figure 1.4
Looking at the bottom left of Figure 1.4, we see a CIK number 0001731348 for Tilray (outlined in yellow). "CIK" stands for Central Index Key, which is an identifier the SEC assigns to all companies and individuals who are required to file documents. We point it out here because we'll see Tilray's CIK used later in this example.
The list of files required for a company's 10-Q filing is divided into two sections, "Document Format Files" and "Data Files". If we click on the link with the description of "10-Q" in the "Document Format Files" section (document name of "tlry-10q_20200930.htm"), we will see the actual 10-Q report for Tilray for September 30, 2020 (see Figure 1.5 below):
Figure 1.5
While it's useful to be able to view a company's 10-Q report on line, our objective is to capture the information from a 10-Q report and store it in a database. Once we've done this, we can leverage the power of programming languages like SQL and Python to compare the financial statements of different companies against one another in an automated fashion, to help choose desirable investment candidates.
With this in mind, let's look at the list of files in the "Data Files" section of the Filing Detail to see which of these will be most helpful (see Figure 1.6 below):
Figure 1.6
Based on the description of these files, we can tell that their content is formatted using the XBRL standard, which is in turn based on the ubiquitous XML standard. The XBRL standard is rich and can accomodate many of the complexities of financial reporting. In this high-level example, we will only scratch the surface, but you will get some exposure to how the standard works.
While all of the XBRL files in the "Data Files" section play an important role, we will initially focus on two of them: the Extracted XBRL Instance Document (tlry-10q_20200930_htm.xml), and the XBRL Taxonomy Extension Presentation Linkbase (tlry-20200930_pre.xml)
At a high level, you can think of the Extracted XBRL Instance Document as a catalogue of:
Facts can be:
As an illustration, here is what the "Cash and Cash Equivalents" fact looks like in the Extracted XBRL Instance Document (See Figure 1.7 below):
Figure 1.7
And here is what the "Profit Participation Arrangement" fact looks like (see Figure 1.8 below):
Figure 1.8
Three things to note on each of the two facts shown in Figures 1.7 and 1.8 (outlined in yellow):
Let's take a look at the entries found in the Extracted XBRL Instance Document for the contexts used to fix the two facts shown above in Figures 1.7 and 1.8. First, let's look at the "C_0001731348_20200930" context (see Figure 1.9 below):
Figure 1.9
From this entry, we can see that the "C_0001731348_20200930" context refers to a single instant in time, the day of September 30, 2020.
Since the context associated with the us-gaap:CashAndCashEquivalentsAtCarryingValue fact in Figure 1.7 above is "C_0001731348_20200930", this tells us the Cash and Cash Equivalent value that this fact represents was as of September 30, 2020
Now let's look at the "C_0001731348_20200101_20200930" context (see Figure 1.10 below):
Figure 1.10
From this entry, we can see that the "C_0001731348_20200101_20200930" context refers to a period of time, between January 1, 2020 and September 30, 2020.
Since the context associated with the tlry:ProfitParticipationArrangementTextBlock fact in Figure 1.8 above is "C_0001731348_20200101_20200930", this tells us the Profit Participation Arrangement value that this fact represents was for the time period between January 1, 2020 and September 30, 2020
From what we've seen so far, we are now armed with some useful information:
However, what we do not know yet is how to organize facts that are related to each other and commonly grouped together
For instance, in a 10-Q financial report, there are typically three main sections (or groupings) containing numeric or alphanumeric facts:
Below is shown the Balance Sheet section from Tilray's September 30, 2020 10-Q filing (see Figure 1.11 below):
Figure 1.11
Looking at the Balance Sheet, it is divided into three main sections (outlined in Figure 1.11 above, in magenta):
In turn, we can see that some of the items in the three main sections are further divided into even more granular sub-sections, in a hierarchical structure
In order to re-create the Balance Sheet from the facts and contexts in the Extracted XBRL Instance Document, we need information on how to find the hierarchical structure in which to group the facts. This information can be found in the XBRL Taxonomy Extension Presentation Linkbase file.
To illustrate how the hierarchical structure is represented in the XBRL Taxonomy Extension Presentation Linkbase, the section that specifies the facts that make up the Assets section of Tilray's Balance Sheet, and the order in which they will appear, is shown below (see Figure 1.12 below):
Figure 1.12
To represent facts that are organized in a hierarchical structure, XBRL uses the notion of an arc. Looking at Figure 1.12 above, we can see multiple lines labelled "link:presentationArc", with an "arcrole" of "parent-child". The "xlink:from" (i.e. - parent) and "xlink:to" (i.e. - child) attributes specify the direction of the arc that connects two facts, and the order of the facts is specified using the "order" attribute value.
We can also see that the same fact that is the child of one fact (i.e. - it is on the "xlink:to" side of the arc) can also be the parent of one or more other facts (i.e. - it is on the "xlink:from" side of the arc).
To make the "link:presentationArc" information a little easier to read, we show the XBRL Taxonomy Extension Presentation Linkbase information in a tree structure in Figure 1.13 below:
Figure 1.13
What we can see from the tree structure is that:
Looking at Tilray's Balance Sheet information from the 10-Q report for September 30, 2020, we see that the information found in the XBRL Taxonomy Extension Presentation Linkbase gives us the correct grouping and ordering for the facts in the Assets section of Tilray's Balance Sheet (see Figure 1.14 below):
Figure 1.14
Now that we know how to find the hierarchical structure using the "link:presentationArc" entries in the XBRL Taxonomy Extension Presentation Linkbase to group and order the facts available in the Extracted XBRL Instance Document, we have the basic building blocks to retrieve financial reports from the SEC Edgar site, and to recreate the sections of a company's 10-Q filing.
However, there is more detailed information about the XBRL technical specification we need to learn before we can make full use of the facts in the Extracted XBRL Instance Document. We will learn more about the specification in the subsequent sections of this tutorial.
Also, at this point, we know where to find the facts and hierarchical structure information from the Extracted XBRL Instance Document and XBRL Taxonomy Extension Presentation Linkbase XML files. In order to use the contents of these files, we will need to know how to parse the XBRL files and how all of the information fits together.
If we are familiar with a programming language, and prepared to read and understand the XBRL technical specification, there is nothing stopping us from attempting to parse and utilize the XBRL files on our own
But, before diving in to the deep end of the XBRL pool, we should refer to the good advice given at the XBRL Organization's "Getting Started For Developers" web page:
The strong recommendation of the XBRL consortium is that developers coming to use the standard for the first time should integrate third party libraries to deal with XBRL data and metadata (whether producing or consuming that information) rather than attempting to implement the XBRL specifications themselves, from scratch.
From the perspective of someone who regretfully chose not to heed this recommendation when first learning about XBRL, I can suggest a third-party package called Arelle which will:
With the contents of the Filing Detail files loaded into a relational database schema, the task of interpreting and utilizing the information for financial analysis purposes becomes a lot easier
In the next section of the tutorial, we will discuss how to use Arelle to parse the XBRL files that make up a company's 10-Q filing, and to load the information into a PostgreSQL relational database schema.
| Previous Page | Crosskeys Home Page | Next Page |
| SEC Edgar, XBRL and Arelle | Home Page | Loading SEC Edgar 10-Q Information Into PostgreSQL RDBMS Using Arelle |
© Copyright 2020, Crosskeys Technologies