|
我嘗試用三種方式下載台灣八大公股行庫買超前30名的股票資料,分別用DOM標籤、XPath以及正則擷取節點資料,分享給大家。只是,用正則的方式,礙於初學正則,雖然能成功將資料寫入工作表,但總感覺不是很有效率。請有更好想法的朋友提供意見,謝謝。附件裡面的兩個檔案分別為整個網頁的原始檔(All.txt)以及經過處理的部份原始檔(Part.txt)。
Sub DownloadBroker8_RegExp()
Dim myStr As String
Dim mh, k
Dim Arr()
Dim i As Integer
Dim j As Integer
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", "https://histock.tw/stock/broker8.aspx?d=2020-01-30", False
.setrequestheader "Referer", "https://histock.tw/stock/broker8.aspx"
.send
myStr = .responseText
End With
myStr = Split("<ul class=""stock-list"">" & Split(myStr, "<ul class=""stock-list"">")(1), "</span></li></ul>")(0) & "</span></li></ul>"
myStr = Replace(myStr, " ", "")
With CreateObject("vbscript.regexp")
.Pattern = "(?=<span class=""w70"">|<span class=""w100 name"">)(.+?)</span>"
.Global = True
Set mh = .Execute(myStr)
End With
ReDim Arr(1 To 10, 1 To mh.Count / 10)
i = 1
For Each k In mh
If i Mod 10 = 1 Then
j = j + 1
i = 1
End If
Arr(i, j) = Replace(Replace(Replace(k, "<span class=""w70"">", ""), "<span class=""w100 name"">", ""), "</span>", "")
i = i + 1
Next
With ActiveSheet
.Cells.Clear
.Range("A1").Resize(1, 10) = Array("股票", "合庫", "土銀", "台銀", "台企銀", "彰銀", "第一金", "兆豐銀", "華南永昌", "合計(萬)")
.Range("A2").Resize(mh.Count / 10, 10) = Application.Transpose(Arr)
End With
End Sub
Sub DownloadBroker8_XPath()
Dim myStr As String
Dim i As Integer
Dim j As Integer
Dim Arr()
Dim Nodes
Dim Node
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", "https://histock.tw/stock/broker8.aspx?d=2020-01-30", False
.setrequestheader "Referer", "https://histock.tw/stock/broker8.aspx"
.send
myStr = .responseText
End With
myStr = Split("<ul class=""stock-list"">" & Split(myStr, "<ul class=""stock-list"">")(1), "</span></li></ul>")(0) & "</span></li></ul>"
myStr = Replace(myStr, " ", "")
myStr = Replace(myStr, "S&P", "SP")
myStr = "<?xml version=""1.0"" encoding=""big5""?>" & vbCrLf & myStr
With CreateObject("MSXML2.DOMDocument.6.0")
.LoadXML myStr
Set Nodes = .SelectNodes("//span[contains(@class,""w100 name"") or contains(@class,""w70"")]")
End With
ReDim Arr(1 To 10, 1 To Nodes.Length / 10)
i = 1
For Each Node In Nodes
If i Mod 10 = 1 Then
j = j + 1
i = 1
End If
Arr(i, j) = Node.Text
i = i + 1
Next Node
With ActiveSheet
.Cells.Clear
.Range("A1").Resize(1, 10) = Array("股票", "合庫", "土銀", "台銀", "台企銀", "彰銀", "第一金", "兆豐銀", "華南永昌", "合計(萬)")
.Range("A2").Resize(Nodes.Length / 10, 10) = Application.Transpose(Arr)
End With
End Sub
Sub DownloadBroker8_DOM()
Dim myStr As String
Dim StockList
Dim i As Integer
Dim j As Integer
Dim nRow As Integer
Dim nCol As Byte
Dim Arr()
With CreateObject("WinHttp.WinHttpRequest.5.1")
.Open "GET", "https://histock.tw/stock/broker8.aspx?d=2020-01-30", False
.setrequestheader "Referer", "https://histock.tw/stock/broker8.aspx"
.send
myStr = .responsetext
End With
With CreateObject("HtmlFile")
.write myStr
Set StockList = .All.tags("ul")
nRow = StockList(16).ChildNodes.Length
nCol = StockList(16).ChildNodes(0).ChildNodes.Length
ReDim Arr(0 To nRow, 0 To nCol)
For i = 0 To nRow - 1
For j = 0 To nCol - 1
Arr(i, j) = StockList(16).ChildNodes(i).ChildNodes(j).innertext
Next j
Next i
With ActiveSheet
.Cells.Clear
.Range("B1").Resize(1, 10) = Array("股票", "合庫", "土銀", "台銀", "台企銀", "彰銀", "第一金", "兆豐銀", "華南永昌", "合計(萬)")
.Range("A2").Resize(nRow, nCol) = Arr
End With
End With
End Sub
|
评分
-
1
查看全部评分
-
|