Retrieve cryptocurrency prices using in-built Google Sheets functions.
Syntax=GOOGLEFINANCE(ticker_symbol)ticker_symbol – a valid ticker symbol for the corresponding cryptocurrency with the currency to quote the price in.
Sample Usages=GOOGLEFINANCE("BTCUSD")
//Retrieves the price of Bitcoin in US dollar.=GOOGLEFINANCE("ETHEUR")
//Returns the price of Ethereum in euro.
In this article, we will 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 get crypto prices in Google Sheets? In this article, we will look at how to pull crypto prices in Google Sheets. By the end, you should be able to import 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 enables you to 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 is as given under.
=GOOGLEFINANCE(ticker_symbol)Where ticker_symbol is the valid ticker symbol for the cryptocurrency to fetch the price (called the base asset or currency) and the currency–crypto or fiat–to get the price in (also called quote asset or currency).
For example, to retrieve the price of bitcoin in US dollar, the ticker symbol is BTCUSD. Similarly, for Ethereum, it is ETHUSD. We can replace the base and the quote assets with any valid currency symbol.
In the image above, the prices of bitcoin and ethereum are pulled into Google Sheets. We can likewise pull the price for any of the supported cryptocurrencies into Google Sheets.
Let’s look at another method on how to pull crypto prices in Google Sheets.
Pull crypto price in Google Sheets using the IMPORTXML function
We can use the CoinMarketCap website–or indeed any other website with crypto data–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.
- Click on any currency page and copy the URL. For example, for bitcoin it is https://coinmarketcap.com/currencies/bitcoin/.
- Make a table consisting of the name and the corresponding URL addresses for the cryptocurrencies you want to fetch the price.
- Use the following formula to pull crypto prices in Google Sheets.
=IMPORTXML(URL, "//div[@class='priceValue ']")- Replace “URL” in the above formula with the cryptocurrency URL. Or simply reference the cell containing the URL.
- The IMPORTXML function lets us import data using the HTML code from 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.
Conclusion
Hence in this article, we learned how to pull Crypto prices in Google Sheets. We learned how to do so using two methods, one using Google Finance and the other using IMPORTXML. The Google Finance method is very simple but quite limited in use while with the IMPORTXML function, we can pull data from any website but it’s more complicated.
See Also
Loved this article on how to get crypto prices in 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.