How to Automate Web Scraping in Google Sheets

In this video, I am going to take you through how to automate web scraping in Google sheets using  the import XML. In one of my previous  video, I covered how to scrape tables from web pages   using the import HTML function in Google  sheets. I'll be leaving the link to that video   at the end of this video. So the first thing  we are going to do is web scrape data using   the import XML function then I will show you  how to automate the web scraping process. Let's   now go to our Google sheets and start the  process. So we go here you go to then import   so you can see the other import function import XML, import data, import feed, import HTML, import   range. All of these have their own function. So we are going to be using import XML you can click on   it now for those who are asking how to turn on and turn off the Syntax feature. You can see that mine   is turned off so to turn it on you just take your mouse to this question mark here you can see turn   on formula app if you turn it on it will give you this you can see it right now so in case maybe you   are working with your spreadsheet and it is turned  off you just take your mouse to that place and   turn it on and you start seeing it the way to turn  it off is to click here when I click it, it is gone   but it's advisable you turn it on so that you can get help with the formulas. Now the next thing we have   to do is to copy the URL of the website we want to scrape the data from so we go back to the Yahoo   finance website and copy the URL of the website. So we copy it and we'll go back to the Google sheets and paste it here. So the first thing you  do is to open a quote then you paste the link also you close the code again and  include a comma. Alright, the next   thing is to copy the xpath query. You go  back to the Yahoo finance and then we right click.   You take your mouse to inspect, you click  on inspect this will open the codes of this   website all right here we have it so what we  are going to do is we will move our mouse to   look for the code that has this stock price on it  so you move the mouse look for it okay this is it   so this has the code on the stock price if you  want to check for that we can still move it you   can see some takes everything but what we want is  just a stock price the next thing you will do is   to right click then go to copy and go to copy Full XPath. Click on it then we go back to our spreadsheet   and paste that code on the spreadsheet.  So when you get here you open a code also   then you paste that on this and you close  it with a code then you close the brackets   all right when you have done that just  hit the enter button and this will load   up and give us the stock price all right  you can see here we have the stock price 168.83 . If will go back to confirm that let's close  this all right you can see the stock price is   fluctuating this is 168.34 so there we have it now  how do we automate this process whereby we don't   need to start going back to this website to check  the current price it is very easy what we need to   do is to copy this function so click on it Ctrl A then you Ctrl C. You copy this function you take it   to a new sheet so let's use a new sheet now this  is our Tesla and paste that function on this box all right so this is our new sheet now let's  assume we don't want the stock price for Tesla   we want the stock price for let's say Apple stock so Apple stock is AAPL. Now if you hit the enter button,  we are not going to get it because of the  code. This code precisely is for Tesla so even if   we put any of the company's name here it is not  going to update so to get a code that update for   any of the stock price you just have to edit this Tesla import code so that it can work for any   stock company. With that being said you just take your mouse to this place and erase everything that has to do with Tesla. So you erase it. Let me off  this so that you can see it clearly we erase it   to here. Now when we get to this  part, we just open a quote. We open a quote then   we include the AND symbol. Then  we specify the cell number that we want to take   effect in, And the cell number is C3. But our formula is blocking it but the cell   number is C3. So we just write C3 that is  just it then you hit the enter button this will   load up for Apple Stock. Now Apple stock here is 143.81. Let's go back to   the website to confirm that let's okay yes what  is this is Apple Stock 143.73 you can see it is   fluctuating and that's what we got here now let's  confirm this thing and see if we can use to check   for other stock price let's see Amazon AMZN so if the enter button this will load up the   stock price for Amazon this is 89.54 let's go  Amazon 89.65 you know it's always fluctuating   see the same way let's check for Facebook meta  let's check for meta cmta all right the enter   button this will give us a stock price for META  it's loading up one one four point seven let's   confirm that 114.77 you can see it here so that  is how you can automate web scraping in Google   sheets using the import XML. Hit the like button if  you found value in this video if you would like   to know how to scrape tables from web pages in Google Sheets, click the video displaying on the   screen right now and don't forget to subscribe  before you leave see you in my next video.

Channel: Jotter Studio

Views: 7,584

Keywords: web scraping google sheets, importxml, importxml function, web scraping, google sheets, automate web scraping in google sheets, google sheets web scraping, yahoo finance

Id: vnVUdES2dVs

Length: 6min 58sec (418 seconds)

Published: Wed Dec 14 2022