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 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')