Import Web Data to Excel using VBA Macros

hi guys here I'm gonna show you how to import web data using VBA macros in Excel this one's gonna get interesting but don't worry I'm gonna cover everything step by step and go through the macro line by line so that by the end of this tutorial you understand exactly how to do everything and anything I don't have time to show you in this tutorial will be in the premium course on teacher SOUTHCOM before we start check the video description and click the link to teach excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials first thing to do let's go find some data to import so go to our web browser and I'm gonna stick with a financial data since it updates so it makes for a nice example in Excel let's go to finance yahoo.com and let's see here after hours so let's do the euro USD exchange rate because we have a number that will change and this time unlike the last tutorial with excels stupid web import we are not limited by the table interface because if you remember from that tutorial we could only pull in these guys down here this one and this one now let's get what we really want which is this guy right here so this tutorial will show you how to pull almost any piece of data that you want from this web page so this is what we're gonna get but what I'm gonna do now is we're gonna go back to excel and I'm going to show you the macro that we're gonna use I made it super easy to follow and now let's go to the VBA window alt f11 now I'm gonna increase the text size and let's go ahead and close these dudes okie dokie I'll go ahead and download this file from teach Excel calm so you can follow along with me I'm not gonna force you to write it out by hand all of this what you see this is the macro you can see that it's not that big and I have it heavily commented so now it's called get web data and okay back up to these guys so these are the variables for the macro and yes as you may have heard me say before technically speaking you don't need to list these up here it is good practice it's not required in most cases but it's good practice so I went ahead and did it for this one we've got something that is going to send our request to the website something to get the request back something to hold the HTML then a variable to hold the website we want to go to and the data we want to get which is price now we can move into the meat of the macro and the first part here is one of the few things you're gonna have to change to work for your own macro so we have the website variable and what we're gonna do here is just store the website that you want to go to so this is the URL of the page that has the data you want so this is a URL from earlier where I was testing it with another stock or just a stock and what you want to do here for this one very simple we just go back to our browser go up here to the URL and copy the entire URL including the HTTP now we can go here and delete everything within the double quotation marks and paste our new URL perfect the next line as it says here create the object that will make the webpage request don't worry about this you don't have to do anything with this all that it does is it creates the object basically the thing that's going to take our values from the macro and then go into the web to do something with it so this website is our value here it's going to use that to go to the website so set request equal to an object and now that request equals this object right here we can use request down here and do some things with it so the first thing that we want to do is we want to open the request so I've got this right here and I have these things sectioned like this because in most examples they are within a width block I don't have that here because I thought it might be a little bit easier to just write it out like this and if you don't understand what that even means then don't worry about it just know that I wanted to make the code as easy as possible to read so now we have our request object and we got to do something with it so the first thing that we want to do is to go down here and type request dot open and that's basically what gets everything started and you don't need to change any of this so I'm gonna say 98% of the time you're not going to need to change any of the arguments here this get is the type of requests that we're going to make this right here is our website so our website variable you could just copy paste this here right here but I think it's a little bit easier to edit to macro this way and this right here controls if it's synchronous or asynchronous do not change this if you change this you're going to need to change the rest of the macro leaving this as false is going to make your life really easy so if you don't know even what asynchronous or synchronous means then don't worry about it just leave that the way it is and your life will be easier okay let's add a little space here a little space here to this guy right here is a fun little thing so when I send or when the macro sends a request to this website it says hey this website right here give me some data but it doesn't only say this it says a bunch of other stuff to it in what's called a header I don't know how much you want to know about that probably not too much but right here is where we set the request header so here's our request and then dots that request header and so what we are doing is we are telling the website this right here and what this is is it says if the website has been modified since this really early date which it should have probably been modified since then send us the new data but in layman's terms this line right here once again don't need to do anything with it it just means we're going to get fresh data you can comment this line out and see that your data won't update the way you want it to so that's if you run the macro once and then keep running it over and over again next thing very simple we have to tell the request to send so we do request dot send now we've sent a request we're gonna wait on a response all that's done behind the scenes no problem and then down here is where we're going to get a response technically you don't have to convert the string here into Unicode don't worry about that it's just to make things a little bit easier with character encoding but basically what this does here this request dot response body is what actually gets the response and then we're gonna put that into a variable response STR Co NV just converts it from one encoding to another and this is what says the the encoding to convert to but like I said don't worry about this this is the part that gets the data this is where the data is now stored now what we need to do is to kind of put it in a different format so we're going to take this response over here and we're going to type HTML body inner HTML equals the response that way we can then use the HTML variable to access elements from within the webpage so if I go up here the HTML variable is an HTML document type and that's a special type that allows you to essentially hold a webpage and iterate through different elements within the webpage so that's a special little guy right there and you don't have access to that by default but I'll show you how to get it in a moment so now once we have an HTML all we have to do is figure out what we want to get so this is the second part that you're going to have to edit here we have the price variable that's going to store our price here we have HTML we can now use that because we set it right here and we can use these little guys right here get elements by class name and there are a couple more I'll show you in a moment and this is what allows you to get the actual data so what we're going to have to do in a moment is go back to the web page find the class name for the data we want to pull in in this case it's a really weird class name because it's from Yahoo or any big company is going to have weird class names like this and then once we get the class name we need to do some funky stuff over here that I will explain once we come back to the macro and finally the easy thing we're going to output the price in a message box so we can see it okay now let's go and find the class name so we need to go to our web page there it is alright now very easy if you're in chrome this also works in Firefox you can find the identification for any element in a web page if you right click it and you go to inspect now here is where I'm going to tell you a little bit about HTML I'll try not spend much time as I sometimes do let me get this guy down here okay so you'll see these flashing that means that it's that it has been updated so it's not a big deal all the static elements they aren't going to flash this is when the price updates don't worry about that though the important bits of data for us are what allows us to identify this piece of information so look to the left here when I move the mouse you'll see that it highlights different sections on the web page and if I look to the mouse on the right you can see so this div highlights the price and the percentage change and all of that but if I go down a little bit it's just the price a little bit more just the percentage change so this allows you to identify the section of code over here that you need to pay attention to and when you right-click the item in tight and hit click inspect element it's going to usually not always but usually it's going to highlight the exact element that you need to pull in so here we need the span and for the span we have a class now a lot of examples will tell you to pull in an element by the ID because that is easier however most modern or at least large websites where you're going to be getting data like this don't use IDs anymore they use classes now don't worry about why but the one thing you should know is generally speaking an ID should only apply to a one single element within the webpage so the ID will only be used once that's what I mean but a class can be used multiple times I mean the same class can be used multiple times the same ID should only be used once in a webpage okay that kind of sounds confusing but the point is webpages have switched from using IDs to using classes to make their life easier so let's get this class right here all you have to do is go in between the quotation marks double click once and it's going to select all of it and that's exactly what we want then we're gonna hit ctrl C to copy that and you want to get it by the way in the smallest element the element that is closest to the price so we're getting it from the span tag right here because that tag is what directly contains the price I don't want to get the class for this div right here or this one or this one div elements are used to structure webpages but I don't want to get the developments because then I still have a bunch of other crap inside them that I don't want get the span it has only the data that I want the price now that we've got that I've done enough talking let's get off the webpage go back to the VBA window we have our class allows us to identify the data that we want now let's go here in between the quotation marks delete that and paste this in now remember that I told you I didn't do a great job explaining it but I tried to explain the classes can be listed multiple times on the same webpage so the same class but an ID cannot as a result when we pull in a class get elements you see it's get elements by class name there could be a lot of elements so in this case we only want one of them and what we do here is we want to get the first element and to get the first element in an array not always but usually you place a zero in there so what we've done I'm gonna go ahead and delete this is here we've gone to the HTML on the web page we're getting all of the elements that have this class name and as far as this code is concerned there could be many elements with the class name so we need to tell it which element to get so we tell it to get the first one which is identified with a zero so open parenthesis zero close parenthesis you could also write this dot item zero like that so if it's easier to remember you can write that item zero like that or just with the parenthesis then we can ooh innertext and that's going to get the text within that specific class now it's a good thing to make sure I almost forgot to mention it's a good thing to make sure that you only have this class once within the code over here now I know this code is only going to have it once but you should probably check this for your webpage so I'm gonna go over here click anywhere just to make sure it's active hit ctrl F we have a little search window down here and I find sometimes it's a bit finicky but it can work so we can type in the class and see how many times it found this class one of one so it only found it once but if it found it let's say five times and we wanted to get the second occurrence of this class then we would simply change the number over here where are we number over here from a zero to a one now this example you're not going to have to do that you're not gonna have to worry about that but it can get much more complicated and I will go over many more complex examples of how to pull in multiple element elements and things like that in the premium course but now we have our webpage we identified the data we want we understand the macro probably more than we want to understand it we got the class we understand HTML more than we want to understand so let's run the macro but wait it's not going to work just yet there's one more thing you have to do this is really annoying I have to say we go up to tools references and we need to make basically I'm gonna say Excel smarter so we need to give it some more information what you need to do is you need to go in this list it will not be at the top of the list but it is in alphabetical order and find Microsoft XML then V 6.0 and then also find Microsoft HTML object library now these are the two the HTML object library you for sure need XML one you're going to need for a lot of things perhaps not this particular macro and if it still doesn't work when you have those two things checked just look at the other things that I've checked here and go ahead and check them because I've had them checked for quite a while and it's hard to remember which one applies to which so if you have all of these guys check like I do then your should work just fine now you may have different version numbers it might not say Microsoft Office 16 object library or Excel 16 object library could say a different number that's okay it has to do with the version of Excel essentially that you have and the same with XML that might be a different version number but don't worry about that so check all those hit ok and now we're ready to run it so I'm gonna go ahead and run the macro just from here but first let's check oh yeah let's hit play and you can see it's running it running it 1.1 3 2 - all right let's go back to the browser and it's not exactly that price because the price is changing rather rapidly but that's all there is to it so we got it in there let's try it out again let's see if we can get any closer it's now 13 18 see 13 18 let's see if we can do it 13:22 so 1.13 22 so two things to note the website that I'm using does not have up-to-date data and the browser isn't going to get the most up-to-date data because it has to wait to refresh and the macro itself isn't going to get the most up-to-date data because it takes a couple seconds to actually run the macro so definitely don't plan your life on getting the most up-to-date financial data from this you should use something a lot more professional for that that's not just some free Yahoo thing that says delayed price right here but that's how you can do it so now with this little tiny macro you can go inside the browser and get pretty much any information that you want this doesn't go inside the browser but I meant you could go to a browser find the information that you want you can identify it by its class and then you can go here and grab it now you can also I said I was going to show you just a couple different ways so we're not gonna work through it but if you were to use an ID you could use like this HTML dot and just type get and you can see the other things that you can use to identify the data by so we have get element by ID that used to be the traditional way to do it get elements by class name by name by tag name by tag name in s and get selection and most of these you're never going to use most of the time you're just going to use get elements by class name but in the premium tutorial I'll show you some really cool things you can do with a couple of these other ones for now though let's go ahead and delete this guy so I don't confuse you and here is our macro in all of its lovely lovely glory now go ahead and make sure that you download this macro so you've got on your computer play around with it ask questions about it and it'll be on teach excel calm but that's it for this tutorial and I hope you found it helpful I hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 97,675

Rating: 4.8787003 out of 5

Keywords: excel vba, import macro in excel, import vba macro, vba training, macro tutorial, excel import, excel training, excel tutorial, microoft office training, free excel training, import data, ms excel training, teachexcel, teachexcel.com

Id: IOzHacoP-u4

Length: 20min 29sec (1229 seconds)

Published: Tue Feb 12 2019