|
Sub HomeWork1_1()
'新手:DongYu 优化:NeemxYang
'作业:1、网站:http://data.bank.hexun.com/lccp/jrxp.aspx
'操作:点击“今日在售产品”,获取今日在售产品第一页的数据。
'工具-引用 MSXML 3.0-6.0
Dim xml As New MSXML2.XMLHTTP, url As String, St As String
Dim arr, brr, ar, i, c, j, k
With ActiveSheet
.[a1].Resize(1, 10) = [{"编号","产品名称","银行","起售日","停售日","币种","管理期(月)","产品类型","预期收益(%)","收益"}]
End With
For j = 1 To 44
url = "http://data.bank.hexun.com/lccp/Jrxp.aspx?col=1&tag=desc&date=" & Date & "&page=" & j '修改了日期起止时间为今日和分页循环(今天是44页)
With xml
.Open "GET", url, False
.send
St = .responseText
End With
St = Split(Split(St, "<div class=""mark"">")(1), "</div>")(0)
arr = Split(St, "<tr align='center'>")
ReDim brr(1 To UBound(arr), 1 To 9)
For i = 1 To UBound(arr)
ar = arr(i)
brr(i, 1) = Split(Split(ar, "value='")(1), "'")(0) + Split(Split(ar, "<font class='cred'>")(1), "</font>")(0)
brr(i, 2) = Split(Split(ar, "</font></td><td class='hl'>")(1), "</td>")(0)
brr(i, 3) = Split(Split(ar, "<td class='on'>")(1), "</td>")(0)
brr(i, 4) = Split(Split(ar, "<td class='hl'>")(1), "</td>")(0)
brr(i, 5) = Split(Split(ar, "<td class='hl'>")(2), "</td>")(0)
brr(i, 6) = Split(Split(ar, "<td class='hl'>")(3), "</td>")(0)
brr(i, 7) = Split(Split(ar, "<td class='hl'>")(4), "</td>")(0)
brr(i, 8) = Split(Split(ar, "<td class='hl'>")(5), "</td>")(0)
brr(i, 9) = Split(Split(Split(ar, "<td class='hl'>")(5), "</td>")(1), ">")(1)
Next i
With ActiveSheet
'.Cells.Clear
k = [b63356].End(3).Row
.Columns("D:E").NumberFormatLocal = "yyyy-m-d"
.Range("b" & k + 1).Resize(UBound(brr, 1), 9) = brr
With .Cells '优化了单元格的自动调整及水平和垂直对齐
.Rows.AutoFit
.Columns.AutoFit
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
End With
Next j
ActiveSheet.[a:j].Sort key1:=[i2], order1:=xlDescending '增加了对收益排序 降序排序
End Sub |
|