sector = range(1, 10) #list of 1-9 properties = "coname" sort = "u" static = ".csv" con = getMysqlConnection() for i in sector: time.sleep(5) try: csvReader = yahooFinanceAPIRequest(url + str(i) + properties + sort + static) except: print "Error with URL Request/ Converting CSV " + url + str(i) + properties + sort + static try: with con: cur = con.cursor() for row in csvReader: cur.execute('select id from industry where name="' + row["Industry"] + '"') industryId = cur.fetchone() #If the industry is not present then create it if industryId is None: cur.execute('insert into industry (name, sector_id) values ("' + row["Industry"] + '", ' + str(i) + ')') cur.execute('select id from industry where name="' + row["Industry"] + '"') industryId = cur.fetchone() cur.execute('insert into industries (sector_number, industry, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (' + str(i) + ', '+ str(industryId[0]) + ', ' + row["1-Day Price Chg %"]+ ', ' + convert(row["Market Cap"]) +', "' + row["P/E"]+'", ' + row["ROE %"]+', ' + convert(row["Div. Yield %"])+', ' + convert(row["Debt to Equity"])+', ' + row["Price to Book"]+', ' + row["Net Profit Margin (mrq)"]+', ' + row["Price To Free Cash Flow (mrq)"] +' )') con.commit() except db.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) con.close()
html = html.partition('\n') single = html[2].partition('\n') row = single[0].rsplit(',') #print row html = html[2] if len(row) == 10: cur.execute('select id from industry where name=' + row[0] + '') industryId = cur.fetchone() if industryId is None: cur.execute('insert into industry (name, sector_id) values (' + row[0] + ', ' + str(i) + ')') cur.execute('select id from industry where name=' + row[0] + '') industryId = cur.fetchone() #print 'insert into industries (sector_number, industry, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (' + str(i) + ', '+ str(industryId[0]) + ', ' + row[1]+ ', ' + convert(row[2]) +', "' + row[3]+'", ' + row[4]+', ' + row[5]+', ' + row[6]+', ' + row[7]+', ' + row[8]+', ' + row[9] +' )' cur.execute('insert into industries (sector_number, industry, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (' + str(i) + ', '+ str(industryId[0]) + ', ' + row[1]+ ', ' + convert(row[2]) +', "' + row[3]+'", ' + row[4]+', ' + convert(row[5])+', ' + convert(row[6])+', ' + row[7]+', ' + row[8]+', ' + row[9] +' )') else: cur.execute('select id from industry where name=' + row[0] + row[1]+ '') industryId = cur.fetchone() if industryId is None: cur.execute('insert into industry (name, sector_id) values (' + row[0] + row[1] + ', ' + str(i) + ')') cur.execute('select id from industry where name=' + row[0] + row[1]+ '') industryId = cur.fetchone() #print 'insert into industries (sector_number, industry, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (' + str(i) + ', '+ str(industryId[0]) + ', ' + row[2] +', ' + convert(row[3]) +', ' + row[4]+', ' + row[5]+', ' + row[6]+', ' + row[7]+', ' + row[8]+', ' + row[9] +', ' + row[10] +')' cur.execute('insert into industries (sector_number, industry, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (' + str(i) + ', '+ str(industryId[0]) + ', ' + row[2] +', ' + convert(row[3]) +', ' + row[4]+', ' + row[5]+', ' + convert(row[6])+', ' + convert(row[7]) +', ' + row[8]+', ' + row[9] +', ' + row[10] +')') except db.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) #sys.exit(1) #print str(i) + ', '+ row[0] + ', ' + row[1]+ ', "' + row[2] +'", "' + row[3]+'", ' + row[4]+', ' + row[5]+', ' + row[6]+', ' + row[7]+', ' + row[8]+', ' + row[9]
if len(row) == 1: rest = row[0].rsplit(',') #print row #print "rest" + rest[0] html = html[2] if len(rest) == 10: cur.execute('select id from company where name=' + rest[0] + '') companyId = cur.fetchone() if companyId is None: cur.execute('insert into company (name) values (' + rest[0] + ')') cur.execute('select id from company where name=' + rest[0] + '') companyId = cur.fetchone() #print companyId[0] #print str(rest[0]) + ', ' + convert(rest[1])+ ', "' + convert(rest[2]) +'", "' + convert(rest[3])+'", ' + convert(rest[4])+', ' + convert(rest[5])+', ' + convert(rest[6])+', ' + convert(rest[7])+', ' + convert(rest[8])+', ' + convert(rest[9]) cur.execute('insert into companies (company_id, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values ('+ str(companyId[0]) + ', ' + convert(rest[1])+ ', "' + convert(rest[2]) +'", "' + convert(rest[3])+'", ' + convert(rest[4])+', ' + convert(rest[5])+', ' + convert(rest[6])+', ' + convert(rest[7])+', ' + convert(rest[8])+', ' + convert(rest[9]) +' )') elif len(rest) == 11: cur.execute('select id from company where name=' + rest[0] + rest[1] + '') companyId = cur.fetchone() if companyId is None: cur.execute('insert into company (name) values (' + rest[0] + rest[1] + ')') cur.execute('select id from company where name=' + rest[0] + rest[1] + '') companyId = cur.fetchone() #print companyId[0] #print str(rest[0]) + ', ' + convert(rest[1])+ ', "' + convert(rest[2]) +'", "' + convert(rest[3])+'", ' + convert(rest[4])+', ' + convert(rest[5])+', ' + convert(rest[6])+', ' + convert(rest[7])+', ' + convert(rest[8])+', ' + convert(rest[9]) cur.execute('insert into companies (company_id, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values ('+ str(companyId[0]) + ', ' + convert(rest[2])+ ', "' + convert(rest[3]) +'", "' + convert(rest[4])+'", ' + convert(rest[5])+', ' + convert(rest[6])+', ' + convert(rest[7])+', ' + convert(rest[8])+', ' + convert(rest[9])+', ' + convert(rest[10]) +' )') elif len(rest) == 12: cur.execute('select id from company where name=' + rest[0] + rest[1] + rest[2] + '') companyId = cur.fetchone() if companyId is None: cur.execute('insert into company (name) values (' + rest[0] + rest[1] + rest[2] + ')')
try: #update database con = db.connect('localhost', 'root', 'metsfan', 'finance2') with con: cur = con.cursor() cnt = html.count('\n') for i in range(cnt-1): html = html.partition('\n') single = html[2].partition('\n') row = single[0].rsplit(',') html = html[2] #get the id for the sector cur.execute('select id from sector where name=' + row[0] + '') sectorId = cur.fetchone() #print 'insert into sectors (sector, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (' + str(sectorId[0]) + ', ' + row[1]+ ', ' + convert(row[2]) +', ' + row[3]+', ' + row[4]+', ' + row[5]+', ' + row[6]+', ' + row[7]+', ' + row[8]+', ' + row[9] +' );' cur.execute('insert into sectors (sector, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (' + str(sectorId[0]) + ', ' + row[1]+ ', ' + convert(row[2]) +', ' + row[3]+', ' + row[4]+', ' + row[5]+', ' + row[6]+', ' + row[7]+', ' + row[8]+', ' + row[9] +' );') except db.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) #sys.exit(1) finally: if con: con.close() #f = open("data/sector/all.csv","w") #f.write(html)
companyToIndustry = cur.fetchone() if companyToIndustry is None: cur.execute( "insert into companyToIndustry (company_id, industry_id) values (" + str(companyId[0]) + ", " + str(companyToIndustry[0]) + ")" ) # print companyId[0] # print str(rest[0]) + ', ' + convert(rest[1])+ ', "' + convert(rest[2]) +'", "' + convert(rest[3])+'", ' + convert(rest[4])+', ' + convert(rest[5])+', ' + convert(rest[6])+', ' + convert(rest[7])+', ' + convert(rest[8])+', ' + convert(rest[9]) cur.execute( "insert into companies (company_id, day_price_change, market_cap , price_to_earnings_ratio, roe_percent, div_yield_percent, debt_to_equity, price_to_book, net_profit_margin, price_to_free_cash_flow) values (" + str(companyId[0]) + ", " + convert(row["1-Day Price Chg %"]) + ', "' + convert(row["Market Cap"]) + '", "' + convert(row["P/E"]) + '", ' + convert(row["ROE %"]) + ", " + convert(row["Div. Yield %"]) + ", " + convert(row["Debt to Equity"]) + ", " + convert(row["Price to Book"]) + ", " + convert(row["Net Profit Margin (mrq)"]) + ", "