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 |
