import re import os import csv import email import quopri import base64 from io import BytesIO from datetime import datetime, timezone from bs4 import BeautifulSoup import pandas as pd import sys def load_html(file_path): """Load HTML content from either a plain .html or .mht/.mhtml file. Returns (html_content, download_date_str) where download_date_str may be None.""" with open(file_path, 'rb') as f: raw = f.read() # Detect MHT/MHTML by looking for MIME headers try: header_chunk = raw[:2048].decode('utf-8', errors='ignore') except Exception: header_chunk = '' if 'MIME-Version:' in header_chunk or 'Content-Type: multipart' in header_chunk: # Parse as MIME message msg = email.message_from_bytes(raw) download_date = msg.get('Date') # Standard MIME Date header for part in msg.walk(): ct = part.get_content_type() if ct == 'text/html': payload = part.get_payload(decode=True) charset = part.get_content_charset() or 'utf-8' return payload.decode(charset, errors='replace'), download_date # Fallback: try decoding the whole thing return raw.decode('utf-8', errors='replace'), download_date else: # Plain HTML — fall back to file modification time mtime = os.path.getmtime(file_path) download_date = datetime.fromtimestamp(mtime, tz=timezone.utc).strftime('%a, %d %b %Y %H:%M:%S %z') + ' (file modification time)' return raw.decode('utf-8', errors='replace'), download_date def parse_stock_price(texts): """Extract the current share price from texts like '$238.16' which appears after the ticker symbol 'QCOM'.""" price_regex = re.compile(r'^\$(\d{1,6}(?:\.\d{1,4})?)$') for t in texts: m = price_regex.match(t.strip()) if m: return float(m.group(1)) return None def parse_html(html_path): html_content, download_date = load_html(html_path) soup = BeautifulSoup(html_content, 'html.parser') texts = list(soup.stripped_strings) # regex for date: Jan 1, 2020 or Oct 11, 2013 date_regex = re.compile(r'^[A-Z][a-z]{2}\s\d{1,2},\s\d{4}$') stock_price = parse_stock_price(texts) grants = [] current_grant = None i = 0 while i < len(texts): s = texts[i] if date_regex.match(s): # Check if vesting row if i + 1 < len(texts) and '%' in texts[i+1]: if current_grant: # Amount is usually the 3rd column: Date, %, Amount, Value qty_str = texts[i+2].replace(',', '') if re.match(r'^\d+$', qty_str): v_qty = int(qty_str) current_grant['Vesting Schedule'].append({ 'Date': s, 'Amount': v_qty }) i += 3 continue # Check if grant row elif i + 2 < len(texts): qty_str = texts[i+1].replace(',', '') price_str = texts[i+2] if re.match(r'^\d+$', qty_str) and price_str.startswith('$'): if current_grant: grants.append(current_grant) current_grant = { 'Grant Date': s, 'Granted Quantity': int(qty_str), 'Shares_circle': 0, 'Vested_circle': 0, 'Unvested_circle': 0, 'Tax Track': '', 'Average Price': '', 'Vesting Schedule': [] } i += 2 continue if current_grant: if s == "Shares": if i + 1 < len(texts): avail_str = texts[i+1].replace(',', '') if re.match(r'^\d+$', avail_str): current_grant['Shares_circle'] = int(avail_str) elif s == "Vested": if i + 1 < len(texts): vested_str = texts[i+1].replace(',', '') if re.match(r'^\d+$', vested_str): current_grant['Vested_circle'] = int(vested_str) elif s == "Unvested": if i + 1 < len(texts): unvested_str = texts[i+1].replace(',', '') if re.match(r'^\d+$', unvested_str): current_grant['Unvested_circle'] = int(unvested_str) elif s == "Tax Track": if i + 1 < len(texts): current_grant['Tax Track'] = texts[i+1] elif s == "Average Price": if i + 1 < len(texts): current_grant['Average Price'] = texts[i+1] i += 1 if current_grant: grants.append(current_grant) return grants, stock_price, download_date def compute_totals(grants, stock_price): """Return (total_vested_shares, total_unvested_shares, summary_str).""" total_vested = sum( g.get('Shares_circle', 0) + g.get('Vested_circle', 0) for g in grants ) total_unvested = sum(g.get('Unvested_circle', 0) for g in grants) total_shares = total_vested + total_unvested if stock_price: vested_val = round(total_vested * stock_price) unvested_val = round(total_unvested * stock_price) total_val = round(total_shares * stock_price) price_note = f" (@ ${stock_price:.2f}/share)" summary = ( f"Portfolio Summary{price_note}:\n" f" Vested (available for sale): {total_vested:,} shares = ${vested_val:,}\n" f" Unvested (future vests): {total_unvested:,} shares = ${unvested_val:,}\n" f" Total: {total_shares:,} shares = ${total_val:,}" ) else: summary = ( f"Portfolio Summary (share price not found):\n" f" Vested (available for sale): {total_vested:,} shares\n" f" Unvested (future vests): {total_unvested:,} shares\n" f" Total: {total_shares:,} shares" ) return total_vested, total_unvested, total_shares, summary def process_grants(grants, output_excel, quiet=False, download_date=None): from openpyxl import Workbook rows = [] for g in grants: unvested = g.get('Unvested_circle', 0) future_vests = [] unvested_remaining = unvested for vest in reversed(g['Vesting Schedule']): if unvested_remaining <= 0: break future_vests.insert(0, f"{vest['Date']} ({vest['Amount']})") unvested_remaining -= vest['Amount'] # Available for sale includes the base "Shares" plus any unexercised "Vested" avail = g.get('Shares_circle', 0) + g.get('Vested_circle', 0) vested_avail = avail rows.append({ 'Grant Date': g['Grant Date'], 'Granted Quantity': g['Granted Quantity'], 'Available for Sale': avail, 'Tax Track': g['Tax Track'], 'Average Price': g['Average Price'], 'Vested Available': vested_avail, 'Unvested': unvested, 'Future Vests': ", ".join(future_vests) }) df = pd.DataFrame(rows) # Write xlsx with download date as first row wb = Workbook() ws = wb.active if download_date: ws.append([f"Download date: {download_date}"]) ws.append([]) # blank separator row ws.append(list(df.columns)) for row in df.itertuples(index=False): ws.append(list(row)) wb.save(output_excel) # Write csv with download date as first row (properly quoted via csv.writer) output_csv = output_excel.rsplit('.', 1)[0] + '.csv' with open(output_csv, 'w', encoding='utf-8', newline='') as f: writer = csv.writer(f) if download_date: writer.writerow([f"Download date: {download_date}"]) writer.writerow([]) # blank separator row df.to_csv(f, index=False) if not quiet: print(f"Successfully processed {len(grants)} grants and saved to {output_excel} and {output_csv}.") if len(rows) > 0: print("\nPreview of first 3 rows:") print(df.head(3).to_string()) if __name__ == "__main__": gui_mode = False if len(sys.argv) >= 3: input_html = sys.argv[1] output_xlsx = sys.argv[2] grants, stock_price, download_date = parse_html(input_html) missing_tax_track = any(not g.get('Tax Track') for g in grants) if not grants or missing_tax_track: print("ERROR: Incomplete or missing grant data detected.", file=sys.stderr) print("This usually happens if the HTML file was saved incorrectly or grants were not fully expanded.\n", file=sys.stderr) print("Please follow these exact steps to download the correct data:", file=sys.stderr) print("1. Navigate to: https://www.capital-m.co.il/C-MClient/op2/equity/rsu", file=sys.stderr) print("2. Ensure you are logged in and the page is fully loaded.", file=sys.stderr) print("3. CRITICAL: Expand every single grant row so that the 'More Info' and 'Vesting dates' sections are visible.", file=sys.stderr) print("4. Right-click anywhere on the page and select 'Save As...'", file=sys.stderr) print("5. Ensure the save format is set to 'Webpage, Complete'.", file=sys.stderr) print("6. Run this script again on the newly saved HTML file.", file=sys.stderr) sys.exit(1) process_grants(grants, output_xlsx, download_date=download_date) _, _, _, summary = compute_totals(grants, stock_price) print(summary) else: gui_mode = True try: import tkinter as tk from tkinter import filedialog, messagebox import webbrowser root = tk.Tk() root.withdraw() # Hide the main window while True: input_html = filedialog.askopenfilename( title="Select ESOP HTML or MHT File", filetypes=[ ("Web files", "*.html *.htm *.mht *.mhtml"), ("HTML files", "*.html *.htm"), ("MHT files", "*.mht *.mhtml"), ("All files", "*.*") ] ) if not input_html: sys.exit(0) grants, stock_price, download_date = parse_html(input_html) missing_tax_track = any(not g.get('Tax Track') for g in grants) if not grants or missing_tax_track: url = "https://www.capital-m.co.il/C-MClient/op2/equity/rsu" msg = ( "ERROR: Incomplete or missing grant data detected.\n" "This usually happens if the HTML file was saved incorrectly or grants were not fully expanded.\n\n" "Please follow these exact steps to download the correct data:\n" f"1. Navigate to: {url}\n" "2. Ensure you are logged in and the page is fully loaded.\n" "3. CRITICAL: Expand every single grant row so that the 'More Info' and 'Vesting dates' sections are visible.\n" "4. Right-click anywhere on the page and select 'Save As...'\n" "5. Ensure the save format is set to 'Webpage, Complete'.\n" "6. Run this script again on the newly saved HTML file." ) dialog = tk.Toplevel(root) dialog.title("Parsing Error") dialog.geometry("600x350") dialog.columnconfigure(0, weight=1) dialog.rowconfigure(0, weight=1) text_widget = tk.Text(dialog, wrap="word", bg=dialog.cget("bg"), relief="flat", font=("Arial", 11)) text_widget.grid(row=0, column=0, sticky="nsew", padx=10, pady=10) parts = msg.split(url) text_widget.insert(tk.END, parts[0]) text_widget.insert(tk.END, url, "link") text_widget.tag_config("link", foreground="blue", underline=True) text_widget.tag_bind("link", "", lambda e: webbrowser.open(url)) text_widget.tag_bind("link", "", lambda e: text_widget.config(cursor="hand2")) text_widget.tag_bind("link", "", lambda e: text_widget.config(cursor="")) text_widget.insert(tk.END, parts[1]) text_widget.config(state="disabled") button_frame = tk.Frame(dialog) button_frame.grid(row=1, column=0, pady=10) result = [False] def on_retry(event=None): result[0] = True dialog.destroy() def on_exit(event=None): result[0] = False dialog.destroy() btn_retry = tk.Button(button_frame, text="Pick a different file", command=on_retry, font=("Arial", 10, "bold")) btn_retry.pack(side="left", padx=10) btn_exit = tk.Button(button_frame, text="Exit", command=on_exit, font=("Arial", 10)) btn_exit.pack(side="left", padx=10) dialog.protocol("WM_DELETE_WINDOW", on_exit) dialog.lift() dialog.focus_force() dialog.grab_set() root.wait_window(dialog) if result[0]: continue else: sys.exit(0) # Parsing succeeded, ask for output file output_xlsx = filedialog.asksaveasfilename( title="Save Output Excel File", defaultextension=".xlsx", filetypes=[("Excel files", "*.xlsx"), ("All files", "*.*")] ) if not output_xlsx: sys.exit(0) process_grants(grants, output_xlsx, quiet=True, download_date=download_date) _, _, _, summary = compute_totals(grants, stock_price) messagebox.showinfo( "Success", f"Successfully processed {len(grants)} grants.\nSaved to:\n{output_xlsx}\n\n{summary}" ) break except ImportError: print("Usage: python parse_esop.py ") sys.exit(1)