Python获取股票历史数据和收盘数据的代码实现
各种股票软件，例如通达信、同花顺、大智慧，都可以实时查看股票价格和走势，做一些简单的选股和定量分析，但是如果你想做更复杂的分析，例如回归分析、关联分析等就有点捉襟见肘，所以最好能够获取股票历史及实时数据并存储到，然后再通过其他工具，例如SPSS、SAS、EXCEL或者其他高级语言连接数据库获取股票数据进行定量分析，这样就能实现更多目的了。
& & & 为此，首先需要找到可以获取股票数据的接口，新浪、雅虎、腾讯等都有接口可以实时获取股票数据，历史数据选择了雅虎接口，收盘数据选择了腾讯接口。
& & （1）项目结构
wKioL1YyHhfTV5ZQAAE6J6--0Gg957.jpg
& & （2）数据库连接池
& & &connectionpool.py
#-*- coding: UTF-8 -*-
'''
create a connection pool
'''
from DBUtils import PooledDB
import MySQLdb
import string
maxconn = 30
#最大连接数
mincached = 10
#最小空闲连接
maxcached = 20
#最大空闲连接
maxshared = 30
#最大共享连接
connstring=&root#root#127.0.0.1#3307#pystock#utf8& #数据库地址
dbtype = &&
#选择mysql作为存储数据库
def createConnectionPool(connstring, dbtype):
db_conn = connstring.split(&#&);
if dbtype=='mysql':
pool = PooledDB.PooledDB(MySQLdb, user=db_conn[0],passwd=db_conn[1],host=db_conn[2],port=string.atoi(db_conn[3]),db=db_conn[4],charset=db_conn[5], mincached=mincached,maxcached=maxcached,maxshared=maxshared,maxconnections=maxconn)
return pool
except Exception, e:
raise Exception,'conn datasource Excepts,%s!!!(%s).'%(db_conn[2],str(e))
return None
pool = createConnectionPool(connstring, dbtype)
& & （3）数据库操作
& & &DBOperator.py
#-*- coding: UTF-8 -*-
'''
Created on
@author: Casey
'''
import MySQLdb
from stockmining.stocks.setting import LoggerFactory
import connectionpool
class DBOperator(object):
def __init__(self):
self.logger = LoggerFactory.getLogger('DBOperator')
#self.conn = None
def connDB(self):
#self.conn=MySQLdb.connect(host=&127.0.0.1&,user=&root&,passwd=&root&,db=&pystock&,port=3307,charset=&utf8&)
#连接池中获取连接
self.conn=connectionpool.pool.connection()
return self.conn
def closeDB(self):
if(self.conn != None):
self.conn.close()
def insertIntoDB(self, table, dict):
if(self.conn != None):
cursor = self.conn.cursor()
raise MySQLdb.Error('No connection')
sql = &insert into & + table + &(&
param = []
for key in dict:
sql += key + ','
param.append(dict.get(key))
param = tuple(param)
sql = sql[:-1] + &) values(&
for i in range(len(dict)):
sql += &%s,&
sql = sql[:-1] + &)&
self.logger.debug(sql % param)
n = cursor.execute(sql, param)
cursor.close()
except MySQLdb.Error,e:
self.logger.error(&Mysql Error %d: %s& % (e.args[0], e.args[1]))
self.conn.rollback()
def execute(self, sql):
if(self.conn != None):
cursor = self.conn.cursor()
raise MySQLdb.Error('No connection')
n = cursor.execute(sql)
except MySQLdb.Error,e:
self.logger.error(&Mysql Error %d: %s& % (e.args[0], e.args[1]))
def findBySQL(self, sql):
if(self.conn != None):
cursor = self.conn.cursor()
raise MySQLdb.Error('No connection')
cursor.execute(sql)
rows = cursor.fetchall()
return rows
except MySQLdb.Error,e:
self.logger.error(&Mysql Error %d: %s& % (e.args[0], e.args[1]))
def findByCondition(self, table, fields, wheres):
if(self.conn != None):
cursor = self.conn.cursor()
raise MySQLdb.Error('No connection')
sql = &select &
for field in fields:
sql += field + &,&
sql = sql[:-1] + & from & + table + & where &
param = []
values = ''
for where in wheres:
sql += where.key + &='%s' and &
param.append(where.value)
param = tuple(param)
self.logger.debug(sql)
n = cursor.execute(sql[:-5] % param)
cursor.close()
except MySQLdb.Error,e:
self.logger.error(&Mysql Error %d: %s& % (e.args[0], e.args[1]))
& & （4）日志
& &LoggerFactory.py
#-*- coding: UTF-8 -*-
'''
Created on
@author: Casey
'''
import logging
import time
'''
'''
def getLogger(name):
now = time.strftime('%Y-%m-%d %H:%M:%S')
logging.basicConfig(
= logging.DEBUG,
= now +& : & + name + ' LINE %(lineno)-4d
%(levelname)-8s %(message)s',
= '%m-%d %H:%M',
filename =
&d:\\stocks\stock.log&,
filemode = 'w');
console = logging.StreamHandler();
console.setLevel(logging.DEBUG);
formatter = logging.Formatter(name + ': LINE %(lineno)-4d : %(levelname)-8s %(message)s');
console.setFormatter(formatter);
logger = logging.getLogger(name)
logger.addHandler(console);
return logger
if __name__ == '__main__':
getLogger(&www&).debug(&www&)
& &（5）获取股票历史数据
& & & 采用雅虎的接口：/table.csv?s=&string&&a=&int&&b=&int&&c=&int&&d=&int&&e=&int&&f=&int&&g=d&ignore=.csv
& & 参 数：s & 股票名称&
& & & & & &a & 起始时间，月&
& & & & & &b & 起始时间，日&
& & & & & &c & 起始时间，年&
& & & & & &d & 结束时间，月&
& & & & & &e & 结束时间，日&
& & & & & &f & 结束时间，年&
& & & & & &g& 时间周期。
& & & & & （一定注意月份参数，其值比真实数据-1。如需要9月数据，则写为08。）
& & 示例 查询浦发银行 & 之间日线数据
& & /table.csv?s=600000.SS&a=08&b=25&c=2010&d=09&e=8&f=2010&g=d
& & &Date,Open,High,Low,Close,Volume,Adj Close
& & ,12.37,12.99,12.32,12.95,.95
& & ,12.20,12.69,12.12,12.48,.48
& & ,12.92,12.92,12.57,12.58,.58
& & ,13.00,13.02,12.89,12.94,.94
& &因为数据量比较大，需要跑很久，所以也可以考虑多线程模式来获取相关数据，单线程模式：
#-*- coding: UTF-8 -*-
'''
Created on
@author: Casey
'''
import urllib
import sys
from setting import params
import urllib2
from db import *
dbOperator = DBOperator()
table = &stock_quote_yahoo&
'''查找指定日期股票流量'''
def isStockExitsInDate(table, stock, date):
sql = &select * from & + table + & where code = '%d' and date='%s'& % (stock, date)
n = dbOperator.execute(sql)
if n &= 1:
return True
def getHistoryStockData(code, dataurl):
r = urllib2.Request(dataurl)
stdout = urllib2.urlopen(r, data=None, timeout=3)
except Exception,e:
print &&&&&&& Exception: & +str(e)
return None
stdoutInfo = stdout.read().decode(params.codingtype).encode('utf-8')
tempData = stdoutInfo.replace('&', '')
stockQuotes = []
if tempData.find('404') != -1:
stockQuotes = tempData.split(&\n&)
stockDetail = {}
for stockQuote in stockQuotes:
stockInfo = stockQuote.split(&,&)
if len(stockInfo) == 7 and stockInfo[0]!='Date':
if not isStockExitsInDate(table, code, stockInfo[0]):
stockDetail[&date&] = stockInfo[0]
stockDetail[&open&]
= stockInfo[1]
stockDetail[&high&]
= stockInfo[2]
stockDetail[&low&]
= stockInfo[3]
stockDetail[&close&] = stockInfo[4]
stockDetail[&volume&] = stockInfo[5]
stockDetail[&adj_close&] = stockInfo[6] #收盘adj价格
stockDetail[&code&] = code
dbOperator.insertIntoDB(table, stockDetail)
result = tempData
except Exception as err:
print &&&&&&& Exception: & + str(dataurl) + & & + str(err)
return result
def get_stock_history():
#沪市历史数据
for code in range(2100):
dataUrl = &/table.csv?s=%d.SS&a=01&b=01&c=2005&d=01&e=01&f=2015&g=d& % code
print getHistoryStockData(code, dataUrl )
#深市历史数据
for code in range(1, 1999):
dataUrl = &/table.csv?s=%06d.SZ&a=01&b=01&c=2005&d=01&e=01&f=2015&g=d& % code
print getHistoryStockData(code, dataUrl)
#中小板股票
for code in range():
dataUrl = &/table.csv?s=%06d.SZ&a=01&b=01&c=2005&d=01&e=01&f=2015&g=d& % code
print getHistoryStockData(code, dataUrl)
for code in range(0400):
dataUrl = &/table.csv?s=%d.SZ&a=01&b=01&c=2005&d=01&e=01&f=2015&g=d& % code
print getHistoryStockData(code, dataUrl)
def main():
&main function&
dbOperator.connDB()
get_stock_history()
dbOperator.closeDB()
if __name__ == '__main__':
& & &（6）获取实时价格和现金流数据
& & & A：实时价格数据采用腾讯的接口：沪市：/q=sh&int&，深市：/q=sz&int&
& & & 如获取平安银行的股票实时数据：/q=sz000001，会返回一个包含股票数据的字符串：
v_sz~平安银行~.27~11.27~11.30~512~.27~93~11.26~
:24/11.27/3146/S/48|14:56:59/11.26/15/S/|
14:56:56/11.25/404/S/83|14:56:54/11.26/173/B/80|14:56:51
/11.26/306/B/77|14:56:47/11.26/16/B/~
42~0.00~0.00~11.36~11.25~
11.26/285045~
83~0.27~7.38~~11.36~11.25~0.98~2.59~1.03~12.40~10.14~&;
& & &数据比较多，比较有用的是：1-名称；2-代码；3-价格；4-昨日收盘；5-今日开盘；6-交易量（手）；7-外盘；8-内盘；9-买一；10-买一量；11-买二；12-买二量；13-买三；14-买三量；15-买四；16-买四量；17-买五；18-买五量；19-卖一；20-卖一量；21-卖二；22-卖二量；23-卖三；24-卖三量；25-卖四；26-卖四量；27-卖五；28-卖五量；30-时间；31-涨跌；32-涨跌率；33-最高价；34-最低价；35-成交量（万）；38-换手率；39-市盈率；42-振幅；43-流通市值；44-总市值；45-市净率
& & & &B：现金流数据仍然采用腾讯接口：沪市：/q=ff_sh&int&，深市：/q=ff_sz&int&
& & & 例如平安银行的现金流数据/q=ff_sz000001：
v_ff_sz000001=&sz62.20~74.20~-8.31~46.65~2974.22~
8.31~502.0~~平安银行~.20^39358.20~
13.50^.80^.30^71743.10&;
& & & & & 比较重要的：1-主力流入；2-主力流出；3-主力净流量；4-主力流入/主力总资金；5-散户流入；6-散户流出；7-散户净流量；8-散户流入/散户总资金；9-总资金流量；12-名字；13-日期
& & & & & &采用多线程、数据库连接池实现股票实时价格和现金流数据的获取：
#-*- coding: UTF-8 -*-
'''
Created on 日
@author: Casey
'''
import time
import threading
'''
上证编码：'; .. ';
深圳编码：'; .. ';
中小板：'; .. ';
创业板：'; .. ';
'''
import urllib2
from datetime import date
from db import *
from setting import *
class StockTencent(object):
__stockTables = {'cash':'stock_cash_tencent','quotation':'stock_quotation_tencent'}
'''初始化'''
def __init__(self):
self.__logger = LoggerFactory.getLogger('StockTencent')
self.__dbOperator = DBOperator()
def main(self):
self.__dbOperator.connDB()
threading.Thread(target = self.getStockCash()).start()
threading.Thread(target = self.getStockQuotation()).start()
self.__dbOperator.closeDB()
'''查找指定日期股票流量'''
def __isStockExitsInDate(self, table, stock, date):
sql = &select * from & + table + & where code = '%s' and date='%s'& % (stock, date)
n = self.__dbOperator.execute(sql)
if n &= 1:
return True
'''获取股票资金流明细'''
def __getStockCashDetail(self, dataUrl):
tempData = self.__getDataFromUrl(dataUrl)
if tempData == None:
time.sleep(10)
tempData = self.__getDataFromUrl(dataUrl)
return False
#解析资金流向数据
stockCash = {}
stockInfo = tempData.split('~')
if len(stockInfo) & 13: return
if len(stockInfo) != 0 and stockInfo[0].find('pv_none') == -1:
table = self.__stockTables['cash']
code = stockInfo[0].split('=')[1][2:]
date = stockInfo[13]
if not self.__isStockExitsInDate(table, code, date):
stockCash['code'] = stockInfo[0].split('=')[1][2:]
stockCash['main_in_cash']
= stockInfo[1]
stockCash['main_out_cash']
= stockInfo[2]
stockCash['main_net_cash']
= stockInfo[3]
stockCash['main_net_rate']
= stockInfo[4]
stockCash['private_in_cash']
= stockInfo[5]
stockCash['private_out_cash'] = stockInfo[6]
stockCash['private_net_cash'] = stockInfo[7]
stockCash['private_net_rate'] = stockInfo[8]
stockCash['total_cash']
= stockInfo[9]
stockCash['name']
= stockInfo[12].decode('utf8')
stockCash['date']
= stockInfo[13]
#插入数据库
self.__dbOperator.insertIntoDB(table, stockCash)
'''获取股票交易信息明细'''
def getStockQuotationDetail(self, dataUrl):
tempData = self.__getDataFromUrl(dataUrl)
if tempData == None:
time.sleep(10)
tempData = self.__getDataFromUrl(dataUrl)
return False
stockQuotation = {}
stockInfo = tempData.split('~')
if len(stockInfo) & 45: return
if len(stockInfo) != 0 and stockInfo[0].find('pv_none') ==-1 and stockInfo[3].find('0.00') == -1:
table = self.__stockTables['quotation']
code = stockInfo[2]
date = stockInfo[30]
if not self.__isStockExitsInDate(table, code, date):
stockQuotation['code']
= stockInfo[2]
stockQuotation['name']
= stockInfo[1].decode('utf8')
stockQuotation['price'] = stockInfo[3]
stockQuotation['yesterday_close']
= stockInfo[4]
stockQuotation['today_open']
= stockInfo[5]
stockQuotation['volume']
= stockInfo[6]
stockQuotation['outer_sell']
= stockInfo[7]
stockQuotation['inner_buy']
= stockInfo[8]
stockQuotation['buy_one']
= stockInfo[9]
stockQuotation['buy_one_volume']
= stockInfo[10]
stockQuotation['buy_two']
= stockInfo[11]
stockQuotation['buy_two_volume']
= stockInfo[12]
stockQuotation['buy_three']
= stockInfo[13]
stockQuotation['buy_three_volume']
= stockInfo[14]
stockQuotation['buy_four']
= stockInfo[15]
stockQuotation['buy_four_volume']
= stockInfo[16]
stockQuotation['buy_five']
= stockInfo[17]
stockQuotation['buy_five_volume']
= stockInfo[18]
stockQuotation['sell_one']
= stockInfo[19]
stockQuotation['sell_one_volume']
= stockInfo[20]
stockQuotation['sell_two']
= stockInfo[22]
stockQuotation['sell_two_volume']
= stockInfo[22]
stockQuotation['sell_three']
= stockInfo[23]
stockQuotation['sell_three_volume'] = stockInfo[24]
stockQuotation['sell_four']
= stockInfo[25]
stockQuotation['sell_four_volume']
= stockInfo[26]
stockQuotation['sell_five']
= stockInfo[27]
stockQuotation['sell_five_volume']
= stockInfo[28]
stockQuotation['datetime']
= stockInfo[30]
stockQuotation['updown']
= stockInfo[31]
stockQuotation['updown_rate']
= stockInfo[32]
stockQuotation['heighest_price']
= stockInfo[33]
stockQuotation['lowest_price']
= stockInfo[34]
stockQuotation['volume_amout']
= stockInfo[35].split('/')[2]
stockQuotation['turnover_rate']
= stockInfo[38]
stockQuotation['pe_rate']
= stockInfo[39]
stockQuotation['viberation_rate']
= stockInfo[42]
stockQuotation['circulated_stock']
= stockInfo[43]
stockQuotation['total_stock']
= stockInfo[44]
stockQuotation['pb_rate']
= stockInfo[45]
self.__dbOperator.insertIntoDB(table, stockQuotation)
'''读取信息'''
def __getDataFromUrl(self, dataUrl):
r = urllib2.Request(dataUrl)
stdout = urllib2.urlopen(r, data=None, timeout=3)
except Exception,e:
self.__logger.error(&&&&&&& Exception: & +str(e))
return None
stdoutInfo = stdout.read().decode(params.codingtype).encode('utf-8')
tempData = stdoutInfo.replace('&', '')
self.__logger.debug(tempData)
return tempData
'''获取股票现金流量'''
def getStockCash(self):
self.__logger.debug(&开始:收集股票现金流信息&)
for code in range(2100):
dataUrl = &/q=ff_sh%d& % code
self.__getStockCashDetail(dataUrl)
for code in range(1, 1999):
dataUrl = &/q=ff_sz%06d& % code
self.__getStockCashDetail(dataUrl)
#中小板股票
for code in range():
dataUrl = &/q=ff_sz%06d& % code
self.__getStockCashDetail(dataUrl)
#'; .. ';
#创业板股票
for code in range(0400):
dataUrl = &/q=ff_sz%d& % code
self.__getStockCashDetail(dataUrl)
except Exception as err:
self.__logger.error(&&&&&&& Exception: & +str(code) + & & + str(err))
self.__logger.debug(&结束：股票现金流收集&)
'''获取股票交易行情数据'''
def getStockQuotation(self):
self.__logger.debug(&开始:收集股票交易行情数据&)
for code in range(2100):
dataUrl = &/q=sh%d& % code
self.getStockQuotationDetail(dataUrl)
for code in range(1, 1999):
dataUrl = &/q=sz%06d& % code
self.getStockQuotationDetail(dataUrl)
#中小板股票
for code in range():
dataUrl = &/q=sz%06d& % code
self.getStockQuotationDetail(dataUrl)
#'; .. ';
创业板股票
for code in range(0400):
dataUrl = &/q=sz%d& % code
self.getStockQuotationDetail(dataUrl)
except Exception as err:
self.__logger.error(&&&&&&& Exception: & +str(code) + & & + str(err))
self.__logger.debug(&结束:收集股票交易行情数据&)
if __name__ == '__main__':
StockTencent(). main()Python（8）
1.urllib 使用笔记
urlopen(url,data,timeout)
第一个参数url即为URL，第二个参数data是访问URL时要传送的数据，第三个timeout是设置超时时间。
第二三个参数是可以不传送的，data默认为空None，timeout默认为 socket._GLOBAL_DEFAULT_TIMEOUT
第一个参数URL是必须要传送的，在这个例子里面我们传送了百度的URL，执行urlopen方法之后，返回一个response对象，返回信息便保存在这里面。
import urllib2
url = 'http://python.org/'
def use_urllib2():
response = urllib2.urlopen('http://python.org/')
html = response.read()
except urllib2.URLError, e:
print e.reason
print len(html)
if __name__ == "__main__":
use_urllib2()
#!/usr/bin/python
# -*- coding:utf-8 -*-
# urllib2_test.py
# author: sfzoro
import urllib
import urllib2
url = '/login'
user_agent = 'Mozilla/4.0 ( MSIE 5.5; Windows NT)'
values = {'username' : 'cqc',
'password' : 'XXXX' }
headers = { 'User-Agent' : user_agent }
data = urllib.urlencode(values)
request = urllib2.Request(url, data, headers)
response = urllib2.urlopen(request)
page = response.read()
#对付防盗链，服务器会识别headers中的referer是不是它自己
headers = { 'User-Agent' : 'Mozilla/4.0 ( MSIE 5.5; Windows NT)'
'Referer':'/articles' }
import urllib2
enable_proxy = True
proxy_handler = urllib2.ProxyHandler({"http" : ':8080'})
null_proxy_handler = urllib2.ProxyHandler({})
if enable_proxy:
opener = urllib2.build_opener(proxy_handler)
opener = urllib2.build_opener(null_proxy_handler)
urllib2.install_opener(opener)
#Timeout 设置
import urllib2
response = urllib2.urlopen('', timeout=10)
response = urllib2.urlopen('', data, 10)
3.urllib2 get和post
#!/usr/bin/python
# -*- coding:utf-8 -*-
# urllib2_test.py
# author: sfzoro
import urllib
import urllib2
values['username'] = ""
values['password']="XXXX"
data = urllib.urlencode(values)
url = "http://passport.csdn.net/account/login"
geturl = url + "?"+data
request = urllib2.Request(geturl)
response = urllib2.urlopen(request)
print response.read()
#!/usr/bin/python
# -*- coding:utf-8 -*-
# urllib2_test.py
# author: sfzoro
import urllib
import urllib2
values = {"username":"","password":"XXXX"}
data = urllib.urlencode(values)
url = "https://passport.csdn.net/account/login?from=http://my.csdn.net/my/mycsdn"
request = urllib2.Request(url,data)
response = urllib2.urlopen(request)
print response.read()
4.1.Python urllib 爬取基金数据
用的2.7 的python 爬取用urllib不用再下其它的包了，不过代码多的点，下次改用Requests.
主要使用了 做ORM了，给个淘宝svn地址，大家自己下代码
如果下载不可以给我邮件，不扯了主代码
import urllib
import urllib2
import sqlite3
from collections import OrderedDict
from PraseConfig import ParseConfig
from models import Founds
def srcapydata():
configdata = ParseConfig().parse()
values = OrderedDict()
values['fundcode'] = configdata.fundcode
values['startdate'] = configdata.startdate
values['enddate'] = configdata.enddate
data = urllib.urlencode(values)
geturl = configdata.url + "?"+data
print geturl
user_agent = "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0. Safari/537.36 LBBROWSER"
referer = "http://jingzhi./DataBase/jzzs.aspx?fundcode=163412&startdate=&enddate="
accAccept = "text/html,application/xhtml+xml,application/q=0.9,image/webp,*/*;q=0.8"
headers = { 'User-Agent' : user_agent, 'Referer': referer, 'Accept':accAccept}
request = urllib2.Request(geturl, headers=headers)
response = urllib2.urlopen(request)
html = response.read()
listData = re.findall( r'&td style="text-align:"&(.*?)&/td&', html, re.M|re.I)
listBaiFengBi = re.findall( r'&td style="text-align:" class="f_.*"&(.*?)&/td&', html, re.M|re.I)
listBaiFengBi2 = re.findall( r'&td style="text-align:" class="end"&(.*?)&/td&', html, re.M|re.I)
timeList = []
for i in range(len(listData)):
if(i%2 == 0):
timeList.append(listData[i])
valueList = []
for i in range(len(listData)):
if(i%2 == 1):
valueList.append(listData[i])
dbPath = "G:\\2016\\coding\\trunk\\Stock\\founds_"
dbPath += configdata.fundcode
dbPath += ".db"
conn = sqlite3.connect(dbPath)
c = conn.cursor()
c.execute('''
CREATE TABLE if not exists "Founds" (
INTEGER PRIMARY KEY AUTOINCREMENT,
"release_date"
TEXT(10) NOT NULL,
"unit_price"
REAL(10) NOT NULL,
"accumulative_net_value"
"daily_growth_rate"
conn.close()
for i in range(len(timeList)):
founds = Founds()
founds.release_date = timeList[i]
founds.unit_price = valueList[i]
founds.accumulative_net_value = listBaiFengBi2[i]
founds.daily_growth_rate = listBaiFengBi[i]
founds.save()
if __name__ == "__main__":
srcapydata()
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：30244次
排名：千里之外
原创：32篇
(1)(2)(1)(1)(8)(2)(1)(1)(1)(4)(4)(3)(2)(4)(1)}