How-To

How to Import Cryptocurrency Prices Into Google Sheets

How to Import Cryptocurrency Prices Into Google Sheets

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 stocks and 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.

  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”)

    googlefinance formula

  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.
    googlefinance result
  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 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.
     google sheets extensions
  3. Hover over Macros and select Record Macro.
    google sheets 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.
    google sheets save macro
  9. Give your macro a name. If you wish, you can also add a keyboard shortcut.
    google sheets name macro
  10. Click Save.
  11. To run your macro, press your keyboard shortcut, or go to Extensions > Macros and click on your macro name.
    google sheets run macro
  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.

  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/")

    importdata formula

  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.
     google sheets extensions
  2. Hover over Add-ons and click Get Add-ons.
    google sheets get add ons
  3. In the search field, type API Connector and press Enter.
    google sheets search add ons
  4. Select API Connector in the search results.
    google sheets api connector
  5. Click Install.
    google sheets install api connector
  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.
    coinmarketcap get api key
  9. Enter your details, select Basic as your plan type, and click Create My Account.
    coinmarketcap create 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.
    coinmarketcap copy key
  12. Return to your Google Sheets document and click Extensions.
     google sheets extensions
  13. Hover over API Connector and click Open.
    google sheets open api connector
  14. Click Create Request.
    api connector create request
  15. Under Application select CoinMarketCap from the drop-down box.
    api connector appllication
  16. Click in the API Key field and paste your copied API key.
    api connector authorization
  17. Under Endpoint, select /v2/cryptocurrency/quotes/latest from the drop-down.
    api connector endpoint
  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.
    api connector aux
  19. Click the Plus (+) symbol next to Slug and select the coin or coins you want to pull data for.
    api connector slug
  20. Select a cell in your spreadsheet where you want the first cell of the pulled data to appear, and click Set Current.
    api connector set current
  21. Give your request a name and click Save.
    api connector name
  22. Click Run and your request will run.
    api connector run
  23. The column USD.price contains the current price information.
    quote usd price

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.

  1. Click the Extensions menu.
     google sheets extensions
  2. Hover over API Connector and select Open.
    google sheets open api connector
  3. Select the Schedule tab.
    api connector schedule tab
  4. Click Create Trigger.
    api connector create trigger
  5. Under API Request, select the name of the request you created in the section above.
    api connector request name
  6. Under Run Request, select your refresh time.
    api connector run request frequency
  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.

1 Comment

1 Comment

  1. Igor

    May 13, 2024 at 4:48 am

    Thank You sooooo much!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top