def populateCountry(name, suffix):
log.info("populating Country with values %s:%s" % (name, suffix))
sqlQuery = "INSERT INTO COUNTRY (NAME, SUFFIX) VALUES (%s, %s);"
try:
db.insertSingle(sqlQuery, [name, suffix])
except:
log.error("error inserting country")
def populateIndustry(file):
log.info("populating INDUSTRY table with data from %s" % file)
industryList = []
with open(file, "r") as f:
reader = csv.reader(f)
companies = [ x for x in reader ]
for company in companies:
industryList.append(company[2])
industryList = list(set(industryList))
log.info("unique industries: %s" % ", ".join(industryList))
sqlQuery = "INSERT INTO INDUSTRY (NAME) VALUES (%s);"
for industry in industryList:
log.debug("trying to populate industry with value %s" % industry)
sqlValue = [industry]
db.insertSingle(sqlQuery, sqlValue)
def populateCompanies(file):
log.info("populating COMPANY table, getting data from %s" % file)
with open(file, "r") as f:
reader = csv.reader(f)
companies = [ x for x in reader ]
for company in companies:
companyName = company[0]
companyCode = company[1]
# get code for query
industry = company[2]
industryAsList = [industry]
sqlQuery = "SELECT ID FROM INDUSTRY WHERE NAME = %s;"
industryID = db.selectQuery(sqlQuery, industryAsList)
industryID = industryID[0]['id']
insertSQL = "INSERT INTO COMPANY (CODE, NAME, INDUSTRY_ID) VALUES (%s, %s, %s);"
try:
log.debug("trying to populate company with value %s" % companyName)
db.insertSingle(insertSQL, [companyCode, companyName, industryID])
except:
log.error("unable to insert %s into company" % companyName)
def getHistoricInfo(ycode):
code = ycode.split(".")[0]
#fromDay = "2000-01-01"
fromDay = "2013-11-21"
toDay = datetime.datetime.now().strftime("%Y-%m-%d")
log.info("getting historic [%s - %s] data for %s" % (fromDay, toDay, ycode))
try:
stockData = stock.get_historical_prices(ycode, fromDay, toDay)
except:
log.error("Unable to get historic data for %s" % ycode)
return None
historicDataQuery = """INSERT INTO HISTORIC_DATA
(DATE,
CODE,
HIGH,
LOW,
OPEN,
VOLUME)
VALUES (%s, %s, %s, %s, %s, %s);"""
days = stockData.keys()
for day in days:
date = str(day)
data = stockData[day]
# print data, type(data)
historicValues = [date,
code,
data['High'],
data['Low'],
data['Open'],
data['Volume']]
db.insertSingle(historicDataQuery, historicValues)
def insertData(data):
ycode = data['Symbol']
code = ycode.split(".")[0]
today = str(datetime.date.today())
keys = ["AverageDailyVolume",
"BookValue",
"Change",
"ChangeFromFiftydayMovingAverage",
"ChangeFromTwoHundreddayMovingAverage",
"ChangeFromYearHigh",
"ChangeFromYearLow",
"DaysHigh",
"DaysLow",
"DividendShare",
"DividendYield",
"EarningsShare",
"EBITDA",
"EPSEstimateCurrentYear",
"EPSEstimateCurrentYear",
"EPSEstimateNextQuarter",
"FiftydayMovingAverage",
"LastTradePriceOnly",
"MarketCapitalization",
"Open",
"PERatio",
"PercebtChangeFromYearHigh",
"PercentChange",
"PercentChangeFromFiftydayMovingAverage",
"PercentChangeFromTwoHundreddayMovingAverage",
"PercentChangeFromYearLow",
"PreviousClose",
"PriceBook",
"PriceEPSEstimateCurrentYear",
"PriceEPSEstimateCurrentYear",
"PriceEPSEstimateNextYear",
"PriceEPSEstimateNextYear",
"PriceSales",
"TwoHundreddayMovingAverage",
"Volume",
"YearHigh",
"YearLow"]
# validate data, 0 all null values
for key in keys:
data[key] = validate(data[key])
averagesQuery = """INSERT INTO AVERAGES
(DATE,
CODE,
AVERAGEDAILYVOLUME,
TWOHUNDREDDAYMOVINGAVERAGE,
FIFTYDAYMOVINGAVERAGE)
VALUES (%s, %s, %s, %s, %s);"""
changeQuery = """INSERT INTO CHANGE
(DATE,
CODE,
change,
changefromfiftydaymovingaverage,
changefromtwohundreddaymovingaverage,
changefromyearhigh,
changefromyearlow,
percentchangefromyearhigh,
percentchange,
percentchangefromfiftydaymovingaverage,
percentchangefromtwohundreddaymovingaverage,
percentchangefromyearlow)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
dailyQuery = """INSERT INTO DAILY
(DATE,
CODE,
volume,
dayshigh,
dayslow,
bookvalue,
open,
previousclose,
pricebook,
dividendshare,
dividendyield,
earningsshare,
marketcapitalization,
yearhigh,
yearlow,
peratio,
pricesales,
ebitda)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
epsQuery = """INSERT INTO EPS
(DATE,
CODE,
EPSESTIMATECURRENTYEAR,
EPSESTIMATENEXTQUARTER,
PRICEEPSESTIMATECURRENTYEAR,
PRICEEPSESTIMATENEXTYEAR)
VALUES (%s, %s, %s, %s, %s, %s);"""
historicQuery = """INSERT INTO HISTORIC_DATA
(DATE,
CODE,
HIGH,
LOW,
OPEN,
CLOSE,
VOLUME)
VALUES (%s, %s, %s, %s, %s, %s, %s);"""
avgValues = [today,
code,
data['AverageDailyVolume'],
data['TwoHundreddayMovingAverage'],
data['FiftydayMovingAverage']]
changeValues = [today,
code,
data['Change'],
data['ChangeFromFiftydayMovingAverage'],
data['ChangeFromTwoHundreddayMovingAverage'],
data['ChangeFromYearHigh'],
data['ChangeFromYearLow'],
data['PercebtChangeFromYearHigh'],
data['PercentChange'],
data['PercentChangeFromFiftydayMovingAverage'],
data['PercentChangeFromTwoHundreddayMovingAverage'],
data['PercentChangeFromYearLow']]
dailyValues = [today,
code,
data['Volume'],
data['DaysHigh'],
data['DaysLow'],
data['BookValue'],
data['Open'],
data['PreviousClose'],
data['PriceBook'],
data['DividendShare'],
data['DividendYield'],
data['EarningsShare'],
data['MarketCapitalization'],
data['YearHigh'],
data['YearLow'],
data['PERatio'],
data['PriceSales'],
data['EBITDA']]
epsValues = [today,
code,
data['EPSEstimateCurrentYear'],
data['EPSEstimateNextQuarter'],
data['PriceEPSEstimateCurrentYear'],
data['PriceEPSEstimateNextYear']]
historicValues = [today,
code,
data['DaysHigh'],
data['DaysLow'],
data['Open'],
data['PreviousClose'],
data['Volume']]
db.insertSingle(averagesQuery, avgValues)
db.insertSingle(changeQuery, changeValues)
db.insertSingle(dailyQuery, dailyValues)
db.insertSingle(epsQuery, epsValues)
db.insertSingle(historicQuery, historicValues)
