How-To

How to Import Cryptocurrency Prices Into Google Sheets

By Adam Davidson

Published June 21, 2023

Do you want to keep on top of the latest crypto price rises (and falls)? Learn how to import cryptocurrency prices into Google Sheets using this guide.

If you own any cryptocurrency or you’re thinking about buying some, you’ll want to keep track of the current prices. That way, you’ll know if it’s the right time to buy or sell. There are plenty of websites that can provide this information, but you might prefer to use a spreadsheet to keep track of prices over time, or to do other calculations.

If you’re a fan of Google Sheets, it’s possible to get cryptocurrency price data into your spreadsheets and then use them how you wish. Learn how to import cryptocurrency prices into Google Sheets below.

How to Import Cryptocurrency Prices using GOOGLEFINANCE in Google Sheets

The GOOGLEFINANCE function allows you to pull live information on a wide range of traditional currencies directly from Google. It also allows you to pull prices for some of the most popular cryptocurrencies, including Bitcoin, Ethereum, Litecoin, Cardano, and Binance Coin, but it doesn’t work with others such as Dogecoin.

To import cryptocurrency prices using GOOGLEFINANCE:

1. Open a Google Sheets document.
2. Select the cell where you want the price to appear.
3. For the price of Ethereum, enter the following formula:

   =GOOGLEFINANCE(“ETHUSD”)

   

4. For the price of Ethereum, enter the following formula:

   =GOOGLEFINANCE(“BTCUSD”)

5. If you want to import other cryptocurrencies, you’ll need to include CURRENCY in the formula. For example, for Litecoin, enter the following formula:

   =GOOGLEFINANCE(“CURRENCY:LTCUSD”)

6. You can replace LTC with many of the more popular cryptocurrency codes, such as ADA and XRP, but it won’t work with four-letter codes such as DOGE and LINK.
7. Press Enter and the price will calculate.
   
8. Your spreadsheet will automatically update periodically as the price of your selected cryptocurrency changes. This usually happens after 20 minutes or so.

How to Import Cryptocurrency Prices Using IMPORTXML in Google Sheets

If you want to import cryptocurrency prices that the GOOGLEFINANCE function doesn’t handle, then one option is to scrape the data directly from a cryptocurrency website. The IMPORTXML function allows you to pull the data from the webpage and bring it into your Google Sheets document.

To import cryptocurrency prices using IMPORTXML:

1. In an empty cell, paste the URL for the webpage containing your cryptocurrency price data. For example, for the Ethereum page of CoinMarketCap.com, you would paste the URL https://coinmarketcap.com/currencies/ethereum/

2. Navigate to that page in Chrome, and right-click on the price you want to import.
3. Select Inspect.
   
4. The value you selected should be highlighted. If not, click on the price in the left-hand pane.
5. Just above the price, you should see a div name.
   
6. Right-click on this line and select Copy Element.
7. Paste this somewhere into your spreadsheet and highlight and copy just the class name, such as class=”priceValue “
8. In the cell next to the URL, type:

   //div[@

9. Paste the class name that you just copied and type a closed square bracket. Your cell should now read something like:

   //div[@class="priceValue "]

   

10. Now select the cell where you want your cryptocurrency price data to appear and type the following:

    =IMPORTXML(

11. Click in the cell containing your URL, type a comma, click in the cell containing your div class, and finally type a closed bracket. Your formula should look something like this:

    =IMPORTXML(G10,H10)

    

12. Press Enter and the cryptocurrency price data should be scraped from the website.

How to Refresh Scraped Cryptocurrency Prices in Google Sheets

The data you have scraped will update once per hour. To keep the value updated more frequently, you can force the cell to recalculate. One fairly simple way to do so is to create a macro that copies the cell, deletes it, and pastes it back in again. This will cause the cell to recalculate which should pull the most recent data from the cryptocurrency website.

To refresh scraped cryptocurrency prices using a macro:

1. Open the Google Sheets spreadsheet containing your scraped data.
2. In the menu, click Extensions.
   
3. Hover over Macros and select Record Macro.
   
4. Once the macro is recording, click the cell with your scraped data.
5. Press Ctrl+C on Windows or Cmd+C on Mac to copy the cell.
6. Press Delete or Backspace to delete the cell.
7. In the same cell, press Ctrl+V on Windows or Cmd+V on Mac to paste the formula back into the cell.
8. Click Save at the bottom of the screen to save your macro.
   
9. Give your macro a name. If you wish, you can also add a keyboard shortcut.
   
10. Click Save.
11. To run your macro, press your keyboard shortcut, or go to Extensions > Macros and click on your macro name.
    
12. Your cell should now refresh and the data inside it should update.

How to Import Cryptocurrency Prices Using IMPORTDATA

You can also import cryptocurrency data using a website specifically designed for this purpose. The IMPORTDATA function allows you to pull price information directly from this website. The data will refresh once an hour.

To import cryptocurrency prices using IMPORTDATA:

1. Click in the cell where you want the price data to appear.
2. Enter a formula in the following format:

   =IMPORTDATA(https://cryptoprices.cc/ETH/)

   

3. You can change ETH to the code of a variety of different cryptocurrencies. See https://cryptoprices.cc/sitemap.txt for the full list of options.
4. Press Enter and you should see the current price.

How to Import Cryptocurrency Prices Using the CoinMarketCap API

Another way to get cryptocurrency prices from CoinMarketCap is to use its API. By creating an account and generating your own API key, you can poll the site directly and import a huge amount of cryptocurrency information into your Google Sheets document.

In order to get the data into Google Sheets, you’ll need to use an add-on called API Connector.

To import cryptocurrency prices using the CoinMarketCap API:

1. Open your Google Sheets document and click the Extensions menu.
   
2. Hover over Add-ons and click Get Add-ons.
   
3. In the search field, type API Connector and press Enter.
   
4. Select API Connector in the search results.
   
5. Click Install.
   
6. Sign in to your Google account and allow the required permissions.
7. To set up your CoinMarketCap API, navigate to https://coinmarketcap.com/api/
8. Click Get Your API Key Now.
   
9. Enter your details, select Basic as your plan type, and click Create My Account.
   
10. You’ll be emailed to verify your account. Click the link, and you’ll be redirected to the API dashboard.
11. Hover over the API Key and click Copy Key.
    
12. Return to your Google Sheets document and click Extensions.
    
13. Hover over API Connector and click Open.
    
14. Click Create Request.
    
15. Under Application select CoinMarketCap from the drop-down box.
    
16. Click in the API Key field and paste your copied API key.
    
17. Under Endpoint, select /v2/cryptocurrency/quotes/latest from the drop-down.
    
18. Click the Plus (+) symbol next to Aux and select any of the options from the drop-down—the request won’t run without a selection in this field.
    
19. Click the Plus (+) symbol next to Slug and select the coin or coins you want to pull data for.
    
20. Select a cell in your spreadsheet where you want the first cell of the pulled data to appear, and click Set Current.
    
21. Give your request a name and click Save.
    
22. Click Run and your request will run.
    
23. The column USD.price contains the current price information.
    

How to Update Cryptocurrency Prices Using API Connector

The price generated by the CoinMarketCap API will not automatically update. However, it is possible to get API Connector to refresh your data at regular intervals from once an hour up to once a month. This will help you to ensure that your cryptocurrency prices are up-to-date.

To update cryptocurrency prices using API Connector:

1. Click the Extensions menu.
   
2. Hover over API Connector and select Open.
   
3. Select the Schedule tab.
   
4. Click Create Trigger.
   
5. Under API Request, select the name of the request you created in the section above.
   
6. Under Run Request, select your refresh time.
   
7. Enter a name under Trigger Name and click Save.
8. Your cryptocurrency data will now refresh at the frequency that you selected.

Checking Your Portfolio in Google Sheets

Learning how to import cryptocurrency prices into Google Sheets means you can use that price data in whatever calculations you wish. You’ll be able to monitor prices, and even set up some conditional formatting to alert you when the cryptocurrency hits a certain price.

There are plenty of other useful Google Sheets tricks you can learn. You might want to learn how to use timestamps in Google Sheets, for example. Or you may want to know how to remove dollar signs in Google Sheets if some of your scraped data has included them. You might also want to learn how to merge sheets in Google Sheets to help you organize your data.