How to use Excel to get API data from any source

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

There are many API’s available on the Internet that can provide great and timely data in ways that Excel can understand, here’s how you can do it.

We’ve recently received emails asking how to use Excel to import data from specific API’s.  Always we have the same answers because most API’s work in similar ways and output data in known formats that Excel can understand.

The good news, no programming or VBA code is required.  In most cases, modern Excel for Windows with PowerQuery can handle it all for you.

What’s an API?

API = Application Programming Interface.  Sounds complicated but it’s a simple concept.

We humans type a web link into a browser and get a web page back.  An API is just an automatic version of the same thing.  Your software (such as Excel) sends a web link and gets back a ‘web page’ in a machine-readable format.

It’s an automated way to receive and send data to a web site.  Often the data flow is one-way, sending specific data to you.  Sometimes it’s two-way where your software sends a request, like a buy/sell request and gets back a result for that action (like price, date, time, etc).

We’ll limit ourselves to simple ‘one way’ API’s which request data in a web link and receive information back.  We’ll explain the basics of asking for data (a special web link) and formats for the incoming data (JSON or XML).

Our ebook Real Time Excel has many working examples of API’s with Excel for example getting the latest weather and forecast for a location.  Also stock prices and currency rates for older Excel’s which don’t have Linked Data Types.

Asking for data

API’s accept requests via a web link. Use the correct link format and the API will reply with the info you want.  Consult the API documentation and examples.

Most API’s require some form of authentication or identification.  That’s where you’ll see talk of an ‘API key’. Usually you create an account then get an API key unique to you.

Here’s a simple example with a fake API key. OpenWeatherMap.org has an API to get weather info for a location (London, UK in this example) plus an appid to identify the request.

http://api.openweathermap.org/data/2.5/weather?q=London,uk&appid=B5Forever

Real Time Excel goes into a lot more detail about the OpenWeatherMap API, which has many options. The book also includes a sample Excel file to open and try for yourself.

Some API’s have additional authentication requirements, check the API docs for details.

Receiving data that Excel can understand

Testing an API and its results can be very easy.  Just paste the web link into a browser and see if some useful text comes back (i.e. not an error message or nothing at all!).

Most API’s return data in one of two common formats. Both are plain text with strict formatting that Excel understand and can parse into a table, rows and columns. PowerQuery is the best way to do this because it handles a lot of the work automatically.

JSON

JSON is popular, compact and standard format for exchanging data across the Internet.

Source: Wikipedia

We imported the Wikipedia example into Excel via Data | Get and Transform.  PowerQuery detected the JSON format and imported without any fuss.

XML

The other likely format for API data is XML which is also plain text but somewhat bigger with all the </????> end tags that JSON doesn’t need.  Here’s the XML version of the same JSON example.

Again, Excel PowerQuery will gobble up the XML and make it available as tables in Excel.

API’s with Excel have a lot of help

If you need help getting Excel to work with a specific API, there’s usually a lot of online help available.

Excel is such a common tool that most API documentation will have a section devoted to Excel integration. Failing that, there’ll be a developer forum for that API, Look in the online discussion for help or ask a question.

Don’t get scared by all the tech talk and acronyms like API, JSON, REST, XML and what-not.  The fundamentals of using an API are simple and modern Excel handles a lot of the conversion of data for you.

Real Time Excel

More linked data types coming to Excel 365
Be careful with Excel 365’s Linked Data Types

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address