Highlight & Move Cells Based On CriteriaSep 7, 2009
I have 3 data series, which are each futures contracts. Each of these futures contracts (the one on the left expires soonest) has an expiration date and as that date approaches, one will need to replace it with a new futures contract (the data series immediately to its right) having a later expiration date. This process is called a ‘roll’. I am seeking to create a continuous data series, which will incorporate the appropriate ‘roll’ based on two rules.
The rules are as follows:
1) Where the volume of the 2nd contract exceeds the volume of the 1st contract for 10 consecutive intervals, roll on that date.
2) Once the roll date has been identified, roll at the specific time interval on that date that has the highest aggregate volume (between the 1st and 2nd contract).
I have laid out the original data in a tab entitled ‘original data stream’. This is how the data will appear at first. In the tab entitled ‘Roll Example’, I have shown what I would like to happen to the original data streams. You will notice that in this tab, the data series are the same as the previous tab, except that they are not aligned as before. The data series to the left ( cells B4: D808) expires ..................................