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)"])
+ ", "
