spb/firc0001_uqo

1"""N6 - suspected false-positive classes: financing instruments / buybacks / dividends (cluster F). Read-only."""2from __future__ import annotations3import sys4from pathlib import Path5sys.path.insert(0, str(Path(__file__).resolve().parent))6from common import *78FIN = r"repurchase|buy-?back|dividend|credit facility|revolving|term loan|notes? (due|offering)|at-the-market|senior notes|commercial paper"9r = Run("fp_classes")10con = connect(SPID_DB)11con.execute(f"""CREATE TEMP TABLE fm AS SELECT m.*, regexp_matches(lower(coalesce(project_name,'') || ' ' || coalesce(description,'')), '{FIN}') AS fin,12   regexp_extract(lower(coalesce(project_name,'') || ' ' || coalesce(description,'')), '({FIN})', 1) AS fin_term FROM project_mentions m""")13r.dump("financing_regex_by_item_type.csv", con, """14SELECT form_type, section_name item, project_type, count(*) mentions, count(*) FILTER (WHERE fin) matched, round(100.0*count(*) FILTER (WHERE fin)/count(*),2) pct,15       round(sum(amount_usd) FILTER (WHERE fin)/1e9,2) matched_amount_gusd, round(sum(amount_usd)/1e9,2) total_amount_gusd16FROM fm GROUP BY 1,2,3 ORDER BY 4 DESC""")17r.dump("financing_regex_by_item.csv", con, """18SELECT form_type, section_name item, count(*) mentions, count(*) FILTER (WHERE fin) matched, round(100.0*count(*) FILTER (WHERE fin)/count(*),2) pct,19       count(*) - count(*) FILTER (WHERE fin) mentions_excl_class20FROM fm GROUP BY 1,2 ORDER BY 1,3 DESC""")21r.dump("financing_regex_by_type.csv", con, """22SELECT project_type, count(*) mentions, count(*) FILTER (WHERE fin) matched, round(100.0*count(*) FILTER (WHERE fin)/count(*),2) pct,23       round(sum(amount_usd) FILTER (WHERE fin)/1e9,2) matched_amount_gusd24FROM fm GROUP BY 1 ORDER BY 2 DESC""")25r.dump("financing_regex_terms.csv", con, """SELECT fin_term, count(*) n, round(sum(amount_usd)/1e9,2) amount_gusd FROM fm WHERE fin GROUP BY 1 ORDER BY 2 DESC""")26r.dump("financing_regex_by_form.csv", con, """SELECT form_type, count(*) mentions, count(*) FILTER (WHERE fin) matched, round(100.0*count(*) FILTER (WHERE fin)/count(*),2) pct FROM fm GROUP BY 1 ORDER BY 1""")27# projects: representative name + description (arg_max confidence) and 'any mention matched'28con.execute(f"""CREATE TEMP TABLE fp AS SELECT p.*, regexp_matches(lower(coalesce(p.project_name,'') || ' ' || coalesce(p.description,'')), '{FIN}') AS fin_rep,29   coalesce(a.any_fin, FALSE) AS fin_any30   FROM projects p LEFT JOIN (SELECT md5(cik || '|' || project_type || '|' || CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END) project_id, bool_or(fin) any_fin31        FROM (SELECT *, lower(coalesce(try(locations[1]), '')) AS loc1, regexp_extract(lower(regexp_replace(project_name, '[^A-Za-z ]', '', 'g')), '([a-z]{{4,}})', 1) AS name_tok FROM fm) GROUP BY 1) a USING (project_id)""")32r.dump("table5_restated_excl_financing.csv", con, """33SELECT project_type, count(*) n, round(100.0*count(*)/(SELECT count(*) FROM fp),1) pct,34       count(*) FILTER (WHERE NOT fin_rep) n_excl_rep, round(100.0*count(*) FILTER (WHERE NOT fin_rep)/(SELECT count(*) FROM fp WHERE NOT fin_rep),1) pct_excl_rep,35       count(*) FILTER (WHERE NOT fin_any) n_excl_any, round(100.0*count(*) FILTER (WHERE NOT fin_any)/(SELECT count(*) FROM fp WHERE NOT fin_any),1) pct_excl_any,36       count(*) FILTER (WHERE fin_rep) matched_rep, round(100.0*count(*) FILTER (WHERE fin_rep)/count(*),2) pct_matched_rep,37       round(median(total_amount_usd)/1e6,1) median_amount_musd, round(median(total_amount_usd) FILTER (WHERE NOT fin_rep)/1e6,1) median_amount_musd_excl_rep38FROM fp GROUP BY 1 ORDER BY 2 DESC""")39S = {}40S["mentions_matched"] = con.execute("select count(*) filter (where fin), count(*), round(100.0*count(*) filter (where fin)/count(*),2) from fm").fetchone()41S["projects_matched_rep"] = con.execute("select count(*) filter (where fin_rep), count(*) filter (where fin_any), count(*) from fp").fetchone()42S["matched_amount_gusd"] = con.execute("select round(sum(amount_usd) filter (where fin)/1e9,2), round(sum(amount_usd)/1e9,2) from fm").fetchone()43S["unregistered_sales_matched"] = con.execute("select count(*) filter (where fin), count(*) from fm where section_name='unregistered_sales'").fetchone()44S["controls_matched"] = con.execute("select count(*) filter (where fin), count(*) from fm where section_name='controls_and_procedures'").fetchone()45S["financial_statements_matched"] = con.execute("select count(*) filter (where fin), count(*) from fm where section_name='financial_statements'").fetchone()46S["table4_rows_over_10pct"] = con.execute("select form_type, section_name, round(100.0*count(*) filter (where fin)/count(*),2) from fm group by 1,2 having 100.0*count(*) filter (where fin)/count(*) > 10 order by 3 desc").fetchall()47# Part I / Part II 'Item 2' collision test within 10-Q filings48con.execute("""CREATE TEMP TABLE q AS SELECT s.accession_number, s.section_name, s.word_count FROM src_sections s JOIN src_filings f USING (accession_number) WHERE f.form_type='10-Q' AND s.section_name IN ('unregistered_sales','md_and_a')""")49S["item2_collision"] = con.execute("""50WITH p AS (SELECT u.accession_number, u.word_count uw, m.word_count mw FROM q u JOIN q m USING (accession_number) WHERE u.section_name='unregistered_sales' AND m.section_name='md_and_a')51SELECT count(*) pairs, round(median(uw)) median_unreg_words, round(median(mw)) median_mdna_words,52       count(*) FILTER (WHERE uw > 3000) unreg_gt_3000, round(100.0*count(*) FILTER (WHERE uw > 3000)/count(*),2) pct_gt_3000,53       count(*) FILTER (WHERE uw > mw) unreg_longer_than_mdna, count(*) FILTER (WHERE mw < 500) mdna_lt_500,54       count(*) FILTER (WHERE uw > 3000 AND mw < 500) swapped_candidates55FROM p""").fetchone()56S["item2_collision_cols"] = ["pairs","median_unreg_words","median_mdna_words","unreg_gt_3000","pct_gt_3000","unreg_longer_than_mdna","mdna_lt_500","swapped_candidates"]57S["unregistered_sales_mentions_from_gt3000"] = con.execute("""select count(*), count(*) filter (where fin) from fm where section_name='unregistered_sales' and section_id in (select s.section_id from src_sections s where s.section_name='unregistered_sales' and s.word_count > 3000)""").fetchone()58S["unregistered_sales_sections_ledger_gt3000"] = con.execute("""select count(*) from extracted_sections e join src_sections s using(section_id) where s.section_name='unregistered_sales' and s.word_count > 3000""").fetchone()[0]59r.dump("unregistered_sales_wordcount_dist.csv", con, """select case when word_count<100 then '<100' when word_count<500 then '100-499' when word_count<1000 then '500-999' when word_count<3000 then '1000-2999' else '>=3000' end bucket, count(*) sections,60   count(*) filter (where section_id in (select section_id from extracted_sections)) ledgered from src_sections where section_name='unregistered_sales' group by 1 order by min(word_count)""")61r.dump("fin_sample_mentions.csv", con, """select mention_id, form_type, section_name, project_type, left(project_name,80) project_name, left(description,200) description, amount_usd, fin_term from fm where fin order by random() limit 60""")62con.execute(f"SELECT setseed(0.{SEED})")63r.write_json("summary.json", S)64r.finish()65