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.
- Open a Google Sheets document.
- Select the cell where you want the price to appear.
- For the price of Ethereum, enter the following formula:
=GOOGLEFINANCE(“ETHUSD”)
- For the price of Ethereum, enter the following formula:
=GOOGLEFINANCE(“BTCUSD”)
- 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”)
- 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.
- Press Enter and the price will calculate.
- 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:
- Open the Google Sheets spreadsheet containing your scraped data.
- In the menu, click Extensions.
- Hover over Macros and select Record Macro.
- Once the macro is recording, click the cell with your scraped data.
- Press Ctrl+C on Windows or Cmd+C on Mac to copy the cell.
- Press Delete or Backspace to delete the cell.
- In the same cell, press Ctrl+V on Windows or Cmd+V on Mac to paste the formula back into the cell.
- Click Save at the bottom of the screen to save your macro.
- Give your macro a name. If you wish, you can also add a keyboard shortcut.
- Click Save.
- To run your macro, press your keyboard shortcut, or go to Extensions > Macros and click on your macro name.
- 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.
- Click in the cell where you want the price data to appear.
- Enter a formula in the following format:
=IMPORTDATA("https://cryptoprices.cc/ETH/") - 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.
- 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:
- Open your Google Sheets document and click the Extensionsmenu.
- Hover over Add-ons and click Get Add-ons.
- In the search field, type API Connector and press Enter.
- Select API Connector in the search results.
- Click Install.
- Sign in to your Google account and allow the required permissions.
- To set up your CoinMarketCap API, navigate to https://coinmarketcap.com/api/
- Click Get Your API Key Now.
- Enter your details, select Basic as your plan type, and click Create My Account.
- You’ll be emailed to verify your account. Click the link, and you’ll be redirected to the API dashboard.
- Hover over the API Key and click Copy Key.
- Return to your Google Sheets document and clickExtensions.
- Hover over API Connector and click Open.
- Click Create Request.
- Under Application select CoinMarketCap from the drop-down box.
- Click in the API Key field and paste your copied API key.
- Under Endpoint,select /v2/cryptocurrency/quotes/latest from the drop-down.
- 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.
- Click the Plus (+) symbol next to Slug and select the coin or coins you want to pull data for.
- Select a cell in your spreadsheet where you want the first cell of the pulled data to appear, and click Set Current.
- Give your request a name and click Save.
- Click Run and your request will run.
- 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.
- Click the Extensionsmenu.
- Hover over API Connector and select Open.
- Select the Scheduletab.
- Click Create Trigger.
- Under API Request, select the name of the request you created in the section above.
- Under Run Request, select your refresh time.
- Enter a name under Trigger Name and clickSave.
- 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.
Was this article helpful?
Awesome, share it:
ShareTweetRedditLinkedInEmail
Thanks!
Thanks for getting in touch with us.
Not enough detailsOutdated informationHard to understandOther
Click to comment
Leave a Reply
Related Items:Google
