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:
EDIT: Since this article was initially written the CMC API has changed. I’ve updated this to continue pulling all coins.
Hit enter…after some loading you should see magic happen. Your spreadsheet is now populated with every coin and all the publicly available data CMC has:
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.