How to refresh data in Cryptosheets

Overview

There are multiple ways to refresh data in both Excel & Googlesheets
Some methods may be limited by your subscription which you can see on the pricing page
This page explores various methods & examples for both environments

IMPORTANT REMINDER:

Using volatile functions including =NOW(), =TODAY(), =RAND(), =RANDBETWEEN(), =OFFSET(), =INDIRECT(), =INFO() and =CELL() cause the entire workbook to refresh all cells on any* state change DO NOT use them with Cryptosheets functions as they will cause errors and rapidly consume your request quota. There are many other things that can cause unintended auto refresh of formulas - please see the bottom of this page and read the full help center article

Refresh any crypto data on demand

Automatically refresh any crypto data at any interval

Refresh Methods (from your spreadsheet application)

Automatically refresh the entire workbook (Google Sheets)
Refresh a single worksheet
Refresh a specific range of cells
Refresh a specific cell or formula
Refresh a group of specific cells or formulas with linked invocations and triggers

Streaming Refresh Methods (from the source)

Streaming refresh (ie CS.EXRATE) at custom intervals
Auto invocating refresh at custom intervals (ie CS.TIME for Excel) useable with any integrated data source

Refresh Methods (using templates)

Read our extensive blog tutorial on how to build a real time streaming dashboard using the CS.EXRATE custom function (Excel only)

Refresh Examples

TIP: Use Google to help search for Cryptosheets data refresh examples by adding a "?" to your question

Example: "How do I refresh data in Cryptosheets?"

Advanced Refresh Examples

Cryptosheets custom functions allow infinite combinations of custom refresh setups including high frequency low interval time series with simultaneous technical indicator transformations!

Googlesheets

Try these methods to manually or automatically refresh Cryptosheets data in Google Sheets

Using your keyboard select any cell with a formula then press F2 + enter
Using a simple cell reference as a custom refresh parameter with the CSPRICEA function and changing the cell value to trigger the invocation
=CSPRICEA("base","BTC","quote","USD","refresh",A1)
Change any parameter in the formula and/or change it back
Example: =CSPRICE("BTC","USD")
to =CSPRICE("ETH","USD")

Using a checkbox - see YouTube tutorial from the Insert menu to create a trigger/control cell that others can link too

Using Googlesheets built in spreadsheet settings
Using =GOOGLEFINANCE()
=CSPRICE("BTC","USD",,"Coinbase","ask",GOOGLEFINANCE("CURRENCY:USDEUR"))
Using =GOOGLEFINANCE() with CSPRICEA function
=CSPRICEA("base","BTC","quote","USD","refresh",GOOGLEFINANCE("CURRENCY:USDEUR"))

Excel

Using your keyboard select any cell with a formula then press F2 + enter
Using a simple cell reference as a custom refresh parameter with the CS.PRICEA function and changing the cell value to trigger the invocation
=CS.PRICEA("base","BTC","quote","USD","refresh",A1)
Using a checkbox
Using the CS.TIME custom function as a scheduled trigger for other cells/formulas at custom intervals
Using the CS.EXRATE custom function for real time global VWAP pricing
Using a simple control or trigger cell (similar to the checkbox method)

Custom Function Comparison

Below is a detailed comparison of the different features and capabilities of three of the most commonly used Cryptosheets custom functions (aka formulas) including the ability for each to define specific data providers & sources (ie global VWAP default, exchanges, API providers) and their built-in or supported refresh capabilities

functionality	EXRATE	PRICE	PRICEA
live current price	✅	✅	✅
live, streaming prices (streaming for Excel only)	✅	❌	❌
live, automatic custom refresh (using CS.TIME in Excel only)	❌	✅	✅
built-in native refresh as a parameter	✅	❌	❌
global VWAP as default/fallback	✅	✅	✅
specific pricing from over 314+ exchanges	❌	✅	✅
specific pricing from over 588+ exchanges	❌	❌	✅
default price source overrides	❌	❌	✅
historical prices by date/time	❌	✅	✅
supports single string `symbolId` parameter	❌	❌	✅
supports the `source` parameter	❌	❌	✅
market price type (bid/mid/ask)	❌	✅	✅
derivatives live, current pricing (futures, options, perpetuals, swaps, index, credit)	❌	❌	✅
derivatives historical pricing by date/time (futures, options, perpetuals, swaps, index, credit)	❌	❌	✅

See the full help center article on CS.EXRATE vs CS.PRICE vs CS.PRICEA here

Limitations

TIP: Remember that just because you are refreshing a formula or API call in your spreadsheet doesn't necessarily mean the actual underlying data (from the source) is being refreshed at the same time or frequency

Your Request concurrency limits define the amount of API calls or formulas you can refresh simultaneously, if you exceed the limits of your subscription tier your formulas will ERROR out and add up towards your quota and potentially triggering your account to be auto-disabled - please read our help center tutorials = it's easy to avoid!
You can monitor your quota usage in real time anytime using the side panel or the CS.STATUS CSSTATUS custom function

Googlesheets

Does not support real time refresh or streaming
By default automatically opens your entire workbook every day to complete the auto save for backups on GSuite or Google Drive
Has specific limits on number of requests, bandwidth and total amount of data size that can be requested to single IP addresses, subnets and more

Unintentional Auto Refresh

Read the full Help Center article here

Example causes that could trigger auto disable: 

👉 Using volatile functions including =NOW(), =TODAY(), =RAND(), =RANDBETWEEN(), =OFFSET(), =INDIRECT(), =INFO() and =CELL() cause the entire workbook to refresh all cells on any* state change

⚠️ DO NOT use them with Cryptosheets functions as they will cause errors and rapidly consume your request quota ⚠️

You have multiple Cryptosheets functions that are directly or indirectly linked to cells with volatile functions like =NOW(), =TODAY() =BNOW()
You are repeatedly sending incorrect symbol, or exchange asset pairs (which result in errors)
You are repeatedly using incorrect parameters with custom functions (ie using time or exchange for =CS.EXRATE when those aren't valid parameters)
You have existing VBA macros, custom Google App Scripts or similar programmatic triggers setup
You are using a custom function that that your subscription tier doesn't have access to (ie =CS.TIME)
You have multiple hanging errors in cells that are trying to auto recalculate constantly
You have a formula with an array output (multiple columns & rows) that is stuck in #SPILL! mode because there is data in the cells it needs to write data to

Other Common Things that Can & Will Cause Unexpected Refresh Include:

Entering new data (if Excel is in Automatic recalculation mode, described later in this topic).
Explicitly instructing Excel to recalculate all or part of a workbook.
Deleting or inserting a row or column.
Saving a workbook while the Recalculate before save option is set.
Performing certain Autofilter actions.
Double-clicking a row or column divider (in Automatic calculation mode).
Adding, editing, or deleting a defined name.
Renaming a worksheet.
Changing the position of a worksheet in relation to other worksheets.
Hiding or unhiding rows, but not columns.

Read the full Help Center article here