伙伴们好啊，今天老祝和大家一起学习一个导入网页数据的技巧。

很多时候，咱们需要将网页中的数据导入到Excel里做进一步的分析汇总，比如每天的股市行情、外汇牌价、开奖信息等等。

接下来，老祝就来演示一下，从下面这个网页中导入中国银行的每日外汇牌价。

在这个网页中，外汇牌价的信息一共有10页，如果使用Excel 早期版本中的导入网页数据功能，默认只能导入第一页的信息。

接下来咱们以Excel 2019为例，说说导入全部10页信息的具体操作过程。

步骤1

依次单击【数据】→【自网页】，输入以下网页链接：

https://www.boc.cn/sourcedb/whpj/index_1.html

在【导航器】对话框中选择 Table 0，将数据加载到Power Query编辑器。

注意此时只加载了第一页的信息，还需要咱们进一步的处理一下。

步骤2

单击【高级编辑器】按钮，弹出【高级编辑器】对话框。在let语句之前添加以下内容，将查询转换为参数化的函数：

(page as number) as table=>

据说这个两个as语句是指定参数类型的，这里咱们先暂时记住这样用。

接下来将url中表示页面的数值“1”替换为以下内容，来构建一个动态的查询参数字符串：

" & Number.ToText(page) & "

单击【完成】按钮，会出现这样的界面：

步骤3

接下来要生成一组序号，作为刚刚定义的函数的页码参数。

在左侧的查询列表中单击右键，新建空查询。在【高级编辑器】里输入以下内容：

{1..10}

这里的10，可以根据实际页码数来确定。最后单击【到表】按钮，转换为Table。

步骤4

双击查询名称“Table 0”，输入一个容易记忆的名称，比如“ Web”

步骤5

在“查询1”中添加自定义列：

=Web([Column1])

步骤6

由于咱们定义的页数比网页中的实际页数要多，所以最后一行出现了错误值，需要先将其删除，然后再展开数据，加载到工作表中。

最后，咱们还可以依次单击【数据】→【全部刷新】→【连接属性】，设置一下刷新频率，使Excel能够按照指定的时间在后台自动刷新：

好了，今天咱们的内容就是这些吧，祝各位周日好心情！

图文制作：祝洪忠

言
股票市场的波动往往存在一定的共振，尤其是同一个行业或主题概念的公司股票，当面临行业基本面的冲击时，其波动存在一定的相似性，即表现出同涨同跌。如果能通过交易行情数据对股票市场的波动结构进行刻画，对于我们深入理解板块轮动和网络关联性具有重要的启示作用。那么如何借助可视化的手段对股票市场结构进行分析呢？机器学习中的无监督学习算法或许可以帮助我们解决这一问题。本文以上证50指数成分股为例，使用稀疏逆协方差（GraphicalLassoCV）计算股票之间的条件相关性，然后使用聚类分析将行为相似的股票分组在一起并进行可视化。

数据获取
使用tushare pro获取上证50指数成分股收盘价和开盘价数据，以收盘价减去开盘价作为日波动的替代变量。以下代码使用Jupyter notebook运行。

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from matplotlib.collections import LineCollection
from sklearn import cluster, covariance, manifold
%matplotlib inline #Jupyter Notebook显示图形专用
plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus']=False

import tushare as ts
token='到tushare pro官网获取你的token'
pro=ts.pro_api(token)
#获取上证50成分股票代码和名称
def get_50_code():
    #获取上证50成分股代码
    dd=pro.index_weight(index_code='000016.SH')
    dd=dd[dd.trade_date=='20201130']
    codes50=dd.con_code.values
    #获取全市场股票基本信息
    df = pro.stock_basic(exchange='', list_status='L')
    df=df[df.ts_code.isin(codes50)]
    codes=df.ts_code.values
    names=df.name.values
    stocks=dict(zip(codes,names))
    return stocks

def get_data(code,start='20191210',end='20201210'):
    df=ts.pro_bar(ts_code=code,adj='qfq', 
                  start_date=start, end_date=end)
    df.index=pd.to_datetime(df.trade_date)
    df=df.sort_index()
    return dfcodes, names = np.array(sorted(get_50_code().items())).T
data=pd.DataFrame({name:(get_data(code).close-get_data(code).open) 
                   for code,name in zip(codes,names)})
variation=data.dropna().valuesdata.head()

上证50成分股股价日变动情况：

聚类分析
由于相互关联的股票会在交易中产生共波动，所以我们可以使用无监督学习算法从历史报价中提取股票市场结构的变化，如使用（收盘价-开盘价）来刻画股价每日价格变动，然后使用稀疏逆协方差估计找出哪些股票存在条件相关性。换句话说，稀疏逆协方差可以得到一个方差关联性列表，对于每只股票来说，与之相关的股票有助于解释其波动。然后再使用聚类分析将行为相似的股票分组在一起。scikit-learn提供了十种不同的聚类算法，本文用“Affinity_propagation”（AP算法），主要基于该算法可以从数据中自动选择聚类的数量。AP算法的基本思想是将全部样本看作网络的节点，然后通过网络中各条边的消息传递计算出各样本的聚类中心，关于该算法的详细原理可参考scikit-learn官网或相关书籍。

# 相关系数
edge_model = covariance.GraphicalLassoCV()
X = variation.copy()
X /= X.std(axis=0)
edge_model.fit(X)
_, labels = cluster.affinity_propagation(edge_model.covariance_)
n_labels = labels.max()

for i in range(n_labels + 1):
    print('Cluster %i: %s' % ((i + 1), ', '.join(names[labels == i])))

#输出结果：Cluster 1: 万华化学
Cluster 2: 恒瑞医药, 贵州茅台, 伊利股份
Cluster 3: 山东黄金
Cluster 4: 三安光电, 闻泰科技, 汇顶科技
Cluster 5: 浦发银行, 民生银行, 中国石化, 招商银行, 兴业银行, 农业银行, 中国平安, 交通银行, 工商银行, 邮储银行, 光大银行, 中国石油, 中国银行
Cluster 6: 三一重工, 保利地产, 海螺水泥, 中国神华, 中国铁建, 中国建筑
Cluster 7: 上海机场, 中信证券, 中国联通, 上汽集团, 海尔智家, 海通证券, 中信建投, 工业富联, 国泰君安, 红塔证券, 中国人保, 新华保险, 中国太保, 中国人寿, 华泰证券, 中国中免, 中国重工, 洛阳钼业
Cluster 8: 京沪高铁
Cluster 9: 复星医药, 用友网络, 隆基股份, 药明康德

数据可视化

为了将上述聚类分析进行可视化，需要在一个2D画布上布置不同的股票。为此，需要使用“流形”技术来检索二维嵌入。模型的输出组合成一个二维图，其中节点代表股票名称，边表示:集群标签用于定义节点的颜色使用稀疏协方差模型来显示边缘的强度二维嵌入用于在平面中定位节点

node_position_model=manifold.LocallyLinearEmbedding( n_components=2, eigen_solver='dense', n_neighbors=6)embedding=node_position_model.fit_transform(X.T).T

# 可视化
plt.figure(1, facecolor='w', figsize=(10, 8))
plt.clf()
ax = plt.axes([0., 0., 1., 1.])
plt.axis('off')

# 计算偏相关系数
partial_correlations = edge_model.precision_.copy()
d = 1 / np.sqrt(np.diag(partial_correlations))
partial_correlations *= d
partial_correlations *= d[:, np.newaxis]
non_zero = (np.abs(np.triu(partial_correlations, k=1)) > 0.02)

# 使用嵌入的坐标绘制节点
plt.scatter(embedding[0], embedding[1], s=100 * d ** 2, c=labels,
            cmap=plt.cm.nipy_spectral)

# 画相互关联的边
start_idx, end_idx = np.where(non_zero)
segments = [[embedding[:, start], embedding[:, stop]]
            for start, stop in zip(start_idx, end_idx)]
values = np.abs(partial_correlations[non_zero])
lc = LineCollection(segments,
                    zorder=0, cmap=plt.cm.hot_r,
                    norm=plt.Normalize(0, .7 * values.max()))
lc.set_array(values)
lc.set_linewidths(15 * values)
ax.add_collection(lc)

#向每个节点添加一个标签，难点在于定位标签，以避免与其他标签重叠
for index, (name, label, (x, y)) in enumerate(
        zip(names, labels, embedding.T)):

    dx = x - embedding[0]
    dx[index] = 1
    dy = y - embedding[1]
    dy[index] = 1
    this_dx = dx[np.argmin(np.abs(dy))]
    this_dy = dy[np.argmin(np.abs(dx))]
    if this_dx > 0:
        horizontalalignment = 'left'
        x = x + .002
    else:
        horizontalalignment = 'right'
        x = x - .002
    if this_dy > 0:
        verticalalignment = 'bottom'
        y = y + .002
    else:
        verticalalignment = 'top'
        y = y - .002
    plt.text(x, y, name, size=10,
             horizontalalignment=horizontalalignment,
             verticalalignment=verticalalignment,
             bbox=dict(facecolor='w',
                       edgecolor=plt.cm.nipy_spectral(label / float(n_labels)),
                       alpha=.6))

plt.xlim(embedding[0].min() - .15 * embedding[0].ptp(),
         embedding[0].max() + .10 * embedding[0].ptp(),)
plt.ylim(embedding[1].min() - .03 * embedding[1].ptp(),
         embedding[1].max() + .03 * embedding[1].ptp())

plt.show()

图表反映了变量之间的条件关系，而聚类反映了边际属性：聚在一起的变量可以被认为在整个股票市场水平上具有类似的影响。从下图中可以看出，无监督学习通过对交易报价信息的提取，可以大致勾勒出上证50指数成分股的一个市场结构，具有相同行业属性或概念属性的个股其波动表现出相似性，如医药、银行、券商、保险、大基建等。

结语
机器学习是量化分析的一个重要工具，掌握机器学习算法的基本原理和应用场景可以为我们分析和研究金融市场提供一个参考框架。无监督机器学习中的聚类分析能够从纷繁复杂的数据中提取有用信息，刻画多维特征的“相似性”和“关联性”，再借助网络分析的视角，可以进一步考察数据变量间的微观结构和运动状态。本文参考scikit-learn官方网站示例，对上证50指数成分股的“共波动”结构进行了可视化分析，为大家深入学习机器学习抛砖引玉。关于网络分析方面，Python有个很好用的第三方库——networkx，可以画出各种精美的网络分析图，感兴趣的读者可以进一步了解。

参考资料：

scikit-learn官方网站案例：https://scikit-learn.org/stable/auto_examples/applications/plot_stock_market.html?highlight=plot%20stock%20market

生苦短，我用 Python

如果我的文章对您有帮助，请关注支持下作者的公众号：极客挖掘机，您的关注，是对小编坚持原创的最大鼓励：）

前文传送门：

小白学 Python 爬虫（1）：开篇

小白学 Python 爬虫（2）：前置准备（一）基本类库的安装

小白学 Python 爬虫（3）：前置准备（二）Linux基础入门

小白学 Python 爬虫（4）：前置准备（三）Docker基础入门

小白学 Python 爬虫（5）：前置准备（四）数据库基础

小白学 Python 爬虫（6）：前置准备（五）爬虫框架的安装

小白学 Python 爬虫（7）：HTTP 基础

小白学 Python 爬虫（8）：网页基础

小白学 Python 爬虫（9）：爬虫基础

小白学 Python 爬虫（10）：Session 和 Cookies

小白学 Python 爬虫（11）：urllib 基础使用（一）

小白学 Python 爬虫（12）：urllib 基础使用（二）

小白学 Python 爬虫（13）：urllib 基础使用（三）

小白学 Python 爬虫（14）：urllib 基础使用（四）

小白学 Python 爬虫（15）：urllib 基础使用（五）

小白学 Python 爬虫（16）：urllib 实战之爬取妹子图

小白学 Python 爬虫（17）：Requests 基础使用

小白学 Python 爬虫（18）：Requests 进阶操作

小白学 Python 爬虫（19）：Xpath 基操

小白学 Python 爬虫（20）：Xpath 进阶

小白学 Python 爬虫（21）：解析库 Beautiful Soup（上）

小白学 Python 爬虫（22）：解析库 Beautiful Soup（下）

小白学 Python 爬虫（23）：解析库 pyquery 入门

小白学 Python 爬虫（24）：2019 豆瓣电影排行

引言

上一篇的实战写到最后没有用到页面元素解析，感觉有点小遗憾，不过最后的电影列表还是挺香的，真的推荐一看。

本次选题是先写好代码再写的文章，绝对可以用到页面元素解析，并且还需要对网站的数据加载有一定的分析，才能得到最终的数据，并且小编找的这两个数据源无 ip 访问限制，质量有保证，绝对是小白练手的绝佳之选。

郑重声明： 本文仅用于学习等目的。

分析

首先要爬取股票数据，肯定要先知道有哪些股票吧，这里小编找到了一个网站，这个网站上有股票的编码列表：https://hq.gucheng.com/gpdmylb.html 。

打开 Chrome 的开发者模式，将股票代码一个一个选出来吧。具体过程小编就不贴了，各位同学自行实现。

我们可以将所有的股票代码存放在一个列表中，剩下的就是找一个网站，循环的去将每一只股票的数据取出来咯。

这个网站小编已经找好了，是同花顺，链接： http://stockpage.10jqka.com.cn/000001/ 。

想必各位聪明的同学已经发现了，这个链接中的 000001 就是股票代码。

我们接下来只需要拼接这个链接，就能源源不断的获取到我们想要的数据。

实战

首先，还是先介绍一下本次实战用到的请求库和解析库为： Requests 和 pyquery 。数据存储最后还是落地在 Mysql 。

获取股票代码列表

第一步当然是先构建股票代码列表咯，我们先定义一个方法：

def get_stock_list(stockListURL):
    r=requests.get(stockListURL, headers=headers)
    doc=PyQuery(r.text)
    list=[]
    # 获取所有 section 中 a 节点，并进行迭代
    for i in doc('.stockTable a').items():
        try:
            href=i.attr.href
            list.append(re.findall(r"\d{6}", href)[0])
        except:
            continue
    list=[item.lower() for item in list]  # 将爬取信息转换小写
    return list

将上面的链接当做参数传入，大家可以自己运行下看下结果，小编这里就不贴结果了，有点长。。。

获取详情数据

详情的数据看起来好像是在页面上的，但是，实际上并不在，实际最终获取数据的地方并不是页面，而是一个数据接口。

http://qd.10jqka.com.cn/quote.php?cate=real&type=stock&callback=showStockDate&return=json&code=000001

至于是怎么找出来，小编这次就不说，还是希望各位想学爬虫的同学能自己动动手，去寻找一下，多找几次，自然就摸到门路了。

现在数据接口有了，我们先看下返回的数据吧：

showStockDate({"info":{"000001":{"name":"\u5e73\u5b89\u94f6\u884c"}},"data":{"000001":{"10":"16.13","8":"16.14","9":"15.87","13":"78795234.00","19":"1262802470.00","7":"16.12","15":"40225508.00","14":"37528826.00","69":"17.73","70":"14.51","12":"5","17":"945400.00","264648":"0.010","199112":"0.062","1968584":"0.406","2034120":"9.939","1378761":"16.026","526792":"1.675","395720":"-948073.000","461256":"-39.763","3475914":"313014790000.000","1771976":"1.100","6":"16.12","11":""}}})

很明显，这个结果并不是标准的 json 数据，但这个是 JSONP 返回的标准格式的数据，这里我们先处理下头尾，将它变成一个标准的 json 数据，再对照这页面的数据进行解析，最后将分析好的值写入数据库中。

def getStockInfo(list, stockInfoURL):
    count=0
    for stock in list:
        try:
            url=stockInfoURL + stock
            r=requests.get(url, headers=headers)
            # 将获取到的数据封装进字典
            dict1=json.loads(r.text[14: int(len(r.text)) - 1])
            print(dict1)

            # 获取字典中的数据构建写入数据模版
            insert_data={
                "code": stock,
                "name": dict1['info'][stock]['name'],
                "jinkai": dict1['data'][stock]['7'],
                "chengjiaoliang": dict1['data'][stock]['13'],
                "zhenfu": dict1['data'][stock]['526792'],
                "zuigao": dict1['data'][stock]['8'],
                "chengjiaoe": dict1['data'][stock]['19'],
                "huanshou": dict1['data'][stock]['1968584'],
                "zuidi": dict1['data'][stock]['9'],
                "zuoshou": dict1['data'][stock]['6'],
                "liutongshizhi": dict1['data'][stock]['3475914']
            }
            cursor.execute(sql_insert, insert_data)
            conn.commit()
            print(stock, '：写入完成')
        except:
            print('写入异常')
            # 遇到错误继续循环
            continue

这里我们加入异常处理，因为本次爬取的数据有些多，很有可能由于某些原因抛出异常，我们当然不希望有异常的时候中断数据抓取，所以这里添加异常处理继续抓取数据。

完整代码

我们将代码稍作封装，完成本次的实战。

import requests
import re
import json
from pyquery import PyQuery
import pymysql

# 数据库连接
def connect():
    conn=pymysql.connect(host='localhost',
                           port=3306,
                           user='root',
                           password='password',
                           database='test',
                           charset='utf8mb4')

    # 获取操作游标
    cursor=conn.cursor()
    return {"conn": conn, "cursor": cursor}

connection=connect()
conn, cursor=connection['conn'], connection['cursor']

sql_insert="insert into stock(code, name, jinkai, chengjiaoliang, zhenfu, zuigao, chengjiaoe, huanshou, zuidi, zuoshou, liutongshizhi, create_date) values (%(code)s, %(name)s, %(jinkai)s, %(chengjiaoliang)s, %(zhenfu)s, %(zuigao)s, %(chengjiaoe)s, %(huanshou)s, %(zuidi)s, %(zuoshou)s, %(liutongshizhi)s, now())"

headers={
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36'
}

def get_stock_list(stockListURL):
    r=requests.get(stockListURL, headers=headers)
    doc=PyQuery(r.text)
    list=[]
    # 获取所有 section 中 a 节点，并进行迭代
    for i in doc('.stockTable a').items():
        try:
            href=i.attr.href
            list.append(re.findall(r"\d{6}", href)[0])
        except:
            continue
    list=[item.lower() for item in list]  # 将爬取信息转换小写
    return list


def getStockInfo(list, stockInfoURL):
    count=0
    for stock in list:
        try:
            url=stockInfoURL + stock
            r=requests.get(url, headers=headers)
            # 将获取到的数据封装进字典
            dict1=json.loads(r.text[14: int(len(r.text)) - 1])
            print(dict1)

            # 获取字典中的数据构建写入数据模版
            insert_data={
                "code": stock,
                "name": dict1['info'][stock]['name'],
                "jinkai": dict1['data'][stock]['7'],
                "chengjiaoliang": dict1['data'][stock]['13'],
                "zhenfu": dict1['data'][stock]['526792'],
                "zuigao": dict1['data'][stock]['8'],
                "chengjiaoe": dict1['data'][stock]['19'],
                "huanshou": dict1['data'][stock]['1968584'],
                "zuidi": dict1['data'][stock]['9'],
                "zuoshou": dict1['data'][stock]['6'],
                "liutongshizhi": dict1['data'][stock]['3475914']
            }
            cursor.execute(sql_insert, insert_data)
            conn.commit()
            print(stock, '：写入完成')
        except:
            print('写入异常')
            # 遇到错误继续循环
            continue
def main():
    stock_list_url='https://hq.gucheng.com/gpdmylb.html'
    stock_info_url='http://qd.10jqka.com.cn/quote.php?cate=real&type=stock&callback=showStockDate&return=json&code='
    list=get_stock_list(stock_list_url)
    # list=['601766']
    getStockInfo(list, stock_info_url)

if __name__=='__main__':
    main()

成果

最终小编耗时 15 分钟左右，成功抓取数据 4600+ 条，结果就不展示了。

示例代码

本系列的所有代码小编都会放在代码管理仓库 Github 和 Gitee 上，方便大家取用。

示例代码-Github

示例代码-Gitee