In this article, we would have a look at how to pull crypto prices in Google Sheets.
What is the need to pull Crypto Prices in Google Sheets?
These days investors are looking to diversify their portfolios as much as possible and crypto has become a viable option. But how do you pull Crypto Prices in Google Sheets? In this article, we would look at how to pull Crypto prices in Google Sheets. In the end, you should be able to pull crypto prices and track your portfolio in Google Sheets.
Using Google Finance to Pull Crypto Prices in Google Sheets
GOOGLE FINANCE is a very versatile function in Google Sheets. It helps you fetch real-time financial data along with historical securities data from Google Finance. Google Finance is a web service by Google, focused on business news and financial information.
If you are unsure about the syntax for Google Sheets you can check out this article.
But how do you pull crypto prices using Google Finance? You just need the ticker symbols for cryptocurrencies to pull their respective prices. Let’s say we want to get the information for bitcoin. The Bitcoin Price at the time of writing this article is in the given figure.
The syntax to fetch a real-time price for crypto in Google Sheets would be as follows.
Where ticker_symbol is the valid ticker symbol for the corresponding cryptocurrency. For example for Bitcoin, it is BTCUSD. Similarly, for Ethereum, it is ETHUSD.
In this figure, you can see the live prices for these using GOOGLEFINANCE formula.
Even though we correctly pulled data using Google Finance, you should not use this because GOOGLEFINANCE currently does not supports cryptocurrency.
Apart from Bitcoin and Ethereum, there is no valid ticker symbol that would work for Google Sheets. Let’s move to the next section to have a look at a workaround to how you can pull crypto prices in Google Sheets.
USE IMPORTXML to pull crypto price in Google Sheets
We can use the CoinMarketCap website to pull crypto prices in Google Sheets using a simple formula.
- Go to CoinMarketCap and have a look at a list of all available cryptocurrencies. Alternatively, you can click here for the same.
- Click on any currency page and copy the URL(for example, it would be https://coinmarketcap.com/currencies/bitcoin/ for bitcoin). Make a table consisting of the name and the corresponding URL addresses for the pages.
- Use the following formula to pull crypto prices in Google Sheets.
=IMPORTXML(URL, “//div[@class=’priceValue ‘]”)
- IMPORTXML lets you import data using the HTML code in any webpage. For example, here we have fetched data from a div with the class name “priceValue ” which returns us the live value of the cryptocurrency.
- You can use IMPORTXML to pull any data from a website. To read more about IMPORTXML you can check out the article here.
Hence in this article, we saw how to pull Crypto prices in Google Sheets. We saw how to do so using two methods, one using Google Finance and the other one using IMPORTXML. The Google Finance method is very limited in use whereas you can pull any data from any website using IMPORTXML.
Loved this article on Slow Google Sheets? Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
How to create a Dynamic Named Range In Google Sheets: In this article, we would have a look at a Dynamic Named Range in Google Sheets. We will implement it in a step-by-step fashion using a simple example.
How To Calculate Standard Deviation In Google Sheets: We will learn how to calculate standard deviation in Google Sheets using the STDEV function. The STDEV formula is used to find the standard deviation of numbers or a range of values.
How to Make a Double Bar Graph In Google Sheets: Learn how to make a double bar graph in Google Sheets.