def writeUsStockGuruAnalysisToDB(self):
""" Insert Stock Revenue to tblStockNasdaqGuru (delete duplicate, mark delete old and insert new) """
stockNasdaqGurueTable = "tblStockNasdaqGuru"
# 1. delete duplicate: del record with same Ticker, ListedExch, Source and DataAsOfDate
databaseOper = clsDatabaseOper.DatabaseOper()
listDelCols = ["StockTicker", "ListedExchCode", "DataAsOfDate"]
listDelVals = [
self.compTicker, self.listedExchCode,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_AS_OF_DATE_FORMAT)
]
databaseOper.deleteRecord(stockNasdaqGurueTable, listDelCols,
listDelVals)
del databaseOper
# 2. mark delete old: update IsLatest to 'N' with same Ticker, ListedExch and Source
databaseOper = clsDatabaseOper.DatabaseOper()
listUpdCols = ["StockTicker", "ListedExchCode"]
listUpdVals = [self.compTicker, self.listedExchCode]
databaseOper.updateSingleValue(stockNasdaqGurueTable, "IsLatest", "N",
listUpdCols, listUpdVals)
del databaseOper
# 3. insert new record to DB
databaseOper = clsDatabaseOper.DatabaseOper()
listInstCols = [
"StockTicker", "ListedExchCode", "PeterLynchVerdict",
"PeterLynchAnalysis", "BenjaminGarhamVerdict",
"BenjaminGarhamAnalysis", "MomentumStrategyVerdict",
"MomentumStrategyAnalysis", "JamesOShaughnessyVerdict",
"JamesOShaughnessyAnalysis", "MotleyFoolVerdict",
"MotleyFoolAnalysis", "DavidDremanVerdict", "DavidDremanAnalysis",
"MartinZweigVerdict", "MartinZweigAnalysis",
"KennethFisherVerdict", "KennethFisherAnalysis", "DataAsOfDate"
]
listInstVals = [
self.compTicker, self.listedExchCode, self.guruPeterLynchVerdict,
self.guruPeterLynchValue, self.guruBenjaminGarhamVerdict,
self.guruBenjaminGarhamValue, self.guruPMomentumStrategyVerdict,
self.guruPMomentumStrategyValue, self.guruJamesOShaughnessyVerdict,
self.guruJamesOShaughnessyValue, self.guruMotleyFoolVerdict,
self.guruMotleyFoolValue, self.guruDavidDremanVerdict,
self.guruDavidDremanValue, self.guruMartinZweigVerdict,
self.guruMartinZweigValue, self.guruKennethFisherVerdict,
self.guruKennethFisherValue,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_AS_OF_DATE_FORMAT)
]
databaseOper.insertListOfData(stockNasdaqGurueTable, listInstVals,
listInstCols)
del databaseOper
return None
def writeUsStockNewsToDB(self):
stockNewsTable = "tblStockDailyNews"
# print("self.stockNewsList", self.stockNewsList)
for stockNews in self.stockNewsList:
try:
newsDate = datetime.datetime.strptime(stockNews[0],
self.GOOGLE_DATE_FORMAT)
except ValueError as timeError:
newsDate = datetime.datetime.now()
newsSource = stockNews[1].replace("'", "''")
newsText = stockNews[2].replace("'", "''")
newsUrl = stockNews[3].replace("'", "''")
#
# 1. delete duplicate: del record with same Ticker, ListedExch, Source and DataAsOfDate
databaseOper = clsDatabaseOper.DatabaseOper()
listDelCols = [
"StockTicker", "ListedExchCode", "Source", "DataAsOfDate"
]
listDelVals = [
self.stockTicker, self.exchangeCode, self.DATA_SOURCE,
newsDate.strftime(clsGeneralConstants.GeneralConstants.
DATA_AS_OF_DATE_FORMAT)
]
databaseOper.deleteRecord(stockNewsTable, listDelCols, listDelVals)
del databaseOper
# # 2. mark delete old: update IsLatest to 'N' with same Ticker, ListedExch and Source
# databaseOper = clsDatabaseOper.DatabaseOper()
# listUpdCols = ["StockTicker", "ListedExchCode", "Source"]
# listUpdVals = [self.stockTicker, self.exchangeCode, self.DATA_SOURCE]
# databaseOper.updateSingleValue(stockNewsTable, "IsLatest", "N", listUpdCols, listUpdVals)
# del databaseOper
#
# 3. insert new record to DB
databaseOper = clsDatabaseOper.DatabaseOper()
listInstCols = [
"StockTicker", "ListedExchCode", "Source", "NewsSource",
"NewsText", "NewsUrl", "DataAsOfDate"
]
listInstVals = [
self.stockTicker, self.exchangeCode, self.DATA_SOURCE,
newsSource, newsText, newsUrl,
newsDate.strftime(clsGeneralConstants.GeneralConstants.
DATA_AS_OF_DATE_FORMAT)
]
# print("listInstVals=",listInstVals)
databaseOper.insertListOfData(stockNewsTable, listInstVals,
listInstCols)
del databaseOper
return None
def writeUsStockRevenueToDB(self):
""" Insert Stock Revenue to tblStockRevenue (delete duplicate, mark delete old and insert new) """
stockRevenueTable = "tblStockRevenue"
# 1. delete duplicate: del record with same Ticker, ListedExch, Source and DataAsOfDate
databaseOper = clsDatabaseOper.DatabaseOper()
listDelCols = [
"StockTicker", "ListedExchCode", "Source", "DataAsOfDate"
]
listDelVals = [
self.compTicker, self.listedExchCode, self.DATA_SOURCE,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_AS_OF_DATE_FORMAT)
]
databaseOper.deleteRecord(stockRevenueTable, listDelCols, listDelVals)
del databaseOper
# 2. mark delete old: update IsLatest to 'N' with same Ticker, ListedExch and Source
databaseOper = clsDatabaseOper.DatabaseOper()
listUpdCols = ["StockTicker", "ListedExchCode", "Source"]
listUpdVals = [self.compTicker, self.listedExchCode, self.DATA_SOURCE]
databaseOper.updateSingleValue(stockRevenueTable, "IsLatest", "N",
listUpdCols, listUpdVals)
del databaseOper
# 3. insert new record to DB
databaseOper = clsDatabaseOper.DatabaseOper()
listInstCols = [
"StockTicker", "ListedExchCode", "Source", "EpsRegressGrowth",
"DataAsOfDate"
]
listInstVals = [
self.compTicker, self.listedExchCode, self.DATA_SOURCE,
self.forecastEarningsGrowth,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_AS_OF_DATE_FORMAT)
]
databaseOper.insertListOfData(stockRevenueTable, listInstVals,
listInstCols)
del databaseOper
return None
def markPotentialStocks(self, sqlField, sqlSort, sqlNumOfItems, updateCol):
''' Rank Stocks by Revenues figures (EPS, ) from DB.
First column is Stock Code; Second column is listed exchange code; Third column is company name.
'''
# PLACEHOLDER_LIST = ["<-CheckFieldParam->", "<-SortParam->", "<-CheckItems->"]
# SQL_VALUES_LIST = ["EpsRegressGrowth_Reuters", REVENUE_SQL_SORT, clsGeneralConstants.GeneralConstants.NUM_OF_ITEMS_TO_RANK]
listOfMarkedStocks = []
markSQL = ""
generalMethods = clsGeneralMethods.GeneralMethods()
markSQL = generalMethods.getSqlFromFile(
self.MARK_POTENTIAL_SQL_FILE_PATH)
if ("" != markSQL):
#format the SQL
markSQL = markSQL.replace("<-CheckFieldParam->", sqlField)
markSQL = markSQL.replace("<-SortParam->", sqlSort)
markSQL = markSQL.replace("<-CheckItems->", str(sqlNumOfItems))
listOfPotentialStocks = []
databaseOper = clsDatabaseOper.DatabaseOper()
listOfPotentialStocks = databaseOper.retrieveSelectMultiValuesSQL(
markSQL)
del databaseOper
listOfMarkedStocks = generalMethods.putRankingMarksToList(
listOfPotentialStocks, sqlSort)
for markedStock in listOfMarkedStocks:
#databaseOper.updateSingleValue(self, tableName, updateCol, updateVal, listOfKeyCols, listOfKeyValues):
listOfKeyValues = []
listOfKeyValues.append(markedStock[0])
listOfKeyValues.append(markedStock[1])
listOfKeyValues.append(
clsGeneralConstants.GeneralConstants.SQL_IS_LATEST_TRUE)
updateVal = "{:10.6f}".format(markedStock[-1]).strip()
databaseOper = clsDatabaseOper.DatabaseOper()
databaseOper.updateSingleValue(
self.FUNDAMENTAL_GURU_TABLE, updateCol, updateVal,
self.LIST_OF_FUNDAMENTAL_GURU_UPDATE_FIELD,
listOfKeyValues)
del databaseOper
return listOfMarkedStocks
def setPotentialStocksAsRankBase(self):
''' Retrieve List of Potential Stock and insert to the Fundamental Guru table as ranking base.
'''
generalMethods = clsGeneralMethods.GeneralMethods()
insertPotentialSQL = generalMethods.getSqlFromFile(
self.INSERT_POTENTIAL_SQL_FILE_PATH)
potentialSQL = generalMethods.getSqlFromFile(
self.SOURCE_POTENTIAL_SQL_FILE_PATH)
insertPotentialSQL = insertPotentialSQL.replace(
"<-potentialSQL->", potentialSQL)
if ("" != insertPotentialSQL):
databaseOper = clsDatabaseOper.DatabaseOper()
databaseOper.insertSelectRecords(insertPotentialSQL)
del databaseOper
return None
def retrievePotentialStocks(self):
''' Retrieve List of Potential Stock from DB.
First column is Stock Code; Second column is listed exchange code; Third column is company name.
'''
listOfPotentialStocks = []
potentialSQL = ""
generalMethods = clsGeneralMethods.GeneralMethods()
potentialSQL = generalMethods.getSqlFromFile(
self.RETRIEVE_INIT_POTENTIAL_SQL_FILE_PATH)
if ("" != potentialSQL):
databaseOper = clsDatabaseOper.DatabaseOper()
listOfPotentialStocks = databaseOper.retrieveSelectMultiValuesSQL(
potentialSQL)
del databaseOper
return listOfPotentialStocks
def writeIndexStaticToDB(self):
databaseOper = clsDatabaseOper.DatabaseOper()
tableName = "tblIndexStatic"
listOfKeyCols = ["IndexCode", "DataAsOfDate"]
listOfKeyVals = [
self.ric,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_RECORD_DATE_FORMAT)
]
listOfInCols = ["Name", "Source"]
listOfInVals = [self.name, self.DATA_SOURCE]
databaseOper.insertIfNotExist(tableName, listOfKeyCols, listOfKeyVals,
listOfInCols, listOfInVals)
del databaseOper
return None
def writeIndexDailyPriceToDB(self):
databaseOper = clsDatabaseOper.DatabaseOper()
tableName = "tblIndexDailyPrice"
listOfKeyCols = ["IndexCode", "Source", "DataAsOfDate"]
listOfKeyVals = [
self.ric, self.DATA_SOURCE,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_RECORD_DATE_FORMAT)
]
listOfInCols = [
"OpenPrice", "ClosePrice", "DayHighPrice", "DayLowPrice",
"PrevClosePrice"
]
listOfInVals = [
self.openPrice, self.closePrice, self.dayHighPrice,
self.dayLowPrice, self.prevClosePrice
]
databaseOper.insertIfNotExist(tableName, listOfKeyCols, listOfKeyVals,
listOfInCols, listOfInVals)
del databaseOper
return None
def writeUsStockHoldingInfoToDB(self):
stockShareHoldingTable = "tblStockDailySharesHolding"
# 1. delete duplicate: del record with same Ticker, ListedExch, Source and DataAsOfDate
databaseOper = clsDatabaseOper.DatabaseOper()
listDelCols = [
"StockTicker", "ListedExchCode", "Source", "DataAsOfDate"
]
listDelVals = [
self.compTicker, self.listedExchCode, self.DATA_SOURCE,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_AS_OF_DATE_FORMAT)
]
databaseOper.deleteRecord(stockShareHoldingTable, listDelCols,
listDelVals)
del databaseOper
# 2. mark delete old: update IsLatest to 'N' with same Ticker, ListedExch and Source
databaseOper = clsDatabaseOper.DatabaseOper()
listUpdCols = ["StockTicker", "ListedExchCode", "Source"]
listUpdVals = [self.compTicker, self.listedExchCode, self.DATA_SOURCE]
databaseOper.updateSingleValue(stockShareHoldingTable, "IsLatest", "N",
listUpdCols, listUpdVals)
del databaseOper
# 3. insert new record to DB
databaseOper = clsDatabaseOper.DatabaseOper()
listInstCols = [
"StockTicker", "ListedExchCode", "Source", "SharesOutstanding",
"TotalNumOfInstitutionalHolders", "TotalSharedHeldByInstitutions",
"PercentageHeldByInstitutions",
"NumOfInstitutionsBoughtNewPosition",
"NumOfInstitutionsSoldOutPosition", "NetNumOfInstitutionsTraded",
"SharesBoughtByInstitutions", "SharesSoldByInstitutions",
"NetSharesTradedByInstitutions", "NumOfInsidersBoughtIn3M",
"NumOfInsidersSoldIn3M", "NetNumOfInsidersTradedIn3M",
"SharesBoughtByInsidersIn3M", "SharesSoldByInsidersIn3M",
"NetNumOfSharesTradedByInsidersIn3M", "NumOfInsidersBoughtIn12M",
"NumOfInsidersSoldIn12M", "NetNumOfInsidersTradedIn12M",
"SharesBoughtByInsidersIn12M", "SharesSoldByInsidersIn12M",
"NetNumOfSharesTradedByInsidersIn12M", "DataAsOfDate"
]
listInstVals = [
self.compTicker, self.listedExchCode, self.DATA_SOURCE,
self.sharesOutstanding, self.numberOfInstitHolderHeldPos,
self.sharesInstitHolderHeld, self.percentageHeldByInstit,
self.numberOfInstitHolderBoughtNewPos,
self.numberOfInstitHolderSoldOutPos,
self.netNumberOfInstitHolderTradedOutPos,
self.sharesInstitHolderBoughtNew, self.sharesInstitHolderSoldOut,
self.netSharesInstitHolderTradedNew,
self.numberOfInsiderBoughtInThreeMonth,
self.numberOfInsiderSoldInThreeMonth,
self.netNumberOfInsiderHoldingInThreeMonth,
self.sharesInsiderBoughtInThreeMonth,
self.sharesInsiderSoldInThreeMonth,
self.netSharesInsiderTradedInThreeMonth,
self.numberOfInsiderBoughtInTwelveMonth,
self.numberOfInsiderSoldInTwelveMonth,
self.netNumberOfInsiderHoldingInTwelveMonth,
self.sharesInsiderBoughtInTwelveMonth,
self.sharesInsiderSoldInTwelveMonth,
self.netSharesInsiderTradedInTwelveMonth,
self.asOfDate.strftime(
clsGeneralConstants.GeneralConstants.DATA_AS_OF_DATE_FORMAT)
]
databaseOper.insertListOfData(stockShareHoldingTable, listInstVals,
listInstCols)
del databaseOper
return None