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
• Part 2: Create your API Request URL
• Part 3: Pull Trading Pair API Data into Sheets
• Part 4: Get More CryptoCompare Data
• Appendix A: Get Trading Pair Signals
• Appendix B: Get Social Info For Crypto
• Appendix C: [PRO Only] Get Crypto Exchange Data
• Appendix D: [PRO Only] Get Gambling Venue Data

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.
   
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’
   
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.
   
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.
   

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
   
   
3. Under Headers enter a set of key-value pairs like this:
   

   authorization

   Apikey YOUR_API_KEY

   Replace YOUR_API_KEY with the API key you received above in step 6.
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:
   

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

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)

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! 🙂

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.

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:

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:

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