Among other things, I am interested in the study of Economics, the Stock Exchange and the Derivatives Exchange. I do not care much about speculating, because I consider it to be a form of gamble. My view towards investment is to “never sell”. Of course, this requires making buying decisions with this notion in mind: buying stocks for their dividends, buying property in order to rent it and so on. Buying stocks or property in order sell them may be a gamble and I am not sure I want to do that. (Although, of course, as a counter-argument, I have to say that options are a great tool to help you alleviate risk.) To fully explain my point of view concerning investments would require a blog post of its own, so I should leave this subject here for now. But I always love learning concepts and Economics is a science full of interesting discoveries and inventions. The concepts behind the Stock Exchange and the Derivatives Exchange are important and useful for everyone to learn, and so are a lot of concepts in Microeconomics and Macroeconomics.
The Derivatives Exchange is one of the subjects in Economics that I find particularly interesting. And a big part of the Derivatives Exchange are options. At some point, I bought an excellent book, titled “Getting started in options” by Michael C. Thomsett, in its 3rd edition, published by John Wiley & Sons, Inc., with ISBN 0-471-17758-X. I recommend this book to everyone who wishes to learn about options. It certainly helped me tremendously. I really loved this book and when I read it, I could not wait to try my new-found knowledge. I wanted to experiment with all the new concepts I had learnt (spreads and butterfly spreads and so on). I used Excel to try all the concepts, but what I really wanted was an automated way to calculate prices. What I really wanted was an “options lab”: a program that would allow me to enter one or more options and it would calculate the amounts for me. Since I did not know if such an educational program existed, I decided to create my own. I thought a lot about what programming environment to use and I decided that Excel was the best, because I wanted to display a table of the results and Excel’s grid was perfect for this job.
So I used Excel 97 and I developed a VBA macro that consisted of a userform, a module and a class module. The name of the userform is formInputOptions, the name of the module is not important but let’s call it OptionModule and the name of the class module is OptionClass.
Here is a screenshot of the userform formInputOptions in design mode:
The userform formInputOptions consists of a combobox named comboboxBuySell, a textbox named textboxStrikingPrice, a combobox named comboboxCallPut, a textbox named textboxPremium, a commandbutton named buttonSet, a listbox named listboxList and a commandbutton named buttonCalculate.
The code of the userform formInputOptions follows:
Option Explicit
Private Sub buttonCalculate_Click()
Dim myObject As OptionClass
If myCollection.Count <= 0 Then
MsgBox "You must enter at least one option contract"
Exit Sub
End If
'Code for testing. It is not needed.
'For Each myObject In myCollection
' MsgBox myObject.BuySell & " " & CStr(myObject.StrikingPrice) & " " & myObject.CallPut & " at " & CStr(myObject.Premium)
'Next myObject
MousePointer = fmMousePointerHourGlass
DoEvents
'Assign the Excel column for each option contract
Dim ExcelMajor As String
Dim colcounter As Long
Dim letterCounter As Long
ExcelMajor = ""
letterCounter = 1 'It starts from 1 rather than 0, because 0 is for the first Excel column which will be occupied by the stock prices
For Each myObject In myCollection
If letterCounter = 26 Then
letterCounter = 0
If ExcelMajor = "" Then
ExcelMajor = "A"
Else
ExcelMajor = Chr(Asc(ExcelMajor) + 1)
End If
End If
myObject.ExcelColumn = ExcelMajor & Chr(letterCounter + Asc("A"))
letterCounter = letterCounter + 1
Next myObject
'Assign the Excel column for the sum
Dim ExcelColumnForSums As String
If letterCounter = 26 Then
letterCounter = 0
If ExcelMajor = "" Then
ExcelMajor = "A"
Else
ExcelMajor = Chr(Asc(ExcelMajor) + 1)
End If
End If
ExcelColumnForSums = ExcelMajor & Chr(letterCounter + Asc("A"))
'Find the MinPrice (the minimum stock price for which all option contracts will be calculated)
Dim firstTimeIn As Boolean
firstTimeIn = True
Dim MinPrice As Long
For Each myObject In myCollection
If firstTimeIn Then
firstTimeIn = False
MinPrice = myObject.SuggestedMinPrice
End If
If myObject.SuggestedMinPrice < MinPrice Then
MinPrice = myObject.SuggestedMinPrice
End If
Next myObject
'Find the MaxPrice (the maximum stock price for which all option contracts will be calculated)
firstTimeIn = True
Dim MaxPrice As Long
For Each myObject In myCollection
If firstTimeIn Then
firstTimeIn = False
MaxPrice = myObject.SuggestedMaxPrice
End If
If myObject.SuggestedMaxPrice > MaxPrice Then
MaxPrice = myObject.SuggestedMaxPrice
End If
Next myObject
'Clear all cells of the active Excel sheet
Cells.Select
Selection.ClearContents
'Fill the first Excel column (it has the stock prices)
Range("A1").Select
ActiveCell.FormulaR1C1 = "Price Per Share"
Dim myCounter As Long
Dim myDifference As Long
myDifference = 0
For myCounter = 2 To MaxPrice - MinPrice + 2
Range("A" & CStr(myCounter)).Select
ActiveCell.FormulaR1C1 = MaxPrice - myDifference
myDifference = myDifference + 1
Next myCounter
'Fill the other Excel columns (each Excel column represents an option contract)
For Each myObject In myCollection
myObject.CalculateValues MinPrice, MaxPrice
Next myObject
'Fill the last Excel column which has the sum of all the option contract columns
Range(ExcelColumnForSums & "1").Select
ActiveCell.FormulaR1C1 = "Total Profit"
For myCounter = 2 To MaxPrice - MinPrice + 2
Range(ExcelColumnForSums & CStr(myCounter)).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-" & CStr(myCollection.Count) & "]:RC[-1])"
Next myCounter
'Change the font for the first row
Rows("1:1").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold Italic"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
'Adjust the size of all columns
Cells.Select
Cells.EntireColumn.AutoFit
'Set focus on first Excel cell
Range("A1").Select
'Clear form and collection
comboboxBuySell.Text = ""
comboboxCallPut.Text = ""
textboxStrikingPrice.Text = ""
textboxPremium = ""
listboxList.Clear
Set myCollection = Nothing
'Set focus to first entry control, in case the application will be needed again
comboboxBuySell.SetFocus
MousePointer = fmMousePointerDefault
DoEvents
formInputOptions.Hide
End Sub
Private Sub buttonSet_Click()
If comboboxBuySell.Text <> "BUY" And comboboxBuySell.Text <> "SELL" Then
MsgBox "Please enter BUY or SELL"
Exit Sub
End If
If comboboxCallPut.Text <> "CALL" And comboboxCallPut.Text <> "PUT" Then
MsgBox "Please enter CALL or PUT"
Exit Sub
End If
If Trim(textboxStrikingPrice.Text) = "" Then
MsgBox "Please enter a Striking Price"
Exit Sub
End If
If Trim(textboxPremium.Text) = "" Then
MsgBox "Please enter a Premium"
Exit Sub
End If
Dim myObject As New OptionClass
myObject.BuySell = comboboxBuySell.Text
myObject.CallPut = comboboxCallPut.Text
myObject.StrikingPrice = CStr(textboxStrikingPrice.Text)
myObject.Premium = CStr(textboxPremium.Text)
myCollection.Add myObject
Set myObject = Nothing
listboxList.AddItem comboboxBuySell.Text & " " & CStr(textboxStrikingPrice.Text) & " " & comboboxCallPut.Text & " at " & CStr(textboxPremium.Text)
comboboxBuySell.Text = ""
comboboxCallPut.Text = ""
textboxStrikingPrice.Text = ""
textboxPremium = ""
comboboxBuySell.SetFocus
End Sub
Private Sub textboxPremium_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Process these keys (by doing nothing)
If Shift = 0 And KeyCode = 8 Then 'Backspace
ElseIf Shift = 0 And KeyCode = 9 Then 'Tab
ElseIf Shift = 0 And KeyCode = 13 Then 'Enter
ElseIf Shift = 0 And KeyCode = 37 Then 'Left arrow
ElseIf Shift = 0 And KeyCode = 39 Then 'Right arrow
ElseIf Shift = 0 And KeyCode = 46 Then 'Del
ElseIf Shift = 0 And KeyCode >= 48 And KeyCode <= 57 Then 'numbers 0,1,2,3,4,5,6,7,8,9
ElseIf Shift = 1 And KeyCode = 9 Then 'Shift tab
ElseIf Shift = 1 And KeyCode = 37 Then 'Shift left arrow
ElseIf Shift = 1 And KeyCode = 39 Then 'Shift right arrow
ElseIf Shift = 2 And KeyCode = 67 Then 'Control C
ElseIf Shift = 2 And KeyCode = 86 Then 'Control V
ElseIf Shift = 2 And KeyCode = 88 Then 'Control X
Else
'Do not process all other key combinations (by setting keycode to 0)
KeyCode = 0
End If
End Sub
Private Sub textboxStrikingPrice_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Process these keys (by doing nothing)
If Shift = 0 And KeyCode = 8 Then 'Backspace
ElseIf Shift = 0 And KeyCode = 9 Then 'Tab
ElseIf Shift = 0 And KeyCode = 13 Then 'Enter
ElseIf Shift = 0 And KeyCode = 37 Then 'Left arrow
ElseIf Shift = 0 And KeyCode = 39 Then 'Right arrow
ElseIf Shift = 0 And KeyCode = 46 Then 'Del
ElseIf Shift = 0 And KeyCode >= 48 And KeyCode <= 57 Then 'numbers 0,1,2,3,4,5,6,7,8,9
ElseIf Shift = 1 And KeyCode = 9 Then 'Shift tab
ElseIf Shift = 1 And KeyCode = 37 Then 'Shift left arrow
ElseIf Shift = 1 And KeyCode = 39 Then 'Shift right arrow
ElseIf Shift = 2 And KeyCode = 67 Then 'Control C
ElseIf Shift = 2 And KeyCode = 86 Then 'Control V
ElseIf Shift = 2 And KeyCode = 88 Then 'Control X
Else
'Do not process all other key combinations (by setting keycode to 0)
KeyCode = 0
End If
End Sub
Private Sub UserForm_Activate()
comboboxBuySell.Clear
comboboxBuySell.AddItem "BUY"
comboboxBuySell.AddItem "SELL"
comboboxCallPut.Clear
comboboxCallPut.AddItem "CALL"
comboboxCallPut.AddItem "PUT"
'Clear form and collection
comboboxBuySell.Text = ""
comboboxCallPut.Text = ""
textboxStrikingPrice.Text = ""
textboxPremium = ""
listboxList.Clear
Set myCollection = Nothing
End Sub
Private Sub UserForm_Deactivate()
'Clear form and collection
comboboxBuySell.Text = ""
comboboxCallPut.Text = ""
textboxStrikingPrice.Text = ""
textboxPremium = ""
listboxList.Clear
Set myCollection = Nothing
End Sub
The code of the module OptionModule follows:
Option Explicit Public myCollection As New Collection Public Sub StockOptions() formInputOptions.Show End Sub
The code of the class module OptionClass follows:
Option Explicit
Public BuySell As String
Public CallPut As String
Public StrikingPrice As Long
Public Premium As Long
Public ExcelColumn As String
Public Function SuggestedMinPrice() As Long
Dim Boundary As Long
Boundary = 5
If CallPut = "CALL" Then
SuggestedMinPrice = StrikingPrice - Boundary
End If
If CallPut = "PUT" Then
SuggestedMinPrice = StrikingPrice - Premium - Boundary
End If
End Function
Public Function SuggestedMaxPrice() As Long
Dim Boundary As Long
Boundary = 5
If CallPut = "CALL" Then
SuggestedMaxPrice = StrikingPrice + Premium + Boundary
End If
If CallPut = "PUT" Then
SuggestedMaxPrice = StrikingPrice + Boundary
End If
End Function
Public Sub CalculateValues(ByVal MinPrice As Long, ByVal MaxPrice As Long)
Dim myArray() As Long
ReDim myArray(MinPrice To MaxPrice) As Long
Dim myCounter As Long
Dim myDifference As Long
If CallPut = "CALL" And BuySell = "BUY" Then
'From MinPrice to StrikingPrice, Value = -Premium
'From StrikingPrice to MaxPrice, Value increases from -Premium
For myCounter = MinPrice To StrikingPrice Step 1
myArray(myCounter) = -Premium
Next myCounter
myDifference = 0
For myCounter = StrikingPrice To MaxPrice Step 1
myArray(myCounter) = -Premium + myDifference
myDifference = myDifference + 1
Next myCounter
End If
If CallPut = "CALL" And BuySell = "SELL" Then
'From MinPrice to StrikingPrice, Value = Premium
'From StrikingPrice to MaxPrice, Value decreases from Premium
For myCounter = MinPrice To StrikingPrice Step 1
myArray(myCounter) = Premium
Next myCounter
myDifference = 0
For myCounter = StrikingPrice To MaxPrice Step 1
myArray(myCounter) = Premium - myDifference
myDifference = myDifference + 1
Next myCounter
End If
If CallPut = "PUT" And BuySell = "BUY" Then
'From MaxPrice to StrikingPrice, Value is -Premium
'From StrikingPrice to MinPrice, Value increases from -Premium
For myCounter = MaxPrice To StrikingPrice Step -1
myArray(myCounter) = -Premium
Next myCounter
myDifference = 0
For myCounter = StrikingPrice To MinPrice Step -1
myArray(myCounter) = -Premium + myDifference
myDifference = myDifference + 1
Next myCounter
End If
If CallPut = "PUT" And BuySell = "SELL" Then
'From MaxPrice to StrikingPrice, Value is Premium
'From StrikingPrice to MinPrice, Value decreases from Premium
For myCounter = MaxPrice To StrikingPrice Step -1
myArray(myCounter) = Premium
Next myCounter
myDifference = 0
For myCounter = StrikingPrice To MinPrice Step -1
myArray(myCounter) = Premium - myDifference
myDifference = myDifference + 1
Next myCounter
End If
Range(ExcelColumn & "1").Select
ActiveCell.FormulaR1C1 = BuySell & " " & CStr(StrikingPrice) & " " & CallPut & " at " & CStr(Premium)
myDifference = MaxPrice
For myCounter = 2 To MaxPrice - MinPrice + 2
Range(ExcelColumn & CStr(myCounter)).Select
ActiveCell.FormulaR1C1 = myArray(myDifference)
myDifference = myDifference - 1
Next myCounter
End Sub
When the macro is run, the user is supposed to enter one or more transactions. For each transaction, she should select either BUY or SELL, enter a striking price, select either CALL or PUT, enter a premium and press the button Set. The transaction now appears in the list box under the button Set. The user can enter as many transactions as she wishes. When all transactions have been entered, the user should press the button Calculate and the form will be hidden and a table of the calculated result of all the transactions will be displayed in Excel.
