Store historical data locally

Never re-fetch from the broker API every time you want to backtest or analyze — store candles locally and append incrementally.

SQLite (simple, queryable, good for most solo-trader setups)

import sqlite3
import pandas as pd

def save_candles(df: pd.DataFrame, instrument_token: int, interval: str, db_path="data/market.db"):
    conn = sqlite3.connect(db_path)
    df = df.copy()
    df["instrument_token"] = instrument_token
    df["interval"] = interval
    df.to_sql("candles", conn, if_exists="append", index=True, index_label="date")
    conn.close()

def load_candles(instrument_token: int, interval: str, db_path="data/market.db") -> pd.DataFrame:
    conn = sqlite3.connect(db_path)
    df = pd.read_sql(
        "SELECT * FROM candles WHERE instrument_token=? AND interval=? ORDER BY date",
        conn, params=(instrument_token, interval), index_col="date", parse_dates=["date"],
    )
    conn.close()
    return df

Add a unique index to prevent duplicate rows on re-runs:

CREATE UNIQUE INDEX idx_candle_unique ON candles(instrument_token, interval, date);

Use INSERT OR IGNORE (raw SQL) instead of to_sql(if_exists="append") once this index exists, so re-fetching an overlapping range doesn't error out or duplicate rows.

Parquet (faster for large backtests, no query engine needed)

def save_candles_parquet(df: pd.DataFrame, path: str):
    df.to_parquet(path, engine="pyarrow")

def load_candles_parquet(path: str) -> pd.DataFrame:
    return pd.read_parquet(path, engine="pyarrow")

Incremental daily update pattern

def update_daily(kite, instrument_token: int, interval: str):
    existing = load_candles(instrument_token, interval)
    last_date = existing.index.max() if not existing.empty else date(2020, 1, 1)
    new_candles = kite.historical_data(instrument_token, last_date, date.today(), interval)
    if new_candles:
        save_candles(pd.DataFrame(new_candles).set_index("date"), instrument_token, interval)

Run this once per day (chapter 93 covers scheduling) so your local store stays current without ever re-downloading the full history.

Next: 031 — Corporate actions and adjusted price