Using Live Crypto Prices in Google Sheets
Google Sheets is an amazing way for me to keep track of my crypto portfolio, as well as how much and where it's invested or held.
It's nice to see a live updated price for my portfolio net worth, similar to using the GoogleFinance() function to return the almost live price of stocks. I am currently using the GDAX API to query the last traded price for each cryptocurrency listed on the site, and then stripping away the extra characters in the response to be able to use the price as a numeric value.
To get the price of BTC in USD, I have this formula in the google sheets cell:
=VALUE(SUBSTITUTE(SUBSTITUTE(INDEX(IMPORTDATA("https://api.gdax.com/products/BTC-USD/ticker"),0,2), "price:",""), """", ""))
Replacing the 'BTC-USD' with 'LTC-USD' or 'ETH-USD' will return the last traded price for either of those trading pairs.
I'm also able to track any coin that is listed on coinmarketcap.com using a similar function. For example:
=INDEX(IMPORTHTML("https://coinmarketcap.com/assets/omisego/","table",0),2,5)
returns the USD value of OMG. To track any other currency, it just involves copying the URL from coinmarketcap into the IMPORTHTML() function.
I've been blown away with how much benefit there is in using google sheets over excel with the ability to get live data (may be delayed by up to 15 min). I briefly looked at what it would take to implement the same kind of functionality in excel, but external data connections are required, and the entire process is much more involved than copying a formula to a cell in Google Sheets.
Hope someone finds this information useful.
Thanks for this. I'm trying to get the % change for the pairs, using BTC, ETH and USDT as the base pair. I can't seem to figure it out. I'm sure it's just a maths equation using the CoinMarketCap data but my brain can't work it out. :-(
@ductapeprodigy, THANKS! I found it really useful. I was looking for the https string to pull GDAX quotes because I'm too dumb to figure out their API. There it was, embedded in your goog sheets formula. I use excel and got some tips from another steemer, @tomshwom, about setting up the data connections in excel. Steem is proving to be a pretty useful place for me. Thanks again, Rick
Congratulations @ducttapeprodigy! You received a personal award!
You can view your badges on your Steem Board and compare to others on the Steem Ranking
Vote for @Steemitboard as a witness to get one more award and increased upvotes!