Syntax and Functions
- Chris Harrison
- Brendan Hebert (Unlicensed)
Microsoft Excel provides a worksheet function called RealTimeData or RTD for short. This function enables cmdtyView for Excel to call a Component Object Model (COM) Automation server to retrieve market data in real time.
The cmdtyView for Excel RTD function uses the following syntax for streaming quotes:
=BCD(Topic1,[Topic2], ...)
where the function arguments are as follows:
RealTimeServerProgID is a string that represents the Program ID of the RTD server installed on the local system which is BCD in our case
Topic1, [Topic2] are strings that determine the cells being referenced and the data being retrieved
To get the latest trade for December 2020 Corn - apart from using the UI button Insert Quotes then searching for and selecting the symbol in question and the desired field - you may type in a cell =BCQ("ZCZ19","Last")
To get the volume for Apple, you may enter =BCD("AAPL","Volume")
Excel respects Windows regional settings which for most of the European community means that the list separator is not a comma, but rather a semicolon. Therefore, people with such regional settings will enter something like this =BCQ("GOOG";"Previous")
The fields currently supported include the following:
Field | Description |
|---|---|
Ask | The last Ask Price from the exchange indicating a willingness to sell at that price |
Ask Size | The total number of contracts offered at the current Ask Price |
Bid | The last Bid Price from the exchange indicating a willingness to purchase at that price |
Bid Size | The total number of contracts offered at the current Bid Price |
Change | The difference between the Last Price and the Close Price from the previous day or session |
Close | The current session's Close Price |
Description | The description of the symbol |
Exchange | The exchange or data source in which the instrument resides |
High | The highest price paid during the current trading session |
Last | The last price paid during the current trading session |
Low | The lowest price paid during the current trading session |
OI | The total number of futures contracts traded that have not yet been liquidated either by an offsetting futures transaction or by delivery |
Open | The first price paid during the current trading session |
% Change | The percentage difference between the Last Price and the Close Price from the previous day or session |
Previous | The previous trading session's Closing Price |
Prev Settle | The previous trading session's Settlement Price |
Settle | The current session's Settlement Price |
Size x Bid | Contains both the last Bid Price and the number of contracts offered at that price |
Size x Ask | Contains both the last Ask Price and the number of contracts offered at that price |
Symbol | The Barchart unique identifier which represents the symbol of a particular instrument |
Tick Dir | The symbol’s minimum upward or downward last traded price |
Time | Time of the last trade |
Underlying | A cash values deliverable futures expiration month |
Volume | The total number of contracts traded during the current trading session |
Vol Time | Time of last volume update |
Barchart for Excel Functions
BCD Function
Using the following function, Barchart for Excel users can pull in any of the Streaming Quote 'Fields’ into specified cell in a worksheet:
=@BCD(Topic1,[Topic2], ...)
where the function arguments are as follows:
RealTimeServerProgID is a string that represents the Program ID of the RTD server installed on the local system which is BCD in our case
Topic1, [Topic2] are strings that determine the cells being referenced and the data being retrieved
To get the lasted trade price for IBM - apart from using the UI button Insert Quotes then searching for and selecting the symbol in question and the desired field - you may type in a cell =BCD("IBM","Last")
A single symbol and single field can be entered through a cell refence. In the below image, A2 contains the symbol and B1 contains the field. The absolute reference is G2.
Single symbol and multiple fields can be inserted by locking the rows and columns. The single symbol with multiple field reference in B2 is =BCD($A2,B$1). The $ locks the columns and rows which enables you to drag and copy the formula across multiple rows. Once finished dragging, the cell(s) will automatically update with that fields value.
The same logic as above holds true for multiple symbols and and one field. The multiple symbol and single field reference in B2 is =BCD($A2,B$1) again. Now you can click and drag the formula to the other symbols cells. Once finished dragging, the cell(s) will automatically update with that fields value.
(Note: Expressions are supported instruments when using the BCD function).
BCF Function
Similar to =BCD, the =BCF function will independently populate a list of fields or arguments that can be inserted into your spreadsheet without needing to know the arguments name. Upon entering in =BCF, you can either scroll through the list or enter in a keyword to narrow the list’s results.
Once a field is selected, the symbol you wish to populate the corresponding data for can be entered as an argument.
BCF Field and Function Names Documents
Below is a text document that contains all BCF function field names. These fields can be copied and pasted into excel to pull in data via cell reference.
Below is a text document that contains all of the BCF functions. Copy and paste any of the functions into Excel (with a ‘equals sign’ prefix) to quickly pull in data into your spreadsheet.
BCH Historical Date Function
Using the =BCH function, daily, weekly, monthly, quarterly and yearly historical data as of a specific date can be called directly into a single cell in your Excel spreadsheet. When keying in =BCH, you will see a list of available historical date functions.
Historical data can be entered into the cell via absolute values or cell reference. For absolute values, similar to the =BCF function, after selecting the required function you will need to enter in the symbol and a date.
=BCH_Daily_Close(“Symbol”,”Date”)
For cell reference, in the below image, A2 contains the symbol and B1 contains the field. The absolute reference is B2.
Single symbol and multiple dates can be inserted by locking the rows and columns. The single symbol with multiple date reference in B2 is =BCH_Daily_Close($A2,B$1). The $ locks the columns and rows which enables you to drag and copy the formula across multiple rows. Once finished dragging, the cell(s) will automatically update with that fields value.
The same logic as above holds true for multiple symbols and one date. The multiple symbol and single field reference in B2 is =BCH_Daily_Close($A2,B$1) again. Now you can click and drag the formula to the other symbols cells. Once finished dragging, the cell(s) will automatically update with that fields value.
(Note: Using weekly and higher aggregations will require the correct start date to properly pull in the data. Example, for last week's weekly close, Monday's date will have to be entered)
Futures data as of a date can be adjusted for both the ‘nearest’ and ‘continue’ aggregations based on roll options: days to expiration and volume and open interest.
To view historical data where the underlying future rolled on expiration, enter in '0'. To roll the historical data based on a date prior to expiration, enter in a days to expiration value. Leave this argument blank to roll based on a combination of volume and open interest.
Equity data as of a date can be adjusted for both dividends and splits by opting to use either or both of the following arguments following the symbol,:
BE_ADJ_DIVIDEND()
BE_ADJ_NO_SPLIT()
BE_ADJ_DIVIDEND()&BE_ADJ_NO_SPLIT())
Sample query for dividend adjusted and non split adjusted TSLA:
=BCH_Daily_Close("TSLA","8/31/2020",BE_ADJ_DIVIDEND()&BE_ADJ_NO_SPLIT())
BCH Date Function Document
Below is a text document that contains all of the cmdtyView for Excel Historical date functions. Copy and paste any of the functions into Excel (with a ‘equals sign’ prefix) to quickly pull in data into your spreadsheet.
BCT Historical Time Series Functions
Similar in syntax to the =BCH historical date function, historical time series data can be called directly into an Excel spreadsheet using the =BCT Barchart for Excel function. Tick, intraday, Daily, weekly, monthly, quarterly and yearly historical time series data can be queried with the =BET function.
Historical data can be entered into the cell via absolute values or cell reference. For absolute values, similar to the =BCH function, after selecting the required function you will need to enter in the symbol, field, start date and end date.
=BCT_Daily(“Symbol”,”Field”,”Start Date”,”End Date”)
(Note: Expressions are supported when using the BCT historical time series function)
For cell reference, multiple symbols and multiple fields can inserted using a range of cells. In the image below, the symbol range is A1:C1.
After the symbol range is selected, a field range can be selected. Below the field range is A2:E2.
After the symbol and field ranges are selected, enter in the start date and end date. Once the parenthesis are closed, hit enter the time series data will populate in the spreadsheet.
Historical ‘rolling' futures data is also available through either of the nearest or continuation aggregations found in the =BCT dropdown menu.
After selecting the desired aggregation enter in the various arguments and then customize the roll options argument.
To roll the contract forward on the current contract's expiration, enter in '0'. To roll the current contract forward on a date prior to expiration, enter in a days to expiration value. Leave this argument blank to roll forward based on combination of volume and open interest.
Equity time series data can be adjusted for both dividends and splits by opting to use either or both of the following arguments following the symbol, field, start date and end date:
BE_ADJ_DIVIDEND()
BE_ADJ_NO_SPLIT()
BE_ADJ_DIVIDEND()&BE_ADJ_NO_SPLIT()
BE_ADJ_PADDED()
Sample query for dividend adjusted and non split adjusted TSLA:
=BCT_Daily("TSLA","Close","7/1/2020","9/20/2020",BE_ADJ_DIVIDEND()&BE_ADJ_NO_SPLIT())
Historical data by default is returned in descending order, using the Sort argument at the end of the string, data can be returned in ascending order using an “A”.
Below is a sample query for unadjusted ZC*0 data sorted in ascending order:
=BCT_Daily("ZC*0","Close","1/1/2024","2/7/2024",,"A")
BCS Historical Study Functions
Historical study data can be called directly into a cell or spreadsheet via the BCS function. When prompted to enter the symbol argument for the =BCH or =BCT function, entering in BCS will populate a list of technical indicators.
After selecting a study, enter in the symbol and the study periods / parameters. Once the study is customized, enclose the study data and continue entering the next arguments of the BCH or BCT function. For historical study data as of a date, use the =BCH and BCS functions together. Below is the S&P 500’s ($SPX) 20 day Hull Moving Average on November 3rd 2020.
When calling historical study data as of a date via the =BEH function, you will need to specify the study’s output to be queried. In the example below, the MACD, MACD-Histogram and the MACD-Signal are all of the outputs of the MACD Oscillator. Only one output can be called at a time using the =BEH function. After selecting the output, enter the symbol and date and hit enter.
(Note: leaving the Period / Parameters blank will pull in the study’s default values)
For historical study time series data, use the =BCT and BCS functions in parallel. In the example below, historical Average Directional Index weekly time series data has been called for the Dow Jones Industrial Average ($DOWI). As no periods or parameters were entered, the default smoothing and length of 14 were the inputs.
BCS Study Function Documents
Below is a text document that contains all of the cmdtyView for Excel study functions. Copy and paste any of the functions into Excel to quickly pull in data into your spreadsheet.
Below is a text document that contains all of the cmdtyView for Excel study outputs or fields. Copy and paste any of the functions into Excel to quickly pull in data into your spreadsheet.
