Bloomberg FTP Futures Reference Data

We also provide a C# program template to perform scheduled data ETL tasks from bloomberg to SQL database or other types of storage.
Contact us for details:[email protected]

In this example, we are going to retrieve some reference data for several securities from Bloomberg FTP, starting from a generic ticker. With Bloomberg, we cannot make a single request to retrieve specific reference data for a generic ticker. Instead, we have to split the request in two parts:

First, we retrieve all futures chains with specific ticker codes for the generic ticker
Next, we use specific ticker codes to retrieve the reference data

Here is the first file we are going to send:

START-OF-FILE
FIRMNAME=XXXXX
PROGRAMFLAG=oneshot
PROGRAMNAME=getdata
REPLYFILENAME=futures_chains.out
SECID=TICKER
COLUMNHEADER=yes
SECMASTER=yes
DERIVED=yes
COMPRESS=no
DELIMITER=,
HEADER=no

START-OF-FIELDS
FUT_CHAIN_LAST_TRADE_DATES
END-OF-FIELDS

START-OF-DATA
ES1 Index
TY1 Comdty
...
END-OF-DATA
END-OF-FILE

For the detailed description of common request parameters, refer to the Bloomberg FTP Intro.

The parameters relevant to the futures contract data are the following:

PROGRAMNAME — we need to use getdata program here since we are looking for reference data.
FIELDS/FUT_CHAIN_LAST_TRADE_DATES — this is a bloomberg field that will get us all future chains with their respective last trading dates, such that we can do some filtering in our business logic, e.g., continue to process only non-expired contracts, or only one year in the future.

In response, we will receive all future chains for a given generic index together with the last trading dates:

START-OF-FILE
"SECURITIES","ERROR CODE","NUM FLDS","FUT_CHAIN_LAST_TRADE_DATES"
"ES1 Index",0,1,";2;12;2;4;ESU2 Index;5;09/16/2022;4;ESZ2 Index;..."

The above request file will return only non-expired contracts from the futures chain, to get the expired contracts, you need to format the request as follows:

START-OF-FILE
...

START-OF-DATA
ES1 Index|TICKER|2|INCLUDE_EXPIRED_CONTRACTS|CHAIN_DATE|Y|20120101|
END-OF-DATA
END-OF-FILE

Where last parameter is the start date from which you want to receive all the contracts.

Now we have the data to send a second request to Bloomberg to get the reference data for the specific contracts:

START-OF-FILE
START-OF-FILE
FIRMNAME=XXXXX
SECID=TICKER
PROGRAMFLAG=oneshot
PROGRAMNAME=getdata
REPLYFILENAME=contracts_data.out
COLUMNHEADER=yes
SECMASTER=yes
CLOSINGVALUES=yes
DERIVED=yes
COMPRESS=no
DELIMITER=,
HEADER=no

START-OF-FIELDS
UNDL_SPOT_TICKER
EXCH_CODE
TICKER
MARKET_SECTOR_DES
MARKET_SECTOR
NAME
ID_EXCH_SYMBOL
ID_FULL_EXCHANGE_SYMBOL
SECURITY_TYP
LAST_TRADEABLE_DT
FUT_FIRST_TRADE_DT
FUT_NOTICE_FIRST
FUT_CONT_SIZE
FUT_VAL_PT
FUT_TICK_SIZE
QUOTED_CRNCY
CALENDAR_CODE
END-OF-FIELDS

START-OF-DATA
ESU2 Index
...
END-OF-DATA
END-OF-FILE

Most of the fields in the request are self-explanatory, but let's touch a couple of important ones:

MARKET_SECTOR_DES — type of security (Index, Commodity, etc.).
SECURITY_TYP — detailed security type description, e.g., "Physical index future."
FUT_VAL_PT — contract size in lots.
CALENDAR_CODE — you want to get the calendar code for the future contract to be able to query holidays (non-settlement dates) for it.

Below is a sample response for one of the contracts:

START-OF-FILE
"SECURITIES","ERROR CODE","NUM FLDS","UNDL_SPOT_TICKER","EXCH_CODE","TICKER","MARKET_SECTOR_DES","MARKET_SECTOR","NAME","ID_EXCH_SYMBOL","ID_FULL_EXCHANGE_SYMBOL","SECURITY_TYP","LAST_TRADEABLE_DT","FUT_FIRST_TRADE_DT",FUT_NOTICE_FIRST","FUT_CONT_SIZE","FUT_VAL_PT","FUT_TICK_SIZE","QUOTED_CRNCY","CALENDAR_CODE"
"ESZ3 Index",0,17,"SPX","CME","ESZ3","Index",9,"S&P500 EMINI FUT Dec23","ES","ESZ3","Physical index future.","12/15/2023","06/07/2021","12/15/2023",50.00,50.00,.25000,"USD","CE"