# How to construct a continuous price time series out of futures raw data in Excel?

My object of research is corn futures:

It is well known that corn futures expire 5 times per year: March, May, July, September and December. Due to their finite life that is limited by their maturity, these events are adversely influencing price time series and makes futures raw data unsuitable for long-term statistical analytics. To overcome this issue, historical raw futures contract data (date, price, open interest) has to be modified and rolled with the aim to create a continuous time series. Considering the existence of different methodologies to select roll dates before a contract’s expiration (see several internet sources and articles), I choose the approach to select roll dates for corn futures based on market movements of open interest.

Consequently, I used two separate individual future price time series to finally construct an artificial continuous return time series: the nearby contract, meaning the contract nearest expiry (= front month – corn C1 contract) and the first-deferred contract, meaning the next contract with the second-shortest time to expiry (= back month – corn C2 contract). To select the roll date, I plan to implemented an algorithm that depending of the level of open interest shifting from one contract to the other splices together the two successive contracts around the last trading day. According to the open interest criterion, the shift between the two series is carried out when the open interest value of the first-deferred contract is consistently greater than the nearby one. This indicated that market participants (liquidity) are leaving the nearby and trading the first-deferred contract. In order to construct a continuous price series, prices should be taken from the contract with consistently higher levels of open interest.

So far so good.

My question now:

• How to implement the algorithm switch from one to the other and to stick the contracts time series together practically?

• Do I understand right, that the nearby (C1) contract dominates the price time series and only around last trading day of the nearby with a decline in open interest I should switch to the first-deferred?

• How to implement that switch in Excel? Are there special constraints? Or is it “easily” done with an IF-Formular like “ IF OI is greater in the NB, use price of NB; if not take the first-def. price?

Please find below my excel sheet with the time series and all relevant data: