Use Excel VBA to Read API Data

today i'm going to show you how to supercharge excel vba using web apis web apis may seem a bit daunting at first but by the time you finish this video you'll be able to read data from any web api and use it in your excel vba application make sure to watch to the end of the video as in the final section i will show you a simple way to write the code for any web api all the code used in this video is available to download from the link in the description below now let's go ahead and get started if you liked this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it what exactly is an api and why do we need it well an api is a programming interface that allows us to interact with applications and primarily nowadays it's web applications so for example twitter youtube and all other major web applications have apis that allow us to interact so what's an api in simple terms it's simply a group of functions that allows us to interact with the application imagine we have some kind of social media website we could do things like get the subscriber count add a subscriber delete a subscriber and so on so how the api works with vba is as follows so we send a request to the api and then the api sends us a response so request looks like this it's made up of four parts it's made of the url so this is basically what we're connecting to and when you go to an api documentation it will tell you this the next thing we need is the function name which is the function that we want to call and most of the time the function will take parameters but sometimes it doesn't require parameters so it's very similar to how we use functions in any program and language and finally we use an api key so an api key is like our customer id it uniquely identifies us when we're accessing the api now we often pass this as a parameter but we can pass it in other ways as well so the response when we get the response back in vba it comes back in json format and what we do then is we convert this to a dictionary structure so json format is essentially a list of key value pairs so you can see here we've got currency so currency is basically the key and cad canadian dollar is the value and again amount is the key and then 127.69 is the value now in some cases what we'll have is a more complicated or a bigger set of data that's been returned and so what we'll have then is collections within collections so here you can see we've got a country and the country has a list of cities and within each city then we've got the actual details such as land area and founder and we convert this to dictionary we have our dictionary of the country and then under cities there's another dictionary which holds the cities and it holds basically a dictionary of the details now if you're not familiar with dictionaries then check out my playlist on dictionaries because it's very important that you understand them if you're already familiar with them don't worry about it being too complicated as it looks here because actually once you start using them it's quite straightforward so using an api in a nutshell we send a request to the api we receive a response in json format we convert json to a dictionary structure and then we simply read from the dictionary and write to our worksheet or wherever we want to place the data so now that we understand what exactly an api is let's have a look at two examples that i've created and after that we'll start writing code and we'll see exactly how we can use apis in vba in this first example you can see what we're doing is we want to get back currency rates so what we do is we pass the two parameters usd and amount and we click on get currency rates and this returns us all the currency rates so this is one example of using a api now we can change our parameters as we want so let's make a canadian dollar and then we run it again and you can see that we've got back our set of rates and amounts so a second example is recipes so we select a recipe let's just pick caesar salad for example and it returns us to calories fat and protein for that recipe so these are two very simple ways that we can use apis so once we get the data back there's many different things that we can do with it now that we understand what an api is and we've seen examples of how it works let's go ahead and start writing the code we're going to start by writing code for this api so this api is very simple it's just got a url or endpoint and on the right hand side from the question mark on is the parameter so we simply pass it the name as a parameter and it returns us the three most likely countries the person is from as well as the probability that they're from that country so if we run it from the website itself you can see the results that we get so this is useful because now we know the format that our results is coming in i'll just make this a little bit bigger in notepad so we can see it it gives us back the name michael and then it gives us back the country which is a collection the country is a collection of dictionaries with two keys and each dictionary has the name of the country and the probability so let's write the code and see how we can use this data ourself the first thing we need to do when we're reading from an api is we use a library and that's the win http library so we go to tools references and we go down to microsoft write down and you see it here microsoft win http services version 5.1 and we click ok and then we say dim request and that's just going to be request as new win and it's going to be win http request once we have that request what we want to do is we want to open so we want to initialize it and then when we're finished initializing what we do is we just send the request now open takes a parameter it takes two parameters the first parameter is what we want to do and we're going to do get which means receiving data and then the url that we're going to use so if we go back to the website we can just take all of this here and this is our url now as i said before what's on the right hand side here from the question mark over is our parameter so we can change that to a different name if we require we hit request send and that will send the request now what we want to do when we do a send is we want to check that what we got back is correct because sometimes it will fail and it could be because we did a wrong parameter or something like that so we say if request dot status does not equal and that should be 200 then what we want to do is give a message box with with the error so request and that will be response text and then we exit the sub because we don't want to carry on any further now if it's okay what we want to do is we want to get the response back so we get the response we say it in response as object and the response is actually as we just saw above it's in a response text but we want to convert this from json and we want to make it into a structure of dictionaries so we do set response equals now how do we convert it so we could write the code for this but obviously it'd be a huge undertaking so what we do is we go to github so if you're not familiar with github it's like a programming database of code snippets for all different languages and you can go and use the code there generally for free so if you go to vba tools this you can see there's lots of different files here but what we're going to do is just download the code so we download the zip and that's going to we're just going to download it into our folder here and let's open the folder or show in the folder router you can see that we have it here now if i open it here all we need in this is the actual this file this basic file all the other files we don't really need them for anything they're just different configuration and stuff so we can just drag that here and drop it into our folder so what we do with this one to use it is we can just drag it like this and drop it into our project and we can do this with any project we want to use it now one other small thing we want to do is debug if we do a debug compile you see we get an error and that's because it doesn't recognize dictionary so what we've got to do is we've got to add the scripting runtime for the dictionary so scripting runtime is the library that we always have to use as a reference when we're using the dictionary so again this is microsoft scripting runtime you see it here now if i do a debug compile you'll see that it doesn't give that error it's given the error on this line of course because we haven't set set it yet so so now to use the json converter we just say json like this json converter dot and we want to parse json and we pass it in the json response text and this will give us back a structure now we're going to access the data that we got back so you may be wondering how do we know what the structure looks like and where do we even start well most apis that you'll deal with will actually give you an example of what the response is like and in this case if we click on try me here it'll actually show us the actual response so you can see the response here is in json so what would this look like when it's converted to a proper data structure so i just adjusted the very same data here and you can see exactly what it will look like if we look to the left here what you'll see is that we've got name and michael so name is the key michael is the value and the second one country is the key and you can see we've got all this data here that's the value so if we look at this data you'll see that we've got three similar parts so country is what we would call a collection so we've got a dictionary and in the dictionary we've got country and then country has a collection of other items and if you look at these individual items you'll see that each one is a dictionary it's got a key and value pair so we've got country id and we've got the country and then we've got probability and we've got the probability value we have this in three cases we've got a dictionary the dictionary has a collection of countries and each of the countries is a dictionary let's go and write the code to extract this data and it's not as difficult as you may think the first thing we're going to write in the code is the response name so response name as we've seen gives us back the actual name that we've supplied now the second key in the top dictionary is country and as we saw this is a collection of countries so we're going to create a collection we're going to call it countries i'm going to say dim countries as collection then we set these countries equal to response country so now we've got our collection countries and what we'll do is we'll read through this country's collection and take each dictionary one at a time so we declare country as a dictionary and we say for each country in countries so each time in this loop we have country and country is the dictionary now that we have this dictionary what we do is we print out the country id and we print out the probability and as the probability comes as decimal we can use format percent to format it into a percentage and that's all we've got to do to read this data so now let's go ahead and run the code and you'll see we got the expected results we got name we got the country and the probability and there's three of these results so let's try it again with a spanish sounding name so that will get back a different selection of countries we run the code again and you can see that we got back the name and got back the different countries and different probabilities so this is how we use a web api at reback the data and write it out somewhere you may have found dealing with the structure a bit complicated so what i'm going to do is i'm going to break it down here into very simple terms that you can understand now normally in vba when we're dealing with a dictionary we have a simple key pair like you can see here so we could have something like fruit and then we have the key name which would be apple orange plum mango and so on and the value would be maybe the number of items that we'd sold or it could be the cost of each one now a second example here is where we have the number and this could be some kind of list of people that we want to process and for each one the key is a number and then the value is the person's name so these are simple versions of a dictionary now can use dictionaries in a more advanced way and in this case you can see the dictionary on the left it has the name and the name is the name of the person it has a city it has a balance which is a currency it has the date joined which is a date obviously and you can see it has different data types now the purchases at the bottom of this dictionary is actually another dictionary and then we look at the other dictionary and you can see this dictionary is similar in that it's got a list of keys which are the items that have been bought so for example a toaster a tv cable these are the keys and then the value is the amount that was spent on each one now if we go back to our api our api looks like this we've got a simple dictionary that is a name and a country now the country has a collection of countries and this collection each item in the collection is a dictionary and you can see the dictionary is country id and probability so we've got a list of dictionaries so this is what we do when we're trying to access the different items in our structure we basically have to figure out what way the structure is first and then we can easily access the items i'm going to break down the structure to show you exactly how it works so the top of our structure we've got a name and we've got country so these are the two key value pairs name is simply a string and country is as we saw a collection so we're going to print out the name just going to do a debug print and print out the name now because response country is a collection what we're going to do is we're going to create a collection variable so we'll say dim countries as collection and then we'll assign this collection to response country so even though they're the same using a collection makes it more readable makes it more obvious what exactly it is now once we have a collection in vba we can read through it and we know our collection is a collection of country dictionaries so we say for each country in countries and this will return us a dictionary each time and once we have the dictionary which is the country we know each of these dictionaries is made up of the country id and the probability so we simply print these out now when we run this code what you see is that it printed out the name and it printed out the countries and their respective probabilities so you can see writing the code like this makes it much more obvious what we're doing now if you find the code still a bit complicated don't worry it's always going to be some mixture of dictionaries and collections but mostly dictionaries so once you can do the basics with the dictionary it's pretty straightforward to do it now i'm going to show you a super fast way to write the code for any api so we're going to go to the rapid api website now the rapid api website is a marketplace of apis and how the apis are presented is very standard so it makes it really easy to use any of these apis so we're going to look at the alpha vantage api and this is an api that returns stock prices so on the left hand side we've got the functions you can see stock time series and these are the different functions when you click on any one of these it shows in the center the different parameters header etc that it takes and on the right hand side we can see code snippets and we'll be using these in a few moments and you'll see how easy it is to use them to create the code that we want so let's go back to vba for a moment and what we're going to do is we want to take this existing template you can get this template by downloading the code from the description below the video and we've got the template we're going to paste it into our module as you can see i've just done if we switch back to the rapid api website and we get go to the code snippet on the right hand side so then we go to javascript and xml http request this gives us code that's very similar to the code we're using in vba and all we really need here is the open and set request header statements because all the other code in our template so we just paste the code here and then we can use it ourselves so you can see their open statement is very similar to the one we use below and i'm just going to split this into the url and the parameters so the first part i'm just going to put a url and a second part of parameters now we could use it all as our get string if we wanted but i just like to split them up to make it a little bit clearer and now we've got our open string ready to go so we delete this and now we've got to do our request headers now we haven't used these before but if you need a request header it basically takes a key value pair now when we paste it down here all we've got to do is replace their variable with our request variable and then get rid of any of the parentheses so get rid of the parentheses at the start and the end and the semicolons and that's all the code we need to request data from the api what we need to do now is just run our code and then figure out what the structure that we get back is so you see here it says sign up for api key so when you sign up to rapid api you get your own api key and you can put it here now i'm calling mine from a function because obviously i don't want to display it now if we want to see what the return looks like so what the data we're getting back looks like we can go back to the rapid api again and if in the chord snippet section we click on example responses it shows us exactly what we're expecting back so you can see we've got two items in our dictionary we've got a time series and within this time series we've got a selection of intervals and each of these intervals has an open high low close and volume so it has all the values for these so we want to get these for each of the time intervals so how do we do it well we first of all we create a dictionary so we get the dictionary from the time series 5 min that you see here and if we use that within response so response is our converted json if we use that here we can set it to be a dictionary so we're going to call our dictionary prices and now we've got our prices dictionary so our prices dictionary as we've seen has a series of intervals and so we're going to read to our dictionary we're going to use a far each loop so we declare our key as a variant and we say for each key in our prices dictionary and then we get access to each item in the dictionary using prices and the key so that gives us the current object and then we create a second dictionary or we declare a second dictionary rather we call price and we set price equal to whatever is at the current key so remember what's that's the current key is a dictionary with the open close volume prices and values and then we can get access to this and we simply say one dot open and that's the price so that's the key for the open price and we want to get the close price which is for close and we'll print out the key as well so let's run this code and you can see that it printed out all the interval times as well as the open and the closed price and you can see that i created the code for this api in just a few minutes and it was just a matter of copying the code snippet pasting it here and then just figuring out from the response what we were looking for let me know in the comments below if you've got any questions about what i've done here today and if you want to download the source code make sure to get it from the link in the description below the video if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it

Channel: Excel Macro Mastery

Views: 28,087

Keywords: ExcelVBAAPI VBAAPI WebAPIVBA

Id: KZeYKZJzQIk

Length: 20min 48sec (1248 seconds)

Published: Tue Oct 19 2021