Excel VBA抓取股價資料

Excel VBA 網路資料蒐集 完整教學:

https://raymondchiendtrt.blogspot.com/2018/08/vba_22.html

有位網友提供了一個連結:
http://webrtqt.fortunengine.com.tw/rtdata/k-chart/day-2330.txt?ts=1526775289018

裡面內容

其實這也是抓資料常會碰到的格式
但既然資料在哪裡已經知道了
接下來就是只要簡單的資料整理就好了



Sub 股價資料()

stockno = InputBox("輸入股票代號")

Dim t: t = Timer

Cells.Clear

Dim myXML As Object
Set myXML = CreateObject("Microsoft.XMLHTTP")

ReDim myArr(1 To 520, 1 To 20)

With myXML
    .Open "GET", "http://webrtqt.fortunengine.com.tw/rtdata/k-chart/day-" & stockno & ".txt?ts=1526775289018", False
    .send
    myText = .responseText
End With

myText1 = Split(myText, Chr(10))


For i = 0 To UBound(myText1)
    myText2s = Split(myText1(i), ",")
    j = 1
    For Each myText2 In myText2s
        myArr(i + 1, j) = myText2
        j = j + 1
    Next
Next

[A4].Resize(520, 20).Value = myArr

Set myXML = Nothing
Erase myArr
Debug.Print Format(Timer - t, "0.00秒")

End Sub