運用XmlHttp對象抓取yahoo股票曆史資料 - EXCEL VBA（25）

股票曆史資料是指過去一段時間内特定股票的交易資料，包括每日、每周或每月的開盤價、收盤價、最高價、最低價、成交量等。這些資料對于股票市場的研究、分析和決策具有重要的價值。

XmlHttp對象是一套可以在Javascript、VbScript、Jscript等腳本語言中通過http協定傳送或從接收XML及其他資料的一套API。XmlHttp最大的用處是可以更新網頁的部分内容而不需要重新整理整個頁面。

現在的絕大多數浏覽器都增加了對XmlHttp的支援，IE中使用ActiveXObject方式建立XmlHttp對象，其他浏覽器如：Firefox、Opera等通過window.XMLHttpRequest來建立xmlhttp對象。

1、yahoo股票曆史資料

yahoo提供國内外免費股票曆史資料Web Service API接口。例如：

I、英國富時100股票Antofagasta plc銅礦開采公司，股票代碼ANTO.L

https://query1.finance.yahoo.com/v7/finance/download/ANTO.L?period1=1580000000&period2=1700000000

II、中國深證成指指數，指數代碼399001.sz

https://query1.finance.yahoo.com/v7/finance/download/399001.sz?period1=1510000000&period2=1700000000

III、中國上證股票中國平安，股票代碼601318.ss

https://query1.finance.yahoo.com/v7/finance/download/601318.ss?period1=1510000000&period2=1700000000

位址中，“period1=1510000000&period2=1700000000”代表起始時間。1510000000和1700000000都是10位數，具體含義不清楚。設截止日期period2=1700000000已經足夠覆寫目前最新日期，起始日期period1=1510000000可以獲得1370天、4年多的資料。如果需要下載下傳更多天資料，适當按位數減小period1、反之增加period1。

在浏覽器中打開位址“https://query1.finance.yahoo.com/v7/finance/download/399001.sz?period1=1510000000&period2=1700000000”，彈出視窗如下：

圖中可以看出資料為EXCEL的CSV格式文本檔案。打開後資料顯示如下：

2、CSV資料抓取

如果一次下載下傳一隻股票的曆史資料，在浏覽器中打開資料服務接口位址下載下傳儲存到本地即可，但是要完成一次自動下載下傳多隻股票、并同時進行資料處理和計算（如計算10日均線、RSI強弱指數），運用XmlHttp對象是非常好的選擇。

I、建立股票池

在EXCEL表格（如，sheet1）中，按格式記錄感興趣的股票代碼與名稱，如圖：

II、建立窗體

（I）窗體設計

• WebBrowser1網頁控件：顯示股票K線圖形；
• TextBox1文本框：顯示在EXCEL表格中滑鼠標明的股票代碼；
• ComboBox1複合框：下拉清單選擇圖形；
• CommandButton1指令按鈕：抓取CSV資料，并寫入本地EXCEL表格。

（II）編寫代碼

• UserForm_Initialize窗體初始化代碼：

Private Sub UserForm_Initialize()
ComboBox1.AddItem "分時線"
ComboBox1.AddItem "日K線"
ComboBox1.AddItem "周K線"
ComboBox1.AddItem "月K線"
ComboBox1.ListIndex = 1
WebBrowser1.Navigate "http://image.sinajs.cn/newchart/daily/n/sh000001.gif"
End Sub           

打開窗體後，效果如下：

滑鼠點選sheet1中股票代碼，如圖，

• 滑鼠點選單元格事件代碼：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r, c
'獲得滑鼠點選單元格所在行
r = Target.Row
'獲得滑鼠點選單元格所在列
c = Target.Column
'調用圖形設定子過程
setYChart r, c
End Sub           

• 圖形設定子過程setYChart在子產品1，代碼如下：

Sub setYChart(r, c)
Dim s As Integer
Dim ID As String
If c = 1 And r > 1 Then ID = Sheet1.Cells(r, 1)
UserForm1.TextBox1 = ID
If Mid(ID, 8, 2) = "ss" Then
  ID = "sh" & Mid(ID, 1, 6)
Else
  ID = Mid(ID, 8, 2) & Mid(ID, 1, 6)
End If
s = UserForm1.ComboBox1.ListIndex
With UserForm1.WebBrowser1
    If s = 0 Then .Navigate "http://image.sinajs.cn/newchart/min/n/" & ID & ".gif"
    If s = 1 Then .Navigate "http://image.sinajs.cn/newchart/daily/n/" & ID & ".gif"
    If s = 2 Then .Navigate "http://image.sinajs.cn/newchart/weekly/n/" & ID & ".gif"
    If s = 3 Then .Navigate "http://image.sinajs.cn/newchart/monthly/n/" & ID & ".gif"
End With
End Sub           

滑鼠點選單元格效果如下圖：

• ComboBox1複合框下拉清單選項變化事件代碼：

Private Sub ComboBox1_Change()
Dim r, c
'擷取所在單元格所在行
r = ActiveCell.Row
'擷取所在單元格所在列
c = ActiveCell.Column
'調用圖形設定子過程
setYChart r, c
End Sub           

在下拉清單中選擇月K線，效果如下圖：

• CommandButton1【事件抓取】指令按鈕代碼：

Private Sub CommandButton1_Click()
Dim httpRequest As Object
Dim URL As String
Dim ID As Integer, txt As String, str As String, rows As Integer
Dim arrs As Variant, arr As Variant
Dim oRows As Integer, i As Integer, j As Integer
txt = TextBox1.Text
'建立XMLHTTP對象
Set httpRequest = CreateObject("MSXML2.XMLHTTP")
'按TextBox1文本框股票代碼拼寫位址
URL = "https://query1.finance.yahoo.com/v7/finance/download/" & txt & "?period1=1510000000&period2=1700000000"
'背景打開網址
httpRequest.Open "GET", URL, False
'定義下載下傳資料格式
httpRequest.setRequestHeader "Content-Type", "text/html"
'向位址伺服器發送請求
httpRequest.Send
'如果請求成功
If httpRequest.Status = 200 Then
'獲得下載下傳文本
  str = httpRequest.ResponseText
  Set httpRequest = Nothing
'按回車符将文本分割為數組
  arrs = Split(str, Chr(10))
'獲得數組總行數
  oRows = UBound(arrs)
  '寫下載下傳資料表頭
  Sheet2.Cells.Clear
  Sheet2.Cells(1, 1) = "Date"
  Sheet2.Cells(1, 2) = "Open"
  Sheet2.Cells(1, 3) = "High"
  Sheet2.Cells(1, 4) = "Low"
  Sheet2.Cells(1, 5) = "Close"
  Sheet2.Cells(1, 6) = "Adj Close"
  Sheet2.Cells(1, 7) = "Volume"
 '表頭設為黑體和紅色
  Sheets("sheet2").Select
  Range("A1:G1").Select
  Selection.Font.Bold = True
  With Selection.Font
    .Color = -16777024
    .TintAndShade = 0
  End With
  For i = 1 To oRows
  'CSV資料為每列用“,”号分割，這裡按行“,”将每行文本分割為數組
    arr = Split(arrs(i), ",")
    For j = 1 To 7
      Sheet2.Cells(i + 1, j) = arr(j - 1)
    Next j
  Next i
End If
End Sub           

運用XmlHttp對象抓取資料效率較高，可以一次性循環抓取、處理多隻股票資料。例如，一次抓取500隻股票、并同時計算出不同股票MACD、RSI、MA等名額，進而實作大資料選股和AI決策股票買賣。

需要注意的是股票資料服務接口并不是一成不變的。例如，騰訊的日線資料接口“https://data.gtimg.cn/flashdata/hushen/daily/22/sh000001.js”已經免費開放20多年，但2022年10月24日該位址資料服務終止。傳回的格式化資料如圖，

騰訊目前日線資料接口為“https://web.ifzq.gtimg.cn/appstock/app/fqkline/get?_var=r_sh000001¶m=sh000001,day,2000-01-01,2023-11-13,500,qfq”，傳回JSON資料如下：

可以看出，網絡資料抓取項目的設計一定要有較強相容性，一旦資料服務位址或網頁内容發生變動，項目代碼可以做出快速調整，避免耽誤所在機關或公司業務。