Are you sick of your coin tracking spreadsheets looking like this?
Are you sick of your coin tracking spreadsheets looking like this?
| BTC | Loading... | Loading... |
| ETH | Loading... | Loading... |
Rather than using Apps Script to fetch data via Javascript, this method uses only native functions to give you instant rates with no more "Loading" text every time you update a cell.
Includes pricing data for the top 5000 coins by market cap. No Javascript, no timeouts, no errors.
And no rate limits. Use this as much as you want, wherever you want.
There's nothing to install. Just copy and paste the formula below and you're done! (I confess you will see the loading text one time, but that's it, and no more for any new coins you track.)
=QUERY(IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US"), "SELECT Col3 WHERE Col1 = 'BTC'", 0)You can replace the BTC with any symbol, or you can use a cell reference (eg "SELECT Col3 WHERE Col1='" & A1 & "'").
Data is updated every 30 minutes and includes the top 5000 coins by market cap.
Alternatively you can import all rates at once, and use VLOOKUP or INDEX MATCH on your other
sheets to look up the rates. Create a blank sheet and put this into A1:
=IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US")Rates are in USD, so convert to any other currency using GOOGLEFINANCE, e.g.:
=QUERY(IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US"), "SELECT Col3 WHERE Col1 = 'BTC'", 0) * GOOGLEFINANCE("USDEUR")For Excel go to Data > From Web, and paste this URL: https://cryptorates.ai/files/standard.csv
If you want more columns (volume, 24h change, etc), use full.csv instead of standard.
To output the time the data was fetched, use this formula:
=QUERY(IMPORTDATA("https://cryptorates.ai/files/standard.csv", ",", "en_US"), "SELECT Col2 WHERE Col1 = 'Updated at'", 0)For any issues or questions, please raise them here: Github issue tracker