Overview
This article demonstrates how to pull real time streaming pricing data for thousands of symbols and asset pairs into any worksheet in seconds using custom functions and templates.
Above: Multiple symbols quoted in USD vs USDT with calculated outputs
CS.EXRATE()
In its simplest form, the CS.EXRATE function says:
=CS.EXRATE(asset symbol [base ,quote ], interval you want it to refresh in seconds)
Scenario
Use =CS.EXRATE() when you need to stream real time or very frequently refreshed data. Common use cases are for traders who need up to the second pricing to monitor their positions or balance their risk.
(*Note: please be conscious of your account and API data quota limits when using streaming custom functions)
Function arguments
Arguments:
=CS.EXRATE("base","quote","refresh interval")Values:
=CS.EXRATE("BTC","USDT","1")
NOTE: arguments like additional parameters that are wrapped in brackets [like this] are optional, all other arguments should be considered required
IMPORTANT:
Using streaming functions may cause instability in workbooks depending on user's resources and if or when they makes large changes to live cells (ie deletes 50 live price cells at same time) - please exercise caution
Function argument descriptions
base:Identifier of the base symbol, ex. BTC (required)quote:Identifier of the quote symbol, ex. USDT (required)interval:Refresh interval defined in seconds, ex. '1' [(optional but required for streaming)]parameters:List of additional path and query parameters (optional)
________________________________________________________________
Examples
Example 1: Bitcoin price quoted in Tether w/ 1 second refresh
=CS.EXRATE("BTC","USDT","1")
Example 2: multiple symbols for quotes in USD vs USDT and calculated outputs
={0,"USD","USDT","spread";"BTC","=CS.EXRATE(A2,$B$1,1)","=CS.EXRATE(A2,$C$1,1)","=C2-B2";"ETH","=CS.EXRATE(A3,$B$1,1)","=CS.EXRATE(A3,$C$1,1)","=C3-B3";"LTC","=CS.EXRATE(A4,$B$1,1)","=CS.EXRATE(A4,$C$1,1)","=C4-B4";"ETC","=CS.EXRATE(A5,$B$1,1)","=CS.EXRATE(A5,$C$1,1)","=C5-B5";"EOS","=CS.EXRATE(A6,$B$1,1)","=CS.EXRATE(A6,$C$1,1)","=C6-B6";"XRP","=CS.EXRATE(A7,$B$1,1)","=CS.EXRATE(A7,$C$1,1)","=C7-B7";"BNB","=CS.EXRATE(A8,$B$1,1)","=CS.EXRATE(A8,$C$1,1)","=C8-B8";"XLM","=CS.EXRATE(A9,$B$1,1)","=CS.EXRATE(A9,$C$1,1)","=C9-B9";"DASH","=CS.EXRATE(A10,$B$1,1)","=CS.EXRATE(A10,$C$1,1)","=C10-B10";"BCH","=CS.EXRATE(A11,$B$1,1)","=CS.EXRATE(A11,$C$1,1)","=C11-B11";"BTT","=CS.EXRATE(A12,$B$1,1)","=CS.EXRATE(A12,$C$1,1)","=C12-B12"}
Example 3: multiple dynamic outputs w/ live heatmap chart
Example 4: using a Cryptosheets autoTemplate
Tips & Tricks
The utility functions are designed to provide a quick and easy way to check the status of your login, the API and market data connectivity. Use these functions when you're having issues connecting to market data.
________________________________________________________________
Additional Resources
Cryptosheets Functions Overview
Cryptosheets Custom Functions Templates
Overview: https://app.cryptosheets.com/#/templates
For Microsoft Excel
Web browsers used by add-ins: https://docs.microsoft.com/en-us/office/dev/add-ins/concepts/browsers-used-by-office-web-add-ins
Office versions and requirement sets: https://docs.microsoft.com/en-us/office/dev/add-ins/develop/office-versions-and-requirement-sets
How to check your Office version: https://docs.microsoft.com/en-us/office/dev/add-ins/develop/office-versions-and-requirement-sets#how-to-check-your-office-version
Install the latest version of Office: https://docs.microsoft.com/en-us/office/dev/add-ins/develop/install-latest-office-version
Custom Function Requirements: https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-architecture
________________________________________________________________
Tags: #exrate, #cs.exrate, #streaming, #live, #realtime, #real-time, #real time, functions, formulas, builder, live, streaming, socket, real time, exchange rates, feed, #refresh, excel, googlesheets, spreadsheet