Database

Register, load and retrieve data from database using quantmod

After table creation, you can use the following code to register instruments with metadata and load historical data.

import os
from quantmod.db import QuantmodDB

# 1. Initialise — load credentials from environment variables
# Set these in your shell or a .env file (never hardcode in source)
#   export SUPABASE_URL="https://your-project.supabase.co"
#   export SUPABASE_KEY="your-service-role-key"
qm = QuantmodDB(
    supabase_url=os.environ["SUPABASE_URL"],
    supabase_key=os.environ["SUPABASE_KEY"],
)

# 2. Register instruments with metadata
qm.register([
    {"symbol": "ITC.NS", "name": "ITC", "exchange": "NSE", "asset_class": "equity", "instrument_type": "stock"},
    {"symbol": "MSFT", "name": "Microsoft", "exchange": "NASDAQ"}
])

# 3. Get instrument details
instruments = qm.get_instrument_id( "MSFT")
print(instruments)

# 4. List all registered instruments
instruments = qm.list_instruments(["AAPL"]) 
instruments = qm.list_instruments(exchange="NASDAQ")
instruments = qm.list_instruments(["AAPL", "MSFT"])
print(instruments)

# Step 5: Load historical data
results = qm.load_history(["AAPL", "MSFT"], "2026-01-01", "2026-01-19")
print(results)

# Step 6: Retrieve data
prices = qm.get_prices(["AAPL", "MSFT"], start_date="2026-01-01")
print(prices.tail())

# Step 7: Get asset prices
prices = qm.get_asset_prices()
print(prices.tail())

# Step 8: Get latest prices
prices = qm.get_latest_prices()
print(prices.tail())

Create below two tables with schema as shown below at supabase end before running the above code.

-- 1. Instruments table (metadata for symbols/tickers)
CREATE TABLE IF NOT EXISTS instruments (
    id          BIGSERIAL PRIMARY KEY,
    symbol      TEXT NOT NULL UNIQUE,
    name        TEXT,
    exchange    TEXT,
    asset_class TEXT,                    -- e.g. 'stock', 'crypto', 'forex', 'etf', 'index'
    instrument_type TEXT,                -- optional: 'equity', 'future', 'option', 'spot', etc.

    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Optional: add some useful indexes
CREATE INDEX IF NOT EXISTS idx_instruments_asset_class ON instruments(asset_class);


-- 2. Prices table (OHLCV data)
CREATE TABLE IF NOT EXISTS prices (
    instrument_id BIGINT NOT NULL 
        REFERENCES instruments(id)
        ON DELETE CASCADE,                -- when instrument is deleted → delete its prices too

    date          DATE NOT NULL,

    open          DOUBLE PRECISION,
    high          DOUBLE PRECISION,
    low           DOUBLE PRECISION,
    close         DOUBLE PRECISION,
    volume        BIGINT,

    -- Composite primary key (prevents duplicates)
    PRIMARY KEY (instrument_id, date),

    created_at    TIMESTAMPTZ DEFAULT NOW(),
    updated_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Recommended indexes for performance
CREATE INDEX idx_prices_instrument_date
    ON prices(instrument_id, date DESC);   -- useful for recent prices queries

CREATE INDEX idx_prices_date 
    ON prices(date);