运用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数据如下：

可以看出，网络数据抓取项目的设计一定要有较强兼容性，一旦数据服务地址或网页内容发生变动，项目代码可以做出快速调整，避免耽误所在单位或公司业务。