In this post, we look at how to track cryptocurrencies using CoinGecko API data. This method involves using IMPORTDATA to import the cryptocurrencies prices (and also other data like 24h price changes percent etc) from CoinGecko API into one worksheet, and then subsequently referencing the parameters we need into a separate worksheet.
The final thing will look like below.
Why CoinGecko?
CoinGecko is the world’s largest independent cryptocurrency data aggregator with over 6,000 different cryptoassets tracked across more than 400 exchanges worldwide. You can find the information on majority of the more popular exchanges, cryptocurrencies, Defi coins out there in the cryptospace. If you are new to cryptocurrencies, you should visit CoinGecko just to get a sense of what's out there in the crypto space.
It also offers free usage of its cryptocurrencies API. A lot of the crypto-related services/products like Metamask, Trust Wallet, DappRaddar are actually using CoinGecko API.
How to use CoinGecko API for tracking crypto portfolio
The steps involved do not require you to install any addons, or run scripts. It's purely using some simple formulas to get the data parameters that we need.
1) First, let's go to the CoinGecko API page.
2) Click on the GET /coins/markets button, and hit "Try it out".
You will see that you are able to enter some data into the fields.
3) Enter USD into the currency field, bitcoin into the ids field,24h into the price_change_percentage field. Hit "execute".
This will generate a Request URL link, which you can copy into clipboard. See below.
h_ttps://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin&order=market_cap_desc&per_page=100&page=1&sparkline=false&price_change_percentage=24h
[NOTE: Please remove the underscore from h_ttps]
4) You can repeat the above steps by entering the crypto (i.e. litecoin) u want to track at the ids field, and hit "execute", to get the request url for that particular crypto.
Use IMPORTDATA to import the data from CoinGecko
Next, open up a new spreadsheet in Google Sheets, and you can name the sheet as "API data". Enter the below into the first cell.
=Importdata("h_ttps://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=bitcoin&order=market_cap_desc&per_page=100&page=1&sparkline=false&price_change_percentage=24h")
[NOTE: Please remove the underscore from h_ttps]
You should see a lot of data for bitcoin being imported into the sheet.
After this, you can create another sheet, which will be used to track the portfolio of the cryptos you owned. You can name the sheet as "Crypto Portfolio". You can create a table similar to what I did below.
Getting Current Price of Crypto
For the current price column, enter the below formulae. What this does is to extract the current price of the crypto from current_price:56129, which is the data at E1 of the "API data" sheet.
=right('API data'!E1, len('API data'!E1)-find(":",'API data'!E1))*1
Bitcoin price at E1 of "API data" sheet
Getting Current Price of Bitcoin from "API data" sheet
You can simply change the cell reference of the formulae from E1 to E2, E3,E4 etc, to get the current price of the other cryptos u have imported at the "API data" sheet.
Most Profitable and Least Profitable Crypto
The Most Profitable field gives you the highest ROI achieved. Use the below MAX formulae to get the highest ROI for ROI column, range J2 to J6.
=max(J2:J6)
For the crypto itself, use the below formulae to get the crypto with the highest ROI. If you want to understand how INDEX is being used, you can refer here.
=index(A2:A5,MATCH(I10,J2:J5,0))
For Least Profitable field gives you the lowest ROI achieved (ROI can be negative). Use the below MIN formulae to get the lowest ROI for ROI column, range J2 to J6.
=min(J2:J6)
For the crypto with lowest ROI, enter the below formulae.
=index(A2:A5,match(I14,J2:J5,0))
Overall, this method is better and more reliable than the method using IMPORTHTML I've shared earlier, as I don't foresee CoinGecko shutting access to the API, since a lot of crypto-related services/products are using their API.
If you like this post, please give it a like or ??. Thank you.
