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
| Private Sub CommandButton1_Click()
cell = Lastcell
'adjust the amount of new data you need here
For cell = Lastcell To Lastcell + 1
Insertdate = Searchdate(GetXMLMSCIWORLD, Getcorrectdate(cell))
Insertdate = Replace(Insertdate, " ", "")
Insertvalue = Searchvalue(GetXMLMSCIWORLD, Getcorrectdate(cell))
insertvalue3 = Searchvalue3(GetGoldINO, getcorrectdateINO(cell))
Cells(cell + 1, 1) = Insertdate
Cells(cell + 1, 2) = Insertvalue
Cells(cell + 1, 3) = insertvalue3
Next cell
End sub
'--------------EVERYTHING OF MSCI DATABASE--------------
Private Function Lastcell()
'EVERYTHING OF MSCI WORLD
Dim lRow As Long
lRow = ActiveWorkbook.Worksheets("DBDLY").Cells(Rows.Count, 1).End(xlUp).Row
Lastcell = lRow
End Function
Private Function GetXMLMSCIWORLD()
Dim objReq As WinHttp.WinHttpRequest
Set objReq = New WinHttp.WinHttpRequest
objReq.Option(WinHttpRequestOption_EnableRedirects) = True
objReq.Open "GET", "http://www.msci.com/webapp/indexperf/charts?baseValue=false&format=XML&frequency=D&endDate=03%20Nov%2C%202080&indices=106%2CC%2C36&scope=R&startDate=03%20Nov%2C%202013&site=gimi¤cy=15&priceLevel=41", False
objReq.setRequestHeader "Cookie", "abcd=cookie:containing:colons"
objReq.send
GetXMLMSCIWORLD = objReq.ResponseText
End Function
Private Function Getcorrectdate(lRow)
date1 = Format(ActiveWorkbook.Worksheets("DBDLY").Cells(lRow, 1).Value, "mm-dd-yyyy")
Dim datum As String
datum = Format(date1, "mm-dd-yyyy")
Getcorrectdate = Replace(datum, "-", "/")
End Function
Private Function Searchdate(GetXMLMSCIWORLD As String, Getcorrectdate As String)
Dim CorrectDate As String
CorrectDate = Getcorrectdate
TestArray = Split(GetXMLMSCIWORLD, CorrectDate)
Dim wy As String
wy = Replace(TestArray(1), "<date>", "")
wy = Replace(wy, "</date>", "")
wy = Replace(wy, "<value>", "")
wy = Replace(wy, "</value>", "")
wy = Replace(wy, "</asOf>", "")
Dim secondArray() As String
secondArray = Split(wy, "<asOf>")
Dim py As Variant
py = Split(secondArray(1), vbLf)
Dim SearchdateBijna As String
SearchdateBijna = Format(py(1), "mm-dd-yyyy")
Searchdate = Format(SearchdateBijna, "mm-dd-yy")
End Function
Private Function Searchvalue(GetXMLMSCIWORLD As String, Getcorrectdate As String)
Dim CorrectDate2 As String
CorrectDate2 = Getcorrectdate
TestArray2 = Split(GetXMLMSCIWORLD, CorrectDate2)
Dim zy As String
zy = Replace(TestArray2(1), "<date>", "")
zy = Replace(zy, "</date>", "")
zy = Replace(zy, "<value>", "")
zy = Replace(zy, "</value>", "")
zy = Replace(zy, "</asOf>", "")
Dim secondArray2() As String
secondArray2 = Split(zy, "<asOf>")
Dim xy As Variant
xy = Split(secondArray2(1), vbLf)
Searchvalue = xy(2)
End Function
'EVERITHING OF INO DATABASE
Private Function GetGoldINO()
'EVERYTHING OF GOLD
Dim objReq As WinHttp.WinHttpRequest
Set objReq = New WinHttp.WinHttpRequest
objReq.Option(WinHttpRequestOption_EnableRedirects) = True
objReq.Open "GET", "http://club.ino.com/quotes/data/?q=1&ticket=7b6cc6d8bbg06a08e074068078068&s=FOREX_XAUUSDO&w=d&f=csv", False
objReq.setRequestHeader "Cookie", "abcd=cookie:containing:colons"
objReq.send
GetGoldINO = objReq.ResponseText
End Function
Private Function Searchvalue3(GetGoldINO As String, getcorrectdateINO As String)
Dim Correctdate3 As String
Correctdate3 = Format(getcorrectdateINO, "yyyymmdd")
testarray3 = Split(GetGoldINO, Correctdate3)
Dim zy3 As String
zy3 = testarray3(1)
zy3 = Replace(testarray183(1), ",,", "")
zy3 = Replace(zy183, ",0,0", "")
Searchvalue3= zy3
End Function |
Requête HTTP : acquisition de données
Répondre avec citation
excuse Marc
: ça peut servir aux autres 
Partager