Nearly 3 years ago I made a free tool for r/cryptocurrency to get live crypto rates into Google Sheets and Excel, without needing to use Javascript and without any rate limits.
This service is still going strong and I've made some big improvements recently:
- Includes the data for the top 5000 coins by 24-hour volume.
- Prices are now updated every 5 minutes.
- I have added API access so you can use this in any application; it doesn't need to be only in Google Sheets / Excel.
- All-time high and low have been added to the dataset along with some other useful columns.
- And most importantly: no rate limits! You can fetch from this service as much as you like. I make heavy use of Cloudflare's free caching functionality, so the load on my server is very minimal. Cloudflare does the heavy lifting for me.
To get any rate into Google Sheets is as simple as copying and pasting this formula; for example to get the rate of PancakeSwap:
=QUERY(IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US"), "SELECT Col3 WHERE Col1 = 'CAKE'", 0) Just change CAKE to the coin of your choice or use a cell reference.
If you want you can reference the rate directly, however this will result in your sheet being slower if you're wanting to track more than one rate: =IMPORTDATA("https://cryptorates.ai/v1/price/BTC")
You can see in this animation how once you've fetched the rate for one coin, all the other coins will have their rate instantly appear without any "Loading…" text:
Adding multiple rates animation
These of course can be converted to any currency; for example to get PancakeSwap in Euro (EUR):
=QUERY(IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US"), "SELECT Col3 WHERE Col1 = 'BTC'", 0) * GOOGLEFINANCE("USDEUR") If you want to track the rates of a lot of coins, it's best to use the VLOOKUP method to make things even faster.
I have also done a lot of work on the documentation, so please let me know if anything is unclear or you get stuck anywhere. Docs are here: https://cryptorates.ai/
In the full dataset you have access to these columns:
| Field | Description |
|---|---|
| Symbol | Crypto ticker/symbol |
| Name | Name |
| Rank | The global rank of this coin by Market Cap |
| Price USD | Current price in USD |
| Volume 24h | Global trading volume in USD over the last 24 hours |
| Market cap | Market cap according to CoinMarketCap's methodology |
| Supply | Current supply in circulation |
| Change 24h | The price change over the last 24 hours (1% change would be returned as 0.01) |
| Change 7d | The price change over the last 7 days (1% change would be returned as 0.01) |
| ATH | The all-time high price |
| ATH date | The all-time high date in Unix time (in seconds) |
| ATL | The all-time low price |
| ATL date | The all-time low date in Unix time (in seconds) |
Original post in Jan 2022: https://www.reddit.com/r/CryptoCurrency/comments/sdvlqj/how_to_get_all_live_crypto_rates_in_google_sheets/
[link] [comments]
You can get bonuses upto $100 FREE BONUS when you:
💰 Install these recommended apps:
💲 SocialGood - 100% Crypto Back on Everyday Shopping
💲 xPortal - The DeFi For The Next Billion
💲 CryptoTab Browser - Lightweight, fast, and ready to mine!
💰 Register on these recommended exchanges:
🟡 Binance🟡 Bitfinex🟡 Bitmart🟡 Bittrex🟡 Bitget
🟡 CoinEx🟡 Crypto.com🟡 Gate.io🟡 Huobi🟡 Kucoin.
Comments