# Part of Rearview (https://rearview.page). Copyright 2026 Phan Vu Chau.
# You may use, copy and change this script. It comes with no warranty.
"""Export Norgate Data to Rearview's import format.

Run it inside the Windows computer or virtual machine where Norgate Data Updater runs,
with Norgate's Python package installed (`pip install norgatedata`):

  python norgate_export.py --out C:\\RearviewData --index "S&P 500=SP500"

It writes a folder that Rearview reads with Data Library ▸ Import ▸ CSV Folder in the
Rearview Format… (or `rvw import`): securities.csv, tickers.csv, bars.csv, actions.csv,
membership.csv and manifest.json, plus norgate-export-report.txt, which says what it wrote
and anything to check. Run it again later and use Update from Folder… to bring the data up
to date.

  --out DIR            a new or empty folder to write (required)
  --database NAME      a Norgate database to export; repeat for more. Default:
                       "US Equities" and "US Equities Delisted"
  --watchlist NAME     also export a watchlist's symbols; repeat for more
  --symbol SYMBOL      also export one symbol, such as $SPX; repeat for more
  --index NAME=LABEL   write the index's membership by date under LABEL, such as
                       "S&P 500=SP500"; repeat for more. Needs a Norgate subscription that
                       includes historical index constituents.
  --from YYYY-MM-DD    the first day to export (default 2000-01-01)

What Norgate's interface doesn't give, the export can't either:

- Delisting reasons: every delisted stock gets the reason UNKNOWN, which Rearview prices
  as a performance delisting, with a warning.
- Past tickers: each security keeps the one symbol Norgate gives it now, so a delisted
  stock keeps Norgate's dated suffix (ABC-201503).
- Splits come from Norgate's capital adjustments. A capital event that doesn't look like a
  split, such as a spinoff, is exported as a split of the ratio Norgate adjusted by, which
  keeps a holding's value as Norgate's adjusted prices do, and the report lists it.

Before writing, the export rebuilds each security's total-return prices from the bars,
splits and dividends it is about to write and compares them with Norgate's own
total-return prices. Any security that differs is listed in the report.

Norgate's terms allow exporting data for your personal use on another computer. Most data
vendors' terms are personal-use only: never share the exported files.
"""

import argparse
import csv
import json
import math
import re
import sys
from datetime import date, timedelta
from fractions import Fraction
from pathlib import Path

DEFAULT_DATABASES = ["US Equities", "US Equities Delisted"]
DEFAULT_FROM = "2000-01-01"
TICKER = re.compile(r"^[A-Z0-9.\-]{1,15}$")
# A total-return close this far from Norgate's, relatively, means an action is off.
TOLERANCE = 0.002
# A split ratio fits when it is this close to Norgate's capital adjustment, relatively.
RATIO_FIT = 5e-4


def day(value):
    """'YYYY-MM-DD' from a Norgate date: numpy datetime64, datetime, date or text."""
    return str(value)[:10]


def number(value):
    """A price or cash amount as the import format writes it: at most six decimals."""
    text = f"{float(value):.6f}".rstrip("0").rstrip(".")
    return text if text not in ("", "-0") else "0"


def finite(*values):
    return all(v is not None and math.isfinite(float(v)) for v in values)


def fit_ratio(r):
    """The split (new, old) whose ratio new / old is `r`: the one with the smallest `old`
    up to 1,000 within RATIO_FIT, else the closest with `old` up to 1,000,000. The flag
    says whether it looks like an ordinary split: k-for-1, 1-for-k, or both terms at most 10,
    such as 3-for-2."""
    if not (r > 0 and math.isfinite(r)):
        raise ValueError(f"no split has ratio {r}")
    for old in range(1, 1001):
        new = round(r * old)
        if new >= 1 and abs(new / old - r) <= RATIO_FIT * r:
            g = math.gcd(new, old)
            new, old = new // g, old // g
            return new, old, (new == 1 or old == 1 or max(new, old) <= 10)
    f = Fraction(r).limit_denominator(1_000_000)
    return f.numerator, f.denominator, False


def kind_of(nd, symbol):
    if symbol.startswith("$"):
        return "INDEX"
    for subtype in (nd.subtype1, nd.subtype2, nd.subtype3):
        try:
            text = (subtype(symbol) or "").lower()
        except Exception:
            text = ""
        if "etf" in text or "exchange traded" in text:
            return "ETF"
    return "STOCK"


def exchange_of(name):
    n = (name or "").lower()
    if "nasdaq" in n:
        return "NASDAQ"
    if "arca" in n:
        return "NYSE_ARCA"
    if "american" in n or "amex" in n or "mkt" in n:
        return "NYSE_AMERICAN"
    if "nyse" in n:
        return "NYSE"
    if "cboe" in n or "bats" in n:
        return "CBOE"
    if "otc" in n or "pink" in n:
        return "OTC"
    return "OTHER"


def names_of(record):
    """The field names of a Norgate record (a numpy record, or a mapping)."""
    try:
        return list(record.dtype.names)
    except AttributeError:
        return list(record.keys())


def field(record, names):
    for name in names:
        if name in names_of(record):
            return record[name]
    raise KeyError(f"none of {names} in a Norgate record, which has {names_of(record)}")


def flag(record):
    """The value of a Norgate indicator series' record: its one field besides Date."""
    return record[[n for n in names_of(record) if n != "Date"][0]]


class Security:
    def __init__(self, symbol, delisted):
        self.symbol = symbol
        self.delisted = delisted
        self.id = None
        self.ticker = None
        self.bars = []  # (date, open, high, low, close, volume), as traded
        self.splits = []  # (date, new, old, plain)
        self.dividends = []  # (date, cash)
        self.membership = {}  # label -> [(from, to)]


class Report:
    def __init__(self):
        self.lines = []
        self.notes = {}

    def note(self, heading, line):
        self.notes.setdefault(heading, []).append(line)


def symbols_to_export(nd, args, report):
    delisted_dbs = [d for d in nd.databases() if d.lower().endswith("delisted")]
    delisted = set()
    for db in delisted_dbs:
        delisted.update(nd.database_symbols(db))
    chosen = []
    for db in args.database or DEFAULT_DATABASES:
        chosen.extend(nd.database_symbols(db))
    for wl in args.watchlist or []:
        chosen.extend(nd.watchlist_symbols(wl))
    chosen.extend(args.symbol or [])
    seen, out = set(), []
    for s in chosen:
        if s not in seen:
            seen.add(s)
            out.append(Security(s, s in delisted))
    report.lines.append(f"Symbols chosen: {len(out)} ({sum(s.delisted for s in out)} delisted)")
    return out


def read_prices(nd, sec, start, report):
    """Bars as traded, dividends and splits of `sec`, checked against Norgate's total-return
    prices. False when it has no usable bars."""
    adj = nd.StockPriceAdjustmentType
    pad = nd.PaddingType.NONE

    def series(setting):
        return nd.price_timeseries(sec.symbol, stock_price_adjustment_setting=setting,
                                   padding_setting=pad, start_date=start,
                                   timeseriesformat="numpy-recarray")

    raw = series(adj.NONE)
    skipped = 0
    for r in raw:
        o, h, lo, c, v = (field(r, [k]) for k in ("Open", "High", "Low", "Close", "Volume"))
        if not finite(o, h, lo, c) or min(o, h, lo, c) <= 0 or lo > min(o, c) or h < max(o, c):
            skipped += 1
            continue
        volume = int(round(float(v))) if finite(v) and float(v) > 0 else 0
        sec.bars.append((day(field(r, ["Date"])), float(o), float(h), float(lo), float(c), volume))
        cash = r["Dividend"] if "Dividend" in names_of(r) else 0
        if finite(cash) and float(cash) > 0:
            sec.dividends.append((day(field(r, ["Date"])), float(cash)))
    if skipped:
        report.note("Bars left out (a price missing, zero, or outside the day's range)",
                    f"{sec.symbol}: {skipped}")
    if not sec.bars:
        return False

    # Splits: where Norgate's capital adjustment factor (adjusted ÷ unadjusted close) jumps.
    capital = {day(field(r, ["Date"])): field(r, ["Close"]) / field(r, ["Unadjusted Close"])
               for r in series(adj.CAPITAL)
               if finite(field(r, ["Close"]), field(r, ["Unadjusted Close"]))
               and field(r, ["Unadjusted Close"]) > 0}
    events = {day(field(r, ["Date"])) for r in nd.capital_event_timeseries(
        sec.symbol, timeseriesformat="numpy-recarray") if finite(flag(r)) and flag(r) != 0}
    dates = [b[0] for b in sec.bars]
    for prev, d in zip(dates, dates[1:]):
        if d not in events or prev not in capital or d not in capital:
            continue
        r = capital[d] / capital[prev]
        if abs(r - 1) <= RATIO_FIT:
            continue
        new, old, plain = fit_ratio(r)
        sec.splits.append((d, new, old, plain))
        if not plain:
            report.note("Capital events that don't look like a split (check each: a spinoff "
                        "or reorganisation shows up this way)",
                        f"{sec.symbol} on {d}: {new}-for-{old} (Norgate's factor {r:.6f})")

    total_return = {day(field(r, ["Date"])): float(field(r, ["Close"])) for r in series(adj.TOTALRETURN)
                    if finite(field(r, ["Close"]))}
    check_total_return(sec, total_return, report)
    return True


def check_total_return(sec, theirs, report):
    """Rebuild total-return closes from the bars and actions about to be written, anchored
    at the last close as Norgate's are, and report the first day they part from Norgate's."""
    closes = [(b[0], b[4]) for b in sec.bars]
    splits = {d: new / old for d, new, old, _ in sec.splits}
    dividends = {}
    for d, cash in sec.dividends:
        dividends[d] = dividends.get(d, 0.0) + cash
    ours = [0.0] * len(closes)
    factor = 1.0
    for i in range(len(closes) - 1, -1, -1):
        d, c = closes[i]
        ours[i] = c * factor
        if i > 0:
            k = 1.0
            if d in splits:
                k /= splits[d]
            if d in dividends:
                prev = closes[i - 1][1] / splits.get(d, 1.0)
                if prev > dividends[d]:
                    k *= (prev - dividends[d]) / prev
            factor *= k
    worst = None
    for (d, _), mine in zip(closes, ours):
        t = theirs.get(d)
        if t and t > 0:
            dev = abs(mine / t - 1)
            if dev > TOLERANCE and (worst is None or dev > worst[1]):
                worst = (d, dev)
    if worst:
        report.note("Total-return prices that differ from Norgate's (an action is missing or "
                    "wrong near this day)", f"{sec.symbol}: {worst[1]:.2%} on {worst[0]}")


def read_membership(nd, sec, indexes, start):
    dates = [b[0] for b in sec.bars]
    for name, label in indexes:
        flags = nd.index_constituent_timeseries(sec.symbol, name, padding_setting=nd.PaddingType.NONE,
                                                start_date=start, timeseriesformat="numpy-recarray")
        member = {day(field(r, ["Date"])): finite(flag(r)) and flag(r) != 0 for r in flags}
        spans, open_from = [], None
        for d in dates:
            if member.get(d, False):
                if open_from is None:
                    open_from = d
            elif open_from is not None:
                spans.append((open_from, d))
                open_from = None
        if open_from is not None:
            end = (date.fromisoformat(dates[-1]) + timedelta(days=1)).isoformat() if sec.delisted else ""
            spans.append((open_from, end))
        if spans:
            sec.membership[label] = spans


def assign_tickers(secs, report):
    taken = {}
    traded = {s.symbol.upper() for s in secs if not s.symbol.startswith("$")}
    for sec in secs:
        ticker = sec.symbol.lstrip("$").upper()
        if sec.symbol.startswith("$") and ticker in traded:
            ticker = f"{ticker}.IDX"
        if not TICKER.match(ticker) or ticker in taken:
            fallback = f"N{sec.id}"
            report.note("Tickers changed (not valid in the import format, or already taken)",
                        f"{sec.symbol} → {fallback}")
            ticker = fallback
        taken[ticker] = sec
        sec.ticker = ticker


def write_csv(path, header, rows):
    with open(path, "w", newline="", encoding="utf-8") as f:
        w = csv.writer(f, lineterminator="\n")
        w.writerow(header)
        w.writerows(rows)


def export(nd, args, out):
    report = Report()
    report.lines.append(f"norgatedata {getattr(nd, '__version__', 'version unknown')}")
    indexes = []
    for spec in args.index or []:
        name, _, label = spec.rpartition("=")
        if not name or not re.match(r"^.{1,32}$", label):
            raise SystemExit(f"--index {spec!r}: write it as \"Index name=LABEL\", LABEL 1 to 32 characters")
        indexes.append((name, label))
    secs = symbols_to_export(nd, args, report)
    kept = []
    for n, sec in enumerate(secs, 1):
        if n % 500 == 0:
            print(f"{n} of {len(secs)}…", file=sys.stderr)
        sec.id = int(nd.assetid(sec.symbol))
        if not read_prices(nd, sec, args.start, report):
            report.note("Symbols with no bars from the start date", sec.symbol)
            continue
        if indexes and kind_of(nd, sec.symbol) != "INDEX":
            read_membership(nd, sec, indexes, args.start)
        kept.append(sec)
    assign_tickers(kept, report)

    securities, kinds, subtypes, exchanges = [], {}, set(), set()
    for sec in kept:
        kind = kind_of(nd, sec.symbol)
        kinds[kind] = kinds.get(kind, 0) + 1
        subtypes.add(str(nd.subtype1(sec.symbol)))
        exchange_name = nd.exchange_name(sec.symbol)
        exchanges.add(f"{exchange_name} → {exchange_of(exchange_name)}")
        sector = industry = ""
        if kind == "STOCK":
            sector = nd.classification_at_level(sec.symbol, "GICS", "Name", 1) or ""
            industry = nd.classification_at_level(sec.symbol, "GICS", "Name", 3) or ""
        delist_date, reason = (sec.bars[-1][0], "UNKNOWN") if sec.delisted else ("", "")
        securities.append([sec.id, nd.security_name(sec.symbol) or sec.ticker, kind,
                           "OTHER" if kind == "INDEX" else exchange_of(exchange_name),
                           sector, industry, delist_date, reason])

    write_csv(out / "securities.csv",
              ["id", "name", "kind", "exchange", "sector", "industry", "delist_date", "delist_reason"],
              sorted(securities))
    write_csv(out / "tickers.csv", ["id", "ticker", "from", "to"],
              sorted([s.id, s.ticker, s.bars[0][0], ""] for s in kept))
    write_csv(out / "bars.csv", ["id", "date", "open", "high", "low", "close", "volume"],
              ([s.id, b[0], number(b[1]), number(b[2]), number(b[3]), number(b[4]), b[5]]
               for s in sorted(kept, key=lambda s: s.id) for b in s.bars))
    actions = []
    for s in kept:
        actions.extend([s.id, d, "SPLIT", new, old, ""] for d, new, old, _ in s.splits)
        actions.extend([s.id, d, "DIVIDEND", number(cash), "", ""] for d, cash in s.dividends)
    write_csv(out / "actions.csv", ["id", "ex_date", "type", "value1", "value2", "ref_id"], sorted(actions))
    if indexes:
        write_csv(out / "membership.csv", ["index", "id", "from", "to"],
                  sorted([label, s.id, a, b] for s in kept for label, spans in s.membership.items()
                         for a, b in spans))
    (out / "manifest.json").write_text(json.dumps({"format": "rvw-import", "version": 1}) + "\n")

    report.lines += [
        f"Exported from {args.start}: {len(kept)} securities "
        f"({', '.join(f'{v} {k}' for k, v in sorted(kinds.items()))}), "
        f"{sum(len(s.bars) for s in kept)} bars, {sum(len(s.splits) for s in kept)} splits, "
        f"{sum(len(s.dividends) for s in kept)} dividends.",
        f"Delisted: {sum(s.delisted for s in kept)}, each with the reason UNKNOWN.",
    ]
    for label in sorted({label for _, label in indexes}):
        report.lines.append(f"Index {label}: {sum(1 for s in kept if label in s.membership)} securities "
                            "were members at some point.")
    report.note("Norgate's subtype1 values seen (STOCK unless it says ETF)", ", ".join(sorted(subtypes)))
    report.note("Norgate's exchange names seen, and what they became", ", ".join(sorted(exchanges)))
    text = "\n".join(report.lines) + "\n"
    for heading, lines in report.notes.items():
        text += f"\n{heading}:\n" + "".join(f"  {line}\n" for line in lines)
    (out / "norgate-export-report.txt").write_text(text, encoding="utf-8")
    return report


def main(argv=None, nd=None):
    p = argparse.ArgumentParser(description="Export Norgate Data to Rearview's import format.")
    p.add_argument("--out", required=True, type=Path)
    p.add_argument("--database", action="append")
    p.add_argument("--watchlist", action="append")
    p.add_argument("--symbol", action="append")
    p.add_argument("--index", action="append")
    p.add_argument("--from", dest="start", default=DEFAULT_FROM)
    args = p.parse_args(argv)
    try:
        date.fromisoformat(args.start)
    except ValueError:
        p.error(f"--from {args.start!r} is not a YYYY-MM-DD date")
    if args.out.exists() and any(args.out.iterdir()):
        p.error(f"{args.out} isn't empty; choose a new folder")
    if nd is None:
        try:
            import norgatedata as nd
        except ImportError:
            p.error("Norgate's Python package isn't installed: pip install norgatedata")
        if not nd.status():
            p.error("Norgate Data Updater isn't running; start it and try again")
    args.out.mkdir(parents=True, exist_ok=True)
    report = export(nd, args, args.out)
    print("\n".join(report.lines))
    print(f"Wrote {args.out}. Read {args.out / 'norgate-export-report.txt'} before importing.")


if __name__ == "__main__":
    main()