def storereportdata(self, startyear):
report = self.report.collection_names()
for r in report:
print u"删除" + r
self.report[r].drop()
x = datetime.datetime.now()
nowyear = int(x.year)
nowmonth = int(x.month)
#print nowmonth,nowyear,startyear,startmonth
for year in range(startyear, nowyear + 1):
if year != nowyear + 1:
for month in range(1, 5):
name = str(year) + "-" + str(month)
print u"正在获取数据" + str(year) + "-" + str(month)
data = ts.get_profit_data(year, month)
self.report[name].insert_many(
json.loads(data.to_json(orient='records')))
else:
session = nowmonth / 4
for month in range(1, session):
name = str(year) + "-" + str(month)
print u"正在获取数据" + str(year) + "-" + str(month)
data = ts.get_profit_data(year, month)
self.report[name].insert_many(
json.loads(data.to_json(orient='records')))
def get_returnSeason(x, Year):
#
basicdata = ts.get_stock_basics()
basicdata = basicdata.loc[x, ['name', 'outstanding']]
basicdata['code'] = basicdata.index
#profitdata=get_profit_report['code','name','roe']
returnSeasonly = np.zeros(4)
for i in range(4):
pd = ts.get_profit_data(Year, i + 1)
while pd is None:
pd = ts.get_profit_data(Year, i + 1)
pd = pd.loc[:, ['code', 'roe']]
pd = pd.dropna()
#pd.index =pd[pd.code<>np.nan]
pd.index = pd['code']
Return = pd.loc[x, ['code', 'roe']]
if len(Return) == 0:
print('空数据，不处理 ' + str(Year) + str(i))
returnSeasonly[i] = 0
break
Return['weight'] = basicdata['outstanding'] / basicdata[
'outstanding'].sum()
Return['Wreturn'] = Return['weight'] * Return['roe']
returnSeasonly[i] = Return['Wreturn'].sum()
if i > 0:
returnSeasonly[i] = returnSeasonly[i] - returnSeasonly[i - 1]
print('已处理 ' + str(Year) + str(i))
return returnSeasonly
def save_profit(y, q):
df = ts.get_profit_data(y,q)
# df = ts.get_today_all()
while df is None:
df = ts.get_profit_data(y,q)
print df
df.to_csv('profit_%s0%s.csv' % (y,q),encoding='utf-8')
def update_single_basic_ele(ele_name, retry_times):
for year in range(2001, int(year_now) + 1):
for quarter in range(1, 5):
table_name = ele_name + str(year) + '0' + str(quarter)
if not is_table_exist(conn=conn_profit,
database_name=stk_profit_data_db_name,
table_name=table_name):
"""失败达到一定次数便不再尝试"""
failure_time = 0
go_on_flag = True
while go_on_flag & (failure_time < retry_times):
try:
if ele_name == "profit":
ts.get_profit_data(year=year,quarter=quarter)\
.to_sql(con=engine_profit,
name=table_name,
if_exists='append',
schema=stk_profit_data_db_name,
index=False)
elif ele_name == "growth":
ts.get_growth_data(year=year, quarter=quarter) \
.to_sql(con=engine_growth,
name=table_name,
if_exists='append',
schema=stk_growth_data_db_name,
index=False,)
elif ele_name == "operation":
ts.get_operation_data(year=year, quarter=quarter) \
.to_sql(con=engine_operation,
name=table_name,
if_exists='append',
schema=stk_operation_data_db_name,
index=False)
elif ele_name == "debtpaying":
ts.get_debtpaying_data(year=year, quarter=quarter) \
.to_sql(con=engine_debtpaying,
name=table_name,
if_exists='append',
schema=stk_debtpaying_data_db_name,
index=False)
elif ele_name == "cashflow":
ts.get_cashflow_data(year=year, quarter=quarter) \
.to_sql(con=engine_cashflow,
name=table_name,
if_exists='append',
schema=stk_cashflow_data_db_name,
index=False)
go_on_flag = False
except:
print(table_name + "下载失败！重试！\n")
failure_time = failure_time + 1
else:
print(table_name + "已经存在！\n")
def get_profit_data(self, year, loops):
for i in range(1,loops):
print(i)
for j in year:
print(year)
try:
ts.get_profit_data(j, 4).to_csv('profit_data_%d.csv'%j, encoding='utf-8')
print(j)
year.remove(j)
except: pass
def basic_information():
ts.get_cashflow_data(2017, 1).to_sql('cash_flow',
engine,
if_exists='append')
ts.get_debtpaying_data(2017, 1).to_sql('debtpaying',
engine,
if_exists='append')
ts.get_growth_data(2017, 1).to_sql('growth', engine, if_exists='append')
ts.get_operation_data(2017, 1).to_sql('operation',
engine,
if_exists='append')
ts.get_profit_data(2017, 1).to_sql('profit', engine, if_exists='append')
ts.get_report_data(2017, 2).to_sql('report', engine, if_exists='append')
print('basic information over ....')
def _fetch_finance():
for year in range(2004, 2018):
set_year = lambda x: str(year) + '-' + x
for quarter in range(1, 5):
print(year, ' year ', 'quarter ', quarter)
rep = ts.get_report_data(
year, quarter)[['code', 'eps', 'bvps', 'epcf', 'report_date']]
pro = ts.get_profit_data(year, quarter)[[
'code', 'roe', 'net_profit_ratio', 'gross_profit_rate',
'net_profits', 'business_income', 'bips'
]]
ope = ts.get_operation_data(year, quarter)[[
'code', 'arturnover', 'arturndays', 'inventory_turnover',
'currentasset_turnover', 'currentasset_days'
]]
gro = ts.get_growth_data(
year, quarter)[['code', 'mbrg', 'nprg', 'nav', 'epsg', 'seg']]
deb = ts.get_debtpaying_data(year, quarter)[[
'code', 'currentratio', 'quickratio', 'cashratio', 'icratio',
'sheqratio', 'adratio'
]]
cas = ts.get_cashflow_data(year, quarter)[[
'code', 'cf_sales', 'rateofreturn', 'cf_nm', 'cf_liabilities',
'cashflowratio'
]]
rep.rename(columns={'report_date': 'date'}, inplace=True)
rep['date'] = rep['date'].apply(set_year)
rep = rep.merge(pro, on='code', how='left')
rep = rep.merge(ope, on='code', how='left')
rep = rep.merge(gro, on='code', how='left')
rep = rep.merge(deb, on='code', how='left')
rep = rep.merge(cas, on='code', how='left')
finance.insert(rep.to_dict('record'))
print(year, quarter)
def valuation_factor(year):
report = ts.get_report_data(year,4)
report = report.sort_values(by = 'code',axis = 0,ascending = True)
report = report.reset_index(drop = True)
report.to_csv("/home/yirui/Desktop/Quant/Report/%s.csv"%year, mode="w")
profit = ts.get_profit_data(year, 4)
profit = profit.sort_values(by='code', axis=0, ascending=True)
profit = profit.reset_index(drop=True)
profit.to_csv("/home/yirui/Desktop/Quant/Profit/%s.csv"%year,mode= "w")
operation = ts.get_operation_data(year,4)
operation = operation.sort_values(by='code', axis=0, ascending=True)
operation = operation.reset_index(drop=True)
operation.to_csv("/home/yirui/Desktop/Quant/Operation/%s.csv" % year, mode="w")
growth = ts.get_growth_data(year,4)
growth = growth.sort_values(by='code', axis=0, ascending=True)
growth = growth.reset_index(drop=True)
growth.to_csv("/home/yirui/Desktop/Quant/Growth/%s.csv" % year, mode="w")
debtpaying = ts.get_debtpaying_data(year,4)
debtpaying = debtpaying.sort_values(by='code', axis=0, ascending=True)
debtpaying = debtpaying.reset_index(drop=True)
debtpaying.to_csv("/home/yirui/Desktop/Quant/Debtpaying/%s.csv" % year, mode="w")
cashflow = ts.get_cashflow_data(year,4)
cashflow = cashflow.sort_values(by='code', axis=0, ascending=True)
cashflow = cashflow.reset_index(drop=True)
cashflow.to_csv("/home/yirui/Desktop/Quant/Cashflow/%s.csv" % year, mode="w")
def get_profit_data(self, year, quarter):
tsdata = ts.get_profit_data(
year=year,
quarter=quarter,
)
jsdata = To_Json(tsdata)
return jsdata
def stat_stock_profit(tmp_datetime, max_year=18):
"""
以后每年7月份取一下上年的年报即可，历史数据不必再取
经验: 19年4月份取18年的年报是不全的，所以延到7月取
"""
# cur_year = int((tmp_datetime).strftime("%Y"))
cur_year = 2005
# i = cur_year - max_year
i = 2001
MAX_RETRY_TIME = 3
retry_time = 0
while i < cur_year:
try:
data = ts.get_profit_data(i, 4)
except IOError:
data = None
if not data is None and len(data) > 0:
print("\nyear done", i)
# data = data.drop_duplicates(subset="code", keep="last")
data.insert(0, "year", [i] * len(data))
data.head(n=1)
common.insert_db(data, "ts_stock_profit", False, "`year`,`code`")
i += 1
retry_time = 0
else:
print("\nno data . stock_profit year", i)
retry_time += 1
if retry_time > MAX_RETRY_TIME:
i += 1
retry_time = 0
time.sleep(5) # 停止5秒
def get_profit(year,q):
conn = default_db()
cur = conn.cursor()
net_profits_df = ts.get_profit_data(year,q)
num = len(net_profits_df.index)
for i in net_profits_df.index:
print num,year,q
stock_id = net_profits_df.loc[i]['code']
stock_name = net_profits_df.loc[i]['name']
if q == 1:
date = '%d-01-01' % (year)
elif q == 2:
date = '%d-04-01' % (year)
elif q == 3:
date = '%d-07-01' % (year)
else:
date = '%d-10-01' % (year)
roe = net_profits_df.loc[i]['roe']
net_profit_ratio = net_profits_df.loc[i]['net_profit_ratio']
gross_profit_rate = net_profits_df.loc[i]['gross_profit_rate']
net_profits = net_profits_df.loc[i]['net_profits']
eps = net_profits_df.loc[i]['eps']
business_income = net_profits_df.loc[i]['business_income']
bips = net_profits_df.loc[i]['bips']
order = 'insert into %s ( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s)values("%s", "%s","%s","%f","%f","%f","%f","%f","%f","%f")'\
% (TABLE_NETPROFIT,NETPROFIT_STOCK_ID,NETPROFIT_STOCK_NAME,NETPROFIT_DATE,NETPROFIT_ROE,NETPROFIT_NET_PROFIT_RATIO,\
NETPROFIT_GROSS_PROFIT_RATE,NETPROFIT_NET_PROFITS,NETPROFIT_EPS,NETPROFIT_BUSINESS_INCOME,NETPROFIT_BIPS,\
stock_id,stock_name,date,roe,net_profit_ratio,gross_profit_rate,net_profits,eps,business_income,bips)
try:
cur.execute(order)
conn.commit()
except Exception, e:
print e
num -= 1
def export(exportType, datePicker):
sb = None
[year, quarter] = getYearQuarter(datePicker)
if exportType == "report":
name = "业绩报表"
sb = ts.get_report_data(year, quarter)
elif exportType == "profit":
name = "盈利能力报表"
sb = ts.get_profit_data(year, quarter)
elif exportType == "operation":
name = "营运能力报表"
sb = ts.get_operation_data(year, quarter)
elif exportType == "growth":
name = "成长能力报表"
sb = ts.get_growth_data(year, quarter)
elif exportType == "debtpaying":
name = "偿债能力报表"
sb = ts.get_debtpaying_data(year, quarter)
elif exportType == "cashflow":
name = "现金流量报表"
sb = ts.get_cashflow_data(year, quarter)
filename = quote(name + str(year) + "Q" + str(quarter) + ".xlsx")
filepath = os.path.join(basedir, app.config['UPLOAD_FOLDER'], filename)
sb.to_excel(filepath)
rtn = send_file(filepath, as_attachment=True)
rtn.headers['Content-Disposition'] += "; filename*=utf-8''%s" % (filename)
return rtn
def profit_data(self, year, quarter): #盈利能力
df = ts.get_profit_data(year, quarter)
for indexs in df.index:
code = str(df.loc[indexs, ["code"]].values[0])
name = str(df.loc[indexs, ["name"]].values[0])
roe = str(df.loc[indexs, ["roe"]].values[0])
roe = '0' if roe in ('nan', '--') else roe
net_profit_ratio = str(df.loc[indexs,
["net_profit_ratio"]].values[0])
net_profit_ratio = '0' if net_profit_ratio in (
'nan', '--') else net_profit_ratio
gross_profit_rate = str(df.loc[indexs,
["gross_profit_rate"]].values[0])
gross_profit_rate = '0' if gross_profit_rate in (
'nan', '--') else gross_profit_rate
net_profits = str(df.loc[indexs, ["net_profits"]].values[0])
net_profits = '0' if net_profits in ('nan', '--') else net_profits
eps = str(df.loc[indexs, ["eps"]].values[0])
eps = '0' if eps in ('nan', '--') else eps
business_income = str(df.loc[indexs,
["business_income"]].values[0])
business_income = '0' if business_income in (
'nan', '--') else business_income
bips = str(df.loc[indexs, ["bips"]].values[0])
bips = '0' if bips in ('nan', '--') else bips
insert = (
"insert into profit_data(code,name,roe,net_profit_ratio,gross_profit_rate,net_profits,eps,business_income,bips,year,quarter) values('"
+ code + "','" + name + "'," + roe + "," + net_profit_ratio +
"," + gross_profit_rate + "," + net_profits + "," + eps + "," +
business_income + "," + bips + ",'" + str(year) + "','" +
str(quarter) + "')")
self.dbA.updateInsertDelete(insert)
self.dbA.commit()
def get_profit(year, q):
conn = default_db()
cur = conn.cursor()
net_profits_df = ts.get_profit_data(year, q)
num = len(net_profits_df.index)
for i in net_profits_df.index:
print num, year, q
stock_id = net_profits_df.loc[i]['code']
stock_name = net_profits_df.loc[i]['name']
if q == 1:
date = '%d-01-01' % (year)
elif q == 2:
date = '%d-04-01' % (year)
elif q == 3:
date = '%d-07-01' % (year)
else:
date = '%d-10-01' % (year)
roe = net_profits_df.loc[i]['roe']
net_profit_ratio = net_profits_df.loc[i]['net_profit_ratio']
gross_profit_rate = net_profits_df.loc[i]['gross_profit_rate']
net_profits = net_profits_df.loc[i]['net_profits']
eps = net_profits_df.loc[i]['eps']
business_income = net_profits_df.loc[i]['business_income']
bips = net_profits_df.loc[i]['bips']
order = 'insert into %s ( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s)values("%s", "%s","%s","%f","%f","%f","%f","%f","%f","%f")'\
% (TABLE_NETPROFIT,NETPROFIT_STOCK_ID,NETPROFIT_STOCK_NAME,NETPROFIT_DATE,NETPROFIT_ROE,NETPROFIT_NET_PROFIT_RATIO,\
NETPROFIT_GROSS_PROFIT_RATE,NETPROFIT_NET_PROFITS,NETPROFIT_EPS,NETPROFIT_BUSINESS_INCOME,NETPROFIT_BIPS,\
stock_id,stock_name,date,roe,net_profit_ratio,gross_profit_rate,net_profits,eps,business_income,bips)
try:
cur.execute(order)
conn.commit()
except Exception, e:
print e
num -= 1
def get_basic_datas(
data_kind): #datakind 为debtpaying,growth,operation,profit,report
client = pymongo.MongoClient('localhost', 27017)
table_stock = client['stock']
sheet = table_stock[data_kind]
for year in range(STARTYEAR, ENDYEAR + 1):
try:
for season in range(1, 5):
print('getting ' + datakind + ' data at year:' + str(year) +
" season:" + str(season))
if data_kind == 'debtpaying':
tf = ts.get_debtpaying_data(year, season)
elif data_kind == 'growth':
tf = ts.get_growth_data(year, season)
elif data_kind == 'operation':
tf = ts.get_operation_data(year, season)
elif data_kind == 'profit':
tf = ts.get_profit_data(year, season)
elif data_kind == 'report':
tf = ts.get_report_data(year, season)
else:
print('Not available data type of data_kind!')
return
jsonres = json.loads(tf.to_json(orient='records'))
for j in jsonres:
sheet.insert_one(j)
except: #数据缺失，tushare接口会报网络错误
print('the year: ' + str(year) + ' lost data will begin next year')
continue
def get_profit_data(year, season):
frame = ts.get_profit_data(year, season)
table_name = 'stock_profit_' + str(year) + 'q' + str(season)
#frame.to_sql(table_name, engine)
db = MySQLdb.connect("localhost",
"root",
"123456",
'stock',
charset='utf8')
cursor = db.cursor()
createDBSql = 'create table if not exists ' + table_name + '(code varchar(10), name varchar(16), roe text, net_profit_ratio text, gross_profit_rate text, net_profits text, eps text, business_income text, bips text)'
cursor.execute(createDBSql)
for i in range(0, len(frame)):
prefix = 'insert into ' + table_name + '(code, name, roe, net_profit_ratio, gross_profit_rate, net_profits, eps, business_income, bips) select \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\' from dual where not exists (select * from ' + table_name + ' where code = \'%s\')'
sql = prefix % (frame['code'][i], frame['name'][i], str(
frame['roe'][i]), str(frame['net_profit_ratio'][i]),
str(frame['gross_profit_rate'][i]),
str(frame['net_profits'][i]), str(frame['eps'][i]),
str(frame['business_income'][i]), str(
frame['bips'][i]), frame['code'][i])
cursor.execute(sql)
db.commit()
print(frame['name'][i])
db.close()
def choosing_stock(the_year, the_quarter):
x_choosing_stock = []
x_temp_choosing_stock = []
print (the_year, the_quarter)
data_choosing_stock = ts.get_profit_data(the_year, the_quarter)
data_choosing_stock.dropna( axis=0, how='any', thresh=None, subset=None, inplace=True )
data_choosing_stock.sort_values( by="code" , ascending=True, inplace=True )
data_choosing_stock.drop_duplicates( ['code'], inplace = True )
data_choosing_stock['yield'] = np.nan
( row_choosing_stock, col_choosing_stock ) = data_choosing_stock.shape
row_choosing_stock = test_less_row ######################################################
for the_row in range(0, row_choosing_stock):
for the_col in range(2, col_choosing_stock-1):
x_temp_choosing_stock.append(data_choosing_stock.iloc[the_row, the_col])
x_choosing_stock.append(x_temp_choosing_stock)
x_choosing_stock_new = MinMax.transform(x_choosing_stock)
y_choosing_stock_predict = model.predict(x_choosing_stock_new)
x_choosing_stock = []
x_temp_choosing_stock = []
data_choosing_stock.iloc[the_row, -1] = y_choosing_stock_predict
data_choosing_stock.sort_values( by="yield", ascending=False, inplace=True )
stock_list = data_choosing_stock[['code', 'name', 'yield']]
print ('stock_list')
print (stock_list)
def update_basics():
basics = ts.get_stock_basics()
f = os.path.join(DATA_DIR, 'basics.h5')
basics.to_hdf(f, 'basics')
length = 4 * 5
year, season = last_report_season()
for i in range(length):
f = os.path.join(DATA_DIR, 'basics-{0}-{1}.h5'.format(year, season))
if os.path.exists(f):
continue
report = ts.get_report_data(year, season)
report.to_hdf(f, 'report')
profit = ts.get_profit_data(year, season)
profit.to_hdf(f, 'profit')
operation = ts.get_operation_data(year, season)
operation.to_hdf(f, 'operation')
growth = ts.get_growth_data(year, season)
growth.to_hdf(f, 'growth')
debtpaying = ts.get_debtpaying_data(year, season)
debtpaying.to_hdf(f, 'debtpaying')
cashflow = ts.get_cashflow_data(year, season)
cashflow.to_hdf(f, 'cashflow')
season -= 1
if season == 0:
season = 4
year -= 1
def load_profits(year, quarter):
"""
加载所有股票指定年份和季度的盈利能力列表。
若列表文件‘profits-(year)-(quarter).csv’不存在，则下载
:param year: 年份
:param quarter: 季度(1,2,3,4)
:return:
code,代码
name,名称
roe,净资产收益率(%)
net_profit_ratio,净利率(%)
gross_profit_rate,毛利率(%)
net_profits,净利润(万元)
eps,每股收益
business_income,营业收入(百万元)
bips,每股主营业务收入(元)
:rtype: DataFrame
"""
file_name = 'profits-' + str(year) + '-' + str(quarter) + '.csv'
if os.path.exists(file_name):
df_profits = pd.read_csv(file_name)
else:
try:
df_profits = ts.get_profit_data(year, quarter)
df_profits.to_csv(file_name)
except Exception as ex:
print ex
df_profits = load_profits(year - 1, quarter)
df_profits.drop(df_profits.index, inplace=True)
return df_profits
def stat_all(tmp_datetime):
# 返回 31 天前的数据，做上个季度数据统计。
tmp_datetime_1month = tmp_datetime + datetime.timedelta(days=-31)
year = int((tmp_datetime_1month).strftime("%Y"))
quarter = int(pd.Timestamp(tmp_datetime_1month).quarter) # 获得上个季度的数据。
print("############ year %d, quarter %d", year, quarter)
# 业绩报告（主表）
data = ts.get_report_data(year, quarter)
# 增加季度字段。
data = concat_quarter(year, quarter, data)
# 处理重复数据，保存最新一条数据。最后一步处理，否则concat有问题。
data = data.drop_duplicates(subset="code", keep="last")
global db
# 插入数据库。
db.insert_db(data, "ts_report_data", True, "`quarter`,`code`")
# 盈利能力
data = ts.get_profit_data(year, quarter)
# 增加季度字段。
data = concat_quarter(year, quarter, data)
# 处理重复数据，保存最新一条数据。
data = data.drop_duplicates(subset="code", keep="last")
# 插入数据库。
db.insert_db(data, "ts_profit_data", True, "`quarter`,`code`")
# 营运能力
data = ts.get_operation_data(year, quarter)
# 增加季度字段。
data = concat_quarter(year, quarter, data)
# 处理重复数据，保存最新一条数据。最后一步处理，否则concat有问题。
data = data.drop_duplicates(subset="code", keep="last")
# 插入数据库。
db.insert_db(data, "ts_operation_data", True, "`quarter`,`code`")
# 成长能力
data = ts.get_growth_data(year, quarter)
# 增加季度字段。
data = concat_quarter(year, quarter, data)
# 处理重复数据，保存最新一条数据。最后一步处理，否则concat有问题。
data = data.drop_duplicates(subset="code", keep="last")
# 插入数据库。
db.insert_db(data, "ts_growth_data", True, "`quarter`,`code`")
# 偿债能力
data = ts.get_debtpaying_data(year, quarter)
# 增加季度字段。
data = concat_quarter(year, quarter, data)
# 处理重复数据，保存最新一条数据。最后一步处理，否则concat有问题。
data = data.drop_duplicates(subset="code", keep="last")
# 插入数据库。
db.insert_db(data, "ts_debtpaying_data", True, "`quarter`,`code`")
# 现金流量
data = ts.get_cashflow_data(year, quarter)
# 增加季度字段。
data = concat_quarter(year, quarter, data)
# 处理重复数据，保存最新一条数据。最后一步处理，否则concat有问题。
data = data.drop_duplicates(subset="code", keep="last")
# 插入数据库。
db.insert_db(data, "ts_cashflow_data", True, "`quarter`,`code`")
def getThingsEveryday(self):
yearEnd = datetime.now().year - 1
if self.__flagUpdateReport:
pdGrowthLastYear = ts.get_growth_data(yearEnd, 4)
pdGrowthLastYear.to_excel('./' + \
str(yearEnd) + 'Growth.xls', sheet_name='Growth')
pdProfitLastYear = ts.get_profit_data(yearEnd, 4)
pdProfitLastYear.to_excel('./' + \
str(yearEnd) + 'Profit.xls', sheet_name='Profit')
pdReportLastYear = ts.get_report_data(yearEnd, 4)
pdReportLastYear.to_excel('./' + \
str(yearEnd) + 'y.xls', sheet_name='Report')
self.__stockBasics = ts.get_stock_basics() #获得昨天pe
self.__stockBasics['code'] = self.__stockBasics.index.astype(int)
self.__stockBasics.sort_index(inplace=True)
self.__stockTodayAll = ts.get_today_all() #获得昨收
self.__stockTodayAll['code'] = self.__stockTodayAll['code'].astype(int)
self.__pdForwardEps = pd.merge(self.__stockBasics,
self.__stockTodayAll,
on='code')
self.__pdForwardEps['feps'] = self.__pdForwardEps[
'settlement'] / self.__pdForwardEps['pe']
print('\n')
def get_profit(year, q):
conn = default_db()
cur = conn.cursor()
net_profits = ts.get_profit_data(year, q)
for i in net_profits.index:
stock_id = net_profits.loc[i]['code']
stock_name = net_profits.loc[i]['name']
if q == 1:
date = '%d-01-01' % (year)
elif q == 2:
date = '%d-04-01' % (year)
elif q == 3:
date = '%d-07-01' % (year)
else:
date = '%d-10-01' % (year)
net_profit = net_profits.loc[i]['net_profits']
order = 'insert into %s ( %s,%s,%s,%s)values("%s", "%s","%s","%f")' % (
TABLE_NETPROFIT, NETPROFIT_STOCK_ID, NETPROFIT_STOCK_NAME,
NETPROFIT_DATE, NETPROFIT_NETPROFIT, stock_id, stock_name, date,
net_profit)
try:
cur.execute(order)
conn.commit()
except Exception, e:
print e
def fetchByYearQuarter(self, mongo, type):
years = range(self.begin_year, self.end_year_notinclude)
quarters = range(1, 5)
for year in years:
for quarter in quarters:
print(str(type) + '_' + str(year) + '_' + str(quarter))
if (type == 'report_data'):
df = fd.get_report_data(year, quarter)
elif (type == 'profit_data'):
df = ts.get_profit_data(year, quarter)
elif (type == 'operation_data'):
df = ts.get_operation_data(year, quarter)
elif (type == 'growth_data'):
df = ts.get_growth_data(year, quarter)
elif (type == 'debtpaying_data'):
df = ts.get_debtpaying_data(year, quarter)
elif (type == 'cashflow_data'):
df = ts.get_cashflow_data(year, quarter)
else:
df = {}
tmpJson = json.loads(df.to_json(orient='records'))
for i in range(len(tmpJson)):
tmpJson[i][u'year'] = int(year)
tmpJson[i][u'quarter'] = int(quarter)
coll = mongo.fundemental[type]
coll2 = mongo.fundemental[str(type) + '_' + str(year) + '_' +
str(quarter)]
coll2.insert(tmpJson)
coll.insert(tmpJson)
def get_temp_data(year, quarter):
df1 = ts.get_report_data(year, quarter)
#print (1)
df1 = df1.merge(ts.get_profit_data(year, quarter),
how='inner',
on=['code', 'name'])
#print (2)
df1 = df1.merge(ts.get_operation_data(year, quarter),
how='inner',
on=['code', 'name'])
#print (3, "n", df1)
df1 = df1.merge(ts.get_growth_data(year, quarter),
how='inner',
on=['code', 'name'])
#print (4)
print(df1)
df1 = df1.merge(ts.get_debtpaying_data(year, quarter),
how='inner',
on=['code', 'name'])
#print (5)
print(df1)
df1 = df1.merge(ts.get_cashflow_data(year, quarter),
how='inner',
on=['code', 'name'])
#print (6)
(row, col) = df1.shape
for i in range(0, row):
df1.iloc[i, 0] = str(df1.iloc[i, 0])
return df1
def get_all_profit_data(year, quarter):
"""
Description : 取得盈利能力
Arg :
@year ： 年份
@quarter ： 季度
Returns : 返回DataFrame
Raises :
"""
year_quarter = "{}-{}".format(year, quarter)
year_quarter_where = 'year_quarter=="{}"'.format(year_quarter)
if ((stone.get_storer(str_profit) is None) or
(len(stone.select(str_profit, where=[year_quarter_where])) == 0)):
pf = ts.get_profit_data(year, quarter)
# 如果为空，就返回空吧
if ((pf is None) or (len(pf) == 0)):
return None
# 添加月份，
pf['year_quarter'] = year_quarter
# 转换格式
pf = pf.astype(dict_profit_columns, copy=True, errors='ignore')
# 保存数据到HDF5
stone.append(str_profit,
pf,
format='table',
data_columns=lst_profit_column_name)
# 返回这个值就可以了。
return pf
return stone.select(str_profit, where=[year_quarter_where])
def get_profit_report():
year = time.strftime("%Y", time.localtime())
month = time.strftime("%m", time.localtime())
season = int(int(month) / 3)
df = ts.get_profit_data(int(year), season)
if df is None:
season = season - 1
df = ts.get_profit_data(int(year), season)
else:
if str(df.head(1).roe[0]) == 'nan':
season = season - 1
df = ts.get_profit_data(int(year), season)
return df
def buildProfitForStock(self, stock):
code = stock.get_code()
if self.__dfProfit is None or self.__dfProfit.empty:
try:
self.__dfProfit = pd.read_sql_table('profit',
con=self.__engine,
index_col='code')
except:
pass
if self.__dfProfit is None or self.__dfProfit.empty:
self.__dfProfit = ts.get_profit_data(
self.__config.get_report()[0],
self.__config.get_report()[1])
self.__dfProfit.to_sql('profit',
self.__engine,
if_exists='replace',
index_label='code',
index=False)
try:
roe = self.__dfProfit.loc[stock.get_code()].get('roe')
if isinstance(roe, pd.Series):
roe = roe.iat[-1]
stock.set_roe(roe)
except:
stock.set_roe(float("nan"))
def get_data_by_year_season(self, year, season):
struct_time = time.localtime()
current_year = struct_time.tm_year
current_month = struct_time.tm_mon
df = ts.get_profit_data(year, season)
if df is not None:
if df.empty is False:
n_data = len(df.index)
for i in range(n_data):
data = df.iloc[i]
data_json = {
"id": uuid.uuid4().hex,
"code": data["code"],
"datetime": str(current_year) + "-" + str(current_month) + "-" + "00" + " " + "00" + ":" + "00" + ":" + "00",
"fetchtime": get_formated_time_mysql(),
"source": self.source_name,
"crawler": self.crawl_id,
"ROE": data["roe"],
"NPMOS": data["net_profit_ratio"],
"GPTS": data["gross_profit_rate"],
"IFMO": data["net_profits"],
"DOE": data["eps"],
}
data_json = toshare_null_handle(data_json)
self.data.append(data_json)
def get_eps_series(code, dates):
eps_series = []
latest_year = 0
latest_quarter = 0
latest_eps = 0.0
date_delta = timedelta(days = 1)
for date in dates:
(year, quarter) = get_report_date(date)
if year == latest_year and quarter == latest_quarter:
eps_series.append(latest_eps)
else:
print("Get profit data of %s, %s" % (year, quarter))
quarter_eps = ts.get_profit_data(year, quarter)
try:
latest_eps = quarter_eps[quarter_eps['code'] == code]['eps'].values[0]
#annualize eps
latest_eps = latest_eps * 4.0 / quarter
except:
print("failed to get eps, using last one %s" % (latest_eps))
eps_series.append(latest_eps)
latest_year = year
latest_quarter = quarter
print('%s: %s' % (date, latest_eps))
date += date_delta
return eps_series
def get_report_data(year, season):
if not available(year, season):
return None
print("get_report_data")
save(ts.get_report_data(year, season), "basics/report_data", year, season)
print("get_profit_data")
save(ts.get_profit_data(year, season), "basics/profit_data", year, season)
filename = "operation_data"
print("get_operation_data")
save(ts.get_operation_data(year, season), "basics/operation_data", year,
season)
filename = "growth_data"
print("get_growth_data")
save(ts.get_growth_data(year, season), "basics/growth_data", year, season)
filename = "get_debtpaying_data"
print("get_debtpaying_data")
save(ts.get_debtpaying_data(year, season), "basics/debtpaying_data", year,
season)
filename = "get_debtpaying_data"
print("get_cashflow_data")
save(ts.get_cashflow_data(year, season), "basics/cashflow_data", year,
season)
def store_fund_data(quarter_list):
stock2year_path = os.path.join(LastFilePath, "stock_fundm_info")
for fun_year, fun_quarter in quarter_list:
#every dataframe you craw down all needs remove the duplicated row. Only need keep the first row of duplicates.
# stock2year_report is tushare:get_report_data (fundamental data).
stock2year_report = ts.get_report_data(
fun_year, fun_quarter).drop_duplicates(keep='first')
# stock2year_prof is tushare.get_profit_data (fundamental data).
stock2year_prof = ts.get_profit_data(
fun_year, fun_quarter).drop_duplicates(keep='first')
# stock2year_opera is tushare.get_operation_data (fundamental data).
stock2year_opera = ts.get_operation_data(
fun_year, fun_quarter).drop_duplicates(keep='first')
#stock2year_grow is tushare.get_growth_data (fundamental data).
stock2year_grow = ts.get_growth_data(
fun_year, fun_quarter).drop_duplicates(keep='first')
#stock2year_debt is tushare.get_debtpaying_data (fundamental data).
stock2year_debt = ts.get_debtpaying_data(
fun_year, fun_quarter).drop_duplicates(keep='first')
#stock2year_cash is tushare.get_cashflow_data (fundamental data).
stock2year_cash = ts.get_cashflow_data(
fun_year, fun_quarter).drop_duplicates(keep='first')
#stock2year_comb is to combine all the stock2year data of same year and quarter in a same stock code.
stock2year_list = [stock2year_report,stock2year_prof,stock2year_opera,stock2year_grow, \
stock2year_debt,stock2year_cash]
for every_fund_element in stock2year_list:
every_fund_element = every_fund_element.set_index('code')
#use pandas concat to combine all the dataframe along columns.
total_fund = pd.concat(stock2year_list, axis=1)
HeadName = fun_year + "/" + fun_quarter + "_" + "fundamt_info"
CsvName = os.path.join(stock2year_path, "{}.csv".format(HeadName))
total_fund.to_csv(CsvName)
def get_profit_data(year, quarter):
try:
df = ts.get_profit_data(year, quarter)
engine = create_engine('mysql://*****:*****@127.0.0.1/stock?charset=utf8')
df.to_sql('profit_data', engine, if_exists='append')
print "message"
except Exception, e:
e.message
def single_stock_report(code, year_start, k_index):
"""
:param code: the valid stock code, for example '002146'
:param year_start: the start date that we want to check the stock report, for example '201801'
:param k_index: the performance of report we want to check
:return: DataFrame table: the index is the quarter from start to end, the
"""
if code is None:
raise ValueError('please assign code')
if year_start is None:
raise ValueError('please assign year')
if k_index is None:
raise ValueError('please assign index')
year_to_market = stock_list('timeToMarket')
ytm = year_to_market[year_to_market.index == code]
ytm = str(ytm.iloc[0])
if ytm >= year_start:
qs = getBetweenQuarter(ytm)
else:
qs = getBetweenQuarter(year_start)
j = len(qs) - 1
results = pd.DataFrame()
new_index = []
for i in range(j):
year = int(qs[i].split('Q')[0])
q = int(qs[i].split('Q')[1])
n = 1
data = []
while n < 10:
if k_index == 'get_profit_data':
data = ts.get_profit_data(int(year), q)
elif k_index == 'get_report_data':
data = ts.get_report_data(int(year), q)
elif k_index == 'get_operation_data':
data = ts.get_operation_data(int(year), q)
elif k_index == 'get_growth_data':
data = ts.get_growth_data(int(year), q)
elif k_index == 'get_debtpaying_data':
data = ts.get_debtpaying_data(int(year), q)
elif k_index == 'get_cashflow_data':
data = ts.get_cashflow_data(int(year), q)
else:
raise Exception('the k_indexs is not correct')
result = data[data['code'] == code]
if len(result) >= 1:
new_index.append('%d0%d' % (year, q))
results = results.append(result[0:1], ignore_index=True)
print(results)
break
elif len(result) == 0:
n += 1
continue
new_index_1 = pd.DataFrame({"Y_Q": new_index})
frames = [results, new_index_1]
return pd.concat(frames, axis=1)
def get_profit_data(year, quarter):
filename = 'stock_profit'
filepath = os.path.join(rootDir, 'StockBasis', filename + '.csv')
if (os.path.exists(filepath)):
data = pd.read_csv(filepath, encoding='gbk')
else:
data = ts.get_profit_data(year, quarter)
data.to_csv(filepath)
return data
def loadprofit(): for year in [2011,2012,2013,2014,2015,2016,2017]: for quarter in [1,2,3,4]: if quarter == 4 and year ==2017: break profit = ts.get_profit_data(year, quarter) filename = "C:\invest\\basic\\profit\\%s%s.csv" % (year, quarter) print filename profit.to_csv(filename, encoding='utf-8')
def profit():
df = ts.get_profit_data(2018, 2)
print(df)
data = df.to_dict('index')
print(data)
for code, value in sorted(data.items()):
# print(value)
if value['code'] == '600420':
print(value)
def revenue(self):
df = ts.get_profit_data(
self.year,
self.season).rename(columns={
"code": "stock_id",
"gross_profit_rate": "gross_profit_ratio"
}).drop(labels=["name"], axis=1)
df["season"] = self.year_season
return df
def table_work(self, duration):
table = ts.get_profit_data(2015, 4)[['net_profits', 'business_income','code']]
previous = ts.get_profit_data(2016-duration-1, 4)[['net_profits', 'business_income','code']]
n = 0
avg = 0
list = []
for i in range(2016-duration,2016):
current = (ts.get_profit_data(i, 4))[['net_profits', 'business_income','code']]
hold = pd.merge(previous, current, on='code')
table = pd.merge(table, hold, on='code')
table['growth_%d'%(i)]=(table.net_profits_y/table.net_profits_x-1)*0.4 + (table.business_income_y/table.business_income_x-1)*0.6
table = table.drop(table.columns[[3+n,4+n,5+n,6+n]], axis=1)
previous = current
n += 1
list.append('growth_%d'%(i))
for j in list:
avg += table[j]*(1/len(j))
table['avg_growth'] = avg
return table
def get_profit_data_quater():
temp_year = start
while(temp_year != end):
for temp_quater in [1,2,3,4]:
item = str(temp_year) + "-" + str(temp_quater)
if not sql.has_table(item, engine, flavor="mysql"):
print item + "begins"
data_total_raw[item] = ts.get_profit_data(temp_year, temp_quater)
print "\n"
if any(data_total_raw[item]): data_total_raw[item].to_sql(item, engine)
temp_year += 1
def getProfileReport(year):
for q in [1, 2, 3, 4]:
getDownloaded()
if not isDownloaded(profitByYear(year, q)):
try:
print('Profit of ' + str(year) + ' and quarter is ' + str(q))
profit_df = ts.get_profit_data(year, q)
profit_df.to_csv(path + profitByYear(year, q))
time.sleep(5)
except Exception:
logError('Profit: ' + str(year) + '-' + str(q) +
' there is problem, will skip it. ')
def sync_profit_data(): ''' sync profit data ''' year = datetime.datetime.now().year month = datetime.datetime.now().month seaon = month/3 if month<3: year = year - 1 seaon = 4 monthstr = '%s%s'%(year,seaon) DataFrameToMongo(ts.get_profit_data(year, seaon), MongoClient(mongourl)['stoinfo']['profit_data'], ['code'], monthstr)
def down_fin(year, season): is_succ = False t_name = 'zf'+str(year)+str(season) print "down_fin.........", t_name print t_name while is_succ == False: try: pd = ts.get_profit_data(year,season) print type(pd) if str(type(pd)) == '<class \'pandas.core.frame.DataFrame\'>': pd.to_sql(t_name, G_DBengine, if_exists='replace') is_succ = True except ValueError, e: print 'ValueError:', e
def write_profit():
arr = [(x, y) for x in range(2007, 2018) for y in range (1, 5)]
arr.remove((2017, 4))
frames = []
for item in arr:
df = ts.get_profit_data(item[0], item[1])
df.set_index('code', inplace=True)
frames.append(df)
quaters = [pd.Period('{}Q{}'.format(x,y)) for (x, y) in arr]
result = pd.concat(frames, keys=quaters)
result.index.names=[u'quarter', u'code']
result.to_csv('profit.csv', encoding='utf-8')
def update_basics():
basics = ts.get_stock_basics()
f = os.path.join(base_dir, 'basics.h5')
basics.to_hdf(f, 'basics')
today = datetime.date.today()
current_year = today.year
current_season = today.month / 3
if current_season == 0:
current_year -= 1
current_season = 4
length = 4 * 5
year = current_year
season = current_season
for i in range(length):
f = os.path.join(base_dir, 'basics-{0}-{1}.h5'.format(year, season))
if os.path.exists(f):
continue
print(f)
report = ts.get_report_data(year, season)
report.to_hdf(f, 'report')
profit = ts.get_profit_data(year, season)
profit.to_hdf(f, 'profit')
operation = ts.get_operation_data(year, season)
operation.to_hdf(f, 'operation')
growth = ts.get_growth_data(year, season)
growth.to_hdf(f, 'growth')
debtpaying = ts.get_debtpaying_data(year, season)
debtpaying.to_hdf(f, 'debtpaying')
cashflow = ts.get_cashflow_data(year, season)
cashflow.to_hdf(f, 'cashflow')
season -= 1
if season == 0:
season = 4
year -= 1
#!/usr/bin/env python3.4
import sys
import time
import tushare as ts
from sqlalchemy import create_engine
import mysql.connector
conn = create_engine('mysql+mysqlconnector://stockadmin:stock2016@localhost/stock?charset=utf8')
for year in range(2004,2012):
for season in range(1,5):
print(year,season)
df_profit = ts.get_profit_data(year,season)
time.sleep(15)
df_growth = ts.get_growth_data(year,season)
time.sleep(15)
df_operation = ts.get_operation_data(year,season)
time.sleep(15)
df_debtpaying = ts.get_debtpaying_data(year,season)
time.sleep(15)
df_cashflow = ts.get_cashflow_data(year,season)
time.sleep(15)
df_report = ts.get_report_data(year,season)
df_profit.to_sql('profit',conn,if_exists='append')
df_growth.to_sql('growth',conn,if_exists='append')
df_operation.to_sql('operation',conn,if_exists='append')
df_debtpaying.to_sql('debtpaying',conn,if_exists='append')
df_cashflow.to_sql('cashflow',conn,if_exists='append')
df_report.to_sql('report',conn,if_exists='append')
@author: Fuqian
"""
from sqlalchemy import create_engine
import tushare as ts
#import pymongo
import pandas as pd
df_base = ts.get_stock_basics()
df_report_1503 = ts.get_report_data(2015,3)
df_profit_1503 = ts.get_profit_data(2015,3)
df_growth_1503 = ts.get_growth_data(2015,3)
#detail_daily={}
engine = create_engine('mysql://*****:*****@127.0.0.1/stock?charset=utf8')
for row_index, row in df_base.iterrows():
try:
f = open('qfq_err', 'a')
f_d = open('detailDay_err','a')
stocknum = row_index
timeToMarket = df_base.ix[stocknum]['timeToMarket']
def download_profit_data(file_path, year, quarter):
profit_data = ts.get_profit_data(year, quarter)
if profit_data is not None:
profit_data.to_csv(file_path + 'profit_' + str(year) + '_' + str(quarter) + '.csv', encoding='utf-8')
# FinancialReport: EPS, EPS_YOY, ROE, net_profits, profits_yoy
# ProfitData: ROE, net_profit_ratio, gross_profit_rate, EPS, bips (business income per share)
# GrowthData: mbrg (main business rate growth), nprg (net profit),
# nav, targ (total asset), epsg, seg (shareholder's eqty)
# DebtPayingData: currentratio, quickratio, cashratio, icratio (interest coverage)
# TODO Data is available quarterly
# TODO Compare data for FinancialReport and ProfitData
FinancialData = ts.get_report_data(CURRENT.year, np.floor((CURRENT.month+2)/3)-1)
FinancialData = FinancialData.set_index('code')
FinancialData = FinancialData.drop(['name', 'bvps', 'distrib', 'epcf', 'report_date'], axis = 1)
FinancialData.to_csv('./ASHR/DATA/FinancialData_2015_1.csv', index = True)
ProfitData = ts.get_profit_data(CURRENT.year, np.floor((CURRENT.month+2)/3)-1)
ProfitData = ProfitData.set_index('code')
ProfitData = ProfitData.drop(['name', 'business_income', 'net_profits'], axis = 1)
ProfitData.to_csv('./ASHR/DATA/ProfitData_2015_1.csv', index = True)
GrowthData = ts.get_growth_data(CURRENT.year, np.floor((CURRENT.month+2)/3)-1)
GrowthData = GrowthData.set_index('code')
GrowthData = GrowthData.drop(['name'], axis = 1)
GrowthData.to_csv('./ASHR/DATA/GrowthData_2015_1.csv', index = True)
DebtPayingData = ts.get_debtpaying_data(CURRENT.year, np.floor((CURRENT.month+2)/3)-1)
DebtPayingData = DebtPayingData.set_index('code')
DebtPayingData = DebtPayingData.drop(['name', 'sheqratio', 'adratio'], axis = 1)
DebtPayingData.to_csv('./ASHR/DATA/DebtPayingData_2014_12.csv', index = True)
# Merging data
import tushare from datastorage.mongo import writetomongo stock = tushare.get_profit_data(2015,4) writetomongo(stock)
def test_getcompany_profit(self):
log.info(ts.get_profit_data(2018, 2))
def pick_data(self, max_num_threads = 20, pause = 0):
"""
pick all necessary data from local database and from internet for loaded stocks. This function will take a while.
"""
logging.info('getting basics from tushare')
self._init_stock_objs()
# self.data_manager.drop_stock()
# self.stocks = {key: self.stocks[key] for key in ['600233', '600130']}
logging.info('totally there are %d listed companies' % len(self.stocks))
logging.info('get indexes from tushare')
self._get_indexes()
# self._pick_hist_data_and_save(self.stocks, False, self.indexes['000001'].hist_start_date, max_num_threads)
logging.info('getting last stock trading data')
df = ts.get_today_all()
self._extract_from_dataframe(df,
ignore=('changepercent', 'open', 'high', 'low', 'settlement', 'volume', 'turnoverratio', 'amount'),
remap={'trade': 'price', 'per': 'pe'})
# calculate the report quarter
report_year, report_quarter = ts.get_last_report_period()
logging.info('getting last report (%d quarter %d) from tushare' % (report_year, report_quarter))
df = ts.get_report_data(report_year, report_quarter)
self._extract_from_dataframe(df)
logging.info('getting last profit data from tushare')
df = ts.get_profit_data(report_year, report_quarter)
self._extract_from_dataframe(df, ignore=('net_profits', 'roe', 'eps'))
logging.info('getting last operation data from tushare')
df = ts.get_operation_data(report_year, report_quarter)
self._extract_from_dataframe(df)
logging.info('getting last growth data from tushare')
df = ts.get_growth_data(report_year, report_quarter)
self._extract_from_dataframe(df)
logging.info('getting last debtpaying data from tushare')
df = ts.get_debtpaying_data(report_year, report_quarter)
self._extract_from_dataframe(df)
logging.info('getting last cashflow data from tushare')
df = ts.get_cashflow_data(report_year, report_quarter)
self._extract_from_dataframe(df)
logging.info('getting history trading data from tushare')
start_from = self.indexes['000001'].hist_start_date
data_full = self._pick_hist_data_and_save(self.stocks, False, start_from, max_num_threads, pause) # anything that pulling data must before here
self._remove_unavailable_stocks()
'''
# calculate qianfuquan data
# deprecated due to precision issue
for code, stock in self.stocks.items():
for i in range(1, len(stock.hist_data.index)-1):
b = stock.hist_data.at[stock.hist_data.index[i], 'close']
a = stock.hist_data.at[stock.hist_data.index[i+1], 'close']
p = stock.hist_data.at[stock.hist_data.index[i+1], 'p_change'] / 100.0
q = (p*a+a)/b
if q > 1.1:
print('%s chuq-uan %s: %s %s %s, 1/%s' % (stock, stock.hist_data.index[i], b, a, p, q))
'''
return data_full
def getprofit(year,quarter):
dfprofit = ts.get_profit_data(year,quarter)
dfprofit.insert(0,'uploadtime',nowtime)
dfprofit.insert(1,'year',year)
dfprofit.insert(2,'quarter',quarter)
dfprofit.to_sql('tb_profit',engine,if_exists='append')
import tushare as ts import sys df = ts.get_profit_data(int(sys.argv[1]), int(sys.argv[2])) df.to_csv(sys.argv[3], encoding="utf8")
import tushare as ts
import marshal, pickle
year = 2015
print(report_data)
report_data = ts.get_report_data(year, 4)
f = file('report_data', 'w')
pickle.dump(report_data, f)
f.close()
profit_data = ts.get_profit_data(year, 4)
f = file('profit_data', 'w')
pickle.dump(profit_data, f)
f.close()
growth_data = ts.get_growth_data(year, 4)
f = file('growth_data', 'w')
pickle.dump(growth_data, f)
f.close()
df.to_sql('report_data',engine, if_exists='replace')
df = ts.get_report_data(currentYear,currentSeason)
df = df.assign(quater=currentQuater)
df.to_sql('report_data',engine, if_exists='append')
# import profit data
#code,代码
#name,名称
#roe,净资产收益率(%)
#net_profit_ratio,净利率(%)
#gross_profit_rate,毛利率(%)
#net_profits,净利润(万元)
#esp,每股收益
#business_income,营业收入(百万元)
#bips,每股主营业务收入(元)
df = ts.get_profit_data(lastYear,lastSeason)
df = df.assign(quater=lastQuater)
df.to_sql('profit_data',engine, if_exists='replace')
df = ts.get_profit_data(currentYear,currentSeason)
df = df.assign(quater=currentQuater)
df.to_sql('profit_data',engine, if_exists='append')
# import operation data
#code,代码
#name,名称
#arturnover,应收账款周转率(次)
#arturndays,应收账款周转天数(天)
#inventory_turnover,存货周转率(次)
#coding=utf-8 import tushare as ts # 获取沪深上市公司基本情况 df = ts.get_stock_basics() date = df.ix['600848']['timeToMarket']#上市日期YYYYMMDD #获取2014年第3季度的业绩报表数据 ts.get_report_data(2014,3) #获取2014年第3季度的盈利能力数据 ts.get_profit_data(2014,3) #获取2014年第3季度的营运能力数据 ts.get_operation_data(2014,3) #获取2014年第3季度的成长能力数据 ts.get_growth_data(2014,3) #获取2014年第3季度的偿债能力数据 ts.get_debtpaying_data(2014,3) #获取2014年第3季度的现金流量数据 ts.get_cashflow_data(2014,3)
# open：开盘价 # high：最高价 # close：收盘价 # low：最低价 # volume：成交量 # price_change：价格变动 # p_change：涨跌幅 # ma5：5日均价 # ma10：10日均价 # ma20:20日均价 # v_ma5:5日均量 # v_ma10:10日均量 # v_ma20:20日均量 # turnover:换手率[注：指数无此项] 4/4 raw_data_profit = ts.get_profit_data() # code,代码 # name,名称 # roe,净资产收益率(%) 7 # net_profit_ratio,净利率(%) # gross_profit_rate,毛利率(%) # net_profits,净利润(万元) # eps,每股收益 # business_income,营业收入(百万元) # bips,每股主营业务收入(元) raw_data_report = ts.get_report_data() # code,代码 # name,名称 # eps,每股收益
