def update_equities(year):
updated = wrds_update("pr_equities_" + str(year),
"rpna",
rename="group=group_")
if updated:
engine.execute("CREATE INDEX ON rpna.pr_equities_" + str(year) +
" (rp_entity_id, rpna_date_utc)")
return updated
def update_schema(schema, wrds_id):
table_list = get_wrds_tables(schema, wrds_id)
regex_list = [
"wrds_company_networks", "company_profile_sr_mgrs",
"wrds_dir_profile_emp", "dir_profile_emp", "wrds_dir_profile_all",
"wrds_org_composition"
]
regex = "(" + "|".join(regex_list) + ")"
for table in table_list:
if re.search("individual_networks", table):
continue
fix_missing = re.search(regex, table) is not None
wrds_update(table_name=table,
schema=schema,
wrds_id=wrds_id,
fix_missing=fix_missing)
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update, make_engine
# engine.execute("CREATE ROLE risk")
# engine.execute("GRANT USAGE ON SCHEMA risk TO risk")
update = wrds_update("va_proposals", "risk")
update = wrds_update("vavoteresults", "risk")
if update:
sql = """
ALTER TABLE risk.vavoteresults ALTER COLUMN companyid TYPE integer;
ALTER TABLE risk.vavoteresults ALTER COLUMN meetingid TYPE integer;
ALTER TABLE risk.vavoteresults ALTER COLUMN itemonagendaid TYPE integer;
ALTER TABLE risk.vavoteresults ALTER COLUMN seqnumber TYPE integer;
UPDATE risk.vavoteresults
SET voterequirement = 0.6667 WHERE voterequirement=66.67;
UPDATE risk.vavoteresults SET base = 'F+A' WHERE base='F A';
UPDATE risk.vavoteresults SET base = 'F+A+AB'
WHERE base IN ('F A AB', 'F+A+B');
UPDATE risk.vavoteresults
SET (votedfor, votedagainst, votedabstain)=(2224433656, 93561790, 34814753)
WHERE itemonagendaid=6019529;
UPDATE risk.vavoteresults
SET (votedfor, votedagainst, votedabstain, ticker)= (10540862,1329889,790539,'KIDE')
WHERE itemonagendaid=6039421;
autoload_with=engine)
columns = the_table.c
col_lst = [
col.name for col in columns
if col.name.startswith("is_") and not isinstance(col.type, Boolean)
]
modify_lst = [mod_col(col, schema, table, engine) for col in col_lst]
if modify_lst:
print("Columns changed to boolean", modify_lst)
return modify_lst
updated = wrds_update("feed25person", "audit")
updated = wrds_update("namesauditorsinfo", "audit")
# Partially working; need to add part4_3_text* columns
updated = wrds_update("nt",
"audit",
drop="match: closest: prior: part4_3_text: ")
# updated = wrds_update("nt", "audit", keep="nt_notify_key", force=True, alt_table_name="nt_other")
updated = wrds_update("auditnonreli",
"audit",
drop="prior: match: closest: eventdate:")
updated = wrds_update("bankrupt", "audit", drop="match: closest: prior:")
if updated:
engine.execute("""
ALTER TABLE audit.bankrupt ALTER COLUMN bank_key TYPE integer;
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
updated = wrds_update("wciklink_gvkey", "wrdssec")
updated = wrds_update("wciklink_names", "wrdssec")
updated = wrds_update("wciklink_cusip", "wrdssec", drop="tmatch")
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
updated = wrds_update("amend", "tfn")
updated = wrds_update("avgreturns", "tfn")
updated = wrds_update("company", "tfn", fix_cr=True)
updated = wrds_update("form144", "tfn")
updated = wrds_update("header", "tfn")
updated = wrds_update("idfhist", "tfn")
updated = wrds_update("idfnames", "tfn")
updated = wrds_update("rule10b5", "tfn")
updated = wrds_update("table1", "tfn")
updated = wrds_update("table2", "tfn")
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
dealscan_tables = ["borrowerbase", "company", "currfacpricing", "dealamendment",
"dealpurposecomment", "facility", "facilityamendment", "facilitydates",
"facilityguarantor", "facilitypaymentschedule", "facilitysecurity",
"facilitysponsor", "financialcovenant", "financialratios", "lendershares",
"link_table", "lins", "marketsegment", "networthcovenant", "organizationtype",
"package", "packageassignmentcomment", "performancepricing",
"performancepricingcomments", "sublimits", "dbo_df_fac_dates_data"]
for table in dealscan_tables:
wrds_update(table, "dealscan")
engine = make_engine()
# Update Treasury yield table crsp.tfz_ft
# From wrds:
# The error is correct, the table "tfz_ft," does not exist. Behind the scenes this web
# query form is joining two tables on the fly. The tables this query is joining are
# "crsp.tfz_idx" and either "crsp.tfz_dly_ft" or "crsp.tfz_mth_ft" (depending on if
# you want daily or monthly data) by the variable "kytreasnox."
# Here are some links to the information about these tables:
# https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=77140
# https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=77137
# https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=77147
tfz_idx = wrds_update("tfz_idx", "crsp", fix_missing=True)
tfz_dly_ft = wrds_update("tfz_dly_ft", "crsp", fix_missing=True)
if tfz_idx or tfz_dly_ft:
sql = """
DROP TABLE IF EXISTS crsp.tfz_ft;
CREATE TABLE crsp.tfz_ft
AS
(SELECT kytreasnox, tidxfam, ttermtype, ttermlbl, caldt, rdtreasno,
rdcrspid, -- to_timestamp(rdcrspid, 'YYYYMMDD.HH24MISS') as rdcrspid,
tdyearstm, tdduratn, tdretadj, tdytm, tdbid, tdask, tdnomprc,
tdnomprc_flg, tdaccint
FROM crsp.tfz_idx
INNER JOIN crsp.tfz_dly_ft
USING (kytreasnox))
"""
engine.execute(sql)
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update, make_engine
engine = make_engine()
updated = wrds_update("wrds_gvkey", "ciq", fix_missing=True)
if updated:
engine.execute("CREATE INDEX ON ciq.wrds_gvkey (companyid)")
updated = wrds_update("wrds_cusip", "ciq", fix_missing=True)
if updated:
engine.execute("CREATE INDEX ON ciq.wrds_cusip (companyid)")
updated = wrds_update("wrds_cik", "ciq", fix_missing=True)
if updated:
engine.execute("CREATE INDEX ON ciq.wrds_cik (companyid)")
updated = wrds_update("ciqfininstance", "ciq")
if updated:
engine.execute("CREATE INDEX ON ciq.ciqfininstance (financialperiodid)")
engine.execute("ANALYZE ciq.ciqfininstance")
updated = wrds_update("ciqfinperiod", "ciq")
if updated:
engine.execute("CREATE INDEX ON ciq.ciqfinperiod (financialperiodid)")
engine.execute("CREATE INDEX ON ciq.ciqfinperiod (companyid)")
engine.execute("ANALYZE ciq.ciqfinperiod")
updated = wrds_update("ciqgvkeyiid", "ciq")
if updated:
engine.execute("CREATE INDEX ON ciq.ciqgvkeyiid (relatedcompanyid)")
autoload_with=engine)
columns = the_table.c
col_lst = [
col.name for col in columns
if col.name.startswith("is_") and not isinstance(col.type, Boolean)
]
modify_lst = [mod_col(col, schema, table, engine) for col in col_lst]
if modify_lst:
print("Columns changed to boolean", modify_lst)
return modify_lst
updated = wrds_update("feed25person", "audit")
updated = wrds_update("namesauditorsinfo", "audit")
# Partially working; need to add part4_3_text* columns
updated = wrds_update("nt",
"audit",
drop="match: closest: prior: part4_3_text: ")
# updated = wrds_update("nt", "audit", keep="nt_notify_key", force=True, alt_table_name="nt_other")
updated = wrds_update("auditnonreli",
"audit",
drop="prior: match: closest: eventdate: http_name_html")
updated = wrds_update("bankrupt", "audit", drop="match: closest: prior:")
if updated:
engine.execute("""
ALTER TABLE audit.bankrupt ALTER COLUMN bank_key TYPE integer;
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
updated = wrds_update("mflink1", "mfl", force=True)
updated = wrds_update("mflink2", "mfl")
#!/usr/bin/env python3
from time import gmtime, strftime
from wrds2pg.wrds2pg import wrds_update, make_engine, run_file_sql
engine = make_engine()
updated = wrds_update("g_exrt_dly", "comp")
updated = wrds_update("g_funda", "comp", fix_missing=True, rename="do=do_")
if updated:
engine.execute("CREATE INDEX ON comp.g_funda (gvkey)")
updated = wrds_update("g_secd", "comp")
if updated:
engine.execute("CREATE INDEX ON comp.g_secd (gvkey)")
updated = wrds_update("g_security", "comp")
updated = wrds_update("g_company", "comp")
updated = wrds_update("sec_history", "comp")
ecupdated = wrds_update("idxcst_his", "comp", rename="from=fromdt")
updated = wrds_update("anncomp", "comp")
if updated:
engine.execute("CREATE INDEX ON comp.anncomp (gvkey)")
updated = wrds_update("adsprate", "comp")
if updated:
engine.execute("CREATE INDEX ON comp.adsprate (gvkey, datadate)")
updated = wrds_update("co_hgic", "comp")
#!/usr/bin/env python3
from wrds2pg.wrds2pg import make_engine, wrds_update
updated = wrds_update("wrds_keydev", "ciq", fix_missing=True)
if updated:
engine = make_engine()
engine.execute("CREATE INDEX ON ciq.wrds_keydev (keydeveventtypeid)")
engine.execute("CREATE INDEX ON ciq.wrds_keydev (companyid)")
engine.dispose()
updated = wrds_update("ciqkeydev", "ciq", fix_cr=True)
if updated:
engine = make_engine()
engine.execute("CREATE INDEX ON ciq.ciqkeydev (keydevid)")
engine.dispose()
wrds_update("ciqkeydeveventtype", "ciq", fix_cr=True)
wrds_update("ciqkeydevstatus", "ciq")
wrds_update("ciqkeydevobjectroletype", "ciq", fix_cr=True)
updated = wrds_update("ciqkeydevtoobjecttoeventtype", "ciq")
if updated:
engine = make_engine()
engine.execute(
"CREATE INDEX ON ciq.ciqkeydevtoobjecttoeventtype (keydevid)")
engine.dispose()
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update, run_file_sql
from wrds2pg.wrds2pg import make_engine, wrds_id
engine = make_engine()
wrds_update("act_epsus", "ibes")
wrds_update("actpsumu_epsus", "ibes")
wrds_update("actu_epsus", "ibes")
updated = wrds_update("detu_epsus", "ibes")
if updated:
engine.execute("SET maintenance_work_mem='1999MB'")
engine.execute("CREATE INDEX ON ibes.detu_epsus (ticker, revdats)")
updated = wrds_update("det_xepsus", "ibes")
if updated:
engine.execute("CREATE INDEX ON ibes.det_xepsus (ticker, revdats)")
wrds_update("curr", "ibes")
wrds_update("det_epsus", "ibes")
wrds_update("detu_epsint", "ibes")
wrds_update("id", "ibes")
wrds_update("idsum", "ibes")
wrds_update("surpsum", "ibes")
wrds_update("statsum_epsus", "ibes")
updated = wrds_update("statsumu_epsus", "ibes")
if updated:
engine.execute("SET maintenance_work_mem='1999MB'")
engine.execute("CREATE INDEX ON ibes.statsumu_epsus (ticker, statpers)")
wrds_update("det_guidance", "ibes")
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
wrds_update("globalvoteresults", "risk")
wrds_update("vavoteresults", "risk")
wrds_update("issrec", "risk")
wrds_update("directors", "risk")
wrds_update("gset", "risk")
wrds_update("votes", "risk")
#!/usr/bin/env python3
from sqlalchemy import MetaData
from wrds2pg.wrds2pg import wrds_update, run_file_sql, make_engine
engine = make_engine()
avail_years = range(2000, 2020)
updated = wrds_update("rp_entity_mapping", "rpna")
def update_equities(year):
updated = wrds_update("pr_equities_" + str(year),
"rpna",
rename="group=group_")
if updated:
engine.execute("CREATE INDEX ON rpna.pr_equities_" + str(year) +
" (rp_entity_id, rpna_date_utc)")
return updated
updated = [update_equities(year) for year in avail_years]
def select(cols):
sql = "SELECT " + ", ".join(cols)
return sql
def get_sql(year, cols):
return (select(cols) + "\nFROM rpna.pr_equities_%s\n" % year)
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
updated = wrds_update("issuer", "cusipm")
updated = wrds_update("issue", "cusipm")
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
updated = wrds_update("firm_ratio", "wrdsapps", force=True)
#!/usr/bin/env python3
from wrds2pg.wrds2pg import wrds_update
updated = wrds_update("history", "kld", fix_missing=True)
