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)