def __buildtickerdictwithcotizaciones__(ticker, engine, session):
tickerdict = {}
# getting last entered index
ccmc = session.query(CotizacionConsolidadaMC).filter(
CotizacionConsolidadaMC.ticker == ticker.ticker).order_by(
CotizacionConsolidadaMC.cotizacioneslastid.desc()).first()
lastid = None
if ccmc != None:
lastid = ccmc.cotizacioneslastid
ids = []
with engine.connect() as conn:
sql = "SELECT id from cotizaciones_mc WHERE ticker = '" + ticker.ticker + "'"
if lastid != None:
sql = sql + " AND id > " + str(int(lastid))
sql = sql + " order by id asc"
rs = conn.execute(sql)
for row in rs:
ids.append(row['id'])
conn.close()
hasrows = False
Session_ = sessionmaker(bind=engine)
session_ = Session_()
for id in ids:
hasrows = True
cotizacionMc = session_.query(CotizacionMC).filter(
CotizacionMC.id == id).first()
timestamp = gettimestampfromfechatexto(cotizacionMc.source,
cotizacionMc)
if timestamp not in tickerdict.keys():
tickerdict[timestamp] = {}
tickerdict[timestamp][cotizacionMc.source] = {}
tickerdict[timestamp][
cotizacionMc.source]["ultimo"] = cotizacionMc.ultimo
tickerdict[timestamp][
cotizacionMc.source]["efectivo"] = cotizacionMc.efectivo
tickerdict[timestamp][cotizacionMc.source]["vol"] = cotizacionMc.vol
if hasrows:
tickerdict[timestamp][cotizacionMc.source]["lastid"] = id
else:
tickerdict = None
session_.commit()
session_.close()
return tickerdict
from sqlalchemy.orm import sessionmaker
from model import getaitraderengine
from model import CotizacionMC
from globalutils import gettimestampfromfechatexto
engine = getaitraderengine()
"""
Archivo para rellenar el campo de derivedsourcetimestamp en la tabla cotizaciones_mc
"""
Session = sessionmaker(bind=engine)
session = Session()
cotizaciones = session.query(CotizacionMC).all()
tostore = []
for c in cotizaciones:
if c.derivedsourcetimestamp == None:
c.derivedsourcetimestamp = gettimestampfromfechatexto(c.source, c)
tostore.append(c)
for c in tostore:
session.add(c)
session.commit()
def doimportinfobolsa(engine, iteracion):
try:
firefox_options = Options()
firefox_options.add_argument('--headless')
firefox_options.add_argument('--no-sandbox')
firefox_options.add_argument('--disable-dev-shm-usage')
if defaults.RUNNING_ON == "WINDOWS":
browser = webdriver.Firefox(firefox_options=firefox_options)
if defaults.RUNNING_ON == "UBUNTU":
try:
browser = webdriver.Firefox(
executable_path=defaults.pathfirefoxdriver,
firefox_options=firefox_options)
except BaseException as be:
print(be)
succeeded = False
while (not succeeded):
try:
browser.get(
'https://www.infobolsa.es/mercado-nacional/mercado-continuo'
)
succeeded = True
except BaseException:
pass
WebDriverWait(browser, 20).until(
ExpectedConditions.presence_of_all_elements_located(
(By.CSS_SELECTOR, ".fullTable tr")))
buttonpressed = False
while (buttonpressed == False):
try:
buttonEntendido = evaluate(
lambda: browser.find_element_by_class_name("introjs-button"
))
buttonEntendido.click()
buttonpressed = True
except BaseException:
time.sleep(2)
#ahora puedo comenzar a extraer
cotizaciones = []
tabla = evaluate(
lambda: browser.find_element_by_class_name("fullTable"))
rows = evaluate(lambda: tabla.find_elements_by_tag_name("tr"))
mthprint("INFOBOLSA:Importando...: Iteracion " + str(iteracion))
for i in range(len(rows)):
row = evaluate(lambda: rows[i])
if (i >= 1):
# browser.wait.until(ExpectedConditions.stalenessOf(row));
cells = evaluate(lambda: row.find_elements_by_tag_name("td"))
valor = {}
valor["nombre"] = evaluate(lambda: cells[
2].find_element_by_tag_name("a").get_property("text"))
valor["ultimo"] = evaluate(lambda: cells[3].text)
valor["percdiff"] = evaluate(lambda: cells[4].text)
valor["max"] = evaluate(lambda: cells[5].text)
valor["min"] = evaluate(lambda: cells[6].text)
valor["vol"] = evaluate(lambda: cells[8].text)
valor["efectivo"] = evaluate(lambda: cells[9].text)
valor["fecha"] = evaluate(lambda: cells[10].text)
dateTimeNow = datetime.datetime.now()
valor["fecha"] = valor["fecha"]
valor['hora'] = None
# print(valor)
cotizacionMc = CotizacionMC()
cotizacionMc.iteracion = iteracion
cotizaciones.append(cotizacionMc)
cotizacionMc.nombre = valor["nombre"]
cotizacionMc.nombre = ''.join(e for e in cotizacionMc.nombre
if e.isalnum())
cotizacionMc.ultimo = evaluate(
lambda: str2float(valor['ultimo']))
cotizacionMc.percdiff = evaluate(
lambda: str2float(valor['percdiff']))
cotizacionMc.max = evaluate(lambda: str2float(valor['max']))
cotizacionMc.min = evaluate(lambda: str2float(valor['min']))
cotizacionMc.vol = evaluate(lambda: str2float(valor['vol']))
cotizacionMc.efectivo = evaluate(
lambda: str2float(valor['efectivo']))
cotizacionMc.ts = dateTimeNow
cotizacionMc.fechaTexto = valor["fecha"]
cotizacionMc.source = INFOBOLSA
try:
cotizacionMc.derivedsourcetimestamp = gettimestampfromfechatexto(
INFOBOLSA, cotizacionMc)
except:
pass
# tratamos de guardar un valor timestamp directamente
try:
cotizacionMc.derivedsourcetimestamp = gettimestampfromfechatexto(
INFOBOLSA, cotizacionMc)
except BaseException as be:
pass
tickerdict = gettickersdictionary(INFOBOLSA, engine)
if cotizacionMc.nombre in tickerdict:
cotizacionMc.ticker = tickerdict[cotizacionMc.nombre]
Session = sessionmaker(bind=engine)
session = Session()
session.add(cotizacionMc)
session.commit()
browser.quit()
browser = None
except BaseException as e:
browser.quit()
browser = None
raise e
def doimportbolsademadrid(engine, iteracion):
try:
firefox_options = Options()
firefox_options.add_argument('--headless')
firefox_options.add_argument('--no-sandbox')
firefox_options.add_argument('--disable-dev-shm-usage')
if defaults.RUNNING_ON == "WINDOWS":
browser = webdriver.Firefox(firefox_options=firefox_options)
if defaults.RUNNING_ON == "UBUNTU":
browser = webdriver.Firefox(
executable_path=defaults.pathfirefoxdriver,
firefox_options=firefox_options)
succeeded = False
while (not succeeded):
try:
browser.get(
'https://www.bolsamadrid.es/esp/aspx/Mercados/Precios.aspx?indice=ESI100000000&punto=indice'
)
succeeded = True
except BaseException:
pass
selectelement = evaluate(
lambda: browser.find_element_by_id('SelMercado'))
select = Select(selectelement)
select.select_by_value('MC')
button = evaluate(
lambda: browser.find_element_by_id("ctl00_Contenido_Consultar"))
button.click()
#ahora puedo comenzar a extraer
valores = []
cotizaciones = []
continuar = True
while (continuar):
tabla = evaluate(lambda: browser.find_element_by_id(
"ctl00_Contenido_tblAcciones"))
rows = evaluate(lambda: tabla.find_elements_by_tag_name("tr"))
mthprint("BOLSADEMADRID:Importando...: Iteracion " +
str(iteracion))
for i in range(len(rows)):
row = rows[i]
if (i >= 1):
cells = evaluate(
lambda: row.find_elements_by_tag_name("td"))
valor = {}
valor["nombre"] = evaluate(lambda: cells[
0].find_element_by_tag_name("a").get_property("text"))
valor["ultimo"] = evaluate(lambda: cells[1].text)
valor["percdiff"] = evaluate(lambda: cells[2].text)
valor["max"] = evaluate(lambda: cells[3].text)
valor["min"] = evaluate(lambda: cells[4].text)
valor["vol"] = evaluate(lambda: cells[5].text)
valor["efectivo"] = evaluate(lambda: cells[6].text)
valor["fecha"] = evaluate(lambda: cells[7].text)
try:
if (valor['fecha'].index("Suspendido") != -1):
valor['fecha'] = valor['fecha'][10:]
except ValueError:
pass
if (len(cells) == 9):
valor["hora"] = cells[8].text
if (valor["hora"] == 'Cierre'):
valor['hora'] = '17:00'
else:
valor["hora"] = None
# print(valor)
cotizacionMc = CotizacionMC()
cotizacionMc.iteracion = iteracion
cotizaciones.append(cotizacionMc)
cotizacionMc.nombre = valor["nombre"]
cotizacionMc.ultimo = evaluate(
lambda: str2float(valor['ultimo']))
cotizacionMc.percdiff = evaluate(
lambda: str2float(valor['percdiff']))
cotizacionMc.max = evaluate(
lambda: str2float(valor['max']))
cotizacionMc.min = evaluate(
lambda: str2float(valor['min']))
cotizacionMc.vol = evaluate(
lambda: str2float(valor['vol']))
cotizacionMc.efectivo = evaluate(
lambda: str2float(valor['efectivo']))
cotizacionMc.ts = datetime.datetime.now()
cotizacionMc.source = BOLSADEMADRID
#tratamos de guardar un valor timestamp directamente
try:
cotizacionMc.derivedsourcetimestamp = gettimestampfromfechatexto(
BOLSADEMADRID, cotizacionMc)
except:
pass
if (valor['hora'] != None):
cotizacionMc.fechaTexto = valor["fecha"] + " " + valor[
"hora"]
else:
cotizacionMc.fechaTexto = valor["fecha"]
tickerdict = gettickersdictionary(BOLSADEMADRID, engine)
if cotizacionMc.nombre in tickerdict:
cotizacionMc.ticker = tickerdict[cotizacionMc.nombre]
#parsing timestamp
Session = sessionmaker(bind=engine)
session = Session()
session.add(cotizacionMc)
session.commit()
valores.append(valor)
#si descubro boton siguiente -> clickar y continuar
try:
btnSiguientes = browser.find_element_by_id(
"ctl00_Contenido_SiguientesAbj")
if (btnSiguientes == None):
continuar = False
else:
btnSiguientes.click()
except NoSuchElementException:
continuar = False
browser.quit()
browser = None
except BaseException as e:
browser.quit()
browser = None
raise e
def doimportbsmarkets(engine, iteracion):
try:
starturl = "https://www.bsmarkets.com/cs/Satellite?cid=1191407147971&pagename=BSMarkets2%2FPage%2FPage_Interna_WFG_Template&language=es_ES"
firefox_options = Options()
firefox_options.add_argument('--headless')
firefox_options.add_argument('--no-sandbox')
firefox_options.add_argument('--disable-dev-shm-usage')
if defaults.RUNNING_ON == "WINDOWS":
browser = webdriver.Firefox(firefox_options=firefox_options)
if defaults.RUNNING_ON == "UBUNTU":
browser = webdriver.Firefox(executable_path=defaults.pathfirefoxdriver, firefox_options=firefox_options)
succeeded = False
while (not succeeded):
try:
browser.get(starturl)
succeeded = True
except BaseException:
pass
table = evaluate(lambda:browser.find_element_by_id("ls_table_constituyentes"))
rows = evaluate(lambda:table.find_elements_by_css_selector(".wfg_cursorMano"))
cotizaciones = []
mthprint("BSMARKETS:Importando...: Iteracion " + str(iteracion))
for row in rows:
cells = evaluate(lambda:row.find_elements_by_css_selector("td"))
nombre = evaluate(lambda:cells[0].text)
ticker = evaluate(lambda:cells[1].text)
ultimo = evaluate(lambda:str2float(cells[3].text))
percdiff = evaluate(lambda:str2float(cells[4].text))
max = evaluate(lambda:str2float(cells[6].text))
min = evaluate(lambda:str2float(cells[7].text))
vol = evaluate(lambda:str2float(cells[8].text))
hora = evaluate(lambda:cells[9].text)
cotizacion = CotizacionMC()
cotizacion.iteracion = iteracion
cotizacion.ticker = ticker
cotizacion.nombre = nombre
cotizacion.ultimo = ultimo
cotizacion.percdiff = percdiff
cotizacion.max = max
cotizacion.min = min
cotizacion.vol = vol
cotizacion.fechaTexto = hora
cotizacion.ts = datetime.datetime.now()
cotizacion.source = BSMARKETS
try:
cotizacion.derivedsourcetimestamp = gettimestampfromfechatexto(BSMARKETS, cotizacion)
except:
pass
# tratamos de guardar un valor timestamp directamente
try:
cotizacion.derivedsourcetimestamp = gettimestampfromfechatexto(BSMARKETS, cotizacion)
except:
pass
cotizaciones.append(cotizacion)
Session = sessionmaker(bind=engine)
session = Session()
for c in cotizaciones:
session.add(c)
session.commit()
browser.quit()
browser = None
except BaseException as e:
browser.quit()
browser = None
raise e