An API Data Puller in VBA
Using an Excel Macro to Retrieve and Dynamically Parse JSON From an API Response
I’ve used Python to pull and parse JSON from APIs. It’s pretty easy… You can either extract the bits and pieces of info that you need from the JSON response, or you can even flatten out the whole JSON itself and insert it, as a record, into a data frame/table.
In my humble opinion, if you want to whip up code for connecting to an API and parsing the JSON response, then you’re probably not going to want to use an Excel macro for that... Personally, my language of choice would be Python. However, it’s still nice to be able to do this if you’re on a computer without a code compiler or interpreter. Let’s say that you just have Microsoft Office, which of course offers its legendary automation capabilities via macros. Can this still be done? The answer to that question is yes!!
I’ll talk about my VBA script for retrieving a single JSON response from an API and flattening it out so that all key value pairs can be written to the first two columns of the Excel spreadsheet.
So… First things first. There are two requirements for this tool. One involves adding a reference to the VBA project and the other involves importing a .bas file into that same project. JsonConverter.bas from the VBA-tools/VBA-JSON repo is what needs to be imported. And Microsoft Scripting Runtime is the reference that needs to be added:
My part in developing this API data puller really came down to just making the web request, traversing through the JSON, and writing the data to Excel. To the developer(s) of the VBA-tools/VBA-JSON code repository, I say THANK YOU!! That tool for parsing the JSON was key for me to use and build on.
And now, here’s my code!
The macro-enabled workbook can also be accessed in my GitHub repository for this project.
I’m sure there may be a better way to send a web request in VBA. But a quick google search helped me find this way of doing it and it worked fine. Of course, this will only work for API endpoints that require only a very simple GET request without any need for authentication. This code would need to be improved in order to handle more complex API calls.
The real work was in figuring out how to dynamically traverse the JSON so that the data could be written nicely to Excel. My good old days of taking algorithms back in college came to mind. And I landed on coming up with a recursive routine. I did my very best to make this workable for most, if not all, JSON data. There’s no hard coding of specific keys or indexes of data to pull. And it flattens the data by concatenating the current key with all parent keys for data that’s in deeper levels of the JSON. For lists, I concatenated the “key path” with an index value. And this will even work for list items that also happen to be dictionaries (except for when the whole JSON is encapsulated as a single item in a list). Here’s an example of what gets written to Excel for a GET request made to this API endpoint taken from the README in the SpaceX-API repo (note that the JSON response shown in that repository’s README is outdated): https://api.spacexdata.com/v5/launches/latest
I’ll end by saying that macro-enabled Excel workbooks are great for when you just want an all in one solution that’s purely in Excel. You could write Python that’ll spit parsed JSON into Excel. But then again… that requires an installed Python interpreter and running a script outside of the workbook. Not that that’s hard… But with this, it’s as easy as opening up the workbook, kicking off the macro, and then viewing your data. All in one commonly used application!
That can be convenient at times…
