Import CryptoCompare Data to Google Sheets
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.
- Before You Begin
- 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
BEFORE YOU BEGIN
Click here to install the API Connector add-on from the Google Marketplace.
PART 1: GET YOUR CRYPTOCOMPARE API KEY
- If you haven’t already, navigate to https://min-api.cryptocompare.com/pricing and click Get your free key.
- Fill out your details and click Sign Up
- 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.
- Click ‘Create an API key’
- 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.
- 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:
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.
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- Under Headers enter a set of key-value pairs like this:
Replace YOUR_API_KEY with the API key you received above in step 6.
authorization Apikey YOUR_API_KEY
- We don’t need any extra authentication so just leave that set to None. Create a new tab and click ‘Set current’ to use that tab as your data destination.
- 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
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:
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:
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.
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
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:
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! 🙂