3 ways to pull crypto prices into Google Sheets | GOOGLEFINANCE, IMPORTXML, IMPORTDATA

Key Takeaways

hey everybody it's corey from spreadsheet class in this video i'm going to show you three different ways to pull cryptocurrency prices into google sheets now i'm going to try to make this video pretty fast-paced because i already made a different video showing the first two methods that i go over in this video and the second method is the more complex method and again i've already went over that in detail in another video so we're just going to browse through all three of these methods and the third method is a brand new one that i learned which i think is really cool and it's really easy to use so the first way to pull cryptocurrency prices into google sheets is by using the google finance function which is usually used to pull stock prices but it can pull a couple crypto prices such as bitcoin and ethereum and so here's an example of using the google finance function to pull cryptocurrency prices right here and we're simply referring to cell a2 which has the symbol btc usd in the cell and as you can see you can do the same thing for ethereum with the symbol e-t-h-u-s-d but right now the google finance function is very limited in what cryptocurrency prices it can pull and so i'm going to show you a couple of different methods another way to pull cryptocurrency prices into google sheets is by using the import xml function which requires a url and then an xpath and so this function is used to pull data directly off of websites and as you can see i've used this formula here on a variety of websites and only some of them are actually giving information back and that's because sometimes this formula simply doesn't work with a certain website for example coinbase it's working right now but after i released my last video people started having problems with an error that says could not fetch url and i think that's a captcha problem but that's the great thing about this import xml function is that you can use it on a variety of websites and so as you can see i'm successfully pulling in crypto prices from coinbase coin market cap coindesk crypto.com and yahoo finance so i can't stress enough to watch my other video if you want to go over this in more detail but i'm going to go over this really fast so i'm coming to coinmarketcap.com i'm looking at loop ring i'm going to right click on the price and then click inspect which brings up a menu on the right i'm hovering my cursor over the code over here on the right that highlights the price off to the left i'm going to right click hover my cursor over copy and then click copy full xpath i'm going back to my spreadsheet and this is where i would paste my xpath directly into the formula or in this case into a cell that i'm referring to with my formula i already have the url pasted here coin coinmarketcap.com currencies loopering and so the formula import xml is referring to the url and then the xpath is listed in cell b7 and so i've specified the page to pull from and the element to pull from which in this case is the element that contains the price and as you can see it's showing me the price for loop ring and here on coinbase we have a special example because the result returns multiple columns and so in this cell f6 i've had to add together two different cells to get the dollar amount plus the amount of cents but again that's in a different video so there's the first two different ways to pull crypto prices but let's go over this third method which i think is really really awesome and this third method utilizes a website called cryptoprices.cc and again i'm trying to go really fast and before i show you this method i have to give credit to the person who created this website because i think they're really awesome for providing us this resource they've used a coin gecko api to create a place for us to use their website to pull cryptocurrency prices and so again i have to give him credit so go to cryptoprices.cc and on this main page you can find all kinds of information about how to use this method which again i'm going to show you here in just a second if you scroll down the page you can click the link that says list of all tokens slash pairs available and it takes you to a page like this which shows you all of the possible cryptocurrency pairs i simply held control and a on the keyboard and then copied this and pasted it into my sheet so that i can easily search for the crypto pair that i want and they have tons and tons of pairs here so you should be able to find what you need now just to try to give a little bit of extra credit to who made this you can see their contact information on the bottom they are a developer and they're probably really awesome so check them out maybe even drop them a message and thank them for giving us this method to pull crypto prices all right so again cryptoprices.cc i'm going to go back to my sheet and these are the crypto pairs that i showed you on the page that shows the crypto pairs and all we have to do to use this method is use the function called import data so i'm going to type equals import data parentheses and i'm simply going to refer to this cell which has the bitcoin pair at cryptoprices.cc then i'm going to press enter and so just like that this import data function which by the way retrieves csv data has pulled in the bitcoin price and again this is with much thanks to cryptoprices.cc and the resource that they've put together so that we can pull crypto prices into google sheets very easily and if you want to learn more about how to use the import xml function to pull data from websites i've linked to that video below if you enjoyed the video please like and subscribe and have a great day