Exporting AIRR data to Excel
How to export data from AIRR into Excel
Caveat: As of the time of this article only the Windows version of Excel supports API data imports.
​
If we were looking at the page on AIRR of one of the portfolio companies, our URL might be the following:
https://app.theairr.com/reports/fund/f7aac1e9-d1df-4df2-b94c-dd2d08f0b312/company/131da366-1837-4bb3-9987-87dc29141b00
​
Here we can notice the first alphanumeric string is the same fund as above, followed by /company/ and then the alphanumeric string representing the company's ID in bold. This is because the company belongs to the fund in the first example.
​
While Excel is not the ideal tool to import and make use of JSON data, it can be used if necessary. This brief tutorial will explain how to import data from a AIRR's JSON API.
Prerequisites:
​
In order to import the data into excel we must gather three things:
​
-
An API endpoint for the data you want to access.
-
The ID of the underlying fund/company/vehicle you are trying to pull data from
-
Your personal AIRR API Key
API Endpoint
​
More information about the accessible data can be found on the API documentation page.
One example endpoint could be:
​
https://api.theairr.com/company/:companyId
​
​
Your API key can be found on the bottom of your profile page:
https://app.theairr.com/profile
​
Object ID
​
For the second requirement you can navigate to the page of the entity on AIRR you are trying to pull data for. For example here is the URL of the page where we edit information for a investment vehicle:
https://app.theairr.com/funds/f7aac1e9-d1df-4df2-b94c-dd2d08f0b312/edit
​
The section in bold is the ID of the fund.
​
Now that the connection is established, simply click "Refresh All" in the Design or Data tabs of the ribbon interface. All the latest data will automatically be pulled into the excel sheet!
If you need, you may add additional data connections to other endpoints.
​
We advise you to keep the data tables on independent sheets, and refer to them in your model on a separate sheet.
​
There are many other manipulations you can do with Excel's built-in PowerQuery tool to the underlying data, but this is the simplest way to get the data into excel with the use of AIRR's API. It is a useful way for you to generate custom charts and reports, albeit not in such an approachable manner as with a proper BI system made to import JSON feeds.
AIRR API key
​
Every user on AIRR is assigned their own API key, which grants and limits access to information on AIRR. These must be treated with care as they provide authentication to the system. They should be kept private like a password.
​
Importing information into Excel
​
Finally we are ready to import data from AIRR into Microsoft Excel.
​
​
The first step is to navigate to the Data tab on the top of the ribbon interface.
Next under the get data button select "From Other Sources" followed by "From Web"
​
​
A dialogue will appear. Switch to "Advanced" And fill out the form as depicted above.
The URL is the API endpoint followed by the relevant ID for the object you want to pull data from. You may break this up over several lines as shown above, but it is not required - everything can go in one line.
​
Under "HTTP request header parameters", we must enter the authentication information.
​
The header should be entered as X-API-KEY followed by your personal API key in the data field.
​
​
If this was entered successfully you will see a preview of the data available. To make use of this we must convert the JSON data into a table. Click "Into Table"
​
Alternately, you may drill down into the nested fields shown as "List" by clicking on them. The navigation steps are shown on the right as "APPLIED STEPS".
These can be removed to undo the navigation and return to the original table.
Once you have the information you would like to access displayed, click "Into Table" followed by "Close and Load".
Your data should be displayed as a table in a new sheet.