def get_data(exchange): df = None for _ in range(3): try: time.sleep(0.1) csv_path = "http://www.nasdaq.com/screening/companies-by-name.aspx?letter=0&exchange=%s&render=download" % exchange df = pd.read_csv(csv_path) df['Symbol'] = df['Symbol'].apply(string.strip).apply(string.rstrip) df['MarketCap'] = df['MarketCap'].apply(marketcap_to_float) df.to_sql('us_%s' % exchange, engine, if_exists='replace', index=True, index_label='id') ytrack.success("us_%s 数据更新成功" % exchange) break except requests.exceptions.ConnectionError as e: yyhtools.error(traceback.format_exc()) return if df is None: ytrack.error("us_%s 数据更新失败." % exchange) return symbols = df['Symbol'].values.tolist() sql = 'select Symbol, cid from us_%s_cid' % exchange try: a = engine.execute(sql) aa = a.fetchall() cids = {} for symbol, cid in aa: cids[symbol] = cid for symbol in symbols: if symbol not in cids: cid = get_cid(exchange, symbol) sql = 'insert into us_%s_cid(Symbol, cid) values("%s", "%s")' % (exchange, symbol, cid) engine.execute(sql) ytrack.success("cid(%s, %s)=%s" % (exchange, symbol, cid)) except Exception as e: yyhtools.error(trackback.format_exc())
def trade_cal(force=False): ''' http://218.244.146.57/static/calAll.csv 交易日历 isOpen=1是交易日,isOpen=0为休市 ''' dst = os.path.join(CURDIR, 'trade.cal.csv') if os.path.exists(dst) and not force: df = pd.read_csv(dst, index_col=0) df = df.set_index('calendarDate') return df def date2int(date): y, m, d = date.split("/") return int(y) * 10000 + int(m) * 100 + int(d) for _ in range(5): try: df = pd.read_csv('http://218.244.146.57/static/calAll.csv') df['calendarDate'] = df['calendarDate'].map(date2int) df.to_csv(dst) df = df.set_index('calendarDate') ytrack.info('update trade_cal from network..') return df except Exception as e: ytrack.error(traceback.format_exc())
def get_sina_goods(): from codes import add_params, GOODS_URL, goods, get_page, parse_df for good in goods: dst = "%s/sina_goods/%s.csv" % (CURDIR, good['breed']) if os.path.exists(dst): ytrack.error("%s exists.." % dst) continue url = add_params(GOODS_URL, good) page = get_page(url) if not page: ytrack.fail('%s page is None' % good['name']) continue data = pd.DataFrame() for i in range(1, page + 1): good.update({"page": i}) url = add_params(GOODS_URL, good) df = parse_df(url) if df is None or len(df) == 0: continue else: data = data.append(df, ignore_index=True) if len(data) > 0: data.columns = ['date', 'close', 'open', 'high', 'low', 'volume'] data.to_csv(dst) ytrack.info("%s finished.." % good['name']) else: ytrack.error("%s len is 0") ytrack.show()
def is_open_day(day): ''' isOpen=1是交易日,isOpen=0为休市 ''' if isinstance(day, datetime.datetime): day = day.year * 10000 + day.month * 100 + day.day try: isOpen = tradecal_df.loc[day]['isOpen'] return isOpen == 0 except Exception as e: ytrack.error(traceback.format_exc()) return False
def get_code(symbol): try: a = engine.execute("select id from us_symbol_int where symbol='%s'" % symbol) aa = a.fetchall() if not aa: b = engine.execute( "insert into us_symbol_int(symbol) values ('%s')" % symbol) a = engine.execute( "select id from us_symbol_int where symbol='%s'" % symbol) aa = a.fetchall() return aa[0][0] except Exception as e: ytrack.error(traceback.format_exc()) return 0
def _get_basics(force=False): fle = os.path.join(CURDIR, 'stock.basics.csv') if os.path.exists(fle) and not force: df = pd.read_csv(fle, dtype=str) df = df.set_index('code') return df for _ in range(5): try: time.sleep(0.1) df = ts.get_stock_basics() df.to_csv(fle, encoding="utf-8") ytrack.info('update 股票列表 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) ytrack.info('成功 股票基本信息 from network..') return df
df5 = _update_macd_weekly(date, symbol, table2) if df5 is not None: df5 = df5[macd_cols] my_update_someday_data(df5, get_week_date(day), '%s_macd_weekly' % exchange) else: ytrack.success(u"%s_macd_weekly 需要更新的数据为空" % exchange) df6 = _update_macd_monthly(date, symbol, table3) if df6 is not None: df6 = df6[macd_cols] my_update_someday_data(df6, get_month_date(day), '%s_macd_monthly' % exchange) else: ytrack.success(u"%s_macd_monthly 需要更新的数据为空" % exchange) ynotice.send(ytrack.get_logs(), style='stock', title=u'%s-%s-K线图更新' % (get_day_date(day), exchange)) if __name__ == "__main__": try: run_daily() except Exception as e: ytrack.error(traceback.format_exc()) ynotice.send(ytrack.get_logs(), style='stock', title=u'us run daily error.')
def update_stock_info(): trade_cal(True) for _ in range(5): try: time.sleep(0.1) df = ts.get_stock_basics() fle = os.path.join(CURDIR, 'stock.basics.csv') df.to_csv(fle, encoding="utf-8") df = df.set_index(df.index.astype(int)) df.to_sql('stock_list', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update 股票列表 save to sql') time.sleep(0.1) df = ts.get_today_all() fle = os.path.join(CURDIR, 'stock.today.csv') df.to_csv(fle, encoding="utf-8") df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_today', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update stock_today') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_industry_classified() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_industry', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update 股票行业 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) # ytrack.info('成功 股票基本信息 from network..') # ynotice.send(ytrack.get_logs(), style='stock', title='更新股票基础数据和交易日期') # return for _ in range(5): try: time.sleep(0.1) df = ts.get_concept_classified() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_concept', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update 股票概念 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_area_classified() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_area', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update 股票地域 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_sme_classified() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_sme', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update 中小板股票 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_gem_classified() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_gem', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update 创业板股票 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_st_classified() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_st', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update st股票 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_hs300s() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_hs300', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update hs300股票 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_sz50s() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_sz50s', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update sz50s股票 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) for _ in range(5): try: time.sleep(0.1) df = ts.get_zz500s() df = df.set_index('code') df = df.set_index(df.index.astype(int)) df.to_sql('stock_zz500s', engine, if_exists='replace', index=True, index_label='code') ytrack.info('update zz500s股票 save to sql') break except Exception as e: ytrack.error(traceback.format_exc()) ytrack.info('成功 股票基本信息 from network..') ynotice.send(ytrack.get_logs(), style='stock', title='更新股票基础数据和交易日期')