from scriptUtils import convert, yahooFinanceAPIRequest from mysqlUtils import getMysqlConnection import time url = "http://biz.yahoo.com/p/csv/" 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)"] +' )')
840, 913, 911, 910, 912, 914, ] # full list here - http://pastie.org/6419646 # industry = [112] sort = "coname" direction = "u" # possible properties - https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty static = ".csv" con = getMysqlConnection() for i in industry: csvReader = yahooFinanceAPIRequest(url + str(i) + sort + direction + static) try: with con: cur = con.cursor() for row in csvReader: cur.execute('select id from company where name="' + row["Description"] + '"') companyId = cur.fetchone() if companyId is None: cur.execute('insert into company (name) values ("' + row["Description"] + '")') cur.execute('select id from company where name="' + row["Description"] + '"') companyId = cur.fetchone() # the insert the convert table cur.execute("select id from companyToIndustry where company_id=" + str(companyId[0]) + "") companyToIndustry = cur.fetchone()