APIs & Google Sheets — Picking Out Microcaps Made Easy
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:
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
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.
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)
- Remove rows with greater than $250,000 market cap
- Remove rows with higher supply (≈50m or higher)
- Add a column that’s 24 Volume USD/Market Cap USD. Remove rows with less than a 2% ratio here.
- Add a column that’s Available Supply/Total Supply. More of a subjective call but I look for things at least over 66% here, usually higher.
That’s it! Now you go look at the remaining coins and make note of what you like/dislike.
