整合營銷服務(wù)商
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
電腦端+手機(jī)端+微信端=數(shù)據(jù)同步管理
免費(fèi)咨詢熱線：
伙伴們好啊，今天老祝和大家一起學(xué)習(xí)一個(gè)導(dǎo)入網(wǎng)頁數(shù)據(jù)的技巧。
很多時(shí)候，咱們需要將網(wǎng)頁中的數(shù)據(jù)導(dǎo)入到Excel里做進(jìn)一步的分析匯總，比如每天的股市行情、外匯牌價(jià)、開獎信息等等。
接下來，老祝就來演示一下，從下面這個(gè)網(wǎng)頁中導(dǎo)入中國銀行的每日外匯牌價(jià)。
在這個(gè)網(wǎng)頁中，外匯牌價(jià)的信息一共有10頁，如果使用Excel 早期版本中的導(dǎo)入網(wǎng)頁數(shù)據(jù)功能，默認(rèn)只能導(dǎo)入第一頁的信息。
接下來咱們以Excel 2019為例，說說導(dǎo)入全部10頁信息的具體操作過程。
步驟1
依次單擊【數(shù)據(jù)】→【自網(wǎng)頁】，輸入以下網(wǎng)頁鏈接：
https://www.boc.cn/sourcedb/whpj/index_1.html
在【導(dǎo)航器】對話框中選擇 Table 0，將數(shù)據(jù)加載到Power Query編輯器。
注意此時(shí)只加載了第一頁的信息，還需要咱們進(jìn)一步的處理一下。
步驟2
單擊【高級編輯器】按鈕，彈出【高級編輯器】對話框。在let語句之前添加以下內(nèi)容，將查詢轉(zhuǎn)換為參數(shù)化的函數(shù)：
(page as number) as table=>
據(jù)說這個(gè)兩個(gè)as語句是指定參數(shù)類型的，這里咱們先暫時(shí)記住這樣用。
接下來將url中表示頁面的數(shù)值“1”替換為以下內(nèi)容，來構(gòu)建一個(gè)動態(tài)的查詢參數(shù)字符串：
" & Number.ToText(page) & "
單擊【完成】按鈕，會出現(xiàn)這樣的界面：
步驟3
接下來要生成一組序號，作為剛剛定義的函數(shù)的頁碼參數(shù)。
在左側(cè)的查詢列表中單擊右鍵，新建空查詢。在【高級編輯器】里輸入以下內(nèi)容：
{1..10}
這里的10，可以根據(jù)實(shí)際頁碼數(shù)來確定。最后單擊【到表】按鈕，轉(zhuǎn)換為Table。
步驟4
雙擊查詢名稱“Table 0”，輸入一個(gè)容易記憶的名稱，比如“ Web”
步驟5
在“查詢1”中添加自定義列：
=Web([Column1])
步驟6
由于咱們定義的頁數(shù)比網(wǎng)頁中的實(shí)際頁數(shù)要多，所以最后一行出現(xiàn)了錯(cuò)誤值，需要先將其刪除，然后再展開數(shù)據(jù)，加載到工作表中。
最后，咱們還可以依次單擊【數(shù)據(jù)】→【全部刷新】→【連接屬性】，設(shè)置一下刷新頻率，使Excel能夠按照指定的時(shí)間在后臺自動刷新：
好了，今天咱們的內(nèi)容就是這些吧，祝各位周日好心情！
圖文制作：祝洪忠
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']=Falseimport tushare as ts
token='到tushare pro官網(wǎng)獲取你的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成分股股價(jià)日變動情況：
# 相關(guān)系數(shù)
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])))#輸出結(jié)果：Cluster 1: 萬華化學(xué)
Cluster 2: 恒瑞醫(yī)藥, 貴州茅臺, 伊利股份
Cluster 3: 山東黃金
Cluster 4: 三安光電, 聞泰科技, 匯頂科技
Cluster 5: 浦發(fā)銀行, 民生銀行, 中國石化, 招商銀行, 興業(yè)銀行, 農(nóng)業(yè)銀行, 中國平安, 交通銀行, 工商銀行, 郵儲銀行, 光大銀行, 中國石油, 中國銀行
Cluster 6: 三一重工, 保利地產(chǎn), 海螺水泥, 中國神華, 中國鐵建, 中國建筑
Cluster 7: 上海機(jī)場, 中信證券, 中國聯(lián)通, 上汽集團(tuán), 海爾智家, 海通證券, 中信建投, 工業(yè)富聯(lián), 國泰君安, 紅塔證券, 中國人保, 新華保險(xiǎn), 中國太保, 中國人壽, 華泰證券, 中國中免, 中國重工, 洛陽鉬業(yè)
Cluster 8: 京滬高鐵
Cluster 9: 復(fù)星醫(yī)藥, 用友網(wǎng)絡(luò), 隆基股份, 藥明康德
為了將上述聚類分析進(jìn)行可視化，需要在一個(gè)2D畫布上布置不同的股票。為此，需要使用“流形”技術(shù)來檢索二維嵌入。模型的輸出組合成一個(gè)二維圖，其中節(jié)點(diǎn)代表股票名稱，邊表示:集群標(biāo)簽用于定義節(jié)點(diǎn)的顏色使用稀疏協(xié)方差模型來顯示邊緣的強(qiáng)度二維嵌入用于在平面中定位節(jié)點(diǎn)
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')
# 計(jì)算偏相關(guān)系數(shù)
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)
# 使用嵌入的坐標(biāo)繪制節(jié)點(diǎn)
plt.scatter(embedding[0], embedding[1], s=100 * d ** 2, c=labels,
cmap=plt.cm.nipy_spectral)
# 畫相互關(guān)聯(lián)的邊
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)
#向每個(gè)節(jié)點(diǎn)添加一個(gè)標(biāo)簽，難點(diǎn)在于定位標(biāo)簽，以避免與其他標(biāo)簽重疊
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()圖表反映了變量之間的條件關(guān)系，而聚類反映了邊際屬性：聚在一起的變量可以被認(rèn)為在整個(gè)股票市場水平上具有類似的影響。從下圖中可以看出，無監(jiān)督學(xué)習(xí)通過對交易報(bào)價(jià)信息的提取，可以大致勾勒出上證50指數(shù)成分股的一個(gè)市場結(jié)構(gòu)，具有相同行業(yè)屬性或概念屬性的個(gè)股其波動表現(xiàn)出相似性，如醫(yī)藥、銀行、券商、保險(xiǎn)、大基建等。
結(jié)語
機(jī)器學(xué)習(xí)是量化分析的一個(gè)重要工具，掌握機(jī)器學(xué)習(xí)算法的基本原理和應(yīng)用場景可以為我們分析和研究金融市場提供一個(gè)參考框架。無監(jiān)督機(jī)器學(xué)習(xí)中的聚類分析能夠從紛繁復(fù)雜的數(shù)據(jù)中提取有用信息，刻畫多維特征的“相似性”和“關(guān)聯(lián)性”，再借助網(wǎng)絡(luò)分析的視角，可以進(jìn)一步考察數(shù)據(jù)變量間的微觀結(jié)構(gòu)和運(yùn)動狀態(tài)。本文參考scikit-learn官方網(wǎng)站示例，對上證50指數(shù)成分股的“共波動”結(jié)構(gòu)進(jìn)行了可視化分析，為大家深入學(xué)習(xí)機(jī)器學(xué)習(xí)拋磚引玉。關(guān)于網(wǎng)絡(luò)分析方面，Python有個(gè)很好用的第三方庫——networkx，可以畫出各種精美的網(wǎng)絡(luò)分析圖，感興趣的讀者可以進(jìn)一步了解。
參考資料：
scikit-learn官方網(wǎng)站案例：https://scikit-learn.org/stable/auto_examples/applications/plot_stock_market.html?highlight=plot%20stock%20market
生苦短，我用 Python
如果我的文章對您有幫助，請關(guān)注支持下作者的公眾號：極客挖掘機(jī)，您的關(guān)注，是對小編堅(jiān)持原創(chuàng)的最大鼓勵：）
前文傳送門：
小白學(xué) Python 爬蟲（1）：開篇
小白學(xué) Python 爬蟲（2）：前置準(zhǔn)備（一）基本類庫的安裝
小白學(xué) Python 爬蟲（3）：前置準(zhǔn)備（二）Linux基礎(chǔ)入門
小白學(xué) Python 爬蟲（4）：前置準(zhǔn)備（三）Docker基礎(chǔ)入門
小白學(xué) Python 爬蟲（5）：前置準(zhǔn)備（四）數(shù)據(jù)庫基礎(chǔ)
小白學(xué) Python 爬蟲（6）：前置準(zhǔn)備（五）爬蟲框架的安裝
小白學(xué) Python 爬蟲（7）：HTTP 基礎(chǔ)
小白學(xué) Python 爬蟲（8）：網(wǎng)頁基礎(chǔ)
小白學(xué) Python 爬蟲（9）：爬蟲基礎(chǔ)
小白學(xué) Python 爬蟲（10）：Session 和 Cookies
小白學(xué) Python 爬蟲（11）：urllib 基礎(chǔ)使用（一）
小白學(xué) Python 爬蟲（12）：urllib 基礎(chǔ)使用（二）
小白學(xué) Python 爬蟲（13）：urllib 基礎(chǔ)使用（三）
小白學(xué) Python 爬蟲（14）：urllib 基礎(chǔ)使用（四）
小白學(xué) Python 爬蟲（15）：urllib 基礎(chǔ)使用（五）
小白學(xué) Python 爬蟲（16）：urllib 實(shí)戰(zhàn)之爬取妹子圖
小白學(xué) Python 爬蟲（17）：Requests 基礎(chǔ)使用
小白學(xué) Python 爬蟲（18）：Requests 進(jìn)階操作
小白學(xué) Python 爬蟲（19）：Xpath 基操
小白學(xué) Python 爬蟲（20）：Xpath 進(jìn)階
小白學(xué) Python 爬蟲（21）：解析庫 Beautiful Soup（上）
小白學(xué) Python 爬蟲（22）：解析庫 Beautiful Soup（下）
小白學(xué) Python 爬蟲（23）：解析庫 pyquery 入門
小白學(xué) Python 爬蟲（24）：2019 豆瓣電影排行
上一篇的實(shí)戰(zhàn)寫到最后沒有用到頁面元素解析，感覺有點(diǎn)小遺憾，不過最后的電影列表還是挺香的，真的推薦一看。
本次選題是先寫好代碼再寫的文章，絕對可以用到頁面元素解析，并且還需要對網(wǎng)站的數(shù)據(jù)加載有一定的分析，才能得到最終的數(shù)據(jù)，并且小編找的這兩個(gè)數(shù)據(jù)源無 ip 訪問限制，質(zhì)量有保證，絕對是小白練手的絕佳之選。
鄭重聲明： 本文僅用于學(xué)習(xí)等目的。
首先要爬取股票數(shù)據(jù)，肯定要先知道有哪些股票吧，這里小編找到了一個(gè)網(wǎng)站，這個(gè)網(wǎng)站上有股票的編碼列表：https://hq.gucheng.com/gpdmylb.html 。
打開 Chrome 的開發(fā)者模式，將股票代碼一個(gè)一個(gè)選出來吧。具體過程小編就不貼了，各位同學(xué)自行實(shí)現(xiàn)。
我們可以將所有的股票代碼存放在一個(gè)列表中，剩下的就是找一個(gè)網(wǎng)站，循環(huán)的去將每一只股票的數(shù)據(jù)取出來咯。
這個(gè)網(wǎng)站小編已經(jīng)找好了，是同花順，鏈接： http://stockpage.10jqka.com.cn/000001/ 。
想必各位聰明的同學(xué)已經(jīng)發(fā)現(xiàn)了，這個(gè)鏈接中的 000001 就是股票代碼。
我們接下來只需要拼接這個(gè)鏈接，就能源源不斷的獲取到我們想要的數(shù)據(jù)。
首先，還是先介紹一下本次實(shí)戰(zhàn)用到的請求庫和解析庫為： Requests 和 pyquery 。數(shù)據(jù)存儲最后還是落地在 Mysql 。
第一步當(dāng)然是先構(gòu)建股票代碼列表咯，我們先定義一個(gè)方法：
def get_stock_list(stockListURL):
r=requests.get(stockListURL, headers=headers)
doc=PyQuery(r.text)
list=[]
# 獲取所有 section 中 a 節(jié)點(diǎn)，并進(jìn)行迭代
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] # 將爬取信息轉(zhuǎn)換小寫
return list
將上面的鏈接當(dāng)做參數(shù)傳入，大家可以自己運(yùn)行下看下結(jié)果，小編這里就不貼結(jié)果了，有點(diǎn)長。。。
詳情的數(shù)據(jù)看起來好像是在頁面上的，但是，實(shí)際上并不在，實(shí)際最終獲取數(shù)據(jù)的地方并不是頁面，而是一個(gè)數(shù)據(jù)接口。
http://qd.10jqka.com.cn/quote.php?cate=real&type=stock&callback=showStockDate&return=json&code=000001
至于是怎么找出來，小編這次就不說，還是希望各位想學(xué)爬蟲的同學(xué)能自己動動手，去尋找一下，多找?guī)状?，自然就摸到門路了。
現(xiàn)在數(shù)據(jù)接口有了，我們先看下返回的數(shù)據(jù)吧：
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":""}}})
很明顯，這個(gè)結(jié)果并不是標(biāo)準(zhǔn)的 json 數(shù)據(jù)，但這個(gè)是 JSONP 返回的標(biāo)準(zhǔn)格式的數(shù)據(jù)，這里我們先處理下頭尾，將它變成一個(gè)標(biāo)準(zhǔn)的 json 數(shù)據(jù)，再對照這頁面的數(shù)據(jù)進(jìn)行解析，最后將分析好的值寫入數(shù)據(jù)庫中。
def getStockInfo(list, stockInfoURL):
count=0
for stock in list:
try:
url=stockInfoURL + stock
r=requests.get(url, headers=headers)
# 將獲取到的數(shù)據(jù)封裝進(jìn)字典
dict1=json.loads(r.text[14: int(len(r.text)) - 1])
print(dict1)
# 獲取字典中的數(shù)據(jù)構(gòu)建寫入數(shù)據(jù)模版
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('寫入異常')
# 遇到錯(cuò)誤繼續(xù)循環(huán)
continue
這里我們加入異常處理，因?yàn)楸敬闻廊〉臄?shù)據(jù)有些多，很有可能由于某些原因拋出異常，我們當(dāng)然不希望有異常的時(shí)候中斷數(shù)據(jù)抓取，所以這里添加異常處理繼續(xù)抓取數(shù)據(jù)。
我們將代碼稍作封裝，完成本次的實(shí)戰(zhàn)。
import requests
import re
import json
from pyquery import PyQuery
import pymysql
# 數(shù)據(jù)庫連接
def connect():
conn=pymysql.connect(host='localhost',
port=3306,
user='root',
password='password',
database='test',
charset='utf8mb4')
# 獲取操作游標(biāo)
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 節(jié)點(diǎn)，并進(jìn)行迭代
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] # 將爬取信息轉(zhuǎn)換小寫
return list
def getStockInfo(list, stockInfoURL):
count=0
for stock in list:
try:
url=stockInfoURL + stock
r=requests.get(url, headers=headers)
# 將獲取到的數(shù)據(jù)封裝進(jìn)字典
dict1=json.loads(r.text[14: int(len(r.text)) - 1])
print(dict1)
# 獲取字典中的數(shù)據(jù)構(gòu)建寫入數(shù)據(jù)模版
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('寫入異常')
# 遇到錯(cuò)誤繼續(xù)循環(huán)
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()
最終小編耗時(shí) 15 分鐘左右，成功抓取數(shù)據(jù) 4600+ 條，結(jié)果就不展示了。
本系列的所有代碼小編都會放在代碼管理倉庫 Github 和 Gitee 上，方便大家取用。
示例代碼-Github
示例代碼-Gitee
*請認(rèn)真填寫需求信息，我們會在24小時(shí)內(nèi)與您取得聯(lián)系。
