利用Power Query动态采取数据

很长的一段时间内对微软都有一种偏见，认为微软的东西抹杀了创造力。不过另一方面想，软件的产品确实将复杂的东西简单化了，专业人员搞的功能可以很容易的让一个不懂的人通过点几几个按键也能实现。这点确实比较符合产品美学的理念。就像本篇要讲的Excel下的power query功能 ，这个默认在office 2016默认有集成，之前的产品要单独装 。本篇要实现的功能就是通过power query自动采集某个web站点下多个page页的内容，并按一定的时间实时刷新内容。

一、采集单个页面

这个不装power query插件，通过数据－－－自网站也可以实现，当然和通过power query －－－从web是一样的。这里以某个股票站的数据为例：

需要刷新的话，可以在选项里选择刷新频率（以分钟为单位）。

二、采集多个页面

1、单页面采集

这里还以同花顺页面上的行情数据为例：http://data.10jqka.com.cn/funds/ggzjl/field/zjjlr/order/desc/page/1/ajax/1/ ，一共有56页内容，每个url 变动的只是page后面的数字。首先还是从第一个页面开始：

这里可以选择基本，这里选了高级部分是为了展现其可以实现传的参数。接下来确定后，会出现页面的元素部分，如下图：

选中table部分后，会返回数据给我们 。

2、指定页面数据采集

数据返回后，依次选择开始－－－高级编辑器－－－可以查看我们刚刚点选内部是通过以下代码来实现的 。

在上面的代码头部我们增加一行，最后的代码内容如下：

1(page as number) as table =>
2let
3    源 = Web.Page(Web.Contents("http://data.10jqka.com.cn/funds/ggzjl/field/zjjlr/order/desc/page/" & Number.ToText(page) & "/ajax/1/")),
4    Data0 = 源{0}[Data],
5    更改的类型 = Table.TransformColumnTypes(Data0,{{"序号", Int64.Type}, {"股票代码", Int64.Type}, {"股票简称", type text}, {"最新价", type number}, {"涨跌幅", type text}, {"换手率", type text}, {"流入资金(元)", type text}, {"流出资金(元)", type text}, {"净额(元)", type text}, {"成交额(元)", type text}, {"大单流入(元)", type text}})
6in
7    更改的类型

保存后，会出现如下界面，这时我们使用变量 ” & Number.ToText(page) & “代替页面值。而具体的某个页面值需要我们手动输入，如下，比如我们输入5调用，就会返回第5页的内容。

当然上面的调用page页不能是一个范围，只能是单个整数（站在python\shell 等常用编程语言的角度来看，感觉又好low）。

3、多页面采集

上面的调用部分我们写好后，我们关闭关上载。这时会返回到excel 主界面，我们给刚刚定义好自定义调用函数取一个内涵点的名字（有点类似于python下的def了），这里我改为getdata 。

依然选择power query －－－从其他源－－－空查询，并输入一个自定义的范围值，如：{1..5} ，界面如下：

my god ,这不就是linux shell下的用法吗？返回了一个list列表给我们。右键转换为表 。这时我们可以选择添加自定义表或者调用自定义函数（两者最张实现的效果是相同的，只不过代码稍有差别），如下：

这里选中自已需要的列以后就可以返回数据了。对于返回的数据我们还可以过滤晒选或排序。规则定义好以后，同样是保存上载。后续双击时，就会自动查询了。下面列下增加自定义函数时，调用的代码：

 1let
 2    源 = {1..4},
 3    转换为表 = Table.FromList(源, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 4    重命名的列 = Table.RenameColumns(转换为表,{{"Column1", "Page"}}),
 5    已添加自定义 = Table.AddColumn(重命名的列, "自定义", each Getdata([Page])),
 6    #"展开的“自定义”" = Table.ExpandTableColumn(已添加自定义, "自定义", {"成交额(元)", "大单流入(元)", "股票代码", "股票简称", "换手率", "净额(元)", "流出资金(元)", "流入资金(元)", "序号", "涨跌幅", "最新价"}, {"自定义.成交额(元)", "自定义.大单流入(元)", "自定义.股票代码", "自定义.股票简称", "自定义.换手率", "自定义.净额(元)", "自定义.流出资金(元)", "自定义.流入资金(元)", "自定义.序号", "自定义.涨跌幅", "自定义.最新价"}),
 7    删除的列 = Table.RemoveColumns(#"展开的“自定义”",{"自定义.流出资金(元)", "自定义.流入资金(元)","自定义.换手率"}),
 8    重排序的列 = Table.ReorderColumns(删除的列,{"Page", "自定义.序号", "自定义.成交额(元)", "自定义.大单流入(元)", "自定义.股票代码", "自定义.股票简称", "自定义.净额(元)", "自定义.涨跌幅", "自定义.最新价"})
 9in
10    重排序的列

和上面调用自定义函数的区别在于Getdata 一个是page，另一个是column ，但最终实现的结果是一致的。

三、自动刷新

上面我们这完成了多页面数据的获取，如果想要对这4页的内容进行定时刷新的话也比较easy，如下图依次选择数据－－－连接－－－属性－－－指定刷新频率即可。

四、总结

本篇讲到的内容本来想通过python BeautifulSoup和urllib2采集实现，不过看同事在对数据库oswatch的性能数据通过excel透析图实现绘图时，也想顺便通过excel实现下web采集多页并自动刷新的功能。当然excel本身非常强大，可以实现做战地图、数据调用sql查询等。最新的power BI功能更是在在数据展示、易用、分析各功能之间做了一个平衡，可以不需要很专业的知识就可以实现牛X的功能。