轉(zhuǎn)載的別人的資料，yuzhucu@CSDN
# -*- coding:utf-8 -*-
####################################################################################################################
'''
程序：Wind股票數(shù)據(jù)下載
功能：從Wind終端或者Wind資訊量化接口個(gè)人免費(fèi)版中下載股票相關(guān)數(shù)據(jù)，保存至本地MySQL數(shù)據(jù)庫(kù)，以進(jìn)一步加工處理和分析
創(chuàng)建時(shí)間：2016/01/15 V1.01 創(chuàng)建版本，Python2.7
更新歷史：2017/01/06 V1.02 從本地文件讀取股票代碼列表;升級(jí)到Python3.5版本
2017/01/07 V1.03 封裝為函數(shù)，便于調(diào)試和代碼管理
2017/01/08 V1.04 封裝為類(lèi)，為后續(xù)完善功能準(zhǔn)備。自動(dòng)從Wind中獲取股票列表，獨(dú)立運(yùn)行；增加日志和參數(shù)處理
環(huán)境和類(lèi)庫(kù)：使用Python 3.5及第三方庫(kù)pandas、WindPy、sqlalchemy
數(shù)據(jù)庫(kù)：MySQL 5.7.16
Wind資訊量化接口 個(gè)人版（免費(fèi)），可從Wind官網(wǎng)或大獎(jiǎng)?wù)戮W(wǎng)站下載安裝，注冊(cè)即可使用
作者：yuzhucu
'''
####################################################################################################################
import pandas as pd
from WindPy import *
from sqlalchemy import create_engine
import datetime,time
import os
class WindStock():
def getCurrentTime(self):
# 獲取當(dāng)前時(shí)間
return time.strftime('[%Y-%m-%d %H:%M:%S]', time.localtime(time.time()))
def AStockHisData(self,symbols,start_date,end_date,step=0):
'''
逐個(gè)股票代碼查詢(xún)行情數(shù)據(jù)
wsd代碼可以借助 WindNavigator自動(dòng)生成copy即可使用;時(shí)間參數(shù)不設(shè)，默認(rèn)取當(dāng)前日期，可能是非交易日沒(méi)數(shù)據(jù);
只有一個(gè)時(shí)間參數(shù)時(shí)，默認(rèn)作為為起始時(shí)間，結(jié)束時(shí)間默認(rèn)為當(dāng)前日期；如設(shè)置兩個(gè)時(shí)間參數(shù)則依次為起止時(shí)間
'''
print(self.getCurrentTime(),": Download A Stock Starting:")
for symbol in symbols:
w.start()
try:
#stock=w.wsd(symbol,'trade_code,open,high,low,close,volume,amt',start_date,end_date)
'''
wsd代碼可以借助 WindNavigator自動(dòng)生成copy即可使用;
時(shí)間參數(shù)不設(shè)，默認(rèn)取當(dāng)前日期，可能是非交易日沒(méi)數(shù)據(jù);
只有一個(gè)時(shí)間參數(shù)，默認(rèn)為起始時(shí)間到最新；如設(shè)置兩個(gè)時(shí)間參數(shù)則依次為起止時(shí)間
'''
stock=w.wsd(symbol, "trade_code,open,high,low,close,pre_close,volume,amt,dealnum,chg,pct_chg,vwap, adjfactor,close2,turn,free_turn,oi,oi_chg,pre_settle,settle,chg_settlement,pct_chg_settlement, lastradeday_s,last_trade_day,rel_ipo_chg,rel_ipo_pct_chg,susp_reason,close3, pe_ttm,val_pe_deducted_ttm,pe_lyr,pb_lf,ps_ttm,ps_lyr,dividendyield2,ev,mkt_cap_ard,pb_mrq,pcf_ocf_ttm,pcf_ncf_ttm,pcf_ocflyr,pcf_nflyr,trade_status", start_date,end_date)
index_data = pd.DataFrame()
index_data['trade_date']=stock.Times
stock.Data[0]=symbol
index_data['stock_code']=stock.Data[0]
#index_data['stock_code'] =symbol
index_data['open'] =stock.Data[1]
index_data['high'] =stock.Data[2]
index_data['low'] =stock.Data[3]
index_data['close']=stock.Data[4]
index_data['pre_close']=stock.Data[5]
index_data['volume']=stock.Data[6]
index_data['amt']=stock.Data[7]
index_data['dealnum']=stock.Data[8]
index_data['chg']=stock.Data[9]
index_data['pct_chg']=stock.Data[10]
#index_data['pct_chg']=index_data['pct_chg']/100
index_data['vwap']=stock.Data[11]
index_data['adj_factor']=stock.Data[12]
index_data['close2']=stock.Data[13]
index_data['turn']=stock.Data[14]
index_data['free_turn']=stock.Data[15]
index_data['oi']=stock.Data[16]
index_data['oi_chg']=stock.Data[17]
index_data['pre_settle']=stock.Data[18]
index_data['settle']=stock.Data[19]
index_data['chg_settlement']=stock.Data[20]
index_data['pct_chg_settlement']=stock.Data[21]
index_data['lastradeday_s']=stock.Data[22]
index_data['last_trade_day']=stock.Data[23]
index_data['rel_ipo_chg']=stock.Data[24]
index_data['rel_ipo_pct_chg']=stock.Data[25]
index_data['susp_reason']=stock.Data[26]
index_data['close3']=stock.Data[27]
index_data['pe_ttm']=stock.Data[28]
index_data['val_pe_deducted_ttm']=stock.Data[29]
index_data['pe_lyr']=stock.Data[30]
index_data['pb_lf']=stock.Data[31]
index_data['ps_ttm']=stock.Data[32]
index_data['ps_lyr']=stock.Data[33]
index_data['dividendyield2']=stock.Data[34]
index_data['ev']=stock.Data[35]
index_data['mkt_cap_ard']=stock.Data[36]
index_data['pb_mrq']=stock.Data[37]
index_data['pcf_ocf_ttm']=stock.Data[38]
index_data['pcf_ncf_ttm']=stock.Data[39]
index_data['pcf_ocflyr']=stock.Data[40]
index_data['pcf_ncflyr']=stock.Data[41]
index_data['trade_status']=stock.Data[42]
index_data['data_source']='Wind'
index_data['created_date']=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
index_data['updated_date']=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
index_data = index_data[index_data['open'] > 0]
#index_data.fillna(0)
try:
index_data.to_sql('stock_daily_data',engine,if_exists='append');
except Exception as e:
#如果寫(xiě)入數(shù)據(jù)庫(kù)失敗，寫(xiě)入日志表，便于后續(xù)分析處理
error_log=pd.DataFrame()
error_log['trade_date']=stock.Times
error_log['stock_code']=stock.Data[0]
error_log['start_date']=start_date
error_log['end_date']=end_date
error_log['status']=None
error_log['table']='stock_daily_data'
error_log['args']='Symbol: '+symbol+' From '+start_date+' To '+end_date
error_log['error_info']=e
error_log['created_date']=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
error_log.to_sql('stock_error_log',engine,if_exists='append')
print ( self.getCurrentTime(),": SQL Exception :%s" % (e) )
continue
w.start()
except Exception as e:
#如果讀取處理失敗，可能是網(wǎng)絡(luò)中斷、頻繁訪(fǎng)問(wèn)被限、歷史數(shù)據(jù)缺失等原因。寫(xiě)入相關(guān)信息到日志表，便于后續(xù)補(bǔ)充處理
error_log=pd.DataFrame()
error_log['trade_date']=stock.Times
error_log['stock_code']=stock.Data[0]
error_log['start_date']=start_date
error_log['end_date']=end_date
error_log['status']=None
error_log['table']='stock_daily_data'
error_log['args']='Symbol: '+symbol+' From '+start_date+' To '+end_date
error_log['error_info']=e
error_log['created_date']=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
error_log.to_sql('stock_error_log',engine,if_exists='append')
print ( self.getCurrentTime(),":index_data %s : Exception :%s" % (symbol,e) )
time.sleep(sleep_time)
w.start()
continue
print(self.getCurrentTime(),": Downloading [",symbol,"] From "+start_date+" to "+end_date)
print(self.getCurrentTime(),": Download A Stock Has Finished .")
def getAStockCodesFromCsv(self):
'''
獲取股票代碼清單，鏈接數(shù)據(jù)庫(kù)
'''
file_path=os.path.join(os.getcwd(),'Stock.csv')
stock_code = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk')
Code=stock_code.code
return Code
def getAStockCodesWind(end_date=time.strftime('%Y%m%d',time.localtime(time.time()))):
'''
通過(guò)wset數(shù)據(jù)集獲取所有A股股票代碼，深市代碼為股票代碼+SZ后綴，滬市代碼為股票代碼+SH后綴。
如設(shè)定日期參數(shù)，則獲取參數(shù)指定日期所有A股代碼，不指定日期參數(shù)則默認(rèn)為當(dāng)前日期
:return: 指定日期所有A股代碼，不指定日期默認(rèn)為最新日期
'''
w.start()
#加日期參數(shù)取最指定日期股票代碼
#stockCodes=w.wset("sectorconstituent","date="+end_date+";sectorid=a001010100000000;field=wind_code")
#不加日期參數(shù)取最新股票代碼
stockCodes=w.wset("sectorconstituent","sectorid=a001010100000000;field=wind_code")
return stockCodes.Data[0]
#return stockCodes
def main():
'''
主調(diào)函數(shù)，可以通過(guò)參數(shù)調(diào)整實(shí)現(xiàn)分批下載
'''
global engine,sleep_time,symbols
sleep_time=5
windStock=WindStock()
engine = create_engine('mysql://root:root@localhost/invest?charset=utf8')
#start_date='20100101'
#end_date='20131231'
#symbols=windStock.getAStockCodesFromCsv()#通過(guò)文件獲取股票代碼
#symbols=windStock.getAStockCodesWind()
#通過(guò)Wind API獲取股票代碼,默認(rèn)取最新的，可以指定取歷史某一日所有A股代碼
#symbols=['000001.SZ', '000002.SZ', '000004.SZ']#通過(guò)直接賦值獲取股票代碼用于測(cè)試
#print (symbols)
#windStock.AStockHisData(symbols,start_date,end_date)
for i in range(2013,1990,-1):
start_date=str(i)+'0101'
end_date=str(i)+'1231'
print (start_date,end_date,'Starting')
symbols=windStock.getAStockCodesWind()
windStock.AStockHisData(symbols,start_date,end_date)
print (start_date,end_date,'Finished')
def test():
'''
測(cè)試腳本，新增和優(yōu)化功能時(shí)使用
'''
symbol='000001.SZ'
start_date='20170101'
end_date='20170109'
#w.start();
#stock=w.wsd(symbol,'trade_code,open,high,low,close')
#stock=w.wsd(symbol, "trade_status,open,high,low,close,pre_close,volume,amt,dealnum,chg,pct_chg,vwap, adjfactor,close2,turn,free_turn,oi,oi_chg,pre_settle,settle,chg_settlement,pct_chg_settlement, lastradeday_s,last_trade_day,rel_ipo_chg,rel_ipo_pct_chg,susp_reason,close3, pe_ttm,val_pe_deducted_ttm,pe_lyr,pb_lf,ps_ttm,ps_lyr,dividendyield2,ev,mkt_cap_ard,pb_mrq,pcf_ocf_ttm,pcf_ncf_ttm,pcf_ocflyr,pcf_nflyr", start_date,end_date)
#stock=w.wsd("000001.SZ", "pre_close,open,high,low,close,volume,amt,dealnum,chg,pct_chg,vwap,adjfactor,close2,turn,free_turn,oi,oi_chg,pre_settle,settle,chg_settlement,pct_chg_settlement,lastradeday_s,last_trade_day,rel_ipo_chg,rel_ipo_pct_chg,trade_status,susp_reason,close3", "2016-12-09", "2017-01-07", "adjDate=0")
#print (stock)
for i in range(2014,1990,-1):
start_date=str(i)+'0101'
end_date=str(i)+'1231'
print (start_date,end_date)
if __name__ == "__main__":
main()
