Public Company Financials with XBRL Filed Data
This is the next post in the add-ins series, where we are exploring various add-ins that enhance and extend the capabilities of Excel. In this post, we’ll take a look at the XBRL Filed Data add-in. In summary, it enables us to get public company financial data (and more) into Excel.
Before we get too far, let’s get a general sense of what the add-in does.
The add-in is offered by XBRL US, the national consortium for business reporting standards. It is a not-for-profit organization that helps with digital reporting standards.
As companies and organizations around the world file their financial statements and other public documents, this organization imports the data into their database. You can then pull specific values from their database directly into Excel.
The XBRL Filed Data add-in is available in the app store. To get to the app store, just click Insert > Get Add-ins. Search for the term XBRL, install the XBRL Filed Data add-in and you’ll be ready to go.
Once installed, you’ll see a new XBRL Filed Data tab with various commands.
Note: depending on your version of Excel, you may or may not have access to these add-ins.
Let’s pull a few pieces of data to get a sense of the basics. This add-in is quite comprehensive, so I’m just covering the high-level basics in this post. If this add-in seems helpful in your line of work, you’ll definitely want to explore all of the capabilities it has to offer.
The first step is to register for a free account so that you can login to retrieve data. You can initiate the process by using the XBRL Filed Data > Get a Free Account button:
Follow the steps, including the step to obtain a client id and secret. Your username, password, client id, client secret, and entrypoint url will be needed when you login through the add-in.
Once you have these, click the Login button in the add-in pane:
Enter the requested information and you’ll be ready to retrieve data into Excel.
The add-in pane looks like this:
As you can see, there are many data points that we can retrieve.
Let’s get warmed up, and select entity from the function drop-down. We can search for an entity by name, such as microsoft. We can also select which fields to return:
Note that we can opt to return Function+Data, Data, or Query. If we select the default, Function+Data, the add-in returns a formula. Data returns just the data value. Query returns the api url.
When we click Get, the data is entered into our workbook:
When we inspect cell A1, we see that that it contains a formula like this:
=XBRL.showData("https://api.xbrl.us/api/v1/entity/search?entity.name=microsoft&fields=entity.name,entity.ticker,entity.limit(),entity.offset()&","", "", "1")
We can edit this formula directly, or copy it to another cell and change. If we wanted to replace the search term with a cell reference to say B20, we could do so using concatenation, like this:
=XBRL.showData("https://api.xbrl.us/api/v1/entity/search?entity.name="&B20&"&fields=entity.name,entity.ticker,entity.limit(),entity.offset()&","", "", "1")
We can retrieve a list of filed reports for any entity by selecting report from the Function drop down.
We can search by various fields, including company name. This function returns a list of the filed reports, like this:
We could copy any link and paste it into a browser to see the source, like this:
We can retrieve specific values from individual reports as well. Let’s select fact from the function drop-down:
There are several search fields available, including Select Report by Entity Name. We can enter the entity name to reveal a drop-down list of available reports. We could also filter by a fiscal year, period, or concept name. The add-in populates the worksheet accordingly:
Depending on our needs, we could then perform comparisons to other entities, format the data, use it with lookup functions, or otherwise use the power of Excel to accomplish our desired task.
The add-in provides commands to additional documentation and support as well:
If you work with public company filed data, this add-in will enable you to retrieve such data directly into Excel … and probably save you some time 🙂
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.