作者 主題: [分享]Excel VBA 與 風險控管,常態分配,選擇權計價的應用  (閱讀 18668 次)

0 會員 與 1 訪客 正在閱讀本文。

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 622
    • 檢視個人資料
***基本:取得excel 資料

=C$3*$B4

3取絕對值,B取絕對值
=SUM($A$1:$A$10)
=Sheet2!A1+1
=[Budget.xls]Sheet1!A1+1


插入名稱定義
=income-expense

Sum of Sales where Month="Jan" AND Region="North"
=SUM((A2:A10="Jan")*(B2:B10="North")*C2:C10)

Sum of Sales where Month="Jan" AND Region<>"North"
=SUM((A2:A10="Jan")*(B2:B10<>"North")*C2:C10)

Count of Sales where Month="Jan" AND Region="North"
=SUM((A2:A10="Jan")*(B2:B10="North"))

Sum of Sales where Month="Jan" and Sales>= 200
=SUM((A2:A10="Jan")*(C2:C10>=200)*(C2:C10))

Sum of Sales between 300 and 400
=SUM((C2:C10>=300)*(C2:C10<=400)*(C2:C10))

Count of Sales between 300 and 400
=SUM((C2:C10>=300)*(C2:C10<=400))


column a 有英文名 如何透過一連串的formula 將英文名的姓分出

代碼: [選擇]
b8-h8

=TRIM(A8) 
=FIND(" ",B8,1)
=FIND(" ",B8,C8+1)
=IF(ISERROR(D8),C8,D8)
=LEFT(B8,C8)
=RIGHT(B8,LEN(B8)-E8)
=F8&G8

***XML 資料匯入
/資料/XML/XML來源
XML對應
拖至某儲存格


代碼: [選擇]

<?xml version="1.0"?>
<Customer>
<Name>Joe Smith</Name>
<AcctNo>32374-94</AcctNo>
<LoanAmt>$325,983</LoanAmt>
<IntRate>6.25%</IntRate>
<Term>30</Term>
</Customer>

/資料/XML/匯入

***改變 comment顏色
  Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
        cmt.Shape.Fill.ForeColor.SchemeColor = Int((80) * Rnd + 1) '1-80
        cmt.Shape.TextFrame.Characters.Font.ColorIndex = Int((56) * Rnd + 1) '1-56
    Next cmt


***VBA Format

' 以系統設定的長時間格式傳回現在系統時間。
MyStr = Format(Time, "Long Time")

' 以系統設定的長日期格式傳回現在系統日期。

代碼: [選擇]
MyStr = Format(Date, "Long Date")

MyStr = Format(MyTime, "h:m:s")    ' 傳回 "17:4:23"。
MyStr = Format(MyTime, "hh:mm:ss AMPM")    ' 傳回 "05:04:23 PM"。
MyStr = Format(MyDate, "dddd, mmm d yyyy")    ' 傳回 "Wednesday, Jan 27 1993"。
' 如果沒有指定格式,則傳回字串。
MyStr = Format(23)    ' 傳回 "23"。

' 使用者自訂的格式。
MyStr = Format(5459.4, "##,##0。00")    ' 傳回 "5,459.40"。
MyStr = Format(334。9, "###0。00")    ' 傳回 "334.90"。
MyStr = Format(5, "0。00%")    ' 傳回 "500.00%"。
MyStr = Format("HELLO", "<")    ' 傳回 "hello"。
MyStr = Format("This is it", ">")    ' 傳回 "THIS IS IT"。

***開啟檔案,讀取.txt檔

Sub DoWhileDemo()

   Dim LineCt As Long
   Open "c:\data\textfile.txt" For Input As #1
   LineCt = 0

   Do While Not EOF(1)
   Line Input #1, LineOfText
   Range("A1").Offset(LineCt,0) = UCase(LineOfText)
   LineCt = LineCt +1
   Loop

   Close #1
End Sub


***物件控制

   Dim AllVisible As Boolean
  Dim Item As Window
   For Each Item In Window
        If Item.Visible = False Then

              AllVisible = False
              Exit For
        End If

  Next Item

MsgBox AllVisible


***對selection的做控制
Dim Cell as Range
For Each Cell In Selection
     Cell.Value = UCase(Cell.Value)
Next Cell




***GOTO and Error

Sub GoToDemo()
   UserName = InputBox("Enter Your Name:")
   If UserName >< "Howard" Then GoTo WrongName
   MsgBox ("Welcome Howard ....")
'   more code here

   Exit Sub

WrongName
   MsgBox "Sorry. Only Howard can run this."

End Sub



***select case range
Sub Discount3()
Dim Quantity as Variant
Dim Discount as Double

Quantity = InputBox("Enter Quantity")

Select Case Quantity

       Case ""
          Exit Sub

       Case 0 To 24
          Discount = 0.1

      Case 25 To 49
          Discount = 0.15

       Case is >= 75
          Discount = 0.25

End Select
Msgbox "Discount: " & Discount

End Sub

       





***顯示comment
 If Application.DisplayCommentIndicator = xlCommentAndIndicator Then
        Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    Else
        Application.DisplayCommentIndicator = xlCommentAndIndicator
  End If

***顯示msgbox
Msg = "Is your name " & Application.UserName & "?"
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbNo Then
        MsgBox "Oh, never mind."
    Else
        MsgBox "I must be clairvoyant!"
    End If


***動態指定
ActiveCell.Resize(,6)=Array('Jan","Feb","Mar","Apr","May","Jun")

ActiveCell.Offset(0,0) ="Jan"

With Range("A1:C5")
      .NumberFormat = "#,##0.00"
      .Font.Bold = True
End With

***即時運算
? Range("A1").Value


***斷行
Worksheets("Sheet1").Range("A1").Copy _
 Worksheets("Sheet1").Range("B1")


***Debug
For Each cmt in ActiveSheet.Comments
     Debug.Print cmt.
Next cmt


***
Worksheets("Sheet1").Range("income").Value=1
Worksheets("Sheet1").Range("A1:B10").Value=1
Range("A1","B10") = 2
Range("C1:C10 A6:E6") =3
Range("A1,A3,A5,A7,A9") = 7


*多行
x=1: y=2: z=3: MsgBox x+y+z













*Range
Worksheets("Sheet1").Range("A1") = "kk"

Worksheets("Backtesting").Activate
Range("A1:A5")
Range("PriceList")



整列Range("3:3")
整行Range("D:D")

*Cells物件 格式:Cells(列號,行號)
C2儲存格: Cells(2,3)
Range(Cells(1,1), Cells(10,10))=Range("A1:J10")



*Offset物件 格式:Offset(列偏移量,行偏移量)
C2 儲存格: Range("A1").Offset(1,2)

*Rows物件
Rows("1:5")

*Columns(範圍區間)
Column("A:C")


***常用範圍物件屬性

*value
MsgBox Worksheets("Sheet1").Range("A1").Value
Worksheets("Sheet1").Range("A1:C3").Value=75

*Count
MsgBox Range("A1:C3").Count

*Address屬性
MsgBox Range(Cells(1,1),Cells(5,5)).Address

*Formula屬性
Range("A13").Formula="=Sqrt(100)"

*NumberFormat屬性
Columns("A:A").NumberFormat="0.00%"

***陣列宣告
Dim ArrayName(num) As DataType

Dim array_1(11) as Float
Dim arrary_1!(11)

可以option Base#敘述改變基底,預設基底為0
Option Base 1
Dim array_2(12) As Float

可以明確指定索引的上下限
Dim array_3(10 To 21) As Float


**二維陣列
Dim array_2D(2, 3) as Float
dim MyArray() as Integer
Dim MyArray(1 To 10, 1 To 10) as Integer



***使用者自訂的資料型態
Type CustomerInfo
   Company As String * 25
   Contact As String * 15
   RegionCode As Integer
   Sales As Long
End Type

Dim Customers(1 To 100) As CustomerInfo

Customer(1).Company = "Acme Tools"
Customer(1).Contact = "Tim Robertson"
Customer(1).RegionCode =3
Customer(1).Sales = 150677

Customer(2) = Customer(1)

Customer(2).Sales = Customer(1).Sales






**example

Dim i, j As Integer
Dim Array_1(10,10) As Double

For i = 1 To 10
      For j = 1 To 10

            Arrary_1(i, j) = i * j

      Next  j
Next I



***常用範圍物件方法
*Select 方法
選取一個範圍物件,要先Activate工作表
選取A1:C12
Sheets("Sheet1").Activate
Range("A1:C12").Select


*Copy & Paste方法
將A1:A12選取,並拷貝到C1格

Sub CopyRange()
    Range("A1:A12").Select
    Selection.Copy
    Range("C1").Select
    Selection.Paste
End Sub



直接拷貝A1:A12到C1格

Sub CopyRange2()


      Range("A1:A12").Copy Range("C1")

End Sub

*Clear方法
清除D行的內容
        Columns("D:D").Clear

*Delete方法
刪除第6列

        Row("6:6").Delete


------------------------------------------------------------------------
***數值函數
    *Exp 函數:指數函數
       數學型式: y=ex
     程是範例:
     Dim X as Double, Y As Double
     X=1
     Y=Exp(X)
     MsgBox Y      'Y=2.7182


    *Log函數
       數學形式:  y=log(x)

     程是範例:
     Dim X as Double, Y As Double
     X = 10
     Y = Log(X)
     MsgBox Y    'Y=2.302585

     *開根號函數
       Y = Sqr(X)  '將X開根號

       *幾次方函數
       Pow(X,Z) 'X的Z次方

       *絕對數
      Y=Abs(X)   '取X的絕對數

      *將指定字串轉為數值
       Y=Val(X)

     *Round 四捨五入

       *Int 傳回小於或等於指定數值的整數
       Y=Int(x)


      *Len 字串長度

       *Chr(65) 傳回A

     *Now

     *Year(Date)

     *Month(Date)

     *Day(Date)


***輸出入函數

       *MsgBox x
     *Y=InputBox "Input Your Name:"


-----------------------------------------------------------
***statement
*for next

Public Sub AddTotal()
    Dim count As Integer
    Dim Sum As Long
   
    Sum = 0
    For count = 1 To 10 Step 1
        Sum = Sum + count
        MsgBox Str(count) + " / " + Str(Sum)
    Next count

    MsgBox "Total Sum is :" + Str(Sum)
End Sub

* if 算折現值

Public Sub DiscountTest()
    Dim CF As Double
    Dim Maturity As Date
    Dim Rate As Double
    Dim today As Date
    Dim P As Double
   
    CF = InputBox("CF Amount")
    Maturity = InputBox("CF Date")
    Rate = InputBox("Discount Rate")
    today = Date
   
    If (Maturity < (today + 365)) Then
        P = CF / (1 + Rate * (Maturity - today) / 365)
    Else
        P = CF / ((1 + Rate) ^ ((Maturity - today) / 365))
    End If
   
    MsgBox P
End Sub



***輸入常態分配間距z,算出單線機率
Const Pi = 3.14159265358979
Public Sub PdfTest()
    Dim x As Double
    Dim pdf As Double

    x = Val(InputBox("Input the point to calculate probability: "))
   
    pdf = Exp(-x * x / 2) / Sqr(2 * Pi)
   
    MsgBox "The probability at " + Str(x) + " is " + Str(pdf)
End Sub

***輸入log常態分配間距z,算出單直線區域累績機率
LOGNORMDIST(x,mean,standard_dev)
X    是一個用來評估這個函數的值。
Mean    為 ln(x) 的平均數。
Standard_dev    為 ln(x) 的標準差。



***輸入常態分配間距z,算出累績機率 。此分配的平均值是 0 (零) 和標準差 1。利用此函數可代替標準常態分配函數曲線之表格。

*等於 系統內建的功能 y = Application.WorksheetFunction.NormSDist(x)
*NormSDist(x)=NORMDIST(x,0,1,TRUE)


***輸入log常態分配間距d,算出單直線區域累績機率
Public Function NorCdf(d As Double) As Double
    Dim ans As Double, g As Double
    Const a1 = 0.4361836
    Const a2 = -0.1201676
    Const a3 = 0.937298
    g = 1 / (1 + 0.33267 * d)
 
    If d >= 0 Then
        ans = 1 - (a1 * g + a2 * g * g + a3 * g * g * g) * NorPdf(d)
    Else
        ans = 1 - NorCdf(-d)
    End If
       
    NorCdf = ans
End Function

***用Black Shloe Model 算出Option Delta

Public Sub Op_Calculate()
    Dim S As Double
    Dim K As Double
    Dim T As Double
    Dim sig As Double
    Dim r As Double
    Dim y As Double
    Dim C_Value As Double
    Dim P_Value As Double
    Dim d1 As Double, d2 As Double
   
   
  For i = 2 To 100
 
    S = Worksheets("Delta").Cells(i, 4).Value
 
  'MsgBox ("S" & " " & S)
  Worksheets("Delta").Cells(i, 4).Select
 
 
    K = Worksheets("Delta").Cells(1, 2).Value
   '   MsgBox ("K" & " " & K)
 
   
    T = Worksheets("Delta").Cells(4, 2).Value
    '  MsgBox ("T" & " " & T)
Worksheets("Delta").Cells(4, 2).Select
 
    sig = Worksheets("Delta").Cells(3, 2).Value
   
     ' MsgBox ("Sig" & " " & sig)

    r = Worksheets("Delta").Cells(2, 2).Value
      'MsgBox ("risk free" & " " & r)

    y = Worksheets("Delta").Cells(6, 2).Value
       ' MsgBox ("y equal to zero" & " " & y)

    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    'Worksheets("Delta").Cells(i, 5).Select
   
    Worksheets("Delta").Cells(i, 5).Value = d1
    'd2 = d1 - sig * Sqr(T)
   
'    Worksheets("Sheet1").Cells(5, 5).Value = d1
'    Worksheets("Sheet1").Cells(5, 6).Value = d2
'Worksheets("Delta").Cells(i, 7).Value = NorPdf(d1)


Worksheets("Delta").Cells(i, 7).Value = NorCdf(d1)


Next i

End Sub

Public Function GBSDelta(OpClass As String, _
    S As Double, K As Double, T As Double, _
    sig As Double, r As Double, y As Double) _
    As Double
               
    Dim d1 As Double
   
    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
   
    If OpClass = "C" Then
        GBSDelta = Exp(-y * T) * NorCdf(d1)
    ElseIf OpClass = "P" Then
        GBSDelta = Exp(-y * T) * (NorCdf(d1) - 1)
    End If
   
End Function

--------------------------------------------
一般選擇權定價公式

S=目前股價  Stock

K=執行價格  Strick Price

r=融資利率為即期利率

T=距到期日的時間

sig=股價之波動性

y=連續固定股利比例

當y=0,便會回到不發放股利的BSOption計算函數

Public Function GBSOption(OpClass As String, S As Double, K As Double, T As Double, _
    sig As Double, r As Double, y As Double) As Double
   
    Dim d1 As Double, d2 As Double
    Dim z As Double
   
    If OpClass = "C" Then
        z = 1#
    Else
        z = -1#
    End If
   
    d1 = (Log(S / K) + (r - y + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    GBSOption = z * (S * Exp(-y * T) * NorCdf(z * d1) - K * Exp(-r * T) * NorCdf(z * d2))

End Function
-------------------------------------------
美式選擇權定價

如果目前股價大於或等於提前執行的關鍵價格,則買權應立刻被執行
買權價格即為內含價值:

C A (Call Option Price of America) = S-K, S>=  提前執行關鍵價格

如果目前股價小於提前執行的關鍵價格,則美式買權等於相對應的歐式買權加上提前執行的權利金
C A=CE(S,K,T)+A2(S/提前執行的關鍵價格)q2

代碼: [選擇]

Global Const Pi = 3.14159265358979

Option Explicit     'Requirs that all variables to be declared explicitly.
Option Compare Text 'Uppercase letters to be equivalent to lowercase letters.

Option Base 1       'The "Option Base" statment alowws to specify 0 or 1 as the
                    'default first index of arrays.
                   
                                                                                                                                                                                                                                     
'// Normal Pdf
Public Function NorPdf(D As Double) As Double
    Dim ans As Double
   
    ans = Exp(-D * D / 2) / Sqr(2 * Pi)
   
    NorPdf = ans
End Function


'// Normal Cdf
Public Function NorCdf(D As Double) As Double
    Dim ans As Double, g As Double
    Const a1 = 0.4361836
    Const a2 = -0.1201676
    Const a3 = 0.937298
    g = 1 / (1 + 0.33267 * D)
 
    If D >= 0 Then
        ans = 1 - (a1 * g + a2 * g * g + a3 * g * g * g) * NorPdf(D)
    Else
        ans = 1 - NorCdf(-D)
    End If
       
    NorCdf = ans
End Function


'// The cumulative bivariate normal distribution function
Public Function BiNorCdf(A As Double, b As Double, rho As Double) As Double
    Dim X As Variant, y As Variant
    Dim rho1 As Double, rho2 As Double, delta As Double
    Dim a1 As Double, b1 As Double, Sum As Double
    Dim I As Integer, j As Integer
   
    X = Array(0.24840615, 0.39233107, 0.21141819, 0.03324666, 0.00082485334)
    y = Array(0.10024215, 0.48281397, 1.0609498, 1.7797294, 2.6697604)
    a1 = A / Sqr(2 * (1 - rho ^ 2))
    b1 = b / Sqr(2 * (1 - rho ^ 2))
   
    If A <= 0 And b <= 0 And rho <= 0 Then
        Sum = 0
        For I = 1 To 5
            For j = 1 To 5
                Sum = Sum + X(I) * X(j) * Exp(a1 * (2 * y(I) - a1) _
                + b1 * (2 * y(j) - b1) + 2 * rho * (y(I) - a1) * (y(j) - b1))
            Next
        Next
        BiNorCdf = Sqr(1 - rho ^ 2) / Pi * Sum
    ElseIf A <= 0 And b >= 0 And rho >= 0 Then
        BiNorCdf = NorCdf(A) - BiNorCdf(A, -b, -rho)
    ElseIf A >= 0 And b <= 0 And rho >= 0 Then
        BiNorCdf = NorCdf(b) - BiNorCdf(-A, b, -rho)
    ElseIf A >= 0 And b >= 0 And rho <= 0 Then
        BiNorCdf = NorCdf(A) + NorCdf(b) - 1 + BiNorCdf(-A, -b, rho)
    ElseIf A * b * rho > 0 Then
        rho1 = (rho * A - b) * Sgn(A) / Sqr(A ^ 2 - 2 * rho * A * b + b ^ 2)
        rho2 = (rho * b - A) * Sgn(b) / Sqr(A ^ 2 - 2 * rho * A * b + b ^ 2)
        delta = (1 - Sgn(A) * Sgn(b)) / 4
        BiNorCdf = BiNorCdf(A, 0, rho1) + BiNorCdf(b, 0, rho2) - delta
    End If
End Function


'// Black and Scholes (1973) Stock options
Public Function BSOption(OpClass As String, S As Double, K As Double, T As Double, _
  sig As Double, r As Double) As Double
   
    Dim d1 As Double, d2 As Double
    Dim z As Double
   
    If OpClass = "C" Then
        z = 1#
    Else
        z = -1#
    End If
   
    d1 = (Log(S / K) + (r + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    BSOption = z * (S * NorCdf(z * d1) - K * Exp(-r * T) * NorCdf(z * d2))
End Function


'// American Options

'// American Calls on stocks with known dividends, Roll-Geske-Whaley
Public Function RollGeskeWhaley(S As Double, _
    K As Double, t1 As Double, T2 As Double, _
    r As Double, D As Double, sig As Double) As Double
    't1 time to dividend payout
    'T2 time to option expiration
   
    Dim Sx As Double, I As Double
    Dim a1 As Double, a2 As Double
    Dim b1 As Double, b2 As Double
    Dim HighS As Double, LowS As Double
    Dim epsilon As Double
    Dim ci As Double, infinity As Double
   
    infinity = 100000000
    epsilon = 0.00001
    Sx = S - D * Exp(-r * t1)
    If D <= K * (1 - Exp(-r * (T2 - t1))) Then
        RollGeskeWhaley = BSOption("C", Sx, K, T2, sig, r)
        Exit Function
    End If
    ci = BSOption("C", S, K, T2 - t1, sig, r)
    HighS = S
    While (ci - HighS - D + K) > 0 And HighS < infinity
        HighS = HighS * 2
        ci = BSOption("C", HighS, K, T2 - t1, sig, r)
    Wend
    If HighS > infinity Then
        RollGeskeWhaley = BSOption("C", Sx, K, T2, sig, r)
        Exit Function
    End If
   
    LowS = 0
    I = HighS * 0.5
    ci = BSOption("C", I, K, T2 - t1, sig, r)
   
    While ((Abs(ci - I - D + K) > epsilon) And _
    (HighS - LowS > epsilon))
        If (ci - I - D + K) < 0 Then
            HighS = I
        Else
            LowS = I
        End If
        I = (HighS + LowS) / 2
        ci = BSOption("C", I, K, T2 - t1, sig, r)
    Wend
    a1 = (Log(Sx / K) + (r + sig ^ 2 / 2) * T2) / (sig * Sqr(T2))
    a2 = a1 - sig * Sqr(T2)
    b1 = (Log(Sx / I) + (r + sig ^ 2 / 2) * t1) / (sig * Sqr(t1))
    b2 = b1 - sig * Sqr(t1)
   
    RollGeskeWhaley = Sx * NorCdf(b1) + Sx * BiNorCdf(a1, -b1, -Sqr(t1 / T2)) _
        - K * Exp(-r * T2) * BiNorCdf(a2, -b2, -Sqr(t1 / T2)) - (K - D) * Exp(-r * t1) * NorCdf(b2)
End Function



'// Generalized Black and Scholes options
Public Function GBSOption(OpClass As String, S As Double, K As Double, T As Double, _
  sig As Double, r As Double, y As Double) As Double
   
    Dim d1 As Double, d2 As Double
    Dim z As Double
   
    If OpClass = "C" Then
        z = 1#
    Else
        z = -1#
    End If
   
    d1 = (Log(S / K) + (r - y + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    GBSOption = z * (S * Exp(-y * T) * NorCdf(z * d1) - K * Exp(-r * T) * NorCdf(z * d2))
End Function


'// The Barone-Adesi and Whaley (1987) American approximation
Public Function BAWAmericanApprox(CallPutFlag As String, S As Double, K As Double, T As Double, r As Double, y As Double, sig As Double) As Double
    If CallPutFlag = "c" Then
        BAWAmericanApprox = BAWAmericanCallApprox(S, K, T, r, y, sig)
    ElseIf CallPutFlag = "p" Then
        BAWAmericanApprox = BAWAmericanPutApprox(S, K, T, r, y, sig)
    End If
End Function


'// American call
Private Function BAWAmericanCallApprox(S As Double, K As Double, T As Double, r As Double, y As Double, sig As Double) As Double
    Dim Sk As Double, n As Double, CP As Double
    Dim d1 As Double, Q2 As Double, a2 As Double

    If (r - y) >= r Then
        BAWAmericanCallApprox = GBSOption("c", S, K, T, sig, r, y)
    Else
        Sk = Kc(K, T, r, y, sig)
        n = 2 * (r - y) / sig ^ 2                                         '
        CP = 2 * r / (sig ^ 2 * (1 - Exp(-r * T)))
        d1 = (Log(Sk / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
        Q2 = (-(n - 1) + Sqr((n - 1) ^ 2 + 4 * CP)) / 2
        a2 = (Sk / Q2) * (1 - Exp(-y * T) * NorCdf(d1))
        If S < Sk Then
            BAWAmericanCallApprox = GBSOption("c", S, K, T, sig, r, y) + a2 * (S / Sk) ^ Q2
        Else
            BAWAmericanCallApprox = S - K
        End If
    End If
End Function


'// Newton Raphson algorithm to solve for the critical commodity price for a Call
Private Function Kc(K As Double, T As Double, r As Double, y As Double, sig As Double) As Double
    Dim n As Double, m As Double
    Dim Su As Double, Si As Double
    Dim h2 As Double, A As Double
    Dim d1 As Double, Q2 As Double, q2u As Double
    Dim LHS As Double, RHS As Double
    Dim bi As Double, E As Double
   
    '// Calculation of seed value, Si
    n = 2 * (r - y) / sig ^ 2
    m = 2 * r / sig ^ 2
    q2u = (-(n - 1) + Sqr((n - 1) ^ 2 + 4 * m)) / 2
    Su = K / (1 - 1 / q2u)
    h2 = -((r - y) * T + 2 * sig * Sqr(T)) * K / (Su - K)
    Si = K + (Su - K) * (1 - Exp(h2))

    A = 2 * r / (sig ^ 2 * (1 - Exp(-r * T)))
    d1 = (Log(Si / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    Q2 = (-(n - 1) + Sqr((n - 1) ^ 2 + 4 * A)) / 2
    LHS = Si - K
    RHS = GBSOption("c", Si, K, T, sig, r, y) + (1 - Exp(-y * T) * NorCdf(d1)) * Si / Q2
    bi = Exp(-y * T) * NorCdf(d1) * (1 - 1 / Q2) + (1 - Exp(-y * T) * NorCdf(d1) / (sig * Sqr(T))) / Q2
    E = 0.000001
    '// Newton Raphson algorithm for finding critical price Si
    While Abs(LHS - RHS) / K > E
        Si = (K + RHS - bi * Si) / (1 - bi)
        d1 = (Log(Si / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
        LHS = Si - K
        RHS = GBSOption("c", Si, K, T, sig, r, y) + (1 - Exp(-y * T) * NorCdf(d1)) * Si / Q2
        bi = Exp(-y * T) * NorCdf(d1) * (1 - 1 / Q2) + (1 - Exp(-y * T) * NorPdf(d1) / (sig * Sqr(T))) / Q2
    Wend
        Kc = Si
End Function


'// American put
Private Function BAWAmericanPutApprox(S As Double, K As Double, T As Double, r As Double, y As Double, sig As Double) As Double

    Dim Sk As Double, n As Double, CP As Double
    Dim d1 As Double, Q1 As Double, a1 As Double

    Sk = Kp(K, T, r, y, sig)
    n = 2 * (r - y) / sig ^ 2
    CP = 2 * r / (sig ^ 2 * (1 - Exp(-r * T)))
    d1 = (Log(Sk / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    Q1 = (-(n - 1) - Sqr((n - 1) ^ 2 + 4 * CP)) / 2
    a1 = -(Sk / Q1) * (1 - Exp(-y * T) * NorCdf(-d1))

    If S > Sk Then
        BAWAmericanPutApprox = GBSOption("p", S, K, T, sig, r, y) + a1 * (S / Sk) ^ Q1
    Else
        BAWAmericanPutApprox = K - S
    End If
End Function


'// Newton Raphson algorithm to solve for the critical commodity price for a Put
Private Function Kp(K As Double, T As Double, r As Double, y As Double, sig As Double) As Double
    Dim n As Double, m As Double
    Dim Su As Double, Si As Double
    Dim h1 As Double, A As Double
    Dim d1 As Double, q1u As Double, Q1 As Double
    Dim LHS As Double, RHS As Double
    Dim bi As Double, E As Double
   
    '// Calculation of seed value, Si
    n = 2 * (r - y) / sig ^ 2
    m = 2 * r / sig ^ 2
    q1u = (-(n - 1) - Sqr((n - 1) ^ 2 + 4 * m)) / 2
    Su = K / (1 - 1 / q1u)
    h1 = ((r - y) * T - 2 * sig * Sqr(T)) * K / (K - Su)
    Si = Su + (K - Su) * Exp(h1)

    A = 2 * r / (sig ^ 2 * (1 - Exp(-r * T)))
    d1 = (Log(Si / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    Q1 = (-(n - 1) - Sqr((n - 1) ^ 2 + 4 * A)) / 2
    LHS = K - Si
    RHS = GBSOption("p", Si, K, T, sig, r, y) - (1 - Exp(-y * T) * NorCdf(-d1)) * Si / Q1
    bi = -Exp(-y * T) * NorCdf(-d1) * (1 - 1 / Q1) - (1 + Exp(-y * T) * NorPdf(-d1) / (sig * Sqr(T))) / Q1
    E = 0.000001
    '// Newton Raphson algorithm for finding critical price Si
    While Abs(LHS - RHS) / K > E
        Si = (K - RHS + bi * Si) / (1 + bi)
        d1 = (Log(Si / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
        LHS = K - Si
        RHS = GBSOption("p", Si, K, T, sig, r, y) - (1 - Exp(-y * T) * NorCdf(-d1)) * Si / Q1
        bi = -Exp(-y * T) * NorCdf(-d1) * (1 - 1 / Q1) - (1 + Exp(-y * T) * NorCdf(-d1) / (sig * Sqr(T))) / Q1
    Wend
    Kp = Si
End Function






-------------------------------------------
計算選擇權隱含波動率

代碼: [選擇]

Const Pi = 3.14159265358979



'使用牛頓法尋根演算法計算出選擇權隱含波動率
Function GBSNewtonImpVolCall(C As Double, S As Double, K As Double, T As Double, r As Double, y As Double) As Double
    Dim sig As Double
    Dim d1 As Double
    Dim err As Double
    Dim dv As Double
    Dim price_err As Double
    Dim vega As Double
 
    sig = 0.2
    err = 0.0001
    dv = err + 1
    While (Abs(dv) > err)
        d1 = (Log(S / K) + (r - y + sig * sig / 2) * T) / (sig * Sqr(T))
        price_err = GBSOption("C", S, K, T, sig, r, y) - C
        vega = S * Sqr(T) * Exp(-y * T) * NorPdf(d1)
        dv = price_err / vega
        sig = sig - dv
    Wend
 
    GBSNewtonImpVolCall = sig
End Function


'使用二分法尋根演算法 算出隱含波動率
Public Function GBSImpliedVol(OpClass As String, _
   P As Double, S As Double, K As Double, _
   T As Double, r As Double, y As Double) As Double

    Dim sigL As Double, sigR As Double
    Dim err As Double, tol As Double, value As Double
         
    tol = 0.0001
    sigL = 0.0001
    sigR = 2
    err = Abs(sigR - sigL)
 
    Do While (err > tol)
        sig = (sigL + sigR) / 2
        value = GBSOption(OpClass, S, K, T, sig, r, y)

        If (value = P) Then GBSImpliedVol = sig
       
        If (value > P) Then
            sigR = sig
        Else
            sigL = sig
        End If

        err = Abs(sigR - sigL)
    Loop
     
    GBSImpliedVol = sig
End Function



Public Function GBSImpliedVol_1(ByVal OpClass As String, ByVal P As Double, ByVal S As Double, ByVal K As Double, ByVal T As Double, ByVal r As Double, ByVal y As Double) As Double

    Dim sigL As Double, sigR As Double
    Dim err As Double, tol As Double, value As Double
         
    tol = 0.0001
    sigL = 0.0001
    sigR = 2
    sig = (sigL + sigR) / 2
    value = GBSOption(OpClass, S, K, T, sig, r, y)
    err = Abs(value - P)
 
    Do While (err >= tol)
        If (value >= P) Then sigR = sig
        If (value < P) Then sigL = sig
        sig = (sigL + sigR) / 2
        value = GBSOption(OpClass, S, K, T, sig, r, y)
        err = Abs(value - P)
    Loop
     
    GBSImpliedVol_1 = sig

End Function



Public Function BSOption(ByVal OpClass As String, ByVal S As Double, ByVal K As Double, ByVal T As Double, _
  ByVal sig As Double, ByVal r As Double) As Double
   
    Dim d1 As Double, d2 As Double
    Dim z As Double
   
    If OpClass = "C" Then
        z = 1#
    Else
        z = -1#
    End If
   
    d1 = (Log(S / K) + (r + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    BSOption = z * (S * NorCdf(z * d1) - K * Exp(-r * T) * NorCdf(z * d2))

End Function



Public Function GBSOption(ByVal OpClass As String, ByVal S As Double, ByVal K As Double, ByVal T As Double, _
  ByVal sig As Double, ByVal r As Double, ByVal y As Double) As Double
   
    Dim d1 As Double, d2 As Double
    Dim z As Double
   
    If OpClass = "C" Then
        z = 1#
    Else
        z = -1#
    End If
   
    d1 = (Log(S / K) + (r - y + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    GBSOption = z * (S * Exp(-y * T) * NorCdf(z * d1) - K * Exp(-r * T) * NorCdf(z * d2))

End Function


Public Function Op_Cvalue(ByVal S As Double, ByVal K As Double, ByVal T As Double, _
  ByVal sig As Double, ByVal r As Double) As Double
   
    Dim d1 As Double, d2 As Double
   
    d1 = (Log(S / K) + (r + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    Op_Cvalue = S * NorCdf(d1) - K * Exp(-r * T) * NorCdf(d2)
End Function


Public Function Op_Pvalue(ByVal S As Double, ByVal K As Double, ByVal T As Double, _
  ByVal sig As Double, ByVal r As Double) As Double
 
    Dim d1 As Double, d2 As Double
 
    d1 = (Log(S / K) + (r + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    Op_Pvalue = K * Exp(-r * T) * NorCdf(-d2) - S * NorCdf(-d1)
End Function


Public Function NorPdf(D As Double) As Double
    Dim ans As Double
   
    ans = Exp(-D * D / 2) / Sqr(2 * Pi)
   
    NorPdf = ans
End Function


Public Function NorCdf(D As Double) As Double
    Dim ans As Double, g As Double
    Const a1 = 0.4361836
    Const a2 = -0.1201676
    Const a3 = 0.937298
    g = 1 / (1 + 0.33267 * D)
 
    If D >= 0 Then
        ans = 1 - (a1 * g + a2 * g * g + a3 * g * g * g) * NorPdf(D)
    Else
        ans = 1 - NorCdf(-D)
    End If
       
    NorCdf = ans
End Function





--------------------------------------------
計算option的希臘字母 Delta, Gamma

代碼: [選擇]

Const Pi = 3.14159265358979


'由於Call Delta=0.6242,此資產價格上漲$1元,買權上漲$1x0.6242=$0.6242

Public Function GBSDelta(OpClass As String, _
    S As Double, K As Double, T As Double, _
    sig As Double, r As Double, y As Double) _
    As Double
               
    Dim d1 As Double
   
    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
   
    If OpClass = "C" Then
        GBSDelta = Exp(-y * T) * NorCdf(d1)
    ElseIf OpClass = "P" Then
        GBSDelta = Exp(-y * T) * (NorCdf(d1) - 1)
    End If
   
End Function


Public Function GBSGamma(S As Double, K As Double, _
    T As Double, sig As Double, r As Double, _
    y As Double) As Double
   
    Dim d1 As Double
   
    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    GBSGamma = Exp(-y * T) * NorPdf(d1) / (S * sig * Sqr(T))
   
End Function


'當Vega 為36.7810,波動性上漲1%,買權上漲0.01x36.7810=$0.367810

Public Function GBSVega(S As Double, _
    K As Double, T As Double, sig As Double, _
    r As Double, y As Double) As Double
   
    Dim d1 As Double
   
    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    GBSVega = S * Exp(-y * T) * NorPdf(d1) * Sqr(T)
   
End Function

'當買權的Theta為-8.1085,隨到期日接近一日,買權下跌1/365*-8.1085=-$0.022215

Public Function GBSTheta(OpClass As String, _
    S As Double, K As Double, T As Double, _
    sig As Double, r As Double, y As Double) _
    As Double
   
    Dim d1 As Double, d2 As Double
   
    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)

    If OpClass = "C" Then
        GBSTheta = -S * Exp(-y * T) * NorPdf(d1) * sig / (2 * Sqr(T)) + y * S * Exp(-y * T) * NorCdf(d1) - r * K * Exp(-r * T) * NorCdf(d2)
    ElseIf OpClass = "P" Then
        GBSTheta = -S * Exp(-y * T) * NorPdf(d1) * sig / (2 * Sqr(T)) - y * S * Exp(-y * T) * NorCdf(-d1) + r * K * Exp(-r * T) * NorCdf(-d2)
    End If
   
End Function


' 當Rho_r 為47.991,利率上漲1%,買權上漲0.01 x 47.9971=$0.479971
Public Function GBSRho_r(OpClass As String, _
    S As Double, K As Double, T As Double, _
    sig As Double, r As Double, y As Double) _
    As Double
   
    Dim d1 As Double, d2 As Double
   
    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
   
    If OpClass = "C" Then
        If (r - y) <> 0 Then
            GBSRho_r = T * K * Exp(-r * T) * NorCdf(d2)
        Else
            GBSRho_r = -T * GBSOption(OpClass, S, K, T, sig, r, y)
        End If
    ElseIf OpClass = "P" Then
        If (r - y) <> 0 Then
            GBSRho_r = -T * K * Exp(-r * T) * NorCdf(-d2)
        Else
            GBSRho_r = -T * GBSOption(OpClass, S, K, T, sig, r, y)
        End If
    End If
   
End Function

'當Rho_y為-62.4215, 資產收益率上漲1% ,買權上漲0.01x-62.4215=
-$0.624215

Public Function GBSRho_y(OpClass As String, _
    S As Double, K As Double, T As Double, _
    sig As Double, r As Double, y As Double) _
    As Double
   
    Dim d1 As Double

    d1 = (Log(S / K) + (r - y + sig ^ 2 / 2) * T) / (sig * Sqr(T))
   
    If OpClass = "C" Then
        GBSRho_y = -T * S * Exp(-y * T) * NorCdf(d1)
    ElseIf OpClass = "P" Then
        GBSRho_y = T * S * Exp(-y * T) * NorCdf(-d1)
    End If
   
End Function


'******************************************************************************************

Public Function BSOption(OpClass As String, S As Double, K As Double, T As Double, _
    sig As Double, r As Double) As Double
   
    Dim d1 As Double, d2 As Double
    Dim z As Double
   
    If OpClass = "C" Then
        z = 1#
    Else
        z = -1#
    End If
   
    d1 = (Log(S / K) + (r + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    BSOption = z * (S * NorCdf(z * d1) - K * Exp(-r * T) * NorCdf(z * d2))

End Function



Public Function GBSOption(OpClass As String, S As Double, K As Double, T As Double, _
    sig As Double, r As Double, y As Double) As Double
   
    Dim d1 As Double, d2 As Double
    Dim z As Double
   
    If OpClass = "C" Then
        z = 1#
    Else
        z = -1#
    End If
   
    d1 = (Log(S / K) + (r - y + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    GBSOption = z * (S * Exp(-y * T) * NorCdf(z * d1) - K * Exp(-r * T) * NorCdf(z * d2))

End Function


Public Function Op_Cvalue(S As Double, K As Double, T As Double, _
    sig As Double, r As Double) As Double
   
    Dim d1 As Double, d2 As Double
   
    d1 = (Log(S / K) + (r + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    Op_Cvalue = S * NorCdf(d1) - K * Exp(-r * T) * NorCdf(d2)
End Function


Public Function Op_Pvalue(S As Double, K As Double, T As Double, _
    sig As Double, r As Double) As Double
 
    Dim d1 As Double, d2 As Double
 
    d1 = (Log(S / K) + (r + sig * sig / 2) * T) / (sig * Sqr(T))
    d2 = d1 - sig * Sqr(T)
 
    Op_Pvalue = K * Exp(-r * T) * NorCdf(-d2) - S * NorCdf(-d1)
End Function


Public Function NorPdf(D As Double) As Double
    Dim ans As Double
   
    ans = Exp(-D * D / 2) / Sqr(2 * Pi)
   
    NorPdf = ans
End Function


Public Function NorCdf(D As Double) As Double
    Dim ans As Double, g As Double
    Const a1 = 0.4361836
    Const a2 = -0.1201676
    Const a3 = 0.937298
    g = 1 / (1 + 0.33267 * D)
 
    If D >= 0 Then
        ans = 1 - (a1 * g + a2 * g * g + a3 * g * g * g) * NorPdf(D)
    Else
        ans = 1 - NorCdf(-D)
    End If
       
    NorCdf = ans
End Function




--------------------------------------------
由歷史資料算歷史波動率 voltality

欄A輸入歷史股價
欄B5=A5/A4,以此類推,先計算資產價格的報酬率
欄C5=LN(B5),以此類推,再以Ln()函數計算對數報酬
欄F7 利用試算函數 Stdev(C5:C24)算出資產價格波動性

假設一年有250個交易日,乘上開根號250便可得到年波動性,43.54%

-------------------------------------------
***使用array將歷史波動率算出

Const DayOfYear = 250  '一年的工作天數
Const NumOfObs = 21    '歷史收盤價樣本數
Const IndexNum = NumOfObs - 1 

Public Sub CalHisVol()
    Dim ClosePrice(IndexNum) As Double  '價格陣列
    Dim LogReturn(IndexNum) As Double   '報酬陣列
    Dim Sum As Double, Average As Double
    Dim Variance As Double
    Dim VolDay As Double, VolYear As Double
    Dim i As Integer
   
    For i = 0 To IndexNum  '讀取工作表上的價格
        ClosePrice(i) = Worksheets("History Vol"). _
            Cells(i + 4, 1).value
    Next i

    For i = 1 To IndexNum  '算出相對應的報酬率 陣列
        LogReturn(i) = Log(ClosePrice(i) _
            / ClosePrice(i - 1))
    Next i

    Sum = 0   '算出對數報酬的平均數
    For i = 1 To IndexNum
        Sum = Sum + LogReturn(i)
    Next i
    Average = Sum / (NumOfObs - 1)
   
    Sum = 0
    For i = 1 To 20
        Sum = Sum + (LogReturn(i) - Average) ^ 2
    Next i
    Variance = Sum / (NumOfObs - 2) 算出變異數 (n-1
    VolDay = Sqr(Variance)  算出stdard deviation=voltality
    VolYear = VolDay * Sqr(DayOfYear)  算出一年的voltality 波動率
   
    MsgBox "Daily Volatility is " + Str(VolDay)
    MsgBox "Annual Volatility is " + Str(VolYear)
End Sub

--------------------------------------------
如果你發現一買權的隱含波動性為30%,但是你認為該股價的波動性是60%。 則
對你而言,該買權被低估了。你可以買入此買權。如果你的見解是正確的,則相對於所承擔的風險,你應該可以賺得超額的報酬。


---------------------------------------------


---------------------------------------------
參考資料來源
財務工程與Excel VBA的應用,證券暨期貨市場發展基金會
作者:董夢雲博士


----------------------------------------------
frm key concept

*add on capital is used to protect against future exposure

*AAA Derivatives subsidiary" are established to secure credit ratings that exceed those of their parent company.

*The Credit Portfolio View (McKinsey) model conditions the default rate on the state of the economy

*creditRisk+: default rate and its votality,minimal data inputs(frequncy of drfault event,loss fiven default, distribution of default loss for a portofolio)

*creditMetrics: the credit ratting migrations metrics

*KMV: Value of Asset-Value of Liability/Standard Deviation of Assets = default distance

100-80/10=2 -2 = 2.3% EDF =0.023

*credit exposure: certificate of deposit>closer exchange currency swap> currency swap> interest rate swap

*Cumulative default rates for B-rated corporate bonds: 5% after 1 year and 30% after 10 years

*total return swap: credit derivatives that enhance investor returns in the case of default, TRS requires payments based on changes in market valuation of an instrument, bank tranfer the total obligation(interest risk & credit risk) to the investor

*credit default swap: credit derivatives that enhance investor returns in the case of default, only transfer the credit risk to the investor

*first-to-default put(basket default swap):Compensates the buyer in case one of the assets in a specified pool defaults, valued by default probability and correlations as there is no payout for multiple defaults

*delta: instant risk, equity delta = 1, buy call and put is long gamma neutral delta stratege, call option >0 and <1, put option <0 and >-1, if the delta of a call option is 0.2, then when the equity increase 1 dollar, then the call option will increase 0.2 dollar.

由於Call Delta=0.6242,因此資產價格上漲$1元,買權上漲$1x0.6242=$0.6242

*gamma: short term risk, buy option with fix strike price= long gamma, long gamma expect voltality, is greatest when the at-the-money opion goes near the maturity


'當買權的Theta為-8.1085,隨到期日接近一日,買權下跌1/365*-8.1085=-$0.022215

*Theta: short term risk, time value, when the maturity date come closer one day, the time value decrease percentage. So the closer to maturity the bigger theta value, so the decrease volume is greater.

'當Vega 為36.7810,波動性上漲1%,買權上漲0.01x36.7810=$0.367810
*vega: long term risk.
represent the sensitivity of option price toward the voltality of commodity price
if you buy option, your vega is positive, that means you wish voltality from the market. if you sell option, your vega is negative, that means you wish the market stability. hedger always short vega. speculator always long vega.


*Rho: the mapping votality of the option value within riskless interest rate.
當Rho_r 為47.991,利率上漲1%,買權上漲0.01 x 47.9971=$0.479971


*beta:A beta of more than one indicates the stock has higher volatility than the index (or composite) and a beta of one indicates volatility equivalent to the index (or composite). For example, the price of a stock with a beta of 1.5 will change by 1.5% if the index value changes by 1%. Typically, the S&P500 index is used in calculating the beta of a stock.



*exposure: equity value * beta

*the option value needed buy for to hedge: exposure / delta of the option (option ™JŠi&Igrave;“&reg;›”œ&auml;—&brvbar;)

*no netting exposure: gross - net

*credit-spread option: credit spread option payout=notional * (strike price - expired price) /1000

*confid = 95 and tailed = 1 , z = 1.645
confid = 95 and tailed = 2 , z = 1.96
confid = 99 and tailed = 1 , z = 2.326
confid = 99 and tailed = 2 , z = 2.576

*RAPM asset value =500m standard variaton=15% confidence level=95%=1.645 (one tail)
(risk adjusted performance measurement)

var=500*15%*1.645=123.3750m
profit=25

RAPM =profit/var= 25/123.275 =20%

*voltality=standard diviation=square root of (variance)=sigma

*The yield curve is a line graph that plots the relationship between yields to maturity and time to maturity for bonds of the same asset class and credit quality.

year (x) yield (y)

*Basel II: Capital Requirement for Market Risk: the average VaR over the previous sixty business days, multiplied by a factor of at least 3.

*reverse floater: a combination of a bought Fixed rate bond and receiving fixed on an Interest Rate Swap: + 2 times fixed, - 1 times floating

*US Treasury Bill:

*deviation = x - mean of x




*3-month US Treasury Bill 美國國庫券               

*ABS(資產支援證券) Assets-backed security 

*American option backward recursion 

*Annualized Return 年度累積表現 

*ask price 賣出價 

*Back Test 回朔測試 

*Basel II Pillar I: 最低資本: 定義適足資本對銀行風險性資產最低比例原則 

*Basel II Pillar II: 監理檢視:要求監理機關對銀行適足資本計提與資本分配是否符合相關標準進行質量的評估,並做必要的早期干預 

*Basel II Pillar III: 市場紀律:規定資訊公開揭露條件,已促進市場紀律 

*Basel II: 8% BIS capital charge(100% weight) publicly traded stock,U.S. government bond (不算),  venture capital fund (算) 

*Basel II: Credit Risk : IRB **依銀行內建違約率(Probabillty of default )及監理機關公佈之其餘參數,包括:違約損失率(loss given default )、違約曝險金額(exPosure at default )及期問(maturity )等所評定之信用評等訂定信用風險權數
**number for red zone about credit risk = 200
 
*Basel II: Credit Risk : Standard  依據外部信用評等訂定信用風險權數(ECAI) 

*Basel II: Credit Risk:   

*Basel II: Credit Risk: AIRB 依據銀行內建所有參數,所評定之信用評等訂定信用風險權數 

*Basel II: Credit Risk: IRB/AIRB 信用風險內部模型法,一般評等法僅計算違約率,進階評等法計算違約率及預期損失率 

*Basel II: Credit Risk: Standard Model 標準法 

*Basel II: Market Risk: internal model: IMA *需以前10 個交易日(或2 個星期)為基礎計算VAR

*實務上允許計算1 個交易日的VAR,再乘上天數平方根

*99%的信賴區間- 至少需要1 年以上的歷史資料,且需每季更新資料

*1 個交易日的VAR*天數平方根* factor 3+risk surcharge

*Capital Requirement for Market Risk: the average VaR over the previous sixty business days, multiplied by a factor of at least 3.

*red zone exception number about market risk =10

 
*Basel II: Market Risk: internal model:IMA  ensure involvement of senior management conduct independen review of the trading units
conduct comprehensive stress testing of the portfolio with back testing
 
*Basel II: Market Risk: Regulatory Model:Standard Model:BIS *對不同的資產及風險,給予不同的風險權數,除Interest rate risk依區間計算(0.02-15%)及Commodity Risk 為乘於15%以外,其餘(foreign exchange,equity)皆是*0.08

*以loan為例:銀行向別人借Libor -10bps,借給別人Libor+25bps, 來算annualized return (0.0025+0.001)/0.08=0.04375=4.375

*General risk charge:reflect the product of the modified duration and interest rate shock anticipated for each maturity 如: swap,option

*Specific risk charge:measure the risk of a decline in the liquidity of the trading portfolio over the holding period 如: debt security future contract
&Disallowance factors:long and short position in different maturity zone are not offset perfectly (basis risk)

 
*Basel II: Operational Risk: Advanced Measurement Approaches 作業風險所引起之損失紀錄為基礎,經分析後,作為計算作業 

*Basel II: Operational Risk: Basic Indicator Approach 前三年度中,年營業毛利 (an-nual gross income)為正值(positive)的部分乘上固定比例 (α=15%),再取其平均值來計提資本 

*Basel II: Operational Risk: Standardised Approach 銀行所有業務活動劃分為八大類:企業財務規劃、財務交易與銷售、消費金融、商業金融、收付清算、代理業務、資產管理、消費經紀等,再就銀行各項業務別,設定計提指標後,按固定之資本計提權數(p 值)提列 

*Basel II: Specific risk charge: debt security future contract 

*Basel II:Captital Charge for a Loan unfunded commitement = (funded commitment ,OECD loan) *1/2 * 20%(risk weight)*0.08  eg:100*1/2*0.2*0.08 

*Basel II:credit risk charge Tier 1 capital(at least 50%)+tier 2 capital>=credit risk charge 

*Basel II:market risk charge tier 1Capital +tier3 capital(not access 2.5 times of tier1 capital) >= market risk charge 

*Basel II:Off balance sheet risk charge

      1.Instruments that substitue for loan(guarantees, bankers acceptance 100% weight
« 上次編輯: 2011-08-04 03:28 由 小徒兒 »

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 622
    • 檢視個人資料
有關統計,列印
« 回覆 #1 於: 2006-04-24 15:48 »
***計算兩時間相差小時
=(A1-A2)*24


***datediff (vba) 計算兩時間相差天
Dim TheDate As Date   
Dim Msg

Msg = "Days from today: " & DateDiff("d", "2007/04/02", CDate(txtStartDate))
MsgBox Msg


***將儲存為文字的日期轉換成日期
當排序錯誤

代碼: [選擇]
Sub macro1()
For i = 1 To 20
mystr = Format(Sheet2.Cells(i, 2), "mm/dd/yyyy")
'Sheet2.Cells(i, 3) = DateValue(mystr)
Sheet2.Cells(i, 3) = mystr
Next i
End Sub



選取列印範圍
檔案/設定列印範圍,標題


你知道如果excel中有一表格很長,如何能在列印 each pag都能看到他的表頭

1.在菜单“视图”下选择"檢視" “页眉与页脚”

2.打开“页眉与页脚”---选择“工作表”----“顶端标题行”點兩下,如$1:$1 选中你要固定列印的内容,点击“确定”




***binomial distribution 二項分布
is discrete
二項分布是離散型機率模型中最有名的一個

***poisson distribution
discrete
Poisson 分布,它可以看成為二項分布的一種極限情形。

***discrete probability distribution 離散型機率模型


1.DV01(“Dollar Value of an 01”):DV01 measures the dollar change in the value of a security for a basis point change in interest rate.(金證照講義p.20) 到期日愈長, 債券價格對殖利率之敏感度愈強.
     







***
variance=Var(X)=sum(可能值^2 *機率) - (平均數)^2


*KMV: Value of Asset-Value of Liability/Standard Deviation of Assets = default distance

mean 80, standard deviation =80
100-80/10=2 -2 = 2.3% EDF =0.023

單尾

99%  confidence of standard deviation =2.326
95%  confidence of standard deviation =1.645

雙尾
99%  observation falls withing 2.58
95% observation falls within 1.965
68%  distance of standard deviation =1 (34|34)


***poisson distribution


***殖利率 百元價 債券價格換算

2006/2/2   發行日
2006/5/19   計算日
2007/2/2   下次複習日
259    下次複習日-計算日
0.02263   加權平均殖利率
   
2012/2/2   50000000   44002889.63   面額   cash flow折現值

         88.00577926         百元價: cash flow折現值除以面額

   到期現金(50000000)   
----------------------------------------------------------------
                折現分母: power((1+r),剩餘年+剩餘日/365)   



***在作業完成之前超過逾時等待的時間
請設定 SqlCommand 的 CommandTimeout 屬性 (預設是 30 秒),
把它設成 0 ,表示無限。

如果還不行,
請設定 SqlConnection 連線字串 (ConnectionString) 的 ConnectionTimeout 屬性 (預設是 15 秒),
把它設成 0 ,表示無限。

connectionstring= "Driver={SQL Server};Server=172.16.200.210;Database=VaR;UID=kk;PWD=kk;Connection Timeout = 0"

commandADOA.CommandTimeout = 0


 
資料來源: 聖哥的資訊站
http://teacher.allok.com.tw/viewtopic.mspx?t=1944&highlight=%B9O%AE%C9


****
ActiveSheet.UsedRange.Rows.Count


****找出重複的資料
=IF(B6=B5,"****",B5)

***得出T3分配的截距,樣本數201-1,自由度200,信心水準0.01 -->機率0.02
=TINV(0.02,200)

*** '99% 信賴區間之反傳值
Application.NormSInv(0.99)

***Correlation corr(X1,X2)^2=Cov(X1,X2)/variance of X1 * variance of X2
***correlation corr(X,Y) = Cov(X,Y)/standard deviation of X * standard deviation of Y


***hedge ratio = correlation (S,F) * standard deviation of S/ standard deviation of F

** Beta hedge ratio 現貨期貨避險: 現貨組合總值 * beta值 / 單口期貨合約價值
。以2,000萬元的股票市值,假設該現貨組合之beta值為1.2,而目前12月台指期約交易價格為5000點,換言之,單口期貨合約價值100萬元(台指期貨合約規格為每點200元),依照前述公式計算,該投資人欲百分之百避險的期貨口數應為放空24口。


***duration hedge ratio
number of hedge=-(bond 1 price *bond 1duration )/(bond 2 price * bond 2 duration)

 n = -(B1D1)/(B2D2)


***調整chart位置
Sub alignChart()
Dim shapes1 As Shapes
Set shapes1 = ActiveSheet.Shapes
For i = 1 To shapes1.Count

If shapes1(i).Type = msoChart Then

   shapes1(i).Left = 670
   shapes1(i).Top = 720.75

End If
Next i

End Sub
« 上次編輯: 2008-06-02 13:40 由 小徒兒 »

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 622
    • 檢視個人資料
[分享]張松允 MACD 現貨模型 vba & RSI
« 回覆 #2 於: 2007-12-06 17:25 »
代碼: [選擇]
規則:

***MCAD:
DIF向下跌破DEM與零軸線 空單                       ex:2007/12/6 11:55
DIF 向上突破,在零軸線下突破,空頭平倉   ex:2007/12/6 1:35
在零軸線上交叉,多頭平倉                             ex:2007/12/7 10:15
DIF 向上突破DEM與零軸線時,為買進訊號



代碼: [選擇]
MACD 可以解釋為:

以兩條長短不一的移動平均線開口大小與開口大小的平均值及是否交叉做為進出交易依據。

 

MACD 最敏感的交易方式為:

MACD  由負轉正 è  回補空單並且買進多單。差離值由下向上穿越差離平均值。

MACD  由正轉負 è  多單平倉並且空單進場。差離值由下向上穿越差離平均值。

當『差離值的絕對值』即『柱狀圖』開始縮小,代表兩條移動平均線開始靠近。

單純以柱狀圖的轉正與轉負,做為交易進出依據。

 

MACD 中等敏感的交易方式為:

DIF 向上突破DEM與零軸線時,為買進訊號

DIF 在零軸線上,向下突破DEM,為多頭平倉 è MACD  由正轉負

DIF向下跌破DEM與零軸線時,為賣出訊號

DIF 在零軸線下,向上突破DEM,為空頭平倉 è MACD  由負轉正

 

兩線交叉 è 開倉

『開口大小』小於『開口大小的平均值』è 平倉

 

 source from 元大期貨  高雄分公司
副理  黎亞光

 


代碼: [選擇]


Sub Macro1()



For i = 0 To 200

If ((Sheet5.Cells(47 + i, 5).Value > Sheet5.Cells(47 + i, 6).Value) And (Sheet5.Cells(47 + i, 6).Value > 0) And (Sheet5.Cells(47 + i, 5).Value > 0) And (Sheet5.Cells(47 + i, 7).Value > 0)) Then
Sheet5.Cells(47 + i, 8).Value = "多頭買進"

       
Sheet5.Cells(47 + i, 8).Interior.ColorIndex = 3
Sheet5.Cells(47 + i, 8).Interior.Pattern = xlSolid
Sheet5.Cells(47 + i, 8).Font.ColorIndex = 2


End If


If (Sheet5.Cells(47 + i, 7).Value < 0) Then
Sheet5.Cells(47 + i, 8).Value = "多頭平倉"
       
Sheet5.Cells(47 + i, 8).Interior.ColorIndex = 5
Sheet5.Cells(47 + i, 8).Interior.Pattern = xlSolid
Sheet5.Cells(47 + i, 8).Font.ColorIndex = 2


End If


If ((Sheet5.Cells(47 + i, 5).Value < 0) And (Sheet5.Cells(47 + i, 6).Value < 0) And (Sheet5.Cells(47 + i, 7).Value < 0)) Then
Sheet5.Cells(47 + i, 8).Value = "空單"
       
Sheet5.Cells(47 + i, 8).Interior.ColorIndex = 43
Sheet5.Cells(47 + i, 8).Interior.Pattern = xlSolid
Sheet5.Cells(47 + i, 8).Font.ColorIndex = 1


End If


If ((Sheet5.Cells(47 + i, 5).Value < 0) And (Sheet5.Cells(47 + i, 6).Value < 0) And (Sheet5.Cells(47 + i, 7).Value > 0)) Then
Sheet5.Cells(47 + i, 8).Value = "空頭平倉"
       
Sheet5.Cells(47 + i, 8).Interior.ColorIndex = 5
Sheet5.Cells(47 + i, 8).Interior.Pattern = xlSolid
Sheet5.Cells(47 + i, 8).Font.ColorIndex = 1


End If


Next i

End Sub



***RSI
頭背
4R >80 價格創新高,RSI未創高: 空 多單平倉      --> 價格高,但波動較小,表示上漲出現疲態

4R <20 下20,價格創新低,RSI未創新低,多單   --> 價格低,但波動續高,表示出現上漲跡象

RSI=sum(成交價變化)+sum(成交價取決對值)/2/sum(成交價取決對值)

取損益與波動(損益的絕對值) 為參數.





<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
 #,##0.00_);(#,##0.00);0.00;"sales "@
$0.00" Surplus";$-0.00" Shortage"
[<=100];[>100]

星期   Sun–Sat   ddd
星期   Sunday–Saturday   dddd

« 上次編輯: 2008-07-31 12:06 由 小徒兒 »