Import CryptoCompare Data to Google Sheets

CryptoCompare provides streaming pricing data for cryptocurrencies along with a plethora of other blockchain-relevant data. In this guide, we’ll walk through how to pull cryptocurrency data from the CryptoCompare API directly into Google Sheets, using the API Connector add-on for Sheets. We’ll first get an API key from CryptoCompare, and then set up a request to pull in cryptocurrency exchange rate details to your spreadsheet.

The end of this article contains several example templates to get you started analyzing crypto, social, and gambling data using the CryptoCompare API.

PART 1: GET YOUR CRYPTOCOMPARE API KEY

  1. If you haven’t already, navigate to https://min-api.cryptocompare.com/pricing and click Get your free key.
    cryptocompare-img1
  2. Fill out your details and click Sign Up
  3. A moment later you’ll receive an email. Click on the email to verify your account, and you’ll land on your new CryptoCurrency dashboard. Click https://www.cryptocompare.com/cryptopian/api-keys to access the API key section of your account.
  4. Click ‘Create an API key’
    cryptocompare-img2
  5. Give your API key a name, it can be anything but here we’ll call it ‘Crypto for Google Sheets’. Select the permissions you’d like, and click Add.
    cryptocompare-img3
  6. You’ll now see your API key. Congrats, you now have access to the CryptoCompare API! Copy this and keep it handy, we’ll need it in a moment.
    cryptocompare-img4

PART 2: CREATE YOUR CRYPTOCOMPARE API REQUEST URL

We’ll follow the CryptoCompare documentation to access the current price of BTC (Bitcoin) across several fiat trading pairs.

  • API root: https://min-api.cryptocompare.com
  • Endpoint: /data/price
  • Query Strings: ?fsym=BTC&tsyms=USD,JPY,EUR

Putting it together, we get the full API Request URL:

https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=USD,JPY,EUR

PART 3: PULL TRADING PAIR API DATA INTO SHEETS

We can now enter all our values into API Connector and import CryptoCompare API data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created
    cryptocompare-img5
  3. Under Headers enter a set of key-value pairs like this:
    authorizationApikey YOUR_API_KEY
    Replace YOUR_API_KEY with the API key you received above in step 6.cryptocompare-img6
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see the latest exchange rates for Bitcoin in your Google Sheet:
    cryptocompare-img7

PART 4: GET MORE CRYPTOCOMPARE DATA

Experiment with endpoints and query strings as described in the documentation to see other types of cryptocurrency data. For example, you can see top coins by total volume in the last 24 hours by entering

https://min-api.cryptocompare.com/data/top/totalvolfull?limit=10&tsym=USD
If you’re looking for a more dynamic, ticker-like experience, check out the API Connector custom function IMPORTAPI() for faster refresh rates.

APPENDIX A: GET TRADING PAIR SIGNALS

In addition to typical crypto/fiat pairs, CryptoCompare offers a range of metrics for varied blockchain-related data. Let’s dig into what trading pair signals are. From the documentation, this data is provided by a machine learning service known as IntoTheBlock, a blockchain machine learning service that leverages AI to help you trade more efficiently. Let’s start by pulling in some data for BTC with the following GET request:

https://min-api.cryptocompare.com/data/tradingsignals/intotheblock/latest?fsym=BTC

If we navigate over to the analysis section of Bitcoin, we can see the 4 metrics provided by IntoTheBlock. These include:

  • Net Nework Growth (True growth, or [New addresses – addresses that go to zero])
  • Large Transactions (Transactions > $100,000)
  • Concentration (Change of large holder’s positions)
  • In The Money (Increasing number of profiting addresses = bullish signal)
cryptocompare-img8

APPENDIX B: GET SOCIAL INFO FOR CRYPTO

Sentiment analysis has become a useful tool in the traders toolkit. By no means as comprehensive as services like CryptoMood, who pride themselves on sentiment analysis of blockchain projects, CryptoCompare can spit out some raw data for doing your own analysis. Let’s start with a URL to return some basic stats on how a crypto is viewed in various online communities. We’ll start with the controversial but possibly cutest crypto, DOGE:

https://min-api.cryptocompare.com/data/social/coin/latest?coinId=4432

Note: In order to get the coinId of a crypto, you will need to either:

  • Run the following request in a separate sheet
  • Or on CryptoCompare’s built in documentation tool here

Anyway, this is the URL you will need in either case.

https://min-api.cryptocompare.com/data/blockchain/list

If you decided to run this in API Connector, you will need to modify a few more things to get the data to return correctly. First, change the Report Style to “concatenate” by going to Output options -> Report style. Next, you will need to add Data.values(@) to the JMES path. Sorry, API Connector Pro+ users only 🙁

Either method you used by now you should be able to track down the coinId you want.

After running the request, we should get several summary stats for various social media platforms that include:

  • CrpyoCompare’s own social activity
  • Twitter
  • Reddit
  • Facebook
  • GitHub

Another Note: times will come back as Unix Epoch Time. You can convert this by running =arrayformula(D2:D/86400+date(1970,1,1)) in a column adjacent to the date.

You can also choose to get summary statistics for the last 30 days by running the following URL request:

https://min-api.cryptocompare.com/data/social/coin/histo/day?coinId=4432

At a casual glance, it looks like Reddit has some interesting peaks/variation at different points in the month versus other social media services… Coincidence? Go find out! 🙂

cryptocompare-img9

APPENDIX C: [PRO ONLY] GET CRYPTO EXCHANGE DATA

In response to inflated and fake trade volume, many blockchain websites like CyptoCompare began analyzing and releasing metrics for how to score crypto exchanges on things other than just trade volume. You can read more about how they measure them here. This data can help you decide which exchanges are best for trading on, releasing your own blockchain project, market research, or just out of general curiosity. Pop this URL Request in API Connector:

https://min-api.cryptocompare.com/data/exchanges/general

Once you’ve got that in API Connector, we need to modify our output options a bit to get it wrangled into Sheets properly. Like in Appendix B, first change the Report Style to “concatenate” by going to Output options -> Report style. Next, you will need to add Data.values(@) to the JMES path.

You should end up with a Sheet that pretty much mirrors the link provided at the beginning of this Appendix. We can see scores related to Know Your Customer (KYC), Asset Diversity, Legal, and others.

cryptocompare-img10

APPENDIX D: [PRO Only] GET GAMBLING VENUE DATA

Gambling has become somewhat synonymous with the rise of Dapps due to the ease of implementing smart contracts in this context. They are also really exciting and lend themselves to the wild-west, speculative nature of crypto, so they naturally tend to attract a lot of crypto users. CryptoCompare has started to integrate with various crypto gambling venues across the web and has begun to receive ratings from users. You can access this data and other attributes by using the following URL in API Connector:

https://min-api.cryptocompare.com/data/gambling/general

Once you’ve got that in API Connector, we need to modify our output options a bit to get it wrangled into Sheets properly. Like in Appendix B, first change the Report Style to “compact” by going to Output options -> Report style. Next, you will need to add Data.values(@) to the JMES path.

We can now see some interesting things like what kind of crypto they take, what gambling methods are available, and other special features. However, due to the format we used to pull the data in, a lot of our attributes are compressed into a single column like in the screenshot below:

cryptocompare-img11

Ideally, we would like to see some of the values in this columns expanded out into their own columns and pseudo “one hot encoded” (OHC). This is especially useful if we want to do any kind of visualization or machine learning later on. You can follow this tutorial I wrote up to accomplish this. If you follow that tutorial, you should end up with a sparse matrix that looks something like this:

cryptocompare-img12

And there you have it! You can now figure out what kinds of games which gambling sites have along with any other metrics provided.

3 thoughts on “Import CryptoCompare Data to Google Sheets”

    • Hey Doug! Sorry you’re having trouble. You can see full information on error messages here, but basically this error is usually associated with large requests timing out, since Google Sheets has a 6 minute / execution request limit. I checked this file and the issue is not that it’s huge, but that it has a deeply nested structure, which takes a long time to flatten out to cells and potentially runs into that execution limit. In my own test it worked when I switched to compact mode, since that runs a bit faster, can you please try that?

      Reply

Leave a Comment