Syntax and Functions

Microsoft Excel provides a worksheet function called RealTimeData or RTD for short. This function enables Barchart for Excel to call a Component Object Model (COM) Automation server to retrieve market data in real time. The default Quote syntax is all cell references and appears as below in Barchart for Excel. 

=@BEQ(A2:A2,B1:C1)

Open

The first cell reference is the symbol range and the second cell reference is the field range. 

The time series formula syntax is editable, meaning you can change many of the parameters in-line and dynamically change the corresponding results without interacting with the UI/toolbar.  Click the Time Series cell for any table and modify the formula and see what changes.  

Below the Aggregation is set to weekly:

Open

Same query with Week changed to day:

Barchart for Excel Functions

BERTD Function

Using the following function, Barchart for Excel users can pull in any of the Streaming Quote 'Fields’ into specified cell in a worksheet:

=@BERTD(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 =BERTD("IBM","Last")

To get the volume for Apple, you may enter =BERTD("AAPL","Volume")

A single symbol and single field can be entered through a cell refence. In the below image, F3 contains the symbol and G2 contains the field. The absolute reference is G3.

Single symbol and multiple fields can be inserted by locking the rows and columns. The single symbol with multiple field reference in B2 is =BERTD($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 =BERTD($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.

All of the aviable fields supported by the =BERTD can be found here and below.

(Note: Expressions are supported symbols when using the BERTD function)

BERTD Field Name List

Below is a text document that contains all BERTD field names. These fields can be copied and pasted into excel to pull in data via absolute values or cell reference.

Open

BEF Function

Similar to =BERTD , the =BEF 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 =BEF, 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.

(Note: Expressions are supported symbols when using the BEF function)

BEF Field and Function Names Documents

Below is a text document that contains all BEF function field names. These fields can be copied and pasted into excel to pull in data via cell reference.

Open

Below is a text document that contains all of the BEF functions. Copy and paste any of the functions into Excel (with a ‘equals sign’ prefix) to quickly pull in data into your spreadsheet.

Open

BEH Historical Date Function

Using the =BEH 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 =BEH, 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 =BEF function, after selecting the required function you will need to enter in the symbol and a date.

=BEH_Daily_Close(“Symbol”,”Date”)

For cell reference, in the below image, D2 contains the symbol and E1 contains the field. The absolute reference is E2.

Single symbol and multiple dates can be inserted by locking the rows and columns. The single symbol with multiple date reference in E2 is =BEH_Daily_Close($D2,E$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 =BEH_Daily_Close($D2,E$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 underlying futures rolled on their expirations, 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())

BE_ADJ_PADDED

Sample query for dividend adjusted and non split adjusted TSLA:

=BEH_Daily_Close("TSLA","8/31/2020",BE_ADJ_DIVIDEND()&BE_ADJ_NO_SPLIT())

BEH Date Function Document

Below is a text document that contains all of the Barchart 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.

Open

BET Historical Time Series Functions

Similar in syntax to the =BEH historical date function, historical time series data can be called directly into an Excel spreadsheet using the =BET 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 =BEH function, after selecting the required function you will need to enter in the symbol, field, start date and end date.

=BET_Daily(“Symbol”,”Field”,”Start Date”,”End Date”)

(Note: Expressions are supported symbols when using the BET 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:E1.

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 =BET 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())

Sample query for dividend adjusted and non split adjusted TSLA:

=BET_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 TSLA data sorted in ascending order:

=BET_Daily("TSLA","Close","1/1/2024","2/5/2024",,"A")

BES Historical Study Functions

Historical study data can be called directly into a cell or spreadsheet via the BES function. When prompted to enter the symbol argument for the =BEH or =BET function, entering in BES 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 BEH or BET function. For historical study data as of a date, use the =BEH and BES 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 =BET and BES 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.

BES Study Function Documents

Below is a text document that contains all of the Barchart for Excel study functions. Copy and paste any of the functions into Excel to quickly pull in data into your spreadsheet.

Open

Below is a text document that contains all of the Barchart for Excel study outputs or fields. Copy and paste any of the functions into Excel to quickly pull in data into your spreadsheet.

Open

Equity Option Time and Sales (BET_TS) Functions

Insert equity option time and sales data into your spreadsheet using =BET_TS function via two methods: all transactions by date or all transactions by date with a time range. When entering in the syntax you will be prompted to enter in the subsequent fields:

Enter in the strike price, an applicable field (see below for all supported fields) and then the start and end date.

(Note: equity option time and sales data is available on a per day basis so the start and end date will need to be identical)

For all option data on a given date, the last parameter, ‘Set Date Not As Formula', must be set to TRUE. Once all required entries are set, hit enter and the data will insert into the sheet.

Below is an example of a query only specifying a date:

=BET_TS("INTC|20231020|44.00C","LastSize","6/28/2023","6/28/2023",TRUE)

To specify a time range in addition to the date, after inserting the syntax, strike, and field, you will need to use the DATE and TIME functions.

DATE requires the following inputs comma separated:

(year, month, day)

TIME requires the following inputs comma separated:

(hour, minute, second)

Once all parameters are set, hit enter and the equity option time and sales data will be inserted into the sheet.

Below is an example of a query specifying a date and time range:

=BET_TS("CHPT|20230707|9.00P","LastSize",DATE(2023,6,29)+TIME(9,30,0),DATE(2023,6,29)+TIME(9,59,0))

Below are the available equity option time and sales fields: