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