def deposit_rate(**kwargs):
df = ts.get_deposit_rate()
df["Date"] = pd.to_datetime(df.date, format="%Y-%m-%d")
df["rate"] = df[[
"rate"
]].applymap(lambda x: float(x) if x != "--" else None)["rate"]
return df[["rate", "deposit_type"]].set_index(df["Date"])
def setDepositRate(self,
isSave=False,
tableName=MACROECONOMIC_DEPOSIT_RATE):
df = ts.get_deposit_rate()
if isSave is True:
df.to_sql(tableName, self.engine_sql, if_exists='append')
return df
def get_deposit_rate():
try:
df = ts.get_deposit_rate()
engine = create_engine('mysql://*****:*****@127.0.0.1/stock?charset=utf8')
df.to_sql('deposit_rate', engine, if_exists='append')
print "message"
except Exception, e:
e.message
def get_deposit_rate_info():
# 存款利率
df = ts.get_deposit_rate()
if df is not None:
res = df.to_sql(microE_deposit_rate, engine, if_exists='replace')
msg = 'ok' if res is None else res
print('获取存款利率: ' + msg + '\n')
else:
print('获取存款利率: ' + 'None' + '\n')
def stat_all(tmp_datetime):
# 存款利率
data = ts.get_deposit_rate()
common.insert_db(data, "ts_deposit_rate", False, "`date`,`deposit_type`")
# 贷款利率
data = ts.get_loan_rate()
common.insert_db(data, "ts_loan_rate", False, "`date`,`loan_type`")
# 存款准备金率
data = ts.get_rrr()
common.insert_db(data, "ts_rrr", False, "`date`")
# 货币供应量
data = ts.get_money_supply()
common.insert_db(data, "ts_money_supply", False, "`month`")
# 货币供应量(年底余额)
data = ts.get_money_supply_bal()
common.insert_db(data, "ts_money_supply_bal", False, "`year`")
# 国内生产总值(年度)
data = ts.get_gdp_year()
common.insert_db(data, "ts_gdp_year", False, "`year`")
# 国内生产总值(季度)
data = ts.get_gdp_quarter()
common.insert_db(data, "ts_get_gdp_quarter", False, "`quarter`")
# 三大需求对GDP贡献
data = ts.get_gdp_for()
common.insert_db(data, "ts_gdp_for", False, "`year`")
# 三大产业对GDP拉动
data = ts.get_gdp_pull()
common.insert_db(data, "ts_gdp_pull", False, "`year`")
# 三大产业贡献率
data = ts.get_gdp_contrib()
common.insert_db(data, "ts_gdp_contrib", False, "`year`")
# 居民消费价格指数
data = ts.get_cpi()
common.insert_db(data, "ts_cpi", False, "`month`")
# 工业品出厂价格指数
data = ts.get_ppi()
common.insert_db(data, "ts_ppi", False, "`month`")
#############################基本面数据 http://tushare.org/fundamental.html
# 股票列表
data = ts.get_stock_basics()
print(data.index)
common.insert_db(data, "ts_stock_basics", True, "`code`")
def __risk_free_rate(self):
result = {}
result['D'] = \
(lambda x: pd.DataFrame({'rate': x, 'trade_days': np.ones(x.shape)}, index=x.index))(
(lambda x: pd.Series(x['rate'].apply(float).values, index=pd.DatetimeIndex(x['date'])).reindex(
self.__index_daily).fillna(method='ffill').fillna(method='bfill'))(
(lambda x: x[(x['deposit_type'] == '定期存款整存整取(一年)') & (x['rate'] != '--')])(
tushare.get_deposit_rate()
)
)
)
# TODO 最后一个bfill本应是填入最近的上一个值
result['W'] = result['D'].resample('W-MON', how='sum')
result['M'] = result['D'].resample('MS', how='sum')
return result
def __risk_free_rate(self):
result = {}
result['D'] = \
(lambda x: pd.DataFrame({'rate': x, 'trade_days': np.ones(x.shape)}, index=x.index))(
(lambda x: pd.Series(x['rate'].apply(float).values, index=pd.DatetimeIndex(x['date'])).reindex(
self.__index_daily).fillna(method='ffill').fillna(method='bfill'))(
(lambda x: x[(x['deposit_type'] == '定期存款整存整取(一年)') & (x['rate'] != '--')])(
tushare.get_deposit_rate()
)
)
)
# TODO 最后一个bfill本应是填入最近的上一个值
result['W'] = result['D'].resample('W-MON', how='sum')
result['M'] = result['D'].resample('MS', how='sum')
return result
def get_macro():
Macro={}
Macro['Depo']=ts.get_deposit_rate()
Macro['Loan']=ts.get_loan_rate()
Macro['RRR']=ts.get_rrr()
Macro['MoneySupply']=ts.get_money_supply()
Macro['MoneyBalance']=ts.get_money_supply_bal()
Macro['GDPYOY']=ts.get_gdp_year()
Macro['GDPQOQ']=ts.get_gdp_quarter()
Macro['GDPFOR']=ts.get_gdp_for()
Macro['GDPPULL']=ts.get_gdp_pull()
Macro['GDPCON']=ts.get_gdp_contrib()
Macro['CPI']=ts.get_cpi()
Macro['PPI']=ts.get_ppi()
Macro['SHIBO']=ts.shibor_data()
return Macro
def get_deposit_rate():
"""得到存款利率"""
logger.info('Begin get DepositsRate.')
try:
data_df = ts.get_deposit_rate()
except Exception as e:
logger.exception('Error get DepositsRate.')
return None
else:
data_dicts = []
if data_df.empty:
logger.warn('Empty get DepositsRate.')
else:
data_dicts = [{'date': row[0], 'deposit_type': row[1], 'rate': row[2]} for row in data_df.values]
logger.info('Success get DepositsRate.')
return data_dicts
def test_get_deposit_rate(self):
# ------------------------------------------------
# 宏观经济数据
# 宏观经济数据提供国内重要的宏观经济数据，目前只提供比较常用的宏观经济数据，通过简单的接口调用可获取相应的DataFrame格式数据，大项主要包括以下类别：
#
# 金融信息数据
# 国民经济数据
# 价格指数数据
# 景气指数数据
# 对外经济贸易数据
# 【注：以下所有数据的结果打印只显示了前10行记录】
# ------------------------------------------------
mongo = MongoClient("127.0.0.1", 27017)
df = ts.get_deposit_rate()
print(df)
insert_string = df.to_json(orient='records')
items = json.loads(insert_string)
mongo.macro.deposit_rate.insert(items)
self.core_function("gdp_year")
def return_deposit_rate(self):
'''
存款利率
'''
df = ts.get_deposit_rate()
labels = [
u'定期存款整存整取(三个月)',
u'定期存款整存整取(半年)',
u'定期存款整存整取(一年)',
u'定期存款整存整取(二年)',
u'定期存款整存整取(三年)',
u'定期存款整存整取(五年)'
]
detail = {"date": [],
"datasets": []}
detail['date'] = sorted(set(df[df['deposit_type'].isin(labels)]['date'].values.tolist()))
for label in labels:
df2 = df[df['deposit_type'] == label]
rate = df2['rate'].values.tolist()
rate.reverse()
date = df2['date'].values.tolist()
date.reverse()
if date == detail['date']:
item = {
'label': label,
'data': rate,
}
else:
tmp_dict = {}
for idx in xrange(0, len(date)):
tmp_dict[date[idx]] = rate[idx]
for idx in xrange(0, len(detail['date'])):
k = detail['date'][idx]
if k not in tmp_dict:
tmp_dict[k] = None
item = {
'label': label,
'data': [tmp_dict[k] for k in detail['date']]
}
detail['datasets'].append(item)
self.reply(detail=detail)
def get_interest_rate():
deposit = ts.get_deposit_rate()
loan = ts.get_loan_rate()
data = deposit[[x.startswith('定期存款整存整取') for x in deposit['deposit_type']
]].pivot_table(values="rate",
index=['date'],
columns='deposit_type',
aggfunc=sum)
data1 = loan[[
x.startswith('短期贷款') or x.startswith('中长期贷款')
for x in loan['loan_type']
]].pivot_table(values="rate",
index=['date'],
columns='loan_type',
aggfunc=sum)
data.columns = [
x.strip().replace('''定期存款整存整取''', '').replace('''(''',
'').replace(''')''', '')
for x in data.columns
]
data.columns = ['DOYEAR' if x == '一年' else x for x in data.columns]
data.columns = ['DTMON' if x == '三个月' else x for x in data.columns]
data.columns = ['DTYEAR' if x == '三年' else x for x in data.columns]
data.columns = ['DSYEAR' if x == '二年' else x for x in data.columns]
data.columns = ['DFYEAR' if x == '五年' else x for x in data.columns]
data.columns = ['DHYEAR' if x == '半年' else x for x in data.columns]
data1.columns = [
x.strip().replace('''短期贷款''',
'').replace('''中长期贷款''',
'').replace('''(''',
'').replace(''')''', '')
for x in data1.columns
]
data1.columns = ['SYEAR' if x == '一至三年' else x for x in data1.columns]
data1.columns = ['LTMON' if x == '三至五年' else x for x in data1.columns]
data1.columns = ['LFYEAR' if x == '五年以上' else x for x in data1.columns]
data1.columns = ['LHYEAR' if x == '六个月以内' else x for x in data1.columns]
data1.columns = ['LOYEAR' if x == '六个月至一年' else x for x in data1.columns]
res = pd.concat([data1, data], axis=1).reset_index().fillna(method='ffill')
res.columns = ['date' if x == 'index' else x for x in res.columns]
res['crawl_date'] = QA_util_today_str()
return (res)
def __call__(self, conns):
self.base = Base()
self.financial_data = conns['financial_data']
'''存款利率'''
deposit_rate = ts.get_deposit_rate()
self.base.batchwri(deposit_rate, 'deposit_rate', self.financial_data)
'''贷款利率'''
loan_rate = ts.get_loan_rate()
self.base.batchwri(loan_rate, 'loan_rate', self.financial_data)
'''存款准备金率'''
rrr = ts.get_rrr()
self.base.batchwri(rrr, 'RatioOfDeposit', self.financial_data)
'''货币供应量'''
money_supply = ts.get_money_supply()
self.base.batchwri(money_supply, 'money_supply', self.financial_data)
'''货币供应量（年底余额)'''
money_supply_bal = ts.get_money_supply_bal()
self.base.batchwri(money_supply_bal, 'money_supply_bal',
self.financial_data)
'''国内生产总值(年度）'''
gdp_year = ts.get_gdp_year()
self.base.batchwri(gdp_year, 'gdp_year', self.financial_data)
'''国内生产总值（季度）'''
gdp_quarter = ts.get_gdp_quarter()
self.base.batchwri(gdp_quarter, 'gdp_quarter', self.financial_data)
'''三大需求对GDP贡献'''
gdp_for = ts.get_gdp_for()
self.base.batchwri(gdp_for, 'gdp_for', self.financial_data)
'''三大产业对GDP拉动'''
gdp_pull = ts.get_gdp_pull()
self.base.batchwri(gdp_pull, 'gdp_pull', self.financial_data)
'''三大产业贡献率'''
gdp_contrib = ts.get_gdp_contrib()
self.base.batchwri(gdp_contrib, 'gdp_contrib', self.financial_data)
'''居民消费价格指数'''
cpi = ts.get_cpi()
self.base.batchwri(cpi, 'cpi', self.financial_data)
'''工业品出场价格指数'''
ppi = ts.get_ppi()
self.base.batchwri(ppi, 'ppi', self.financial_data)
def deposit_vs_loan(self):
dat_loan=ts.get_loan_rate()
dat_deposit=ts.get_deposit_rate()
#dat_loan = dat_loan[dat_loan['loan_type']=="中长期贷款(一至三年)"]
dat_loan = dat_loan[dat_loan['loan_type']=="短期贷款(六个月以内)"]
dat_deposit=dat_deposit[dat_deposit['deposit_type']=="活期存款(不定期)"]
dat_loan.index=pd.to_datetime(dat_loan['date'])
dat_deposit.index=pd.to_datetime(dat_deposit['date'])
dat_loan=dat_loan.drop(['date','loan_type'],axis=1)
dat_deposit=dat_deposit.drop(['date','deposit_type'],axis=1)
dat_loan=dat_loan['2015':'2000']
dat_deposit=dat_deposit['2015':'2000']
dat=dat_loan
dat['deposit']=dat_deposit['rate']
dat['loan']=dat['rate']
dat=dat.drop(['rate'],axis=1)
dat=dat.dropna()
dat=dat.astype(dtype='float64')
dat['loan-deposit']=dat['loan']-dat['deposit']
print(dat)
dat.plot()
plt.show()
def initial_data(self):
sql_cmd1 = """select t2.SecuCode as fundcode
,t3.TradingDay as TradingDay
,t3.UnitNVRestored as nvr
,t3.NVRDailyGrowthRate as nvr_growthrate
from mf_fundarchives t1
join secumain t2
on t1.InnerCode = t2.InnerCode
join mf_fundnetvaluere t3
on t1.InnerCode = t3.InnerCode
where (t1.fundtypecode = 1101 and t1.type in (2,3,6,7,9) and t1.investmenttype <> 7) and
(t1.ExpireDate > curdate() or t1.ExpireDate is null) and
t1.StartDate <= date_add(curdate(),interval -1 year)
"""
sql_cmd2 = """select t2.SecuCode as secucode
,t1.TradingDay as TradingDay
,t1.IndexValue as IndexValue
,t1.ValueDailyGrowthRate as ValueDailyGrowthRate
from MF_IndexReturnHis t1
join
secumain t2
on t1.indexcode= t2.innercode
where t2.secucode = '000300' and t2.SecuCategory = 4
"""
sql_cmd3 = """
select t2.secucode as secucode
,t1.canceldate as canceldate
,t1.maxchargerate as maxchargerate
,t1.chargeratetype as chargeratetype
from mf_chargeratenew t1
join secumain t2
on t1.InnerCode = t2.InnerCode
join mf_fundarchives t3
on t2.InnerCode
= t3.InnerCode
where t1.chargeratetype in (12000,12200,11010,11210) and t1.chargerateunit=6 and
(t3.fundtypecode = 1101 and t3.type = 2 and t3.investmenttype <> 7) and
(t3.ExpireDate > curdate() or t3.ExpireDate is null) and
t3.StartDate <= date_add(curdate(),interval -1 year)
"""
#基金数据
cursor = self.conn_mysql()
df = self.extract_fmysql(cursor, sql_cmd1)
dataset = pd.DataFrame(list(df), columns = ["fundcode", "TradingDay", "nvr", "nvr_growthrate"]) #261支基金
#沪深300市场行情数据
df_bm = self.extract_fmysql(cursor, sql_cmd2)
dataset_bm = pd.DataFrame(list(df_bm), columns = ["SecuCode", "TradingDay", "IndexValue", "ValueDailyGrowthRate"]).fillna(value = 0) #沪深300指数
dataset_bm = dataset_bm.sort_values(by='TradingDay').set_index('TradingDay')
#基金费率数据
df_fee = self.extract_fmysql(cursor, sql_cmd3)
dataset_fee = pd.DataFrame(list(df_fee), columns=['SecuCode', 'canceldate', 'maxchargerate', 'chargeratetype']).fillna(value=np.nan)
dataset_fee.loc[pd.isnull(dataset_fee.canceldate), 'canceldate'] = datetime.datetime(2099, 1, 1)
dataset_fee_slices = dataset_fee.groupby("SecuCode")
fee = []
for fundcode, data_fee_group in dataset_fee_slices:
redeem_fee_group = data_fee_group.loc[data_fee_group.chargeratetype.isin([12000, 12200])]
redeem_discount_default = 1.0
if len(redeem_fee_group) != 0:
redeem_fee = float(redeem_fee_group.sort_values(['canceldate', 'maxchargerate'])['maxchargerate'].iloc[-1]) * redeem_discount_default * 0.01
else:
redeem_fee = 0.0
apply_fee_group = data_fee_group.loc[data_fee_group.chargeratetype.isin([11210, 11010])]
apply_discount_default = 0.1
if len(apply_fee_group) != 0:
apply_fee = float(apply_fee_group.sort_values(['canceldate', 'maxchargerate'])['maxchargerate'].iloc[-1]) * apply_discount_default * 0.01
else:
apply_fee = 0.0
fund_fee = [fundcode, apply_fee, apply_discount_default, redeem_fee, redeem_discount_default]
fee.append(fund_fee)
fee = pd.DataFrame(fee, columns=['fundcode', 'apply_fee', 'apply_discount_default', 'redeem_fee','redeem_discount_default'])
#无风险利率：一年期存款利率
deposit_rate = ts.get_deposit_rate().loc[ts.get_deposit_rate()['deposit_type'] == u'定期存款整存整取(一年)']
deposit_rate['deposit_rate_1y'] = deposit_rate['rate'].astype('float') * 0.01
# deposit_rate.rename(columns = {'rate':'deposit_rate_1y'}, inplace = True)
deposit_rate = deposit_rate.set_index('date').sort_index()
deposit_rate.index = pd.to_datetime(deposit_rate.index, format='%Y/%m/%d')
deposit_rate.drop(['deposit_type', 'rate'], axis=1, inplace=True)
return dataset, dataset_bm, fee, deposit_rate
def deposit():
return ts.get_deposit_rate()
# -*- coding: utf-8 -*- import tushare as ts # 宏观经济数据 #存款利率 saving_rate = ts.get_deposit_rate() #贷款利率 loan_rate = ts.get_loan_rate() #存款准备金率 reserve_rate = ts.get_rrr() #货币供应量 m_supply = ts.get_money_supply() #货币供应量(年底余额) m_supply_y = ts.get_money_supply_bal() #GDP gdp_y = ts.get_gdp_year() gdp_q = ts.get_gdp_quarter() #三大需求对GDP贡献 gdp_for = ts.get_gdp_for() #三大产业对GDP拉动 gdp_pull = ts.get_gdp_pull() #三大产业贡献率 gdp_contrib = ts.get_gdp_contrib() cpi = ts.get_cpi()
def getDepositRate(self):
file_name = 'deposit_rate.csv'
path = self.index + self.index_deposit_rate + file_name
data = ts.get_deposit_rate()
data.to_csv(path, encoding='utf-8')
print(file_name)
def load_macro_economy():
# 下载存款利率
try:
rs = ts.get_deposit_rate()
pd.DataFrame.to_sql(rs, "deposit_rate", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载存款利率ok")
except:
print("下载存款利率出错")
# 下载贷款利率
try:
rs = ts.get_loan_rate()
pd.DataFrame.to_sql(rs, "loan_rate", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载贷款利率ok")
except:
print("下载贷款利率出错")
# 下载存款准备金率
try:
rs = ts.get_rrr()
pd.DataFrame.to_sql(rs, "rrr", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载存款准备金率ok")
except:
print("下载存款准备金率出错")
# 下载货币供应量
try:
rs = ts.get_money_supply()
pd.DataFrame.to_sql(rs, "money_supply", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载货币供应量ok")
except:
print("下载货币供应量出错")
# 下载货币供应量(年底余额)
try:
rs = ts.get_money_supply_bal()
pd.DataFrame.to_sql(
rs, "money_supply_bal", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True
)
print("下载货币供应量(年底余额)ok")
except:
print("下载货币供应量(年底余额)出错")
# 下载国内生产总值(年度)
try:
rs = ts.get_gdp_year()
pd.DataFrame.to_sql(rs, "gdp_year", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载国内生产总值(年度)ok")
except:
print("下载国内生产总值(年度)出错")
# 下载国内生产总值(季度)
try:
rs = ts.get_gdp_quarter()
pd.DataFrame.to_sql(rs, "gdp_quarter", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载国内生产总值(季度)ok")
except:
print("下载国内生产总值(季度)出错")
# 下载三大需求对GDP贡献
try:
rs = ts.get_gdp_for()
pd.DataFrame.to_sql(rs, "gdp_for", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载三大需求对GDP贡献ok")
except:
print("下载三大需求对GDP贡献出错")
# 下载三大产业对GDP拉动
try:
rs = ts.get_gdp_pull()
pd.DataFrame.to_sql(rs, "gdp_pull", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载三大产业对GDP拉动ok")
except:
print("下载三大产业对GDP拉动出错")
# 下载三大产业贡献率
try:
rs = ts.get_gdp_contrib()
pd.DataFrame.to_sql(rs, "gdp_contrib", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载三大产业贡献率ok")
except:
print("下载三大产业贡献率出错")
# 下载居民消费价格指数
try:
rs = ts.get_cpi()
pd.DataFrame.to_sql(rs, "gdp_cpi", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载居民消费价格指数ok")
except:
print("下载居民消费价格指数出错")
# 下载工业品出厂价格指数
try:
rs = ts.get_ppi()
pd.DataFrame.to_sql(rs, "gdp_ppi", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载工业品出厂价格指数ok")
except:
print("下载工业品出厂价格指数出错")
'''
i didn't multiprocess this one since the performance seems acceptable
'''
import tushare as ts
import pandas as pd
if __name__ =='__main__':
deposit_rate=ts.get_deposit_rate()
deposit_rate .to_csv('D:\\ts\\macro\\deposit_rate.csv',encoding='gbk')
loan_rate=ts.get_loan_rate()
loan_rate.to_csv('D:\\ts\\macro\\loan_rate.csv', encoding='gbk')
rrr=ts.get_rrr()
rrr.to_csv('D:\\ts\\macro\\rrr.csv', encoding='gbk')
money_supply=ts.get_money_supply()
money_supply.to_csv('D:\\ts\\macro\\money_supply.csv', encoding='gbk')
money_supply_bal=ts.get_money_supply_bal()
money_supply_bal.to_csv('D:\\ts\\macro\\money_supply_bal.csv', encoding='gbk')
gdp_year=ts.get_gdp_year()
gdp_year.to_csv('D:\\ts\\macro\\gdp_year.csv', encoding='gbk')
gdp_quater=ts.get_gdp_quarter()
gdp_quater.to_csv('D:\\ts\\macro\\gdp_quater.csv', encoding='gbk')
gdp_for=ts.get_gdp_for()
def macro_type(macros_type):
if macros_type == 'deposit_rate':
deposit_rate = ts.get_deposit_rate()
if deposit_rate is not None:
deposit_rate.to_sql('macros_deposit_rate',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'loan_rate':
loan_rate = ts.get_loan_rate()
if loan_rate is not None:
loan_rate.to_sql('macros_loan_rate',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'rrr':
rrr = ts.get_rrr()
if rrr is not None:
rrr.to_sql('macros_rrr',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'money_supply':
money_supply = ts.get_money_supply()
if money_supply is not None:
money_supply.to_sql('macros_money_supply',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'money_supply_bal':
money_supply_bal = ts.get_money_supply_bal()
if money_supply_bal is not None:
money_supply_bal.to_sql('macros_money_supply_bal',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_year':
gdp_year = ts.get_gdp_year()
if gdp_year is not None:
gdp_year.to_sql('macros_gdp_year',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_quater':
gdp_quater = ts.get_gdp_quarter()
if gdp_quater is not None:
gdp_quater.to_sql('macros_gdp_quater',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_for':
gdp_for = ts.get_gdp_for()
if gdp_for is not None:
gdp_for.to_sql('macros_gdp_for',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_pull':
gdp_pull = ts.get_gdp_pull()
if gdp_pull is not None:
gdp_pull.to_sql('macros_gdp_pull',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_contrib':
gdp_contrib = ts.get_gdp_contrib()
if gdp_contrib is not None:
gdp_contrib.to_sql('macros_gdp_contrib',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'cpi':
cpi = ts.get_cpi()
if cpi is not None:
cpi.to_sql('macros_cpi',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'ppi':
ppi = ts.get_ppi()
if ppi is not None:
ppi.to_sql('macros_ppi',
engine,
flavor='mysql',
if_exists='replace')
def capture_stock_data():
capture_date = datetime.datetime.now().strftime("%Y%m%d")
save_dir = "/home/dandelion/stock_data/" + capture_date
if not os.path.exists(save_dir):
os.mkdir(save_dir)
print("The save directory is created successfully!\n", save_dir)
print("The save directory is already exist!\n", save_dir)
# ======================Daily Command================================================================
# get the boxoffcie data of the last day and save as csvfile named as the capture command
ts.day_boxoffice().to_csv(
save_dir + "/" + capture_date + "_day_boxoffice.csv",
header=True,
sep=",",
index=False,
)
print("day_boxoffice data capture completed!")
# get the cinema data of the last day and save as csvfile named as the capture command
ts.day_cinema().to_csv(
save_dir + "/" + capture_date + "_day_cinema.csv",
header=True,
sep=",",
index=False,
)
print("day_cinema data capture completed!")
ts.month_boxoffice().to_csv(
save_dir + "/" + capture_date + "_month_boxoffice.csv",
header=True,
sep=",",
index=False,
)
print("month_boxoffice data capture completed!")
ts.realtime_boxoffice().to_csv(
save_dir + "/" + capture_date + "_realtime_boxoffice.csv",
header=True,
sep=",",
index=False,
)
print("realtime_boxoffice data capture completed!")
# get the stock data index of the last day and save as csvfile named as the capture command
ts.get_index().to_csv(
save_dir + "/" + capture_date + "_get_index.csv",
header=True,
sep=",",
index=False,
)
print("get_index data capture completed!")
# get the history cpi data and save as csvfile named as the capture command
ts.get_cpi().to_csv(
save_dir + "/" + capture_date + "_get_cpi.csv",
header=True,
sep=",",
index=False,
)
print("get_cpi data capture completed!")
# get the history gdp data by month and save as csvfile named as the capture command
ts.get_gdp_year().to_csv(
save_dir + "/" + capture_date + "_get_gdp_year.csv",
header=True,
sep=",",
index=False,
)
print("get_gdp_year data capture completed!")
# get today all stock data and save as csvfile named as the capture command
# ts.get_today_all().to_csv(save_dir+'/'+capture_date+'_get_today_all.csv',header=True,sep=',',index=False)
# get detail information of the top brokers today and save as csvfile named as the capture command
ts.broker_tops().to_csv(
save_dir + "/" + capture_date + "_broker_tops.csv",
header=True,
sep=",",
index=False,
)
print("broker_tops data capture completed!")
# get detail information of the top brokers today and save as csvfile named as the capture command
ts.cap_tops().to_csv(
save_dir + "/" + capture_date + "_cap_tops.csv",
header=True,
sep=",",
index=False,
)
print("cap_tops data capture completed!")
ts.get_area_classified().to_csv(
save_dir + "/" + capture_date + "_get_area_classified.csv",
header=True,
sep=",",
index=False,
)
print("get_area_classified data capture completed!")
# ts.get_balance_sheet(code='').to_csv(save_dir+'/'+capture_date+'_get_balance_sheet.csv',header=True,sep=',',index=False)
# print('get_balance_sheet data capture completed!')
# ts.get_cash_flow(code='').to_csv(save_dir+'/'+capture_date+'_get_cash_flow.csv',header=True,sep=',',index=False)
# print('get_cash_flow data capture completed!')
ts.get_day_all().to_csv(
save_dir + "/" + capture_date + "_get_day_all.csv",
header=True,
sep=",",
index=False,
)
print("get_day_all data capture completed!")
ts.get_cashflow_data(2018, 3).to_csv(
save_dir + "/" + capture_date + "_get_cashflow_data.csv",
header=True,
sep=",",
index=False,
)
print("get_cashflow_data data capture completed!")
ts.get_concept_classified().to_csv(
save_dir + "/" + capture_date + "_get_concept_classified.csv",
header=True,
sep=",",
index=False,
)
print("get_concept_classified data capture completed!")
ts.get_debtpaying_data(2018, 3).to_csv(
save_dir + "/" + capture_date + "_get_debtpaying_data.csv",
header=True,
sep=",",
index=False,
)
print("get_debtpaying_data data capture completed!")
ts.get_deposit_rate().to_csv(
save_dir + "/" + capture_date + "_get_deposit_rate.csv",
header=True,
sep=",",
index=False,
)
print("get_deposit_rate data capture completed!")
ts.get_gdp_contrib().to_csv(
save_dir + "/" + capture_date + "_get_gdp_contrib.csv",
header=True,
sep=",",
index=False,
)
ts.get_gdp_for().to_csv(
save_dir + "/" + capture_date + "_get_gdp_for.csv",
header=True,
sep=",",
index=False,
)
ts.get_gdp_pull().to_csv(
save_dir + "/" + capture_date + "_get_gdp_pull.csv",
header=True,
sep=",",
index=False,
)
ts.get_gdp_quarter().to_csv(
save_dir + "/" + capture_date + "_get_gdp_quarter.csv",
header=True,
sep=",",
index=False,
)
print("get_gdp_ data capture completed!")
# ts.get_gdp_year().to_csv(save_dir+'/'+capture_date+'_get_gdp_year.csv',header=True,sep=',',index=False)
ts.get_gem_classified().to_csv(
save_dir + "/" + capture_date + "_get_gem_classified.csv",
header=True,
sep=",",
index=False,
)
ts.get_gold_and_foreign_reserves().to_csv(
save_dir + "/" + capture_date + "_get_gold_and_foreign_reserves.csv",
header=True,
sep=",",
index=False,
)
ts.get_growth_data(2018, 3).to_csv(
save_dir + "/" + capture_date + "_get_growth_data.csv",
header=True,
sep=",",
index=False,
)
ts.get_industry_classified().to_csv(
save_dir + "/" + capture_date + "_get_industry_classified.csv",
header=True,
sep=",",
index=False,
)
ts.get_hs300s().to_csv(
save_dir + "/" + capture_date + "_get_hs300s.csv",
header=True,
sep=",",
index=False,
)
ts.get_sz50s().to_csv(
save_dir + "/" + capture_date + "_get_sz50s.csv",
header=True,
sep=",",
index=False,
)
ts.get_zz500s().to_csv(
save_dir + "/" + capture_date + "_get_zz500s.csv",
header=True,
sep=",",
index=False,
)
ts.get_operation_data(2018, 3).to_csv(
save_dir + "/" + capture_date + "_get_operation_data.csv",
header=True,
sep=",",
index=False,
)
ts.get_stock_basics().to_csv(
save_dir + "/" + capture_date + "_get_stock_basics.csv",
header=True,
sep=",",
index=False,
)
ts.get_report_data(2018, 3).to_csv(
save_dir + "/" + capture_date + "_get_report_data.csv",
header=True,
sep=",",
index=False,
)
ts.inst_detail().to_csv(
save_dir + "/" + capture_date + "_inst_detail.csv",
header=True,
sep=",",
index=False,
)
ts.inst_tops().to_csv(
save_dir + "/" + capture_date + "_inst_tops.csv",
header=True,
sep=",",
index=False,
)
print("inst_tops data capture completed!")
ts.new_stocks().to_csv(
save_dir + "/" + capture_date + "_new_stocks.csv",
header=True,
sep=",",
index=False,
)
print("new_stocks data capture completed!")
ts.top_list().to_csv(
save_dir + "/" + capture_date + "_top_list.csv",
header=True,
sep=",",
index=False,
)
print("top_list data capture completed!")
def Get_deposit_rate(self):
dt = ts.get_deposit_rate()
dt.to_csv('存款利率.csv')
print(dt)
def stat_all(tmp_datatime):
data = ts.get_deposit_rate()
common
def getdepositdb():
deposit=ts.get_deposit_rate()
#print(deposit, sep=' ', end='\n', file=sys.stdout, flush=False)
deposit.to_sql('deposit_data',ENGINE,if_exists='append')
def init(engine, session): tbl = "macro_deposit" tsl.log(tbl + " start...") df = ts.get_deposit_rate() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_loan" tsl.log(tbl + " start...") df = ts.get_loan_rate() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_rrr" tsl.log(tbl + " start...") df = ts.get_rrr() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_money_supply" tsl.log(tbl + " start...") df = ts.get_money_supply() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_money_supply_year" tsl.log(tbl + " start...") df = ts.get_money_supply_bal() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_year" tsl.log(tbl + " start...") df = ts.get_gdp_year() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_quarter" tsl.log(tbl + " start...") df = ts.get_gdp_quarter() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_for" tsl.log(tbl + " start...") df = ts.get_gdp_for() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_pull" tsl.log(tbl + " start...") df = ts.get_gdp_pull() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_contrib" tsl.log(tbl + " start...") df = ts.get_gdp_contrib() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_cpi" tsl.log(tbl + " start...") df = ts.get_cpi() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_ppi" tsl.log(tbl + " start...") df = ts.get_ppi() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "gold_and_foreign_reserves" tsl.log(tbl + " start...") df = ts.get_gold_and_foreign_reserves() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done")
def call_deposit_rate_v1():
df = ts.get_deposit_rate()
df = df[(df.date == df.date.max()) & (df.deposit_type == '定期存款整存整取(一年)')]
return df['rate'].max()
def job_5():
try:
print("I'm working......宏观经济数据")
# 存款利率
deposit_rate = ts.get_deposit_rate()
data = pd.DataFrame(deposit_rate)
data.to_sql('deposit_rate',engine,index=True,if_exists='replace')
print("存款利率......done")
# 贷款利率
loan_rate = ts.get_loan_rate()
data = pd.DataFrame(loan_rate)
data.to_sql('loan_rate',engine,index=True,if_exists='replace')
print("贷款利率......done")
# 存款准备金率
rrr = ts.get_rrr()
data = pd.DataFrame(rrr)
data.to_sql('rrr',engine,index=True,if_exists='replace')
print("存款准备金率......done")
# 货币供应量
money_supply = ts.get_money_supply()
data = pd.DataFrame(money_supply)
data.to_sql('money_supply',engine,index=True,if_exists='replace')
print("货币供应量......done")
# 货币供应量(年底余额)
money_supply_bal = ts.get_money_supply_bal()
data = pd.DataFrame(money_supply_bal)
data.to_sql('money_supply_bal',engine,index=True,if_exists='replace')
print("货币供应量(年底余额)......done")
# 国内生产总值(年度)
gdp_year = ts.get_gdp_year()
data = pd.DataFrame(gdp_year)
data.to_sql('gdp_year',engine,index=True,if_exists='replace')
print("国内生产总值(年度)......done")
# 国内生产总值(季度)
gdp_quarter = ts.get_gdp_quarter()
data = pd.DataFrame(gdp_quarter)
data.to_sql('gdp_quarter',engine,index=True,if_exists='replace')
print("国内生产总值(季度)......done")
# 三大需求对GDP贡献
gdp_for = ts.get_gdp_for()
data = pd.DataFrame(gdp_for)
data.to_sql('gdp_for',engine,index=True,if_exists='replace')
print("三大需求对GDP贡献......done")
# 三大产业对GDP拉动
gdp_pull = ts.get_gdp_pull()
data = pd.DataFrame(gdp_pull)
data.to_sql('gdp_pull',engine,index=True,if_exists='replace')
print("三大产业对GDP拉动......done")
# 三大产业贡献率
gdp_contrib = ts.get_gdp_contrib()
data = pd.DataFrame(gdp_contrib)
data.to_sql('gdp_contrib',engine,index=True,if_exists='replace')
print("三大产业贡献率......done")
# 居民消费价格指数
cpi = ts.get_cpi()
data = pd.DataFrame(cpi)
data.to_sql('cpi',engine,index=True,if_exists='replace')
print("居民消费价格指数......done")
# 工业品出厂价格指数
ppi = ts.get_ppi()
data = pd.DataFrame(ppi)
data.to_sql('ppi',engine,index=True,if_exists='replace')
print("工业品出厂价格指数......done")
except Exception as e:
print(e)
# coding: utf-8
import tushare as ts
import talib as tl
import tushare as ts
import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
from matplotlib import gridspec
import os
# # choose SSE 50 component index stocks as portfolio
sz_50 = ts.get_sz50s()
start = '2017-11-01'
end = '2018-11-01'
data = get_stock_data(sz_50,start,end)
df = ts.get_deposit_rate()
df.sort('data',ascending = True).head(10)
#No day trading, No sell mechanism, free interest rate (FR = 0.35%)
def get_stock_data(codes,start,end,freq = '5MIN'):
#codes = pandas.DataFrame(Tushare)
#start, end = str
print()
code = codes['code'].get_values()
api = ts.get_apis()
data = {}
for item in code:
try:
source = ts.bar(code=str(item),conn = api,start_date=start,end_date=end,freq = freq)
data[str(item)] = source
except:
def init_fund_info(self):
"""Init the info of the structured fund."""
# 1. Get the basic info.
url = 'http://www.abcfund.cn/style/fundlist.php'
reg_ex = r'<tr.*?><td>(.*?)</td></tr>'
split_str = '</td><td>'
data_list = web_crawler(url, reg_ex, split_str)
frame_info_1 = pd.DataFrame(data_list, columns=[
'm_code', 'm_name', 'establish_date', 'list_date', 'a_code', 'a_name', 'b_code',
'b_name', 'a_to_b', 'delist_date', 'current_annual_rate', 'i_code', 'i_name'])
frame_info_1 = frame_info_1[frame_info_1.a_code.str.contains(r'15|50')]
frame_info_1 = frame_info_1.set_index('m_code')
# 2. Get the info of rate adjustment.
url = 'http://www.abcfund.cn/data/arateadjustment.php'
reg_ex = r'<tr.*?><td>(.*?)</td></tr>'
split_str = '</td><td>'
data_list = web_crawler(url, reg_ex, split_str)
frame_info_2 = pd.DataFrame(data_list, columns=[
'm_code', 'm_name', 'rate_adjustment_condition', 'next_rate_adjustment_date'])
frame_info_2 = frame_info_2.drop('m_name', axis=1)
frame_info_2 = frame_info_2.set_index('m_code')
# 3. Get the conversion condition.
url = 'http://www.abcfund.cn/data/zsinfo.php'
reg_ex = r'onclick.*?><td>(.*?)</td><tr'
replace_str = '</td><td>'
split_str = '<td>'
data_list = web_crawler(url, reg_ex, split_str, replace_str)
frame_info_3 = pd.DataFrame(data_list, columns=[
'm_code', 'm_name', 'next_regular_conversion_date', 'days_to_next_regular_conversion_date',
'ascending_conversion_condition', 'descending_conversion_condition'])
frame_info_3 = frame_info_3.drop('m_name', axis=1)
frame_info_3 = frame_info_3.set_index('m_code')
# 4. Get the net value of m fund, a and b.
url = 'http://www.abcfund.cn/data/premium.php'
reg_ex = r'<tr.*?><td>(.*?)</td></tr>'
split_str = '</td><td>'
# Get the date of the data of net value.
reg_ex_date = r'\d{4}年\d{1,2}月\d{1,2}日'
data_list, date = web_crawler(url, reg_ex, split_str, reg_ex_2=reg_ex_date)
self.net_value_date = datetime.datetime.strptime(date[0], '%Y年%m月%d日').date()
frame_info_4 = pd.DataFrame(data_list, columns=[
'm_code', 'm_name', 'm_net_value', 'a_code', 'a_name', 'a_net_value',
'a_price', 'a_premium', 'a_volume', 'b_code', 'b_name', 'b_net_value', 'b_price', 'b_premium',
'b_volume', 'whole_premium'])
frame_info_4 = frame_info_4.loc[:, ['m_code', 'm_net_value', 'a_net_value', 'b_net_value']]
frame_info_4 = frame_info_4.set_index('m_code')
# 5. Join the data frames together.
self.frame_info = frame_info_1.join([frame_info_2, frame_info_3, frame_info_4], how='inner')
# Get the one-year deposit rate
deposit_name, deposit_rate = ts.get_deposit_rate().loc[6, ['deposit_type', 'rate']]
if deposit_name == '定期存款整存整取(一年)':
deposit_rate = float(deposit_rate) / 100
else:
logging.error('Failure in getting deposit rate!')
deposit_rate = 1.5 / 100
# Format the data of table
establish_date_column = []
list_date_column = []
delist_date_column = []
years_to_delist_date_column = []
a_in_10_column = []
a_to_b_column = []
current_annual_rate_column = []
rate_rule_column = []
next_annual_rate_column = []
next_rate_adjustment_date_column = []
days_to_next_rate_adjustment_date_column = []
rate_adjustment_condition_column = []
next_regular_conversion_date_column = []
ascending_conversion_condition_column = []
descending_conversion_condition_column = []
a_net_value_column = []
b_net_value_column = []
m_net_value_column = []
for index in self.frame_info.index:
fund = self.frame_info.loc[index, :]
try:
establish_date = datetime.datetime.strptime(fund.establish_date, '%Y-%m-%d').date()
except ValueError:
establish_date = None
try:
list_date = datetime.datetime.strptime(fund.list_date, '%Y-%m-%d').date()
except ValueError:
list_date = None
try:
delist_date = datetime.datetime.strptime(fund.delist_date, '%Y-%m-%d').date()
except ValueError:
delist_date = None
try:
years_to_delist_date = (delist_date - self.TODAY_DATE).days / 365
except TypeError:
years_to_delist_date = None
a_in_10 = (int(fund.a_to_b[-3:-2]) / (int(fund.a_to_b[-3:-2]) + int(fund.a_to_b[-1:]))) * 10
a_to_b = '{0}:{1}'.format(int(a_in_10), int(10-a_in_10))
rate_and_rule = fund.current_annual_rate.split('<br><font color=#696969>')
if index in self.MANUAL_CORRECT_RATE:
current_annual_rate, rate_rule = self.MANUAL_CORRECT_RATE[index]
elif len(rate_and_rule) > 1:
current_annual_rate = float(rate_and_rule[0][:-1]) / 100
rate_rule = rate_and_rule[1][:-7]
if rate_rule == '固定':
rate_rule = '固定' + rate_and_rule[0]
if '.' not in rate_rule:
rate_rule = rate_rule[:-1] + '.0%'
else:
current_annual_rate = None
rate_rule = rate_and_rule[0]
if '1年+' in rate_rule:
next_annual_rate = deposit_rate + float(rate_rule[3:-1]) / 100
elif '3年+' in rate_rule:
next_annual_rate = 2.75 / 100 + float(rate_rule[3:-1]) / 100
elif '固定' in rate_rule:
next_annual_rate = float(rate_rule[2:-1]) / 100
elif rate_rule == '特殊情况':
next_annual_rate = None
else:
# This is the rate of mother code '162215'
next_annual_rate = 0.0358
try:
next_rate_adjustment_date = datetime.datetime.strptime(
fund.next_rate_adjustment_date, '%Y-%m-%d').date()
except ValueError:
next_rate_adjustment_date = None
try:
days_to_next_rate_adjustment_date = (next_rate_adjustment_date - self.TODAY_DATE).days
except TypeError:
days_to_next_rate_adjustment_date = None
if '动态调整' in fund.rate_adjustment_condition:
rate_adjustment_condition = '动态调整'
elif '不定期' in fund.rate_adjustment_condition:
rate_adjustment_condition = '折算调整'
elif '不调整' in fund.rate_adjustment_condition:
rate_adjustment_condition = '不调整'
else:
rate_adjustment_condition = '定期调整'
try:
next_regular_conversion_date = datetime.datetime.strptime(
fund.next_regular_conversion_date, '%Y年%m月%d日').date()
except ValueError:
next_regular_conversion_date = None
if fund.ascending_conversion_condition[0] == '母':
ascending_conversion_condition = float(fund.ascending_conversion_condition[7:])
elif fund.ascending_conversion_condition[0] == 'B':
ascending_conversion_condition = float(fund.ascending_conversion_condition[6:]) * (-1)
else:
ascending_conversion_condition = None
if fund.descending_conversion_condition[0] == 'B':
descending_conversion_condition = float(fund.descending_conversion_condition[6:])
elif fund.descending_conversion_condition[0] == '母':
descending_conversion_condition = float(fund.descending_conversion_condition[7:]) * (-1)
else:
descending_conversion_condition = None
try:
a_net_value = float(fund.a_net_value)
except ValueError:
a_net_value = None
try:
b_net_value = float(fund.b_net_value)
except ValueError:
b_net_value = None
try:
m_net_value = float(fund.m_net_value)
except ValueError:
try:
m_net_value = (a_net_value * a_in_10 + b_net_value * (10 - a_in_10)) / 10
except TypeError:
m_net_value = None
establish_date_column.append(establish_date)
list_date_column.append(list_date)
years_to_delist_date_column.append(years_to_delist_date)
delist_date_column.append(delist_date)
a_in_10_column.append(a_in_10)
a_to_b_column.append(a_to_b)
current_annual_rate_column.append(current_annual_rate)
rate_rule_column.append(rate_rule)
next_annual_rate_column.append(next_annual_rate)
next_rate_adjustment_date_column.append(next_rate_adjustment_date)
days_to_next_rate_adjustment_date_column.append(days_to_next_rate_adjustment_date)
rate_adjustment_condition_column.append(rate_adjustment_condition)
next_regular_conversion_date_column.append(next_regular_conversion_date)
ascending_conversion_condition_column.append(ascending_conversion_condition)
descending_conversion_condition_column.append(descending_conversion_condition)
a_net_value_column.append(a_net_value)
b_net_value_column.append(b_net_value)
m_net_value_column.append(m_net_value)
self.frame_info['establish_date'] = establish_date_column
self.frame_info['list_date'] = list_date_column
self.frame_info['delist_date'] = delist_date_column
self.frame_info['years_to_delist_date'] = years_to_delist_date_column
self.frame_info['a_in_10'] = a_in_10_column
self.frame_info['a_to_b'] = a_to_b_column
self.frame_info['current_annual_rate'] = current_annual_rate_column
self.frame_info['rate_rule'] = rate_rule_column
self.frame_info['next_annual_rate'] = next_annual_rate_column
self.frame_info['next_rate_adjustment_date'] = next_rate_adjustment_date_column
self.frame_info['days_to_next_rate_adjustment_date'] = days_to_next_rate_adjustment_date_column
self.frame_info['rate_adjustment_condition'] = rate_adjustment_condition_column
self.frame_info['next_regular_conversion_date'] = next_regular_conversion_date_column
self.frame_info['ascending_conversion_condition'] = ascending_conversion_condition_column
self.frame_info['descending_conversion_condition'] = descending_conversion_condition_column
self.frame_info['a_net_value'] = a_net_value_column
self.frame_info['b_net_value'] = b_net_value_column
self.frame_info['m_net_value'] = m_net_value_column
# 5. Save the data into csv file
self.frame_info.to_csv('../data/structured_fund_info.csv')
