Weekly Issue 3 | VBA与宏在Excel中的应用举例

　　Weekly Issue 3 | VBA与宏在Excel中的应用举例

　　VBA与宏在Excel当中的应用举例－从Yahoo finance网站自动抓取股价数据

　　Visual Basic for Applications(VBA)作为Visual Basic的一种宏语言，是由微软发布，用于在其桌面应用程序中执行通用自动化(OLE)任务的编程语言。VB/VBA作为一种自动化语言于上世纪90年代就已被开发出来。因为我们工作生活依然离不开微软Office软件，而利用VBA可以大幅提高使用效率，所以其重要性依然不可被低估。本期的《实用技能》栏目关注的是VBA在获取xls格式的股票交易数据方面的应用。相信大家在工作或学习当中经常需要提取股票交易数据进行分析。例如，在对一个包含大量股票组合的交易策略进行历史数据测试时，我们需要在网站上对标的股票逐个查找，选定时间区间及时间间隔进行数据下载。而下载后还需要将大量的Excel数据进行并表，十分繁琐。而如果我们学会使用Excel当中简单的VBA及宏操作，就能轻松实现数据提取。

　　准备工作

　　大家在准备进行VBA及宏操作之前，需要确认一下在Excel的选项卡中是否将“开发工具”一栏调出。如果没有，可以在（以office2013为例）“文件—选项—自定义功能区中”进行勾选：

　　确定完毕之后，我们便能在工具栏中找到VisualBasic与宏的相关按钮。

　　录制宏—股票交易数据导入为了实现我们的目的，我们需要借助Excel中的宏工具。宏相当于是一个操作录制工具，可以将用户的一连串操作记录下来，便于之后实现一键再现的操作。由于这一录制过程能够通过代码进行体现，对于并不太熟悉VBA的用户来说，宏也是一个学习VBA代码撰写的利器。我们今天的主要逻辑亦为：首先对我们需要的操作进行录制以获取代码，再在基本框架上进行简单的修改，从而实现获取特定股票交易数据的简易化。具体步骤如下：点击“录制宏”，命名为“数据获取”，并单击“确定”：

　　从Yahoo Finance网站中任意查找一支股票的历史股价（以沃尔玛 ticker: WMT为例），并任意选取起始时间及时间间隔（之后在代码中都可以进行调整）：

　　拖至网页最下方，找到

　　后，单击右键选择“复制链接地址”。

　　回到Excel，找到“数据”选项卡中的“自文本”。将刚刚复制的链接粘贴至最下方的“文件名”处，并选择“打开”：

　　进入弹出的文件导入向导。第1步选择默认的“分隔符号”。第2步在额外勾选“逗号”后，会发现Excel成功依据我们所需要的形式将数据分割为了若干列。继续单击下一步：

　　进入第3步后，我们需要确保第一列的日期按照Yahoo所提供的格式进行排列（如本例中的YMD, Year-Month-Day）。当然，如果我们认为其中某一列数据并不必要，我们可以选择对其“不导入此列（跳过）”（这在之后的代码中同样可以进行调整）。最后单击“完成”：

　　在表格的$A$1位置确定导入。

　　利用VBA对代码进行修改

　　至此，我们获得了沃尔玛在指定时间区间内的股价。接下来我们需要做的是对通过录制获取的代码进行修改，步骤如下：

　　在“开发工具—宏”中，对我们刚刚录制的宏“数据获取”选择“编辑”：

　　弹出Visual Basic Editor界面（该界面也可以通过快捷键Alt+F11打开）。由此我们获得了刚刚录制一连串操作所得的代码：

　　由于此处不需要从头对代码进行撰写，且受篇幅所限，我们就不逐行对VBA语言进行详细的介绍。有兴趣的同学可以在互联网上搜索相关资源自行学习（例如等的视频学习教程）。

　　对输入项进行定义。因为我们希望最后只需要通过输入股票对应的ticker及起止日期就能获得对应股票交易信息，所以我们需要对这些输入项进行定义。在VBA中，定义是由“dim“完成的，经常使用定义的包括三种：整数型变量(Interger, %) 长整型变量(Long, &) 字符串变量(String, $)。由于ticker的内容代指一个含有多位字母的字符串，起止年/月/日都是多位数字，我们采取以下定义（只要该行代码没有出现错误，回车后首字母会自动大写，颜色也会相应产生改变）：

　　每次输入数据前，需要程序将A-G列进行清空，代码如下：

　　对ticker及起止日期定位，并用range分别表示其值。单元格K1对应ticker，K2对应起始日期，K3对应结束日期。Day/Month/Year()函数分别表示取日期中的年月日相应部分：

　　值得注意的是，在Yahoo Finance当中，一月用数字0表示；而在Excel当中，一月的表示方式为数字1。所以在代码中我们需要在月份后面加上“-1”以实现从Excel输入数据到Yahoo Finance提取数据的转换。

　　对URL进行修改。由于打开Yahoo Finance时，所查股票ticker及起止日期在地址栏中存在对应的指代部分，我们可以通过将刚刚定义的可变变量插入下方表示URL的代码当中，修改方式如下：

　　删除CommanType = 0整行

　　Array后的“5”表示该列采用的是YMD的时间数据，“1”表示该列为普通数据。如果不需要导出某列，只需要按顺序将该列设置为“9”即可

　　如果只需要导入数据的若干列，只需找到代码的这一行：TextFileColumnDataTypes = Array(5, 1, 1, 1, 1, 1, 1)

　　实现一键操作完成以上步骤并检查完毕后，在ticker处输入“GOOGL”，并调整起止日期。这时便可以点击上方工具栏的运行子过程（快捷键F5）来运行这一程序：

　　如果遇到程序报错，可以通过“视图”打开“本地窗口”，用F8对代码进行逐步检测从而发现具体问题来源。

　　于是我们轻松导入了谷歌在2016年前三个月的股票交易信息：

　　通过“开发工具—插入—按钮（窗体控件）”绘制按钮，并命名为“获取历史股价”。此处按住Alt键可以确保按钮能够与单元格分界线对齐。选择已录制并修改的宏，单击“确定”。同时，我们还可以根据需要加上平均单日回报，收盘价标准差等等（注意：由于导入数据的时长可能发生变动，每期数据样本量会产生差异，这些附加的公式内都应直接选取整列，如=AVERAGE(H:H)

　　美股直接输入ticker即可（如WMT, GOOGL），而港股及沪深股市只需要在数字后分别加上”.HK”,”.SS”,”.SZ”即可（如0001.HK，600000.SS）。这样我们便能在Excel输入目标股票的ticker及起止日期后，实现一键获取股票交易相关数据。

　　希望这次的《实用技能》栏目能够为大家平时的数据查找过程提供一条捷径。笔者也希望能够透过这一案例，使得大家对于VBA与宏在相关professional service当中的重要地位有一个初步了解。

　　供稿 ｜ 王主丰 许晓琛编辑 ｜ 白书豪 唐轶一