A Data API is a way of easily accessing data. The Data API comes in the form of a URL web link. Software applications can use that link to download data. In this article we'll use Excel to download some data using a free Data API.
Example scenario
I would like a list of all the countries in the world and their 3-digit ISO codes. I might want to use this information to combine with my own data for analysis. I could of course go to Wikipedia and download the list but that would be a static list. What if any of the data is updated in the future? It would be great if I could click Refresh and get the latest information. This Data API will allow me to do that.
The following website provides a free Data API for country information:
We'll use this Data API for our example here but there are many different Data APIs available.
Instructions
In Excel click Data (menu tab)
Click From Web (or click Get Data | From Web)
Enter the Data API URL. In our example we'll retrieve only two fields of data; the country name ("name") and 3-digit ISO code ("alpha3Code"):
https://restcountries.eu/rest/v2/all?fields=name;alpha3Code
NOTE: More data fields can be retrieved, just add a semi-colon and the field name. We will explore this more, later in this article.
Click OK and Power Query opens...
On the Transform tab click To Table
Click OK
In the Column click the <-> icon (as shown above)
Click OK
Now you'll see the data in Power Query Editor:
Click Close & Load
A table will appear in your workbook:
The table is like any other Excel table. Use the data whichever way you like.
Click Refresh to update/fetch the data from the Data API.
More fields
The Data API URL from restcountries.eu can be modified to give more data. In principle this is how other Data APIs work too. Near the end of the URL it says fields=. The field names are listed with semi-colons to separate them.
To fetch the country name and ISO code:
https://restcountries.eu/rest/v2/all?fields=name;alpha3Code
To fetch the country name, ISO code and capital city:
https://restcountries.eu/rest/v2/all?fields=name;alpha3Code;capital
To fetch everything in this data set:
https://restcountries.eu/rest/v2/all
Remember that the more data you retrieve, the slower it will be. Always try to restrict your request to the data you need.
TIP: If you want to test a Data API, paste the URL into your browser. If it's working you'll see all the data displayed (although difficult to read!).
Excel version
I used Excel 365 (May 2020) in this example. However, it is likely older versions of Excel will work the same way or similar. Feel free to write comments below to help others.
Conclusion
Like many things this is easy once you know how. There are many public data sources available, just do a search and and you'll find many, sometimes free like the one here, others free but you must register (you need an ID to access the Data API) and others where you must pay (subscribe to a service). Of course you might also use this to easily access your own corporate data - ask your IT department!
The best thing about Data APIs is that they can be updated easily and they are pretty easy to set up. I hope this article has been helpful, at least to get you started.
If you want to learn more about APIs, have a look at this video, it's pretty good for beginners:
Disclaimer
The Data API I used here as an example, it is just that, an example. I have no affiliation with that Data API site. I just used it because it was free to the public and had data that was good for this demonstration. If you find errors in the data please address any issue to that website and not to me. Everything I have written here is without guarantee, I'm just trying to help with this article :-)
No comments:
Post a Comment