Automatically Refresh 110,000 Crypto Assets in Real Time or at Any Interval in Excel & Googlesheets using Cryptosheets

Chris Ware
May 7 · 4 min read

Keeping crypto data up to date in a spreadsheet can be a real pain in the #ss. No more VBA, no shady scripts or downloads, no complicated triggers & you can use it anywhere. Here’s how to do it with one single formula

Automatically refresh any crypto data set real time or at custom intervals with Cryptosheets for Excel & Googlesheets

OVERVIEW

Any Cryptosheets custom function, data set or endpoint accessible in Cryptosheets can easily and automatically be refreshed or updated at any custom interval in Excel or Googlesheets* using one single formula. The formulas are slightly different in Excel vs Googlesheets and there are limitations in Googlesheets, there are none in Excel.

Image for post
Image for post
Automatic refresh is available to paid subscriptions (hobbyist and up*)

This article covers:

  1. How Does It Work?
  2. Example Use Cases
  3. How to Upgrade Your Subscription
  4. FAQ & Troubleshooting

❓ HOW DOES IT WORK?

Automatic refresh for 14 period RSI on 1 second OHLCV candles at Coinbase… using 1x single formula → (#WOW)

Excel

  1. Type =CS.TIME(# of seconds) into any cell (ie $A$1)
    Example =CS.TIME(3600)
    👉 3600 seconds / 60 = 60 minutes = 1 hour
  2. Using any Cryptosheets custom function, add a reference to the cell with =CS.TIME in it as the time parameter or at the very end of the formula if there isn't a time parameter
    Syntax =CSPRICE("base","quote","time","exchange","returnType")
    Example =CS.PRICE("BTC","USD",$A$1)
    Example 2 =CS.PRICE("BTC","USD",CS.TIME(3600))
  3. Every time the =CS.TIME function updates, any/all functions that reference it will automatically refresh!
Image for post
Image for post
Automatically update thousands of crypto prices by adding one single formula

Googlesheets

  1. In any cell (ie $A$1) type =GOOGLEFINANCE("CURRENCY:USDEUR") which will automatically update (about every 2 minutes)
  2. Using any Cryptosheets custom function, add a reference to that cell at the very end of the formula for the global refresh argument
    Syntax =CSPRICE("base","quote","time","exchange","returnType","refresh")
    Example =CSPRICE("BTC","USD",,,$A$1)
    Example 2 CSPRICE("BTC","USD",,,GOOGLEFINANCE("CURRENCY:USDEUR"))
  3. Everytime the GoogleFinance cell updates 👉 so will your Cryptosheets data! That’s it!
Image for post
Image for post
Use the global “refresh” argument for any Cryptosheets custom function in Googlesheets

TIP
Just be aware of your Cryptosheets account subscription limits Google's quotas and limits as your data will stop refreshing if you run too many requests

📈 EXAMPLE USE CASES

So many that we can only mention a few and we’ll be highlighting specific use cases in future article so follow us here and on Twitter for updates

  • trading
  • arbitrage
  • recurring charts
  • recurring reports or articles
  • back office accounting
  • portfolio tracking
  • research
  • backtesting
  • dashboards
  • templates
  • validation
  • mining rigs

😎 HOW TO UPGRADE YOUR SUBSCRIPTION

  • Very easy and can be done anytime from within the Cryptosheets add-on side panel or from the website
  • See this article for detailed explanation

⚠️ IMPORTANT WARNING

  1. CS.TIME might well be the single most powerful tool for Excel I've ever seen. It's a genuine game changer and will improve your quality of life by saving you time, stress and mistakes (#AWESOME)
  2. Using automatic refresh features can and will significantly increase your quota usage
  3. It is YOUR RESPONSIBILITY to monitor your usage and we will not reset quotas for lower tier accounts that use =CS.TIME() and use up their quota accidentally
  4. You can access your current quota usage in real time ANYtime using the billing section in your your settings (Help Center tutorial: See your subscription usage in real time)
Image for post
Image for post

________________________________________________________________

ℹ️ FAQ &TROUBLESHOOTING & RESOURCES

  1. Check your syntax for =CS.TIME() carefully, different functions for the same data may have slightly different required syntax
  2. Check the required arguments for =CS.TIME() vs the optional arguments
  3. Check your data & API quotas and limits
  4. Check your formulas carefully
  5. Check your local computer & Excel settings for default date, time, geography, format
  6. Use Excel’s error checking tools
  7. Check Excel’s help sections: https://support.office.com/en-us/article/function-arguments-65b29fb5-ec7b-4c0b-a54b-a67923571519
  8. Find additional troubleshooting resources and tips here

Other Something else? — Click here for full list of Excel error explanation

See more troubleshooting tips & tricks here

Additional Resources

For Microsoft Excel

For Googlesheets

Cryptosheets is free to sign up and start using now. What are you waiting for?

👉 Get the Excel add-in now

👉 Get the Googlesheet add-on now

ADDITIONAL RESOURCES

Website | WebApp | Excel | Googlesheets | Help Center | Blog | Twitter
______________________________________________________________

TAGS : refresh, real time, streaming, schedule, upgrade, subscription, exceeded, limit, exhaust, update, live, streaming, invocation, seconds, #BUSY, automatic, googlefinance, schedule, trigger, quota, limit, datetime, time, timestamp, cs.time, cstime, automatic, custom functions, excel, googlesheets, spreadsheet, crypto data, bitcoin, blockchain

Spreadstreet

Spreadstreet makes it simple to connect cryptocurrency…

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store