bloombergcommodity

Commodity Term Structure Data from Bloomberg

I am looking for a way to download daily WTI crude oil (CL) term structure data from Bloomberg in the Excel Add-in.

My goal is to create a table that looks like this: For each futures contract, I want the lat price and the days to maturity.

Date CL1 CL2 ...
1.1.12 PX_Last FUT_ACT_DAYS_EXP PX_Last FUT_ACT_DAYS_EXP

I have tried the FUT_ACT_DAYS_EXP code but for historical price series it is not available. Is there maybe a different way to receive the term structure data with days to maturity from BB?

Many thanks!

Solution

  • The generic contract CL1 Comdty has a historic field of FUT_CUR_GEN_TICKER. So you can pull back a timeseries of PX_LAST and FUT_CUR_GEN_TICKER.

    enter image description here

    Then you can feed these underlying contract tickers into a BDP call for LAST_TRADEABLE_DT and subtract the PX_LAST date. You can hide the intermediate columns if they are not needed.

    enter image description here

    And the final result, with the intermediate column D hidden:

    enter image description here

    NB. I'm using array functions here (note the # symbols in the formulae), rather than hard-coding ranges. It makes it more flexible if you want to change the history range. The multiple BDP calls are unnecessary but the Bloomberg addin may be caching them in any case. If performance is an issue you can use the UNIQUE() function to get a list of the underlying contract names into a lookup table.