干货全拿走-用Excel获取上证指数等50+股指行情及历史数据

一、前言

二、实现思路

三、核心代码

四、注意事项

一、前言

大数据时代，数据分析的价值愈发凸显，数据对于金融市场亦如是。现在越来越多的金融机构和个人借助专业的软件去做数据分析和获取数据，但是显然对于普通人来说，一是学习门槛高，二是年费高。普通人可能做股票，根本不会分析也不会有数据支撑，极少数的人有自己的策略和理论，大多数人都成了韭菜。普通人接触最多的数据分析处理的工具就是EXCEL，而用excel去支撑普通人一般对于股票数据分析的需求，就是我做这个系列的初衷，也是将我原来自己用的一些东西分享出来，发挥更大的价值吧。自己有一部分是用excel做的网抓，另外一部分是算法和模型，这次分享的就是网抓股指数据的部分了。

Excel实现网页抓取（或者叫爬虫），实际上是依托的VBA语言，这个是以excel（或其余的office，如word，ppt等）为宿主的语言，脱离了excel就无法运行，但是其优点在于不需要额外安装什么软件和环境，直接用excel就可以运行，所以对于普通人非常方便友好。这也是我为什么用VBA的原因，虽然这个语言已经很老了，库也不丰富，能实现的功能远不如其余语言，但是很多人往往高估了自己对于功能的需求，我感觉对于普通人来说，可能真的用到的都只是基本的功能。

就网抓而言的话，Excel VBA可以实现在网站反爬虫机制不太高级的情况下的爬虫，简单地说，就是没有人机验证（图片识别、验证码识别等）的情况下。（一般请求头、javascript触发，IP地址反爬这几种还能处理，但是后两者对于VBA技术以及一些网抓衍生知识的要求很高，学习成本比较高）。所以，效率最高的网抓方案就是选择一个比较好抓的网页，而不是绞尽脑汁想用VBA去饶过他。说实话，绕过反爬虫的程序使用VBA来写的话，真的是现代社会钻木取火，它的上限摆在这，这种高级功能还不如用python。

二、实现思路

（1）找数据源。这个是最关键的一步，没有数据源一切都白扯。“巧妇难为无米之炊”。之前一直想下载股指方面的数据，感觉能提供股指历史数据的公开网站是不少，但是很多是需要登录验证，或者只能会员才能查看指定信息，能免登录直接用于VBA网抓的并不多。最后终于找到了一个，这里附上链接

https://q.stock.sohu.com/cn/zs.shtml（股指行情）

https://q.stock.sohu.com/zs/000001/lshq.shtml（股指历史数据）

这里面包括了将近60个股指（都是国内的），A股分析够用了。部分截图展示下：

（2）分析网页数据请求头。这一步简单的来说，就是看网页上的数据，是用什么方式生成的，涉及到网页分析的部分，这里不做重点解说，可以看到股指的历史数据这里是GET请求，请求头的验证信息里也没有特殊的，应该是没有设反爬虫的验证，符合我们用VBA抓取的条件。

（3）用VBA代码模拟请求，先检查下看看数据能否正常返回。

（4）如果数据可以正常返回，就继续把其余配套代码写完整，并结合工作表的结构，涉及数据存储的位置和交互方式，让用户可以直观使用。我这边设计了3个工作表，分别存储行情、历史数据以及图表分析，最后表头设置下，按钮添加一下。

这一步的话，因为历史数据下载涉及到批量下载和自定义日期，所以比行情复杂一些，做了一些交互（下图蓝色部分的信息需要用户输入）

三、核心代码

先看下最后完成的效果，我上传了视频，可以直接观看：

https://www.bilibili.com/video/BV1Xe4y1h7U2/

这里分享股指历史数据部分的网抓及正则表达式部分的代码，分别用于网抓请求和数据清洗，正则表达式已经现成写好，可以直接用（注意VBA中的英文引号需要打两个，所以在正则表达式中会看到””而不是“）

URL = "https://q.stock.sohu.com/hisHq?code=zs_" & StockInput & "&start=" & DateStart & "&end=" & DateEnd & "&stat=1&order=D&period=d&callback=historySearchHandler&rt=jsonp"

With CreateObject("Microsoft.XMLHTTP")

.Open "GET", URL, False

.send

Do While .ReadyState <> 4

If Timer - TimeEach > 10 Then

MsgBox "访问超时，请检查以下链接正确性：" & URL

Exit Function

End If

DoEvents

Loop

mystr = .responsetext

End With

Dim Result As Object

Set RegExpression = CreateObject("VBScript.RegExp")

With RegExpression

.ignorecase = True

.Global = True

.Pattern = "\[""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)"",""([\s\S]+?)""\]"

Set Result = .Execute(mystr)

End With

对于股指行情的部分，采用了DOM文件的形式去处理网页数据，没有采用正则表达式匹配。主要是当时看行情页只有一个表格，结构很不错，可以直接用DOM文件，提取Table标签出来，速度也会更快些。核心代码如下：

Set objIE = CreateObject("InternetExplorer.Application")

With objIE

.Visible = False

.Navigate "https://q.stock.sohu.com/cn/zs.shtml"

Do Until .ReadyState = 4

DoEvents

Loop

Set objIEDOM = .document

End With