Defining Current and Next Comex Futures Contract

[dlmandalia]

Hi,

I don't know how to code but I want to create code where as it check with today's date and define current contact and next contact.

All the time I have to change current contract and next contract manually I want computer update it automatically.

below is table for gold and silver contract for COMEX Exchange.

Example

From 1 Jan to 28 Feb For gold February contact as current contract and April as next Contract,

From 1 March to 30 April For gold April as Current Contract and JUNE AS NEXT CONTRACT.

[TABLE="width: 297"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Code[/TD]
[TD]Gold[/TD]
[TD]Silver[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD]F[/TD]
[TD]FEBRUARY[/TD]
[TD]MARCH[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]G[/TD]
[TD]FEBRUARY[/TD]
[TD]MARCH[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD]H[/TD]
[TD]APRIL[/TD]
[TD]MARCH[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]J[/TD]
[TD]APRIL[/TD]
[TD]MAY[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]K[/TD]
[TD]JUNE[/TD]
[TD]MAY[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]M[/TD]
[TD]JUNE[/TD]
[TD]JULY[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]N[/TD]
[TD]JULY[/TD]
[TD]JULY[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]Q[/TD]
[TD]JULY[/TD]
[TD]SEPTEMBER[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD]U[/TD]
[TD]OCTOBER[/TD]
[TD]SEPTEMBER[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD]V[/TD]
[TD]OCTOBER[/TD]
[TD]DECEMBER[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD]X[/TD]
[TD]DECEMBER[/TD]
[TD]DECEMBER[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD]Z[/TD]
[TD]DECEMBER[/TD]
[TD]DECEMBER[/TD]
[/TR]
</tbody>[/TABLE]

Thanking you in advance. Let me know how can i do it.

Regards, Dipen Mandalia

 

[ChrisBM]

Assuming your Columns are A to D:

In order to find current contract: (GOLD)

=Offset(A2, Match(Text(Today(),"mmmm"), A2:A13,0) - 1, 2)

In order to find current contract: (Silver)

=Offset(A2, Match(Text(Today(),"mmmm"), A2:A13,0) - 1, 3)

 

[ChrisBM]

In order to find next contract: (GOLD)

=OFFSET(A2, COUNTIF(INDIRECT("C" & 2 + MATCH(TEXT(Today(),"mmmm"), A2:A13,0) &":C13"), OFFSET(A2, MATCH(TEXT(TODAY(),"mmmm"), A2:A13,0) - 1, 2))+ MATCH(TEXT(Today(),"mmmm"), A2:A13,0)-1, 2)

In order to find next contract: (Silver)

=OFFSET(A2, COUNTIF(INDIRECT("D" & 2 + MATCH(TEXT(Today(),"mmmm"), A2:A13,0) &":D13"), OFFSET(A2, MATCH(TEXT(TODAY(),"mmmm"), A2:A13,0) - 1, 2))+ MATCH(TEXT(Today(),"mmmm"), A2:A13,0)-1, 3)

 

[dlmandalia]

works great thanks for the code.

I also understood about offset formula and it can be used like this as well..

Thank you so much.

Assuming your Columns are A to D:

In order to find current contract: (GOLD)

=Offset(A2, Match(Text(Today(),"mmmm"), A2:A13,0) - 1, 2)

In order to find current contract: (Silver)

=Offset(A2, Match(Text(Today(),"mmmm"), A2:A13,0) - 1, 3)

 

[ChrisBM]

Not a problem.

Happy to help, thanks for the feedback.

 

[dlmandalia]

Now I have one more problem I have been trying to google a lot but couldn't sort out.
Hope you can help me out in it

Now after I have found the current and next month I take that month and Convert in formula of DDE but from that DDE formula eventhough I do Paste special (Value Only) It is not acting as formula. I have to press "F2" and Enter then it ast as DDE formula.

I know it is bit difficult to understand as may be I am not able to explain. to illustrate the problem I am attaching the link of the file. You may please to download it and then try to understand the problem and help me out.

https://www.dropbox.com/s/j9uwvq2ypgvtyrc/DDE Link creator.xls?dl=0

You will find the DDE formula that I have generated using your formula. But If I want DDE to get connect via this formula I have to Copy the formula and do the paste special (value) you will only get the formula not the connected live prices.

I hope u understand after looking into the file.

Regards,

Dipen Mandalia

Not a problem.

Happy to help, thanks for the feedback.

 

[dlmandalia]

In order to find next contract: (GOLD)

=OFFSET(A2, COUNTIF(INDIRECT("C" & 2 + MATCH(TEXT(Today(),"mmmm"), A2:A13,0) &":C13"), OFFSET(A2, MATCH(TEXT(TODAY(),"mmmm"), A2:A13,0) - 1, 2))+ MATCH(TEXT(Today(),"mmmm"), A2:A13,0)-1, 2)

In order to find next contract: (Silver)

=OFFSET(A2, COUNTIF(INDIRECT("D" & 2 + MATCH(TEXT(Today(),"mmmm"), A2:A13,0) &":D13"), OFFSET(A2, MATCH(TEXT(TODAY(),"mmmm"), A2:A13,0) - 1, 2))+ MATCH(TEXT(Today(),"mmmm"), A2:A13,0)-1, 3)

Hi Chris,

Sorry for bothering you again. But I have found that above next month formula doesn't work on December Month. I mean to say when we change current date to 1st December 2015 next month value come to zero.

Apart from december it worked on all months.

I have tried to resolve it but I couldn't if you could help me out again thanks.

and about that DDE thing I have found Macro which sends F2 and Enter key so that problem is solved.

Many Thanks.

Dipen

 

[ChrisBM]

In essence, it does not know what the next contract should be because there is no more data in the table. If you extend the ranges and give it more data then it will work

 

[dlmandalia]

In essence, it does not know what the next contract should be because there is no more data in the table. If you extend the ranges and give it more data then it will work

Chris

Then it should go back to First row. this is repeating event every year.

Thanks.