APIs & Google Sheets — Picking Out Microcaps Made Easy

CarpeCrypto
Sep 14, 2017 · 4 min read

A few months ago nik posted an excellent article — Picking Out Microcaps 101. — with a process for microcap hunting. I’m lazy so I wanted to make the process as easy as possible. My solution was to use a combination of CoinMarketCap’s API and Google Sheets to make the first step in the process — weeding out shitcoins — easy.

With the API we can quickly load all coin data into a spreadsheet and filter out high marketcap, high coin supply, low volume coins.

Using an API in Google Sheets is easy so no worries if you’re not technical. I’ll explain everything below. Eventually you need to put in honest work to subjectively evaluate these coins but this should help you get there.

Setting up Google Sheets

First thing you’ll need to do is create a new Google Sheet doc so head over to Google Drive and do that (top-left corner).

In order to use an API in Google Sheets you need to use the Script Editor to create a custom function that takes an API URL (we’ll get that later) and returns the data you want in the form of JSON (doesn’t really matter to know).

Go to Tools > Script editor:

From here, add a new Script file so go to File > New > Script file and give it the name “ImportJSON”:

You should have a page like this:

You’ll want to delete everything in the ImportJSON file so it’s completely blank:

Go to this pastebin link, copy everything from there, and paste it into your ImportJSON file. It should look like this:

It will have a lot more lines than this

If what you’re seeing makes no sense that’s OK because we’re done here. Just make sure it’s saved (File > Save)

Getting CoinMarketCap Data

Now we get to enjoy the fruits of our labor. We’re going to use the ImportJSON function we created to call CoinMarketCap’s API and populate our spreadsheet with all the data. Head back to your spreadsheet, and in the first cell type this:

=ImportJSON("https://api.coinmarketcap.com/v1/ticker/?limit=0") [OLD]

EDIT: CoinMarketCap is shutting down the API used when this was initially written. They have a new API that is still free but you need to create an account.

Useful links for using new API:

https://pro.coinmarketcap.com/migrate

https://medium.com/@moralesgersonpa/how-to-import-coinmarketcap-professional-api-ex-excel-php-49759f10a69d

You’ll now put something like this in the first cell:

=ImportJSON(“https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?CMC_PRO_API_KEY=YOU-API-KEY&start=1&limit=5000”) [NEW]

The new API limits you to pulling 5000 at a time, which as of the time of this writing is more than enough.


Magic

Quick aside — with the ImportJSON function we created you can do some more cool things with the CMC API such as only get the current BTC price of Geocoin:

=ImportJSON(“https://api.coinmarketcap.com/v1/ticker/geocoin/", “/price_btc”, “noHeaders”)

Filter Out Shitcoins

With all the data now in a spreadsheet it’s pretty easy to filter out what you don’t want. First I copy everything in the spreadsheet, paste, then select “Paste values only” just to avoid accidentally refreshing all the data:

I get rid of columns I don’t care about for this — id, Rank, Price Usd, Price Btc, Percent Change 1 hr, Percent Change 24 hr, Percent Change 7d

Then I follow nik’s steps:

  • Sort by market cap (Highlight column headers and go to Data > Filter)

That’s it! Now you go look at the remaining coins and make note of what you like/dislike.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store