def load_data(cls, date, code_list=None): print("Downloading data ...") tuple_str = "(" for s in report_period_generator(period=32, date=date): tuple_str += "'" + s + "'," tuple_str = tuple_str[:-1] tuple_str += ")" sql = ''' SELECT S_INFO_WINDCODE, REPORT_PERIOD, ACTUAL_ANN_DT, STATEMENT_TYPE, NET_PROFIT_EXCL_MIN_INT_INC AS net_income, TOT_OPER_REV AS total_revenue, OPER_REV AS revenue, TOT_OPER_COST AS total_opcost, LESS_OPER_COST AS operating_cost, LESS_SELLING_DIST_EXP AS sale_expense, LESS_GERL_ADMIN_EXP AS management_expense, RD_EXPENSE AS research_expense, LESS_FIN_EXP AS financial_expense, OPER_PROFIT AS operating_profit FROM wind.ASHAREINCOME WHERE SUBSTR(S_INFO_WINDCODE, 1, 1) != 'A' AND STATEMENT_TYPE in (408001000, 408004000, 408005000, 408050000) AND REPORT_PERIOD in {0} AND ACTUAL_ANN_DT <= {1} '''.format(tuple_str, date) df = db.query_by_SQL("wind", sql) return df, date
def load_data(cls, date, code_list=None): print("Downloading data ...") tuple_str = "(" for s in report_period_generator(period=32, date=date): tuple_str += "'" + s + "'," tuple_str = tuple_str[:-1] tuple_str += ")" sql = ''' SELECT S_INFO_WINDCODE, REPORT_PERIOD, ACTUAL_ANN_DT, STATEMENT_TYPE, NET_CASH_FLOWS_OPER_ACT AS operating_cashflow, STOT_CASH_INFLOWS_OPER_ACT AS operating_cashinflow, STOT_CASH_OUTFLOWS_OPER_ACT AS operating_cashoutflow, STOT_CASH_INFLOWS_INV_ACT AS investment_cashinflow, STOT_CASH_OUTFLOWS_INV_ACT AS investment_cashoutflow, NET_CASH_FLOWS_INV_ACT AS investment_cashflow, STOT_CASH_INFLOWS_FNC_ACT AS finance_cashinflow, STOT_CASH_OUTFLOWS_FNC_ACT AS finance_cashoutflow, NET_CASH_FLOWS_FNC_ACT AS finance_cashflow FROM wind.ASHARECASHFLOW WHERE SUBSTR(S_INFO_WINDCODE, 1, 1) != 'A' AND STATEMENT_TYPE in (408001000, 408004000, 408005000, 408050000) AND REPORT_PERIOD in {0} AND ACTUAL_ANN_DT <= {1} '''.format(tuple_str, date) df = db.query_by_SQL("wind", sql) pd.set_option("display.max_columns", None) return df, date
def get_listed_stocks(): sql = ''' SELECT S_INFO_WINDCODE FROM wind.ASHAREDESCRIPTION WHERE S_INFO_DELISTDATE IS NULL AND substr( S_INFO_WINDCODE, 1, 1 ) != 'A' ''' stock_list_df = __db.query_by_SQL("wind", sql) return stock_list_df
def load_data(cls, date, code_list=None): print("Downloading data ...") tuple_str = "(" for s in report_period_generator(period=32, date=date): tuple_str += "'" + s + "'," tuple_str = tuple_str[:-1] tuple_str += ")" sql = ''' SELECT S_INFO_WINDCODE, REPORT_PERIOD, ACTUAL_ANN_DT, STATEMENT_TYPE, TOT_ASSETS AS total_assets, TOT_SHRHLDR_EQY_EXCL_MIN_INT AS total_equities_exc_min, TOT_SHRHLDR_EQY_INCL_MIN_INT AS total_equities_inc_min, TOT_NON_CUR_LIAB AS noncur_liabilities, TOT_LIAB AS total_liabilities, LT_BORROW AS longterm_loan, BONDS_PAYABLE AS bonds_payable, LT_PAYABLE AS longterm_payable, OTHER_EQUITY_TOOLS_P_SHR AS preferred_stock, MONETARY_CAP AS cash, TRADABLE_FIN_ASSETS AS tradable_financialasset, NOTES_RCV AS notes_receiveable, ACCT_RCV AS accounts_receivable, INVENTORIES AS inventory, FIX_ASSETS AS fixed_asset, CONST_IN_PROG AS construction_inprogress, INTANG_ASSETS AS intangible_asset, R_AND_D_COSTS AS development_expenditure, GOODWILL AS goodwill, NOTES_PAYABLE AS notes_payable, ACCT_PAYABLE AS accounts_payable FROM wind.ASHAREBALANCESHEET WHERE SUBSTR(S_INFO_WINDCODE, 1, 1) != 'A' AND STATEMENT_TYPE in (408001000, 408004000, 408005000, 408050000) AND REPORT_PERIOD in {0} AND ACTUAL_ANN_DT <= {1} '''.format(tuple_str, date) df = db.query_by_SQL("wind", sql) return df, date
from common.logger import log, default_logger_decorator, operator_logger_decorator from common.sys_config import conf_dict from common import db #默认加载交易日历 calendar = db.query_by_SQL( 'wind', r"select TRADE_DAYS as trade_date from wind.AShareCalendar where S_INFO_EXCHMARKET='SZSE' and TRADE_DAYS>='20000101' order by TRADE_DAYS" )['trade_date']