def __init__(self, dbPathFactorValues, dbPathMarketData, begDate, endDate, logger=None): """Constructor""" if logger == None: self.logger = logging.logger() else: self.logger = logger dbPathRawData = GetPath.GetLocalDatabasePath()["RawEquity"] dbPathProcessedData = GetPath.GetLocalDatabasePath()["ProcEquity"] #价格数据录入内存数据库 self.connDbMkt = lite.connect(":memory:") self.connDbMkt.text_factory = str cur = self.connDbMkt.cursor() self.logger.info( "<{}>-Load local database into in-memory database".format( __name__.split('.')[-1])) _dbPathMarketData = dbPathRawData + dbPathMarketData cur.execute("ATTACH '{}' AS MktData".format(_dbPathMarketData)) cur.execute( "CREATE TABLE MktData AS SELECT StkCode,Date,LC,TC FROM MktData.AStockData WHERE Date>='{}'" .format(begDate)) cur.execute("CREATE INDEX mId ON MktData (Date,StkCode)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) #连接因子数据库 dbFactorValue = dbPathProcessedData + dbPathFactorValues self.connDbFV = lite.connect(dbFactorValue) self.connDbFV.text_factory = str #获取交易日 self._trdDays = GetTrdDay.GetTradeDays() self.revalueDays = [] self.trdDays = [] cur = self.connDbFV.cursor() cur.execute("SELECT DISTINCT Date FROM FactorValues ORDER BY Date ASC") rows = cur.fetchall() for row in rows: self.revalueDays.append(row[0]) for d in self._trdDays: if d >= self.revalueDays[0] and d <= endDate and d >= begDate: self.trdDays.append(d) #Initialte CalculatePortfolioReturn self.objCalcPortReturn = CalcPortRet.CalculatePortfolioReturn( dbPathMarketData, self.connDbMkt, self.logger)
def LoadDatabase(self, dbNameMarketData, startDate, useInMemory, conn): """""" self.logger.info("<{}>-Load local market database".format( __name__.split('.')[-1])) dbPath = GetPath.GetLocalDatabasePath()["EquityDataRaw"] dbPath = dbPath + dbNameMarketData if conn != None: self.conn = conn else: if useInMemory == 1: self.conn = lite.connect(":memory:") self.conn.text_factory = str cur = self.conn.cursor() cur.execute("ATTACH '{}' AS MarketData".format(dbPath)) cur.execute( "CREATE TABLE AStockData AS SELECT StkCode,Date,LC,TC,Vol FROM MarketData.AStockData WHERE Date>'{}'" .format(startDate)) cur.execute("CREATE INDEX idS ON AStockData(Date,StkCode)") cur.execute( "CREATE TABLE IndexData AS SELECT StkCode,Date,LC,TC,Vol FROM MarketData.IndexData WHERE Date>'{}'" .format(startDate)) cur.execute("CREATE INDEX idI ON IndexData(Date,StkCode)") else: self.conn = lite.connect(dbPath) self.conn.text_factory = str
def LoadFactorTablesIntoMemory(self, dbNameFactor, factorTypes): """""" dbPath = GetPath.GetLocalDatabasePath()["EquityDataRefined"] dbPath = dbPath + dbNameFactor self.conn1 = lite.connect(dbPath) self.conn1.text_factory = str self.conn2 = lite.connect(":memory:") self.conn2.text_factory = str cur = self.conn2.cursor() cur.execute("ATTACH '{}' AS FVData".format(dbPath)) self.fundamentalConn = self.conn1 self.technicalConn = self.conn1 self.analystConn = self.conn1 if "Fundamental" in factorTypes: cur.execute( "CREATE TABLE FinancialPITData AS SELECT * FROM FVData.FinancialPointInTimeData" ) cur.execute( "CREATE INDEX idF ON FinancialPITData(StkCode,DeclareDate)") self.fundamentalConn = self.conn2 if "Technical" in factorTypes: cur.execute( "CREATE TABLE TechnicalFactors AS SELECT * FROM FVData.TechnicalFactors" ) cur.execute("CREATE INDEX idT ON TechnicalFactors(StkCode,Date)") self.technicalConn = self.conn2 if "Analyst" in factorTypes: cur.execute( "CREATE TABLE AnalystFactors AS SELECT * FROM FVData.AnalystFactors" ) cur.execute("CREATE INDEX idA ON AnalystFactors(StkCode,Date)") self.analystConn = self.conn2
def __init__(self, mktDataDbName, ifLoadMemoryDb, rawDataStartDate): """Constructor""" mktDbPath = GetPath.GetLocalDatabasePath()["RawEquity"] + mktDataDbName if ifLoadMemoryDb == 1: self.conn = lite.connect(":memory:") self.conn.text_factory = str cur = self.conn.cursor() print "Load market data into memory database" cur.execute("ATTACH '{}' AS MktData".format(mktDbPath)) cur.execute( "CREATE TABLE AStockData AS SELECT StkCode,Date,TC_Adj,Vol,Statu FROM MktData.AStockData WHERE Date>='{}'" .format(rawDataStartDate)) cur.execute( "CREATE TABLE IndexData AS SELECT StkCode,Date,TC,Vol FROM MktData.IndexData WHERE Date>='{}'" .format(rawDataStartDate)) print "Done" print "Create index on table StockData and table IndexData" cur.execute("CREATE INDEX idS ON AStockData (StkCode,Date)") cur.execute("CREATE INDEX idI ON IndexData (StkCode,Date)") print "Done" else: print "Directly connect to local database" self.conn = lite.connect(mktDbPath) self.conn.text_factory = str self.indexConstituents = InvestUniver.GetIndexCompStocks( "MktGenInfo\\IndexComp_Wind_CICC.db") self.rawDataStartDate = rawDataStartDate
def __init__(self,mktDataDbName,rawDataStartDate,stockData=0,fundData=0): """Constructor""" mktDbPath = GetPath.GetLocalDatabasePath()["RawEquity"]+mktDataDbName self.conn = lite.connect(":memory:") self.conn.text_factory = str cur = self.conn.cursor() print "Load market data into memory database" cur.execute("ATTACH '{}' AS MktData".format(mktDbPath)) self.appendStockData = stockData if stockData==1: cur.execute("CREATE TABLE StockData AS SELECT StkCode,Date,OP_Adj,HP_Adj,LP_Adj,TC_Adj,Statu,Vol,Amt FROM MktData.A_Share_Data WHERE Date>='{}'".format(rawDataStartDate)) self.appendFundData = fundData if fundData==1: cur.execute("CREATE TABLE FundData AS SELECT StkCode,Date,OP_Adj,HP_Adj,LP_Adj,TC_Adj,Statu,Vol,Amt FROM MktData.ExchangeTradedFund WHERE Date>='{}'".format(rawDataStartDate)) cur.execute("CREATE TABLE IndexData AS SELECT StkCode,Date,OP,HP,LP,TC,Vol,Amt FROM MktData.IndexData WHERE Date>='{}'".format(rawDataStartDate)) print "Done" print "Create index on table StockData and table IndexData" if stockData==1: cur.execute("CREATE INDEX idS ON StockData (StkCode,Date)") if fundData==1: cur.execute("CREATE INDEX idF ON FundData (StkCode,Date)") cur.execute("CREATE INDEX idI ON IndexData (StkCode,Date)") print "Done" self.indexConstituents = InvestUniver.GetIndexCompStocks("MktGenInfo\\IndexComp_Wind_CICC.db") self.rawDataStartDate = rawDataStartDate
def __init__(self, mktDataDbAddr, conn=None, logger=None): """Constructor""" if logger == None: self.logger = logging.Logger("") else: self.logger = logger if conn != None: self.conn = conn else: self.conn = lite.connect(":memory:") self.conn.text_factory = str cur = self.conn.cursor() self.logger.info( "<{}>-Load local database into in-memory database".format( __name__.split('.')[-1])) locDbPath = GetPath.GetLocalDatabasePath() _mktDataDbAddr = locDbPath["RawEquity"] + mktDataDbAddr cur.execute("ATTACH '{}' AS MktData".format(_mktDataDbAddr)) cur.execute( "CREATE TABLE MktData AS SELECT StkCode,Date,LC,TC FROM MktData.AStockData" ) cur.execute("CREATE INDEX mId ON MktData (Date,StkCode)".format( __name__.split('.')[-1])) self.logger.info("<{}>-Done")
def __init__(self, logger=None): """Constructor""" #Create log file if logger == None: self.logger = LogHandler.LogOutputHandler("SyncFinRpt.log") else: self.logger = logger self.locDbPath = GetPath.GetLocalDatabasePath()
def __init__(self,logger=None): """Constructor""" #Create log file if logger == None: self.logger = LogHandler.LogOutputHandler("ComputeFactorsAndZScores") else: self.logger = logger dbPathProcessedData = GetPath.GetLocalDatabasePath()["EquityDataRefined"] self.dbPathProcessedData = dbPathProcessedData self.totalTradeDay = GetTrdDay.GetTradeDays()
def GetTradeDays(dbName="MktGenInfo\\TradeDate_Wind_CICC.db"): """""" locDbPath = GetPath.GetLocalDatabasePath() trdDateDbAddr = locDbPath["EquityDataRaw"] + dbName conn = lite.connect(trdDateDbAddr) conn.text_factory = str cur = conn.cursor() cur.execute("SELECT TrdDate FROM TradeDate ORDER BY TrdDate ASC") rows = cur.fetchall() trdDay = [] for row in rows: trdDay.append(row[0]) return trdDay
def __init__(self,dbPathPITFundamentalData,dbPathMarketData,conn=None,logger=None): """Constructor""" #Create log file if logger == None: self.logger = LogHandler.LogOutputHandler("CalculateFundamentalFactorValues.log") else: self.logger = logger #Load data into in-memory database if conn!=None: self.conn = conn else: self.conn = lite.connect(":memory:") self.conn.text_factory = str cur = self.conn.cursor() self.logger.info("<{}>-Load local database into in-memory database...".format(__name__.split('.')[-1])) locDbPath = GetPath.GetLocalDatabasePath() _dbPathPITFundamentalData = locDbPath["EquityDataRefined"]+dbPathPITFundamentalData _dbPathMarketData = locDbPath["EquityDataRaw"]+dbPathMarketData cur.execute("ATTACH '{}' AS FdmtData".format(_dbPathPITFundamentalData)) cur.execute("ATTACH '{}' AS MktData".format(_dbPathMarketData)) self.logger.info("<{}>-Load table FinancialPointInTimeData".format(__name__.split('.')[-1])) cur.execute("CREATE TABLE FinancialPITData AS SELECT * FROM FdmtData.FinancialPointInTimeData") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Load table ForecastPintInTimeData".format(__name__.split('.')[-1])) cur.execute("CREATE TABLE ForecastPITData AS SELECT * FROM FdmtData.ForecastPointInTimeData") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Load table MarketData".format(__name__.split('.')[-1])) cur.execute("CREATE TABLE MktData AS SELECT StkCode,Date,TC,LC,TC_Adj FROM MktData.AStockData WHERE Date>='20060101'") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Load talbe MarketCap".format(__name__.split('.')[-1])) cur.execute("CREATE TABLE MktCap AS SELECT * FROM MktData.MarketCap") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table FinancialPITData".format(__name__.split('.')[-1])) cur.execute("CREATE INDEX fiId ON FinancialPITData (StkCode,DeclareDate)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table ForecastData".format(__name__.split('.')[-1])) cur.execute("CREATE INDEX fcId ON ForecastPITData (StkCode,DeclareDate)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table MarketData".format(__name__.split('.')[-1])) cur.execute("CREATE INDEX mId ON MktData (StkCode,Date)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table MarketCap".format(__name__.split('.')[-1])) cur.execute("CREATE INDEX cId ON MktCap (StkCode,Date)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1]))
def ConnectToFactorDatabase(self, dbNameFactorValues): """ 读取基本面因子数据库 """ self.logger.info("<{}>-Connect to local factor database".format( __name__.split('.')[-1])) dbPathProcessedData = GetPath.GetLocalDatabasePath()["ProcEquity"] self.conn = lite.connect(dbPathProcessedData + dbNameFactorValues) self.conn.text_factory = str self.logger.info( "<{}>-Create in-memory database for factor values".format( __name__.split('.')[-1])) self.connIM = lite.connect(":memory:") self.connIM.text_factory = str curIM = self.connIM.cursor() curIM.execute("ATTACH '{}' AS FactorVals".format(dbPathProcessedData + dbNameFactorValues))
def __init__(self, dbAddress, logger=None): """Constructor""" #Create log file if logger == None: self.logger = LogHandler.LogOutputHandler("SyncFinRpt.log") else: self.logger = logger locDbPath = GetPath.GetLocalDatabasePath() lite.register_adapter(decimal.Decimal, lambda x: str(x)) self.conn = lite.connect(":memory:") cur = self.conn.cursor() self.conn.text_factory = str cur.execute( "ATTACH '{}' AS _IndexComp".format(locDbPath["EquityDataRaw"] + dbAddress)) self.logger.info("<{}>-Load table IndexConstituent".format( __name__.split('.')[-1])) cur.execute( "CREATE TABLE IndexComp AS SELECT * FROM _IndexComp.IndexComp") self.logger.info("<{}>-Load table sywg industry classification".format( __name__.split('.')[-1])) cur.execute( "CREATE TABLE SWIndustry1st AS SELECT * FROM _IndexComp.SWIndustry1st" ) self.logger.info("<{}>-Create index on table IndexConstituent".format( __name__.split('.')[-1])) cur.execute( "CREATE INDEX idI ON IndexComp (IndexCode,IncDate,ExcDate,StkCode)" ) self.logger.info( "<{}>-Create index on table sywg industry classification".format( __name__.split('.')[-1])) cur.execute( "CREATE INDEX idS ON SWIndustry1st (IndusCode,IncDate,ExcDate,StkCode)" )
def __init__(self, mktDataDbName, ifLoadMemoryDb, startDate): """Constructor""" mktDbPath = GetPath.GetLocalDatabasePath()["RawEquity"] + mktDataDbName if ifLoadMemoryDb == 1: self.conn = lite.connect(":memory:") self.conn.text_factory = str cur = self.conn.cursor() print "Load market data into memory database" cur.execute("ATTACH '{}' AS MktData".format(mktDbPath)) cur.execute( "CREATE TABLE StockData AS SELECT StkCode,Date,TC_Adj,Statu FROM MktData.A_Share_Data WHERE Date>='{}'" .format(startDate)) cur.execute( "CREATE TABLE IndexData AS SELECT StkCode,Date,TC FROM MktData.IndexData WHERE Date>='{}'" .format(startDate)) print "Done" print "Create index on table StockData and table IndexData" cur.execute("CREATE INDEX idS ON StockData (StkCode,Date)") cur.execute("CREATE INDEX idI ON IndexData (StkCode,Date)") print "Done" else: print "Directly connect to local database" self.conn = lite.connect(mktDbPath) self.conn.text_factory = str
def __init__(self, dbPathMarketData, dbPathConstituentData, conn=None, logger=None): """Constructor""" #Create log file if logger == None: self.logger = LogHandler.LogOutputHandler( "CalculateTechnicalFactorValues.log") else: self.logger = logger #Load data into in-memory database if conn != None: self.conn = conn else: self.conn = lite.connect(":memory:", check_same_thread=False) self.conn.text_factory = str cur = self.conn.cursor() self.logger.info( "<{}>-Load local database into in-memory database...".format( __name__.split('.')[-1])) locDbPath = GetPath.GetLocalDatabasePath() _dbPathMarketData = locDbPath["EquityDataRaw"] + dbPathMarketData cur.execute("ATTACH '{}' AS MktData".format(_dbPathMarketData)) _dbPathConstituentData = locDbPath[ "EquityDataRaw"] + dbPathConstituentData cur.execute("ATTACH '{}' AS ConstituentData".format( _dbPathConstituentData)) self.logger.info("<{}>-Load table MarketData".format( __name__.split('.')[-1])) cur.execute( """CREATE TABLE MktData AS SELECT StkCode,Date,TC,LC,TC_Adj,Vol,Amt,Statu, SmallOrderDiff,MiddleOrderDiff,BigOrderDiff,InstOrderDiff,InBOrderDiff, SmallOrderDiffActive,MiddleOrderDiffActive,BigOrderDiffActive,InstOrderDiffActive,InBOrderDiffActive, NetInFlow,NetInFlowRatio FROM MktData.AStockData WHERE Date>='20060101'""" ) self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Load talbe MarketCap".format( __name__.split('.')[-1])) cur.execute( "CREATE TABLE MktCap AS SELECT * FROM MktData.MarketCap") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Load talbe IndexData".format( __name__.split('.')[-1])) cur.execute( "CREATE TABLE IndexData AS SELECT Date,StkCode,TC FROM MktData.IndexData" ) self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Load talbe ConstituentData".format( __name__.split('.')[-1])) cur.execute( "CREATE TABLE Constituent AS SELECT StkCode,StkName,IncDate,IndusCode,IndusName FROM ConstituentData.SWIndustry1st" ) self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table MarketData".format( __name__.split('.')[-1])) cur.execute("CREATE INDEX mId ON MktData (StkCode,Date)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table IndexData".format( __name__.split('.')[-1])) cur.execute("CREATE INDEX iId ON IndexData (StkCode,Date)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table MarketCap".format( __name__.split('.')[-1])) cur.execute("CREATE INDEX cId ON MktCap (StkCode,Date)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1])) self.logger.info("<{}>-Create index on table Constituent".format( __name__.split('.')[-1])) cur.execute("CREATE INDEX csId ON Constituent (StkCode,IncDate)") self.logger.info("<{}>-Done".format(__name__.split('.')[-1]))