導入網頁真奇妙，上班也能看股票

伙伴們好啊，今天老祝和大家一起學習一個導入網頁數據的技巧。

很多時候，咱們需要將網頁中的數據導入到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

在線咨詢

上一篇：深入 JavaScript 中的 class
下一篇：創建頁面并實現無刷新頁面跳轉

您的項目需求

*請認真填寫需求信息，我們會在24小時內與您取得聯系。

整合營銷服務商