以Excel VBA实现对网页中Table的爬虫 --- Part 1



  • 1.需求与基本概念:

    1.1 需求:
    在我们平时的工作当中,往往会需要去获取一些诸如国债收益率的信息,而这些信息是写在官网的table上面的,需要我们手动去复制粘贴出来,非常浪费时间。因此就有利用爬虫(crawler)去自动获取的需求。

    1.2 爬虫的概念:

    c560acf3-52e4-4264-990f-2095d0424d83-image.png

    写爬虫的方法在不同的语言当中不尽相同,有用python的,也有用java的,而就工作实用性而言,VBA相对容易获得,毕竟不需要再去安装python的语言包。本文仅介绍基于EXCEL VBA对table的爬虫方法。

    2.基本方法介绍:

    2.1 XMLHTTP对象:
    XMLHTTP对象在VBA中用于在后台与服务器(Server)交换数据。

    XMLHTTP对象能够:
    在不重新加载页面的情况下更新网页
    在页面已加载后从服务器请求数据
    在页面已加载后从服务器接收数据
    在后台向服务器发送数据

    2.2 XMLHTTP对象及方法介绍:
    i. 定义XMLHTTP对象:

    Set xml = CreateObject("microsoft.xmlhttp")
    '调用网络访问组件,相当于双击打开浏览器
    

    ii. Open方法:

    xml.Open "get", strURL, False
    'strURL代表网址字符串,这一步相当于打开网页
    

    语法介绍:
    open(bstrMethod, bstrUrl, varAsync, bstrUser, bstrPassword)
    bstrMethod: 数据传送方式,即GETPOST。用POST方式发送数据,可以大到4MB,也可以换为GET,只能256KB。
    bstrUrl: 服务网页的URL。
    varAsync: 是否同步执行。缺省为True,即同步执行,但只能在DOM中实施同步执行。用中一般将其置为False,即异步执行。
    bstrUser: 用户名,可省略。
    bstrPassword:用户口令,可省略。

    iii. Send方法:

    xml.send'提交请求,类似于回车'
    

    send(varBody)
    语法介绍:
    varBody:指令集。可以是XML格式数据,也可以是字符串,流,或者一个无符号整数数组。也可以省略,让指令通过Open方法的URL参数代入。发送数据的方式分为同步和异步两种。在异步方式下,数据包一旦发送完毕,就结束Send进程,客户机执行的操作;而在同步方式下,客户机要等到服务器返回确认消息后才结束Send进程。

    iv. responsetext属性:

    s = xml.responsetext'responsetext得到网页字符串'
    

    xml.responseText
    string型 结果返回为字符串。

    这一步就相当于返回了网页字符串,这个过程也可以手动显示出来,这个在后面写正则表达式(regex)的时候将会非常有用:
    首先打开网页,输入对应的网址
    以美债国库券Yield Curve为例:https://home.treasury.gov/resource-center/data-chart-center/interest-rates/TextView?type=daily_treasury_yield_curve&field_tdr_date_value=2025
    然后按F12,这样就可以显示出来网页的code。
    58f1f8e6-82ed-4602-a15a-fad66868d89e-image.png
    然后我们可以找得到这个Table对应的code:<table>***</table>这一段,我们可以把这个copy出来方便我们之后写正则表达式。
    6daad06f-9735-453a-840f-863a72729305-image.png
    根据网页字符串的写法,<tr>...<tr>就代表一行中间的内容,有多少行就会有多少个<tr>...<tr>。这个我们之后介绍正则表达式的时候再去详细介绍。
    b942066e-0912-4b71-9fe9-65b85c22e5ad-image.png
    所以到现在为止,我们介绍了如何利用XLMHTTP对象访问网页并且返回网页字符串。那么如何从这些字符串当中提取想要的信息呢?请关注博主的第二篇帖子。


登录后回复