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
