def main():
wd = WebDriver()
wd.random_wait()
logger.info("Gathering data from sources")
for k, v in wd.sources_dict.items():
try:
wd.load_url(v.get('url'), sleep_after=True)
df = wd.parse_table(**v)
if df is not None:
s_file = os.path.join(wd.source_data_folder,
v.get('file') + '.csv')
if os.path.exists(s_file):
df = wd.update_existing_data(pd.read_csv(s_file),
df,
exclude_col='time_checked')
df.sort_values(by='time_checked',
ascending=False,
inplace=True)
df.to_csv(s_file, index=False, encoding='utf-8-sig')
wd.webscraping_results.append([wd.time_checked_str, k, 1])
except Exception as e:
logger.error(f"ERROR for {k}")
logger.error(e, exc_info=sys.exc_info())
logger.info('-' * 100)
error_screenshot_file = f"{k} Error {wd.time_checked.strftime('%Y-%m-%d %H%M')}.png"
wd.driver.save_screenshot(
os.path.join(log_folder, 'Screenshots', error_screenshot_file))
wd.webscraping_results.append([wd.time_checked_str, k, 0])
pass
wd.asx()
wd.tkipo()
wd.close_driver()
wd.av_api()
wd.save_webscraping_results()
def main():
logger.info("Combining all the data from external sources together")
dt = DataTransformation()
try:
dt.us()
dt.jpx()
dt.cn()
dt.euronext()
dt.aastocks()
dt.lse()
dt.ca()
dt.frankfurt()
dt.krx()
dt.asx()
dt.twse()
dt.bme()
dt.sgx()
dt.idx()
dt.bm()
dt.nasdaqnordic()
dt.spotlight()
dt.italy()
except Exception as e:
logger.error(e, exc_info=sys.exc_info())
error_email(str(e))
finally:
dt.formatting_all()
dt.save_all()
def file_for_rpds(self):
"""
Creating a file that will be used to create RPDs for Symbology and Fundamentals teams.
This will have all IPOs both from external sources and collected internally.
:return:
"""
pp_cols = [
'iconum', 'CUSIP', 'Company Name', 'client_deal_id', 'ticker',
'exchange', 'Price', 'trading_date', 'last_updated_date_utc'
]
df_outer = pd.merge(self.merge_entity_data(),
self.df_pp[pp_cols],
how='outer',
on='iconum',
suffixes=('_external', '_fds'))
for c in ['IPO Date', 'trading_date']:
try:
df_outer[c] = pd.to_datetime(df_outer[c].fillna(pd.NaT),
errors='coerce')
except Exception as e:
logger.error(f"{c} - {e}")
df_ipo = df_outer.loc[
(df_outer['IPO Date'].dt.date >= date.today())
| (df_outer['trading_date'].dt.date >= date.today())]
df_ipo.to_excel(os.path.join(self.ref_folder,
'IPO Monitoring Data.xlsx'),
index=False,
encoding='utf-8-sig')
df_wd = df_outer.loc[df_outer['Status'] == 'Withdrawn']
df_wd.to_excel(os.path.join(self.ref_folder, 'Withdrawn IPOs.xlsx'),
index=False,
encoding='utf-8-sig')
def main():
try:
for folder in [os.path.join(os.getcwd(), 'Reference', 'Entity Mapping Requests'),
os.path.join(os.getcwd(), 'Logs', 'Screenshots'),
os.path.join(os.getcwd(), 'Logs', 'Concordance API Responses')]:
delete_old_files(folder)
archive_logs()
except Exception as e:
logger.error(e, exc_info=sys.exc_info())
def main():
try:
rpd = RPDCreation()
rpd.update_withdrawn_ipos()
rpd.update_rpds()
rpd.add_new_rpds()
rpd.save_results()
except Exception as e:
logger.error(e, exc_info=sys.exc_info())
error_email(str(e))
def main():
logger.info("Comparing external data with data collected internally")
dc = DataComparison()
try:
dc.concatenate_ticker_exchange()
dc.file_for_rpds()
return dc.compare()
except Exception as e:
logger.error(e, exc_info=sys.exc_info())
error_email(str(e))
def main():
logger.info("Checking Cordance API for entity IDs")
em = EntityMatchBulk()
try:
em.create_csv()
em.entity_mapping_api()
except Exception as e:
logger.error(e, exc_info=sys.exc_info())
logger.info('-' * 100)
error_email(str(e))
def archive_logs(num_days: int = 30):
current_log_file = os.path.join(log_folder, log_file)
with open(current_log_file, 'r') as f:
all_lines = f.readlines()
key_dates = {'first_log_date': return_date_str(all_lines[0]), 'last_log_date': return_date_str(all_lines[-1])}
old_date = datetime.utcnow() - timedelta(days=num_days)
if key_dates.get('first_log_date') is not None:
first_log_date_datetime = datetime.strptime(key_dates.get('first_log_date'), '%Y-%m-%d')
if first_log_date_datetime < old_date:
log_file_name, log_file_ext = os.path.splitext(log_file)
archived_log = f"{log_file_name} {key_dates.get('first_log_date')} - {key_dates.get('last_log_date', datetime.today().strftime('%Y-%m-%d'))}{log_file_ext}"
# TODO: this will fail with PermissionError
# The process cannot access the file because it is being used by another process
# os.rename(current_log_file, os.path.join(log_folder, 'Previous Logs', archived_log))
try:
shutil.move(current_log_file, os.path.join(log_folder, 'Previous Logs', archived_log))
except PermissionError as e:
logger.error(e)
def update_existing_data(old_df: pd.DataFrame,
new_df: pd.DataFrame,
exclude_col=None) -> pd.DataFrame:
"""
If there is already existing data, this function can be called to remove any duplicates.
:param old_df: DataFrame with existing data
:param new_df: DataFrame with new data
:param exclude_col: Column(s) that will be excluded when removing duplicate values in DataFrames.
Can be given either as a list of columns or a string with the column name.
:return: DataFrame
"""
try:
df = pd.concat([old_df, new_df.astype(old_df.dtypes)],
ignore_index=True,
sort=False)
except KeyError as ke:
logger.error(ke)
logger.info(f"Existing df columns: {', '.join(old_df.columns)}")
logger.info(f"New df columns: {', '.join(new_df.columns)}")
except ValueError as ve:
logger.error(ve)
logger.info(
f"Existing df data types: \n{old_df.dtypes.to_string(na_rep='')}"
)
logger.info(
f"New df data types: \n{new_df.dtypes.to_string(na_rep='')}")
df = pd.concat([old_df, new_df], ignore_index=True, sort=False)
if exclude_col and isinstance(exclude_col, str):
ss = [col for col in df.columns.to_list() if col != exclude_col]
elif exclude_col and isinstance(exclude_col, list):
ss = [
col for col in df.columns.to_list() if col not in exclude_col
]
else:
ss = df.columns.to_list()
# I want to preserve when this item was first added to the website and have most recent updates at the top so
# sorting by most recent time_checked, dropping duplicates for subset of columns and keeping the last (earliest)
if 'time_checked' in df.columns:
df.sort_values(by='time_checked', ascending=False, inplace=True)
df.drop_duplicates(subset=ss, keep='last', inplace=True)
return df
def av_api(self):
try:
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
parameters = {
'function': self.config.get('AV', 'funct'),
'apikey': self.config.get('AV', 'funct')
}
r = requests.get(self.config.get('AV', 'base_url'),
params=parameters,
verify=False)
cal = [[cell.replace('\r', '') for cell in row.split(',')]
for row in r.text.split('\n')]
df = pd.DataFrame(cal)
df.columns = df.loc[0]
df = df.drop(0).reset_index(drop=True)
df = df.dropna()
df.loc[df['name'].str.contains(r' Warrant'),
'assetType'] = 'Warrants'
df.loc[df['name'].str.contains(r' Right'), 'assetType'] = 'Rights'
df.loc[df['name'].str.contains(r' Unit'), 'assetType'] = 'Units'
df['assetType'].fillna('Shares', inplace=True)
for c in ['priceRangeLow', 'priceRangeHigh']:
df[c] = pd.to_numeric(df[c], errors='coerce')
df['time_checked'] = self.time_checked_str
df.sort_values(by=['ipoDate', 'name'], inplace=True)
s_file = os.path.join(self.source_data_folder,
self.config.get('AV', 'file_name') + '.csv')
if os.path.exists(s_file):
df = self.update_existing_data(pd.read_csv(s_file),
df,
exclude_col='time_checked')
df.sort_values(by='time_checked', ascending=False, inplace=True)
df.to_csv(s_file, index=False, encoding='utf-8-sig')
self.webscraping_results.append(
[self.time_checked_str,
self.config.get('AV', 'file_name'), 1])
except Exception as e:
logger.error(f"ERROR for AV")
logger.error(e, exc_info=sys.exc_info())
logger.info('-' * 100)
self.webscraping_results.append([self.time_checked_str, 'AV', 0])
def asx(self):
try:
self.driver.get(
'https://www2.asx.com.au/listings/upcoming-floats-and-listings'
)
soup = self.return_soup()
listing_info = [
co.text.strip()
for co in soup.find_all('span',
attrs={'class': 'gtm-accordion'})
]
df = pd.DataFrame(listing_info)
df.columns = ['listing_info']
df['Company Name'] = df['listing_info'].str.extract(
r'^([a-zA-Z0-9\s,\.&]*)\s\-')
df['IPO Date'] = df['listing_info'].str.extract(
r'\s*-\s*(\d{1,2}\s\w*\s\d{2,4})')
df['IPO Date'] = pd.to_datetime(df['IPO Date'],
errors='coerce').dt.date
df['Market'] = 'Australian Stock Exchange'
df['time_checked'] = self.time_checked_str
if df is not None:
s_file = os.path.join(self.source_data_folder, 'ASX.csv')
if os.path.exists(s_file):
df = self.update_existing_data(pd.read_csv(s_file),
df,
exclude_col='time_checked')
df.sort_values(by='time_checked',
ascending=False,
inplace=True)
df.to_csv(s_file, index=False, encoding='utf-8-sig')
self.webscraping_results.append(
[self.time_checked_str, 'ASX', 1])
except Exception as e:
logger.error(f"ERROR for ASX")
logger.error(e, exc_info=sys.exc_info())
logger.info('-' * 100)
error_screenshot_file = f"ASX Error {self.time_checked.strftime('%Y-%m-%d %H%M')}.png"
self.driver.save_screenshot(
os.path.join(log_folder, 'Screenshots', error_screenshot_file))
self.webscraping_results.append([self.time_checked_str, 'ASX', 0])
def tkipo(self):
try:
self.driver.get(
'http://www.tokyoipo.com/top/iposche/index.php?j_e=E')
soup = self.return_soup()
table = soup.find('table', attrs={'class': 'iposchedulelist'})
table_data = []
row = []
for r in table.find_all('tr'):
for cell in r.find_all('td'):
cell_text = cell.text.strip()
if '\n\n▶\xa0Stock/Chart' in cell_text:
table_data.append(row)
row = [cell_text.replace('\n\n▶\xa0Stock/Chart', '')]
else:
row.append(cell_text)
table_data.append(row)
df = pd.DataFrame(table_data)
df.columns = [
'Company Name', 'IPO Date', 'Symbol', 'Listed Shares',
'Blank_0', 'Price Range', 'Price', 'Book Building Period',
'Opening Price', 'Change', 'Lead Underwriter',
'Business Description', 'Blank_1'
]
df.replace('', np.nan, inplace=True)
df.dropna(how='all', inplace=True)
df.drop(columns=['Blank_0', 'Business Description', 'Blank_1'],
inplace=True,
errors='ignore')
df['Company Name'] = df['Company Name'].str.strip()
df['Price Range Expected Date'] = df['Price Range'].str.extract(
r'^(\d{0,2}\/\d{0,2})$')
df['Price Expected Date'] = df['Price'].str.extract(
r'^(\d{0,2}\/\d{0,2})$')
df['Price'] = pd.to_numeric(df['Price'].str.replace(',', ''),
errors='coerce')
# date is provided as mm/dd, adding current year to make the date formatted as mm/dd/yyyy
df['IPO Date'] = df['IPO Date'] + f"/{datetime.now().year}"
df['IPO Date'] = pd.to_datetime(df['IPO Date'],
errors='coerce').dt.date
# at the beginning of the year, the calendar will still show IPOs from last year
# adding the current year to that previous date will be incorrect
# those incorrect dates will be 6+ months away, we shouldn't see legitimate IPO dates that far in advance
# if the IPO date is more than 6 months away, I subtract 1 year from the IPO date
df.loc[df['IPO Date'] >
(pd.to_datetime('today') + pd.offsets.DateOffset(months=6)),
'IPO Date'] = df['IPO Date'] - pd.offsets.DateOffset(
years=1)
df['Market'] = 'Japan Stock Exchange' + ' - ' + df[
'Symbol'].str.extract(r'\((\w*)\)')
df['Symbol'] = df['Symbol'].str.replace(r'(\(\w*\))',
'',
regex=True)
df['time_checked'] = self.time_checked_str
if df is not None:
s_file = os.path.join(self.source_data_folder, 'TokyoIPO.csv')
if os.path.exists(s_file):
df = self.update_existing_data(pd.read_csv(s_file),
df,
exclude_col='time_checked')
df.sort_values(by='time_checked',
ascending=False,
inplace=True)
df.to_csv(s_file, index=False, encoding='utf-8-sig')
self.webscraping_results.append(
[self.time_checked_str, 'TokyoIPO', 1])
except Exception as e:
logger.error(f"ERROR for TokyoIPO")
logger.error(e, exc_info=sys.exc_info())
logger.info('-' * 100)
error_screenshot_file = f"TokyoIPO Error {self.time_checked.strftime('%Y-%m-%d %H%M')}.png"
self.driver.save_screenshot(
os.path.join(log_folder, 'Screenshots', error_screenshot_file))
self.webscraping_results.append(
[self.time_checked_str, 'TokyoIPO', 0])
def compare(self):
df_m = pd.merge(self.merge_entity_data(),
self.df_pp,
how='left',
on='iconum',
suffixes=('_external', '_fds'))
df_m.drop_duplicates(inplace=True)
for c in [col for col in df_m.columns if 'date' in col.lower()]:
# intermittently getting InvalidIndexError: Reindexing only valid with uniquely valued Index objects
try:
df_m[c] = pd.to_datetime(df_m[c].fillna(pd.NaT),
errors='coerce').dt.date
except Exception as e:
logger.error(f"{c} - {e}")
df_m['IPO Dates Match'] = df_m['IPO Date'] == df_m['trading_date']
df_m['IPO Prices Match'] = df_m['Price_external'] == df_m['Price_fds']
df_m.loc[df_m['Price_external'].isna(), 'IPO Prices Match'] = True
df_m = df_m[[
'IPO Dates Match', 'IPO Prices Match', 'iconum',
'Company Name_external', 'Symbol', 'Market', 'IPO Date',
'Price_external', 'Price Range', 'Status', 'Notes', 'time_checked',
'Company Name_fds', 'master_deal', 'client_deal_id', 'CUSIP',
'ticker', 'exchange', 'Price_fds', 'min_offering_price',
'max_offering_price', 'announcement_date', 'pricing_date',
'trading_date', 'closing_date', 'deal_status',
'last_updated_date_utc'
]]
df_m.drop_duplicates(inplace=True)
df_summary = df_m[[
'Company Name_external', 'iconum', 'master_deal', 'IPO Date',
'Symbol', 'Market', 'Price_external', 'IPO Dates Match',
'IPO Prices Match'
]]
df_summary.rename(columns={
'Company Name_external': 'Company Name',
'Price_external': 'Price'
},
inplace=True)
df_summary.drop_duplicates(inplace=True)
df_summary = df_summary.loc[df_summary['IPO Date'] >= date.today()]
df_summary.sort_values('IPO Date', inplace=True)
with pd.ExcelWriter(
os.path.join(self.results_folder,
'IPO Monitoring.xlsx')) as writer:
df_m.to_excel(writer,
sheet_name='Comparison',
index=False,
encoding='utf-8-sig',
freeze_panes=(1, 0))
self.df_s.to_excel(writer,
sheet_name='Upcoming IPOs - External',
index=False,
encoding='utf-8-sig',
freeze_panes=(1, 0))
self.df_pp.to_excel(writer,
sheet_name='PEO-PIPE IPO Data',
index=False,
encoding='utf-8-sig',
freeze_panes=(1, 0))
df_summary.to_excel(writer,
sheet_name='Summary',
index=False,
encoding='utf-8-sig',
freeze_panes=(1, 0))
return df_summary
def main(file_attachment: str, addtl_message: str = ''):
try:
email_report(attach_file=file_attachment, addtl_message=addtl_message)
except Exception as e:
logger.error(e, exc_info=sys.exc_info())
error_email(str(e))