Hands-on with Enin - Part 2/3: Getting knee deep, as we integrating Enin's Datasets API with Excel

In this three part series, on getting "Hands-on with Enin", we'll show you how to get the most out of Enin's toolbox:

• Part 1: Getting our hands dirty, as we screen for growth tech companies using Enin's Company Browser

• Part 2: Getting knee deep, as we integrating Enin's Datasets API with Excel

• Part 3: Installing the pipes, as we integrate Enin's Datasets API with Alteryx

About the Enin Dataset API

The Datasets API focuses on delivering large amount of data for data analysis and data warehousing. It uses "HTTPS requests", which by now is the defacto standard for most API integration on the web. That said, the underlying infrastructure is made somewhat more flexible than most APIs, and is highly optimized for data extraction.

The Datasets API consists of multiple "endpoints" which are divided into logical groupings. As of January 2021, the following groupings of Datasets API endpoints are available:

Each endpoint is a web address pattern, or URL, which can be modified to get various data. These are in essence search queries. A basic example could be to query for companies like this:

https://api.enin.ai/datasets/v1/dataset/company?limit=10

This would return 10 arbitrary companies as a JSON file. By adding &order_by_fields=company.name and &response_file_type=csv to the end, you can sort them by company name and get the a CSV file instead:

https://api.enin.ai/datasets/v1/dataset/company?
limit=10&order_by_fields=company.name&response_file_type=csv

(remember to remove any "whitespace" or "newline" characters if you copy-paste the above)

And much, much more. For now I'm not going to bore you with the details of the syntax, and just focus on the use cases it can solve. For the technical details I recommend reading this Datasets API tutorial.

Connect the Datasets API to Excel

For this tutorial we are using the Microsoft Excel Version 2012 from Microsoft 360 MSO.

Excel is often an underestimated platform. There are numerous integration options which give you practically unlimited options for analysis. It is just a matter of knowing where to look.

Let's get started with the integration.

Go to the "Data" ribbon in Excel, then click "From Web". Alternatively navigate via "Get Data" > "From Other Sources" > "From Web":

This will bring up the "From Web" dialog, where you enter the URL endpoints we talked about earlier.

This will bring up an authentication dialog. If you haven't received an API credentials from us yet, request access here. Enter your "Basic Authentication Client ID" and "Basic Authentication Client Secret" and choose https://api.enin.ai/ as the "Level". This will make it apply for other endpoints too:

When you now connect you will get a preview of the data:

By clicking "Load" you will get a new a sheet with a data table connected to a Query:

If you got to this point you now have Enin's data at your fingertips.

Something which might be worth mentioning is how to change or remove the API credentials from excel. Go to the "Data" ribbon, then "Get Data" > "Data Source Settings...":

... then choose "Global permissions". This will give you the option of editing the permissions or deleting them entirely:

Syncing with your watchlist

Alright, now we know how to integrate an API endpoint. Next let's fetch some numbers to work with. If you remember from last time, we created a list of relevant companies. And we exported this list to excel and could continue working on it from Excel. That is neat and all, but how do we keep it updated?

Exporting Company Browser data to Excel is really useful, but it is cumbersome if you want to do the same operations often. Syncing your watchlist using the Datasets API might be easier.

Well, the list we created could be saved as a watchlist instead of being exported:

If you look closely at the top browser address line, you can see that the watchlist has an "id" of af2f21ab-19c0-4a01-be70-0465d11056f8 which can be used as part of the API endpoint URL. Let's fetch some accounting numbers using the following URL:

https://api.enin.ai/datasets/v1/dataset/accounts-composite?response_file_type=csv
&watchlist_uuid=af2f21ab-19c0-4a01-be70-0465d11056f8
&accounts.accounting_year=2019
&keep_only_fields=
    company.org_nr,
    company.name,
    accounts_highlights.income_statement__operating_revenue,
    accounts_highlights.income_statement__ebitda,
    accounts_highlights.balance__cash_and_deposits

(remember to remove any "whitespace" or "newline" characters if you copy-paste the above)

Here we specify what watchlist to use with watchlist_uuid=af2f21ab-19c0-4a01-be70-0465d11056f8 and that we want the accounting data for 2019 with accounts.accounting_year=2019 and that we want revenue, EBITDA and cash as fields using the keep_only_fields parameter. An example of a field would be accounts_highlights.income_statement__operating_revenue where accounts_highlights is the entity/table in the Enin systems, and income_statement__operating_revenue is the data field.

If you don't know what fields you would like to keep, you can always just omit the keep_only_fields parameter and get all of the fields, then based on the fields you get, you can select those you are interested in.

Lets go ahead and use this URL by going to the "Data" ribbon, then click "From Web" and enter the above URL into the dialog box:

This time, however, we need to do some changes to the data request, so click "Transform Data":

This brings up the "Power Query Editor" which gives us fine control of how the data is fetched and transformed before it ends in the spreadsheet. The reason we need to change things here is that the API returns standard formatted numbers using periods (.) as decimal points, instead of a commas (,) which is common in some parts of the world. My Excel is configured to use Norwegian "culture" as default, so I have to override how Excel interprets the columns we are importing.

In any case, we do this by selecting "Changed Type" on the right panel, then update the formula at the top panel, so that the accounts columns are set to type number. However, this is not enough because of the period (.) decimal points. To fix this, we can force Excel to accept normal periods decimal points by adding "en-US" as the last "culture" parameter. When this is done we can "Close & Load" the query editor:

This adds another sheet with data. Now we can do things like plotting the data, filtering, sorting, etc. in Excel itself.

If we sort by cash_and_deposites  we can see there are some companies with a lot of cash available. Let's say we aren't that interested in these companies and would like to remove them from our watchlist:

Let's head over to our watchlist in the web portal and lookup the companies to remove them from the watchlist:

Now, if we had used Excel exports instead we couldn't have kept these synced, but now we can refresh the query in our excel sheet and the data will be updated automatically:

As you can see companies with more than 10 MNOK in cash reserves are gone.

API filtering

One thing to note is that you don't have to filter on a watchlist, it is just one of many filter operators.

You are free to use any filter you would like on any available data field.

The available filters are as follows:

In the previous example we removed the companies directly from the watchlist. We could also have just filtered them by modifying the URL, without altering the underlying watchlist.

Let's continue with the previous example: Say we want to remove companies with less than 0.5 MNOK in revenue. Then we could add &accounts_highlights.income_statement__operating_revenue=GTE:500000. Here GTE is an abbreviation for "Greater Than or Equals", meaning we want to only keep companies with more than 0.5 MNOK in revenue.

https://api.enin.ai/datasets/v1/dataset/accounts-composite?response_file_type=csv
&watchlist_uuid=af2f21ab-19c0-4a01-be70-0465d11056f8
&accounts.accounting_year=2019
&keep_only_fields=
    company.org_nr,
    company.name,
    accounts_highlights.income_statement__operating_revenue,
    accounts_highlights.income_statement__ebitda,
    accounts_highlights.balance__cash_and_deposits
&accounts_highlights.income_statement__operating_revenue=GTE:500000

(remember to remove any "whitespace" or "newline" characters if you copy-paste the above)

If we refresh the queries in Excel you can see that all data points below 0.5 MNOK are gone:

However, they still exist in the underlying watchlist. This will only affect your Excel sheet.

Protip: You can use the hotkey "Ctrl+Alt+F5" to refresh all queries in Excel.

There you have it: Excel integration with the Enin Datasets API. In the next "Hands-on with Enin" blog post we will integrate with Alteryx using the same Datasets API. Using a tool like that gives you unlimited control and integration possibilities.