The changeexpiration() Visual Basic Function

    One problem with many option calculators you may come across is that you must know and enter the expiration date by hand.

     Since the regular monthly expiration is always (with rare exceptions) the Saturday after the third Friday of the month, why not make a function that can find that date in any month you want, and enter it for you?

     Note that the program will use the actual Saturday expiration date. Some brokers show the Saturday date, and some brokers show the Friday date. We use the Saturday date so that during the day on expiration Friday, there is still one day of option trading left and the calculator can price the option properly. If we used the Friday date, on expiration Friday the calculator would think that the option was expired (0 days left), and all it would show would be the expiration value - the intrinsic value of the option after expiration.

     This code finds the third Friday of any month you want, then returns the date of the Saturday after that.

     On the completed calculator, you will use the function by clicking the "-" or "+" buttons next to the expiration date, to go to the previous or next expiration. You can keep clicking as many times as you need to change to any expiration date, even if it is a year away.

     Copy and paste the code below into the OptionCalcs module, below the words "end function" of the timeleft() function.

Function changeexpiration(ByVal dt As Date, ByVal num As Integer) As Date 'changes the date supplied to the 15th, adds or subtracts num month(s) to the date, 'and finds the first Friday on or after the 15th, which must be the 3rd Friday of that month dt = dt.AddDays(15 - dt.Day) dt = dt.AddMonths(num) For x As Integer = 1 To 7 If dt.DayOfWeek = DayOfWeek.Friday Then Exit For End If dt = dt.AddDays(1) Next Return dt.AddDays(1) 'actual expiration is the Saturday after close of trade on expiration Friday End Function

Save your project.

     Note: if you usually trade weekly options or quarterly options instead of monthly options, you could set the "previous" and "next" expiration buttons to change to the previous or next Saturday, or the previous or next day after the end-of-quarter.

     If you want to do that, comment out the existing changeexpiration() function and make a new one with the same name, that does what you want.

     We leave it to you as a programming exercise to study the existing code and modify it for your purposes.

     (You can comment out, or disable, any code by selecting it, and then pressing the "comment out" icon, which will usually be just below the "Tools" menu.)

     Remember (as shown with NORMSDIST()) that you can test any function in the Immediate Window, such as by typing ?changeexpiration(#1/1/2013#,1).

     The #'s before and after the date are how you tell Visual Basic that you are giving it a date, and not just some numbers you want divided.