1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| Sub strikevol()
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Users\Siham\Desktop\function.mdb")
Set rs = db.OpenRecordset("siham1", dbOpenTable)
For r = 3 To 14
With rs
.AddNew
.fields("10") = Range("B" & r).Value
.fields("15") = Range("C" & r).Value
.fields("20") = Range("D" & r).Value
.fields("25") = Range("E" & r).Value
.fields("30") = Range("F" & r).Value
.fields("35") = Range("G" & r).Value
.fields("40") = Range("H" & r).Value
.Update
End With
Dim taux_domestique As Double
Dim taux_etranger As Double
Dim r As Integer
Dim i As Integer
Dim j As Integer
Dim duree(12) As Double
Dim volatilite(12, 7) As Double
CP = "call"
Spot = 1.4449
i = 1
Dim ObjCell1 As Range
For Each ObjCell In Range("A3:A14").Cells
duree(i) = ObjCell.Value
i = i + 1
Next
Dim ObjCell3 As Range
i = 1
j = 1
For Each ObjCell3 In Range("A3:H14").Cells
If j <= 12 And i <= 12 Then
volatilite(i, j) = ObjCell3.Value
End If
If j > 12 And i <= 12 Then
j = 1
i = i + 1
End If
Next
For i = 1 To 12
For j = 1 To 7
taux_domestique = InterpoleTx(Range("A17:A26"), Range("B17:B26"), duree(i)) And taux_etranger = InterpoleTx(Range("A29:A42"), Range("C29:C42"), duree(i))
Range("B" & r).Value = Spot * Exp(((taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))) * (duree(i) / 365)) - (volatilite(i, j) * Sqr(2 * (duree(i) / 365) * Log(1 / (0.1 * Sqr(2 * 22 / 7))))))
Range("C" & r).Value = Spot * Exp(((taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))) * (duree(i) / 365)) - (volatilite(i, j) * Sqr(2 * (duree(i) / 365) * Log(1 / (0.15 * Sqr(2 * 22 / 7))))))
Range("D" & r).Value = Spot * Exp(((taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))) * (duree(i) / 365)) - (volatilite(i, j) * Sqr(2 * (duree(i) / 365) * Log(1 / (0.2 * Sqr(2 * 22 / 7))))))
Range("E" & r).Value = Spot * Exp(((taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))) * (duree(i) / 365)) - (volatilite(i, j) * Sqr(2 * (duree(i) / 365) * Log(1 / (0.25 * Sqr(2 * 22 / 7))))))
Range("F" & r).Value = Spot * Exp(((taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))) * (duree(i) / 365)) - (volatilite(i, j) * Sqr(2 * (duree(i) / 365) * Log(1 / (0.3 * Sqr(2 * 22 / 7))))))
Range("G" & r).Value = Spot * Exp(((taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))) * (duree(i) / 365)) - (volatilite(i, j) * Sqr(2 * (duree(i) / 365) * Log(1 / (0.35 * Sqr(2 * 22 / 7))))))
Range("H" & r).Value = Spot * Exp(((taux_domestique - taux_etranger + (0.5 * volatilite(i, j) * volatilite(i, j))) * (duree(i) / 365)) - (volatilite(i, j) * Sqr(2 * (duree(i) / 365) * Log(1 / (0.4 * Sqr(2 * 22 / 7))))))
Next j
Next i
Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub |
Partager