Web Scraping in Google Sheets! (IMPORTXML FUNCTION)

in this video we're going to be going over how to use the import xml function to web scrape in google sheets using the import xml function has allowed me to create some of my portfolio trackers and stock valuation spreadsheets like the ones you see here so let's go ahead and jump into the tutorial okay so the first thing we're going to do is we're going to learn how to web scrape using the import xml function and then we're going to learn how to automate the web scraping process using import xml so you can see the very first thing that we want to do is we want to pull in data from wikipedia and you can see we're currently looking at kobe bryant's wikipedia page and if i scroll down the data that we want to pull into our spreadsheet is what number kobe bryant wore on his jersey which was 8 and 24. so what we're going to do is we're going to jump back over to our spreadsheet and right here is where i'm going to perform the import xml function so in order to do this we're going to put an equal sign and we're just going to type out import xml and you can see it's listing the function right here so we're going to import data from structured data online and we're going to use an open parentheses and the very first thing that we need to do is we're going to jump back over to wikipedia and what we're going to do is we're going to copy the url and so what i'm going to do i know you can't see me is i'm going to come up here i'm going to click on the url and copy it and i'm going to jump back over to my spreadsheet and what i'm going to do is in quotations all i'm going to do is copy and paste the url which you can see right here wikipedia.org slash wiki kobe bryant and so i'm going to end that with quotations and so now we have the first part of our function there's actually only one more part so what we're going to do is we're going to add a comma and now we're going to jump back over to wikipedia.com and if we scroll down again here's the data that we want to pull in so what i'm going to do is i'm going to put my mouse over this and i'm going to double click and if i come down here you can see i'm going to have the option to inspect i'm going to click right here on inspect and as i hover my mouse over this html code you can see it's highlighting different things on my screen so you can see for example if i put my mouse right here it's highlighting the data that we want and so in this case what we're going to do is we're going to double click on this line of code right here and if i come down here to copy you can see it's going to give me the option to copy full xpath and that's what i'm going to click right there and so let's jump back over to our spreadsheet and we're going to open up some quotations again and now i'm just going to paste in that full xpath that we just copied which you can see right here i'm going to close this off with quotations and parentheses so that's the two parts you need for our import xml function we have the url and then we have the full xpath and so i hit enter you can see we now have the numbers that kobe bryant listed so let's go ahead and confirm that it pulled properly we can see his numbers were 8 and 24 and our spreadsheet is correctly showing 8 and 24. so that's example another number one but let's go ahead and do another example using finance data and for this example we're going to be pulling the live stock price for microsoft corporation so you can see right here their current price is 310.88 and this is what we want to pull into our spreadsheet so if we jump back over to our spreadsheet we're going to do the same thing to start out we're going to type an equal sign and we want to use the import xml function right here we'll have an open parenthesis let's jump back over to yahoo finance and we want to come up here and copy the url and once we've done that we'll add quotations and paste this into our spreadsheet so that's the first part of pulling this into google sheets we'll then add a comma and if we jump back over to yahoo finance we now need to pull the xpath in so what i'm going to do is i'm going to put my cursor over the stock price here i'm going to double click and click on inspect and now that this data has pulled in what i'm going to do is i'm going to move my cursor around and i can see it's now highlighting the stock price when i put my cursor on this code so this is the code that we need to pull into our spreadsheet so i'm going to go ahead and double click on this and we'll come here to copy and we're going to click copy full xpath and now that i've copied the full xpath we'll jump back over to our spreadsheet we'll put in quotations we'll paste in the xpath you can see it's a pretty long code here close the quotations close the parentheses and hit enter and you can see we've now pulled yahoo's or excuse me microsoft stock price from yahoofinance.com 310.88 and so the final thing we need to go over is how to automate the web scraping process so what i'm going to do is i'm going to go ahead and copy this entire formula that we just created and we'll jump over to this tab and what i'm going to do is i'm going to paste in this formula here and so essentially what we want to be able to do is we want whatever stock is listed here so for example let's say we list apple we want their stock price to show up right here so currently we have microsoft listed and we have microsoft stock price right here listed but if i were to change this the stock price doesn't update because it's hard-coded so what we can actually do is we can change the url that's listed and have it referred to the stock listed right here so what i'm going to do is i'm going to actually jump in to our url right here and i'm going to delete the data all the way up to where it lists microsoft right here and once i've done that i'm going to jump out of the quotations and we're going to add on to this url in order to do this we're going to add an and sign and then what we need to do is list the cell that has our stock listed and for me in the spreadsheet that is cell f3 and so when i hit enter right here you can see this just updated to apple's stock price so if we go to yahoo finance and we'll close out of this code here and we type in apple we can see their stock price is 175 and if we look at our spreadsheet you can see it's showing 175. so let's just mess with this to make sure it's working correctly so let's say we want to look at tesla stock price i'll type in tsla and hit enter and you can see our spreadsheet is now automatically pulling in the livestock prices for each of these companies using our import xml function so you can see why this is such a powerful function and why i use it to create all of my spreadsheets in google sheets so thank you guys so much for watching this video on how to web scraping google sheets using the import xml function if you'd like to be able to download any of my spreadsheets such as my portfolio trackers or my stock valuation spreadsheets then you can head over to my patreon page at the link in the description so that being said thank you guys so much for watching this video and please don't forget to like and subscribe to the channel

Channel: Dividendology

Views: 77,377

Keywords: web scrape in google sheets, web scraping in google sheets, web scraping google sheets, how to automate web scraping using google sheets, web scraping, web scraping with google sheets, google sheets web scraping, how to web scrape in google sheets, get data from website into google sheets, importxml google sheets, we scraping using google sheets, automated web scraping using google sheets, web scrape in google sheets using importxml function, importxml google sheets tutorial

Id: BLa9HKGos8U

Length: 7min 32sec (452 seconds)

Published: Tue Apr 12 2022