Cryptofinance
Open CryptowatchCreate AccountPricingContact Us
Search…
Getting Started
Best Practices in Google Sheets
How to Refresh Rates with CRYPTOFINANCE in Google Sheets
Things to Know
Advanced Usage and Tips in Google Sheets
Blockchain Data
Bitcoin and Cryptocurrency Network Data in Google Sheets
Markets Data
Cryptowatch Data
Trade Data
Historical Data
Historical OHLCV
All-Time-Highs
Market Macro Data
Return on Investment Data
Order Books Data
Markets Liquidity
Market Order Book
Exchange Data
Exchange Data
Supported Exchanges
Disclaimer & Privacy
Risk Disclaimer
Privacy Statement
Terms of Service
Powered By GitBook
Best Practices in Google Sheets
Tips for a more efficient Cryptofinance sheet.

Use Full Listings

This means don’t use =CRYPTOFINANCE("BTC/USD") and other similar calls, but call a full market listing to get all markets data in 1 API request.
The benefits are a more responsive and lightweight sheet, faster data fetch and save you from hitting the Google Sheets hard quota of 20,000 request calls per day.
Here is how to use a full listing:
  1. 1.
    Create a new sheet, name it cryptodata, then on cell A1 type the formula =CRYPTOFINANCE("KRAKEN"). This will create as many rows as there are markets tracked on Kraken.
  2. 2.
    From there you can just reference this data from your actual work sheet like this =cryptodata!C2. This will show the price of the first market listed.
As the row order may change at each refresh (they are sorted by quote asset), here is how to make your cell references sticky:
1
=INDEX(cryptodata!A1:Z, MATCH("xbt/usd",cryptodata!B1:B,0), 3)
Copied!
Here is how it works, in order:
  1. 1.
    MATCH will search for xbt/usd in the sheet cryptodata on the column B and return the matching row number.
  2. 2.
    INDEX will look at the cryptodata sheet and return the value that is at the row number returned by MATCH and at the column 3. Column 3 is where the price is, you can change it to 14 for volume info, and so on.

Improve Formatting

Wrap you cell references with VALUE() to be able to format the cell.
So your references will look like, if you reference cell =B4: =VALUE(B4).
If you’re using a full market listing (you should), and use the sticky reference explained above, your reference looks like:
1
=VALUE(INDEX(cryptodata!A1:Z, MATCH("xbt/usd",cryptodata!B1:B,0), 3))
Copied!

Avoid Using Time-Driven Trigger

You might be tempted to code a small function, say to refresh the rates, and use a time-driven trigger to get your data refreshed each minute. The issue is that the rates will keep being refreshed even when the sheet is closed, and will make you hit the 20,000 API call quota too fast.
Many users, even with a small number of calls to CRYPTOFINANCE, are being blocked by the API call quota, even though simple math shows they should be from the quota limit. It is not possible to know how much of the current quota has been use at any given time.
Previous
Getting Started
Next
How to Refresh Rates with CRYPTOFINANCE in Google Sheets
Last modified 11mo ago
Copy link
Contents
Use Full Listings
Improve Formatting
Avoid Using Time-Driven Trigger