Automating Web Query in Excel 2016

welcome back folks this is Professor Allen from Brigham Young University today we're going to be looking at recording a macro in Microsoft Excel and edit it specifically we'll be creating a list of stock tickers and then using the web query feature of Excel to automatically bring in data for each one of those stocks so let's go ahead and get started before we dive into the excel part let's just take a look at the website that we're going to be working with so I'm just going to go to finance yahoo.com let's see if I can get there finance that yahoo.com and I'm gonna just put in here a ticker over here as a quote lookup so I'll put in say Apple AAPL and that will bring up information about how Apple is trading here's the current price per share here's the other information the bid-ask spread where it opened price earnings ratio the earnings per share and so forth so what I'd like to do is I would like to be able to use data from this site and be able to bring in say that price earnings ratio and the earnings per share for an arbitrary list of the stocks so the interesting thing to do first here is to look at this stock ticker it's a little bit unusual and that it has the the stock ticker here and then again question mark and it has a parameter here with the same stock ticker I'm gonna get rid of that second one and just see if this behaves the same way if I just put that stock ticker in there once if that's the case then this will all get a little bit easier for me on the code side and it looks like that does and so here we have just by putting the stock ticker here after a Finance a Yahoo dot-com slash quote slash whatever ticker I put here it'll bring that information up and I can change this ticker say to go to Southwest Airlines volume V and that should now bring you information for Southwest Airlines and so that's gonna be really handy and so that's the website that we're going to work with now let's go ahead and set up our Excel workbook to do what we want it to do so let me go ahead and just delete this so I've got a brand new blank worksheet booked to haven't even saved it yet let's go ahead and save this and give it a name so I will see choose file save and I guess I'll just put this on my desktop and get my desktop it's here somewhere and desktop now I'm gonna call this stock info but instead of saving it as an XLS X I'm going to choose macro enabled workbook and so that will be an XLS M file if I save it as an Excel s X I will lose the macro that I'm about to record and modify and so I want to make sure I've got to save it as an XLS Santa all right so I've got stock info dot XLS M because I'd like to do I'd like to put a list of tickers here so TI cker and then what I'd like is the price earnings ratio and like the earnings per share I'll go ahead and list a few stock tickers here Apple AAPL Google Microsoft Southwest Airlines C Exxon Mobil Ford and Geney and the idea here is that we would have you know could potentially have a lengthy list the point is that it's an arbitrary list it's the list is going to start an a2 and then it's going to go as long as as long as we have contiguous and stock tickers here now then I'm gonna I want to be able to retrieve these two pieces of information the price earnings ratio and the earnings per share and so I'm gonna record myself doing this with a macro and so I'm going to go ahead and got my page set up and ready to go before I start recording I'm gonna be sure that I'm gonna I want to make sure that I know which cell I'm going to start in so when I when I run this macro and the macro itself is we're just going to pull in the information for one stock and so when I run just that one macro I want to think where should the active cell be where them when the macro runs and in this case I'd like to indicate the stocks that I'd like the information for by making that the active cell and so because I want the macro to run when the active cell is on one of these stock tickers I'm gonna start on one of those stock tears when I read when I start recording the macro I also want to think about where do I want the active cell to leave off when I'm done recording so that it's ready to run again in this case I want to fill in the price earnings ratio and the earnings per share and then I would like to end up on the next row down on the stock ticker so I'm ready to run that macro again it will just go ahead and run so I'll go ahead and start here on Southwest Airlines and I'm ready now to record my macro so right now my Developer tab is already showing here but if yours isn't then it's pretty simple to do let's just go ahead take a look at that quickly you'll choose file options and then you've got customized ribbon over here and then here's the main tabs that you have on your ribbon and by default developer isn't checked so you let's want to go ahead and check that Developer tab and then you'll have the Developer tab which has all the tools you need for manipulating an editing map so one more thing before we start recording I'm going to make sure I have my sheets name the way I want them to be named and so I'm gonna call this one I'll just call this tickers and I'm gonna add another tab here and I'm gonna call this web query so now I'm ready to begin recording my macro so I'll come here to the Developer tab and I'll click right here on record macro it's gonna prompt me for a name I'm gonna call it stock info I can get a shortcut key I could give it ctrl s but ctrl s normally saves the current workbook but if I recorded this macro and give it a shortcut key of ctrl s it will now run this macro instead of running gets - my stock info macros instead of saving it'll run this macro so I'm gonna put him here and set a capital S which will change the shortcut key to ctrl + Shift + S I'll store it in this workbook and I'll go ahead and put in a description here that this gets the price earnings ratio and the earnings per share for the ticker in the active cell all right so as soon as I say okay it's gonna start recording everything that I do so say okay here I'm gonna copy my first I go ahead and whoops I'm gonna copy my active cell so I've got this copied now I'm gonna select my web query page and I'm gonna come and select c1 you'll see why we want you to see one here in just a bit and I will paste right here so I've pasted the stock ticker that I'm after right now now to be able to get the information from the web I'm going to use a tool off of the data tab of my workbook so I'll come up here to into the ribbon to my data tab and this first area getting transform data I'm gonna choose from web now if you're if your first group doesn't look like this it may be that you've got an older release of Excel 2016 so just make sure that you're updated to the most recent version so I'll click on from web and it will bring up a little window here saying all right what URL you want to go to well I've got my URL here so I'll just copy that right off of my browser and I'll paste that in here of course if you don't have that yet you could just type it in HTTP colon slash slash finance at yahoo.com slash quote slash and then whatever ticker you want in this case I'm putting lue so we'll say okay and that will now communicate Excel will communicate with that web server and it will bring up this little wizard that'll help us choose a date that we're after now it comes up in table view but there is a web view and you can kind of see the page that we're after is located here we've got Southwest Airlines and those that data were after it's just down here a little bit lower over here is price earnings ratio earnings per share I'll go back to table view and it's not in the first block here the second block is that first table I want the next table so that's this one here and so I'll go ahead and say load now you'll notice that when this tool loads that query it's gonna put it on to a new sheet and we'll see the activity it's doing that ultimately we're gonna want that to come right here onto our query sheet but it's gonna create a new sheet here in the interim so let's go ahead and click load we can see it's brought in that new sheet and now it's brought the data in here here's our price earnings ratio and our earnings per share so I'm gonna pause recording my macro at this point and we're gonna go take a look and see what it's recorded and make some modifications to it so I'm going to click on the stop button here and now I want to go look at the macro that's been recorded so back to my Developer tab and I'm gonna come here to my list of macro so I'll click on macros it should bring up the macros I have available I only have one I've just start recording it so it should be pretty easy to find here it is stock info I could run it right here it would actually cause problems if I ran it because it'll try to create a new query with the same name and that will cause an error but let's go ahead and instead of running it let's edit it and now this will bring up the visual basic editor for the language VBA Visual Basic for applications that's built right into Excel so I'll click on edit and here we have the macro that we've created it's called stock info that's what I named it here's the little note that I put in there gets the p/e ratio and it needs per share for the ticker in the active cell as a little note here that the shortcut key is ctrl + Shift + S and then here is the information that we the actual activities of this macro so the very first thing we did once you started recording was copy so selection got copy that just says hey well whatever is selected do the activity called copy then we said hey out of all the sheets there's one called web query let's go ahead and select that and then hey on that on that sheet there's lots of ranges one of them is just a single cell range called c1 we'll go ahead and select that and then we're gonna paste so all those activities that we did those got recorded in code so now we've got application that cut copy mode equals false that just takes us out of that mode ring when we're copying it indicates the cell that's that's been copied with a little flashing lights that go around it and that's fine so this then is the line that's creating the query that we're after and so without going through all the detail here we can just see Hayward in this in our active workbook whichever book is active right now there's a set of queries a collection of queries and we are going to add a new query to that collection we're giving it a name the name is going to be Table one we could change that name or we can I'll just leave it the way it is here and you can see that the source of this is going to be a web page and you can see in here the URL that we're getting so ultimately we'll have to manipulate this but for right now it's pulling in just Southwest Airlines now that creates this new query object that's a part of the workbook it's non-visual we don't see it but it'll be created now here's the next line that's a little bit problematic it says in this active workbook you want to talk about all the collection of all the worksheets that's there and we want to add a new worksheet that's what it did it added a new worksheet to put this code onto that worksheet and so I don't want this line to happen I want this to go right onto the same worksheet that we have and so I'm just going to either delete this line or a little trick is I can put a single quote in front of it back all these up here these lines up here that start with single quotes they're not executable code the single quote is a cut is a comment character just indicates to the the interpreter okay this is not a line for the interpreter try to figure out to do something it's a note to a future programmer and so I can do the same thing here just by turning this line into a comment it will disable that line from executing it's kind of nice when I'm just beginning to work with macros instead of deleting the line then I'd have to figure out how to bring it back if I decided I didn't want to delete it in the first place but if I just use the comment character it disables it and then I can remove it to re-enable it again if I want to so what we've got here is we've got now we've created this query and that will just that will just happen the next time we run this macro then after the query is created we are going to bind we're going to create a new list object so on the on the active worksheet there's a collection of a bunch of list objects next to this questions empty when we start but we're going to add a new list object and we're going to be referring to the the query table that belongs to that list object and here we can see that this provider where the data is coming from from this list it's looking for this location table one that's the query that we just created up here and so the name of this query here needs to be tied in to whatever we have here so this is what's actually going to bring the data from that non-visual query and display it onto the sheet into a list object and there's lots of other information here that we're just going to accept because we're not really into the details of everything that's going on here but just to show you enough about what we have to do to get this to work for this example so the key thing to realize here is that right here at the very all these lines in here it's configuring this this list object this table in Excel and then right here it tells it to refresh in other words to pull the information from the query again and that's what will bring the data in and so if and then here's the end width which ends this structure here and then this end sub is the end of the macro so macros start through the keyword sub and ends with a keyword end sub and so I'd like to do a couple more things right here after we've we've created the we've we've created the query we've used that query to pull the information into the table in Excel and now because the next time I run this macro I'd like to be able to create that query again my code creates a query it's a query names table one so what I'm gonna do at this point after I've brought that data in I am going to say listen in my active workbook refer to all the list of queries find the one called Table one and delete it the action is going to be to delete it I'm gonna delete that because the next time I run this macro I want to be able to create a new query call it table one and if I don't delete it this is going to cause a problem now I've got a similar problem here and that is if I try to create a new list object if I try to create a new table right on top of the existing one it's going to have the same kind of problem and so what I'm gonna do is I'm going to disconnect that data from the table on the just turned into a normal range of data instead of instead of it being a table and to do that I'm gonna say on my active sheet here's my whole set of list objects the list object named table 1 and that's named right here this is the line that gives it its name refer to the one named table 1 and I'm going to give the unlist action and so to be able to make this example work force so it can go over and over again after I create the query and then bring that query in to show the data I'm going to delete the query and I'm going to disconnect the table from the data just delete the table structure the list object but I'm going to leave the data there so now I should be able to run this again and it should bring the data on to our on to our web query sheet because I got rid of the line that adds a new sheet and so actually because this table already exists that's going to be a problem for me so I better go ahead and delete that first so I'm gonna run this line of code outside of this macro just by choosing view immediate window that brings up a window that I can use to just execute one line of code immediately and I'll do this one here just got to get this run once that will delete that query and then I will run this line which will disconnect it disconnect the list box from the list so now when I run this again it should have room to create it should have room to create the query then create the list and then it will delete them immediately once I've already got the data I don't need the I don't need the connection back to the web page anymore so I'm gonna start by deleting this sheet here that I don't need this one that got added I'll delete that and I want to confirm it yes and I'll make sure that I'm over you're on one of the stock tickers I want to be honest I'll go to Google press control-shift s or kind of my list of macros find stock info and say run that should go put the information for Google right here in column C or the the ticker symbol here in column C and then it will bring in the data for Google okay so so far so good to this point I've got my data where I'm copying the active cell I'm bringing it over here putting it in column three and then I'm bringing in the data for a particular stock but yet not just not yet the one that I put here in column C if we go back to the code check click Mac oops cancel yeah macro bring up my list of macros and then edit that one should take me back to my macros will notice that even though I'm pasting that information there into c1 I'm not yet referring to that in the creation of the query and so right here it's hard-coded oh as to well it's a little bit sad but the computers looking for love and it needs to be looking for Google in this case or whatever I've put into range c1 now this is going to be a little bit confusing because this is what we refer to as a string literal and VBA this thing a little bit unusual so a string literal is marked with double quotes so if we look right here here's a short little string literal right here it says double quote and then le T these three first three characters are part of the formula for this for this query that's being created and it starts with the it's just these three characters it starts with a double quote ends of the double quote and that's just a collection of characters this next character is the ampersand symbol and it's just going to say take whatever we have here and glue it on to what we have here that I want to worry right now about character number 13 and character number 10 but we have now here another string literal starts with this quote and it just goes right along to here now we have a problem if we want to put quotes inside of a quote terminated string because when I get to the quote right here if I just want to put quotes around this URL I can't just put one quote there because that would terminate the string so in VBA when I want to put a quote inside of a double quote turn in string I just put two of them next to each other it seems like a little bit weird but the way the interpreter looks at that is it says oh that's not really the end of the string here that's just a quote inside this string so when it comes along and interprets this little part of this this string literal here it says oh here's a quote it's the beginning of the string L et great and it gets to this when it goes wait a minute I think this is the end of the string but let me peek ahead and see if the very next character is another double quote and if it is it'll go alas not really the end of the string it's just a quote in the string but in this case the next character is not a double quote and so it goes I that's the end of the string and we're gonna glue it on to something else and we'll glue it on to something else in something else that's what the ampersand does concatenation and then we get here and it says alright we've got another collection of characters you get right up to that double quote and goes oh I think that's the end of this string wait a minute let me check ahead and it goes oh no that's another quote so that's really just a quote inside the quote and it says okay so I'm still working on this quote I'm still working on this string I got the s the parenthesis now I've got a quote here and now the HTTP all the way up to right here now what I want to do here is I want to terminate the string right here and so what I'm gonna do is I'm going to actually I'm going to first highlight the data that I want to remove from this string and so I'll delete that and then I'm gonna put in two quotes so double quote followed by another double quote and then between those I'm gonna put some space so I can't see it so now if I look at the first part of this string I've got quote and it says oh great here's a bunch of character is all part of the string looking good it gets to that quote and thinks I think that's the end of the string we peek ahead oh no it's actually a quote in the string and it keeps going along it gets to this quote and it goes I think that's the end of this string we peek ahead and it goes oh space yep that was the end of the string this is okay so that's one string right up to here now we've got to do something in here but now here's where it gets a little bit there's somebody see it's a little bit hard to follow this says hey we're starting a new string it gets to this one it thinks oh I that was very short there's nothing you know I think that's the end of a string but wait a minute let's peek ahead and see if there's another quote here he goes oh the next character is a quote so this this two that we have showing here isn't really the end of the string it's just quote in the string and it moves on to that one where it realizes okay that's the end of the string we're trying to put together here so what I've just done it have taken that whole long string little that had the characters lu v inside of it and now i'm ready to be able to splice in an expression that can be evaluated to whatever stock ticker is sitting in c1 and here's how I do it I'm just gonna end that one here I'm gonna concatenate that shift seven I'm going to concatenate range c1 range c1 and I don't want to know the address of c1 I don't want to know the background color what fondant is I want to know the value of c1 and then I will concatenate that right back on to whatever we have going on after this so without taking the time to totally go through and explain this whole source what we've done is we have replaced the characters lu v with this what I have right here quote says hey terminate that string that comes before it now add on to the value of whatever is in cell c1 and then add that on to and we've added this other quote here which will start up that will start up that string again and add on this next collection of characters so now when I run this instead of it always running for Southwest Airlines it should now bring in the information for whatever I have in c1 I'm just going to press alt F 11 which is the hot key that will toggle me back and forth between my excel and my VBA editor that's alt f11 so I'm gonna come back here and I'll just notice that my earnings per share is three point three three here I'm gonna come back to my tickers don't go to Apple and I'll press ctrl shift s and that should run that macro and that has now brought in a different earnings per share now why didn't it delete those columns so to make sure that we have this happening right I think the very first thing I'm gonna have to do once I activate my web query sheet because I want to clear off all the cells of this sheet because I don't want these to keep pushing over I would get messy what's silly would look it would work okay but would get messy so I'm gonna first say I want to clear off all those cells so alt f11 will take me back to my editor and right as soon as I activate copy not sure if I can clear that I have it pasted as well let's give it a shot so as soon as we activate this worksheet I'm just gonna say I want to clear all these cells so all the cells on my active sheet the API active sheet dot cells dot clear let's give that a shot come back to my tickers I'll choose ExxonMobil ctrl shift s and that's better the data's going to come in but it's gotten rid of the information that was there before so while that's running let me just come and review that code that we added in here here is the line of code that we added active active sheet dot cells dot clear that just says I'm not specifying any cells in particular I want all the cells and we'll clear all of those cells and so it should take just a second more for us to pull that data in and let's go back and take a look yeah okay so that made us in now just fine at this point we've got the code working pretty well depending on which cell is active here when we run the macro it will now copy that cell over here to c1 and it will use that to bring in the data for that particular stock so our next step from this point is to copy our price earnings ratio and our earnings per share and to bring it back over to the tickers sheet and so to do that I'm just going to go ahead and start recording again right here and then we'll combine these two macros together into a single macro so I'm going to come up to record macro again I don't need to give it a shortcut key because this one's going to just ultimately become part of my other macro I'll leave it called macro one or whatever it comes up as and we'll just start recording and so since I'm after the price earnings ratio I'm gonna slip that that's gonna record go to b4 and select it I'll copy I'll just do it by pressing ctrl C I'll copy that cell I'll come back now to tickers and now I have a problem because if I click on b6 it's gonna record b6 like an absolute reference just whatever cell I go to but what I really want to tell it is go one cell to the right of where the active cell is and sort of do that I've got I have a notion that's similar to absolute and relative references in a formula I can use relative references when I'm recording and that's the button right here on my Developer tab so if I toggle this on to say use relative references now when I click on b6 it will record offset one cell to the right of whatever's active and select that cell whatever it is and now I can paste this so I'll go ahead and paste that value in and now again I'm still on use relative references so when I select earnings per share here I'll be ready to paste into there I'll come back to my web query page I'll click down one that's okay I think I'm okay to do a relative offset here so I still have relative references I'll select that one I'll copy I'll come back to my ticker sheet I'm already on the cell that I want to be on I'll paste in here and I'm almost done all I have to do now is end up where I want the macro to end up when it's done running so I'll click on the first column of the next row down because I'm on relative references it will say go down one row and go backwards two columns now I'll stop recording and I should be able to run that macro control-shift s should now bring in the data for Ford ah but I have to connect these two macros together and so I'm going to come back to my list of macros and edit my edit what doesn't matter when I edit either one will take me there I'll be right next to each other so I'll edit this macro here's my stock info macro if I scroll down here's the end sub for that macro and here's the starting of the next macro well I don't really want to end the last one or start the next one so I'll just delete that whole block and so now it starts up here with sub stock info and it ends way down here at the new end sub I've just gotten rid of this the ending and the starting of that other sub procedure in the middle and so now it will pick up from here and it will do the rest of these steps so now I'm gonna come back to my tickers tab here I am on Ford I'll press control shift s it should pull in the information for Ford and now it's copied in the price earnings ratio and the earnings per share I'm on GM control shift s you should pull in the data for GM and if I come back up here to Apple control shift s it should bring those in as well you can see that I could just keep pressing control shift s and pull in the data that I want and of course I could keep pressing control shift s and the truth is if I only had these seven stock ticker that's probably exactly what I do but why do I had 700 stock tickers my pinky's gonna get tired from pressing ctrl shift s all afternoon so what I'd like to do is I would like to tell this macro hey let's begin in a - I don't care where I am I start let's jump to a - and then let's continue to run that process until we've till we've run out of stock tickers until we end up with wheat until we move on to a cell that has a blank value right here so to do that I'm going to go ahead and clear this data out and I'm going to come back to my Visual Basic editor so alt f11 is the quick way to toggle back to that so when I have right now is I have a sub procedure here called stock info that gets the stock info I rather than rather than modifying this one to do that I'm this is useful by itself I could always press ctrl shift s and just get these two pieces of information for whatever cell has the stock ticker regardless of really where that is in the list or even in another in another part of my worksheet so I'm going to leave that as a useful unit and I'm going to create another one I'll call this one get all stock info and you'll notice I'm not recording this macro I'm just starting from scratch so I've typed in the keyword sub I've typed in the name of it now I should have these opening closing parenthesis which is for some more advanced use that we may talk about later in a later video but you'll notice that if I hit enter the visual basic editor is gonna plug in some of the syntax for me so it automatically adds in these parentheses and it adds in the end sub for me so it's very thoughtful so just kind of saving me some work there so I'd like to do is I would like well first of all I won't get all stock info to do exactly what stock info does and here's the trick I can make that happen just by saying Paul stock info and so now these two sub procedures do exactly the same thing and I mean exactly because all get stock info does is it just call stock info that's it's gonna just go and do this but before I do that I want to make sure that I'm on the right sheet and so I've got a sheet here called ticker and I've got code down here that selects that so rather than type it I'm just gonna copy that in I'm just gonna say hey let's start off by making sure that we're on that sheet select my ticker sheet and I want to make sure that I'm on range a - that's where my data begin and so I'm gonna change this to range a - dot select that looks pretty good so now when I run get all stuck in fuzzy make sure I'm on the right sheet and then make sure I'm in the right starting place to get all of this and now I want it to do this part over and over and over again and so I'm gonna make a what's called and programming lingo a loop I'll say do which in VBA just says hey this is the beginning of a loop and then i'll say loop which is the end of a loop it says go back to the prior do and i'll bring this in so i can kind of see it as part of this structure so du says hey this is the beginning loop says go back to the do somebody call stock info which will get the information for my current stock plug it in here and then move the active cell here then i'll say call call stock info again and it'll get this information let me down here it'll run it again but at this point i haven't given any reason to stop and i want it to know to stop when it when it's just when it's starting I want to check the active cell and if the active cell doesn't have anything in it I want it to stop I want to exit the loop so the way I do that is with ANATEL clause by the way this this code will execute the way it is it's not especially desirable because there's no way out of this loop it'll have to run until it has an error but in this case I'm going to go ahead and give it a way out so I'm gonna do until some condition is true and what's that what's that condition until the active cell the active cell is not not its address not its formula not its color but its value is equal to something so if I was looking for some you know keyword in there stop until it finds the active cell that says stop well I don't want to have to put in the word stop to tell it to stop for all I know that could be a valid stock ticker but I don't want to put you know I could put stole but same problem there I don't want to just put s T or even but if I go down to nothing get rid of those characters then I'm gonna loop until I bump into an empty cell it seems like a strange way to say empty cell and there are other ways to look check for an empty cell but that's the simplest one it is a collection of characters marked by the double quote starts to the double quote ends with a double quote but there's nothing in it and that's what an empty cells value evaluates to so now if I look at the way that this procedure is going to run it is going to pull in it's going to start by selecting the sheep going to the right place and then it's going to continue to execute the stock info macro the one that actually does the work until it bumps into an empty cell till the active cell is empty so remember control shift s still just runs stock info so we need some way for the user to run this one which will go ahead and take a look at right now so what I'm gonna do is back on my Developer tab in my ribbon I'm gonna choose my little toolbox right here and I'm gonna choose the very first tool so this tool is a button it allows me just to to invoke some code from the form or from the in the worksheet so I'll select this button I'll draw it here onto the sheet it's gonna say oh you're making a button you probably wanna run a macro yes I want to run the macro called get all stock info and we'll say ok and maybe I should change this label to say I'll click off of it and now that button will become active and I should be able to run this and have it pull the data in let's just go ahead and run it and see how it goes so it click get data like it's pulling in the data from Apple and now it's moving on to Google now Microsoft Southwest Airlines ExxonMobil Ford GM and gets the last one and there it's brought all that data in and so there's been a pretty good example we record ourselves doing something working with it a more complex tool of excel bringing data in from a webpage we need to do some modifications to that data or to the macro that it recorded we're able to create a loop and have it pull the data in and here just a short time we've been able to create what would be pretty useful macro all right folks thanks for joining and stay tuned for more videos

Channel: Gove Allen

Views: 40,877

Rating: 4.924933 out of 5

Keywords:

Id: l0mYNqAZJpY

Length: 37min 56sec (2276 seconds)

Published: Tue Aug 29 2017