|
这篇要纪录选择权卖方最佳结算价的小工具设计,这个价格的意义是在参考整个选择权市场上未平仓合约对于最可能结算位置的预测,简单的说,就是结在哪裡会赚最多。 以L自己的习惯来说,参考最大未平仓合约的位置及最佳结算价位置是每日必看的一项指标,有参考性,但同时也要提醒,期交所资料都是收盘后才公布,当日有大行情变化时是跟不上的,另外,这也仅仅是从选择权市场来看。所以,心态上将它当作较长期的变化,这样的角度或许比较适当。 稍微介绍后就来实作吧,写EXCEL VBA,期交所每日公布选择权每日交易行情查询,由Excel汇入外部资料的连结是http://www.taifex.com.tw/chinese/3/3_2_tbl.asp,然后我们需要的资料有合约别、履约价、买卖权、结算价、未平仓合约量,将这些贴到sheet2再来算,前置作业是把周和近远月的合约分开,这点也顺带提醒,由不同月份合约的最佳结算价可以看出市场对于近远月指数的预期,这点也蛮值得参考的。 主要公式就是模拟各结算价下,现在未平仓合约的总损益如何,所以各个履约价都算一次完后加总起来,计算原则很简单, 若是call & 模拟结算价<履约价,获利为市价*未平仓口数 若是call & 模拟结算价>履约价,损益为( (模拟结算价-履约价) 市价)*未平仓口数 若是put & 模拟结算价>履约价,获利为市价*未平仓口数 若是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 Written by L | 
转播
