Hi
I was referred to MrExcel by our office’s resident excel guru, so this is my first post to the board, but I think I have a rather tricky problem involving futures contracts. I have already finished the project with several time-intensive workarounds, but I was hoping someone might be able to enlighten me with some formulas to automate the process next time I have to do something similar. My final goal is to produce graphs which display a 5 year average of values (y-axis) of the spread of prices for 2 contracts on a given day until expiration (x-axis). This will result in about 20 graphs.
(I originally planned to include screens but since I have no hosting that will not work)
My first big problem is figuring out how many days until a futures contract for a given month expires. Contracts are named after the month they expire (e.g. January) and for a given commodity they always expire on specific days of a month (e.g. 3rd Thursday of a month for feeder cattle contracts, or 10th day of the month for feeder cattle). So, I would like to be able to automatically figure out the expiration date for the year and contract I am looking at, and then from the given date, how many days there are until the contract expires.
My current solution for days until expiration is as follows. I figure out the day each contract expires every year, and then I use the formula
=NETWORKDAYS(($A3,$A$15,holidays!$B$2:$J$11)-1)
to calculate the days until the contract expires, since trading only occurs on business days, factoring out the holidays which I have in a table on their own tab.
Since I know the daily prices for each month’s contract, I have no problem calculating the difference between the prices for contracts in 2 different months (the spread).
However, it gets tricky now, because I need to calculate the average of a given spread (Jan-Apr for example) on a given day until expiration for the 5 most recent years (e.g. Jan-Apr, 50 days until expiration), but I have to keep all 7 years of data in the spreadsheet. My current workaround is to sort the dates and spreads by days until expiration, and then sort again by descending year and then manually select the five topmost days for each day until expiration.
The data is averaged in a table I don’t sort with the rest of the data, which I have to Paste Values to another worksheet because the calculated average changes every time I have to shuffle the data it’s based on.
In sum, in the first part, I need a formula for automatically determining the days until expiration , while in the second part I would like a way to calculate the average of the 5 most recent spreads for a given day till expiration without having to re-sort the data every time.
I also have two more bonus problems:
1. Currently, after I calculate the daily spreads in part 1, I have to Copy-Paste Values onto another tab so I can resort the data by days till expiration without messing up the formulas. If anyone can figure out a way to avoid having intermediate tabs for the spread (the actual value, not the average) so I can just have one table, that would be awesome
2. Sometimes, for whatever reason, there will be no price for a contract on a given day. This means there is no spread and the spread calculation results in #N/A. It would be really helpful if the formula that averages spreads could also ignore any cells with #N/A, or if the formula that calculates the spreads in the first place could create something the averaging formula would ignore.
This seemed really complicated to me so I might not have communicated it well, please don't hesitate to ask questions if I need to clarify.
thanks
sl85
I was referred to MrExcel by our office’s resident excel guru, so this is my first post to the board, but I think I have a rather tricky problem involving futures contracts. I have already finished the project with several time-intensive workarounds, but I was hoping someone might be able to enlighten me with some formulas to automate the process next time I have to do something similar. My final goal is to produce graphs which display a 5 year average of values (y-axis) of the spread of prices for 2 contracts on a given day until expiration (x-axis). This will result in about 20 graphs.
(I originally planned to include screens but since I have no hosting that will not work)
My first big problem is figuring out how many days until a futures contract for a given month expires. Contracts are named after the month they expire (e.g. January) and for a given commodity they always expire on specific days of a month (e.g. 3rd Thursday of a month for feeder cattle contracts, or 10th day of the month for feeder cattle). So, I would like to be able to automatically figure out the expiration date for the year and contract I am looking at, and then from the given date, how many days there are until the contract expires.
My current solution for days until expiration is as follows. I figure out the day each contract expires every year, and then I use the formula
=NETWORKDAYS(($A3,$A$15,holidays!$B$2:$J$11)-1)
to calculate the days until the contract expires, since trading only occurs on business days, factoring out the holidays which I have in a table on their own tab.
Since I know the daily prices for each month’s contract, I have no problem calculating the difference between the prices for contracts in 2 different months (the spread).
However, it gets tricky now, because I need to calculate the average of a given spread (Jan-Apr for example) on a given day until expiration for the 5 most recent years (e.g. Jan-Apr, 50 days until expiration), but I have to keep all 7 years of data in the spreadsheet. My current workaround is to sort the dates and spreads by days until expiration, and then sort again by descending year and then manually select the five topmost days for each day until expiration.
The data is averaged in a table I don’t sort with the rest of the data, which I have to Paste Values to another worksheet because the calculated average changes every time I have to shuffle the data it’s based on.
In sum, in the first part, I need a formula for automatically determining the days until expiration , while in the second part I would like a way to calculate the average of the 5 most recent spreads for a given day till expiration without having to re-sort the data every time.
I also have two more bonus problems:
1. Currently, after I calculate the daily spreads in part 1, I have to Copy-Paste Values onto another tab so I can resort the data by days till expiration without messing up the formulas. If anyone can figure out a way to avoid having intermediate tabs for the spread (the actual value, not the average) so I can just have one table, that would be awesome
2. Sometimes, for whatever reason, there will be no price for a contract on a given day. This means there is no spread and the spread calculation results in #N/A. It would be really helpful if the formula that averages spreads could also ignore any cells with #N/A, or if the formula that calculates the spreads in the first place could create something the averaging formula would ignore.
This seemed really complicated to me so I might not have communicated it well, please don't hesitate to ask questions if I need to clarify.
thanks
sl85
