Hi,
I have a "master" sheet, the first column of which contains every date that the stock market is open.
Somewhere in the middle of each month there is one day when stock market options expire. I have an "exp_symb" sheet that lists the date of option expiration for each month in the E column.
What I want to do is calculate the percentage of trading days that today is relative to the previous options expiration date and the next options expiration date. Because the option expiration dates are based on the day of the week ie. the third Wednesday, the number of trading days will vary from month to month.
For example if today was one of the options expiration dates, the function would return 0% because I am 0% of the way to the next options expiration date. Suppose that there are 20 trading days between this option expiration and the next option expiration day, then tomorrow the function would return 5% and the following day, 10%.
What I have so far is =VLOOKUP(TODAY(),'Exp Symb'!$E:$E,1,TRUE). This looks at ExpSymb and returns the date of the most recent options expiration.
The second step would be to find the date of the next options expiration date. I want to say "look at the box returned in that previous function and go to the box right below it and return that date."
Finally, I want to subtract, not the days between these two expiration dates, but the number of trading days between the two dates. So in english I'd say, "find the previous expiration date in column A of the master sheet and note the row number. then find the next expiration date also in column A of the master sheet and note its row number. Now subtract the first from second. That will give the number of rows between the previous and next options expiration dates which will be equal to the number of trading days between the two dates."
Any help would be greatly appreciated.
Thanks,
Steve
I have a "master" sheet, the first column of which contains every date that the stock market is open.
Somewhere in the middle of each month there is one day when stock market options expire. I have an "exp_symb" sheet that lists the date of option expiration for each month in the E column.
What I want to do is calculate the percentage of trading days that today is relative to the previous options expiration date and the next options expiration date. Because the option expiration dates are based on the day of the week ie. the third Wednesday, the number of trading days will vary from month to month.
For example if today was one of the options expiration dates, the function would return 0% because I am 0% of the way to the next options expiration date. Suppose that there are 20 trading days between this option expiration and the next option expiration day, then tomorrow the function would return 5% and the following day, 10%.
What I have so far is =VLOOKUP(TODAY(),'Exp Symb'!$E:$E,1,TRUE). This looks at ExpSymb and returns the date of the most recent options expiration.
The second step would be to find the date of the next options expiration date. I want to say "look at the box returned in that previous function and go to the box right below it and return that date."
Finally, I want to subtract, not the days between these two expiration dates, but the number of trading days between the two dates. So in english I'd say, "find the previous expiration date in column A of the master sheet and note the row number. then find the next expiration date also in column A of the master sheet and note its row number. Now subtract the first from second. That will give the number of rows between the previous and next options expiration dates which will be equal to the number of trading days between the two dates."
Any help would be greatly appreciated.
Thanks,
Steve