選擇權賣方最佳結算價

 

這篇要紀錄選擇權賣方最佳結算價的小工具設計，這個價格的意義是在參考整個選擇權市場上未平倉合約對於最可能結算位置的預測，簡單的說，就是結在哪裡會賺最多。

 

以L自己的習慣來說，參考最大未平倉合約的位置及最佳結算價位置是每日必看的一項指標，有參考性，但同時也要提醒，期交所資料都是收盤後才公布，當日有大行情變化時是跟不上的，另外，這也僅僅是從選擇權市場來看。所以，心態上將它當作較長期的變化，這樣的角度或許比較適當。

 

稍微介紹後就來實作吧，寫EXCEL VBA，期交所每日公布選擇權每日交易行情查詢，由Excel匯入外部資料的連結是http://www.taifex.com.tw/chinese/3/3_2_tbl.asp，

 

然後我們需要的資料有合約別、履約價、買賣權、結算價、未平倉合約量，將這些貼到sheet2再來算，前置作業是把周和近遠月的合約分開，這點也順帶提醒，由不同月份合約的最佳結算價可以看出市場對於近遠月指數的預期，這點也蠻值得參考的。

 

主要公式就是模擬各結算價下，現在未平倉合約的總損益如何，所以各個履約價都算一次完後加總起來，計算原則很簡單，

1. 若是call & 模擬結算價<履約價，獲利為市價*未平倉口數
2. 若是call & 模擬結算價>履約價，損益為( (模擬結算價-履約價)+市價)*未平倉口數
3. 若是put & 模擬結算價>履約價，獲利為市價*未平倉口數
4. 若是put & 模擬結算價<履約價，損益為( (履約價-模擬結算價)+市價)*未平倉口數

 

最後把各履約價的損益加總起來計在模擬價旁邊，看在哪個模擬價位是獲利最大就得到了。這個東西也能再進階去探索，就是把未平倉的GREEKS全算出來，搭配期貨去看，那有機會再寫。

 

其他的部份都是處理資料和迴圈而已，有需要參考的讀友可以看看下面程式碼區，如果想進一步瞭解的話，或許也可以再詳細作一篇紀錄。

當然有錯的話，L不負責任，有心得或指教的話，歡迎留言討論。

 

Sub cal()

Sheet2.Range("A2:Z700").Clear

i = 6

While Sheet1.Cells(i, 1) <> ""

If Sheet2.Cells(2, 2) = "" Then
    Sheet2.Cells(2, 2) = Sheet1.Cells(i, 2)
ElseIf Sheet2.Cells(3, 2) = "" And Sheet1.Cells(i, 2) <> Sheet2.Cells(2, 2) Then
    Sheet2.Cells(3, 2) = Sheet1.Cells(i, 2)
ElseIf Sheet2.Cells(4, 2) = "" And Sheet2.Cells(3, 2) <> "" And Sheet1.Cells(i, 2) <> Sheet2.Cells(3, 2) Then
    Sheet2.Cells(4, 2) = Sheet1.Cells(i, 2)
ElseIf Sheet2.Cells(5, 2) = "" And Sheet2.Cells(4, 2) <> "" And Sheet2.Cells(3, 2) <> "" And Sheet1.Cells(i, 2) <> Sheet2.Cells(4, 2) Then
    Sheet2.Cells(5, 2) = Sheet1.Cells(i, 2)
End If

Sheet2.Cells(i + 2, 2) = Sheet1.Cells(i, 2)
Sheet2.Cells(i + 2, 3) = Sheet1.Cells(i, 3)
Sheet2.Cells(i + 2, 4) = Sheet1.Cells(i, 4)
Sheet2.Cells(i + 2, 5) = Sheet1.Cells(i, 13)
Sheet2.Cells(i + 2, 6) = Sheet1.Cells(i, 9)

i = i + 1
Wend



i = 8

While Sheet2.Cells(i, 2) <> ""

If Sheet2.Cells(i, 2) = Sheet2.Cells(2, 2) Then
    If Sheet2.Cells(i, 4) = "Call" And Sheet2.Cells(i, 5) > Sheet2.Cells(2, 4) Then
        Sheet2.Cells(2, 4) = Sheet2.Cells(i, 5)
        Sheet2.Cells(2, 3) = Sheet2.Cells(i, 3)
    End If
    If Sheet2.Cells(i, 4) = "Put" And Sheet2.Cells(i, 5) > Sheet2.Cells(2, 6) Then
        Sheet2.Cells(2, 6) = Sheet2.Cells(i, 5)
        Sheet2.Cells(2, 5) = Sheet2.Cells(i, 3)
    End If
End If

If Sheet2.Cells(i, 2) = Sheet2.Cells(3, 2) Then
    If Sheet2.Cells(i, 4) = "Call" And Sheet2.Cells(i, 5) > Sheet2.Cells(3, 4) Then
        Sheet2.Cells(3, 4) = Sheet2.Cells(i, 5)
        Sheet2.Cells(3, 3) = Sheet2.Cells(i, 3)
    End If
    If Sheet2.Cells(i, 4) = "Put" And Sheet2.Cells(i, 5) > Sheet2.Cells(3, 6) Then
        Sheet2.Cells(3, 6) = Sheet2.Cells(i, 5)
        Sheet2.Cells(3, 5) = Sheet2.Cells(i, 3)
    End If
End If

If Sheet2.Cells(i, 2) = Sheet2.Cells(4, 2) Then
    If Sheet2.Cells(i, 4) = "Call" And Sheet2.Cells(i, 5) > Sheet2.Cells(4, 4) Then
        Sheet2.Cells(4, 4) = Sheet2.Cells(i, 5)
        Sheet2.Cells(4, 3) = Sheet2.Cells(i, 3)
    End If
    If Sheet2.Cells(i, 4) = "Put" And Sheet2.Cells(i, 5) > Sheet2.Cells(4, 6) Then
        Sheet2.Cells(4, 6) = Sheet2.Cells(i, 5)
        Sheet2.Cells(4, 5) = Sheet2.Cells(i, 3)
    End If
End If

If Sheet2.Cells(i, 2) = Sheet2.Cells(5, 2) Then
    If Sheet2.Cells(i, 4) = "Call" And Sheet2.Cells(i, 5) > Sheet2.Cells(5, 4) Then
        Sheet2.Cells(5, 4) = Sheet2.Cells(i, 5)
        Sheet2.Cells(5, 3) = Sheet2.Cells(i, 3)
    End If
    If Sheet2.Cells(i, 4) = "Put" And Sheet2.Cells(i, 5) > Sheet2.Cells(5, 6) Then
        Sheet2.Cells(5, 6) = Sheet2.Cells(i, 5)
        Sheet2.Cells(5, 5) = Sheet2.Cells(i, 3)
    End If
End If


i = i + 1
Wend


Sheet2.Cells(7, 9) = Sheet2.Cells(2, 2)
Sheet2.Cells(7, 10) = Sheet2.Cells(3, 2)
Sheet2.Cells(7, 11) = Sheet2.Cells(4, 2)
Sheet2.Cells(7, 12) = Sheet2.Cells(5, 2)

i = Sheet2.Cells(1, 10)
j = 8

While i < Sheet2.Cells(1, 11)

Sheet2.Cells(j, 8) = i


j = j + 1
i = i + 5
Wend








j = 8

While Sheet2.Cells(j, 8) <> ""

settle = Sheet2.Cells(j, 8)


    i = 8
    
    While Sheet2.Cells(i, 2) <> ""
    
    If Sheet2.Cells(i, 4) = "Call" And Sheet2.Cells(i, 6) <> "-" Then
        If settle > Sheet2.Cells(i, 3) Then Sheet2.Cells(i, 7) = (Sheet2.Cells(i, 6) - (settle - Sheet2.Cells(i, 3))) * Sheet2.Cells(i, 5)
        If settle <= Sheet2.Cells(i, 3) Then Sheet2.Cells(i, 7) = Sheet2.Cells(i, 6) * Sheet2.Cells(i, 5)
    End If
    
    If Sheet2.Cells(i, 4) = "Put" And Sheet2.Cells(i, 6) <> "-" Then
        If settle >= Sheet2.Cells(i, 3) Then Sheet2.Cells(i, 7) = Sheet2.Cells(i, 6) * Sheet2.Cells(i, 5)
        If settle < Sheet2.Cells(i, 3) Then Sheet2.Cells(i, 7) = (Sheet2.Cells(i, 6) - (Sheet2.Cells(i, 3) - settle)) * Sheet2.Cells(i, 5)
    End If
    
    If Sheet2.Cells(i, 2) = Sheet2.Cells(7, 9) Then
        Sheet2.Cells(j, 9) = Sheet2.Cells(j, 9) + Sheet2.Cells(i, 7)
    ElseIf Sheet2.Cells(i, 2) = Sheet2.Cells(7, 10) Then
        Sheet2.Cells(j, 10) = Sheet2.Cells(j, 10) + Sheet2.Cells(i, 7)
    ElseIf Sheet2.Cells(i, 2) = Sheet2.Cells(7, 11) Then
        Sheet2.Cells(j, 11) = Sheet2.Cells(j, 11) + Sheet2.Cells(i, 7)
    ElseIf Sheet2.Cells(i, 2) = Sheet2.Cells(7, 12) Then
        Sheet2.Cells(j, 12) = Sheet2.Cells(j, 12) + Sheet2.Cells(i, 7)
    End If
    
    i = i + 1
    Wend
    
j = j + 1
Wend


Sheet2.Cells(6, 9) = Application.WorksheetFunction.Max(Sheet2.Range("I8:I360"))
Sheet2.Cells(6, 10) = Application.WorksheetFunction.Max(Sheet2.Range("J8:J360"))
Sheet2.Cells(6, 11) = Application.WorksheetFunction.Max(Sheet2.Range("K8:K360"))
Sheet2.Cells(6, 12) = Application.WorksheetFunction.Max(Sheet2.Range("L8:L360"))




i = 8
    
While Sheet2.Cells(i, 8) <> ""

If Sheet2.Cells(i, 9) = Sheet2.Cells(6, 9) Then Sheet2.Cells(2, 7) = Sheet2.Cells(i, 8)
If Sheet2.Cells(i, 10) = Sheet2.Cells(6, 10) Then Sheet2.Cells(3, 7) = Sheet2.Cells(i, 8)
If Sheet2.Cells(i, 11) = Sheet2.Cells(6, 11) Then Sheet2.Cells(4, 7) = Sheet2.Cells(i, 8)
If Sheet2.Cells(i, 12) = Sheet2.Cells(6, 12) Then Sheet2.Cells(5, 7) = Sheet2.Cells(i, 8)

i = i + 1
Wend



i = 1
While Sheet3.Cells(i, 1) <> ""

i = i + 1
Wend

Sheet3.Cells(i, 1) = Sheet1.Cells(2, 1)
Sheet3.Cells(i, 3) = Sheet2.Cells(2, 7)



End Sub