净值、估值和日增长率等信息"/>

利用Excel Power Query获取基金历史净值、估值和日增长率等信息

利用Excel Power Query获取基金历史净值、估值和日增长率等信息

• 先展示结果
• Excel Power Query说明
• 基金数据源
• • 数据查询API接口分析
• Excel query 查询获取基金数据
• • 如何导入所有页的历史净值
  • 创建基金查询的query函数
  • 利用自定义的query函数获取完整数据
  • 最关键步骤
  • 重复利用自定义的query函数获取其他基金完整数据
• 获取的基金历史净值数据

先展示结果

Excel Power Query说明

Excel 自带的 “Power Query” 插件是导入和分析数据的利器，可以从本地源、数据库、云和网页中导入数据并转换。

参考链接
中文excel说明
Power query docs

基金数据源

天天基金网

示例&&易方达蓝筹精选混合 (代码005827)

.html

易方达蓝筹精选混合 (005827)
基金历史净值查询入口

.html

2021-06-19

数据查询API接口分析

数据查询接口链接

.aspx?type=lsjz&code=005827&per=20&page=1

两个重要参数：
基金代码 code=005827
页数 page=1

查询返回结果如下，和网页上显示的一样

var apidata={ content:"
净值日期 单位净值 累计净值 日增长率 申购状态 赎回状态 分红送配
2021-06-18 2.9668 2.9668 -0.40% 限制大额申购 开放赎回
2021-06-17 2.9786 2.9786 0.41% 限制大额申购 开放赎回
2021-06-16 2.9664 2.9664 -1.41% 限制大额申购 开放赎回
2021-06-15 3.0088 3.0088 -1.09% 限制大额申购 开放赎回
2021-06-11 3.0421 3.0421 -1.60% 限制大额申购 开放赎回
2021-06-10 3.0916 3.0916 1.08% 限制大额申购 开放赎回
2021-06-09 3.0587 3.0587 0.32% 限制大额申购 开放赎回
2021-06-08 3.0490 3.0490 -2.46% 限制大额申购 开放赎回
2021-06-07 3.1260 3.1260 -0.48% 限制大额申购 开放赎回
2021-06-04 3.1411 3.1411 0.62% 限制大额申购 开放赎回
2021-06-03 3.1219 3.1219 -0.70% 限制大额申购 开放赎回
2021-06-02 3.1438 3.1438 -0.93% 限制大额申购 开放赎回
2021-06-01 3.1733 3.1733 1.34% 限制大额申购 开放赎回
2021-05-31 3.1313 3.1313 1.65% 限制大额申购 开放赎回
2021-05-28 3.0805 3.0805 -0.68% 限制大额申购 开放赎回
2021-05-27 3.1016 3.1016 -0.20% 限制大额申购 开放赎回
2021-05-26 3.1078 3.1078 0.07% 限制大额申购 开放赎回
2021-05-25 3.1056 3.1056 4.17% 限制大额申购 开放赎回
2021-05-24 2.9813 2.9813 0.93% 限制大额申购 开放赎回
2021-05-21 2.9538 2.9538 -0.43% 限制大额申购 开放赎回
",records:650,pages:33,curpage:1};

Excel query 查询获取基金数据

（需要能正常联网**）
新建一个excel 文件,数据–新建查询–从其他源–自网站

填入查询的api接口链接

.aspx?type=lsjz&code=005827&per=20&page=1

确定，接下来选择含数据Table0，可见power query 已经把第一页数据正常读入了，加载就能导入到Excel 的表中了
看来读取表格数据还是 Excel NB!（超级简单&&）

如何导入所有页的历史净值

导入第一页数据后，打开power query 编辑器界面，如下图

管理参数部分，选择新建参数，新建两个参数：
基金代码 code=005827
页数 page=1
注意参数类型要选择文本

效果如下，关闭并上载保存回到Excel 表

创建基金查询的query函数

还是从web导入数据，数据–新建查询–从其他源–自网站
查询api接口链接

.aspx?type=lsjz&code=005827&per=20&page=1

选择高级，把数据查询API链接分成几段，如下图，把选择基金代码005827和页数1替换为参数{code}和{page)

PS:其实对应的query 语句 url 是：

.aspx?type=lsjz&code={code}&per=20&page={page}

确定后，获取的数据和之前一样，在Table0中，加载

再打开Power query编辑器，如下,选择Table 0 (2) ，右键创建函数 fund_fulldata

新自定义函数

自定义的query函数fx= fund_fulldata (code, page), 有两个自定义参数 {code} 和 {page}

利用自定义的query函数获取完整数据

参数界面，关闭并上载保存，然后在excel表中新建一列1-40，用于设定页数查询的参数page
PS：易方达蓝筹精选混合 (005827)这个数据其实有33页，其他基金不一样，这个按照需求设定就行

接下来，选中这列，

power query 编辑器如下，把列1的数据格式改成文本（ABC）

最关键步骤

接下来选中刚刚导入的表3， 添加列–调用自定义函数–功能查询选择fund_fulldata函数，两个参数{code} 填基金代码，{page} 选择刚刚导入的列1（注意修改成文本格式，第2点），确定

如果不出现错误， 正常的情况下，excel获取外部数据源要求选择隐私检查选项，点继续，选中忽略此文件的隐私级别检查，保存，下一步

接下来power query 按照刚才定义的fund_fulldata 函数和给定的参数去获取数据，等待时间取决于网络链接速度，查询得到的基金历史净值每页的数据，返回以Table格式保存于新列中，如下

点击展开数据源，去掉使用原始列名的框，确定

展开后，下拉，可以看到从易方达蓝筹精选混合(005827) 2018/09/5成立以来的基金历史净值等信息都得到了，接下来关闭并上载回到Excel 表中

如下，注意把 净值日期一列的单元格修改为日期格式，这样易方达蓝筹精选混合(005827) 成立以来的基金历史净值数据全部获取完毕。

重复利用自定义的query函数获取其他基金完整数据

示例 &&蔡总的诺安成长混合(320007)

诺安成长混合(320007)

诺安成长混合(320007基金净值信息

总共接近150页

.aspx?type=lsjz&code=320007&per=20&page=1

步骤总结：
1、新建表 新建一列1-150，修改单元格式为文本
2、导入新列，数据–从表格
3、选中新表–添加列–调用自定义函数，{code} 除改成蔡总的基金代码320007，{page} 改成列1
4、power query获取数据，展开数据，关闭上载、保存到新表中
5、搞定，拿到数据，该干啥干啥

a) 注意修改列1的格式为文本格式，因为参数{page}的格式设置为了文本格式，如果设置成默认的数字格式会报错得不到数据。
b) 其实一开始page参数就设置成数字格式也可以，但{code}要设置为文本，因为Excel 数字格式会自动去掉前面的0，005827就变成了5827，一样会报错得不到数据。

获取的基金历史净值数据

数据仅供展示，不构成投资建议。
请勿抄袭，转载请说明。

VerTicaL&& 2021/06/19 in Shanghai