Data Tool
Oil Spill Investment Guidance - JSOSIF
I built this financial model for my university's student investment fund as a reference that our investment teams can turn to whenever a company they're covering or holding gets hit with an oil spill. It pulls real historical price data around past spills and measures how each stock reacted over the following year (the initial drop, the worst point, and whether and when it recovered) so the team has an empirical basis for reassessing their investment thesis.
Try the tool
Tool not loading? Open it in a new tab.
Oil Spill Investment Guidance
**The PowerShell Script used to retrieve the financial data from Yahoo Finance can be found at the bottom
I built this model for my university’s student investment fund ($300k AUM) where I lead a team called “Special Situations”. This model was designed so that our investment teams have somewhere to turn when a company they’re covering (or one we already own) gets hit with a special situation.
Everything starts with the baseline. Before I can say anything about how a stock reacted to an event, I need to know what it was worth right before the news hit the market. So the first thing the model does is pin down the baseline date, which I define as the last trading day before the incident was publicly disclosed. And the public disclosure distinction matters greatly. The event itself and the day the market finds out about it aren’t always the same, for instance, with Deepwater Horizon the rig exploded on the 20th but the media didn’t find out about it until the 21st. Therefore, I anchor to the disclosure, not the physical event, because disclosure is the first moment the price could actually react to anything. The baseline price is the adjusted close on that day, and I use adjusted close on purpose so that dividends and splits don’t distort the comparison down the line.
Once I’ve got that anchor, I measure where the stock is at a fixed set of intervals after disclosure: one day out, one month, four months, six, eight, and a full year. For each of those I grab the first trading day on or after the target and take that close. The return at each interval is then just that price divided by the baseline, minus one. So if BP sat around 60 at baseline and hit roughly 27 at its worst, that’s about a 55% raw drop. The one-day number tells me the market’s immediate reaction, the one-month and four-month numbers show whether the panic stuck or faded, and the one-year mark gives me a good idea about where the dust actually settled.
But a raw return on its own is misleading, and this is where most of the real work happens. If a stock fell 12% after an event, I can’t call that a 12% hit until I know what the rest of the market and the rest of the sector were doing over the exact same stretch. Say the whole market dropped 10% in those four months for reasons that had nothing to do with the spill, in that case the company only really underperformed by about 2%, and pinning the full 12% on the event would be a misstep. So for every interval I pull the same start and end dates for a broad market index, run the identical return calculation on it, and subtract that from the company’s raw return. What’s left is the market-adjusted return.
Then I run the same subtraction a second time against an industry benchmark, ideally a sector ETF that tracks the company’s peers. This one matters even more for something like oil, because when crude sells off, every energy equity drops with it whether or not they had a spill on their hands. Netting out the sector return leaves me with the industry-adjusted number, which is about the cleanest read I can get on the damage that’s genuinely specific to this company and this event rather than the market or the commodity moving underneath it. One rule I’m pretty strict about here is that the company and its benchmarks have to be in the same currency and on the same market. I’m not going to measure a Toronto-listed stock against an ETF listed in Tokyo, because at that point I could just be importing a currency swing into the calculation and calling it a reaction.
Alongside the interval returns, the model tracks the shape of the fall. It records the lowest close within a year after the event and the date it happened, so I can see where the share price actually bottomed out. This allows us to run a calculation of what the max drawdown was from the baseline. It also tracks the recovery date, i.e. the first day the stock climbed back to its pre-event price after having dropped below it. If it never gets back, that field stays blank, which is a finding in itself. There’s also a “next stable high” marker that tries to catch the moment the stock stops bleeding and starts a durable climb, which I define as the first close that breaks above the prior 20 trading days and then holds without giving back more than 5% over the following 10. I’m pretty confident with using those parameters in the case of an oil spill to say “the market has gotten over it”.
The aggregate tab is where all of that gets pulled together across every event in the set. It runs averages and medians for each interval and each type of return (raw, market-adjusted, industry-adjusted). The drawdowns and recovery times are also averaged there, and it breaks the one-year market-adjusted figure out by company type so I can see whether, say, pipelines behave differently from offshore drillers. I also keep a “clean subset” that drops events where something unrelated was obviously driving the tape.
That last point is exactly why I’ve been thinking about outliers. My take is that IQR is the right tool for this kind of data, not a Z-score. Stock returns can go parabolic, and with a Z-score a single enormous return drags the mean and the standard deviation up so far that the outlier basically justifies its own existence. IQR relies on the median and the middle half of the distribution.
None of this would be workable by hand. Pulling adjusted closes for every company, every benchmark, and every one of those dates across dozens of events would take forever and invite typos everywhere, so I run a script through PowerShell that automatically pulls the market data directly from Yahoo Finance, it grabs the close and the adjusted close at each interval, computes the lows and the recovery dates, and hands me a clean sheet I can paste straight into the model. That’s the piece that makes scaling this from ten events up to the dozens we’re planning actually realistic.
Right now this is a proof of concept sitting on ten oil spills, which is enough to prove the engine runs end to end, but the real guidance models my team will build out will each carry dozens and dozens of events. We plan to create separate models for every special situation you can think of, everything from natural disasters to cyberattacks to CEO sex scandals.
Below is the PowerShell Script:
import os
import sys
import time
import traceback
import pandas as pd
from dateutil.relativedelta import relativedelta
import yfinance as yf
INPUT_CSV = "events_input.csv" # the list of events (you edit this)
OUTPUT_DIR = "output" # where results are saved
EXCEL_NAME = "event_study_output.xlsx" # the output workbook
# The seven time points we pull, anchored to the DISCLOSURE date.
# (label, how-to-find, time-offset-from-disclosure)
TIME_POINTS = [
("Baseline (last trading day before disclosure)", "before", None),
("1 day after disclosure", "after", relativedelta(days=1)),
("1 month after disclosure", "after", relativedelta(months=1)),
("4 months after disclosure", "after", relativedelta(months=4)),
("6 months after disclosure", "after", relativedelta(months=6)),
("8 months after disclosure", "after", relativedelta(months=8)),
("12 months after disclosure","after", relativedelta(months=12)),
]
# --- Settings for the three COMPANY-ONLY stock metrics (low / recovery / NSH) ---
# These only affect the company stock, never the benchmarks, and never the seven
# price points above.
LOW_WINDOW_MONTHS = 12 # "lowest price within 1 year" looks over this window
RECOVERY_SEARCH_YEARS = 6 # how long to keep looking for a recovery / stable high
NSH_LOOKBACK_DAYS = 20 # "next stable high": must beat the prior 20 trading days
NSH_FORWARD_DAYS = 10 # ...and then hold over the next 10 trading days
NSH_MAX_DROP = 0.05 # ...without falling more than 5% during that hold
# ----------------------------------------------------------------------------
# SECTION 1: DOWNLOAD PRICES FROM YAHOO FINANCE
# ----------------------------------------------------------------------------
def _pick_field(raw, field, ticker):
"""Pull one price column (e.g. 'Close') out of whatever shape yfinance gave."""
if isinstance(raw.columns, pd.MultiIndex):
if field in raw.columns.get_level_values(0):
block = raw[field]
if isinstance(block, pd.DataFrame):
col = ticker if ticker in block.columns else block.columns[0]
return block[col]
return block
return None
return raw[field] if field in raw.columns else None
def download_prices(ticker, start_date, end_date, attempts=3):
"""
Download daily prices for one ticker.
Returns (DataFrame with columns ['close','adj_close'] indexed by date, note).
The DataFrame is None if the download failed.
Tries up to `attempts` times with a short, growing pause between tries,
because Yahoo occasionally rate-limits when many tickers are pulled in a row
(10 events x 3 tickers = 30 downloads).
"""
last_note = "unknown error"
for attempt in range(1, attempts + 1):
try:
raw = yf.download(
ticker,
start=start_date.strftime("%Y-%m-%d"),
end=end_date.strftime("%Y-%m-%d"),
auto_adjust=False, # keep a SEPARATE 'Adj Close' so we get BOTH numbers
progress=False,
threads=False,
)
except Exception as exc:
last_note = f"download error: {exc}"
time.sleep(1.5 * attempt) # wait a bit longer each retry
continue
if raw is None or len(raw) == 0:
last_note = "no data returned (check the ticker symbol)"
time.sleep(1.5 * attempt)
continue
close = _pick_field(raw, "Close", ticker)
adj = _pick_field(raw, "Adj Close", ticker)
if close is None and adj is None:
return None, "no Close/Adj Close columns found"
if close is None: # very rare; fall back so we still have something
close = adj
if adj is None:
adj = close
df = pd.DataFrame({"close": close, "adj_close": adj}).dropna(how="all")
if len(df) == 0:
last_note = "price data was empty after cleaning"
time.sleep(1.5 * attempt)
continue
df = df.sort_index()
df.index = pd.to_datetime(df.index).tz_localize(None) # plain dates, no timezone
note = f"ok ({len(df)} trading days)"
if attempt > 1:
note += f" [after {attempt} tries]"
return df, note
return None, last_note
# ----------------------------------------------------------------------------
# SECTION 2: FIND THE PRICE ON A GIVEN DATE
# ----------------------------------------------------------------------------
def price_before(df, ref_date):
"""Last trading day STRICTLY BEFORE ref_date -> (date, close, adj_close)."""
earlier = df.index[df.index < pd.Timestamp(ref_date)]
if len(earlier) == 0:
return None
d = earlier[-1]
return d, float(df.loc[d, "close"]), float(df.loc[d, "adj_close"])
def price_on_or_after(df, target_date):
"""First trading day ON OR AFTER target_date -> (date, close, adj_close)."""
later = df.index[df.index >= pd.Timestamp(target_date)]
if len(later) == 0:
return None
d = later[0]
return d, float(df.loc[d, "close"]), float(df.loc[d, "adj_close"])
def get_point(df, kind, disclosure_date, offset):
"""Return (date_used, close, adj_close) for one time point, or None if no data."""
if df is None:
return None
if kind == "before":
return price_before(df, disclosure_date)
else:
target = disclosure_date + offset
return price_on_or_after(df, target)
# ----------------------------------------------------------------------------
# SECTION 2b: COMPANY-ONLY STOCK METRICS (low / recovery / next stable high)
# ----------------------------------------------------------------------------
# These all run on the company's "Close" price (the real, Yahoo-matching price),
# NOT the adjusted close, because "did the price return to pre-incident levels"
# is about the actual price level. They are computed ONLY for the company stock.
def lowest_within_window(close, baseline_date, disclosure_date):
"""
Lowest CLOSE the stock reached in the year after the incident.
Returns (date, price) or (None, None). Looks at dates after the baseline up to
LOW_WINDOW_MONTHS past disclosure (so it captures the incident-driven low, not
an unrelated crash years later).
"""
window_end = disclosure_date + relativedelta(months=LOW_WINDOW_MONTHS)
post = close[(close.index > baseline_date) & (close.index <= window_end)]
if len(post) == 0:
return None, None
return post.idxmin(), float(post.min())
def recovery_date(close, baseline_date, baseline_price, disclosure_date):
"""
The first day the price climbs back to its pre-incident (baseline) level AFTER
it first fell below that level. Returns (date, status).
* If the stock never closed below baseline in the first year -> (None, ...)
meaning it never dropped because of the incident: leave blank.
* If it dropped but never came back within RECOVERY_SEARCH_YEARS -> (None,...)
meaning it never recovered: leave blank.
"""
one_year_end = disclosure_date + relativedelta(months=LOW_WINDOW_MONTHS)
horizon_end = disclosure_date + relativedelta(years=RECOVERY_SEARCH_YEARS)
first_year = close[(close.index > baseline_date) & (close.index <= one_year_end)]
dropped = first_year[first_year < baseline_price]
if len(dropped) == 0:
return None, "never closed below baseline within 1 year (no incident drop)"
first_drop = dropped.index[0]
search = close[(close.index >= first_drop) & (close.index <= horizon_end)]
recovered = search[search >= baseline_price]
if len(recovered) == 0:
return None, f"did not return to baseline within {RECOVERY_SEARCH_YEARS} years"
return recovered.index[0], "ok"
def next_stable_high(close, baseline_date, baseline_price, low_date, disclosure_date):
"""
The date the stock 'started to consistently regain its price without
perpetually falling'. Operationally: starting at the post-incident low, the
first day whose Close beats the highest Close of the prior NSH_LOOKBACK_DAYS
trading days AND then does not fall more than NSH_MAX_DROP over the next
NSH_FORWARD_DAYS trading days. Only meaningful if the stock actually dropped.
Returns a date or None.
"""
# Only applies if there was an incident-driven drop below baseline.
one_year_end = disclosure_date + relativedelta(months=LOW_WINDOW_MONTHS)
first_year = close[(close.index > baseline_date) & (close.index <= one_year_end)]
if len(first_year[first_year < baseline_price]) == 0 or low_date is None:
return None
horizon_end = disclosure_date + relativedelta(years=RECOVERY_SEARCH_YEARS)
idx = close.index
start_pos = idx.get_loc(low_date)
n = len(close)
for i in range(start_pos, n):
if idx[i] > horizon_end:
break
if i - NSH_LOOKBACK_DAYS < 0 or i + NSH_FORWARD_DAYS >= n:
continue
price_i = float(close.iloc[i])
prior_high = float(close.iloc[i - NSH_LOOKBACK_DAYS:i].max())
if price_i > prior_high:
forward_low = float(close.iloc[i + 1:i + 1 + NSH_FORWARD_DAYS].min())
if forward_low >= price_i * (1 - NSH_MAX_DROP):
return idx[i]
return None
# ----------------------------------------------------------------------------
# SECTION 3: PROCESS ONE EVENT
# ----------------------------------------------------------------------------
def clean_ticker(val):
"""Return a tidy ticker string, or '' if the cell is blank/missing."""
if pd.isna(val):
return ""
s = str(val).strip()
return "" if s.lower() in ("", "nan", "none") else s
def process_one_event(row):
"""Pull all prices for a single event. Returns (clean_rows, detailed_rows, summary)."""
event_id = str(row["event_id"]).strip()
company = str(row["company"]).strip()
c_ticker = clean_ticker(row.get("ticker"))
b_ticker = clean_ticker(row.get("broad_benchmark")) # optional
i_ticker = clean_ticker(row.get("industry_benchmark")) # optional
print(f"\n--- {event_id} ({company}: {c_ticker or '???'},"
f" {b_ticker or 'no broad'}, {i_ticker or 'no industry'}) ---")
# Disclosure date drives everything; fall back to event_date if it is blank.
disc = row.get("public_disclosure_date")
if pd.notna(disc) and str(disc).strip():
disclosure_date = pd.Timestamp(str(disc).strip())
else:
disclosure_date = pd.Timestamp(str(row["event_date"]).strip())
print(" (no disclosure date given; using event_date instead)")
# Benchmarks only need ~13 months of data (for the seven price points).
short_start = disclosure_date - relativedelta(days=15)
short_end = disclosure_date + relativedelta(months=13) + relativedelta(days=15)
# The COMPANY needs a longer history so we can find a recovery / stable high
# that may happen well after the first year.
long_end = disclosure_date + relativedelta(years=RECOVERY_SEARCH_YEARS) \
+ relativedelta(days=30)
# Company is required; benchmarks are optional (blank = simply skipped).
if c_ticker:
c_df, c_note = download_prices(c_ticker, short_start, long_end)
else:
c_df, c_note = None, "no company ticker given"
b_df, b_note = (download_prices(b_ticker, short_start, short_end)
if b_ticker else (None, "not provided"))
i_df, i_note = (download_prices(i_ticker, short_start, short_end)
if i_ticker else (None, "not provided"))
print(f" company {c_ticker or '(none)':<10} -> {c_note}")
print(f" broad {b_ticker or '(none)':<10} -> {b_note}")
print(f" industry {i_ticker or '(none)':<10} -> {i_note}")
clean_rows, detailed_rows = [], []
for label, kind, offset in TIME_POINTS:
target_date = (disclosure_date if kind == "before"
else disclosure_date + offset)
c = get_point(c_df, kind, disclosure_date, offset)
b = get_point(b_df, kind, disclosure_date, offset)
i = get_point(i_df, kind, disclosure_date, offset)
# date "used" on the clean sheet = the company's actual trading date
date_used = c[0].date().isoformat() if c else "NO DATA"
notes = []
if c is None: notes.append(f"company '{c_ticker}' has no price here")
if b_ticker and b is None: notes.append(f"broad '{b_ticker}' has no price here")
if i_ticker and i is None: notes.append(f"industry '{i_ticker}' has no price here")
# warn if a benchmark resolved to a different trading day than the company
for tag, x in (("broad", b), ("industry", i)):
if c and x and x[0] != c[0]:
notes.append(f"{tag} used {x[0].date()} (different trading day)")
clean_rows.append({
"event_id": event_id,
"company": company,
"time_point": label,
"target_date": target_date.date().isoformat(),
"date_used": date_used,
"company_ticker": c_ticker,
"company_close": round(c[1], 4) if c else None,
"company_adj_close": round(c[2], 4) if c else None,
"broad_ticker": b_ticker,
"broad_close": round(b[1], 4) if b else None,
"broad_adj_close": round(b[2], 4) if b else None,
"industry_ticker": i_ticker,
"industry_close": round(i[1], 4) if i else None,
"industry_adj_close": round(i[2], 4) if i else None,
"notes": "; ".join(notes) if notes else "OK",
})
# detailed (one row per ticker) for a clean audit trail
for role, tkr, x in (("company", c_ticker, c),
("broad_benchmark", b_ticker, b),
("industry_benchmark", i_ticker, i)):
detailed_rows.append({
"event_id": event_id,
"time_point": label,
"target_date": target_date.date().isoformat(),
"role": role,
"ticker": tkr,
"date_used": x[0].date().isoformat() if x else "NO DATA",
"close": round(x[1], 4) if x else None,
"adj_close": round(x[2], 4) if x else None,
})
# ------------------------------------------------------------------
# COMPANY-ONLY metrics: lowest price in year 1, recovery date, stable high.
# Computed on the company's Close series only (benchmarks untouched).
# ------------------------------------------------------------------
summary = {
"event_id": event_id,
"company": company,
"ticker": c_ticker,
"baseline_date": None,
"baseline_close": None,
"low_1y_close": None,
"low_1y_date": None,
"recovery_date": None,
"next_stable_high_date": None,
"notes": "",
}
if c_df is not None:
close = c_df["close"]
bl = price_before(c_df, disclosure_date) # (date, close, adj_close)
if bl is not None:
baseline_date, baseline_price = bl[0], bl[1]
summary["baseline_date"] = baseline_date.date().isoformat()
summary["baseline_close"] = round(baseline_price, 4)
low_date, low_price = lowest_within_window(close, baseline_date, disclosure_date)
if low_date is not None:
summary["low_1y_date"] = low_date.date().isoformat()
summary["low_1y_close"] = round(low_price, 4)
rec_date, rec_status = recovery_date(close, baseline_date, baseline_price, disclosure_date)
summary["recovery_date"] = rec_date.date().isoformat() if rec_date is not None else None
nsh = next_stable_high(close, baseline_date, baseline_price, low_date, disclosure_date)
summary["next_stable_high_date"] = nsh.date().isoformat() if nsh is not None else None
# a short, plain note explaining any blanks
note_bits = []
if rec_date is None:
note_bits.append(rec_status) # why recovery is blank
if summary["next_stable_high_date"] is None and rec_date is None \
and "no incident drop" in rec_status:
note_bits.append("no stable-high (stock never dropped)")
summary["notes"] = "; ".join(note_bits) if note_bits else "OK"
else:
summary["notes"] = "no baseline price (no trading day before disclosure)"
else:
summary["notes"] = f"company '{c_ticker}' returned no data"
return clean_rows, detailed_rows, summary
# ----------------------------------------------------------------------------
# SECTION 4: WRITE THE EXCEL WORKBOOK
# ----------------------------------------------------------------------------
def write_excel(clean_df, detailed_df, summary_df, excel_path):
readme = pd.DataFrame({
"How to read this workbook": [
"This file lists the prices pulled from Yahoo Finance for each event.",
"",
"SHEETS:",
" 'Prices (clean)' - one row per time point; the company, broad, and",
" industry prices side by side. Start here.",
" 'Prices (detailed)' - one row per ticker per time point (audit trail).",
" 'Lows & Recovery' - one row per COMPANY stock with its lowest price in",
" the year after the incident, plus recovery dates.",
"",
"TWO PRICE COLUMNS for every ticker:",
" Close = the real closing price that day. Matches Yahoo Finance.",
" USE THIS as your main number and to verify the data.",
" Adj Close = re-adjusted for all dividends/splits paid since that date.",
" For old dates this is much lower than Close and it changes",
" over time. Only use it if you want a dividend-adjusted basis.",
"",
"TIME POINTS are anchored to the public disclosure date:",
" Baseline = last trading day BEFORE disclosure; then 1 day, 1, 4, 6, 8,",
" and 12 months AFTER disclosure (first trading day on/after each target).",
"",
"'LOWS & RECOVERY' SHEET (company stock only, on the real Close price):",
" low_1y_close / low_1y_date = the lowest close in the 12 months after the",
" incident, and the date (YYYY-MM-DD) it happened.",
" recovery_date = first day the price climbed back to its baseline level",
" AFTER first falling below it. Blank if the stock never dropped because",
" of the incident, or never returned to baseline within "
+ str(RECOVERY_SEARCH_YEARS) + " years.",
" next_stable_high_date = when the stock started to consistently regain",
" ground without perpetually falling (first close above the prior 20",
" trading days that then holds within 5% for 10 trading days). Blank if",
" the stock never dropped.",
"",
"Always read the 'notes' column for any data warnings.",
]
})
try:
with pd.ExcelWriter(excel_path, engine="openpyxl") as writer:
readme.to_excel(writer, sheet_name="Read Me", index=False)
clean_df.to_excel(writer, sheet_name="Prices (clean)", index=False)
detailed_df.to_excel(writer, sheet_name="Prices (detailed)", index=False)
summary_df.to_excel(writer, sheet_name="Lows & Recovery", index=False)
# widen columns a little so the sheets are readable
for sheet_name, frame in (("Prices (clean)", clean_df),
("Prices (detailed)", detailed_df),
("Lows & Recovery", summary_df)):
ws = writer.sheets[sheet_name]
for col_idx, col in enumerate(frame.columns, start=1):
try:
maxlen = frame[col].astype(str).str.len().max()
if pd.isna(maxlen):
maxlen = 12
width = max(12, min(34, max(int(maxlen) + 2, len(str(col)) + 2)))
except Exception:
width = 16
ws.column_dimensions[ws.cell(row=1, column=col_idx).column_letter].width = width
except PermissionError:
print("\n*** ERROR: could not save the Excel file. ***")
print(f" '{excel_path}' looks like it is OPEN in Excel.")
print(" Close it in Excel and run the script again.\n")
return False
except Exception as exc:
print(f"\n*** ERROR writing Excel: {exc} ***\n")
return False
return True
# ----------------------------------------------------------------------------
# SECTION 5: MAIN
# ----------------------------------------------------------------------------
def main():
print("=" * 70)
print(" JSOSIF Special-Situations Event Study - price puller")
print("=" * 70)
os.makedirs(OUTPUT_DIR, exist_ok=True)
if not os.path.exists(INPUT_CSV):
print(f"ERROR: '{INPUT_CSV}' not found next to this script.")
sys.exit(1)
events = pd.read_csv(INPUT_CSV)
required = ["event_id", "company", "ticker", "event_date",
"public_disclosure_date", "broad_benchmark",
"industry_benchmark", "currency", "notes"]
missing_cols = [c for c in required if c not in events.columns]
if missing_cols:
print(f"ERROR: your CSV is missing these columns: {missing_cols}")
print(f" It must have exactly this header row:\n {','.join(required)}")
sys.exit(1)
print(f"Loaded {len(events)} event(s) from {INPUT_CSV}.")
all_clean, all_detailed, all_summary = [], [], []
event_summary = [] # (event_id, cells filled, cells expected)
total = len(events)
for n, (_, row) in enumerate(events.iterrows(), start=1):
print(f"\n[{n}/{total}]", end="")
# how many tickers did this row actually supply? (company + optional benchmarks)
n_tickers = sum(1 for col in ("ticker", "broad_benchmark", "industry_benchmark")
if clean_ticker(row.get(col)))
expected = 7 * max(1, n_tickers)
try:
clean_rows, detailed_rows, summary_row = process_one_event(row)
all_clean.extend(clean_rows)
all_detailed.extend(detailed_rows)
all_summary.append(summary_row)
got = sum(1 for r in detailed_rows if r["close"] is not None)
event_summary.append((str(row.get("event_id")), got, expected))
except Exception:
print(f" UNEXPECTED ERROR on {row.get('event_id')}:")
traceback.print_exc()
event_summary.append((str(row.get("event_id")), 0, expected))
# brief pause so we don't hammer Yahoo across many events
if n < total:
time.sleep(0.6)
clean_df = pd.DataFrame(all_clean)
detailed_df = pd.DataFrame(all_detailed)
summary_df = pd.DataFrame(all_summary)
excel_path = os.path.join(OUTPUT_DIR, EXCEL_NAME)
ok = write_excel(clean_df, detailed_df, summary_df, excel_path)
# Print a quick scoreboard so you can see which events pulled cleanly.
print("\n" + "-" * 70)
print(" DATA PULLED PER EVENT (price cells filled / expected; 7 per ticker):")
for eid, got, exp in event_summary:
mark = "OK " if got == exp else ("PARTIAL" if got > 0 else "NONE")
print(f" {mark:8} {eid:<30} {got}/{exp}")
print("-" * 70)
print("\n" + "=" * 70)
if ok:
print(" DONE.")
print(f" Workbook: {excel_path}")
print(" Open the 'Prices (clean)' sheet. The 'Close' columns match Yahoo.")
print(" Check the 'notes' column for any events that need a manual pull.")
else:
print(" Finished WITH ERRORS - see messages above.")
print("=" * 70)
if __name__ == "__main__":
main()