Building FX-Insight: Daily FX Market Data Automation

What FX-Insight does

Every run, the script opens a fresh TradingView websocket session for each tracked symbol — the dollar index, major FX pairs, oil, gold, NIFTY, SENSEX, NASDAQ-100, and the S&P 500 — pulls the last few daily candles, and works out which one is the most recently completed session rather than whatever bar happens to be forming right now. In parallel it scrapes FII/DII (foreign and domestic institutional investor) flow data from Moneycontrol, with NSE and NSDL as fallback and supplementary sources. Once everything is collected, it appends one new row per sheet to a tracked Forex_Insights.xlsx workbook, formats the cells, uploads the file to Drive, and posts a summary to Slack.

The hard part: knowing which candle is actually closed

TradingView streams daily candles stamped at their open time in each exchange's own reference timezone, and different markets close at different UTC hours — FX/oil/gold roll at the New York close, India at NSE close, US indices at the NYSE close, all of it shifting an hour with daylight saving. Naively taking "the latest candle" risks grabbing a bar that's still forming. The script instead computes a per-market UTC cutoff hour, filters out any candle whose session hasn't closed yet, and only then takes the last one:

def session_close_utc_hour(market: str) -> int:
    """Return the UTC hour after which a market's daily bar is complete."""
    summer = _dst_active()
    if market in ("FX", "OIL", "GOLD"):
        return 21 if summer else 22
    if market == "INDIA":
        return 10
    if market == "US":
        return 20 if summer else 21
    return 21


def last_completed_candles(key, ordered):
    market = SYMBOL_MARKET.get(key, "FX")
    close_hour = session_close_utc_hour(market)
    now_utc = datetime.utcnow()
    cutoff = now_utc.replace(hour=close_hour, minute=0, second=0, microsecond=0)
    if now_utc < cutoff:
        cutoff -= timedelta(days=1)
    complete = [c for c in ordered if datetime.utcfromtimestamp(c["ts"]) < cutoff]
    if not complete:
        complete = ordered
    return complete[-1], complete[-2] if len(complete) > 1 else complete[-1]

A companion function, market_tz_offset, converts each candle's UTC open timestamp back to the exchange-local calendar date — nudging FX/oil/gold forward to the next UTC midnight, shifting India by +5:30, and adjusting US by the EDT/EST offset — so every sheet is labelled with the date a trader would actually recognize, not a UTC date that's off by one depending on the market.

Fetching 20+ symbols concurrently without getting rate-limited

Each symbol gets its own websocket thread, staggered by a small launch delay so TradingView doesn't see a burst of simultaneous connections. If a symbol comes back with an HTTP 429, the fetch retries with linear backoff up to a fixed retry count before giving up and logging a failure — the run still completes and writes whatever data it did manage to collect rather than failing the whole script over one flaky symbol.

def fetch_all_symbols() -> None:
    threads = []
    for key, symbol in SYMBOLS.items():
        thread = threading.Thread(target=fetch_tradingview_symbol, args=(key, symbol), daemon=True)
        thread.start()
        threads.append(thread)
        time.sleep(LAUNCH_DELAY)
    for thread in threads:
        thread.join(timeout=60)

# inside fetch_tradingview_symbol, on a 429:
if attempt <= MAX_RETRIES:
    wait = RETRY_DELAY * attempt
    time.sleep(wait)
    fetch_tradingview_symbol(key, tv_symbol, attempt + 1)

A shared AppState dataclass, guarded by a lock, collects results as each thread finishes — so writes from concurrent symbols never race against each other.

FII/DII flows: three sources, one fallback chain

Institutional flow data is scraped rather than pulled from a clean API, so the script treats it defensively: Moneycontrol's widget is the primary source for cash and F&O FII/DII nets, with NSE's public endpoint as a fallback if that scrape fails. Separately, NSDL's FPI report supplies the equity/debt breakdown that neither of the other two expose. Each source is wrapped in its own try/except so one broken scraper doesn't take down the others:

try:
    moneycontrol = fetch_moneycontrol_fii()
    state.fii.update(moneycontrol)
except OSError as exc:
    print(f"  WARN Moneycontrol FII widget failed: {exc}")
    try:
        nse = fetch_nse_cash_fii()
        state.fii.update(nse)
    except OSError as fallback_exc:
        print(f"  WARN NSE FII fallback failed: {fallback_exc}")

try:
    sebi = fetch_nsdl_fpi_sebi()
    state.fii["equity"] = sebi.get("equity")
    state.fii["debt"] = sebi.get("debt")
except OSError as exc:
    print(f"  WARN NSDL FPI (EQUITY/DEBT) failed: {exc}")

Writing to Excel without breaking the workbook

The workbook is a living file that gets appended to on every run, so the script has to find the next empty row rather than overwrite anything, apply consistent number formats and borders, and color each cell green or red based on whether the instrument closed up or down from its open:

def next_append_row(worksheet: Worksheet) -> int:
    """Return the next empty row after the last populated row in column A."""
    last_row = 1
    for row in worksheet.iter_rows(min_row=2, max_col=1, values_only=False):
        if row[0].value is not None:
            last_row = row[0].row
    return last_row + 1


def direction_fill(close, open_value):
    if open_value is None or close is None:
        return None
    return UP_FILL if close >= open_value else DOWN_FILL


def apply_cell(cell, value, is_pct=False, fill=None, number_format=None):
    cell.value = value
    cell.font = BODY_FONT
    cell.border = thin_border()
    cell.alignment = Alignment(horizontal="center", vertical="center")
    if fill:
        cell.fill = fill
    if number_format:
        cell.number_format = number_format
    elif is_pct and isinstance(value, (int, float)):
        cell.number_format = "+0.00%;-0.00%;0.00%"

Four sheets get their own append function — DXY, USDINR (which also carries the FII/DII and gold/oil columns), Indian_Stock, and US_Indices — each writing a timestamp, open/close pairs, and a computed change percent, with column layouts stable enough that a one-time migration function was needed when NDX/SPX moved from the Indian_Stock sheet to their own.

Delivery: Drive and Slack

Once the workbook is saved, the run finishes by uploading it to Google Drive so the latest version is always available outside the machine running the script, then posting a summary to Slack so the day's numbers show up without anyone opening the file:

workbook.save(EXCEL_FILE)
upload_to_drive()
send_slack_summary(state)

Splitting these into their own drive_uploader and notifier modules keeps the main script focused on fetch-and-write, and means either delivery channel can be swapped or disabled without touching the data logic.

Where this connects to my work

FX-Insight runs the same loop as the other automations on my portfolio home: fetch on a schedule, normalize and validate the data, write it somewhere durable, and alert Slack. Here the data source is TradingView and institutional flow scrapes instead of Reddit or payments records, but the shape — concurrent fetch with retries, defensive fallbacks per source, and a clean append into a tracked file — is the same pattern applied to market data.