API Interaction With Excel

Click to Download Workbook: API Interaction

This post will serve as a two in one tutorial to those who have never used Power Query or UserForms within Excel. The goal will be to try to interact with an API through a get request in order to obtain data in our spreadsheet. In this case, we will be using the Zacks Investment Research API for stock data. The response will come in the form of a JSON (JavaScript Object Notation) file, which can be dealt with using Power Query. The JSON data format is essentially an associative array with multiple key and value pairs in order to access different elements of what is known as a dictionary. In this case, we have three key values we can use to select different frequencies of dividend data.

We want to make it so that we can run a new query every time a ticker symbol and frequency is passed in as a string argument to the URL. We will first run a macro while using the Power Query feature of Excel in order to get the foundation of our script that will be modified with string arguments. We have to keep in mind that we still need to deal with the QueryTable and connection when we get this data into Excel. We also need to account for invalid tickers as well. There is a simple error handling statement near the bottom of the script that deals with queries that don’t return data. Note we handle column names and data formats within the Power Query Editor which can be seen in the video above.

We can then create a UserForm to pass in variables to modules within the workbook without having to use cells in the worksheet as a reference. Though I added a bit more style to this UserForm, it will look something like this:

We first need to make a routine to launch the UserForm. Next, we will handle dealing with all the buttons and their functionality. When we close the UserForm, we will remove it from memory with the Unload statement. We will pass the variables in indirectly from the other save button in the UserForm after the user enters the desired information in both of the boxes respectively. The frequency box is a drop-down list that will be populated once the UserForm is initialized. Below is every aspect of the UserForm excluding the two individual input boxes on the form.

The end result will be our data outputted to cell A1 in the worksheet. PowerQuery can be really useful when dealing with different data formats and API services as well.

About the author


Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts


Leave a Reply

Your email address will not be published. Required fields are marked *