how to get live crypto prices in google sheets thumbnail

How To Get Live Crypto Prices in Google Sheets in Only 1 Minute (Easy!)

Want to ditch using third party portfolio trackers and learn how to get live crypto prices in google sheets? This blog post is for you!

Track Your Crypto Portfolio in Real-time with CoinGecko and Google Sheets

Are you wondering how to get live crypto prices in Google Sheets? Well this is the guide for you! Welcome to our tutorial on how to get live crypto prices from CoinGecko and put them in a Google Sheet. Cryptocurrency prices can be highly volatile, and staying up-to-date on the latest market trends is crucial. With CoinGecko‘s website and Google Sheets, you can easily track the prices of your favorite coins in real-time, and create a dynamic, automatically updating spreadsheet to monitor your portfolio. This is a simple and efficient way to keep an eye on your investments and stay ahead of the market. In this post, we will walk you through the process step-by-step and provide you with the tools you need to know how to get live crypto prices in your Google Sheets crypto portfolio today.

Tutorial: How To Get Live Crypto Prices in Google Sheets

  1. Go to a website, which tracks cryptocurrency prices, such as CoinGecko.com, and find the cryptocurrency you want to track.
How To Get Live Crypto Prices in Google Sheets
This is the CoinGecko page for Bitcoin (BTC).

2. Go to your Google Sheet; enter the following formula in the cell that you want the cryptocurrency price to be.

=IMPORTXML("","")

3. Now paste the URL of the CoinGecko page into the first pair of ” “.

The URL of the page containing the Bitcoin Price.
=IMPORTXML("https://www.coingecko.com/en/coins/bitcoin","")

4. Now we have to copy the Full XPATH of the text, which shows us the price, to do that

Right-mouse click on the price and then click ‘Inspect‘ on the menu that appears.

A menu should appear on the right, as shown below.

You should see a highlighted section of code, indicated with the red arrow. In that code you should see the current price of the cryptocurrency in white.

Now simply Right mouse click on the aforementioned section of code and click on Copy > Copy Full XPath

Then finally go back to your Google Sheet, and paste the value you copied, into the second pair of ” “, from the formula.

Your formula on Google Sheets should look like this

=IMPORTXML("https://www.coingecko.com/en/coins/bitcoin","/html/body/div[5]/div[5]/div[1]/div/div[1]/div[3]/div/div[1]/span[1]/span")

You should now be able to see the price within your spreadsheet. The price will update whenever you reload your spreadsheet (assuming the data on the page, from which you are pulling the data has also changed).

Bonus: Quickly get the Price for many Cryptocurrencies

If you have a list of cryptocurrency names, it is possible to quickly fetch the price for all of them. This is because the XPath on each page is the same; the only thing that changes is the cryptocurrency name in the URL.

To simply drag down and get the current cryptocurrency price for a list of cryptocurrencies, as shown above, use the following formula seen below.

=IMPORTXML(CONCATENATE("https://www.coingecko.com/en/coins/",LOWER($A2)),"/html/body/div[5]/div[5]/div[1]/div/div[1]/div[3]/div/div[1]/span[1]/span")

The CONCATENATE() function allows us to combine multiple strings together.

The LOWER() function turns whatever word is inside of it to lowercase. This is required as the URL structure of CoinGecko, is case-sensitive.

$A2 is the cell location of the name of the cryptocurrency (in the case of B2). The reason we put a $ in front of the A, is so that the formula can only be dragged vertically and not horizontally.

Here’s how you can make passive income with crypto.

Concluding remarks

In conclusion, using CoinGecko’s website in combination with Google Sheets is a great way to easily keep track of live cryptocurrency prices. With just a few simple steps, you can set up a dynamic, automatically updating spreadsheet to monitor your favorite coins and track their performance over time. Whether you’re a professional trader or a casual investor, this powerful tool can help you stay on top of the ever-changing crypto market. Thanks for reading, and happy tracking!