Excel Power Query Web Scraping, Custom Functions & Parameters - Part 3

alright and this video we're gonna expand on our getting data from web series and try to get some particular data from a website some piece of text or some piece of information a number something like that so in one of the videos I was showing you how to get something from a table but what if you wanted something that's not really in a tables so to do something like that I'm gonna use this Yahoo Finance website in this particular case and it doesn't really matter I'm gonna use this as a popular website example it's probably not the way you should be getting prices or anything like that there are better ways but you know as an example I'm gonna use this so what I'm gonna try to do I'm gonna try to get the price for the stock right here see this 200 774 that number from this page to get to the number first I need the link to that web page so this is that web page after I searched so it did some things here with parameters after you - question mark here as you can see on top hopefully you can see that but I'm gonna remove all of those parameters right there and just keep this clean like this that should still hopefully work now I'm gonna try to go grab this number out of this page so this is gonna be quite a long journey to get there because power query currently doesn't have a way to search by a class or an idea or something like that it seems like these features are coming up but as of right now we have to do this the long way so what I'm gonna do I'm gonna just copy the link I'm gonna open Excel go to my again power query depending on your version hopefully at this point you know where it is I'm gonna go on their data and do from web in our get and transform section or if you have the add-on you'll go to your add-on tab and find it here so I'm gonna paste the link right there and heat okay so this is what I'm gonna get I'm going to connect to this and remember it's looking for tables by default these are some tables I don't want tables so I'm gonna click on the document itself so if I go to this transform data we have to basically navigate through this node system and the way it's gonna work is basically our HTML tree so if I open this Firefox and just right-click and inspect element C it's gonna give us the tree here and if I scroll all the way up see the first one is HTML industry we need to basically go through all of these nodes here to get to that one that has the price in here now I'm gonna go here see this HTML is right now the one that we're on this is that HTML and what I can do I can click on this table thing right here and when I click on it it's gonna go inside of that HTML and see inside of that it finds head and body and if I go to my inspector here see inside of this HTML if I just close this main thing see inside of HTML we have head and we have body that part is gonna be in the body part of this page so now I'm gonna click inside of body in this view and open the table what it's called for the body and it's gonna be inside of that now you can see how it says div div and that's some scripts and then another div now I'm gonna go ahead here and open the body and apparently that's there's an iframe let's close this so there's div div there were some scripts right and there was another Dave there so there is apparently this display:none which seems like it doesn't show up but anyway let me just try to find which one it's supposed to be in so it's doesn't seem to be in this one so let's open this Dave ya see that starts highlighting it so that's really the first div in that tree so I'm gonna go back here and open the first one table basically I have to keep doing this until I get there so see now it says there's just one Dave inside of it so there's one so I'm just gonna go in but basically it said there was just one div inside of that one and there is also just one Dave inside of this one let's go check our tree so there's this div and if I close this see there's just one Dave inside of that that's correct and inside of this there's also one div and inside of that new div there is gonna be what seems to be three of them so let's go check I'm gonna go back and open the table and there it is three of them in this three it seems like see when I highlight this that highlights the background so that's the first one so I'm gonna go ahead and open the first one out of there that opens that inside of that it seems it's just gonna be one div I'm gonna go inside of this - let's go and open that inside of this there should be just see one div that's correct I'm gonna open inside of this now inside of this see that one seems to be the second one there four of them but we need the second one here so I'm gonna go here and find the second div there this just one so I'm gonna open this one too and again there's just one so and there's just one you can just keep clicking on it but here I just want to show you see there's this div there's just one inside of that I'm gonna hit this there is just one inside of this again so when I open this apparently there are one two three four and that fourth one five actually but the fourth one is the one that's highlighting that area in the background see that has the number in it so I need the fourth div so I'm gonna go back to excel this is the one that was just one I'm gonna click on that now we need the fourth one one two three four I'm gonna click on the fourth div apparently there is a script and there's div that's gonna be given on this one let's go just check this out really quickly so this one see if I open this there is this div and there's a script we just need to diff we're gonna open that one two and that's just gonna have one div in it we should open that too I'm gonna go ahead and do that let's click on this table and that table too apparently there's just one I'm gonna click on that too and then we should have apparently - I think - because this one says no but we'll go and check so here I'm gonna open this one let's minimize this so we can see what's going on so that's just one that's correct I'm gonna open that let's close these so we can see all the ones on the same level so apparently one this was probably that empty one two and three see that third one when I select it highlights the actual number there that's the one we need so the third one there it is so inside of that one there's just one so we'll open that and inside of that there's just one we'll open that - and then there are three of this so let's see so inside of this there's just one where is that one I think there was this one and then there is this hopefully I didn't make a mistake there but there it is decided that Dave see there is this span span and deep I think that's what I was looking at the last time so let's go check it out so let's go here see span span div cope that looks good that seems like matching this one span span and div first span see that's the one that gets the number in it so I need the first one from that list so the span I'm going to open the table and dairies the number hopefully you can see the number I'm gonna open this thing that's at 207 74 so now out of all of these I just need to keep that texting so I'm gonna right click and remove all the other columns that just gets us the number so basically you just keep going through that HTML tree until you get to the element you need and then we grab the text and now to load this back to Excel I can do close and load and closing load and that should just load this back to excel hopefully if we give it enough time let me just get rid of this magnifier really quick here alright and there this that's the number so we pull that number out of that web page so let me just close this thing so that web page you are out just to refresh your memory was this so this was that web page URL and as you can see it's basically like this thing right here that goes until here and then we have the stock AAPL so that's Apple I'm gonna go here and create a small table just to take this to the next level so we're gonna say stock so let's say I want to get for Apple Google Facebook so for three of them basically what we would do if we wanted to get Google we would have to just change that last part of this link to instead of being the Google stock it would be well Apple stock it would be Google stock and that would be the coast for Google stock in this particular case what I'm gonna do I'm gonna create a custom function to make this work so I'm gonna right click on this thing that I made and do edit get back to this power query window so this is the one where I went to there and got the price for this Apple stock so if I go here I'm gonna open this queries window see we have this document thing that's what we apparently called it but then it really doesn't matter what this is called so I'm gonna right-click on that on that gives me this and then I'm gonna create function out of this so it says there are no parameter yeah we're fine we're gonna click create anyways and it's gonna ask us what's the name of the function call it anything you want I'm gonna call it get stock price so that's the name of my function I'm gonna hit okay so what its gonna do it's gonna give you this thing so what you want to do this point see it gives me this whole thing with Lett and everything else so what I did I just basically just right clicked on this and I want to create function and basically I made a function out of it so now see this is the function the second one so I'm gonna click on that function 1 and then I'm gonna go here and click on this advanced editor so that's gonna say are you sure you want to continue whatever we're gonna hit ok that's gonna open this this is that whole thing that we've created that goes through all those nods and then it basically gets the text for the price and if you look all the way on top here see this is the part where you will get the URL right here in this web content now what's changing in this URL is instead of being this being like APL it goes to like Google ticker and all these other stocks that we might want to do so what I'm gonna do I'm gonna create a parameter for this function so instead of this parenthesis right here now we can do a parameter I'm gonna just call that parameter stock I guess so what I'm gonna do I'm gonna use that over here instead of this being hard-coded link I'm gonna create a concatenated link with using an ampersand and then that parameter that I just did that I'm calling stock and by dad I want to be able to pass this parameter stock to this function and go get the price for that particular stock click done so see now we have this function here see this as FX that's called get stock price and then on top here si accepts parameter stock and we can test that function over here by basically just providing a parameter and invoking this to see what we get so if I do something like this and click invoke that should run that function with that parameter and see it went there and it found the price for this stock which is in this case Google and there does that invoked function so that works so we made a function that we can just change the ticker now and go get the price for it so now what I'm gonna do I'm gonna use this in my advantage by first of all going back and creating a table for me so for now I'm just gonna do close and load right there so that should just close it so yeah that also loaded that invoked function which we don't really need so I'm gonna right click and remove that invoked function I don't really care about it delete I was just doing that to test that it works so now I'm gonna go back here this is the table I made with my stocks that I'm interested in I'm gonna make a table out of this so I'm gonna select this go under insert and make a table this table has a header so I'm gonna take this box here ok so that checks that now I'm gonna go on top here and rename this table I'm gonna call it stocks table some table name just no spaces as usual with table names so once I have this as a table now I'm gonna go to my data tab and then I'm gonna do from table range in my get and transform section and basically I'm gonna grab that table and put it in my power query as well so that will just return that table with those three stocks there it is now what I'm gonna do I'm gonna add and you column here to the side that will be the price for each one of those and the way I'm gonna do that I'm just gonna go here on top and click Add column and what I'm gonna do I'm gonna create that column by invoking a function and that function is the function that we just paid that gets the price for the ticker so I'm gonna create invoke custom function it's gonna bring this box now what's the name of the column I'm gonna call it yeah it doesn't matter let's call it prices for now and function we want to run if I open this well there is only one function with meit so that's the only one that shows up get the stock price and finally I want to run that function with all of these stock prices in this column stock so this is where as the parameter we're gonna choose that stock column which is that stock column from this table so I'm gonna hit OK it's gonna say there's the data privacy rule information is required we're gonna click continue because we're getting data from an external site so you'll have to figure out what this is for now what I'm gonna do I'm just gonna ignore this and you can decide what it needs to be for you it's safe and see what I got is this and I got this table thing so I'm gonna click on this little expand and out of this I need the text which is the only one we have anyways so I'm gonna hit OK here we are see the prices have loaded for each one of this so now I want to load this to excel so I'm gonna go in there home and in my home tab I'm gonna go close and load I want to close and load this stocks table these are the items and we got the prices for them so now what I can do if I want to pull more items here I can just go here and add the new stock that I'm interested in so I'm gonna do AMZN will do Amazon that's fine I'm gonna go back here and try to reload this refresh and here we go now we have the stock price for that one too and let you do it for this video thanks for watching please subscribe and I'll see you next one

Channel: Learn Google Spreadsheets

Views: 14,314

Rating: 4.9180326 out of 5

Keywords: Excel, Web Scraping, Power Query, Custom Function, get, data, tutorial

Id: nFj2Aw8qzTY

Length: 18min 9sec (1089 seconds)

Published: Wed Aug 21 2019