PRICEA

=CS.PRICEA

In its simplest form, the PRICEA function says

=CS.PRICEA(what base asset, what quoted asset, at what date or time, from what exchange, for which price type, for what instrument type, with what details)

image

image

image

Use Case Scenarios

Use =CS.PRICEA() when you need maximum flexibility across all available assets, derivative instruments and dimensions. When you need to quickly pull in the latest price for specific assets, pairs or derivative instruments or the historical price at specific times from specific exchanges. This function is a more advanced & flexible version of the original CS.PRICE custom function. The main differences are that CS.PRICEA allows users to query for current or historical data across ALL crypto spot assets AND derivative instruments. The other major difference is that CS.PRICEA allows users to specify three additional parameter fields for type, details and/or symbolId. Using symbolId gives the additional option of specifying the entire symbol and details as one value instead of multiple parameters which is a common practice with professional derivatives traders.

PRICEA can take any combination of any parameters and/or Global Arguments and in any order as long as each input and value are paired. It also allows you to combine relative and absolute references in the same input and value pairs without having to use a nested array wrapped in {curly brackets}. This seemingly minor detail is incredibly important in the certain situations making CS.PRICEA a powerful tool to have.

PRICEA - Syntax

Function arguments

Arguments: =CS.PRICEA("base","quote","time","exchange","returnValue","type","details","symbolId","source")

Example Values: =CS.PRICEA("base","BTC","quote","USD","time","1/1/2020","exchange","KRAKENFTS","returnValue","ask","type","PERPETUAL")

Function argument descriptions

image

image

  • base: [OPTIONAL] The cryptocurrency symbol of interest [string]
  • quote: [OPTIONAL] Cryptocurrency symbol to convert into [string]
  • time: [OPTIONAL] Time at which exchange rate is calculated (if not supplied then current rate is returned) [string]
  • exchange: [OPTIONAL] Name of the supported exchange (ie KRAKENFTS use CS.EXCHANGES for full list) [string]
  • returnValue: [OPTIONAL] The price type to return (ie bid, mid or ask) Note: this is required when specifying an exchange explicitly [string]
  • type: [OPTIONAL] The asset or instrument type (ie FUTURES, OPTION, PERPETUAL etc) [string]
  • details: [OPTIONAL] Details of the instrument if applicable (ie futures expiration or option strike price) [string]
  • symbolId: [OPTIONAL] Concatenated symbol string including exchange, base, quote, type & details (ie KRAKENFTS_PERP_ETH_USD) [string]
  • source: [OPTIONAL] Explicit pricing source including support for CoinGecko, CryptoCompare and Messari [string]
  • address: [OPTIONAL] Explicit contract address string for the supported network. NOTE: This parameter will only work when source is specified as coingecko [string]
  • globalArgs [OPTIONAL] Global arguments available on all functions. See https://docs.cryptosheets.com/functions/common for all available options.

PARAMETERS - Absolute vs. Relative

Absolute Parameter Values

Formulas using absolute values for arguments with multiple parameters using CS.PRICEA can be referenced anywhere in the formula query string as long as they are paired together:

=CS.PRICEA("base","BTC","quote","USD","time","1/1/2020","exchange","KRAKENFTS","returnValue","ask","type","PERPETUAL")

Relative Parameter Values

Formulas using referenced values for arguments with multiple parameters using CS.PRICEA can be referenced anywhere and in any order within the formula query string as long as they are paired together:

=CS.PRICEA("A1","B1","A2","B2","C3","C3",...)

Combining Relative AND Absolute Parameter Values

One of the features about CS.PRICEA vs CS.PRICE that makes it so flexible is the ability to combine any configuration of relative and absolute values at any point in the formula query string without being bound by {"nested:arrays"} as long as they are paired together:

=CS.PRICEA("base","A1","quote","A2","time","A3",...)

image

image

Click here to search for supported assets & exchanges

PRICEA - Examples

TIP: Try copying + pasting the example formulas directly into your worksheet

Example 1:

Excel

=CS.PRICEA("base","ADA","quote","USD","exchange","BINANCEFTSC","type","FUTURES","details","001225")

Googlesheets

=CSPRICEA("base","ADA","quote","USD","exchange","BINANCEFTSC","type","FUTURES","details","001225")

image

Example 2:

Excel

=CS.PRICEA("base","ETH","quote","USD","exchange","DERIBIT","type","OPTION","details","200828-400-C")

Googlesheets

=CSPRICEA("base","ETH","quote","USD","exchange","DERIBIT","type","OPTION","details","200828-400-C")

image

Example 3:

Excel

=CS.PRICEA("symbolId","KRAKENFTS_PERP_ETH_USD")

Googlesheets

=CSPRICEA("symbolId","KRAKENFTS_PERP_ETH_USD")

image

Example 4:

Excel

=CS.PRICEA("symbolId","DERIBIT_OPT_ETH_USD_200828_400_C")

Googlesheets

=CSPRICEA("symbolId","DERIBIT_OPT_ETH_USD_200828_400_C")

image

Example 5:

Excel

=CS.PRICEA("symbolId","KRAKENFTS_PERP_ETH_USD")

Googlesheets

=CSPRICEA("symbolId","KRAKENFTS_PERP_ETH_USD")

image

Example 6 - Using CryptoCompare for Default Pricing:

Excel

=CS.PRICEA("base","BTC","quote","USD","source","cryptocompare","exchange","coinbase")

Googlesheets

=CSPRICEA("base","BTC","quote","USD","source","cryptocompare","exchange","coinbase")

image

Example 7 - Using CoinGecko for Default Pricing:

Excel

=CS.PRICEA("base","BTC","quote","USD","source","coingecko","exchange","ftx_spot")

Googlesheets

=CSPRICEA("base","BTC","quote","USD","source","coingecko","exchange","ftx_spot")

image

Example 8 - Using CoinGecko by Contract Address for Default Pricing:

Excel

=CS.PRICEA("base","ETH","quote","USD","source","coingecko","address","0xc00e94cb662c3520282e6f5717214004a7f26888")

Googlesheets

=CSPRICEA("base","ETH","quote","USD","source","coingecko","address","0xc00e94cb662c3520282e6f5717214004a7f26888")

image

Example 9 - Using Messari for Default Pricing:

Excel

=CS.PRICEA("base","ETH","source","messari")

Googlesheets

=CSPRICEA("base","ETH","source","messari")

image

Troubleshooting

  1. Check your formula syntax for carefully, different functions for the same data may have slightly different required syntax

  2. Check the required arguments for your function vs the optional arguments

  3. Check your data & API quotas and limits

  4. Check your formulas carefully

  5. Check your relative references

  6. Use Excels error checking tools

  7. Check Excels help sections

  8. Error Response Codes Reference

    • #N/A OK – Everything worked as expected
    • #BUSY Heavy query and/or unauthorized – Your User/Password API Keys are incorrect
    • #LIMIT Forbidden – You are not subscribed to the data feed requested
    • Other Something else? Reach out to us directly

Additional Resources

For Microsoft Excel

For Googlesheets