def update_equities(year): updated = wrds_update("pr_equities_" + str(year), "rpna", engine, wrds_id, rename="group=group_") if updated: engine.execute("CREATE INDEX ON rpna.pr_equities_" + str(year) + " (rp_entity_id, rpna_date_utc)") return updated
#!/usr/bin/env python3 from sqlalchemy import create_engine import os dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") engine = create_engine("postgresql://" + host + "/" + dbname) from wrds_fetch import wrds_update, run_file_sql updated = wrds_update("anncomp", "comp", engine, wrds_id) if updated: engine.execute("CREATE INDEX ON comp.anncomp (gvkey)") updated = wrds_update("adsprate", "comp", engine, wrds_id) if updated: engine.execute("CREATE INDEX ON comp.adsprate (gvkey, datadate)") updated = wrds_update("co_hgic", "comp", engine, wrds_id) if updated: engine.execute("CREATE INDEX ON comp.co_hgic (gvkey)") updated = wrds_update("co_ifndq", "comp", engine, wrds_id) if updated: engine.execute("CREATE INDEX ON comp.co_ifndq (gvkey, datadate)") company_updated = wrds_update("company", "comp", engine, wrds_id) if company_updated: engine.execute("CREATE INDEX ON comp.company (gvkey)") updated = wrds_update("idx_ann", "comp", engine, wrds_id)
#!/usr/bin/env python3 from sqlalchemy import create_engine import os dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") dbname = engine = create_engine("postgresql://" + host + "/" + dbname) from wrds_fetch import wrds_update wrds_update("act_epsus", "ibes", engine, wrds_id) wrds_update("actpsumu_epsus", "ibes", engine, wrds_id) wrds_update("actu_epsus", "ibes", engine, wrds_id) updated = wrds_update("detu_epsus", "ibes", engine, wrds_id) if True: # updated: engine.execute("SET maintenance_work_mem='1999MB'") engine.execute("CREATE INDEX ON ibes.detu_epsus (ticker, revdats)") wrds_update("det_epsus", "ibes", engine, wrds_id) wrds_update("id", "ibes", engine, wrds_id) wrds_update("idsum", "ibes", engine, wrds_id) wrds_update("surpsum", "ibes", engine, wrds_id) wrds_update("statsum_epsus", "ibes", engine, wrds_id) updated = wrds_update("statsumu_epsus", "ibes", engine, wrds_id) if updated: engine.execute("SET maintenance_work_mem='1999MB'") engine.execute("CREATE INDEX ON ibes.statsumu_epsus (ticker, statpers)") # Update guidance wrds_update("det_guidance", "ibes", engine, wrds_id) wrds_update("det_guidance_ext", "ibes", engine, wrds_id)
#!/usr/bin/env python3 from sqlalchemy import create_engine import os, sys sys.path.insert(0, '..') from wrds_fetch import wrds_update dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") engine = create_engine("postgresql://" + host + "/" + dbname) # Import sas file to pg fpath = os.path.dirname(os.path.abspath(__file__)) print("fpath: %s" % fpath) # Table_name should be the same as sas file, eg. for admit.sas7bdat, table_name = "admit" table_name = 'msp500' schema = 'audit' #wrds_update(table_name, fpath, schema, engine, wrds_id="", fix_missing=True, fix_cr=True, # drop="id name", obs="10", rename="fee=fee_old") wrds_update(table_name=table_name, schema=schema, engine=engine, fpath=fpath)
#!/usr/bin/env python3 from sqlalchemy import create_engine import os dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") dbname = engine = create_engine("postgresql://" + host + "/" + dbname) from wrds_fetch import wrds_update wrds_update("borrowerbase", "dealscan", engine, wrds_id) wrds_update("company", "dealscan", engine, wrds_id) wrds_update("currfacpricing", "dealscan", engine, wrds_id) wrds_update("dealamendment", "dealscan", engine, wrds_id) wrds_update("dealpurposecomment", "dealscan", engine, wrds_id) wrds_update("facility", "dealscan", engine, wrds_id) wrds_update("facilityamendment", "dealscan", engine, wrds_id) wrds_update("facilitydates", "dealscan", engine, wrds_id) wrds_update("facilityguarantor", "dealscan", engine, wrds_id) wrds_update("facilitypaymentschedule", "dealscan", engine, wrds_id) wrds_update("facilitysecurity", "dealscan", engine, wrds_id) wrds_update("facilitysponsor", "dealscan", engine, wrds_id) wrds_update("financialcovenant", "dealscan", engine, wrds_id) wrds_update("financialratios", "dealscan", engine, wrds_id) wrds_update("lendershares", "dealscan", engine, wrds_id) wrds_update("link_table", "dealscan", engine, wrds_id) wrds_update("lins", "dealscan", engine, wrds_id) wrds_update("marketsegment", "dealscan", engine, wrds_id) wrds_update("networthcovenant", "dealscan", engine, wrds_id) wrds_update("organizationtype", "dealscan", engine, wrds_id) wrds_update("package", "dealscan", engine, wrds_id)
#!/usr/bin/env python3 from sqlalchemy import create_engine, MetaData import os, sys dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") engine = create_engine("postgresql://" + host + "/" + dbname) sys.path.insert(0, '..') from wrds_fetch import wrds_update avail_years = range(2000, 2019) updated = wrds_update("rp_entity_mapping", "rpna", engine, wrds_id) def update_equities(year): updated = wrds_update("pr_equities_" + str(year), "rpna", engine, wrds_id, 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]
#!/usr/bin/env python3 from sqlalchemy import create_engine import os, sys dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") dbname = engine = create_engine("postgresql://" + host + "/" + dbname) sys.path.insert(0, '..') from wrds_fetch import wrds_update updated = wrds_update("wrds_gvkey", "ciq", engine, wrds_id, fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_gvkey (companyid)") updated = wrds_update("wciklink_gvkey", "wrdssec", engine, wrds_id) updated = wrds_update("wciklink_names", "wrdssec", engine, wrds_id) updated = wrds_update("wciklink_cusip", "wrdssec", engine, wrds_id) updated = wrds_update("wrds_cusip", "ciq", engine, wrds_id, fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_cusip (companyid)") updated = wrds_update("wrds_cik", "ciq", engine, wrds_id, fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_cik (companyid)") updated = wrds_update("wrds_keydev", "ciq", engine, wrds_id, fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_keydev (keydeveventtypeid)") engine.execute("CREATE INDEX ON ciq.wrds_keydev (companyid)")
#!/usr/bin/env python3 from sqlalchemy import create_engine import os, sys dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") dbname = engine = create_engine("postgresql://" + host + "/" + dbname) sys.path.insert(0, '..') from wrds_fetch import wrds_update wrds_update("globalvoteresults", "risk", engine, wrds_id) wrds_update("vavoteresults", "risk", engine, wrds_id) wrds_update("issrec", "risk", engine, wrds_id)
#!/usr/bin/env python3 from sqlalchemy import create_engine import os dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") dbname = engine = create_engine("postgresql://" + host + "/" + dbname) from wrds_fetch import wrds_update updated = wrds_update("wciklink_gvkey", "wrdssec", engine, wrds_id) updated = wrds_update("wciklink_names", "wrdssec", engine, wrds_id) updated = wrds_update("wciklink_cusip", "wrdssec", engine, wrds_id, drop="tmatch")
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 # Partially working, has to add cols http_X, nt_X, ac_X back updated = wrds_update( "nt", "audit", engine, wrds_id, drop="matchfy: matchqu: priorfy: closestfy: priorqu: http: ac: nt:") updated = wrds_update("auditnonreli", "audit", engine, wrds_id, drop="prior:match:") updated = wrds_update("bankrupt", "audit", engine, wrds_id, drop="match: closest: prior:") if updated:
""" the_table = Table(table, MetaData(), schema=schema, autoload=True, 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("amend", "tfn", engine, wrds_id) updated = wrds_update("avgreturns", "tfn", engine, wrds_id) updated = wrds_update("company", "tfn", engine, wrds_id, fix_cr=True) updated = wrds_update("form144", "tfn", engine, wrds_id) updated = wrds_update("header", "tfn", engine, wrds_id) # updated = wrds_update("idfhist", "tfn", engine, wrds_id) updated = wrds_update("idfnames", "tfn", engine, wrds_id) updated = wrds_update("rule10b5", "tfn", engine, wrds_id) updated = wrds_update("table1", "tfn", engine, wrds_id) updated = wrds_update("table2", "tfn", engine, wrds_id)
# updated = wrds_update("feed17change", "audit", engine, wrds_id) # updated = wrds_update("feed17del", "audit", engine, wrds_id) # updated = wrds_update("auditchange", "audit", engine, wrds_id, drop="matchfy: matchqu: priorfy: priorqu:") # if updated: # is_col_to_bool(engine, "audit", "auditchange") # updated = wrds_update("auditsox404", "audit", engine, wrds_id, drop="matchfy: matchqu: priorfy: priorqu:") # if updated: # is_col_to_bool(engine, "audit", "auditsox404") # updated = wrds_update("auditsox302", "audit", engine, wrds_id, drop="match: prior:") # if updated: # engine.execute(""" # ALTER TABLE audit.auditsox302 # ALTER COLUMN is_effective TYPE integer USING is_effective::integer;""") # is_col_to_bool(engine, "audit", "auditsox302") # updated = wrds_update("auditlegal", "audit", engine, wrds_id, drop="matchfy:matchqu:priorfy:priorqu:") # if updated: # # Takes a lot of time # is_col_to_bool(engine, "audit", "auditlegal") # # Partially working, has to add cols http_X, nt_X, ac_X back updated = wrds_update("nt", "audit", engine, wrds_id, drop="matchfy: matchqu: priorfy: priorqu:http:nt:ac:")
print(".", end="") import os dbname = os.getenv("PGDATABASE") print(".", end="") host = os.getenv("PGHOST", "localhost") print(".", end="") wrds_id = os.getenv("WRDS_ID") engine = create_engine("postgresql://" + host + "/" + dbname) print(".", end="") from wrds_fetch import wrds_update, run_file_sql print(".") # Update monthly data msf = wrds_update("msf", "crsp", engine, wrds_id, fix_missing=True) if msf: engine.execute("CREATE INDEX ON crsp.msf (permno, date)") msi = wrds_update("msi", "crsp", engine, wrds_id) msedelist = wrds_update("msedelist", "crsp", engine, wrds_id, fix_missing=True) mport = wrds_update("mport1", "crsp", engine, wrds_id) if mport: print("Getting ermport1") from wrds_fetch import get_process, wrds_process_to_pg sas_code = """ proc sql; CREATE TABLE ermport AS