Pull data from websites in Google Sheets with IMPORTXML function | Web scraping (Stock prices)

hey everybody it's corey from spreadsheet class today i have a really cool formula that i want to show you how to use and it's called the import xml formula this formula allows you to web scrape which is to pull information off of different websites into your google spreadsheet and so there's a lot of different cool stuff that you can do with this formula personally i use this formula all the time to look up stock prices and crypto prices and different things that are related to stocks which is what you're seeing here on this screen and so i'll show you what this formula does before i show you how to use it so here we're looking at tesla stock tsla and this spreadsheet is programmed mainly with the import xml formula to retrieve information for the stock symbol that's entered here in this yellow cell so you can see here if i change the stock symbol the gme for gamestop the information changes and it shows me the description for gamestop and prices on different websites google yahoo and then different information related to this stock now you can look up the price for most stocks by simply using the google finance function this is also a very very useful function but the import xml function can pull prices or other information directly off of websites that you specify which is what i have here and so you see this formula import xml and we're pulling information off of google finance and we're specifying the symbol gamestop in our function because this symbol is included in the url for google finance quotes and this formula looks a little extra complex right now because we're using this cell reference but we can make it even simpler by simply typing the symbol directly into the formula like this and we would get the same results but don't worry i'm going to show you exactly what each component of the formula does here in a minute and so over here i'm pulling in the description for the company which is coming from yahoo finance and there's other things that i'm doing as well such as pulling in the news feed from fidelity and one more quick example of what you can do with this import xml formula which i'll go over in more detail in another video is pulling in crypto prices and so right here i'm pulling in the price for loop ring crypto from coinbase and there's a little extra that needs to be done here because sometimes when you pull in data it doesn't pull in as just a single cell as you can see here in these three different columns but again that's for another video so anyways you can see that there's a lot that you can do with this formula and so i'm going to show you exactly how to use it to web scrape or to pull information off of different websites now there's one more thing that we need to go over before we get into the google sheets formula which is how to find this code that you see here in the formula bar on each web page that we want to pull information from and more specifically from each element that we want to pull information from into our google spreadsheet and this is a good lesson on web scraping in general because even with other types of web scraping that are done on different platforms such as python require knowledge of this information and so this is a good lesson but don't let all this code intimidate you because all we have to do is click a couple of buttons to copy the code from the place that we want and then to paste it so don't let this intimidate you so here we're on the yahoo finance website looking at a price quote for tesla tsla now one important thing to note is that in the url or the site address they include the stock symbol within the url and so we need to use this url in our google sheets formula to specify what website or what web page we're pulling information from but since they include the stock symbol in the url this means that we can do some pretty cool things like i showed you on my sheet where we simply have to change a cell reference by inputting a stock symbol and we can look up new stocks but that's a little more advanced just remember that we're going to have to copy and paste the url or the site address from the top of the web page into our google sheets formula to specify where we're pulling information from now that's the pretty basic part here's the part that you need to know if you want to web scrape now in this case i want to recreate the formula that we use that shows the description of the company and so on yahoo finance at least on a computer this means we have to scroll down so here it shows the company description tesla inc designs develops manufacturers so forth so note that each little different thing on a web page is called an element that includes the buttons the entire bar that contains the buttons the sidebar elements and each of these elements has a variety of addresses that you can simply copy and paste and the one that we're concerned with for the purposes of using the import xml formula is what's called the x path and so what i'm going to do is hover my cursor over this element here which is the description and then i'm going to right click and then i'm going to click inspect now a bar is going to pop up on the right again do not let this intimidate you there's a lot of different code here you don't have to know what it means but as you can see here i wanted to inspect the element that i hovered my cursor over and it's highlighted for me here what that element is and so i'm simply going to right click again hover my cursor over copy and then i'm going to click copy full xpath usually copy xpath works but sometimes it includes some code that shortens things that doesn't work and so make sure that you click copy full xpath now here's something extra that's important to note when we right clicked on this element which is the description for the company it worked out perfectly to where it took us directly to this business summary and if you look closely whenever you hover your cursor over the code that's shown here on the right your screen will highlight the element off to the left that that code represents so now that my cursor is hovered over this business summary section you can see that off to the left there's that blue box that's highlighted the business summary and it shows the block that our cursor is hovering over and this is really important because sometimes when you right click and then click inspect it doesn't take you to the exact correct spot sometimes you'll need to move your cursor a little bit to make sure that the exact right element is highlighted off to the left and so as you can see if i drag my cursor upwards it highlights a new block off to the left and i can see exactly what it is that i'm hovering my cursor over and therefore what the x path of that element will be connected to and again in this case it worked out just perfectly to where it highlighted the correct element but sometimes it might take you to an element that is inside the element that you want and i'll show you an example of that in just a second here or it'll just be a little bit off so again as you drag your cursor up and down you can find the exact element that it is that you want to refer to with your google sheets formula and therefore the exact element that you want to copy the x path of and it's also good to note how these web pages are structured and so like i said sometimes you have an element within an element as i drag my cursor upwards it shows the next block up but as i drag my cursor upwards just a little bit more as you can see now it's highlighted the entire block that contains both the description as well as the address and so forth and so that's how web pages are structured there's entire sections that are their own elements and then within each section you can find more and more elements and that's why sometimes you just have to drag your cursor up and down to find what you're looking for so let's go over an example of how you might actually have to search for the correct element after you've right clicked and then click inspect so i'm on the same page for the tesla price quote and let's check out what happens when we right click on the price and then click inspect again it's going to bring up code off to the right here and you can see that the highlighted code here is fin dash streamer and all of this different code and again this time it worked out just right because i happen to know that this is the correct element to refer to but sometimes for example you might click just perhaps on the outside edge of the element or something like that and as i drag my cursor up just a little bit here look what happens to the place that's highlighted on the left side of the screen see when i've highlighted this piece only the price is captured but there's a container that you might have accidentally clicked on that shows the price and the loss and the percentage loss and so forth and so if i copied the x path for this portion it wouldn't exactly be what i was looking for and so here's just another example to where it's good practice to hover your cursor over the code and then look off to the left to make sure that the correct element is highlighted so in this case i'm going to drag my cursor down just a little bit and make sure that only the price is highlighted so now you know how to fix it if it didn't take you to the exact right element and one more little thing is notice that these little drop down arrows expand and collapse little pieces of code now this is even more rare but sometimes you will have to click one of these arrows to open up the code and then again just like before you can drag your cursor to make sure that you have the right element and so if this had been collapsed and i hover my cursor right here i won't be able to find the right code that i'm looking for unless i expand the arrow but you'll know that you need to do that when you're dragging your cursor from one piece of code to the next and it just jumps from one big block to the next and i know that i want something from this block i want the price i could see off to the left that the price is included in this block and when i drag my cursor down it just skips and so i know i need to expand this block by clicking the arrow and then i drag down a little bit and again now my cursor is over the correct element and therefore i know that that would be the correct element right click and copy the x path so i know that's a lot of information but it can save you a headache someday when again it doesn't take you to the exact right spot now you know how to fix that but for this example we're not playing in the price we're pulling in the description so again the two important things that we need for our formula is the url to tell which page that we're pulling information from and the x path that is from the element that we want to pull information from so let's go ahead and take those two different things and put them into our import xml formula in google sheets so what i'm going to do is recreate the formula here in cell j2 that again shows the description of the company that's pulled from yahoo finance so i'm going to delete the formula that's there so first what i'm going to do is go to yahoo finance and copy the url then i'm going to type equals import xml parentheses and now it's asking us for the url and so i'm going to type a quotation mark and i'm going to paste the url that i copied from yahoo finance then i'm going to put another quotation mark then i'm going to type a comma and now it's asking us for the xpath query so again we're going to need to retrieve this from yahoo finance so i'm going back to yahoo finance hovering my cursor over the description which is the element that i want to retrieve the information from i right click then i click inspect it takes a minute for the bar on the side to pop up but when it does right click hover your cursor over copy and then click copy full xpath go back to your google spreadsheet type a quotation mark paste the xpath that you just copied from yahoo finance type another quotation mark and now simply press enter now our formula is working in a google spreadsheet by simply using a formula we're pulling information off of the yahoo finance website and in this case we're showing the description for tesla stock now again we pasted this url directly into the formula and so if we wanted as it currently is we would need to change the stock symbol manually to change the description for the stock and so now it matches what's in my sheet but that's just because we happen to have entered the right stock symbol here so i'm going to show you how to modify this formula to combine it with cell references so that you can simply change the contents of a cell to change the criteria for your import xml formula and again this works really well in situations like this where the stock symbol is included in the price quotes and that's pretty common in the financial world so instead of typing slash gme what i'm going to do is remove that portion from the formula and then after the quotation mark i'm going to type an and symbol and then i'm going to type b2 because i want to refer to cell b2 as you can see here the symbol gme is entered into cell b2 and i want that appended to the end of the url i want that combined with this portion of the url and so i'm typing and b2 and then i press enter and now cell b2 is connected to our import xml formula and when we change the stock symbol just like all of the other things on the sheet the description here changes when the stock symbol changes so now you know how to use the import xml formula to web scrape and pull information off of different websites into your spreadsheet sometimes when you pull in information from a single element that element will split into multiple columns such as when you pull in the price from coinbase it's split into three different columns the dollar sign the dollar amount and the amount of cents and so in this case as you can see here in cell t6 i've had to add together the values in cell z6 and aa6 to get the dollar amount plus the amount of sense and so in another video i'll probably show you some more advanced methods like this such as using the index function to choose which column you want to retrieve information from especially when there's a large amount of columns or even rows contained in a single element like pulling from a table so remember a couple of important things which is that web pages change their coding and the naming of their elements on their web pages all of the time and also some websites are coded to not allow this type of information pooling sometimes you'll come up with an error in the formula or sometimes you'll actually get a message saying that bots are not allowed to pull information but in my experience i'd say at least 70 percent of websites allow you to pull this type of information and also please like and subscribe and have a great day