How to refresh ...

Overview

Refresh any cry...

Automatically r...

Refresh Methods...

Streaming Refre...

Refresh Examples

Advanced Refres...

Googlesheets

Excel

Custom Function...

Limitations

Unintentional A...

Other Common Th...

## How to refresh data in Cryptosheets

### Overview
- There are multiple ways to refresh data in both <linkify text="Excel"></linkify> & <linkify text="Googlesheets"></linkify>
- Some methods may be limited by your **[subscription](https://intercom.help/cryptosheets/en/articles/1769408-how-can-i-upgrade-my-subscription-plan)** which you can see on the **[pricing ](https://cryptosheets.com/pricing)** page
- This page explores various methods & examples for both environments

> **IMPORTANT REMINDER**: 
>
Using [**volatile functions**](https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation?redirectedfrom=MSDN) 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](https://intercom.help/cryptosheets/en/articles/3712846-see-your-subscription-usage-in-real-time). 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**](https://intercom.help/cryptosheets/en/articles/4283962-you-have-been-disabled-due-to-excessive-usage-or-errors)

![IMG](https://res.cloudinary.com/cryptosheets/image/upload/v1571966039/cryptosheets-docs/excel/functions/exrate-hp-9.gif)

### Refresh any crypto data on demand

<iframe
  style={{ maxWidth: "570px" }}
  width="570"
  height="350"
  src="https://www.youtube.com/embed/2xYvhc-3KA8"
  frameborder="0"
  allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture"
  allowfullscreen
></iframe>

### Automatically refresh any crypto data at any interval

<iframe
  style={{ maxWidth: "570px" }}
  width="570"
  height="350"
  src="https://www.youtube.com/embed/xpOEUgGwaKE"
  frameborder="0"
  allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture"
  allowfullscreen
></iframe>


### Refresh Methods (from your spreadsheet application)

1. Automatically refresh the entire workbook ([Google Sheets](https://intercom.help/cryptosheets/en/articles/4456485-how-to-configure-auto-refresh-for-googlesheets))
2. Refresh a single worksheet
3. Refresh a specific range of cells
4. Refresh a specific cell or formula
5. Refresh a group of specific cells or formulas with linked invocations and triggers
 
### Streaming Refresh Methods (from the source)

1. Streaming refresh (ie [`CS.EXRATE`](https://docs.cryptosheets.com/functions/streaming)) at custom intervals
2. Auto invocating refresh at custom intervals (ie [`CS.TIME`](https://docs.cryptosheets.com/functions/time) for Excel) useable with any integrated data source



### Refresh Methods (using templates)

[Read our extensive blog tutorial](https://medium.com/cryptosheets/how-to-build-a-real-time-streaming-cryptocurrency-dashboard-in-excel-in-5-minutes-60dca67a6627) on how to build a real time streaming dashboard using the CS.EXRATE custom function (Excel only)

[![img](https://miro.medium.com/max/1000/1*QRnBQV0MrPgGUevPotqFcw.gif)](https://medium.com/cryptosheets/how-to-build-a-real-time-streaming-cryptocurrency-dashboard-in-excel-in-5-minutes-60dca67a6627)







### Refresh Examples

> TIP: [Use Google](https://www.google.com/search?q=how+do+i+refresh+data+in+cryptosheets) to help search for Cryptosheets data refresh examples by adding a **`"?"`** to your question

**Example**: [`"How do I refresh data in Cryptosheets?"`](https://www.google.com/search?q=how+do+i+refresh+data+in+cryptosheets)

[![IMG](https://paradigmas.blob.core.windows.net/paradigmas-content/sharex/cryptosheets-docs_google_refreshing-data-search-questions-from-google.png)](https://www.google.com/search?q=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!

![](https://res.cloudinary.com/cryptosheets/image/upload/v1588884141/cryptosheets-docs/quickcharts/indicators/rsi-refresh.gif)

### Googlesheets

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

1. Using your keyboard select any cell with a formula then **`press F2 + enter`**

2. Using a simple cell reference as a custom refresh parameter with the [CSPRICEA function](https://docs.cryptosheets.com/functions/pricea) and changing the cell value to trigger the invocation

   <inline-code functionvalue='=CSPRICEA("base","BTC","quote","USD","refresh",A1)'>=CSPRICEA("base","BTC","quote","USD","refresh",A1)</inline-code>

3. Change any parameter in the formula and/or change it back
   
   Example: <inline-code functionvalue='=CSPRICE("BTC","USD")'>=CSPRICE("BTC","USD")</inline-code> to <inline-code functionvalue='=CSPRICE("ETH","USD")'>=CSPRICE("ETH","USD")</inline-code>


4. [Using a checkbox - see YouTube tutorial](https://www.youtube.com/watch?v=2xYvhc-3KA8&list=PLBg8GR5ao-azCwkh769O7WUMPOKdleKNH&index=14&t=0s) from the Insert menu to create a trigger/control cell that others can link too

![](https://paradigmas.blob.core.windows.net/paradigmas-content/sharex/cryptosheets-docs_google_functions_insert-checkbox.png)

![](https://paradigmas.blob.core.windows.net/paradigmas-content/sharex/cryptosheets-docs_functions_google_pricea-refresh-example.png)

5. Using [Googlesheets built in spreadsheet settings](https://intercom.help/cryptosheets/en/articles/4456485-how-to-configure-auto-refresh-for-googlesheets)

6. [Using =GOOGLEFINANCE()](https://intercom.help/cryptosheets/en/articles/3972081-how-to-automatically-refresh-cryptosheets-custom-functions-data-in-excel-googlesheets-using-cs-time)

   <inline-code functionvalue='=CSPRICE("BTC","USD",,"Coinbase","ask",GOOGLEFINANCE("CURRENCY:USDEUR"))'>=CSPRICE("BTC","USD",,"Coinbase","ask",GOOGLEFINANCE("CURRENCY:USDEUR"))</inline-code>

   ![img](https://downloads.intercomcdn.com/i/o/246206395/2b52e9967491a70de99f8e18/image.png)

   ![img](https://downloads.intercomcdn.com/i/o/207124346/54bb53399eed1955a7a3c800/image.png)

7. [Using =GOOGLEFINANCE() with CSPRICEA function](https://docs.cryptosheets.com/functions/pricea)

   <inline-code functionvalue='=CSPRICEA("base","BTC","quote","USD","refresh",GOOGLEFINANCE("CURRENCY:USDEUR"))'>=CSPRICEA("base","BTC","quote","USD","refresh",GOOGLEFINANCE("CURRENCY:USDEUR"))</inline-code>

   ![img](https://paradigmas.blob.core.windows.net/paradigmas-content/sharex/cryptosheets-docs_google_refresh_cspricea-example-googlefinance.png)


### Excel

1. Using your keyboard select any cell with a formula then **`press F2 + enter`**

2. Using a simple cell reference as a custom refresh parameter with the [CS.PRICEA function](https://docs.cryptosheets.com/functions/pricea) and changing the cell value to trigger the invocation

   <inline-code functionvalue='=CS.PRICEA("base","BTC","quote","USD","refresh",A1)'>=CS.PRICEA("base","BTC","quote","USD","refresh",A1)</inline-code>

3. [Using a checkbox](https://www.youtube.com/watch?v=2xYvhc-3KA8&list=PLBg8GR5ao-azCwkh769O7WUMPOKdleKNH&index=14&t=0s) 
4. Using the [`CS.TIME`](https://docs.cryptosheets.com/functions/time) custom function as a scheduled trigger for other cells/formulas at custom intervals

   ![img](https://downloads.intercomcdn.com/i/o/207137503/62ff7ae9a5a28fe1697ca1ad/cryptosheets-docs_excel_functions_csprice-refresh-example%5B1%5D.png)

5. Using the [`CS.EXRATE`](https://docs.cryptosheets.com/functions/streaming) custom function for real time global VWAP pricing
6. Using a simple **control or trigger cell** (similar to the checkbox method)

   ![ControlCell](https://paradigmas.blob.core.windows.net/paradigmas-content/sharex/YT-1355-3873_cryptosheets-docs_providers_cryptosheets_console_csPRICEA-example6.gif)


### Custom Function Comparison

Below is a detailed comparison of the different features and capabilities of three of the most commonly used **[Cryptosheets custom functions](https://docs.cryptosheets.com/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**

<div class="markdown">


| **functionality** | **[EXRATE](https://docs.cryptosheets.com/functions/streaming)** | **[PRICE](https://docs.cryptosheets.com/functions/price)** | **[PRICEA](https://docs.cryptosheets.com/functions/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](https://docs.cryptosheets.com/metadata)**                                                        | ❌                            | ✅                          | ✅                          |
| specific **[pricing from over 588+ exchanges](https://docs.cryptosheets.com/metadata)**                                                        | ❌                            | ❌                          | ✅                          |
| [**default price source overrides** (for CoinGecko, CryptoCompare, Messari)](https://docs.cryptosheets.com/functions/pricea)                           | ❌                            | ❌                          | ✅                          |
| **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) | ❌                            | ❌                          | ✅                          |

</div>

> **[See the full help center article on CS.EXRATE vs CS.PRICE vs CS.PRICEA here](https://intercom.help/cryptosheets/en/articles/4448907-what-s-the-difference-between-cs-exrate-vs-cs-price-vs-cs-pricea)**


### 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](https://cryptosheets.com/pricing)** 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!](https://intercom.help/cryptosheets/en/articles/4283962-you-have-been-disabled-due-to-excessive-usage-or-errors)**
- You can **[monitor your quota usage in real time anytime](https://intercom.help/cryptosheets/en/articles/3712846-see-your-subscription-usage-in-real-time)** using the side panel or the **[CS.STATUS CSSTATUS custom function](https://intercom.help/cryptosheets/en/articles/4820458-use-the-status-function-to-run-a-quick-diagnostic-or-check-your-subscription-quota)**

#### Googlesheets
1. Does not support real time refresh or streaming
2. By default automatically opens your entire workbook every day to complete the auto save for backups on GSuite or Google Drive
3. 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](https://intercom.help/cryptosheets/en/articles/4283962-you-have-been-disabled-due-to-excessive-usage-or-errors)*

**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 ⚠️ 

1. You have multiple Cryptosheets functions that are directly or indirectly linked to cells with volatile functions like `=NOW()`, `=TODAY()` `=BNOW()`
2. You are repeatedly sending incorrect symbol, or exchange asset pairs (which result in errors) 
3. You are repeatedly using incorrect parameters with custom functions (ie using time  or exchange  for =CS.EXRATE when those aren't valid parameters)
4. You have existing VBA macros, custom Google App Scripts or similar programmatic triggers setup
5. You are using a custom function that that your subscription tier doesn't have access to (ie `=CS.TIME`)
6. You have multiple hanging errors in cells that are trying to auto recalculate constantly 
7. 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](https://intercom.help/cryptosheets/en/articles/4283962-you-have-been-disabled-due-to-excessive-usage-or-errors)

Learn several options for how to automatically refresh your Cryptosheets data in Excel and Google Sheets

How to refresh data in Cryptosheets

Data Refresh