def get_financial_data(report_period=None, financial_type=None): """ :param report_period: :param financial_type: :return: a financial DataFrame() data report_period should be str(year)+'0331' or '0630' or '0930' or '1231' financial_type should be one of profitability solvency capital_structure operational_capability growth_ability cash_flow query financial data by TSL api """ if (report_period is None) or (financial_type is None): print("Invalid arguments!") stock_code = change_code(history_allA(report_period)) stock_code_str = ";".join(stock_code) tsl_statement = Admin.financial_dict[financial_type].format( stock_code_str, report_period) financial_df = tsl_frame(tsl_statement) financial_df = change_code( financial_df, option=1 if financial_type == "performance_forecast" else None) # financial_df['report_period'] = datetime.strptime(report_period, '%Y%m%d') financial_df['report_period'] = report_period columns = Admin.financial_column_dict[financial_type] financial_df = sort_df_columns(financial_df, columns) financial_df = financial_df.replace( [float("inf"), float("-inf")], 100000000) return financial_df
def get_base_data(self): """ :return: base_data which is a DataFrame() query all A stock base data such as open close high low and so on by TSL api """ num = date_to_num(self.date) stock_code = change_code(history_allA(self.date)) stock_code_str = ";".join(stock_code) t1 = Admin.base_data_statement.format(self.date, stock_code_str, num) daily_df = change_code(tsl_frame(t1)) # daily_df = change_code(pd.DataFrame(daily_data)) # daily_df['date'] = datetime.strptime(self.date, '%Y%m%d') daily_df['date'] = self.date columns = Admin.base_data_column daily_df = sort_df_columns(daily_df, columns) daily_df = daily_df.replace([float("inf"), float("-inf")], 100000000) return daily_df
def get_index_component(self, index_code, day=None): used_date = self.date if day is None else day if isinstance(index_code, str): tsl_statement = Admin.index_component_statement.format( index_code, used_date) df = change_code(tsl_frame(tsl_statement)) df[Admin.index_component_column[1]] = df[ Admin.index_component_column[0]] df_new = df[Admin.index_component_column[1]] return df_new elif isinstance(index_code, list): df_total = pd.DataFrame() for index in index_code: tsl_statement = Admin.index_component_statement.format( index, used_date) df = change_code(tsl_frame(tsl_statement)) df[Admin.index_component_column[1]] = df[ Admin.index_component_column[0]] df_new = df[Admin.index_component_column[1]] df_total = df_total.append(df_new) return df_total else: raise TypeError("index_code should be str or list not%s" % type(index_code))
# @Email : [email protected] # @Time :2018-7-2 10:31 from WindPy import w from TSL_function.TSL_connect import (history_allA, change_code) from Other_function.some_function import code_convert w.start() def get_sw_industry(code_list, date): """ :param code_list: :param date: :return: sw level one industry date should be trade date code_list can not duplicated """ if len(set(code_list)) != len(code_list): raise ValueError("duplicated value in code_list input") else: sw_industry_code = w.wsd(code_list, "indexcode_sw", date, date, "industryType=1").Data[0] return sw_industry_code if __name__ == '__main__': stock_code = code_convert(change_code(history_allA('20180627'))) industry_code = get_sw_industry(stock_code, '20180627')
def __init__(self): Admin = Admin() # connect MySQL self.conn = pymysql.connect(user=Admin.user, passwd=Admin.passwd, host=Admin.host, charset="utf8", use_unicode=True, port=Admin.port) self.cursor = self.conn.cursor() # create database print('Mysql connected') self.cursor.execute(Admin.createDBSQL) print('create database') self.conn.select_db(Admin.databaseName) print("select {} database".format(Admin.databaseName)) self.cursor.execute(Admin.createSQL_startST) print("create table startST") self.cursor.execute(Admin.createSQL_endST) print("create table endST") self.cursor.execute(Admin.createSQL_basedata) print("create table basedata") engine = create_engine( "mysql+pymysql://root:090390704lol@localhost/{}?charset=utf8". format(Admin.databaseName)) # self.engine = create_engine( # "mysql+mysqldb://{0}:{1}@{2}/{3}?charset=utf8".format(user, passwd, host, databasename)) start_day = '20180618' # today = datetime.today().strftime('%Y%m%d') today = '20180621' basedata_date = list( map(format_date, w.tdays(start_day, today, "").Data[0])) for date in basedata_date: stockcode = change_code(history_allA(date)) stockcode_str = ";".join(stockcode) t1 = """ setsysparam(pn_date(),inttodate({0})); setsysparam(pn_rate(),1); setsysparam(pn_rateday(),-1); return Query("","{1}",True,"", "stock_code",DefaultStockID(), "stock_name",CurrentStockName(), "high",high(), "low",low(), "pre_close",StockPrevClose3(), "close",close(), "open",open(), "pct_chg",StockZf3(), "turn",StockHsl3(), "volume_ratio",volrate(), "log_return",StockLnZf3(), "is_down",IsDown(), "is_up",IsUp(), "is_equal",IsEqual(), "ZT_one({2})",StockIsZt2(43270), "DT_one({3})",StockIsDt2(43270), "ZT({4})",StockIsZt(43270), "DT({5})",StockIsDt(43270), "ever_ZT({6})",StockIsCJZt(43270), "ever_DT({7})",StockIsCJDt(43270), "Total_value",StockTotalValue3(), "Total_liq_value",StockMarketValue3(), "PE_TTM",StockPE3_V(0), "PB",StockPNA3_II(), "PS",StockPMI3_V(), "PC",StockPCF3_V(), "PTB",StockPTBR3_II()); """.format(date, stockcode_str, date, date, date, date, date, date) daily_df = tsl_frame(t1) # daily_df = change_code(pd.DataFrame(daily_data)) daily_df['date'] = datetime.strptime(date, '%Y%m%d') # substitute columns which accompanied by date special_columns = DB_information.base_special_column daily_df[special_columns] = daily_df[[ i + '(%s)' % date for i in special_columns ]] for column in special_columns: del daily_df[column + '(%s)' % date] columns = DB_information.base_data_column daily_df = sort_df_columns(daily_df, columns) daily_df.to_sql("basedata", engine, if_exists='append', index=False, index_label=None) print("insert %s data into basedata" % date) self.conn.commit() self.cursor.close() self.conn.close() print("all done")