Excel VBA 抓取三大法人買賣超日報(搭配Javascript)

看到一位網友提問，希望抓取證交所三大法人買賣超日報
於是便自己先抓抓看
找到資料源後，發現資料是以JSON呈現，而且還是比較難處理的格式
想到很久以前有學著使用Javascript來處理這種格式，因此今天來複習一下



程式碼

	Sub getContent()
	Dim t: t = Timer
	Cells.Clear
	Dim myXML As Object
	Set myXML = CreateObject("Microsoft.XMLHTTP")
	Dim myHTML As Object
	Set myHTML = CreateObject("HTMLFile")
	Set oWindow = myHTML.parentWindow
	With myXML
	.Open "GET", "http://www.twse.com.tw/fund/T86?response=json&date=20180816&selectType=11", False
	.send
	'Debug.Print .responseText
	oWindow.execScript "var a=" & .responseText & ";"
	arrayLength = oWindow.eval("a.fields.length")
	dataLength = oWindow.eval("a.data.length")
	For i = 0 To arrayLength - 1
	Cells(1, i + 1) = oWindow.eval("a.fields[" & i & "]")
	For j = 0 To dataLength - 1
	Cells(j + 2, i + 1) = oWindow.eval("a.data[" & j & "][" & i & "]")
	Next
	Next
	End With
	Set myXML = Nothing
	Set myHTML = Nothing
	Debug.Print Format(Timer - t, "0.00秒")
	End Sub

view raw Excel VBA 抓取三大法人買賣超日報(使用Javascript) hosted with ❤ by GitHub