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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121
| Sub recupereDonneesCBOE()
Dim oWbk As Excel.Workbook
Dim v1 As Variant, vT As Variant, donneesFinales As Variant
Dim monthStudy As Long, yearStudy As Long, i As Long, i1 As Long, iK As Long, lastiK As Long, iT As Long, j As Integer, k As Integer
Dim oSh As Excel.Worksheet, oRng As Excel.Range
Const csSheetName As String = "CBOE"
Dim beginPrint As Boolean
Dim monthToLetter(): monthToLetter = Array("F", "G", "H", "J", "K", "M", "N", "Q", "U", "V", "X", "Z")
Application.StatusBar = "Begin download..."
'Données VIX Index
On Error GoTo noMore
Application.StatusBar = "Downloading VIX Index"
Set oWbk = Application.Workbooks.Open("http://ichart.finance.yahoo.com/table.csv?s=%5EVIX&a=0&b=1&c=2000&d=0&e=0&f=" & year(Now()) + 1 & "&g=d&ignore=.csv")
v1 = oWbk.Worksheets(1).UsedRange.Value
oWbk.Close False
ReDim donneesFinales(1 To UBound(v1, 1), 1 To 10)
donneesFinales(1, 1) = "Dates"
donneesFinales(1, 2) = "Spot"
For i = 1 To 7
donneesFinales(1, i + 2) = i
Next i
donneesFinales(1, 10) = "Roll"
For i = 2 To UBound(v1, 1)
donneesFinales(i, 1) = CDate(v1(i, 1))
donneesFinales(i, 2) = v1(i, 7)
donneesFinales(i, 10) = False
Next i
v1 = Empty
'Données Futures VIX
On Error GoTo noMore
For j = 2006 To year(Now()) + 1
For k = 0 To 11
Application.StatusBar = "Downloading VIX Future : " & monthToLetter(k) & Format(DateSerial(j, 1, 1), "yy")
beginPrint = False
Set oWbk = Application.Workbooks.Open("http://cfe.cboe.com/Publish/ScheduledTask/MktData/datahouse/CFE_" & monthToLetter(k) & Format(DateSerial(j, 1, 1), "yy") & "_VX.csv")
v1 = oWbk.Worksheets(1).UsedRange.Value
oWbk.Close False
i1 = 2
iT = UBound(donneesFinales, 1)
'On cherche 2 dates égales
While (i1 <= UBound(v1, 1)) And (iT > 1)
While CDate(v1(i1, 1)) <> donneesFinales(iT, 1)
If CDate(v1(i1, 1)) < donneesFinales(iT, 1) Then
i1 = i1 + 1
Else
iT = iT - 1
'Si on a déjà commencé à inscrire des données et qu'on itére iT, alors il manque des données
If beginPrint = True Then
iK = 3
While (donneesFinales(iT, iK) <> "" And iK < 10)
iK = iK + 1
Wend
If iK < 10 Then
donneesFinales(iT, iK) = "#N/A N/A"
End If
End If
End If
Wend
'Dernier future à inscrire, limité à 9
iK = 3
While (donneesFinales(iT, iK) <> "" And iK < 10)
iK = iK + 1
Wend
If iK < 10 Then
If v1(i1, 7) <> 0 And v1(i1, 6) <> 0 Then 'Il y a eu trading pendant la journée
If beginPrint = False Then
beginPrint = True
ElseIf iK < lastiK Then
'Nous sommes ici dans le cas d'un roll de futures
donneesFinales(iT, 10) = True
End If
If donneesFinales(iT, 1) < CDate("26/03/2007") Then
donneesFinales(iT, iK) = v1(i1, 7) / 10
Else
donneesFinales(iT, iK) = v1(i1, 7)
End If
Else
If i1 < UBound(v1, 1) Then 'données manquante, sauf si expiry alors no data printed
donneesFinales(iT, iK) = "#N/A N/A"
End If
End If
lastiK = iK
End If
i1 = i1 + 1
iT = iT - 1
Wend
v1 = Empty
Next k
Next j
noMore:
'On copie maintenant nos données sur la feuille et on met en forme
With Worksheets(csSheetName)
.Activate
.UsedRange.Clear
.Range(Cells(1, 1), Cells(UBound(donneesFinales, 1), UBound(donneesFinales, 2))).Value = donneesFinales
.Rows("1:1").Font.Bold = True
.Rows("1:1").HorizontalAlignment = xlCenter
End With
End Sub |
Partager