One problem with many option calculators you
may come across is that you must know and enter the expiration date
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 changeexpiration(ByVal dt As Date, ByVal num As Integer)
'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
dt = dt.AddDays(1)
Return dt.AddDays(1) 'actual expiration is the Saturday after
close of trade on expiration Friday
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
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.