internet.py

import sys
import os
import time
#Web Driver
import selenium
from selenium import webdriver
from seleniumwire import webdriver as wire_webdriver
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import Select
from selenium.webdriver.support.select import Select
from selenium.common.exceptions import TimeoutException
from selenium.webdriver import ActionChains as ac
from selenium.webdriver.firefox.options import Options
from selenium.webdriver.common.desired_capabilities import DesiredCapabilities

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

import multiprocessing
import threading
import urllib3
import OpenSSL

from io import StringIO

import gc

import pymysql

# Parsing HTML
import requests 

import copy

from math import isnan, nan

#Yahoo Financials
from yahoofinancials import YahooFinancials as yf
import yfinance

import pandas_datareader as pdr
import pandas_datareader.data as data
import pandas as pd
from sqlalchemy import create_engine, text

import timestring

# Excel operations
#import csv
import xlrd
import xlwt

# Date
import datetime
from datetime import datetime as dt, timedelta
from dateutil.relativedelta import relativedelta
from datetime import date

#List Files
from fractions import Fraction

import smtplib
import re

from bs4 import BeautifulSoup

import pprint

import excel
import conf
from datastructures import *
import parse_html
import DB
from common import *
import hdf5

stop_thread=False
br=None

def get_ticker(symbol):
    return yfinance.Ticker(symbol)

def get_stock_price_data(country, tick, symbol, symbols, stk, db, sql_engine, proxy=False, vpn_event=None, write_to_db=True):
    ret = False
    df = pd.DataFrame() 
    collection = DB.get_collection(country, db)

    try:
        today=dt.now()
        end=dt.now().date()# - timedelta(7)
        #Updating the price and volume for the first time
        if stk['bscs']['symbol'] in US_indices.keys():
            index  = True
            symbol = US_indices[stk['bscs']['symbol']]
        else:
            index  = False
            symbol = stk['bscs']['symbol']
        #symbol = '/' + stk['bscs']['symbol']

        table = DB.get_symbol_table_name(symbol)

        if len(symbols) == 0 or symbol.replace('-','.').replace('_','.') not in symbols:
            # Check if symbol is ending with +, =, -
            # Delete those junk symbols from mongodb
            if re.match(r'.*[\+|\=|\-]$', symbol):
                print("Deleting Junk Symbol: %r" %(symbol))
                if write_to_db:
                    db.US_Stocks.remove({"bscs.symbol" : symbol})
                    db.US_Stocks_List.remove({"symbol" : symbol})
            else:
                start = dt.strptime("1970-01-01", "%Y-%m-%d").date()
                print("New symbol: getting data for %r from yahoo" %(stk['bscs']['symbol']))
                df = hdf5.get_stock_data(country, stk, start, end, vpn_event, tick=tick, proxy=proxy)
                #df = remove_df_duplicates(df)
                if not df.empty:
                    df['Date'] = df.index.strftime("%Y-%m-%d")
                    df.index = df['Date'] #Is it required?
                    print("mysql: %s: %s"%(symbol,stk['bscs']['name']))
                    if write_to_db:
                        DB.check_n_write_to_sql(sql_engine, DB.get_symbol_table_name(symbol), copy.deepcopy(df), list(df.columns))
                        # Reset mysql_price_failcount
                        DB.update_field(collection, symbol, "failcount.mysql_price_failcount", 0)
 
                else:
                    if write_to_db:
                        DB.update_field(collection, symbol, "ignore", "YES")
                        DB.update_price_failcount(stk, country, df=True)

        #Updating today's price and volume
        else:
            #if index:
            if True:
                # Yahoo Finance sometimes returns wrong volume data for the latest date.
                # Check and delete record.
                # Will be populated again the below code.
                # Happens only when small set of data is requested.
                DB.check_volume_of_last_record(sql_engine, DB.get_symbol_table_name(stk['bscs']['symbol']))
            else:
                pass

            query='select Date from ' + table + ' order by Date DESC limit 1'
            rdf = DB.read_from_sql(query, sql_engine)

            # Read the existing data of the symbol
            if rdf.empty:
                PRINT_ERR("update_dataframe_price_volume: %s: No data. Read from the start" %(stk['bscs']['symbol']))
                start = dt.strptime("1970-01-01", "%Y-%m-%d").date()
            else:
                #get timestamp of the last entry
                start = dt.strptime(rdf['Date'][0], "%Y-%m-%d").date()
            if start < end:
            #if True:
                # If date difference is less than a week, get atleast
                # a week of prices. yahoofinance sometimes misbehaves
                # in case of a shorter timespan and returns inconsistent data.
                # Min of week is a safer timespan.
                # Though you get a week data, insert only the entries that are missing.
                # Taken care below.
                if end-start < timedelta(10):
                    start = end - timedelta(10)

                df = hdf5.get_stock_data(country, stk, start, end, vpn_event, tick=tick, proxy=proxy)
                # Sometimes yahoo gives wrong data. Wrong data will have volume as 0. Discard those rows
                #df.drop(df[df['Volume']==0].index, inplace=True)
                #e=time.time()
                #print("got data for %r from yahoo, elapsed time: %r sec" %(stk['bscs']['symbol'], (e-s)))
                #print("two: sym: %r, start: %r, end: %r" %(stk['bscs']['symbol'], str(start), str(end)))
                if not df.empty:
                    ret=True
                    xdf=copy.deepcopy(df)
                    #rdf = rdf.append(df)
                    #rdf = remove_df_duplicates(rdf)
                    #df['Symbol'] = symbol
                    df['Date'] = df.index.strftime("%Y-%m-%d")
                    df.index = df['Date'] #Is it required?
                    #df = df[~df.Date.isin(rdf.Date)]
                    # Get the data starting from the next day of the last entry in MySQL database
                    #df=df.loc[rdf['Date'][0]:].drop(rdf['Date'][0])
                    if not df.empty:
                        try:
                            if not rdf.empty and rdf['Date'][0] in list(df.index):
                                index = df.index.get_loc(rdf['Date'][0])
                                df = df[index+1:]
                        except Exception as E:
                            print("internet.py: %r: update_dataframe_price_volume exception: %r"%(symbol, str(E)))
                            print("internet.py: %r: update_dataframe_price_volume exception, df: %r"%(symbol, df))
                            print("internet.py: %r: update_dataframe_price_volume exception, xdf: %r"%(symbol, xdf))
                            print("internet.py: %r: update_dataframe_price_volume exception, rdf: %r"%(symbol, rdf))
                    if not df.empty and write_to_db:
                        #print("Writing to sql prices for %r" %(symbol))
                        #print("writing data for %r to mysql" %(stk['bscs']['symbol']))
                        #s=time.time()
                        print("mysql get_stock_data(): %s: %s"%(symbol,stk['bscs']['name']))
                        #DB.write_to_sql(sql_engine, table, df)
                        DB.mysql_update_table(sql_engine, table, df, insert=True)
                        #DB.update_field(collection, symbol, "dates.mysql_price_date", dt.combine(dt.now(), dt.min.time()))
                        # Reset mysql_price_failcount
                        DB.update_field(collection, symbol, "failcount.mysql_price_failcount", 0)
                        #threading.Thread(target=internet.update_price_change, args=(country, collection, stk['bscs']['symbol'], None, sql_engine,)).start()
                        #e=time.time()
                        #print("done data for %r to mysql, elapsed time: %r sec" %(stk['bscs']['symbol'], (e-s)))
                        #print("Wrote to sql prices for %r" %(symbol))
 
                    ##if index:
                    ##    rdf = update_percent_change(rdf)
                    ##Update Betas
                    ##if stk['bscs']['symbol'] not in India_indices.keys() and stk['bscs']['symbol'] not in US_indices.keys():
                    ##    rdf = hdf_get_beta(country, symbol, rdf)
                    ##    #DB.update_stock_betas2(country, stk, df=rdf)
                    #write_to_hdf(country, rdf, symbol)
                    #write_to_hdf_store(country, rdf, stk['bscs']['symbol'])
                    # Update the date on which the price is updated
                    #DB.update_field(collection, symbol, "ignore", "NO")
                else:
                    PRINT_ERR("df empty for %r" %(symbol))
                    DB.update_field(collection, symbol, "ignore", "YES")
                    #DB.update_field(collection, symbol, "dates.mysql_price_date", dt.combine(dt.now(), dt.min.time()))
                    DB.update_price_failcount(stk, country, df=True)
            else:
                ret = True

        if write_to_db:
            DB.update_field(collection, symbol, "dates.mysql_price_date", dt.combine(dt.now(), dt.min.time()))

    finally:
        return ret, df

def open_browser(head=None, wiredriver=False):
    profile = webdriver.FirefoxProfile()
    capabilities = DesiredCapabilities.FIREFOX

    options = Options()
    if head == 'headless':
        options.add_argument('--headless')

    profile.set_preference("browser.cache.disk.enable", False)
    profile.set_preference("browser.cache.memory.enable", False)
    profile.set_preference("browser.cache.offline.enable", False)
    profile.set_preference("network.http.use-cache", False)
    profile.set_preference("browser.privatebrowsing.autostart", True)
    profile.set_preference("dom.webnotifications.enabled", False)
    #profile.set_preference('browser.download.folderList', 2) # custom location
    #profile.set_preference('browser.download.manager.showWhenStarting', False)
    #profile.set_preference('browser.download.manager.focusWhenStarting', False)
    #profile.set_preference('browser.download.manager.closeWhenDone', True)
    #profile.set_preference('browser.download.manager.showAlertComplete', False)
    #profile.set_preference('browser.download.manager.useWindow', False)
    #profile.set_preference('services.sync.prefs.sync.browser.download.manager.showWhenStarting', False)
    #profile.set_preference('browser.download.useDownloadDir', True)
    #profile.set_preference('browser.download.dir', '/tmp')
    #profile.set_preference("browser.helperApps.alwaysAsk.force", False);
    #profile.set_preference("browser.helperApps.neverAsk.openFile", "text/plain, application/octet-stream, application/binary, text/csv, application/csv, application/excel, text/comma-separated-values, text/xml, application/xml");
    #profile.set_preference('browser.helperApps.neverAsk.saveToDisk', "text/plain, application/octet-stream, application/binary, text/csv, application/csv, application/excel, text/comma-separated-values, text/xml, application/xml")
    #profile.set_preference('csvjs.disabled', True)
    #profile.set_preference('pdfjs.disabled', True)

    #profile.add_extension(extension='/home/vpetla/.mozilla/firefox/ekwma54v.default-release/extensions/jid1-P34HaABBBpOerQ@jetpack.xpi')
    #profile.add_extension(extension='/home/vpetla/.mozilla/firefox/ekwma54v.default-release/extensions/{246C9D65-51E6-4B0C-9CCF-B081B7BF9242}.xpi')
    if wiredriver:
        browser = wire_webdriver.Firefox(firefox_profile=profile, options=options, capabilities=capabilities)
    else:
        browser = webdriver.Firefox(firefox_profile=profile, options=options, capabilities=capabilities)
    #browser.set_page_load_timeout(30)
    #browser.maximize_window()
    return browser

def close_browser(br):
    #br.close()
    br.delete_all_cookies()
    br.quit()

def send_email(message):
    # creates SMTP session 
    s = smtplib.SMTP('smtp.gmail.com', 587) 
      
    # start TLS for security
    s.ehlo()
    s.starttls() 
      
    # Authentication 
    s.login("askpvenkatesh@gmail.com", "tasche#gm") 
      
    # message to be sent 
    message = "Hello World."
      
    # sending the mail 
    s.sendmail("askpvenkatesh@gmail.com", "askpvenkatesh@gmail.com", message) 
      
    # terminating the session 
    s.quit() 

def send_email2(user, recipient, subject, body):

    try:
        FROM = user
        TO = recipient if isinstance(recipient, list) else [recipient]
        SUBJECT = subject
        TEXT = body

        # Prepare actual message
        #message = """From: %s\nTo: %s\nSubject: %s\n\n%s
        #""" % (FROM, ", ".join(TO), SUBJECT, TEXT)

        message = MIMEMultipart('alternative')
        message['Subject'] = subject
        message['From'] = user
        message['To'] = recipient
        message.attach(MIMEText(body, 'html'))

        #server = smtplib.SMTP_SSL("smtp.gmail.com", 465)
        server = smtplib.SMTP("smtp.gmail.com", 587)
        #server.set_debuglevel(1)
        server.ehlo()
        server.starttls()
        pwd="poggfuvtowfdtsyp"
        server.login(user, pwd)
        server.sendmail(FROM, TO, message.as_string())
        #server.sendmail(FROM, TO, message)
        server.close()
        print('successfully sent the mail')

    except Exception as E:
        print("Failed to send email, err: %s", str(E))
        pass

def index_change(country, sym, name, num_days, data_type):
    change = 0
    symbol = sym.replace('.', '-')
    if data_type == 'HOT':
        end = dt.now()
        diff = end.weekday() - 4
        #If weekend
        if diff > 0:
            end = end - timedelta(days=diff+1)
        start = end - timedelta(days=num_days)
        diff = start.weekday() - 4
        if diff > 0:
            start = start - timedelta(days=diff+1)
        try:
            #print("Symbol: %s, Name: %s" %(sym, name))
            #read = pdr.DataReader(symbol, 'morningstar', start, end)
            read = pdr.DataReader(symbol, 'yahoo', start, end)
        except pdr._utils.RemoteDataError:
            PRINT_ERR("Unable to get data for %s"%(sym))
            return None
        except KeyError:
            PRINT_ERR("Unable to get data for %s"%(sym))
            return None
 
        en_price = read.iat[-1, read.columns.get_loc('Adj Close')]
        st_price = read.iat[0, read.columns.get_loc('Adj Close')]
        change = en_price/st_price - 1
    return change, round((en_price-st_price), 2)

# Deprecated. Use hdf_price_change().
def price_change(country, sym, name, num_days, data_type):
    change,diff=index_change(country,sym,name,num_days,data_type)
    return change

def check_price_change(country, sym, stock, name, change, req_change, count, sheet, sheet_type, excel_type):
    if change >= req_change:
        #print("sym: %s, name: %s, change: %d percent" %(sym, name, change*100))
        count += 1
        if excel_type == 'EXCEL':
            excel.write_to_price_change_excel(count, sheet, stock, sheet_type)

    elif change < -(req_change):
        #print("sym: %s, name: %s, change: -%d percent" %(sym, name, change*100))
        count += 1 
        if excel_type == 'EXCEL':
            excel.write_to_price_change_excel(count, sheet, stock, sheet_type)

    return count


def price_suprise(country, collection, stock, sym, name, change_percent, xl, criteria, db_type, excel_type):
   #st_price = read.iat[0, read.columns.get_loc('Close')]
    #en_price = read.iat[-1, read.columns.get_loc('Close')]
   
    if criteria == ALL or criteria & YEAR:
        change = hdf5.hdf_price_change(country, sym, 365)
        if change:
            if db_type == 'SYNC_DB':
                DB.update_field(collection, sym, "price_change.year", change)
            if excel_type == 'EXCEL':
                sheet = xl.get_sheet(0)
            else:
                sheet = None
            conf.PR_YR_COUNT = check_price_change(country, sym, stock, name, change, 0.40, conf.PR_YR_COUNT, sheet, 'YEAR', excel_type)
    
    if criteria == ALL or criteria & QUARTER:
        change = hdf5.hdf_price_change(country, sym, 90)
        if change:
            if db_type == 'SYNC_DB':
                DB.update_field(collection, sym, "price_change.quarter", change)
            if excel_type == 'EXCEL':
                sheet = xl.get_sheet(1)
            else:
                sheet = None
            conf.PR_QR_COUNT = check_price_change(country, sym, stock, name, change, 0.30, conf.PR_QR_COUNT, sheet, 'QUARTER', excel_type)
    
    if criteria == ALL or criteria & MONTH:
        change = hdf5.hdf_price_change(country, sym, 30)
        if change:
            if db_type == 'SYNC_DB':
                DB.update_field(collection, sym, "price_change.month", change)
            if excel_type == 'EXCEL':
                sheet = xl.get_sheet(2)
            else:
                sheet = None
            conf.PR_MON_COUNT = check_price_change(country, sym, stock, name, change, 0.20, conf.PR_MON_COUNT, sheet, 'MONTH', excel_type)
    
    if criteria == ALL or criteria & WEEK:
        change = hdf5.hdf_price_change(country, sym, 7)
        if change:
            if db_type == 'SYNC_DB':
                DB.update_field(collection, sym, "price_change.week", change)
            if excel_type == 'EXCEL':
                sheet = xl.get_sheet(3)
            else:
                sheet = None
            conf.PR_WEEK_COUNT = check_price_change(country, sym, stock, name, change, 0.10, conf.PR_WEEK_COUNT, sheet, 'WEEK', excel_type)

    if criteria == ALL or criteria & DAY:
        change = hdf5.hdf_price_change(country, sym, 1)
        if change:
            if db_type == 'SYNC_DB':
                DB.update_field(collection, sym, "price_change.day", change)
            if excel_type == 'EXCEL':
                sheet = xl.get_sheet(4)
            else:
                sheet = None
            conf.PR_DAY_COUNT = check_price_change(country, sym, stock, name, change, 0.10, conf.PR_DAY_COUNT, sheet, 'DAY', excel_type)

    DB.update_field(collection, sym, "price_change.date", str(dt.now()))

def get_change(df, field):
    if df.iloc[0][field] and not isnan(df.iloc[0][field]):
        return df.iloc[0][field]
    else:
        return df.iloc[1][field]

# Fix mess caused by update_price_change() caused by the query
# query = 'select `Date`, `Adj Close` from %s where `Day Change` is NULL order by Date'
def nullify_price_change_error_stk(country, collection, sym, sql_engine):
    table_name = DB.get_symbol_table_name(sym)
    if DB.mysql_exists_table(sql_engine, table_name):
        query = 'select `Date`, `Adj Close` from %s where `Day Change` = `Whole Change`' %(table_name)
        df = DB.read_from_sql(query, sql_engine)
        if not df.empty:
            for index, d in df.iterrows():
                end_date = str(pd.to_datetime(index).date())
                query = 'select Date, `Adj Close`, `Day Change` from {} where Date = \'{}\''.format(table_name, end_date)
                cur_df = DB.read_from_sql(query, sql_engine)
                query = 'select Date, `Adj Close` from {} where `Date` < \'{}\' order by Date desc limit 1'.format(table_name, end_date)
                prev_df = DB.read_from_sql(query, sql_engine)
                price_change = round(cur_df['Adj Close'][-1]/prev_df['Adj Close'][-1] - 1, len(str(cur_df['Day Change'][-1]).split('.')[1]))
                if abs(abs(price_change) - abs(cur_df['Day Change'][-1])) > 0.05: # Atleast 5% difference
                    # Nullify from here to end of the table
                    #query = 'select * from {} where `Date` BETWEEN \'{}\' and  NOW()'.format(table_name, end_date)
                    query = 'select `Date`, {} from {} where `Date` BETWEEN \'{}\' and  NOW()'.format(', '.join(['`{}`'.format(c) for c in [*price_change_fields]]), table_name, end_date)
                    df2 = DB.read_from_sql(query, sql_engine)
                    for field in [*price_change_fields]:
                        df2[field] = None
                    print("Updating change: %r" %(cur_df))
                    DB.mysql_update_table(sql_engine, table_name, df2, check=True)
                    break

 
def nullify_price_change_errors():
    country = 'US'
    c = DB.open_db_client()
    db = c['Stocks']
    collection = DB.get_collection(country, db)
    sql_engine = DB.open_sql_connection('localhost', 'root', 'petla123', db='US_Stocks')

    #stocks = collection.find({},no_cursor_timeout=True).batch_size(10).sort([["sno",1]])
    symbols = DB.get_symbols_from_sql(country, sql_engine)

    try:
        for i, symbol in enumerate(symbols):
            print("%d: %r" %(i, symbol))
            nullify_price_change_error_stk(country, collection, symbol, sql_engine)
    finally:
        DB.close_sql_connection(sql_engine)
        DB.close_db_client(c)

def identify_change_indices(params_engine, table_name, stock_dates, field):
    with params_engine.connect() as conn:
        result = conn.execute(text(
                                f"SELECT `Date` FROM {table_name} WHERE `{field}` IS NOT NULL"
                            ))
    
        param_valid_dates = set(row[0] for row in result)
    indices = stock_dates - param_valid_dates
    indices = sorted(indices)

    return indices

def get_rows_from_indices(sql_engine, table_name, indices):
    df = pd.DataFrame()
    
    if indices:
        CHUNK_SIZE = 1000
        with sql_engine.connect() as conn:
            for i in range(0, len(indices), CHUNK_SIZE):
                chunk = list(indices)[i:i + CHUNK_SIZE]
                placeholders = ','.join([':val' + str(j) for j in range(len(chunk))])
                bind_params = {f'val{j}': chunk[j] for j in range(len(chunk))}
                query = text(f"SELECT * FROM {table_name} WHERE `Date` IN ({placeholders})")
                chunk_df = pd.read_sql_query(query, conn, params=bind_params)
                df = pd.concat([df, chunk_df], ignore_index=True)
    
        df.index = df.Date
    return df

def calculate_percent_change(sql_engine, table_name, df, field, start_price=None):
    wdf = pd.DataFrame(index=df.index[1:], columns=[field]) 
    
    for index, d in df.iloc[1:].iterrows():
        cur_price = d['Adj Close']
        cur_date = pd.to_datetime(index).date()
        cur_date_str = str(cur_date)
        #wdf.loc[cur_date_str]=nan
        #wdf.loc[cur_date_str]['Date'] = cur_date_str
        wdf.loc[cur_date_str, 'Date'] = cur_date_str
        if not start_price:
            start_price = DB.mysql_get_price(sql_engine, table_name, str(cur_date - price_change_durations[field]), str(cur_date))
        change = round(percent_change(start_price, cur_price),7)
        wdf.loc[cur_date_str, field] = change
        #wdf.loc[cur_date_str][[*price_change_fields][field_pos]] = change
    
    wdf = wdf.dropna(axis=0)
    return wdf

def insert_or_update(wdf, params_engine, table_name, field):
    # Write to the database
    if not wdf.empty:
        #DB.mysql_update_table(params_engine, table_name, wdf)
        sql = text(f"""
                        INSERT INTO `{table_name}` (`Date`, `{field}`)
                        VALUES (:date, :value)
                        ON DUPLICATE KEY UPDATE `{field}` = :value
                    """)
        with params_engine.begin() as conn:
            for _, row in wdf.iterrows():
                result = conn.execute(sql, {
                    "value": float(row[field]) if pd.notnull(row[field]) else None,
                    "date": row['Date']
                })
    return

def update_price_change_field(params_engine, sql_engine, table_name, stock_dates, field, price_change_last_only, start_price=None):
    indices = identify_change_indices(params_engine, table_name, stock_dates, field)
    if len(indices) <= 1:
        return
    
    df = get_rows_from_indices(sql_engine, table_name, indices)
    
    if price_change_last_only:
        if not df.empty:
            df = df.iloc[-1]
    if df.empty:
        return 
    wdf = calculate_percent_change(sql_engine, table_name, df, field, start_price=start_price)
    insert_or_update(wdf, params_engine, table_name, field)

def update_price_change(country, stk, core, sem=None, index=False, type='Stocks', price_change_last_only=False):
    #st_price = read.iat[0, read.columns.get_loc('Close')]
    #en_price = read.iat[-1, read.columns.get_loc('Close')]

    c  = DB.open_db_client()
    if type == 'Stocks':
        db = c['Stocks']
        collection=db.US_Stocks
        sql_engine = DB.open_sql_connection('localhost', 'root', 'petla123', db='US_Stocks')
        fin_engine = DB.open_sql_connection('localhost', 'vpetla', 'petla123', db='US_Stocks_Fin')
        params_engine = DB.open_sql_connection('localhost', 'vpetla', 'petla123', db='US_Tech_Params')
    else:
        db = c['Cryptos']
        collection=db.Cryptos
        sql_engine = DB.open_sql_connection('localhost', 'root', 'petla123', db='Cryptos')
        fin_engine = None
        params_engine = None

    #print("%s: Core: %r" %(sym, core))
    aff = 0 | 1 << core
    #print("Setting %d's affinity to core: %d" %(os.getpid(), core))
    os.system("taskset -p %r %d >/dev/null 2>&1" %(str(hex(aff)), os.getpid()))
  
    sym = ""
    update = False
    try:
        sym = stk['bscs']['symbol']
    except Exception as E:
        print("update_price_change: error: %s, stk : %s" %(str(E), stk))

    if isinstance(stk, dict):
            if 'General' in stk.keys() and \
                isinstance(stk['General'], dict) and \
                'Exchange' in stk['General'].keys() and \
                stk['General']['Exchange'] not in major_exchanges:
                price_change_last_only=True
    else:
        print("internet.py:521, stk: %s" %(stk))

    try:
        sym = stk['bscs']['symbol']
        table_name = DB.get_symbol_table_name(sym)
        change = 0

        if not DB.mysql_exists_table(params_engine, table_name):
            DB.mysql_check_n_create_table(params_engine, table_name, primary_key=True)
        if True:
            print("mysql: percent_change: %s"%(sym))

            table_cols = DB.mysql_get_columns_from_engine(params_engine, table_name)
            missing_cols = list_difference([*price_change_fields], table_cols)
            # Some price change fields are not present in the database.
            # The datatype of the fields is taken from the price fields 
            # mentioned in the datastructures.py 
            if len(missing_cols) > 0:
                print("%s: Adding missing columns: %r"%(table_name, missing_cols))
                miss = DB.mysql_add_columns(params_engine, table_name, missing_cols, remove_spaces=False)
                if miss > 0:
                    PRINT_ERR("Failed to add %r columns to table %r" %(miss, table_name))
                    PRINT_ERR("Columns: ",missing_cols)
                    sys.exit(1)

            # Step 1: Fetch all Dates from US_Stocks
            with sql_engine.connect() as conn:
                result = conn.execute(text(f"SELECT `Date` FROM {table_name}"))
                stock_dates = set(row[0] for row in result)

            for field_pos, field in enumerate([*price_change_fields][:-2]):
                update_price_change_field(params_engine, sql_engine, table_name, stock_dates, field, price_change_last_only)

            if 'General' in stk.keys() and \
                    'IPODate' in stk['General'].keys() and \
                    is_val(stk['General']['IPODate']):
                start_date = stk['General']['IPODate']
                query = 'select `Date`, `Adj Close` from {} where Date=\'{}\' '.format(table_name, start_date)
                df = DB.read_from_sql(query, sql_engine)
                if df.empty:
                    query = 'select `Date`, `Adj Close` from {} order by Date limit 1'.format(table_name)
                    df = DB.read_from_sql(query, sql_engine)
            else:
                query = 'select `Date`, `Adj Close` from {} order by Date limit 1'.format(table_name)
                df = DB.read_from_sql(query, sql_engine)
                start_date = df.index[0].strftime("%Y-%m-%d")

            #print(query)
            query = 'select `Date`, `Adj Close` from {} order by Date limit 1'.format(table_name)
            df = DB.read_from_sql(query, sql_engine)
            start_price = df['Adj Close'][0]

            # Whole Change
            field = [*price_change_fields][-2]
            update_price_change_field(params_engine, sql_engine, table_name, stock_dates, field, price_change_last_only, start_price=start_price)

            # YTD Change
            field = [*price_change_fields][-1]
            indices = identify_change_indices(params_engine, table_name, stock_dates, field)
            if len(indices) >= 1:
                df = get_rows_from_indices(sql_engine, table_name, indices)
            
                if price_change_last_only:
                    if not df.empty:
                        df = df.iloc[-1]
                if not df.empty:
                    wdf = pd.DataFrame(index=df.index[1:], columns=[field]) 
                    for index, d in df.iloc[1:].iterrows():
                        cur_price = d['Adj Close']
                        cur_date = pd.to_datetime(index).date()
                        start_year = cur_date.year
                        cur_date_str = str(cur_date)
    
                        query = 'select Date, `Adj Close` from {} where Date = (select min(Date) from {} where Year(Date)={})'.format(table_name, table_name, start_year)
                        start_df = DB.read_from_sql(query, sql_engine)
                        if not start_df.empty:
                            start_price = start_df['Adj Close'][0]
                            wdf.loc[cur_date_str, 'Date'] = cur_date_str
                            change = percent_change(start_price, cur_price)
                            wdf.loc[cur_date_str, [*price_change_fields][-1]] = change
                        else:
                            change = nan
    
                wdf = wdf.dropna(axis=0)
                insert_or_update(wdf, params_engine, table_name, field)

            query = 'select `Date`, {} from {} order by Date desc limit 2'.format(', '.join(['`{}`'.format(c) for c in [*price_change_fields]]), table_name)
            df = DB.read_from_sql(query, params_engine)

            change = get_change(df, 'Day Change')
            DB.update_field(collection, sym, "price_change.day", change)

            change = get_change(df, 'Week Change')
            DB.update_field(collection, sym, "price_change.week", change)

            change = get_change(df, 'Two Week Change')
            DB.update_field(collection, sym, "price_change.two_week", change)

            change = get_change(df, 'Month Change')
            DB.update_field(collection, sym, "price_change.month", change)

            change = get_change(df, 'Quarter Change')
            DB.update_field(collection, sym, "price_change.quarter", change)

            change = get_change(df, 'Half Year Change')
            DB.update_field(collection, sym, "price_change.half_year", change)

            change = get_change(df, 'Year Change')
            DB.update_field(collection, sym, "price_change.year", change)

            change = get_change(df, 'Five Year Change')
            DB.update_field(collection, sym, "price_change.five_year", change)

            change = get_change(df, 'Ten Year Change')
            DB.update_field(collection, sym, "price_change.ten_year", change)

            change = get_change(df, 'Whole Change')
            DB.update_field(collection, sym, "price_change.whole", change)

            change = get_change(df, 'YTD Change')
            DB.update_field(collection, sym, "price_change.ytd", change)

            end_date = str(dt.now().date())
            #get 52 week high
            #select max(`Adj Close`) from STKSP500 where Date between date_sub('2020-03-20', INTERVAL 1 YEAR) and '2020-03-20';
            query ='select max(`Adj Close`) from {} where Date between date_sub(\'{}\', INTERVAL 1 YEAR) and \'{}\''.format(table_name, end_date, end_date)
            result=sql_engine.execute(query)
            high_price = result.first()[0]
            #high_price = hdf5.hdf_get_high_n_days(df, 365)
            DB.update_field(collection, sym, "bscs.fiftytwoweek_high", high_price)
            #get 52 week low
            query ='select min(`Adj Close`) from {} where Date between date_sub(\'{}\', INTERVAL 1 YEAR) and \'{}\''.format(table_name, end_date, end_date)
            #query ='select min(`Adj Close`) from ' + table_name + ' where Date between Date between date_sub(%s, INTERVAL 1 YEAR);'%(end_date, end_date)
            result=sql_engine.execute(query)
            low_price = result.first()[0]
            #low_price = hdf5.hdf_get_low_n_days(df, 365)
            DB.update_field(collection, sym, "bscs.fiftytwoweek_low", low_price)

            # Number of weeks
            # query='select count(distinct concat(YEAR(Date), '-', WEEK(Date))) AS total_weeks from STKVFS where Date >= CURDATE() - INTERVAL 5 YEAR;'
            # Number of weeks where the price is down atleast 20 percent
            # query='select YEAR(Date) AS year, WEEK(Date) as week from STKHPE WHERE `Week Change` <= -0.20 AND Date >= CURDATE() - INTERVAL 5 YEAR GROUP BY YEAR(Date), WEEK(Date) HAVING COUNT(*) > 0 UNION ALL SELECT NULL AS year, COUNT(DISTINCT CONCAT(YEAR(Date), '-', WEEK(Date))) AS week FROM STKHPE WHERE `Week Change` <= -0.20 GROUP BY NULL;'

            #query ='select count(Date) from {} where Date between date_sub(\'{}\', INTERVAL 3 YEAR) and \'{}\''.format(table_name, end_date, end_date)
            query='select count(distinct concat(YEAR(Date), \'-\', WEEK(Date))) AS total_weeks from {} where Date >= CURDATE() - INTERVAL 5 YEAR;'.format(table_name)
            rdf=pd.read_sql_query(query, params_engine)
            total_weeks = rdf.iloc[0]['total_weeks']
            # This also works
            #result=sql_engine.execute(query)
            #total_weeks = result.first()[0]

            #query ='select count(Date) from {} where Date between date_sub(\'{}\', INTERVAL 3 YEAR) and \'{}\' and `Week Change` < -0.10'.format(table_name, end_date, end_date)
            query='select YEAR(Date) AS year, WEEK(Date) as week from {} WHERE `Week Change` <= -0.10 AND Date >= CURDATE() - INTERVAL 5 YEAR GROUP BY YEAR(Date), WEEK(Date) HAVING COUNT(*) > 0 UNION ALL SELECT NULL AS year, COUNT(DISTINCT CONCAT(YEAR(Date), \'-\', WEEK(Date))) AS week FROM {} WHERE `Week Change` <= -0.10 GROUP BY NULL;'.format(table_name, table_name)
            rdf=pd.read_sql_query(query, params_engine)
            ten_percent_down_times = len(rdf.dropna())
            #result=sql_engine.execute(query)
            #ten_percent_down_times = result.first()[0]

            #query ='select count(Date) from {} where Date between date_sub(\'{}\', INTERVAL 3 YEAR) and \'{}\' and `Week Change` < -0.20'.format(table_name, end_date, end_date)
            query='select YEAR(Date) AS year, WEEK(Date) as week from {} WHERE `Week Change` <= -0.20 AND Date >= CURDATE() - INTERVAL 5 YEAR GROUP BY YEAR(Date), WEEK(Date) HAVING COUNT(*) > 0 UNION ALL SELECT NULL AS year, COUNT(DISTINCT CONCAT(YEAR(Date), \'-\', WEEK(Date))) AS week FROM {} WHERE `Week Change` <= -0.20 GROUP BY NULL;'.format(table_name, table_name)
            rdf=pd.read_sql_query(query, params_engine)
            twenty_percent_down_times = len(rdf.dropna())
            #result=sql_engine.execute(query)
            #twenty_percent_down_times = result.first()[0]
            DB.update_field(collection, sym, "price_change.total_weeks", int(total_weeks))
            DB.update_field(collection, sym, "price_change.ten_percent_down_times", ten_percent_down_times)
            DB.update_field(collection, sym, "price_change.twenty_percent_down_times", twenty_percent_down_times)
            
            # Get today's price
            query = 'select `Adj Close` from {} order by Date desc limit 1'.format(table_name)
            result=sql_engine.execute(query)
            price = result.first()[0]
            #price = hdf5.hdf_get_price(sym, df, dt.now().date())
            
            if not high_price or high_price == 0:
                change = 0
            else:
                change = (price/high_price) - 1

            DB.update_field(collection, sym, "price_change.with_52week_high", change)
            
            if not low_price or low_price == 0:
                change = 0
            else:
                change = (price/low_price) - 1

            DB.update_field(collection, sym, "price_change.with_52week_low", change)

            #query = 'select max(`Adj Close`) from {}'.format(table_name)
            query = 'select Date, `Adj Close` from {} where `Adj Close` = (SELECT MAX(`Adj Close`) FROM {})'.format(table_name, table_name)
            #adf = DB.read_from_sql(query, sql_engine)
            result = sql_engine.execute(query)
            ret = result.first()
            all_time_high_date = dt.strptime(ret[0], "%Y-%m-%d")
            all_time_high_price = ret[1]
            if not all_time_high_price or all_time_high_price == 0:
                change = 0
            else:
                change = percent_change(all_time_high_price, price)

            DB.update_field(collection, sym, "price_change.with_all_time_high", change)
            DB.update_field(collection, sym, "price_change.all_time_high_price", all_time_high_price)
            DB.update_field(collection, sym, "price_change.all_time_high_date", all_time_high_date)

            query = 'select Date, `Adj Close` from {} where `Adj Close` = (SELECT MIN(`Adj Close`) FROM {})'.format(table_name, table_name)
            #adf = DB.read_from_sql(query, sql_engine)
            result = sql_engine.execute(query)
            ret = result.first()
            all_time_low_date = dt.strptime(ret[0], "%Y-%m-%d")
            all_time_low_price = ret[1]
            if not all_time_low_price or all_time_low_price == 0:
                change = 0
            else:
                change = percent_change(all_time_low_price, price)

            DB.update_field(collection, sym, "price_change.with_all_time_low", change)
            DB.update_field(collection, sym, "price_change.all_time_low_price", all_time_low_price)
            DB.update_field(collection, sym, "price_change.all_time_low_date", all_time_low_date)
            
            if all_time_high_date < all_time_low_date:
                change = percent_change(all_time_high_price, all_time_low_price)
            else:
                change = percent_change(all_time_low_price, all_time_high_price)

            DB.update_field(collection, sym, "price_change.with_all_time_low_high_change", change)
 
            if 'Highlights' in stk.keys() and 'MarketCapitalization' in stk['Highlights'].keys() and stk['Highlights']['MarketCapitalization'] != None:
                if price > 0:
                    num_shares = stk['Highlights']['MarketCapitalization']/price
                else:
                    num_shares = 0
                all_time_high_mcap = num_shares * all_time_high_price
                DB.update_field(collection, sym, "price_change.all_time_high_mcap", all_time_high_mcap)
            else:
                DB.update_field(collection, sym, "price_change.all_time_high_mcap", nan)

            query = 'SELECT Date, Volume FROM (SELECT * FROM {} ORDER BY Date DESC LIMIT 60) AS sub ORDER BY Date ASC'.format(table_name)

            df = DB.read_from_sql(query, sql_engine)
            if not df.empty:
                vol_mean = df['Volume'].mean()
                DB.update_field(collection, sym, "price_change.avg_volume", vol_mean)

                price_times_avg_vol_in_mn = round((price*vol_mean)/1000000,2)
                DB.update_field(collection, sym, "price_change.price_times_avg_vol_in_mn", price_times_avg_vol_in_mn)
                if 'Highlights' in stk.keys() and 'MarketCapitalizationMln' in stk['Highlights'].keys() and stk['Highlights']['MarketCapitalizationMln'] != None:
                    avg_vol_pcent_in_mcap_mn = round(((price*vol_mean)/(stk['Highlights']['MarketCapitalizationMln'] * 1000000))*100, 2)
                    DB.update_field(collection, sym, "price_change.avg_vol_pcent_in_mcap_mn",avg_vol_pcent_in_mcap_mn)
                else:
                    DB.update_field(collection, sym, "price_change.avg_vol_in_mcap_mn", None)
            else:
                DB.update_field(collection, sym, "price_change.avg_volume", None)
                DB.update_field(collection, sym, "price_change.price_times_avg_vol_in_mn", None)
                DB.update_field(collection, sym, "price_change.avg_vol_pcent_in_mcap_mn", None)

            # Update price change since last earnings date
            if type == 'Stocks' and sym not in US_indices.keys():
                query = 'select Symbol, Date, reportDate, marketCap, name from Nasdaq_Earnings_History where Symbol=\'{}\' order by date desc limit 2'.format(sym)
                rdf = DB.read_from_sql(query, fin_engine)
                if rdf.empty:
                    DB.update_field(collection, sym, "price_change.since_ndaq_last_earnings", None)
                else:
                    report_date=dt.strptime(rdf.iloc[0]['reportDate'], "%Y-%m-%d")
                    # Future date is already updated in the database. So consider previous date
                    if report_date > dt.now():
                        report_date=dt.strptime(rdf.iloc[-1]['reportDate'], "%Y-%m-%d")

                    DB.update_field(collection, sym, "dates.ndaq_previous_earnings_date", report_date)
                    query = 'select * from {} where Date >=\'{}\' order by Date'.format(table_name, str(report_date.date()))
                    df = DB.read_from_sql(query, sql_engine)
                    change = percent_change(df.iloc[0]['Adj Close'], df.iloc[-1]['Adj Close'])
                    DB.update_field(collection, sym, "price_change.since_ndaq_last_earnings", change)

        else:
            change=None
            DB.update_field(collection, sym, "price_change.day", change)
            DB.update_field(collection, sym, "price_change.week", change)
            DB.update_field(collection, sym, "price_change.month", change)
            DB.update_field(collection, sym, "price_change.quarter", change)
            DB.update_field(collection, sym, "price_change.half_year", change)
            DB.update_field(collection, sym, "price_change.year", change)
            DB.update_field(collection, sym, "price_change.whole", change)
            DB.update_field(collection, sym, "bscs.fiftytwoweek_high", change)
            DB.update_field(collection, sym, "bscs.fiftytwoweek_low", change)
            DB.update_field(collection, sym, "price_change.with_52week_high", change)
            DB.update_field(collection, sym, "price_change.with_52week_low", change)
            DB.update_field(collection, sym, "price_change.since_last_earnings", change)
            DB.update_field(collection, sym, "price_change.since_ndaq_last_earnings", None)
        update = True
    except Exception as E:
        print("Error: price_change: %s, %s" %(stk['bscs'], str(E)))
    finally:
        if update:
            print("price_change: sym: %s" %(sym))
            DB.update_field(collection, sym, "price_change.date", dt.combine(dt.now(), dt.min.time()))
        DB.close_sql_connection(sql_engine)
        if fin_engine:
            DB.close_sql_connection(fin_engine)
        if params_engine:
            DB.close_sql_connection(params_engine)
        DB.close_db_client(c)
        if sem:
            sem.release()

def update_all_crypto_price_change():
    c  = DB.open_db_client()
    db = c['Cryptos']

    cryptos = db.Cryptos.find({"$or" : [ \
                                            {"price_change.date": {"$exists": False }},\
                                            {"price_change.date": {"$lt": dt.combine(dt.now().date(), dt.min.time())}},\
                                        ]\
                                }\
                                )

    cryptos = db.Cryptos.find({})
    try:
        for i, crypto in enumerate(cryptos):
            print("%d: %r" %(i, crypto['bscs']['symbol']))
            update_price_change('US', crypto, 1, None, index=True,type='Crypto')

    finally:
        DB.close_db_client(c)


def fork_hdf5_process(country):
    ## Randomly get all records whose price is not updated till today
    ##pipeline = [{'$sample': {'size':num_docs}},
    ##            {'$match' : {"price_change.date": {'$ne':today}}},
    ##            #{"$group": {"_id": _id, "count": {"$sum":1}}},
    ##            #{"$group": {"_id": None, "total": {"$sum": 1}, "details":{"$push":{"groupby": "$_id", "count": "$count"}}}}
    ##            ]

    ##stocks = db.US_Stocks.aggregate(pipeline, allowDiskUse=True).batch_size(10)
    c = DB.open_db_client()
    db = c['Stocks']
    collection = DB.get_collection(country, db)
    sql_engine = DB.open_sql_connection('localhost', 'root', 'petla123', db='US_Stocks')

    sort = [1, -1][dt.now().day % 2 == 0]
    today=str(dt.now().date())
    num_docs = collection.find({}).count()
    #num_docs = collection.find({"dates.price_date": {'$ne':today}})
    if num_docs == 0:
        close_db_client(c)
        close_sql_connection(sql_engine)
        return

    symbols = DB.get_symbols_from_sql(country, sql_engine)
    #symbols = get_symbols_from_mongo(collection)
    
    if country == 'India':
        indices = India_indices
    else:
        indices = US_indices 
    stk = {}
    stk['bscs']={}
    stk['General']={}

    num_processes = DB.num_cores * 8
    sem = multiprocessing.BoundedSemaphore(num_processes)
    processes = [None]*num_processes

    try:
        ## ETFs are not Common Stock, so update them explicitly before the
        ## regular stock query below.
        #for k in etfs:
        #    etf_docs = collection.find({'bscs.symbol': k}, no_cursor_timeout=True).batch_size(10).sort([["sno", 1]])
        #    if etf_docs.count() == 1:
        #        etf_stk = etf_docs[0]
        #        print("Price Change: ETF: %r, Name: %r" %(etf_stk['bscs']['symbol'], etf_stk['General']['Name']))
        #        update_price_change(country, copy.deepcopy(etf_stk), 0, sem=None, index=False)

        #Indices
        for i, k in enumerate(indices.keys()):
            stk['bscs']['symbol']  = k
            stk['bscs']['name']    = indices[k]
            stk['General']['Code'] = k
            stk['General']['Name'] = indices[k]
            DB.write_to_collection(collection, stk)
            sem.acquire()
            update_price_change(country, stk, 1, sem, index=True)
            #threading.Thread(target=update_price_change, args=(country, collection, copy.deepcopy(stk['bscs']['symbol']), sem, sql_engine,)).start()
            #processes[i%num_processes] = multiprocessing.Process(target=update_price_change, args=(country, copy.deepcopy(stk), i%DB.num_cores, sem, True))
            #processes[i%num_processes].start()

        ## Randomly get all records whose price is not updated till today
        ##pipeline = [{'$sample': {'size':num_docs}},
        ##            {'$match' : {"dates.price_date": {'$ne':today}}},
        ##            #{"$group": {"_id": _id, "count": {"$sum":1}}},
        ##            #{"$group": {"_id": None, "total": {"$sum": 1}, "details":{"$push":{"groupby": "$_id", "count": "$count"}}}}
        ##            ]

        ##stocks = db.US_Stocks.aggregate(pipeline, allowDiskUse=True).batch_size(10)
        #stocks = collection.find({},no_cursor_timeout=True).batch_size(10).sort([["sno",1]]).allow_disk_use(True)
        #stocks=db.US_Stocks.find({"$and":[{'General.Exchange':{"$in":major_exchanges}},\
        #                                    {'General.Type':'Common Stock'}, \
        #                                    {'price_change.date': {'$lt':DB.get_latest_trading_day()}} \
        #                                ]}).batch_size(10).sort([["sno",1]]).allow_disk_use(True)

        #stocks = db.US_Stocks.find({"$and" : [{"price_change.date": {"$lt": DB.get_latest_trading_day()}}, {"General.IsDelisted": False}, {'General.Type':'Common Stock'}, {'General.Exchange':{"$in":major_exchanges}}]}).batch_size(10).sort([["sno",1]]).allow_disk_use(True)
        stocks=db.US_Stocks.find({"$and":[\
                                            #{'General.Exchange':{"$in":major_exchanges}},\
                                            {"$or": [\
                                                        {'General.Exchange':{"$in":major_exchanges}},\
                                                        {"$and": [ \
                                                                    {'General.Exchange':{"$nin":major_exchanges}},\
                                                                    {'bscs.tracking':{'$exists':True}}, \
                                                                ] \
                                                        },\
                                                    ]\
                                            },\
                                            {'General.Type':'Common Stock'},\
                                            {'General.IsDelisted': False},\
                                            {"$or": [\
                                                        {'dates.mysql_price_pull_date': {'exists':False}},\
                                                        {"$and": [ \
                                                                    #vpetla{'dates.mysql_price_date': {"$gte": DB.get_latest_trading_day()}},\
                                                                    {'dates.mysql_price_pull_success': True},\
                                                                    {'failcount.mysql_price_failcount': {'$eq': 0}},\
                                                                    #{'failcount.mysql_price_failcount': {'$lt': MAX_FAIL_COUNT}},\
                                                                ]\
                                                        }, \
                                                    ] \
                                            },\
                                            {"$or":[\
                                                    {'price_change.date': {"$lt":DB.get_latest_trading_day()}},\
                                                    {'price_change.date': {"$exists": False}}\
                                                    ]\
                                            },\
                                        ]}).batch_size(10).sort([['failcount.mysql_price_failcount',1]]).allow_disk_use(True).sort([['sno',sort]]).allow_disk_use(True)
        #stocks = collection.find({'bscs.symbol':'AAPL'},no_cursor_timeout=True).batch_size(10).sort([["sno",1]])
        print("Price Change: Stocks: %r" %(stocks.count())) 
        i=0
        today=dt.now().date()
        for i, stk in enumerate(stocks):
            #if ignore_stock(stk):
            #    continue
            #print("%r" %(i))

            #last_updated_date = dt.strptime(stk['price_change']['date'].split(' ')[0], "%Y-%m-%d").date()
            #if last_updated_date >= today:
            #    continue
            #if stk['bscs']['symbol'] not in symbols:
            #    continue
            
            #if 'price_failcount' in stk['bscs'].keys() and stk['bscs']['price_failcount'] > 5:
            #    continue

            if 'General' in stk.keys() and 'Name' in stk['General'].keys():
                print("%d: Symbol: %r, Name: %r" %(i, stk['General']['Code'], stk['General']['Name']))
            else:
                print("%d: Symbol: %r" %(i, stk['bscs']['symbol']))
            sem.acquire()
            #core = get_free_core()
            #update_price_change(country, stk, i%DB.num_cores, sem, index=False)
            processes[i%num_processes] = multiprocessing.Process(target=update_price_change, args=(country, copy.deepcopy(stk), i%DB.num_cores, sem, False))
            processes[i%num_processes].start()
    finally:
        # Wait till all threads are completed. You can use join() instead.
        # But need to track threads and update variables.
        # Simplest way is to wait for tentative time taken for the end threads to complete
        # Randomly estimated it to be 10 sec and it perfectly works.
        time.sleep(30)
        for j in range(len(processes)):
            if processes[j] is not None:
                processes[j].join()
        DB.close_sql_connection(sql_engine)
        DB.close_db_client(c)
    print("Percentage Change: Stocks tried :%r"%(i))

def fork_betas_process(country, sem):
    c = DB.open_db_client()
    db = c['Stocks']
    collection = DB.get_collection(country, db)
    num_docs = collection.find({}).count()
    # Randomly get all records whose price is not updated till today
    #pipeline = [{'$sample': {'size':num_docs}},
    #            {'$match' : {"price_change.date": {'$ne':today}}},
    #            #{"$group": {"_id": _id, "count": {"$sum":1}}},
    #            #{"$group": {"_id": None, "total": {"$sum": 1}, "details":{"$push":{"groupby": "$_id", "count": "$count"}}}}
    #            ]

    #stocks = db.US_Stocks.aggregate(pipeline, allowDiskUse=True).batch_size(10)
    stocks = collection.find({},no_cursor_timeout=True).batch_size(10).sort([["sno",1]])
 
    today=str(dt.now().date())
    i=0
    for stk in stocks:
        if DB.ignore_stock(stk):
            continue

        print("Betas: %d: %s: %s"%(i,stk['bscs']['symbol'],stk['bscs']['name']))
        sem.acquire()
        #DB.update_stock_betas(country, collection, stk)
        threading.Thread(target=DB.update_stock_betas, args=(country, collection, stk, sem,)).start()
        #break
        i = i + 1
        #if i > 10:
        #    break

    # Wait randomly till all threads are completed
    time.sleep(10)
    DB.close_db_client(c)
    print("Total stocks: %r" %(i))


# Update the DB with yearly, quarterly and monthly percentage price change
def update_all_stocks_price_change(country):
    i = 0
    #max_threads = DB.thread_factor
    #hdf5_sem = threading.BoundedSemaphore(max_threads)
    #betas_sem = threading.BoundedSemaphore(max_threads)
 
    print("Updating price percent changes")
    fork_hdf5_process(country)
    #fork_betas_process(country, betas_sem)
    #hdf5_process = multiprocessing.Process(target=fork_hdf5_process, args=(country, hdf5_sem, ))
    #betas_process = multiprocessing.Process(target=fork_betas_process, args=(country, betas_sem, ))
    
    #hdf5_process.start()
    #betas_process.start()

    #hdf5_process.join()
    #betas_process.join()

def pcent(val):
    if val != None:
        return str(round(val *100, 2))+'%'
    return ""

#Minimum percent changes per day, week, month etc that should
# be considered for stocks with different ranges of mcaps
# in descending order
# [> 100bn, 10bn to 100bn, 5bn to 10bn, 1bn to 5bn, 1mn to 1bn]
pcent_chg = {}
pcent_chg['day']       = [0.03, 0.05, 0.05, 0.10, 0.15]
pcent_chg['week']      = [0.05, 0.05, 0.10, 0.15, 0.20]
pcent_chg['month']     = [0.05, 0.05, 0.10, 0.15, 0.20]
pcent_chg['quarter']   = [0.10, 0.10, 0.20, 0.25, 0.25]
pcent_chg['half_year'] = [0.15, 0.15, 0.25, 0.30, 0.50]
pcent_chg['year']      = [0.20, 0.20, 0.25, 0.30, 0.50]

# Day change column position.
# Week = Day + 1
# Month = Week + 1 etc
day_col = 10
week_col = day_col + 1
month_col = week_col + 1
quarter_col = month_col + 1
half_year_col = quarter_col + 1
year_col = half_year_col + 1

highlight_columns = { 'day': day_col, 'week':week_col, 'month':month_col, 'quarter':quarter_col, 'half_year':half_year_col, 'year':year_col }

def get_stocks_max_mcap_change(country, low_mcap, high_mcap, direction, change, limit=-1):
    max_change='price_change.with_all_time_high'
    if direction == -1:
        cond = '$lte'
    else:
        cond = '$gte'

    factor = 1
    Mn = 1000000
    Bn = 1000*Mn
    Tn = 1000*Bn

    if country == 'US':
        mcap = "MCap in Millions"
        #Convert to billions for mcap greater than 1 billion.
        if high_mcap > 1000:
            factor = 1/1000
            mcap = "Mcap Bn"
    elif country == 'India':
        mcap = "MCap in Crores"

    db = DB.open_db('Stocks')
    collection = DB.get_collection(country, db)
    
    entries = []
    head=["Symbol", "Name", "Since", "Sectr", mcap, "Alltime Hgh MCap", "With Alltime Hgh", "Vol", "Vol*Price Mn", "Vol %Mcap", "Price", "Day Chg", "Wk Chg", "Mth Chg", "Qrtr Chg", "Hf Yr Chg", "Yr Chg"]

    entries.append(head)
    today = dt.combine(dt.now(), dt.min.time())
    if today == DB.get_latest_trading_day():
        latest_date = DB.get_latest_trading_day()
    else:
        latest_date = DB.get_previous_trading_day()

    #stocks = collection.find({"$and" : [{"dates.mysql_price_date": {"$eq": latest_date}}, {"General.IsDelisted": False}, {'General.Type':'Common Stock'}, {'General.Exchange':{"$in":major_exchanges}}]}).batch_size(10).sort([["failcount.mysql_price_failcount",1]]).allow_disk_use(True).sort([["sno",1]]).allow_disk_use(True)

    conditions = [\
                 #{"dates.mysql_price_date": {"$gte": DB.get_latest_trading_day()}},\
                 {"dates.mysql_price_pull_success": True},\
                 {'price_change.all_time_high_mcap':{'$gte':low_mcap, '$lt':high_mcap}},\
                 {'General.IsDelisted': False},\
                 {'General.Type':'Common Stock'},\
                 #{'General.Exchange':{"$in":major_exchanges}},\
                 {"$or": [\
                             {'General.Exchange':{"$in":major_exchanges}},\
                             {"$and": [ \
                                         {'General.Exchange':{"$nin":major_exchanges}},\
                                         {'bscs.tracking':{'$exists':True}}, \
                                     ] \
                             },\
                         ]\
                 },\
                ]

    if direction > 0 :
        conditions.append({max_change:{cond:change}})
        if limit > 0:
            stocks = collection.find({'$and':conditions}).limit(limit).sort([[max_change,-1]])
        else:
            stocks = collection.find({'$and':conditions}).sort([[max_change,-1]])
        #stocks = collection.find({'$and':[\
        #                                    #{"dates.mysql_price_date": {"$gte": DB.get_latest_trading_day()}},\
        #                                    {"dates.mysql_price_pull_success": True},\
        #                                    {'Highlights.MarketCapitalization':{'$gte':low_mcap, '$lt':high_mcap}},\
        #                                    {'General.IsDelisted': False},\
        #                                    {'General.Type':'Common Stock'},\
        #                                    #{'General.Exchange':{"$in":major_exchanges}},\
        #                                    {"$or": [\
        #                                                {'General.Exchange':{"$in":major_exchanges}},\
        #                                                {"$and": [ \
        #                                                            {'General.Exchange':{"$nin":major_exchanges}},\
        #                                                            {'bscs.tracking':{'$exists':True}}, \
        #                                                        ] \
        #                                                },\
        #                                            ]\
        #                                    },\
        #                                    {rsi_change:{cond:change}},\
        #                                    ]}).sort([[rsi_change,-1]])
    else:
        conditions.append({max_change:{cond:change}})
        if limit > 0:
            stocks = collection.find({'$and':conditions}).limit(limit).sort([[max_change,1]])
        else:
            stocks = collection.find({'$and':conditions}).sort([[max_change,1]])

        #stocks = collection.find({'$and':[\
        #                                    #{"dates.mysql_price_date": {"$gte": DB.get_latest_trading_day()}},\
        #                                    {"dates.mysql_price_pull_success": True},\
        #                                    {'Highlights.MarketCapitalization':{'$gte':low_mcap, '$lt':high_mcap}},\
        #                                    {'General.IsDelisted': False},\
        #                                    {'General.Type':'Common Stock'},\
        #                                    #{'General.Exchange':{"$in":major_exchanges}},\
        #                                    {"$or": [\
        #                                                {'General.Exchange':{"$in":major_exchanges}},\
        #                                                {"$and": [ \
        #                                                            {'General.Exchange':{"$nin":major_exchanges}},\
        #                                                            {'bscs.tracking':{'$exists':True}}, \
        #                                                        ] \
        #                                                },\
        #                                            ]\
        #                                    },\
        #                                    #{rsi_change:{'$gte':change}},\
        #                                    {rsi_change:{'$lte':0}},\
        #                                    ]}).sort([[rsi_change,1]]).limit(10)

    #query = {'$and': [{'bscs.mcap':{'$gte':low_mcap, '$lt':high_mcap}}, {price_change:{cond:change}}]}
    #stocks = db.US_Stocks.find(query).sort([[price_change,direction]])
    i = 0
    for stk in stocks:
        if limit > 0 and i > limit:
            break
        #if DB.ignore_stock(stk):
        #    continue

        bscs  = stk['bscs']
        pchg = stk['price_change']
        print("%r: %s: %s" %(stk['sno'], bscs['symbol'], stk['General']['Name']))
        entry = [ ]
        entry.append(bscs['symbol'])
        entry.append(stk['General']['Name'])
        entry.append(stk['General']['IPODate'])
        if stk['General']['GicSubIndustry']:
            entry.append(stk['General']['GicSubIndustry'])
        else:
            entry.append("")

        cur_mcap = stk['Highlights']['MarketCapitalizationMln']
        num = " Mn"
        if cur_mcap > 1000:
            cur_mcap = cur_mcap/1000
            num = " Bn"
        if cur_mcap > 1000:
            cur_mcap = cur_mcap/1000
            num = " Tn"
 
        entry.append(str(round(cur_mcap, 2))+num)

        if 'price_change' in stk.keys() and 'all_time_high_mcap' in stk['price_change'].keys():
            all_high_mcap = stk['price_change']['all_time_high_mcap']/Mn
            num = " Mn"
            if all_high_mcap > 1000:
                all_high_mcap = all_high_mcap/1000
                num = " Bn"
            if all_high_mcap > 1000:
                all_high_mcap = all_high_mcap/1000
                num = " Tn"
            entry.append(str(round(all_high_mcap,2))+num)
        else:
            entry.append("")

        if 'with_all_time_high' in pchg.keys():
            entry.append(pcent(pchg['with_all_time_high']))
        else:
            entry.append("")
 
        if 'price_change' in stk.keys() and \
            'volume' in stk['price_change'].keys():
            entry.append(str(round(stk['price_change']['volume']/1000, 2))+'k')
        else:
            entry.append("-")
        if 'price_change' in stk.keys() and \
            'avg_volume' in stk['price_change'].keys():
            entry.append((str(round(((stk['price_change']['price']*stk['price_change']['avg_volume'])/(stk['Highlights']['MarketCapitalizationMln'] * 1000000))*100, 2))+'%'))
        else:
            entry.append("-")
        if 'price' in stk['price_change'].keys() and 'avg_volume' in stk['price_change'].keys():
            entry.append(str(round((stk['price_change']['price']*stk['price_change']['avg_volume'])/1000000,2)))
        else:
            entry.append("")
       
        if 'price_change' in stk.keys() and 'price' in stk['price_change'].keys():
            entry.append(str(stk['price_change']['price']))
        else:
            entry.append("")
        entry.append(pcent(pchg['day']))
        entry.append(pcent(pchg['week']))
        entry.append(pcent(pchg['month']))
        entry.append(pcent(pchg['quarter']))
        entry.append(pcent(pchg['half_year']))
        entry.append(pcent(pchg['year']))
 
        entries.append(entry)
        i = i + 1

    DB.close_db()
    return entries

def build_html_max_mcap(s, country, low_mcap, high_mcap, change, segment, uplimit=-1,downlimit=-1): 
    direction = 1
    highlight_column = 6
    s = parse_html.html_text(s, segment)
    e = get_stocks_max_mcap_change(country, low_mcap, high_mcap, -direction, change, uplimit)
    if len(e) > 1:
        #entries = [["Up"]]
        entries = e
        s = parse_html.html_set_line(s)
        s = parse_html.html_text(s, entries, highlight_column)
    #e = get_stocks_max_mcap_change(country, low_mcap, high_mcap, -direction, -change, downlimit)
    #if len(e) > 1:
    #    #entries = [["Up"]]
    #    entries = e
    #    s = parse_html.html_set_line(s)
    #    s = parse_html.html_text(s, entries, highlight_column)

    return s

def get_max_mcap_changes(s, country):

    Mn = 1000000
    Bn = 1000*Mn
    Tn = 1000*Bn
    #s = parse_html.html_set_line(s)
    print("MCap 100 Bn and above")
    s = build_html_max_mcap(s, country, 100*Bn, 100*Tn, -0.70, ["Max MCap 100 Bn and above"])
    print("MCap 10 Bn and 100 Bn")
    s = build_html_max_mcap(s, country, 10*Bn, 100*Bn,  -0.70, ["Max MCap 10 Bn and 100 Bn"], downlimit=20)
    print("MCap 5 Bn and 10 Bn")
    s = build_html_max_mcap(s, country, 5*Bn, 10*Bn,    -0.70, ["Max MCap 5 Bn and 10 Bn"], downlimit=20)
    print("MCap 1 Bn and 5 Bn")
    s = build_html_max_mcap(s, country, 1*Bn, 5*Bn,     -0.70, ["Max MCap 1 Bn and 5 Bn"], downlimit=20)
    print("MCap 500Mn and 1 Bn")
    s = build_html_max_mcap(s, country, 500*Mn, 1*Bn,   -0.70, ["MCap 500Mn and 1 Bn"], uplimit=20, downlimit=20)
    print("MCap < 500 Mn")
    s = build_html_max_mcap(s, country, 1, 500*Mn,      -0.90, ["MCap < 500 Mn"], uplimit=20, downlimit=20)
    return s

def send_email_max_mcap_changes(country):
    disconnect_vpn()

    s = parse_html.html_head()
    s = parse_html.html_text(s, ["All Time Low Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_max_mcap_changes(s, country)
    s = parse_html.html_set_line(s)
    subject='%s: All Time Low Snapshot: %r' %(country, str(datetime.datetime.now().date()))
    send_email2('petlafin@gmail.com', 'petlafin@gmail.com', subject, s)

def get_stocks_trend_change(country, low_mcap, high_mcap, change, limit=-1):
    trend_change='technicals.sar.ta_psar_trend'

    factor = 1
    if country == 'US':
        mcap = "MCap in Millions"
        #Convert to billions for mcap greater than 1 billion.
        if high_mcap > 1000:
            factor = 1/1000
            mcap = "Mcap Bn"
    elif country == 'India':
        mcap = "MCap in Crores"

    db = DB.open_db('Stocks')
    collection = DB.get_collection(country, db)
    
    entries = []
    head=["Symbol", "Name", "Since", "Sectr", mcap, "Vol", "Vol*Price Mn", "Vol %Mcap", "PSAR Prev", "PSAR Cur", "Cur Trend", "Full Trend", "Price", "Day Chg", "Wk Chg", "Mth Chg", "Qrtr Chg", "Hf Yr Chg", "Yr Chg", "RSI", "RSI_Min_Diff", "RSI_Max_Diff", "RSI_PR_Chg"]

    entries.append(head)
    today = dt.combine(dt.now(), dt.min.time())
    if today == DB.get_latest_trading_day():
        latest_date = DB.get_latest_trading_day()
    else:
        latest_date = DB.get_previous_trading_day()

    conditions = [\
                 {"dates.mysql_price_date": {"$gte": DB.get_previous_trading_day()}},\
                 {"dates.mysql_price_pull_success": True},\
                 {'Highlights.MarketCapitalization':{'$gte':low_mcap, '$lt':high_mcap}},\
                 {'General.IsDelisted': False},\
                 {'General.Type':'Common Stock'},\
                 #{'General.Exchange':{"$in":major_exchanges}},\
                 {"$or": [\
                             {'General.Exchange':{"$in":major_exchanges}},\
                             {"$and": [ \
                                         {'General.Exchange':{"$nin":major_exchanges}},\
                                         {'bscs.tracking':{'$exists':True}}, \
                                     ] \
                             },\
                         ]\
                 },\
                 {'technicals.sar.ta_psar_trend':{"$gte":1, '$lte':5}},\
                 {'technicals.sar.ta_psar_prev_trend':{"$lte":-15}},\
                 {"$or": [\
                            {'price_change.price_times_avg_vol_in_mn':{"$gte": 100}},\
                            {'price_change.avg_vol_pcent_in_mcap_mn':{"gte": 4}},\
                        ]\
                 },\
                 #{'technicals.sar.ta_psar_cur_trend_price_change': {"gte": 0.05}},\
                ]

    if limit > 0:
        stocks = collection.find({'$and':conditions}).limit(limit).sort([['technicals.sar.ta_psar_cur_trend_price_change',-1]])
    else:
        stocks = collection.find({'$and':conditions}).sort([['technicals.sar.ta_psar_cur_trend_price_change',-1]])

    #query = {'$and': [{'bscs.mcap':{'$gte':low_mcap, '$lt':high_mcap}}, {price_change:{cond:change}}]}
    #stocks = db.US_Stocks.find(query).sort([[price_change,direction]])
    i = 0
    for stk in stocks:
        if limit > 0 and i > limit:
            break
        #if DB.ignore_stock(stk):
        #    continue

        bscs  = stk['bscs']
        pchg = stk['price_change']
        print("%r: %s: %s" %(stk['sno'], bscs['symbol'], stk['General']['Name']))
        entry = [ ]
        entry.append(bscs['symbol'])
        entry.append(stk['General']['Name'])
        entry.append(stk['General']['IPODate'])
        if stk['General']['GicSubIndustry']:
            entry.append(stk['General']['GicSubIndustry'])
        else:
            entry.append("")
        entry.append(str(round(stk['Highlights']['MarketCapitalizationMln']*factor, 2)))
        if 'price_change' in stk.keys() and \
            'volume' in stk['price_change'].keys():
            entry.append(str(round(stk['price_change']['volume']/1000, 2))+'k')
        else:
            entry.append("-")
        if 'price_change' in stk.keys() and \
            'avg_volume' in stk['price_change'].keys():
            #entry.append((str(round(((stk['price_change']['price']*stk['price_change']['avg_volume'])/(stk['Highlights']['MarketCapitalizationMln'] * 1000000))*100, 2))+'%'))
            entry.append(str(round((stk['price_change']['price']*stk['price_change']['volume'])/1000000, 2))+'Mn')
        else:
            entry.append("-")
        if 'price' in stk['price_change'].keys() and 'avg_volume' in stk['price_change'].keys():
            #entry.append(str(round((stk['price_change']['price']*stk['price_change']['avg_volume'])/1000000,2)))
            entry.append(str(round(((stk['price_change']['price']*stk['price_change']['avg_volume'])/(stk['Highlights']['MarketCapitalizationMln'] * 1000000))*100, 2))+'%')
        else:
            entry.append("")
        #try:
        if 'technicals' in stk.keys() and \
                'sar' in stk['technicals'].keys():
                if 'ta_psar_prev_trend_price_change' in stk['technicals']['sar'].keys() and \
                    stk['technicals']['sar']['ta_psar_prev_trend_price_change'] != None:
                        entry.append(str(round(stk['technicals']['sar']['ta_psar_prev_trend_price_change']*100,2))+'%')
                else:
                    entry.append("-")
                if 'ta_psar_cur_trend_price_change' in stk['technicals']['sar'].keys() and \
                    stk['technicals']['sar']['ta_psar_cur_trend_price_change'] != None:
                    entry.append(str(round(stk['technicals']['sar']['ta_psar_cur_trend_price_change']*100,2))+'%')
                else:
                    entry.append("-")
                if 'ta_psar_trend' in stk['technicals']['sar'].keys():
                    trend = stk['technicals']['sar']['ta_psar_trend']
                    if trend > 0:
                        cur_trend = str(trend) + 'L'
                    else:
                        cur_trend = str(-trend) + 'S'
                else:
                    entry.append("-")
                entry.append(cur_trend)

                if 'ta_psar_trend_sequence' in stk['technicals']['sar'].keys():
                    entry.append(stk['technicals']['sar']['ta_psar_trend_sequence'])
                else:
                    entry.append("-")
        else:
            entry.append("-")
            entry.append("-")
            entry.append("-")
            entry.append("-")
       
        if 'price_change' in stk.keys() and 'price' in stk['price_change'].keys():
            entry.append(str(stk['price_change']['price']))
        else:
            entry.append("")
        entry.append(pcent(pchg['day']))
        entry.append(pcent(pchg['week']))
        entry.append(pcent(pchg['month']))
        entry.append(pcent(pchg['quarter']))
        entry.append(pcent(pchg['half_year']))
        entry.append(pcent(pchg['year']))

        if 'technicals' in stk.keys() and \
            'rsi' in stk['technicals'].keys() and \
            'latest' in stk['technicals']['rsi'].keys() and \
            stk['technicals']['rsi']['latest'] != None:
            entry.append(str(round(stk['technicals']['rsi']['latest'], 2)))
            min_diff = stk['technicals']['rsi']['latest'] - stk['technicals']['rsi']['60day_min']
            max_diff = stk['technicals']['rsi']['60day_max'] - stk['technicals']['rsi']['latest']
            entry.append(str(round(min_diff,2)))
            entry.append(str(round(max_diff,2)))
            entry.append(str(round(stk['technicals']['rsi']['cur_price_max_rsi_change']*100, 2))+'%')
        else:
            entry.append("-")
            entry.append("-")
            entry.append("-")
            entry.append("-")
 
        entries.append(entry)
        i = i + 1

    DB.close_db()
    return entries

def build_html_trend(s, country, low_mcap, high_mcap, change, segment, limit=-1): 
    highlight_column = 9
    s = parse_html.html_text(s, segment)
    e = get_stocks_trend_change(country, low_mcap, high_mcap, change, limit)
    if len(e) > 1:
        #entries = [["Up"]]
        entries = e
        s = parse_html.html_set_line(s)
        s = parse_html.html_text(s, entries, highlight_column)
    return s

def get_trend_changes(s, country):

    Mn = 1000000
    Bn = 1000*Mn
    Tn = 1000*Bn
    #s = parse_html.html_set_line(s)
    print("MCap 100 Bn and above")
    s = build_html_trend(s, country, 100*Bn, 100*Tn, 0.10, ["MCap 100 Bn and above"])
    print("MCap 10 Bn and 100 Bn")
    s = build_html_trend(s, country, 10*Bn, 100*Bn,  0.10, ["MCap 10 Bn and 100 Bn"], limit=20)
    print("MCap 5 Bn and 10 Bn")
    s = build_html_trend(s, country, 5*Bn, 10*Bn,    0.15, ["MCap 5 Bn and 10 Bn"], limit=20)
    print("MCap 1 Bn and 5 Bn")
    s = build_html_trend(s, country, 1*Bn, 5*Bn,     0.15, ["MCap 1 Bn and 5 Bn"], limit=20)
    #print("MCap 500Mn and 1 Bn")
    #s = build_html_trend(s, country, 500*Mn, 1*Bn,   0.20, ["MCap 500Mn and 1 Bn"], uplimit=20, downlimit=20)
    #print("MCap < 500 Mn")
    #s = build_html_trend(s, country, 1, 500*Mn,      0.30, ["MCap < 500 Mn"], uplimit=20, downlimit=20)
    return s

def send_email_trend_changes(country='US'):
    disconnect_vpn()

    s = parse_html.html_head()
    s = parse_html.html_text(s, ["Trend Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_trend_changes(s, country)
    s = parse_html.html_set_line(s)
    subject='%s: Trend Snapshot: %r' %(country, str(datetime.datetime.now().date()))
    send_email2('petlafin@gmail.com', 'petlafin@gmail.com', subject, s)


############
def get_stocks_rsi_change(country, low_mcap, high_mcap, direction, change, limit=-1):
    rsi_change='technicals.rsi.cur_price_max_rsi_change'
    if direction == -1:
        cond = '$lte'
    else:
        cond = '$gte'

    factor = 1
    if country == 'US':
        mcap = "MCap in Millions"
        #Convert to billions for mcap greater than 1 billion.
        if high_mcap > 1000:
            factor = 1/1000
            mcap = "Mcap Bn"
    elif country == 'India':
        mcap = "MCap in Crores"

    db = DB.open_db('Stocks')
    collection = DB.get_collection(country, db)
    
    entries = []
    head=["Symbol", "Name", "Since", "Sectr", mcap, "Vol", "Vol*Price Mn", "Vol %Mcap", "RSI", "RSI Min Diff", "RSI Max Diff", "RSI PR Chg", "PSAR Trend", "PSAR Cur", "PSAR Prev", "Price", "Day Chg", "Wk Chg", "Mth Chg", "Qrtr Chg", "Hf Yr Chg", "Yr Chg"]

    entries.append(head)
    today = dt.combine(dt.now(), dt.min.time())
    if today == DB.get_latest_trading_day():
        latest_date = DB.get_latest_trading_day()
    else:
        latest_date = DB.get_previous_trading_day()

    #stocks = collection.find({"$and" : [{"dates.mysql_price_date": {"$eq": latest_date}}, {"General.IsDelisted": False}, {'General.Type':'Common Stock'}, {'General.Exchange':{"$in":major_exchanges}}]}).batch_size(10).sort([["failcount.mysql_price_failcount",1]]).allow_disk_use(True).sort([["sno",1]]).allow_disk_use(True)

    conditions = [\
                 #{"dates.mysql_price_date": {"$gte": DB.get_latest_trading_day()}},\
                 {"dates.mysql_price_pull_success": True},\
                 {'Highlights.MarketCapitalization':{'$gte':low_mcap, '$lt':high_mcap}},\
                 {'General.IsDelisted': False},\
                 {'General.Type':'Common Stock'},\
                 #{'General.Exchange':{"$in":major_exchanges}},\
                 {"$or": [\
                             {'General.Exchange':{"$in":major_exchanges}},\
                             {"$and": [ \
                                         {'General.Exchange':{"$nin":major_exchanges}},\
                                         {'bscs.tracking':{'$exists':True}}, \
                                     ] \
                             },\
                         ]\
                 },\
                 {"$or": [\
                            {'price_change.price_times_avg_vol_in_mn':{"$gte": 100}},\
                            {'price_change.avg_vol_pcent_in_mcap_mn':{"gte": 4}},\
                        ]\
                 },\
                ]

    if direction > 0 :
        conditions.append({rsi_change:{cond:change}})
        if limit > 0:
            stocks = collection.find({'$and':conditions}).limit(limit).sort([[rsi_change,-1]])
        else:
            stocks = collection.find({'$and':conditions}).sort([[rsi_change,-1]])
        #stocks = collection.find({'$and':[\
        #                                    #{"dates.mysql_price_date": {"$gte": DB.get_latest_trading_day()}},\
        #                                    {"dates.mysql_price_pull_success": True},\
        #                                    {'Highlights.MarketCapitalization':{'$gte':low_mcap, '$lt':high_mcap}},\
        #                                    {'General.IsDelisted': False},\
        #                                    {'General.Type':'Common Stock'},\
        #                                    #{'General.Exchange':{"$in":major_exchanges}},\
        #                                    {"$or": [\
        #                                                {'General.Exchange':{"$in":major_exchanges}},\
        #                                                {"$and": [ \
        #                                                            {'General.Exchange':{"$nin":major_exchanges}},\
        #                                                            {'bscs.tracking':{'$exists':True}}, \
        #                                                        ] \
        #                                                },\
        #                                            ]\
        #                                    },\
        #                                    {rsi_change:{cond:change}},\
        #                                    ]}).sort([[rsi_change,-1]])
    else:
        conditions.append({rsi_change:{'$lt':0}})
        if limit > 0:
            stocks = collection.find({'$and':conditions}).limit(limit).sort([[rsi_change,1]])
        else:
            stocks = collection.find({'$and':conditions}).sort([[rsi_change,1]])

        #stocks = collection.find({'$and':[\
        #                                    #{"dates.mysql_price_date": {"$gte": DB.get_latest_trading_day()}},\
        #                                    {"dates.mysql_price_pull_success": True},\
        #                                    {'Highlights.MarketCapitalization':{'$gte':low_mcap, '$lt':high_mcap}},\
        #                                    {'General.IsDelisted': False},\
        #                                    {'General.Type':'Common Stock'},\
        #                                    #{'General.Exchange':{"$in":major_exchanges}},\
        #                                    {"$or": [\
        #                                                {'General.Exchange':{"$in":major_exchanges}},\
        #                                                {"$and": [ \
        #                                                            {'General.Exchange':{"$nin":major_exchanges}},\
        #                                                            {'bscs.tracking':{'$exists':True}}, \
        #                                                        ] \
        #                                                },\
        #                                            ]\
        #                                    },\
        #                                    #{rsi_change:{'$gte':change}},\
        #                                    {rsi_change:{'$lte':0}},\
        #                                    ]}).sort([[rsi_change,1]]).limit(10)

    #query = {'$and': [{'bscs.mcap':{'$gte':low_mcap, '$lt':high_mcap}}, {price_change:{cond:change}}]}
    #stocks = db.US_Stocks.find(query).sort([[price_change,direction]])
    i = 0
    for stk in stocks:
        if limit > 0 and i > limit:
            break
        #if DB.ignore_stock(stk):
        #    continue

        bscs  = stk['bscs']
        pchg = stk['price_change']
        print("%r: %s: %s" %(stk['sno'], bscs['symbol'], stk['General']['Name']))
        entry = [ ]
        entry.append(bscs['symbol'])
        entry.append(stk['General']['Name'])
        entry.append(stk['General']['IPODate'])
        if stk['General']['GicSubIndustry']:
            entry.append(stk['General']['GicSubIndustry'])
        else:
            entry.append("")
        entry.append(str(round(stk['Highlights']['MarketCapitalizationMln']*factor, 2)))
        if 'price_change' in stk.keys() and \
            'volume' in stk['price_change'].keys():
            entry.append(str(round(stk['price_change']['volume']/1000, 2))+'k')
        else:
            entry.append("-")
        if 'price_change' in stk.keys() and \
            'avg_volume' in stk['price_change'].keys():
            #entry.append((str(round(((stk['price_change']['price']*stk['price_change']['avg_volume'])/(stk['Highlights']['MarketCapitalizationMln'] * 1000000))*100, 2))+'%'))
            entry.append(str(round((stk['price_change']['price']*stk['price_change']['volume'])/1000000, 2))+'Mn')
        else:
            entry.append("-")
        if 'price' in stk['price_change'].keys() and 'avg_volume' in stk['price_change'].keys():
            #entry.append(str(round((stk['price_change']['price']*stk['price_change']['avg_volume'])/1000000,2)))
            entry.append(str(round(((stk['price_change']['price']*stk['price_change']['avg_volume'])/(stk['Highlights']['MarketCapitalizationMln'] * 1000000))*100, 2))+'%')
        else:
            entry.append("")
        #try:
        if 'technicals' in stk.keys() and \
            'rsi' in stk['technicals'].keys() and \
            'latest' in stk['technicals']['rsi'].keys() and \
            stk['technicals']['rsi']['latest'] != None:
            entry.append(str(round(stk['technicals']['rsi']['latest'], 2)))
            min_diff = stk['technicals']['rsi']['latest'] - stk['technicals']['rsi']['60day_min']
            max_diff = stk['technicals']['rsi']['60day_max'] - stk['technicals']['rsi']['latest']
            entry.append(str(round(min_diff,2)))
            entry.append(str(round(max_diff,2)))
            entry.append(str(round(stk['technicals']['rsi']['cur_price_max_rsi_change']*100, 2))+'%')
        else:
            entry.append("-")
            entry.append("-")
            entry.append("-")
            entry.append("-")
       
        if 'technicals' in stk.keys() and \
            'sar' in stk['technicals'].keys() and \
            'ta_psar_trend_sequence' in stk['technicals']['sar'].keys() and \
            stk['technicals']['sar']['ta_psar_trend_sequence'] != None:
                entry.append(stk['technicals']['sar']['ta_psar_trend_sequence'])
        else:
            entry.append("")

        if 'technicals' in stk.keys() and \
            'sar' in stk['technicals'].keys() and \
            'ta_psar_cur_trend_price_change' in stk['technicals']['sar'].keys() and \
            stk['technicals']['sar']['ta_psar_cur_trend_price_change'] != None:
                entry.append(str(round(stk['technicals']['sar']['ta_psar_cur_trend_price_change']*100,2))+'%')
                entry.append(str(round(stk['technicals']['sar']['ta_psar_prev_trend_price_change']*100,2))+'%')
        else:
            entry.append("")
            entry.append("")

        if 'price_change' in stk.keys() and 'price' in stk['price_change'].keys():
            entry.append(str(stk['price_change']['price']))
        else:
            entry.append("")
        entry.append(pcent(pchg['day']))
        entry.append(pcent(pchg['week']))
        entry.append(pcent(pchg['month']))
        entry.append(pcent(pchg['quarter']))
        entry.append(pcent(pchg['half_year']))
        entry.append(pcent(pchg['year']))
 
        entries.append(entry)
        i = i + 1

    DB.close_db()
    return entries

def build_html_rsi(s, country, low_mcap, high_mcap, change, segment, uplimit=-1,downlimit=-1): 
    direction = 1
    highlight_column = 11
    s = parse_html.html_text(s, segment)
    e = get_stocks_rsi_change(country, low_mcap, high_mcap, direction, change, uplimit)
    if len(e) > 1:
        #entries = [["Up"]]
        entries = e
        s = parse_html.html_set_line(s)
        s = parse_html.html_text(s, entries, highlight_column)
    e = get_stocks_rsi_change(country, low_mcap, high_mcap, -direction, -change, downlimit)
    if len(e) > 1:
        #entries = [["Up"]]
        entries = e
        s = parse_html.html_set_line(s)
        s = parse_html.html_text(s, entries, highlight_column)

    return s

def get_rsi_changes(s, country):

    Mn = 1000000
    Bn = 1000*Mn
    Tn = 1000*Bn
    #s = parse_html.html_set_line(s)
    print("MCap 100 Bn and above")
    s = build_html_rsi(s, country, 100*Bn, 100*Tn, 0.10, ["MCap 100 Bn and above"])
    print("MCap 10 Bn and 100 Bn")
    s = build_html_rsi(s, country, 10*Bn, 100*Bn,  0.10, ["MCap 10 Bn and 100 Bn"], downlimit=20)
    print("MCap 5 Bn and 10 Bn")
    s = build_html_rsi(s, country, 5*Bn, 10*Bn,    0.15, ["MCap 5 Bn and 10 Bn"], downlimit=20)
    print("MCap 1 Bn and 5 Bn")
    s = build_html_rsi(s, country, 1*Bn, 5*Bn,     0.15, ["MCap 1 Bn and 5 Bn"], downlimit=20)
    print("MCap 500Mn and 1 Bn")
    s = build_html_rsi(s, country, 500*Mn, 1*Bn,   0.20, ["MCap 500Mn and 1 Bn"], uplimit=20, downlimit=20)
    print("MCap < 500 Mn")
    s = build_html_rsi(s, country, 1, 500*Mn,      0.30, ["MCap < 500 Mn"], uplimit=20, downlimit=20)
    return s

def send_email_rsi_changes(country):
    disconnect_vpn()

    s = parse_html.html_head()
    s = parse_html.html_text(s, ["RSI Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_rsi_changes(s, country)
    s = parse_html.html_set_line(s)
    subject='%s: RSI Snapshot: %r' %(country, str(datetime.datetime.now().date()))
    send_email2('petlafin@gmail.com', 'petlafin@gmail.com', subject, s)

# direction : -1 -> ascending order. Used for negative percent change
# direction : 1 -> descending order. Used for positive percent change.
def get_stocks_price_change(country, low_mcap, high_mcap, direction, change, duration):

    # duration can be "day", "week", "month", "quarter", "half_year", "year"
    price_change="price_change.%s"%(duration)
    # get all stocks with percentage greater than change in descending order
    if direction == -1:
        cond = '$lte'
    # get all stocks with percentage less than change in ascending order
    else:
        cond = '$gte'

    factor = 1
    if country == 'US':
        mcap = "MCap in Millions"
        #Convert to billions for mcap greater than 1 billion.
        if high_mcap > 1000:
            factor = 1/1000
            mcap = "Mcap Bn"
    elif country == 'India':
        mcap = "MCap in Crores"

    db = DB.open_db('Stocks')
    collection = DB.get_collection(country, db)
    
    entries = []
    if country == 'US':
        head=["Symbol", "Name", "Since", "Sectr", mcap, "Vol", "Price", "6M Beta", "52Wk Hgh", "52Wk Lw", "Day Chg", "Wk Chg", "Mth Chg", "Qrtr Chg", "Hf Yr Chg", "Yr Chg", "With 52Wk Hgh", "With 52Wk Lw", "With Alltime Hgh", "PSAR Trend"]
    else:
        head=["Symbol", "Name", "Since", "Sectr", mcap, "Vol", "Price", "6M Beta", "52Wk Hgh", "52Wk Lw", "Day Chg", "Wk Chg", "Mth Chg", "Qrtr Chg", "Hf Yr Chg", "Yr Chg", "With 52Wk Hgh", "With 52Wk Lw"]

    entries.append(head)
    today = dt.combine(dt.now(), dt.min.time())
    if today == DB.get_latest_trading_day():
        latest_date = DB.get_latest_trading_day()
    else:
        latest_date = DB.get_previous_trading_day()

    #stocks = collection.find({"$and" : [{"dates.mysql_price_date": {"$eq": latest_date}}, {"General.IsDelisted": False}, {'General.Type':'Common Stock'}, {'General.Exchange':{"$in":major_exchanges}}]}).batch_size(10).sort([["failcount.mysql_price_failcount",1]]).allow_disk_use(True).sort([["sno",1]]).allow_disk_use(True)

    stocks = collection.find({'$and':[\
                                        {"dates.mysql_price_date": {"$gte": DB.get_latest_trading_day()}},\
                                        {"dates.mysql_price_pull_success": True},\
                                        {'Highlights.MarketCapitalization':{'$gte':low_mcap, '$lt':high_mcap}},\
                                        {'General.IsDelisted': False},\
                                        {'General.Type':'Common Stock'},\
                                        #{'General.Exchange':{"$in":major_exchanges}},\
                                        {"$or": [\
                                                    {'General.Exchange':{"$in":major_exchanges}},\
                                                    {"$and": [ \
                                                                {'General.Exchange':{"$nin":major_exchanges}},\
                                                                {'bscs.tracking':{'$exists':True}}, \
                                                            ] \
                                                    },\
                                                ]\
                                        },\
                                        {price_change:{cond:change}},\
                                        ]}).sort([[price_change,-direction]])
    #query = {'$and': [{'bscs.mcap':{'$gte':low_mcap, '$lt':high_mcap}}, {price_change:{cond:change}}]}
    #stocks = db.US_Stocks.find(query).sort([[price_change,direction]])
    for stk in stocks:
        #if DB.ignore_stock(stk):
        #    continue

        bscs  = stk['bscs']
        pchg = stk['price_change']
        print("%r: %s: %s" %(stk['sno'], bscs['symbol'], stk['General']['Name']))
        entry = [ ]
        entry.append(bscs['symbol'])
        entry.append(stk['General']['Name'])
        entry.append(stk['General']['IPODate'])
        if stk['General']['GicSubIndustry']:
            entry.append(stk['General']['GicSubIndustry'])
        else:
            entry.append("")
        entry.append(str(round(stk['Highlights']['MarketCapitalizationMln']*factor, 2)))
        if 'volume' in bscs.keys():
            entry.append(str(round(bscs['volume']/1000, 2))+'k')
        else:
            entry.append("-")
        if 'price' in stk['price_change'].keys():
            entry.append(str(stk['price_change']['price']))
        else:
            entry.append("")
        #try:
        if 'fig' in stk.keys() and \
            'betas' in stk['fig'].keys() and \
            'six_months' in stk['fig']['betas'].keys() and \
            stk['fig']['betas']['six_months'] != None:
            entry.append(str(round(stk['fig']['betas']['six_months']['beta'], 2)))
        else:
            entry.append("-")
        #entry.append(str(round(stk['fig']['betas']['six_months']['beta'], 2)))
        #except Exception as e:
        #    collection.update({'bscs.symbol': bscs['symbol']}, {'$set': {"fig.betas.six_months.beta": 0}})
        #    entry.append(str("None"))
        if 'fiftytwoweek_high' in bscs.keys() and bscs['fiftytwoweek_high'] is not None:
            entry.append(str(round(bscs['fiftytwoweek_high'], 2)))
        else:
            entry.append("")
        if 'fiftytwoweek_low' in bscs.keys() and bscs['fiftytwoweek_low'] is not None:
            entry.append(str(round(bscs['fiftytwoweek_low'], 2)))
        else:
            entry.append("")
        entry.append(pcent(pchg['day']))
        entry.append(pcent(pchg['week']))
        entry.append(pcent(pchg['month']))
        entry.append(pcent(pchg['quarter']))
        entry.append(pcent(pchg['half_year']))
        entry.append(pcent(pchg['year']))
        if 'with_52week_high' in pchg.keys():
            entry.append(pcent(pchg['with_52week_high']))
        else:
            entry.append("")
        if 'with_52week_low' in pchg.keys():
            entry.append(pcent(pchg['with_52week_low']))
        else:
            entry.append("")
        if 'with_all_time_high' in pchg.keys():
            entry.append(pcent(pchg['with_all_time_high']))
        else:
            entry.append("")
        
        if 'technicals' in stk.keys() and 'sar' in stk['technicals'].keys() and 'ta_psar_trend_sequence' in stk['technicals']['sar'].keys():
            entry.append(stk['technicals']['sar']['ta_psar_trend_sequence'])
        else:
            entry.append("-")
        entries.append(entry)

    DB.close_db()
    return entries

def build_html_price_change(s, country, low_mcap, high_mcap, direction, change, duration, segment): 
    e = get_stocks_price_change(country, low_mcap, high_mcap, direction, change, duration)
    s = parse_html.html_text(s, segment)
    if len(e) > 1:
        #entries = [["Up"]]
        entries = e
        s = parse_html.html_set_line(s)
        s = parse_html.html_text(s, ["Up"])
        s = parse_html.html_set_line(s)
        s = parse_html.html_text(s, entries, highlight_columns[duration])
        s = parse_html.html_set_line(s)
    e = get_stocks_price_change(country, low_mcap, high_mcap, -direction, -change, duration)
    if len(e) > 1:
        #entries = [["Down"]]
        entries = e
        s = parse_html.html_text(s, ["Down"])
        s = parse_html.html_set_line(s)
        s = parse_html.html_text(s, entries, highlight_columns[duration])
        s = parse_html.html_set_line(s)
    return s

def get_price_changes(s, country, duration):

    if country == 'US':
        Mn = 1000000
        Bn = 1000*Mn
        Tn = 1000*Bn
        #s = parse_html.html_set_line(s)
        s = build_html_price_change(s, 'US', 100*Bn, 100*Tn, 1, pcent_chg[duration][0], duration, ["MCap 100 Bn and above"])
        s = build_html_price_change(s, 'US', 10*Bn, 100*Bn,  1, pcent_chg[duration][1], duration, ["MCap 10 Bn and 100 Bn"])
        s = build_html_price_change(s, 'US', 5*Bn, 10*Bn,    1, pcent_chg[duration][2], duration, ["MCap 5 Bn and 10 Bn"])
        #s = build_html_price_change(s, 'US', 1*Bn, 5*Bn,     1, pcent_chg[duration][3], duration, ["MCap 1 Bn and 5 Bn"])
        #s = build_html_price_change(s, 'US', 500*Mn, 1*Bn,   1, pcent_chg[duration][4], duration, ["MCap 500Mn and 1 Bn"])
        #s = build_html_price_change(s, 'US', 1, 500*Mn,      1, pcent_chg[duration][4], duration, ["MCap < 500 Mn"])
    elif country == 'India':
        Bn = 100 # crores
        Tn = 100 * Bn
        #s = parse_html.html_set_line(s)
        s = build_html_price_change(s, 'India', 100*Bn, 10*Tn, 1, pcent_chg[duration][0], duration, ["MCap 10k Cr and above"])
        s = build_html_price_change(s, 'India', 10*Bn, 100*Bn, 1, pcent_chg[duration][1], duration, ["MCap 1k Cr and 10k Cr"])
        s = build_html_price_change(s, 'India', 5*Bn, 10*Bn,   1, pcent_chg[duration][2], duration, ["MCap 500 Cr and 1k Cr"])
        s = build_html_price_change(s, 'India', 1*Bn, 5*Bn,    1, pcent_chg[duration][3], duration, ["MCap 100 Cr and 500 Cr"])
        s = build_html_price_change(s, 'India', 1, 1*Bn,       1, pcent_chg[duration][4], duration, ["MCap < 100 Cr"])
    return s

def send_email_price_changes(country):
    disconnect_vpn()

    s = parse_html.html_head()
    s = parse_html.html_text(s, ["Daily Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_price_changes(s, country, 'day')
    #subject='%s: Price Surprises: %r' %(country, str(datetime.datetime.now().date()))
    #send_email2('petlafin@gmail.com', 'shravanrdstocks@gmail.com', subject, s)
    s = parse_html.html_text(s, ["Weekly Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_price_changes(s, country, 'week')
    s = parse_html.html_text(s, ["Monthly Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_price_changes(s, country, 'month')
    s = parse_html.html_text(s, ["Quarterly Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_price_changes(s, country, 'quarter')
    s = parse_html.html_text(s, ["Half Yearly Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_price_changes(s, country, 'half_year')
    s = parse_html.html_text(s, ["Yearly Price Snapshot"])
    s = parse_html.html_set_line(s)
    s = get_price_changes(s, country, 'year')
    #f = open("/tmp/test.html","w")
    #f.write(s)
    #f.close()
    subject='%s: Price Snapshot: %r' %(country, str(datetime.datetime.now().date()))
    send_email2('petlafin@gmail.com', 'petlafin@gmail.com', subject, s)

def price_surprises(country, change_percent, criteria, db_type, excel_type):
    print("Criteria: %r" %(criteria))
    if excel_type == 'EXCEL':
        xl = xlwt.Workbook()

        yr_sheet = xl.add_sheet("365 days change")
        qr_sheet = xl.add_sheet("90 days change")
        mon_sheet = xl.add_sheet("30 days change")
        week_sheet = xl.add_sheet("7 days change")
        day_sheet = xl.add_sheet("one day change")

        excel.add_price_surprise_header(yr_sheet, 'YEAR')
        excel.add_price_surprise_header(qr_sheet, 'QUARTER')
        excel.add_price_surprise_header(mon_sheet, 'MONTH')
        excel.add_price_surprise_header(week_sheet, 'WEEK')
        excel.add_price_surprise_header(day_sheet, 'DAY')
    else:
        xl = None

    db = DB.open_db('Stocks')
    if country == 'US':
        today = dt.combine(dt.now(), dt.min.time())

        col = db['US_Stocks']
        #for doc in col.find({"bscs.industry":"Accident &Health Insurance"}):
        #for doc in col.find({}):
        #docs = col.find({"bscs.symbol":"HEXO"}).sort([["sno",1]])
        #docs = col.find({}, no_cursor_timeout=True).batch_size(10).sort([["sno",1]])
        if today == get_latest_trading_day():
            latest_date = get_latest_trading_day()
        else:
            latest_date = get_previous_trading_day()

        docs = col.find({"$and" : [\
                                        {"dates.mysql_price_date": {"$eq": latest_date}},\
                                        {"General.IsDelisted": False},\
                                        {'General.Type':'Common Stock'}, \
                                        #{'General.Exchange':{"$in":major_exchanges}}
                                        {"$or": [\
                                                    {'General.Exchange':{"$in":major_exchanges}},\
                                                    {"$and": [ \
                                                                {'General.Exchange':{"$nin":major_exchanges}},\
                                                                {'bscs.tracking':{'$exists':True}}, \
                                                            ] \
                                                    },\
                                                ]\
                                        }\
                                    ]
                        }
                        ).batch_size(10).sort([["failcount.mysql_price_failcount",1]]).allow_disk_use(True).sort([["sno",1]]).allow_disk_use(True)
        print("Count: %r" %(docs.count()))
        i=0
        len_skip= dcf_skip = price_skip = trading_skip = vol_skip = 0
        for doc in docs:
            #last_sno = int(read_from_file("price_surprise.txt"))
            sno = doc['sno']
            #if last_sno == docs.count():
            #    return
            #if sno < last_sno:
            #    print("Skipping %r: %r" %(sno, doc['bscs']['symbol']))
            #    continue
 
            #if sno > 2211:
            if sno > 0:
                doc['id'] = doc.pop('_id')
                #stock = DB.dbObject(**doc)
                stock = doc
                sym = doc['bscs']['symbol']
                name = doc['bscs']['name']
                #print("%r : "%(i), end = '')

                list=doc['num']
                if len(list) == 0:
                    len_skip+=1
                    continue
                if "dcf_years" not in list.keys():
                    dcf_skip+=1
                    continue
                if stock['bscs']['price'] < 1:
                    price_skip+=1
                    continue
                if stock['bscs']['trading'] != 'YES':
                    trading_skip+=1
                    continue
                if stock['bscs']['volume'] < 40000:
                    vol_skip+=1
                    continue
                i+=1
                print("%d: %d: %s: %s" %(i, sno, sym, name))
                if type(stock['num']['eps_20yr']) is int:
                    stock['num']['eps_20yr']=[]
                price_suprise(country, col, stock, sym, name, change_percent, xl, criteria, db_type, excel_type)
                #write_to_file(str(doc['sno']), "price_surprise.txt", "w")

        print("len_skip: %r, dcf_skip = %d, price_skip = %d, trading_skip = %d, vol_skip = %d" %(len_skip, dcf_skip, price_skip, trading_skip, vol_skip))
    elif country == 'India':
        col = db['India_Stocks']
        for doc in col.find({}):
            if i > -1:
                doc['id'] = doc.pop('_id')
                #stock = dbObject(**doc)
                stock = doc
                sym = doc['bscs']['symbol']
                name = doc['bscs']['name']
                sym = sym + '.BO'
                price_suprise(country, col, stock, sym, name, change_percent, xl, criteria, db_type, excel_type)
            i += 1
    
    #now = datetime.datetime.now().date()
    now = datetime.datetime.now()
    if excel_type == 'EXCEL':
        excel_file = "US_Stocks/DCF_Calc/price_surprises_%s.xls" % (str(now))
        #xl.save("US_Stocks/DCF_Calc/price_surprises.xls")
        xl.save(excel_file)


def get_price_volume(stk, country, vpn_event=None):
    #data = pdr.get_data_yahoo(symbols=stk['bscs']['symbol'], start=dt(2019,4,15), end=dt(2019,4,18))
    #stk['bscs']['price']  = round(float(data.iat[-1, data.columns.get_loc('Adj Close')]), 2)
    #vol = data[['Volume']]
    #sum = 0        
    #for v in vol.values.tolist():
    #    sum += v[0]
    #stk['bscs']['volume'] = sum / len(vol.values.tolist())
    
    ##data.get_quote_yahoo(stocklist).to_csv('test.csv', index=False, quoting=csv.QUOTE_NONNUMERIC)

    symbol = stk['bscs']['symbol'].replace('.','-')

    retries = 1
    conn_retries = 0
    while True:
        try:
            if vpn_event and vpn_event.is_set() is False:
                print("**** %s: Waiting..  VPN is changing" %(symbol))
                vpn_event.wait()
                print("**** %s: Waking up" %(symbol))

            # India no longer supported
            if country == 'India':
                #d = data.get_quote_yahoo(symbol+'.BO')
                return
            elif country == 'US':
                tick = yfinance.Ticker(symbol)
                proxy_server = get_proxy()
                info = tick.get_info(proxy=proxy_server)
                #d = data.get_quote_yahoo(symbol)
            else:
                PRINT_ERR("Unknown Country Name")
                return None
        except requests.exceptions.ProxyError as E:
            PRINT_ERR("internet.py: %s:  Proxy Error, retrying" %(symbol))
            delete_proxy_server(proxy_server)
            retries = retries + 1
            continue
        except (KeyError, pdr._utils.RemoteDataError, IndexError) as E:
            PRINT_ERR("internet.py: %s:  Error, retrying, exception: %r" %(symbol, E))
            if vpn_event:
                if retries  > 1:
                    PRINT_ERR("Unable to get price and volume for %s"%(stk['bscs']['symbol']))
                    DB.update_price_failcount(stk, country)
                    return None
                if vpn_event.is_set() is False:
                    print("**** %s: 2DF: Waiting..  VPN is changing" %(symbol))
                    vpn_event.wait()
                    print("**** %s: 2DF: Waking up" %(symbol))
                    continue
                else: 
                    time.sleep(5)
                    vpn_event.clear()
                    print("**** %s: VPN Changing: Sent Wait Event" %(symbol))
                    change_vpn()
                    vpn_event.set()
                    print("**** %s: VPN Changed: Sending Wakeup Event" %(symbol))
                    retries = retries + 1
                    continue
            else:
                if retries  > 1:
                    PRINT_ERR("Unable to get price and volume for %s"%(stk['bscs']['symbol']))
                    DB.update_price_failcount(stk, country)
                    return None
                retries = retries + 1
                time.sleep(2)
                continue
        #except (urllib3.exceptions.NewConnectionError, OpenSSL.SSL.SysCallError) as E:
        except Exception as E:
            if conn_retries > 1:
                PRINT_ERR("Unable to get price and volume for %s"%(stk['bscs']['symbol']))
                return None
            PRINT_ERR("%s: internet.py: Connection Error, retrying, Exception: %r" %(symbol, E))
            time.sleep(1)
            conn_retries = conn_retries + 1
            continue
 
        stk['bscs'] = info

        ##except pdr._utils.RemoteDataError:
        ##    PRINT_ERR("Unable to get data for %s: %s"%(stk['bscs']['name'], stk['bscs']['symbol']))
        ##    if retries  > 5:
        ##        return None
        ##    change_vpn()
        ##    retries = retries + 1
        ##    continue
        ##except IndexError:
        ##    PRINT_ERR("Unable to get price and volume for %s"%(stk['bscs']['symbol']))
        ##    if retries  > 5:
        ##        return None
        ##    change_vpn()
        ##    retries = retries + 1
        ##    continue
        #try:

        #    # Add moving average etc. Refer /tmp/test.csv for details
        #    stk['bscs']['type'] = info['quoteType']
        #    stk['bscs']['exchange'] = info['exchange']

        #    if 'averageVolume' in info.keys():
        #        stk['bscs']['avgerageVolume'] = info['averageVolume']
        #    else:
        #        stk['bscs']['averageVolume'] = 0

        #    stk['bscs']['price']  = info['regularMarketPrice']

        #    if 'regularMarketVolume' in info.keys():
        #        stk['bscs']['volume'] = info['volume']
        #    elif 'volume' in info.keys():
        #        stk['bscs']['volume'] = info['volume']
        #    else:
        #        stk['bscs']['volume'] = 0
 
        #    if 'marketCap' in info.keys():
        #        if country == 'India':
        #            stk['bscs']['mcap']   = float(info['marketCap'][0])/10000000 # in crores
        #        else:
        #            stk['bscs']['mcap']   = float(info['marketCap'][0])/1000000 # in millions
        #    else:
        #        stk['bscs']['mcap'] = 0

        #    if 'industry' not in stk['bscs'].keys():
        #        stk['bscs']['industry'] = info['industry']
        #    if 'sector' not in stk['bscs'].keys():
        #        stk['bscs']['sector'] = info['sector']

        #    if 'sharesOutstanding' in info.keys():
        #        stk['bscs']['outstanding_shares'] = info['sharesOutstanding']
        #    else:
        #        stk['bscs']['outstanding_shares'] = 0
        #    if 'shortRatio' in info.keys():
        #        stk['bscs']['shortRatio'] = info['shortRatio']

        #    if 'longName' in info.keys():
        #        stk['bscs']['Name'] = info['longName']
        #    elif 'shortName' in info.keys():
        #        stk['bscs']['Name'] = info['shortName']

        #    #if 'fullExchangeName' in d.keys():
        #    #    stk['bscs']['exchange_name'] = d.iloc[0]['fullExchangeName']

        #except AttributeError as e:
        #    if 'outstanding_shares' not in stk['bscs'].keys():
        #        stk['bscs']['outstanding_shares'] = 0
        #    if 'volume' not in stk['bscs'].keys():
        #        stk['bscs']['volume'] = 0
        #    PRINT_ERR(str(e))
        #    PRINT_ERR("Couldn't get a particular field for %s" %(stk['bscs']['symbol']))
        #break
    print("internet: %s: %s"%(stk['bscs']['symbol'],stk['bscs']['name']))
    return stk

def get_price_growth(country, stk, years, data_type):
    if stk['bscs']['price_years'] != 5 and stk['bscs']['price_years'] != 10 and stk['bscs']['price_years'] != 0:
        yrs = int(stk['bscs']['price_years'])
    else:
        yrs = years

    if data_type == 'HOT':
        end = dt.today()
        st = dt(end.year-years, end.month, end.day)
        print("start: %s, end: %s" %(st.date(), end.date()))
        try:
            data = pdr.DataReader(stk['bscs']['symbol'], 'yahoo', st, end)
        except pdr._utils.RemoteDataError:
            PRINT_ERR("Unable to get data for %s: %s"%(stk['bscs']['name'], stk['bscs']['symbol']))
            stk['bscs']['hist_price_5'] = 1
            stk['bscs']['hist_price_10'] = 1
            return 0

        st_price = data.iat[0, data.columns.get_loc('Close')]
        en_price = data.iat[-1, data.columns.get_loc('Close')]
        yrs = end.year - int(str(list(data.index)[0]).split('-')[0])
        print("yrs: %d, %s" %(yrs, str(list(data.index)[0]).split('-')[0]))
        del data
        if years <= 5:
            stk['bscs']['hist_price_5'] = st_price
        else:
            end = dt.today()
            st = dt(end.year-5, end.month, end.day)
            data = pdr.DataReader(stk['bscs']['symbol'], 'yahoo', st, end)
            stk['bscs']['hist_price_5'] = data.iat[0, data.columns.get_loc('Close')]
            del data
 
        if years > 5 and years <= 10:
            stk['bscs']['hist_price_10'] = st_price
        else:
            end = dt.today()
            st = dt(end.year-10, end.month, end.day)
            data = pdr.DataReader(stk['bscs']['symbol'], 'yahoo', st, end)
            stk['bscs']['hist_price_10'] = data.iat[0, data.columns.get_loc('Close')]
            del data

        db = DB.open_db('Stocks')
        if country  == 'US':
            collection = db['US_Stocks']
        elif country == 'India':
            collection = db['India_Stocks']
        else:
            raise exception("Unknown Country Name %s" %(country))

        DB.update_field(collection, stk['bscs']['symbol'], "bscs.hist_price_5", stk['bscs']['hist_price_5'])
        DB.update_field(collection, stk['bscs']['symbol'], "bscs.hist_price_10", stk['bscs']['hist_price_10'])
        DB.update_field(collection, stk['bscs']['symbol'], "bscs.price", round(en_price,2))
        DB.update_field(collection, stk['bscs']['symbol'], "bscs.price_years", yrs)
 
    if yrs < 1:
        yrs = 1
    if years == 10:
        st_price = stk['bscs']['hist_price_10']
    else:
        st_price = stk['bscs']['hist_price_5']

    en_price = stk['bscs']['price']
    #    st_price = round(float(st_price.real),2)
    #if isinstance(en_price, complex):
    #    en_price = rount(float(en_price.real),2)
    years = yrs
    try:
        growth = round(((en_price/st_price)**(1/years)-1), 2)
    except ZeroDivisionError:
        growth = 0
    PRINT("years: %d, growth: %r" %(years, growth))
    return growth

# Get stock split information
def get_stock_split_info_yahoo(country, stk):
    if country == 'India':
        sym = stk['bscs']['symbol'] + '.BO'
    elif country != 'US':
        PRINT_ERR("Unknown Country")
        return
    #get split info from Yahoo Finance
    data = yf(sym).get_key_statistics_data()
    stk['bscs']['split_factor'] = float(Fraction(data[sym]['lastSplitFactor']))
    d = data[sym]['lastSplitDate']
    stk['bscs']['split_date'] = d
    stk['bscs']['split_year'] = datetime.datetime.strptime(d, '%Y-%m-%d').year 

def get_LTP(country, sym):
    sym1 = sym
    if country == 'India':
        sym = sym + '.BO'
    elif country != 'US':
        PRINT_ERR("Unknown Country")
        return 0
    #price = yf(sym).get_current_price()
    price = data.get_quote_yahoo(sym).iloc[0]['price']
    if not price and '.' in sym:
        sym = sym.replace('.', '-')
        price = yf(sym).get_current_price()
    if not price:
        PRINT_ERR("Unable to get latest price for %s" %(sym))
        PRINT_ERR("Updating stock to not trading")
        db = DB.open_db('Stocks')
        if country == 'US':
            col = db.US_Stocks
        elif country == 'India':
            col = db.India_Stocks
        else:
            return 0
        #DB.update_field(col, sym1, "bscs.trading", "NO")
        return 0
    return price

def get_page(url, html_file):
    headers={"User-Agent":"Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:69.0) Gecko/20100101 Firefox/69.0"}
    html=requests.get(url, headers=headers)
    if html.status_code == 200:
        write_to_file(html.text, html_file)
    else:
        PRINT_ERR("Couldnt get page : %s" %(url))

def get_webpage(url):
    headers={"User-Agent":"Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:69.0) Gecko/20100101 Firefox/69.0"}
    return requests.get(url, headers=headers).text

def browse_US_stock_page(stock, url):
    driver = webdriver.Firefox()
    driver.set_page_load_timeout(30)
    driver.get(url)
    old_url = driver.current_url
    driver.maximize_window()

    time.sleep(5)
    elem = driver.find_element_by_class_name("panclose5084")
    if elem:
        elem.click()
    return
    elem = driver.find_element_by_class_name("bc-button white-button small settings-button")
    elem.click()
    time.sleep(20)
    driver.close()

        # attr=driver.find_element_by_name('txtStock').get_attribute('innerHTML')
        # attr=elem.get_attribute('innerHTML')
    # elem.send_keys(stock, Keys.ARROW_DOWN)
    # time.sleep(2)
    # elem.send_keys(Keys.RETURN)

"""
    # time.sleep(20)
    try:
        WebDriverWait(driver, 20).until(EC.presence_of_element_located(
            (By.ID, 'lblCompany')))
    except TimeoutException:
        PRINT("Unable to parse %r" % (stock))
        f = open("unparsed_stocks3.txt", "a")
        f.write(stock)
        f.write("\n")
        f.close()
        return
    # PRINT_DBG(str(html_src))

    if driver.current_url == old_url:
        PRINT("Unable to parse %r" % (stock))
        f = open("unparsed_stocks3.txt", "a")
        f.write(stock)
        f.write("\n")
        f.close()
    else:
        # PRINT_DBG("Found stock %r" %(stock))
        html_src = driver.page_source
        html_file = "html_pages/%s.html" % (stock_name)
        f = open(html_file, "w")
        f.write(html_src)
        f.close()
"""
    #    try:
    #        element = WebDriverWait(driver, 100).until(EC.title_contains((By.ID, stock)))
    #        element = WebDriverWait(driver, 100).until(EC.presence_of_element_located((By.ID, 'IdOfMyElement')))
    #        element = WebDriverWait(driver, 100).until(EC.presence_of_element_located((By.TAG_NAME, 'body')))
    #        html_file = "html_pages/%s.html" %(stock)
    #        f = open(html_file, "w")
    #        f.write(html_src)
    #        f.close()
    #    finally:
    #        PRINT_DBG("Unable to parse %r" %(stock))
    #        f = open("unparsed_stocks.txt", "a")
    #        f.write(stock)
    #        f.write("\n")
    #        f.close()

    #driver.close()

    # try:
    #    element = WebDriverWait(driver, 100).until(EC.title_contains((By.ID, stock)))
    # finally:
    #    driver.close()

    # assert "No results found." not in driver.page_source
    # time.sleep(5)

def get_US_earnings_estimates(symbol, name):
    driver = webdriver.Firefox()
    driver.set_page_load_timeout(30)

    url = "https://www.barchart.com/stocks/quotes/%s/earnings-estimates" %(symbol)
    
    driver.get(url)
    old_url = driver.current_url
    driver.maximize_window()

    html_file = "%s/%s_earnings_estimates.html" %(path, symbol)
    get_page(url, html_file)
    return 

def update_DB_US_earnings_estimates(stk, earnings, fiftytwoweek_high, fiftytwoweek_low):
    db = DB.open_db('Stocks')
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "bscs.fiftytwoweek_high", fiftytwoweek_high)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "bscs.fiftytwoweek_low", fiftytwoweek_low)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.date", earnings.date)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.high_target", earnings.high_target)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.mean_target", earnings.mean_target)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.low_target", earnings.low_target)

    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.hist.quarters", earnings.hist.quarters)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.hist.reported", earnings.hist.reported)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.hist.estimate", earnings.hist.estimate)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.hist.difference", earnings.hist.difference)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.hist.surprise", earnings.hist.surprise)

    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.quarters", earnings.est.quarters)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.years", earnings.est.years)

    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.q_avg_est", earnings.est.q_avg_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.q_num_est", earnings.est.q_num_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.q_high_est", earnings.est.q_high_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.q_low_est", earnings.est.q_low_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.q_prior_yr", earnings.est.q_prior_yr)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.q_gr_rate", earnings.est.q_gr_rate)

    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.y_avg_est", earnings.est.y_avg_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.y_num_est", earnings.est.y_num_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.y_high_est", earnings.est.y_high_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.y_low_est", earnings.est.y_low_est)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.y_prior_yr", earnings.est.y_prior_yr)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "quart_fig.Earning_Estimates.est.y_gr_rate", earnings.est.y_gr_rate)

def click_sym(symbol, elem):
    print(len(elem))
    
def populate_US_earnings_estimates(stk):
    earnings = Earnings()

    url = "https://www.barchart.com/stocks/quotes/%s/earnings-estimates" %(stk['bscs']['symbol'])
    br = open_browser('headless')
    try:
        br.get(url)
    except Exception:
        print("%s: %s webpage loading timeout, trying again" %(stk['bscs']['symbol'], stk['bscs']['name']))
        close_browser(br)
        time.sleep(5)
        br = open_browser()
        br.get(url)

    #time.sleep(5)
    #ad popup
    try:
        e = br.find_element_by_xpath("/html/body/div[8]/div[2]/div[1]")
        e.click()
    except Exception:
        PRINT("No popup")

    try:
        page = br.page_source
    except Exception as e:
        print("%s: %s page source exception trying again, err: %s" %(stk['bscs']['symbol'], stk['bscs']['name'], str(e)))
        time.sleep(5)
        br.get(url)
        page = br.page_source

    soup = parse_html.get_soup(page)

    if soup.find("title").text.lstrip().rstrip() == 'Page not found':
        PRINT_ERR("%s:%s Invalid page, skipping" %(stk['bscs']['symbol'], stk['bscs']['name']))
        update_DB_US_earnings_estimates(stk, earnings, stk['bscs']['price'], stk['bscs']['price'])
        close_browser(br)
        return

    msg = ' Earnings are not available for %s.  ' %(stk['bscs']['symbol'])
    pattern = re.compile(r'%s'%msg)
    div = soup.find(text=pattern)
    if div:
        PRINT_ERR("%s:%s does not have earnings estimates, skipping" %(stk['bscs']['symbol'], stk['bscs']['name']))
        update_DB_US_earnings_estimates(stk, earnings, stk['bscs']['price'], stk['bscs']['price'])
        close_browser(br)
        return
    
    l=soup.find(text=re.compile('^HIGH TARGET '))
    earnings.high_target = str_to_float(l.parent.text.split(' ')[-1])
    l=soup.find(text=re.compile('^LOW TARGET '))
    earnings.low_target = str_to_float(l.parent.text.split(' ')[-1])
    l=soup.find(text=re.compile('^MEAN TARGET '))
    earnings.mean_target = str_to_float(l.parent.text.split(' ')[-1])

    l=soup.find(text=re.compile('^52 WK High '))
    fiftytwoweek_high = str_to_float(l.parent.text.split(' ')[-1])
    l=soup.find(text=re.compile('^52 WK Low '))
    fiftytwoweek_low = str_to_float(l.parent.text.split(' ')[-1])


    l = soup.find("span", {"class": "last-change"})
    #l = soup.find("span", {"class": "last-change ng-binding"})
    DB.update_field(DB.open_db('Stocks').US_Stocks, stk['bscs']['symbol'], "bscs.price", str_to_float(l.text))

    l=soup.find_all(text=re.compile('^Qtr Ending'))
    for entry in l:
        earnings.hist.quarters.append(entry.parent.parent.text.split(' ')[3])

    l=soup.find(text=re.compile('^Reported'))
    entries=l.parent.parent.parent.text.split(' ')
    for entry in entries[5:-1]:
        if entry != '':
            earnings.hist.reported.append(str_to_float(entry))

    l=soup.find(text=re.compile('^Estimate'))
    entries=l.parent.parent.parent.text.split(' ')
    for entry in entries[5:-1]:
        if entry != '':
            earnings.hist.estimate.append(str_to_float(entry))

    for i in range(len(earnings.hist.estimate)):
        est = earnings.hist.estimate[i]
        rep = earnings.hist.reported[i]
        try:
            earnings.hist.difference.append(round(rep-est,3))
        except Exception as e:
            earnings.hist.difference.append(float('NaN'))
        try:
            earnings.hist.surprise.append(round(((rep - est)/ est), 4))
        except Exception as e:
            earnings.hist.surprise.append(float('NaN'))

    l=soup.find(text=re.compile('^Current Qtr'))
    earnings.est.quarters.append(l.parent.parent.text.split(' ')[-2])
    l=soup.find(text=re.compile('^Next Qtr'))
    earnings.est.quarters.append(l.parent.parent.text.split(' ')[-2])
    l=soup.find_all(text=re.compile('^Fiscal Yr'))
    earnings.est.years.append(l[0].parent.parent.text.split(' ')[-2])
    earnings.est.years.append(l[1].parent.parent.text.split(' ')[-2])

    l=soup.find(text=re.compile('^Average Estimate'))
    entries=l.parent.parent.parent.text.split(' ')
    count=0
    for entry in entries[5:-1]:
        if entry != '':
            if count < 2:
                earnings.est.q_avg_est.append(str_to_float(entry))
            else:
                earnings.est.y_avg_est.append(str_to_float(entry))
            count += 1

    l=soup.find(text=re.compile('^Number of Estimates'))
    entries=l.parent.parent.parent.text.split(' ')
    count=0
    for entry in entries[5:-1]:
        if entry != '':
            if count < 2:
                earnings.est.q_num_est.append(str_to_float(entry))
            else:
                earnings.est.y_num_est.append(str_to_float(entry))
            count += 1

    l=soup.find(text=re.compile('^High Estimate'))
    entries=l.parent.parent.parent.text.split(' ')
    count=0
    for entry in entries[5:-1]:
        if entry != '':
            if count < 2:
                earnings.est.q_high_est.append(str_to_float(entry))
            else:
                earnings.est.y_high_est.append(str_to_float(entry))
            count += 1

    l=soup.find(text=re.compile('^Low Estimate'))
    entries=l.parent.parent.parent.text.split(' ')
    count=0
    for entry in entries[5:-1]:
        if entry != '':
            if count < 2:
                earnings.est.q_low_est.append(str_to_float(entry))
            else:
                earnings.est.y_low_est.append(str_to_float(entry))
            count += 1

    l=soup.find(text=re.compile('^Prior Year'))
    entries=l.parent.parent.parent.text.split(' ')
    count=0
    for entry in entries[5:-1]:
        if entry != '':
            if count < 2:
                earnings.est.q_prior_yr.append(str_to_float(entry))
            else:
                earnings.est.y_prior_yr.append(str_to_float(entry))
            count += 1

    l=soup.find(text=re.compile('^Growth Rate Est'))
    entries=l.parent.parent.parent.text.split(' ')
    count=0
    for entry in entries[14:-1]:
        if entry != '':
            if entry == 'unch':
                entry = '0'
            if count < 2:
                earnings.est.q_gr_rate.append(round(str_to_float(entry)/100,4))
            else:
                earnings.est.y_gr_rate.append(round(str_to_float(entry)/100,4))
            count += 1

    earnings.date=str(dt.now().date())
    earnings.hist.quarters.reverse()
    earnings.hist.reported.reverse()
    earnings.hist.estimate.reverse()
    earnings.hist.difference.reverse()
    earnings.hist.surprise.reverse()

    #elem = br.find_element_by_name("search")
    #elem.send_keys("FIVE")
    ##time.sleep(10)
    ##page = br.page_source
    ##f=open("/tmp/five.html", "w")
    ##f.write(soup.prettify())
    ##f.close()
    #opts = WebDriverWait(br, 10).until(EC.presence_of_element_located(
    #        (By.CLASS_NAME, 'quick-search')))
    ##elem = br.find_element_by_class_name("quick-search")

	#br.find_element_by_name("search").clear()
	#br.find_element_by_name("search").send_keys("Five Below")
	#br.find_element_by_name("search").send_keys(Keys.ARROW_DOWN)
	#br.find_element_by_name("search").send_keys(Keys.RETURN)

    #click_sym(stk['bscs']['symbol'], elem)
    #print("Opts: %r : %r" %(len(opts.text), opts.text))

    #return
 
    #try:
    #    e=br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div/div[1]")
    #    if e:
    #        s=e.text
    #        if s.find("Earnings are not available for") != -1:
    #            print("No data for %s: %s, skipping" %(stk['bscs']['symbol'], stk['bscs']['name']))
    #            return
    #except Exception as e:
    #    print(str(e))

    #try:
    #    e=br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[1]/div/div[1]/div/div/div/div[2]/div/p[1]")
    #    if e:
    #        print("Page does not exist for %s: %s, skipping" %(stk['bscs']['symbol'], stk['bscs']['name']))
    #        return
    #except Exception as e:
    #    print(str(e))


    #sign on popup
    #e = br.find_element_by_xpath("/html/body/div[9]/div/div/div[1]/div/i")
    #e.click()

    # date
    #e=br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[1]/div[4]/span[2]")
    #date=timestring.Date(e.text)
    #date=date.date
    #date=str(date.date())
    #earnings.date=date
    ##earnings.date=str(dt.now().date())

    ###high target
    ##e = br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[1]/symbol-chart/div/div[2]/span[1]/div[1]/span")
    ##earnings.high_target = str_to_float(e.text)
    ##
    ###mean target
    ##e=br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[1]/symbol-chart/div/div[2]/span[1]/div[2]/span")
    ##earnings.mean_target = str_to_float(e.text)
    ###low target
    ##e=br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[1]/symbol-chart/div/div[2]/span[1]/div[3]/span")
    ##earnings.low_target=str_to_float(e.text)
    ###52 week high
    ##e=br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[1]/symbol-chart/div/div[2]/span[2]/div[1]/span")
    ##earnings.fiftytwoweek_high=str_to_float(e.text)
    ###52 week low
    ##e=br.find_element_by_xpath("/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[1]/symbol-chart/div/div[2]/span[2]/div[2]/span")
    ##earnings.fiftytwoweek_low=str_to_float(e.text)


    ###Earnings History
    ##for i in range(2,6):
    ##    #quarter dates
    ##    xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[2]/div[1]/div/div[2]/div/div/ng-transclude/table/thead/tr/th[%d]/span[2]" %(i)
    ##    e = br.find_element_by_xpath(xpath)
    ##    earnings.hist.quarters.append(e.text)
    ##    #reported
    ##    xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[2]/div[1]/div/div[2]/div/div/ng-transclude/table/tbody/tr[1]/td[%d]" %(i)
    ##    e = br.find_element_by_xpath(xpath)
    ##    rep = str_to_float(e.text)
    ##    earnings.hist.reported.append(rep)
    ##    #estimate
    ##    xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[2]/div[1]/div/div[2]/div/div/ng-transclude/table/tbody/tr[2]/td[%d]" %(i)
    ##    e = br.find_element_by_xpath(xpath)
    ##    est = str_to_float(e.text)
    ##    earnings.hist.estimate.append(est)
    ##    #difference
    ##    earnings.hist.difference.append(round(rep - est,2))
    ##    #surprise
    ##    try:
    ##        earnings.hist.surprise.append(round(((rep - est)/ est), 3))
    ##    except ZeroDivisionError:
    ##        earnings.hist.surprise.append(0)

    #Earnings Estimates
    ##for i in range(2,6):
    ##    if i < 4:
    ##        #quarter dates
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/thead/tr/th[%d]/span[2]" %(i)
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.quarters.append(e.text)

    ##        #average estimate
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[1]/td[%i]" %(i)
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.q_avg_est.append(str_to_float(e.text))
    ##
    ##        #number of estimates
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[2]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.q_num_est.append(str_to_float(e.text))
    ## 
    ##        #high estimate
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[3]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.q_high_est.append(str_to_float(e.text))
    ##
    ##        #low estimate
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[4]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.q_low_est.append(str_to_float(e.text))
    ##
    ##        #prior year
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[5]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.q_prior_yr.append(str_to_float(e.text))
    ##
    ##        #growth rate estimate (yoy)
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[6]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.q_gr_rate.append(round(str_to_float(e.text)/100, 3))

    ##    else:
    ##        #year dates
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/thead/tr/th[%d]/span[2]" %(i)
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.years.append(e.text)

    ##        #average estimate
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[1]/td[%i]" %(i)
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.y_avg_est.append(str_to_float(e.text))
    ##
    ##        #number of estimates
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[2]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.y_num_est.append(str_to_float(e.text))
    ## 
    ##        #high estimate
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[3]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.y_high_est.append(str_to_float(e.text))
    ##
    ##        #low estimate
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[4]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.y_low_est.append(str_to_float(e.text))
    ##
    ##        #prior year
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[5]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.y_prior_yr.append(str_to_float(e.text))
    ##
    ##        #growth rate estimate (yoy)
    ##        xpath = "/html/body/div[2]/div/div[2]/div[2]/div/div[2]/div/div/div/div[2]/div[3]/div[1]/div[1]/div[2]/div/div/ng-transclude/table/tbody/tr[6]/td[%d]" %(i) 
    ##        e = br.find_element_by_xpath(xpath)
    ##        earnings.est.y_gr_rate.append(round(str_to_float(e.text)/100, 3))

    #print(earnings.date)
    #print(earnings.high_target)
    #print(earnings.mean_target)
    #print(earnings.low_target)

    #print("Historical data")
    #print("Quarters: %r" %(earnings.hist.quarters))
    #print("Reported: %r" %(earnings.hist.reported))
    #print("Estimate: %r" %(earnings.hist.estimate))
    #print("Difference: %r" %(earnings.hist.difference))
    #print("Surprise: %r" %(earnings.hist.surprise))

    #print("Estimates")
    #print("Quarters: %r" %(earnings.est.quarters))
    #print("Years: %r" %(earnings.est.years))
    #
    #print("Quarter Avg Est: %r" %(earnings.est.q_avg_est))
    #print("Quarter Num Est: %r" %(earnings.est.q_num_est))
    #print("Quarter High Est: %r" %(earnings.est.q_high_est))
    #print("Quarter Low Est: %r" %(earnings.est.q_low_est))
    #print("Quarter Prior Yr: %r" %(earnings.est.q_prior_yr))
    #print("Quarter Growth Rate: %r" %(earnings.est.q_gr_rate))

    #print("Year Avg Est: %r" %(earnings.est.y_avg_est))
    #print("Year Num Est: %r" %(earnings.est.y_num_est))
    #print("Year High Est: %r" %(earnings.est.y_high_est))
    #print("Year Low Est: %r" %(earnings.est.y_low_est))
    #print("Year Prior Yr: %r" %(earnings.est.y_prior_yr))
    #print("Year Growth Rate: %r" %(earnings.est.y_gr_rate))

    update_DB_US_earnings_estimates(stk, earnings, fiftytwoweek_high, fiftytwoweek_low)

    #get_US_earnings_chart(earnings, br)
    close_browser(br)


def get_val(br, description, convert):
    soup = BeautifulSoup(br.page_source, 'html.parser')
    f=open("/home/vpetla/avgo-chart.html","w")
    f.write(soup.prettify())
    f.close()
    try:
        e = soup.find("td", {"class": "field-value"})
        print("get_val(): %s: %s" % (description, e.text))
        if convert:
            return float(e.text)
        return e.text
        # print("%d: %s: %s" %(i, description, eps))
        # if i > 10:
        #    break
    except Exception as e:
        print("Exception occured:", str(e))
        if convert:
            return 10000
        #return "10000"

def get_price(description):
    return round(float(description.split(",")[-1][:-1]), 2)

def get_date(br, description):
    soup = BeautifulSoup(br.page_source, 'html.parser')
    e = soup.find("td", {"class": "field-value"})
    e = e.find_next("td", {"class": "field-value"})
    #return e.text
    try:
        date = dt.strptime(e.text, '%m/%d/%Y').date()
    except Exception:
        PRINT_ERR("Failed to get Date: %r", e.text)
        exit()
    return date
    #eps_date = description.rsplit(",", 1)[0].split(".")[-1].split(",", 1)[-1].lstrip().rstrip().replace(",", "")
    #eps_date = dt.strptime(eps_date, '%b %d %Y').date()
    #return str(eps_date)

perform_i=0
def perform(h):
    global perform_i
    try:
        perform_i +=1
        h.perform()
    except Exception as e:
        if perform_i > 0:
            print("h.perform() error")
            print(str(e))
            #exit()
            perform_i=0
            return False
        perform(h)
    perform_i=0
    return True

def get_action_chain(br):
    return ac(br)

def scroll(br, direction):
    e=br.find_element_by_tag_name('html')
    for i in range(5):
        e.send_keys(direction)

def tab(br):
    print("tab")
    a = ac(br)
    a.send_keys(Keys.TAB).perform()

def popout_chart():
    global br
    try:
        a = ac(br)

        #Interactive chart
        we=br.find_element_by_class_name("bc-interactive-chart__wrapper-chart-content")
        h=a.move_to_element(we)
        h.context_click().perform()
        #soup=BeautifulSoup(br.page_source,'html.parser')
        #pattern=re.compile(r'Popout Chart')
        #e=soup.find(text=pattern)

        scroll(br, Keys.ARROW_DOWN)
        time.sleep(1)
        scroll(br, Keys.ARROW_DOWN)
        time.sleep(1)
        scroll(br, Keys.ARROW_DOWN)
        time.sleep(1)
        # Popout Chart
        we=br.find_element_by_css_selector("li.bc-interactive-chart-context-menu__menu-list-item:nth-child(28)")
        #we =br.find_element_by_css_selector("li.bc-interactive-chart-context-menu__menu-list-item:nth-child(28)")
        #we = br.find_element_by_class_name("bc-interactive-chart-context-menu__menu-list-item grouped margin-bottom-7")
        h=a.move_to_element(we)
        h.click().perform()
        WebDriverWait(br, 20).until(EC.number_of_windows_to_be(2))
        handles=br.window_handles
        br.switch_to_window(handles[0])
        br.close()
        br.switch_to_window(handles[-1])
    except Exception as E:
        exception_info(E)

def get_eps_for_element(br, description, convert, entries, offset, field, we, trial):
    entry = {}
    j = 0
    if trial == 1:
        a=ac(br)
        h = a.move_to_element(we)
        h.perform()
        time.sleep(0.1)
    while j in range(10):
        a = ac(br)
        val = get_val(br, description, convert)
        print("val:",val)
        if field == "split_factor":
            pattern=re.compile(r'\d{1,4}-\d{1,4}')
            #pattern=re.compile(r'^[1-9]*-[1-9]*')
            if pattern.match(val):
                split = round(float(val.split("-")[0])/float(val.split("-")[-1]), 3)
                entry[get_date(br, description)] = {"price":get_price(description), "split":val, field:split}
            entries.update(entry)
            return j
        elif val != 10000:
            entry[get_date(br,description)] = {"price":get_price(description), field:val}
            entries.update(entry)
            #print(entries)
            return j
        if trial:
            #print("height: %d" %(we.size['height']))
            #print("width: %d" %(we.size['width']))
            x_offset = we.size['height'] / 4 #+ j
            y_offset  = we.size['width'] / 4 #+ j
            #h = a.move_to_element_with_offset(we, x_offset, y_offset)
            h = a.move_by_offset(offset, offset)
            time.sleep(1)
        else:
            h = a.move_by_offset(offset, offset)
        perform(h)
        time.sleep(1)
        j += 1
    return j

def convert_date(label):
    date = None
    label = label.rsplit(",", 1)[0].split(".")[-1].split(",", 1)[-1].lstrip().rstrip().replace(",", "")
    try:
        date = dt.strptime(label, '%B %Y').date()
    except ValueError:
        print(date)
        date = dt.strptime(label, '%B %D %Y').date()
    if not date:
        print(date)
        sys.exit()
    return date

def get_all_entries(br, stk, item, field, pattern, convert):
    entries = {}
    entry = {}
    now = dt.now().date()
    start = dt.strptime("1950-01-01", '%Y-%m-%d').date()
    end = now

    if item not in stk['fig'].keys():
        stk['fig'][item]={}
    dates = list(stk['fig'][item].keys())
    last_date = get_last_date(stk, dates, '%Y-%m-%d')

    time.sleep(1)
    #scroll(br, Keys.ARROW_DOWN)

    # wire driver
    req = br.requests[-1]
    path = req.path
    if path.find('earnings=true'):
        earnings = response.body
        data = StringIO(earnings.decode('utf-8'))
        df = pd.read_csv(data, names=['Symbol','Date', 'Earnings','EPS'])

    else:
        return

    #soup = BeautifulSoup(br.page_source, 'html.parser')
    #tags = soup.find({"g"}, {"class":"#bc-interactive-chart__chart-container"})
    ##tags = soup.find({"g"}, {"class":"highcharts-series-group"})
    #labels = tags.findAll({"rect"})
    #start  = convert_date(str(labels[0].attrs['aria-label']))
    #end    = convert_date(str(labels[-1].attrs['aria-label']))

    db = DB.open_db('Stocks')
    if start < last_date:
        start = last_date + timedelta(7)
        end   = now
    #else:
    #    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "bscs.since", str(start))

    st = start
    while True:
        en = st + timedelta(days=365*10)
        if en >= now:
            en = now

        print("Selecting date")
        print("start: %s, end: %s" %(st, en))
        we = br.find_element_by_css_selector(".bc-glyph-calendar")
        we.click()
        d="%s/%s/%s"%(st.strftime('%m'), st.strftime('%d'), st.strftime('%Y'))
        #we = br.find_element_by_xpath("/html/body/div[6]/div/div/form/div[3]/div[1]/div/input")
        we = br.find_element_by_css_selector("div.interactive-chart-date-range:nth-child(1) > div:nth-child(1) > input:nth-child(1)")
        we.clear()
        we.click()
        we.send_keys(d)
        time.sleep(0.5)
        we.send_keys(Keys.TAB)
        d="%s/%s/%s"%(en.strftime('%m'), en.strftime('%d'), en.strftime('%Y'))
        #we = br.find_element_by_xpath("/html/body/div[6]/div/div/form/div[3]/div[2]/div/input")
        we = br.find_element_by_css_selector("div.interactive-chart-date-range:nth-child(3) > div:nth-child(1) > input:nth-child(1)")
        we.clear()
        we.click()
        we.send_keys(d)
        time.sleep(0.5)
        we.send_keys(Keys.TAB)
        #tab(br)
        #h=a.send_keys(d)
        #h.perform()
        br.find_element_by_css_selector("button.bc-button:nth-child(2)").click()
        time.sleep(2)
    
        soup = BeautifulSoup(br.page_source, 'html.parser')
        elements = soup.findAll(text=pattern)
   
        #a = ac(br)
        i = 0
        print("elements: %d" % (len(elements)))
        if len(elements) > 0:
            #description = elements[0].parent.parent.attrs['aria-label']
            #d1date = description.rsplit(",", 1)[0].split(".")[-1].split(",", 1)[-1].lstrip().rstrip().replace(",", "")
            #d1date = dt.strptime(d1date, '%b %d %Y').date()
    
            #description = elements[-1].parent.parent.attrs['aria-label']
            #d2date = description.rsplit(",", 1)[0].split(".")[-1].split(",", 1)[-1].lstrip().rstrip().replace(",", "")
            #d2date = dt.strptime(d2date, '%b %d %Y').date()
            ##d1date = datetime.strptime(d1, '%m/%d/%Y')
            ##d2date = datetime.strptime(d2, '%m/%d/%Y')
            #print(d1date)
            #print(d2date)
            #if d1date > d2date:
            #    elements.reverse()
            #f = open("/home/vpetla/aapl-chart1.html","w")
            #f.write(soup.prettify())
            #f.close()
            #input("Press any key to continue")

            for e in elements:
                if i >= 0:
                    #print(e.parent.parent)
                    #if not 'fill' in e.parent.parent.attrs:
                    #    continue
                    description = e.parent.parent.attrs['aria-label']
                    #print("Description:", description)
                    #print("Parent:",e.parent.parent)
                    attr = "g[aria-label=\'%s\'" % (description)
                    we = br.find_element(By.CSS_SELECTOR, attr)
                    #print("Web Element:", we)
                    a=ac(br)
                    h = a.move_to_element(we)
                    time.sleep(1)
                    # h = a.move_to_element_with_offset(we,0,0)
                    #print("Hovering to element")
                    if perform(h) is False:
                        print("Skipping %s" %(description))
                        continue
                    else:
                        #print("Hover Successfull")
                        pass
                    h=a=None
                    time.sleep(1)
                    offset = -1
                    j = get_eps_for_element(br, description, convert, entries, offset, field, we, 0)
                    if j == 20:
                        PRINT_ERR("%s: Couldn't get Value for : %r, checking the other way" %(stk['bscs']['symbol'], description))
                        offset = 1
                        a=ac(br)
                        h = a.move_to_element(we)
                        h.perform()
                        j = get_eps_for_element(br, description, convert, entries, offset, field, we, 1)
                        if j == 20:
                            PRINT_ERR("%s: Couldn't get Value for : %r, writing 100000" %(stk['bscs']['symbol'], description))
                            entry = {}
                            if field == "split_factor":
                                entry[get_date(br, description)] = {"price":get_price(description), "split":"100000:100000", field:100000}
                            else:
                                entry[get_date(br,description)] = {"price":get_price(description), field:100000}
                            entries.update(entry)
                            #exit()
                        #e={}
                        #return e
                i += 1
        st = en
        if en >= now:
            break
        if en >= end:
            break

    pp = pprint.PrettyPrinter(indent=4)
    sorted_entries = stk['fig'][item]
    for e in sorted(entries.keys()):
        sorted_entries[str(e)] = entries[e]
    sorted_entries['date'] = str(now)
    #pp.pprint(sorted_entries)
    scroll(br, Keys.ARROW_UP)
    return sorted_entries

def thread_click(e, lock):
    if e:
        lock.acquire()
        try:
            e.click()
        finally:
            lock.release()
 
def thread_close_popups(br, lock):
    while True:
        e = None
        print("looping close_popups")
        if stop_thread:
            break
        try:
            e = br.find_element_by_xpath("/html/body/div[8]/div[2]/div[1]")
            thread_click(e, lock)
        except Exception:
            pass
        try:
            # e=br.find_element_by_xpath("//*[@id="ic_guyoff6702"]")
            e = br.find_element_by_xpath("/html/body/div[9]/div[2]/div[3]/div/img")
            thread_click(e, lock)
        except Exception as E:
           pass
        try:
            e = br.find_element_by_class_name("hide-for-small hide-for-medium-only")
            thread_click(e, lock)
        except Exception as E:
           pass
        try:
            e = br.find_element_by_css_selector(".form-close-wrapper > i:nth-child(1)")
            thread_click(e, lock)
        except Exception as E:
           pass
        try:
            e = br.find_element_by_css_selector("#off9609")
            thread_click(e, lock)
        except Exception as E:
           pass
 
        time.sleep(1)

def close_login(br):
    try:
        br.switch_to_window(handles[-1])
        br.close()
        br.switch_to_window(handles[0])
    except:
        pass

def click(e):
    try:
        e.click()
    except ElementClickInterceptedException:
        close_login(br)

def find_element_by_css_selector(br, select):
    try:
        return br.find_element_by_css_selector(select)
    except Exception as e:
        print("Failed to find element by css selector")
        print(str(e))
        soup=BeautifulSoup(br.page_source,"html.parser")
        f=open("/home/vpetla/stock-chart.html","w")
        f.write(soup.prettify())
        f.close()
        exit()

def toggle_earnings_button():
    global br
    # goto settings
    e = find_element_by_css_selector(br, "span.show-for-medium-up")
    click(e)
    # goto adjustments
    e = find_element_by_css_selector(br, "div.bc-tabs__tab:nth-child(3)")
    click(e)

    # select earnings
    e = find_element_by_css_selector(br,
        ".row-events > ul:nth-child(2) > li:nth-child(2) > div:nth-child(1) > label:nth-child(2)")
    click(e)

    # apply
    #e = find_element_by_css_selector(br, "button.bc-button:nth-child(2)")
    e = find_element_by_css_selector(br, "button.light-blue")
    click(e)

def toggle_dividend_button():
    global br
    # goto settings
    e = find_element_by_css_selector(br, "span.show-for-medium-up")
    click(e)

    # goto adjustments
    e = find_element_by_css_selector(br, "div.bc-tabs__tab:nth-child(3)")
    click(e)

    # select dividend
    e = find_element_by_css_selector(br,
        ".row-events > ul:nth-child(2) > li:nth-child(1) > div:nth-child(1) > label:nth-child(2)")
    click(e)

    # apply
    #e = find_element_by_css_selector(br, "button.bc-button:nth-child(2)")
    e = find_element_by_css_selector(br, "button.light-blue")
    click(e)

def toggle_split_button():
    global br
    # goto settings
    e = find_element_by_css_selector(br, "span.show-for-medium-up")
    click(e)

    # goto adjustments
    e = find_element_by_css_selector(br, "div.bc-tabs__tab:nth-child(3)")
    click(e)

    # select split
    e = find_element_by_css_selector(br,
        ".row-events > ul:nth-child(2) > li:nth-child(3) > div:nth-child(1) > label:nth-child(2)")
    click(e)

    # apply
    #e = find_element_by_css_selector(br, "button.bc-button:nth-child(2)")
    e = find_element_by_css_selector(br, "button.light-blue")

    click(e)

def set_max_range(br, stk):
    try:
        # set 10 year range
        # e = br.find_element_by_css_selector("div.quick-settings:nth-child(2) > ul:nth-child(1) > li:nth-child(11)")
        # set MAX range
        e = br.find_element_by_css_selector("div.quick-settings:nth-child(2) > ul:nth-child(1) > li:nth-child(13)")
        e.click()
    except Exception as e:
        print(str(e))
        stk_file="/home/vpetla/%s-chart.html" %(stk['bscs']['symbol'])
        soup=BeautifulSoup(br.page_source,"html.parser")
        f=open(stk_file,"w")
        f.write(soup.prettify())
        f.close()
        return False

def write_hist_to_db(stk, eps_hist, dividend_hist, split_hist):
    db = DB.open_db('Stocks')
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "fig.EPS_History", eps_hist)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "fig.DIVIDEND_History", dividend_hist)
    DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "fig.SPLIT_History", split_hist)

# field can be earnings, dividends, splits
def populate_entries(br, mysql_engine, field):
    try:
        column = {"earnings":"EPS", "dividends":"DIVIDEND", "splits":"SPLIT"}
        table = {'earnings':'EPS_History', 'dividends': 'DIVIDEND_History', 'splits':'SPLIT_History'}

        for i in reversed(range(len(br.requests))):
            path = br.requests[i].path
            if path.find('{}=true'.format(field)) > 0:
                field_data = br.requests[i].response.body
                break
        if i == 0:
            print("Couldn't find the response for field: %r"%(field))
            return

        data = StringIO(field_data.decode('utf-8'))

        df = pd.read_csv(data, names=['Symbol','Date', 'junk', column[field]])
        del df['junk']

        if not df.empty:
            if mysql_engine.has_table(table[field]):
                query = 'select * from {} where Symbol=\'{}\''.format(table[field], df['Symbol'][0])
                ddf = DB.read_from_sql(query, mysql_engine, date=False)
                if not ddf.empty:
                    df = df[~df.Date.isin(ddf.Date)]
            if not df.empty: 
                DB.mysql_update_table(mysql_engine, table[field], df, check=True, insert=True, unknown_table=True, cols_type='fin', temp=True, date_column=False, format_columns=False)
    except Exception as E:
        exception_info(E)

stop_thread=False

def close_popups():
    global stop_thread
    global br

    while True:
        if stop_thread:
            print("Exiting thread")
            break
        e = None
        try:
            e = br.find_element_by_xpath("/html/body/div[8]/div[2]/div[1]")
            if e:
                print("Closed popup")
                e.click()
        except Exception:
            pass
        try:
            # e=br.find_element_by_xpath("//*[@id="ic_guyoff6702"]")
            e = br.find_element_by_xpath("/html/body/div[9]/div[2]/div[3]/div/img")
            if e:
                print("Closed popup")
                e.click()
        except Exception as E:
           pass
        try:
            e = br.find_element_by_class_name("hide-for-small hide-for-medium-only")
            if e:
                print("Closed popup")
                e.click()
        except Exception as E:
           pass
        try:
            e = br.find_element_by_css_selector(".form-close-wrapper > i:nth-child(1)")
            if e:
                print("Closed popup")
                e.click()
        except Exception as E:
           pass
        try:
            e = br.find_element_by_css_selector("#off9609")
            if e:
                print("Closed popup")
                e.click()
        except Exception as E:
           pass
 
    print("Exiting thread")

def populate_US_EPS(stk, mysql_engine=None, db=None):
    if 'EPS_DIV_SPLIT_History_Date' in stk['bscs'].keys():
        last_date = stk['bscs']['EPS_DIV_SPLIT_History_Date']
        if (dt.now() - last_date) < timedelta(30):
            print("Already updated on %r" %(str(last_date.date())))
            return
    global stop_thread
    global br
    th = None

    try:
        mysql_engine_created=False
        mongodb_engine_created=False
        if not mysql_engine:
            mysql_engine = DB.open_sql_connection('localhost', 'root', 'petla123', db='US_Stocks_Fin')
            mysql_engine_created=True
        if not db:
            c  = DB.open_db_client()
            db = c['Stocks']
            mongodb_engine_created=True

        fig = 'fig'
        #if not 'EPS_History' in stk[fig].keys():
        #    stk[fig]['EPS_History'] = {}

        #dates = list(stk[fig]['EPS_History'].keys())
        #dates.reverse()
        #if 'date' in dates:
        #    now = dt.now().date()
        #    last_date = stk[fig]['EPS_History']['date']
        #    last_date = dt.strptime(last_date, "%Y-%m-%d").date()
        #    if (dt.now().date() - last_date) < timedelta(30):
        #        print("Already updated on %r" %(str(last_date)))
        #        return


        #eps_hist = {}
        #split_hist = {}
        #dividend_hist = {}

        url = "https://www.barchart.com/stocks/quotes/%s/interactive-chart" %(stk['bscs']['symbol'])
        #br = open_browser(wiredriver=True)
        br = open_browser('headless', wiredriver=True)

        try:
            br.get(url)
        except Exception:
            print("%s: %s webpage loading timeout, trying again" %(stk['bscs']['symbol'], stk['bscs']['name']))
            close_browser(br)
            time.sleep(5)
            br = open_browser()
            br.get(url)

        stop_thread=False
        th = threading.Thread(target=close_popups, args=())
        th.start()

        # try:
        #    e=br.find_element_by_xpath("//*[@id="off7131"]")
        #    if e:
        #        e.click()

        # set 10 year range
        #e = br.find_element_by_css_selector("div.quick-settings:nth-child(2) > ul:nth-child(1) > li:nth-child(11)")
        time.sleep(4)
        
        if set_max_range(br, stk) is False:
            ##db = DB.open_db('Stocks')
            ##DB.update_field(db.US_Stocks, stk['bscs']['symbol'], "ignore", "Yes")
            #write_hist_to_db(stk, eps_hist, dividend_hist, split_hist)
            #close_browser(br)
            gc.collect()
            if mysql_engine_created:
                DB.close_sql_connection(mysql_engine)
            return
            
        br.maximize_window()
        time.sleep(2)

        # Cookies policy pop-up. Close it
        try:
            br.find_element_by_css_selector(".closebutton_closeButton--3abym > svg:nth-child(1) > path:nth-child(1)").click()
        except:
            pass
        time.sleep(1)
        popout_chart()
        #br.maximize_window()
        try:
            opts = WebDriverWait(br, 20).until(EC.presence_of_element_located((By.CSS_SELECTOR, '.bc-interactive-chart__wrapper-chart-content')))
            #opts = WebDriverWait(br, 20).until(EC.presence_of_element_located((By.CSS_SELECTOR, '.highcharts-background')))
        except selenium.common.exceptions.TimeoutException:
            pass
        print("chart loaded")
        time.sleep(4)

        toggle_earnings_button()
        time.sleep(3)
        populate_entries(br, mysql_engine, 'earnings')
        db.US_Stocks.update({'bscs.symbol': stk['bscs']['symbol']}, {'$set': {"bscs.EPS_History_Date": dt.now()}})
        #pattern = re.compile(r'^E$')
        #eps_hist = get_all_entries(br, stk, "EPS_History", "eps", pattern, 1)
        toggle_earnings_button()
        
        time.sleep(2)
        set_max_range(br, stk)
        time.sleep(2)
        toggle_dividend_button()
        time.sleep(3)
        populate_entries(br, mysql_engine, 'dividends')
        db.US_Stocks.update({'bscs.symbol': stk['bscs']['symbol']}, {'$set': {"bscs.DIVIDEND_History_Date": dt.now()}})
        #pattern = re.compile(r'^D$')
        #dividend_hist = get_all_entries(br, stk, "DIVIDEND_History", "dividend", pattern, 1)
        toggle_dividend_button()

        time.sleep(2)
        set_max_range(br, stk)
        time.sleep(2)
        toggle_split_button()
        time.sleep(3)
        populate_entries(br, mysql_engine, 'splits')
        db.US_Stocks.update({'bscs.symbol': stk['bscs']['symbol']}, {'$set': {"bscs.SPLIT_History_Date": dt.now()}})
        #pattern = re.compile(r'^S$')
        #split_hist = get_all_entries(br, stk, "SPLIT_History", "split_factor", pattern, 0)

        #write_hist_to_db(stk, eps_hist, dividend_hist, split_hist)

        db.US_Stocks.update({'bscs.symbol': stk['bscs']['symbol']}, {'$set': {"bscs.EPS_DIV_SPLIT_History_Date": dt.now()}})

        close_browser(br)
        if mysql_engine_created:
            DB.close_sql_connection(mysql_engine)
        if mongodb_engine_created:
            DB.close_db_client(c)
        gc.collect()
    except Exception as E:
        exception_info(E)
    finally:
        print("Killing firefox")
        os.system('pkill -f firefox >/dev/null 2>&1')
        stop_thread=True
        if th:
            th.join()

def get_page_with_check(url):
    html_page = get_webpage(url)
    #html_page  = get_html(html_file)
    soup = parse_html.get_soup(html_page)
    pattern = re.compile(r'Sorry, there is no additional data for this symbol.')
    div = soup.find(text=pattern)
    if div:
        return None
    pattern = re.compile(r'Oops, something\'s wrong.')
    div = soup.find(text=pattern)
    if div:
        return None
    pattern = re.compile(r'Fundamentals')
    div = soup.find(text=pattern)
    if div:
        print("Going to overview page, breaking")
        return None
    return html_page

def get_pages(path, symbol, statement_type, duration_type):
    print(statement_type, duration_type)
    i=1
    trials = 0
    while True:
        url = "https://www.barchart.com/stocks/quotes/%s/%s/%s?reportPage=%s" %(symbol, statement_type, duration_type, i)
        html_file = "%s/%s_%s_%s_%s.html" %(path, symbol, statement_type, duration_type, i)
        html_page = get_webpage(url)
        #html_page  = get_html(html_file)
        soup = parse_html.get_soup(html_page)
        pattern = re.compile(r'Sorry, there is no additional data for this symbol.')
        div = soup.find(text=pattern)
        if div:
            break
        pattern = re.compile(r'Oops, something\'s wrong.')
        div = soup.find(text=pattern)
        if div:
            break
        pattern = re.compile(r'Fundamentals')
        div = soup.find(text=pattern)
        if div:
            print("Going to overview page, breaking")
            break

        if 'Financial data is not available' in html_page:
            print("Reached till no data is available")
            break
 
        if '403 ERROR' in html_page:
            PRINT_ERR("*********************** Access to Barchart blocked ******************, changing VPN")
            time.sleep(5)
            change_vpn()
            trials = trials + 1
            if trials > 5:
                PRINT_ERR("Changing VPN didn't work")
                PRINT_ERR("exiting")
                sys.exit(1)
            else:
                continue

        write_to_file(html_page, html_file)
        i = i + 1
        if  i > 100:
            print(" ERROR: Going to infinite loop")
            break
 
def get_US_stock_page(symbol, name):
    path = "/home/vpetla/work/stockanalysis/US_Stocks/html_pages/%s" %(name)
    path = path.lstrip().rstrip().replace(",","")
    try:
        os.makedirs(path, exist_ok=True)
    except FileExistsError:
        PRINT_ERR("%s exists" %(symbol))
        return

    #income statements
    get_pages(path, symbol, "income-statement", "annual")
    get_pages(path, symbol, "income-statement", "quarterly")
    #cash flow statements
    get_pages(path, symbol, "cash-flow", "annual")
    get_pages(path, symbol, "cash-flow", "quarterly")
    #balance sheet statements
    get_pages(path, symbol, "balance-sheet", "annual")
    get_pages(path, symbol, "balance-sheet", "quarterly")

    #url = "https://www.barchart.com/stocks/quotes/%s/profile" %(symbol)
    #html_file = "%s/%s_profile.html" %(path, symbol)
    #get_page(url, html_file)

    return path

def get_all_US_html_pages():
    db = open_db('Stocks')
    docs = db.US_Stocks_List.find({})
    for i, doc in enumerate(docs):
        if i > -1:
            sym  = doc['symbol']
            name = doc['Name']
            if "&#39;" in name:
                print("stk has &")
                name = name.replace("&#39;", "\'")
                db.US_Stocks_List.update({'Name': doc['Name']}, {'$set': {"Name": name}})
            if "/" in name:
                print("stk has /")
                name = name.replace("/", "")
                db.US_Stocks_List.update({'Name': doc['Name']}, {'$set': {"Name": name}}) 
            if "^" in sym:
                print("symbol has ^")
                sym = sym.replace("^", "-")
                print(sym)
                db.US_Stocks_List.update({'symbol': doc['symbol']}, {'$set': {"symbol": sym}})

            print("%d: %s: %s "%(i, sym, name))
            get_US_stock_page(sym, name)

def get_html(stock_page):
    try:
        html = open(stock_page)
    except FileNotFoundError:
        PRINT_ERR("Failed to open %s" %(stock_page))
        return None
    return html

def get_India_stock_page(stock):
    driver = webdriver.Firefox()
    driver.get("http://www.ratestar.in/home")
    old_url = driver.current_url
    elem = driver.find_element_by_name("txtStock")

    elem.clear()
    for i in range(len(stock)):
        elem.send_keys(str(stock[i]))
        time.sleep(10/1000)
        #divs = driver.find_element_by_xpath()
        #s = Select(elem)
        #driver.find_element_by_css_selector("button.btn.btn-default").click()
        opts = WebDriverWait(driver, 10).until(EC.presence_of_element_located(
                (By.ID, 'listPlacementStock')))
        time.sleep(1)
        PRINT_DBG("Opts: %r : %r" %(len(opts.text), opts.text))

        #if len(items) == 1:
        if len(opts.text) == 0:
            PRINT("Unable to parse %r" % (stock))
            f = open("unparsed_stocks3.txt", "a")
            f.write(stock)
            f.write("\n")
            f.close()
            driver.close()
            return
        if not '\n' in opts.text:
            stock_name = opts.text
            #time.sleep(5)
            elem.send_keys(Keys.RETURN)
            break

        #attr=driver.find_element_by_name('txtStock').get_attribute('innerHTML')
        #attr=elem.get_attribute('innerHTML')
    #elem.send_keys(stock, Keys.ARROW_DOWN)
    #time.sleep(2)
    #elem.send_keys(Keys.RETURN)
    
    #time.sleep(20)
    try:
        WebDriverWait(driver, 20).until(EC.presence_of_element_located(
        (By.ID, 'lblCompany')))
    except TimeoutException:
        PRINT("Unable to parse %r" %(stock))
        f = open("unparsed_stocks3.txt", "a")
        f.write(stock)
        f.write("\n")
        f.close()
        return
    #PRINT_DBG(str(html_src))

    if driver.current_url == old_url:
        PRINT("Unable to parse %r" %(stock))
        f = open("unparsed_stocks3.txt", "a")
        f.write(stock)
        f.write("\n")
        f.close()
    else:
        #PRINT_DBG("Found stock %r" %(stock))
        html_src=driver.page_source
        html_file = "html_pages/%s.html" %(stock_name)
        f = open(html_file, "w")
        f.write(html_src)
        f.close()
 
#    try:
#        element = WebDriverWait(driver, 100).until(EC.title_contains((By.ID, stock)))
#        element = WebDriverWait(driver, 100).until(EC.presence_of_element_located((By.ID, 'IdOfMyElement')))
#        element = WebDriverWait(driver, 100).until(EC.presence_of_element_located((By.TAG_NAME, 'body')))
#        html_file = "html_pages/%s.html" %(stock)  
#        f = open(html_file, "w")
#        f.write(html_src)
#        f.close()
#    finally:
#        PRINT_DBG("Unable to parse %r" %(stock))
#        f = open("unparsed_stocks.txt", "a")
#        f.write(stock)
#        f.write("\n")
#        f.close()

    driver.close()
    
    #try:
    #    element = WebDriverWait(driver, 100).until(EC.title_contains((By.ID, stock)))
    #finally:
    #    driver.close()
    
    #assert "No results found." not in driver.page_source
    #time.sleep(5)

def get_India_all_stocks_html():
    wb = xlrd.open_workbook(bse_stocks)
    sheet = wb.sheet_by_index(0)
#    with open("missing_files.txt") as f:
#        for line in f:
#            line = line.replace("\n","")
#            print(line)
#            get_stock_page(line)

    for i in range(0,sheet.nrows):
    #for i in range(1,10):
        PRINT("%r: %r" %(i, sheet.cell_value(i, 2)))
        get_India_stock_page(sheet.cell_value(i,2))
        
#    f = open('NSE_Stocks.csv')
#    #f = open('BSE_Stocks.csv')
#    csv_f = csv.reader(f)
#    for row in csv_f:
#        PRINT_DBG(row)
#        #PRINT_DBG(row[1])
#        #PRINT_DBG(row[0], row[1], row[2],)

# Get stock split information
def get_stock_split_info_yahoo(stk):
    sym = stk['bscs']['symbol'] + '.BO'
    #get split info from Yahoo Finance
    data = yf(sym).get_key_statistics_data()
    stk['bscs']['split_factor'] = float(Fraction(data[sym]['lastSplitFactor']))
    d = data[sym]['lastSplitDate']
    stk['bscs']['split_date'] = d
    stk['bscs']['split_year'] = datetime.datetime.strptime(d, '%Y-%m-%d').year 

def get_stock_split_info(stk):
    wb = xlrd.open_workbook('India_Stocks/split_data.xls')
    sheet = wb.sheet_by_index(0)
    for i in range(1,sheet.nrows):
        if str(sheet.cell_value(i, 0)) == stk['bscs']['name']:
            stk['bscs']['split_date'] = sheet.cell_value(i,1)
            stk['bscs']['split_year'] = datetime.datetime.strptime(stk['bscs']['split_date'], '%d-%b-%Y').year
            try:
                stk['bscs']['split_factor'] = int(sheet.cell_value(i, 3)) / int(sheet.cell_value(i,4))
            except ZeroDivisionError:
                stk['bscs']['split_factor']=1
            return
    if stk['bscs']['face_value'] != 10:
        PRINT_ERR("Could not find split date for %s, facevalue: %r" %(stk['bscs']['symbol'], stk['bscs']['face_value']))   

# Get symbol name for bse symbol
# Get sector information
def get_symbol_and_sector(stk):
    wb = xlrd.open_workbook(bse_stocks)
    sheet = wb.sheet_by_index(0)
    #sheet.cell_value(0,0)

    for i in range(1,sheet.nrows):
        if str(int(sheet.cell_value(i, 0))) == stk['bscs']['bse_symbol']:
            stk['bscs']['symbol'] = sheet.cell_value(i,1)
            stk['bscs']['sector'] = sheet.cell_value(i,7)
            return
    PRINT_ERR("Cant find symbol name for %s" %(stk['bscs']['bse_symbol']))