2014年10月16日 星期四

選擇權賣方最佳結算價

 

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

 

以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

 

 

2 則留言:

  1. 請問一開始 i 為什麼要從6開始呢
    本來以為 i 是指標題列有幾行
    但抓下來的資料 標題列只有2行

    回覆刪除
  2. 若是call & 模擬結算價履約價,損益為( (模擬結算價-履約價)+市價)*未平倉口數
    若是put & 模擬結算價>履約價,獲利為市價*未平倉口數
    若是put & 模擬結算價<履約價,損益為( (履約價-模擬結算價)+市價)*未平倉口數

    不好意思..這段有點不太懂
    請問如果以九月結算的話
    那這個公式要如何套呢?
    可否指點迷津
    謝謝

    回覆刪除