Retrieval with Continuous Contracts#

A series of simple examples are provided showing how to retrieve Futures market data by continuous contract. Continuous Contracts are provided ranging from the Front month to the Twelfth month rolling on expiry. Additionally continuous contracts are available representing the front month rolling based on Maximum Volume or Maximum Open Interest.

Continuous Contracts are queried with:

  • [Product Code]\\1 to [Product Code]\\12. - Front Month to Twelfth Month

  • [Product Code]_r_vol - Front Month based on Maximum Volume

  • [Product Code]_r_oi - Front Month based on Maximum Open Interest

Continuous Contracts are also queried with the Bloomberg symbology BSYM:

  • [Bloomberg Product Code]1 to [Bloomberg Product Code]12. - Front Month to Twelfth Month

  • [Bloomberg Product Code]A - Front Month based on Maximum Volume

Retrieval of Futures Contracts#

Daily Data is retrieved by specifying:

  • Database & DAY Table (separated by a period. e.g. ICE_EU_COM_SAMPLE_DAILY.DAY).

  • Symbol or Symbols using the field SYMBOL_NAME, with the contract definiition

The Symbol represents the futures contract with the [Product Code]\\[Expiry Month][Expiry Year] syntax. For Brent Crude the Product Code is BRN, producing BRN\\Z24

Retrieval of December 2024 Brent Crude Oil Contract#
select * from ICE_EU_COM_SAMPLE_DAILY.DAY
where SYMBOL_NAME='BRN\\Z24'                 -- Returning the December 2024 Brent Oil Futures Contract
and TIMESTAMP >= '2024-01-01 00:00:00 UTC'
and TIMESTAMP < '2024-04-01 00:00:00 UTC'
and UPDATE_TYPE = 'Summary'
and symbol_date = 20240401                  -- Specifies date for cont contract lookup

Retrieval of Continuous Contract by Expiry#

Daily Data is retrieved by specifying:

  • Database & DAY Table (separated by a period. e.g. ICE_EU_COM_SAMPLE_DAILY.DAY).

  • Symbol or Symbols using the field SYMBOL_NAME, with the continuous contract definiition.

The Symbol represents the Continuous contract with the [Product Code]\\1 to [Product Code]\\12 syntax. For Brent Crude the Product Code is BRN, producing BRN\\1

Retrieval of Front Month Brent Crude Oil Continuous Contract#
select * from ICE_EU_COM_SAMPLE_DAILY.DAY
where SYMBOL_NAME='BRN\\1'                 -- Front Month Continuous Contract ([Product code]\1)
and TIMESTAMP >= '2024-01-01 00:00:00 UTC'
and TIMESTAMP < '2024-04-01 00:00:00 UTC'
and UPDATE_TYPE = 'Summary'
and symbol_date = 20240401                  -- Specifies date for cont contract lookup

Retrieval of Continuous Contract by Max Volume#

Daily Data is retrieved by specifying:

  • Database & DAY Table (separated by a period. e.g. ICE_EU_COM_SAMPLE_DAILY.DAY).

  • Symbol or Symbols using the field SYMBOL_NAME, with the continuous contract definiition.

The Symbol represents the Continuous contract with the [Product Code]_r_vol syntax. For Brent Crude the Product Code is BRN, producing BRN_vol

Retrieval of Front Month Brent Crude Oil Continuous Contract by Max Volume#
select * from ICE_EU_COM_SAMPLE_DAILY.DAY
where SYMBOL_NAME='BRN_r_vol'                -- Highest Open Interest Continuous Contract ([Product]_r_vol)
and TIMESTAMP >= '2024-01-01 00:00:00 UTC'
and TIMESTAMP < '2024-04-01 00:00:00 UTC'
and UPDATE_TYPE = 'Summary'
and symbol_date = 20240401                  -- Specifies date for cont contract lookup

Retrieval of Continuous Contract by Max Open Interest#

Daily Data is retrieved by specifying:

  • Database & DAY Table (separated by a period. e.g. ICE_EU_COM_SAMPLE_DAILY.DAY).

  • Symbol or Symbols using the field SYMBOL_NAME, with the continuous contract definiition.

The Symbol represents the Continuous contract with the [Product Code]_r_oi syntax. For Brent Crude the Product Code is BRN, producing BRN_r_OI

Retrieval of Front Month Brent Crude Oil Continuous Contract by Max Open Interest#
select * from ICE_EU_COM_SAMPLE_DAILY.DAY
where SYMBOL_NAME='BRN_r_oi'                -- Highest Open Interest Continuous Contract ([Product]_r_oi)
and TIMESTAMP >= '2024-01-01 00:00:00 UTC'
and TIMESTAMP < '2024-04-01 00:00:00 UTC'
and UPDATE_TYPE = 'Summary'
and symbol_date = 20240401                  -- Specifies date for cont contract lookup

Futures Contract Bloomberg Symbol Retrieval#

Furures Contracts can be retrieved by Bloomberg Symbol with:

  • Prefixing the Database name with BSYM:: - Setting the BSYM symbology

  • Specifying the SYMBOL_NAME as the full Bloomberg Symbol

Retrieve of December 2024 Brent Oil Future Contract with Bloomberg Symbol#
SELECT * FROM BSYM::ICE_EU_COM_DAILY.DAY
WHERE SYMBOL_NAME='COZ4 Comdty'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
and SYMBOL_DATE = 20240104
LIMIT 10

Retrieval of Bloomberg Continuous Contract by Expiry#

Daily Data is retrieved by specifying:

  • Symbology BSYM, Database & DAY Table (separated by a period. e.g. BSYM::ICE_EU_COM_SAMPLE_DAILY.DAY).

  • Symbol or Symbols using the field SYMBOL_NAME, with the Bloomberg continuous contract definiition.

The Symbol represents the Continuous contract with the [Bloomberg Product Code]1 to [Bloomberg Product Code]12 syntax.

Retrieval of Front Month Brent Crude Oil Continuous Contract Front Month with Bloomberg Symbol#
select * from BSYM::ICE_EU_COM_SAMPLE_DAILY.DAY
where SYMBOL_NAME='CO1'                 -- Front Month Continuous Contract ([Bloomberg Product code]1)
and TIMESTAMP >= '2024-01-01 00:00:00 UTC'
and TIMESTAMP < '2024-04-01 00:00:00 UTC'
and UPDATE_TYPE = 'Summary'
and symbol_date = 20240401                 -- Specifies date for cont contract lookup

Retrieval of Bloomberg Continuous Contract by Max Volume#

Daily Data is retrieved by specifying:

  • Symbology BSYM, Database & DAY Table (separated by a period. e.g. BSYM::ICE_EU_COM_SAMPLE_DAILY.DAY).

  • Symbol or Symbols using the field SYMBOL_NAME, with the continuous contract definiition.

The Symbol represents the Continuous contract with the [Product Code]A syntax. For Brent Crude the Product Code is BRN, producing COA

Retrieval of Front Month Brent Crude Oil Continuous Contract by Max Volume with Bloomberg Symbol#
select * from BSYM::ICE_EU_COM_SAMPLE_DAILY.DAY
where SYMBOL_NAME='COA'                -- Highest Open Interest Continuous Contract ([Product]_r_vol)
and TIMESTAMP >= '2024-01-01 00:00:00 UTC'
and TIMESTAMP < '2024-04-01 00:00:00 UTC'
and UPDATE_TYPE = 'Summary'
and symbol_date = 20240401                  -- Specifies date for cont contract lookup