Pull cryptocurrency prices in Google Sheets (2 methods) | GOOGLEFINANCE and IMPORTXML crypto prices

hey everybody it's corey from spreadsheet class today i'm going to show you two different ways that you can pull crypto prices into your google spreadsheet by using google sheets formulas now the first method is much more simple but it's also much more limited on which cryptocurrencies you can display the price of the second method is more advanced but you can pretty much pull any different crypto price that you want with it so first let's go over the simple method which is by using the google finance function a lot of people use the google finance function to pull stock prices but you can also use it to pull the prices of a couple different cryptocurrencies such as bitcoin and ethereum now honestly i'm not sure if there's any other cryptocurrencies that this works with but it definitely works with bitcoin and ethereum and so here's how you use the google finance function to pull crypto prices so as you can see here in cell t3 the blue cell i've entered the formula equals google finance r3 we're referring to cell r3 as the criteria for the google finance function and so what we're basically saying is show me the price of bitcoin and for the purposes of this formula the symbol that google sheets will expect for bitcoin is btc usd that represents the bitcoin us dollars pair now the same thing can be done for ethereum by using the symbol eth usd now if we wanted we could simply type these symbols directly into the formula by typing a quotation mark and then typing btc usd and then a quotation mark and we would get the same result but it's nice to be able to use cell references so that you can simply change the symbol in the cell which will also change the resulted formula and so again we can simply refer to a cell with the google finance function where the cell that we're referring to has the symbol that the formula expects now in a different video i'll go over the google finance function in more detail with traditional stocks because there's actually a lot of different things you can do with the google finance function beyond just showing the current price but here we're just sticking to pulling the current price of a cryptocurrency and if you don't specify any additional criteria the default of this function will simply show the current price this method is the more simple one but it's more limited on the cryptocurrencies that it can cool and so i'm going to show you a more advanced method which is by using the import xml function which pulls data directly off of different websites you can specify an element from a website that you want to pull information from into your google spreadsheet so i'm going to go over that pretty quickly here but i have a video of where i go over this in greater detail and so if you feel like this is going kind of fast that's because i have a different video that you can watch which i've linked in the description below so again the next method for pulling crypto prices into your google spreadsheet is by using the import xml function so as you can see here in cell y6 the blue cell we're using the import xml function to pull the price of loop ring cryptocurrency from coinbase into our google spreadsheet and so the import xml function has two different things that it needs it needs the url for the website that you're pulling information from which in this case is coinbase and then it also needs what's called an x path which is basically an address that tells where on the web page to pull the information from and both of these things we can easily copy and paste from the web page that we want to pull the information from so i'm going to show you how to do that right now so i'm going to delete the formula in cell y6 so that we can recreate it together and then i'm going to coinbase.com to the price quote for loop ring coinbase.com price slash loop ring and again this url at the top is something that we're going to need to copy and paste into our formula but we'll get there in just a second the next thing that we need to copy from this website is the x path for the element that contains the price each web page consists of a bunch of different elements each button is an element each toolbar is an element each table and so there's something called an xpath which is basically like an address and we can copy the x path from this price quote right here to use in our google sheets formula and so we can fairly easily copy this x path and what we do is hover our cursor over the element that we want to pull information from and then right click and then click inspect now a bar has popped up on the right and as you can see there's a bunch of code here and you don't need to worry about what the code does we just need to know the element that it represents now in this case as you can see if you look off to the left the numbers .07 are highlighted and so we want to pull in more than just the .07 we want to pull in the whole price and so i'm going to drag my cursor upwards through this code and as you can see when i do this if you look on the left the element that's highlighted changes so i'm going to go up even more a little bit more and so now when i hover my cursor over this section of the code you can see off to the left that all of the numbers are highlighted and so this is the element that i want to copy the x path of so sometimes the element that you've right clicked on is not the exact element that you want to pull from but it will bring up the code and you'll be very close to the place that you want to pull from and again just drag your cursor up and down and look to see what it highlights on the web page and then you'll know when you've captured the right element so again here where it says div data test id asset overview price well that makes sense asset overview price but anyways this is the element that i want so i'm going to right click on this element then i'm going to hover my cursor over copy and then i'm going to click copy full xpath make sure you click copy full xpath now i've copied this code to my clipboard which is an address to this element which contains the price and i'm going to copy it or paste it into my spreadsheet formula so i'm going back to my spreadsheet and just as a temporary placeholder so that it's ready for me i'm going to paste the xpath that i just copied into cell z6 then i'll reduce the size so you can see what it looks like again it's nothing that you need to worry about how all this works you just need the code i'm just putting it here temporarily because i need to go back and copy the url and then i'll be ready to enter my formula so i'm going to copy the url from coinbase which is here on the top coinbase.com price slash loop ring now i'm going back to my spreadsheet and i have all of the information that i need to enter my formula so i'm going to click on cell y6 and then i'm going to type equals import xml parentheses now you can see that it's asking for a url and we've just copied that to the clipboard so i'm going to type a quotation mark and then paste the url then type another quotation mark now i'm going to type a comma and now the formula is asking us for an xpath query so just temporarily so that i can copy the xpath that's in that cell to the right i'm just going to press enter even though the formula is not complete and then i'm going to copy cell z6 which copies this code right here i'm going back to my formula and again it's asking for the x path after we've typed our comma so i'm going to type a quotation mark paste the x path and then type another quotation mark and then press enter and i'm going to delete my x path from cell z6 and now my information has popped up and so now we're pulling data directly from the coinbase website into our google spreadsheet which is really really cool but as you can see in this particular case the element that we pulled data from was three different columns it's showing the dollar sign in one column the dollar amount in another column and the number of cents in another column and that's really easy to deal with so here in cell t6 i've simply entered the formula equals z6 plus aa6 to add the dollar amount plus the amount of cents and so different websites create their pages in different ways sometimes such as this formula here in cell t5 when you pull from a website's single element it will pull exactly how you want it with the dollar amount plus the cents and the dollar sign all in one element but sometimes again it splits into different columns and you simply have to look at what the result is and whatever you need to do to retrieve the correct data such as adding these two cells together now again in the other video where i go over the import xml formula we go over this in a little more detail so check out that video if you want some more detail but now you know two different ways to pull crypto prices which is with the google finance function or with the import xml function