Willow Tips

Tip #2000-1: Controlling Futures contract rollover dates with Reuters data

Many of you may not be aware that Reuters has a set of futures Instruments Codes (RICs) called continuation or alias RICs, that automatically roll over when the contract has expired. Although using these RICs can save you the considerable time and effort you'd otherwise spend revising Excel formulas, they're inflexible about the exact date when the roll over takes place. Trading usually drops off significantly when a futures contract nears expiration - often eight, ten or twelve days before the actual expiration date. However, the continuation or alias RICs continue to access the first available contract, even when your interest has moved on to the next available contract. For example, the continuation RIC for gold would continue to access the first available contract, GCc1, even if you'd prefer to monitor the next available contract, GCc2.

Reuters live data users: Download a sample spreadsheet

To solve this problem, use the following formula to control the date when the roll over takes place. This formula retrieves the expiration date for the first Gold contract and checks to determine if that date is within ten days of today's date. If so (the TRUE case), the formula displays the next available contract, GCc2. If not, the first Gold contract, GCc1, continues to be displayed.

You can also use the OPEN INTEREST field to determine which Gold contract to watch. For example, if the open interest falls below 100, you might want to watch the next contract month. If the open interest were above 100, you'd probably continue to watch the first contract month. The formula which uses the OPEN INTEREST field looks like this:

Or, you could compare the first contract's OPEN INTEREST to the second contract's OPEN INTEREST, and use the one that's larger.