Financial Index Replication

This template selects 20 of 50 sample stocks and sets how much to invest in each. The goal is to keep their combined returns close to a broad market index while limiting holding size, sector mix, and trading volume.

---

Goals

Optimize

Reasoning types

PrescriptiveRules-based

Experience level

Intermediate

Download ZIPView on GitHub

Browse files

• Directoryfinancial_index_replication/

  • Directorydata/

    • index_returns.csv
    • stock_returns.csv
    • stocks.csv
  • Directorymodel/

    • __init__.py
    • schema.py
    • source.py
  • financial_index_replication.py
  • pyproject.toml
  • README.md
  • runbook.md
  • template-docs.yaml

What this template is for

An asset manager may want to follow a broad market index without buying every stock it contains. Using synthetic monthly returns, this template selects 20 of 50 stocks and assigns weights that track the benchmark within position, sector, and trading-volume limits. It reports tracking error and compares the result with a simple alternative. Adapt the data and limits for another index strategy, but treat the example as a starting point rather than a production portfolio.

Explore the model

Explore the model

The model connects each stock to a sector and records both benchmark and stock returns by month. The decision problem uses those facts to select and weight a sparse basket that follows the benchmark.

StockSectorReturnMonthbelongs tohas a return in

StockSectorReturnMonthbelongs tohas a return in

Stock

One of the 50 stocks the template can hold. Its share of the index and monthly returns help compare the smaller portfolio with the benchmark. Its previous portfolio weight and average daily trading value help limit how much to buy or sell.

Properties

Name	Type
ticker (identity)	String
name	String
sector	String
benchmark_weight	Float
avg_dollar_volume	Float
previous_weight	Float

Relationships

belongs to

Stock belongs to Sector

has a return in

Stock has a return in ReturnMonth

Preview source datastocks.csv · 50 rows; showing 4

Investable stocks with sectors, benchmark weights, liquidity, and previous portfolio weights.

View data/stocks.csv

ticker	name	sector	benchmark_weight	avg_dollar_volume	previous_weight
AAPL	Apple Inc.	Technology	0.02850941820043864	380000000	0.04483963307062611
MSFT	Microsoft Corporation	Technology	0.040427822936529634	210000000	0.06358490845282631
NVDA	NVIDIA Corporation	Technology	0.03657476998503199	75000000	0.057524824050816686
AVGO	Broadcom Inc.	Technology	0.014869334186007837	620000000	0.0

Sector

A group of stocks in one part of the market, such as Technology. The template keeps the amount invested in each sector close to that sector’s share of the index.

Properties

Name	Type
sector_name (identity)	String
sector_benchmark_weight	Float

Relationships

belongs to

Stock belongs to Sector

Preview source datastocks.csv · 50 rows; showing 4

Investable stocks with sectors, benchmark weights, liquidity, and previous portfolio weights.

View data/stocks.csv

ticker	name	sector	benchmark_weight	avg_dollar_volume	previous_weight
AAPL	Apple Inc.	Technology	0.02850941820043864	380000000	0.04483963307062611
MSFT	Microsoft Corporation	Technology	0.040427822936529634	210000000	0.06358490845282631
NVDA	NVIDIA Corporation	Technology	0.03657476998503199	75000000	0.057524824050816686
AVGO	Broadcom Inc.	Technology	0.014869334186007837	620000000	0.0

ReturnMonth

One month of sample returns. It stores the index return and connects to each stock’s return for that month, so the template can compare the portfolio’s return with the index.

Properties

Name	Type
date (identity)	String
index_return	Float

Relationships

has a return in

Stock has a return in ReturnMonth

Preview source dataindex_returns.csv · 42 rows; showing 4

Monthly returns for the benchmark being replicated.

View data/index_returns.csv

date	index_return
2021-01	0.02064559235507961
2021-02	0.01604949459903484
2021-03	-0.00014504165875124783
2021-04	0.006416355689287484

Stock belongs to Sector

Connects a stock to its sector. The template adds up the index and portfolio weights for stocks in each sector, then limits how far the portfolio can differ from the index.

Fields

Field	Value
Name	belongs to
From	Stock
To	Sector

Preview source datastocks.csv · 50 rows; showing 4

Investable stocks with sectors, benchmark weights, liquidity, and previous portfolio weights.

View data/stocks.csv

ticker	name	sector	benchmark_weight	avg_dollar_volume	previous_weight
AAPL	Apple Inc.	Technology	0.02850941820043864	380000000	0.04483963307062611
MSFT	Microsoft Corporation	Technology	0.040427822936529634	210000000	0.06358490845282631
NVDA	NVIDIA Corporation	Technology	0.03657476998503199	75000000	0.057524824050816686
AVGO	Broadcom Inc.	Technology	0.014869334186007837	620000000	0.0

Stock has a return in ReturnMonth

Links each stock to one month and records the stock’s return for that month. The template weights and adds the stock returns to calculate the basket’s monthly return, then compares it with the index return.

Fields

Field	Value
Name	has a return in
From	Stock
To	ReturnMonth

Preview source datastock_returns.csv · 2100 rows; showing 4

Monthly returns for each stock in the investable universe.

View data/stock_returns.csv

date	ticker	return
2021-01	AAPL	0.012791715207687245
2021-01	MSFT	0.021107164726004476
2021-01	NVDA	0.013775022267418398
2021-01	AVGO	0.07609380119185594

stocks.csv

Investable stocks with sectors, benchmark weights, liquidity, and previous portfolio weights.

Populates

Model item

Stock

Model item

Sector

Model item

belongs to

Source

Path

data/stocks.csv

Kind

Local CSV

Preview: 50 rows; showing 4

ticker	name	sector	benchmark_weight	avg_dollar_volume	previous_weight
AAPL	Apple Inc.	Technology	0.02850941820043864	380000000	0.04483963307062611
MSFT	Microsoft Corporation	Technology	0.040427822936529634	210000000	0.06358490845282631
NVDA	NVIDIA Corporation	Technology	0.03657476998503199	75000000	0.057524824050816686
AVGO	Broadcom Inc.	Technology	0.014869334186007837	620000000	0.0

index_returns.csv

Monthly returns for the benchmark being replicated.

Populates

Model item

ReturnMonth

Source

Path

data/index_returns.csv

Kind

Local CSV

Preview: 42 rows; showing 4

date	index_return
2021-01	0.02064559235507961
2021-02	0.01604949459903484
2021-03	-0.00014504165875124783
2021-04	0.006416355689287484

stock_returns.csv

Monthly returns for each stock in the investable universe.

Populates

Model item

has a return in

Source

Path

data/stock_returns.csv

Kind

Local CSV

Preview: 2100 rows; showing 4

date	ticker	return
2021-01	AAPL	0.012791715207687245
2021-01	MSFT	0.021107164726004476
2021-01	NVDA	0.013775022267418398
2021-01	AVGO	0.07609380119185594

Download and run the template

Before you start, install Python 3.10 or later and get access to a Snowflake account with the RAI Native App. Prescriptive reasoning is in Public Preview; ask your RelationalAI support representative to enable it. Preview features are for evaluation and testing, not production applications. The template pins relationalai==1.0.14 in pyproject.toml.

Use this sequence to run the bundled example:

1. Download the template

   Download the ZIP, unzip it, and enter the template directory:

   unzip financial_index_replication.zip
   cd financial_index_replication

2. Create a Python environment

   Create and activate a virtual environment, then update pip:

   python -m venv .venv
   source .venv/bin/activate
   python -m pip install --upgrade pip

3. Install the template

   Install the dependencies pinned in pyproject.toml:

   python -m pip install .

4. Configure your project

   Use the configuration builder in Start building with PyRel to create raiconfig.yaml in the template directory and verify your connection.

5. Run the template

   Run the bundled script from the template directory:

   python financial_index_replication.py

   Output

   Selected names: exactly 20
   Status: OPTIMAL
   Wrote benchmark-vs-replica returns to: data/replica_returns.csv

   The solve selects exactly 20 names, though a selected stock may have zero weight. It writes data/replica_returns.csv for plotting. See runbook.md for the basket, sector exposures, tracking quality, and baseline comparison.

See how it works

The Model Explorer shows the Stock, Sector, and ReturnMonth concepts and their relationships. The steps below show how the template loads stock and monthly-return data, selects stocks under portfolio limits, and compares the basket's historical returns with the index. Use the excerpts to find the data mappings, limits, and tracking calculation you could adapt for another index.

Hover over dotted-underlined code terms to highlight their source. You can also focus or tap each term.

1

Load stocks and monthly returns into the model

Three CSV files list eligible stocks, monthly index returns, and each stock’s monthly returns. pd.read_csv(...) loads each file into a DataFrame.

Stock.new(...) and ReturnMonth.new(...) define base facts from the stock and index-return DataFrames. The code groups stocks by sector and uses Stock.monthly_return(...) to connect each stock to its monthly return, giving the model the history needed to compare a proposed basket’s returns with the index.

model/source.py (lines 12-35)

stocks_csv = pd.read_csv(DATA_DIR / "stocks.csv")
index_returns_csv = pd.read_csv(DATA_DIR / "index_returns.csv")
stock_returns_csv = pd.read_csv(DATA_DIR / "stock_returns.csv")

model.define(Stock.new(model.data(stocks_csv).to_schema()))

model.define(Sector.new(sector_name=Stock.sector))
model.define(Stock.sector_ref(Sector)).where(Stock.sector == Sector.sector_name)
model.define(
    Sector.benchmark_weight(sum(Stock.benchmark_weight).where(Stock.sector_ref(Sector)).per(Sector))
)

model.define(ReturnMonth.new(model.data(index_returns_csv).to_schema()))


def map_stock_returns() -> None:
    stock_return_data = model.data(stock_returns_csv)
    model.define(Stock.monthly_return(ReturnMonth, stock_return_data["return"])).where(
        Stock.ticker(stock_return_data.ticker),
        ReturnMonth.date(stock_return_data.date),
    )


map_stock_returns()

model/source.py (lines 12-35)

stocks_csv = pd.read_csv(DATA_DIR / "stocks.csv")
index_returns_csv = pd.read_csv(DATA_DIR / "index_returns.csv")
stock_returns_csv = pd.read_csv(DATA_DIR / "stock_returns.csv")

model.define(Stock.new(model.data(stocks_csv).to_schema()))

model.define(Sector.new(sector_name=Stock.sector))
model.define(Stock.sector_ref(Sector)).where(Stock.sector == Sector.sector_name)
model.define(
    Sector.benchmark_weight(sum(Stock.benchmark_weight).where(Stock.sector_ref(Sector)).per(Sector))
)

model.define(ReturnMonth.new(model.data(index_returns_csv).to_schema()))


def map_stock_returns() -> None:
    stock_return_data = model.data(stock_returns_csv)
    model.define(Stock.monthly_return(ReturnMonth, stock_return_data["return"])).where(
        Stock.ticker(stock_return_data.ticker),
        ReturnMonth.date(stock_return_data.date),
    )


map_stock_returns()

2

Set up stock choices and weights

Once the return history is in the model, the next decisions are which stocks to hold and how much to invest in each. Stock.x_selected and Stock.x_weight represent those choices in a Problem object created by Problem(model, Float).

The first two problem.solve_for(...) calls create decision variables: yes-or-no selection and a weight from 0% to 10% for each stock. The monthly tracking fields prepare the model to compare the basket’s returns with the index in the next step.

financial_index_replication.py (lines 70-99)

Stock.x_selected = model.Property(f"{Stock} selected if {Float:selected}")
Stock.x_weight = model.Property(f"{Stock} has replication weight {Float:weight}")
ReturnMonth.x_pos_error = model.Property(
    f"{ReturnMonth} has positive tracking residual {Float:pos_error}"
)
ReturnMonth.x_neg_error = model.Property(
    f"{ReturnMonth} has negative tracking residual {Float:neg_error}"
)

selected = Float.ref("selected")
weight = Float.ref("weight")
pos_error = Float.ref("pos_error")
neg_error = Float.ref("neg_error")
index_return = Float.ref("index_return")
stock_return = Float.ref("stock_return")

problem = Problem(model, Float)

problem.solve_for(
    Stock.x_selected(selected),
    type="bin",
    name=["selected", Stock.ticker],
)
problem.solve_for(
    Stock.x_weight(weight),
    type="cont",
    lower=0,
    upper=MAX_WEIGHT,
    name=["weight", Stock.ticker],
)

financial_index_replication.py (lines 70-99)

Stock.x_selected = model.Property(f"{Stock} selected if {Float:selected}")
Stock.x_weight = model.Property(f"{Stock} has replication weight {Float:weight}")
ReturnMonth.x_pos_error = model.Property(
    f"{ReturnMonth} has positive tracking residual {Float:pos_error}"
)
ReturnMonth.x_neg_error = model.Property(
    f"{ReturnMonth} has negative tracking residual {Float:neg_error}"
)

selected = Float.ref("selected")
weight = Float.ref("weight")
pos_error = Float.ref("pos_error")
neg_error = Float.ref("neg_error")
index_return = Float.ref("index_return")
stock_return = Float.ref("stock_return")

problem = Problem(model, Float)

problem.solve_for(
    Stock.x_selected(selected),
    type="bin",
    name=["selected", Stock.ticker],
)
problem.solve_for(
    Stock.x_weight(weight),
    type="cont",
    lower=0,
    upper=MAX_WEIGHT,
    name=["weight", Stock.ticker],
)

3

Minimize monthly return gaps

To see how a proposed basket follows the index, the model compares the basket’s return with the index return for each sample month. problem.satisfy(...) adds constraints that express each difference as a positive or negative monthly gap.

problem.minimize(...) sets an objective that reduces the sum of those gaps across the sample months, so the solver favors baskets closer to the index. This comparison uses past returns and does not guarantee the basket will follow the index as closely in future months.

financial_index_replication.py (lines 166-187)

# Monthly benchmark residual:
# index_return[t] - sum_i weight[i] * stock_return[i,t] = pos_error[t] - neg_error[t]
problem.satisfy(
    model.where(
        ReturnMonth.index_return(index_return),
        ReturnMonth.x_pos_error(pos_error),
        ReturnMonth.x_neg_error(neg_error),
        Stock.x_weight(weight),
        Stock.monthly_return(ReturnMonth, stock_return),
    ).require(
        index_return - sum(Stock, stock_return * weight).per(ReturnMonth)
        == pos_error - neg_error
    )
)

# Minimize total absolute tracking residual.
problem.minimize(
    sum(ReturnMonth, pos_error + neg_error).where(
        ReturnMonth.x_pos_error(pos_error),
        ReturnMonth.x_neg_error(neg_error),
    )
)

financial_index_replication.py (lines 166-187)

# Monthly benchmark residual:
# index_return[t] - sum_i weight[i] * stock_return[i,t] = pos_error[t] - neg_error[t]
problem.satisfy(
    model.where(
        ReturnMonth.index_return(index_return),
        ReturnMonth.x_pos_error(pos_error),
        ReturnMonth.x_neg_error(neg_error),
        Stock.x_weight(weight),
        Stock.monthly_return(ReturnMonth, stock_return),
    ).require(
        index_return - sum(Stock, stock_return * weight).per(ReturnMonth)
        == pos_error - neg_error
    )
)

# Minimize total absolute tracking residual.
problem.minimize(
    sum(ReturnMonth, pos_error + neg_error).where(
        ReturnMonth.x_pos_error(pos_error),
        ReturnMonth.x_neg_error(neg_error),
    )
)

4

Select 20 stocks and allocate the portfolio

The sample index contains 50 stocks, but the model aims to follow the index with a smaller basket. sum(Stock, selected) == N_REPLICATION_NAMES marks 20 of those stocks as selected, and sum(Stock, weight) == 1.0 makes the portfolio weights add to 100%.

MAX_WEIGHT * selected prevents investment in unselected stocks and caps each selected stock at 10%. These constraints still allow a selected stock to have zero weight, so the model may invest in fewer than 20 stocks.

financial_index_replication.py (lines 113-128)

# Select exactly N names and invest all capital.
problem.satisfy(
    model.where(Stock.x_selected(selected)).require(
        sum(Stock, selected) == N_REPLICATION_NAMES
    )
)
# Full-investment constraint: portfolio weights must sum to 100%.
problem.satisfy(model.where(Stock.x_weight(weight)).require(sum(Stock, weight) == 1.0))

# A stock can carry weight only when selected.
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
        Stock.x_selected(selected),
    ).require(weight <= MAX_WEIGHT * selected)
)

financial_index_replication.py (lines 113-128)

# Select exactly N names and invest all capital.
problem.satisfy(
    model.where(Stock.x_selected(selected)).require(
        sum(Stock, selected) == N_REPLICATION_NAMES
    )
)
# Full-investment constraint: portfolio weights must sum to 100%.
problem.satisfy(model.where(Stock.x_weight(weight)).require(sum(Stock, weight) == 1.0))

# A stock can carry weight only when selected.
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
        Stock.x_selected(selected),
    ).require(weight <= MAX_WEIGHT * selected)
)

5

Keep sector exposure and trades within limits

Matching past index returns is not enough if the basket concentrates in one sector or calls for large trades. SECTOR_ACTIVE_BAND keeps each sector’s share of the portfolio within four percentage points of that sector’s share of the index.

MAX_ADV_PARTICIPATION compares each proposed buy or sell with the stock’s average daily trading value and caps the trade at 5% of that value. These constraints keep the sector mix near the index while limiting the trades needed to move from the previous portfolio.

financial_index_replication.py (lines 130-164)

# Keep sector exposures close to benchmark sector weights.
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
        Stock.sector_ref(Sector),
    ).require(
        sum(Stock, weight).per(Sector) <= Sector.benchmark_weight + SECTOR_ACTIVE_BAND
    )
)
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
        Stock.sector_ref(Sector),
    ).require(
        sum(Stock, weight).per(Sector) >= Sector.benchmark_weight - SECTOR_ACTIVE_BAND
    )
)

# Limit per-name buy and sell dollars as a fraction of average dollar volume.
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
    ).require(
        PORTFOLIO_VALUE * (weight - Stock.previous_weight)
        <= MAX_ADV_PARTICIPATION * Stock.avg_dollar_volume
    )
)
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
    ).require(
        PORTFOLIO_VALUE * (Stock.previous_weight - weight)
        <= MAX_ADV_PARTICIPATION * Stock.avg_dollar_volume
    )
)

financial_index_replication.py (lines 130-164)

# Keep sector exposures close to benchmark sector weights.
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
        Stock.sector_ref(Sector),
    ).require(
        sum(Stock, weight).per(Sector) <= Sector.benchmark_weight + SECTOR_ACTIVE_BAND
    )
)
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
        Stock.sector_ref(Sector),
    ).require(
        sum(Stock, weight).per(Sector) >= Sector.benchmark_weight - SECTOR_ACTIVE_BAND
    )
)

# Limit per-name buy and sell dollars as a fraction of average dollar volume.
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
    ).require(
        PORTFOLIO_VALUE * (weight - Stock.previous_weight)
        <= MAX_ADV_PARTICIPATION * Stock.avg_dollar_volume
    )
)
problem.satisfy(
    model.where(
        Stock.x_weight(weight),
    ).require(
        PORTFOLIO_VALUE * (Stock.previous_weight - weight)
        <= MAX_ADV_PARTICIPATION * Stock.avg_dollar_volume
    )
)

6

Choose the basket and check tracking

problem.solve(...) solves the decision problem under the portfolio rules to choose stocks and weights. model.select(...) queries the model for stocks with weights above 0.01% of the portfolio, so a selected stock with zero weight does not appear in the result.

portfolio_returns calculates the basket’s monthly returns from those weights and stock returns, and tracking_diff compares them with the index returns. annualized_tracking_error() summarizes the monthly gaps as a yearly tracking measure, showing how closely this basket followed the index in the sample months.

financial_index_replication.py (lines 205-246)

problem.solve("highs", time_limit_sec=240)
si = problem.solve_info()
si.display()
assert si.termination_status == "OPTIMAL" or si.termination_status == "TIME_LIMIT", "Solver did not find an optimal solution."

print(f"\nStatus: {si.termination_status}")
print(f"Objective: total absolute residual = {si.objective_value:.6f}")

# --------------------------------------------------
# Evaluate and report
# --------------------------------------------------

solution_df = (
    model.select(
        Stock.ticker.alias("ticker"),
        Stock.sector.alias("sector"),
        Stock.benchmark_weight.alias("benchmark_weight"),
        Stock.previous_weight.alias("previous_weight"),
        Stock.avg_dollar_volume.alias("avg_dollar_volume"),
        selected.alias("selected"),
        weight.alias("weight"),
    )
    .where(
        Stock.x_selected(selected),
        Stock.x_weight(weight),
        weight > 0.0001,
    )
    .to_df()
    .sort_values("weight", ascending=False)
)

returns_wide = stock_returns_csv.pivot(
    index="date", columns="ticker", values="return"
).sort_index()
index_series = index_returns_csv.set_index("date").sort_index()
weights = solution_df.set_index("ticker")["weight"]
portfolio_returns = returns_wide[weights.index].mul(weights, axis=1).sum(axis=1)
tracking_diff = portfolio_returns - index_series["index_return"]

def annualized_tracking_error():
    monthly_rms = sqrt((tracking_diff ** 2).mean())
    return monthly_rms * sqrt(MONTHS_PER_YEAR)

financial_index_replication.py (lines 205-246)

problem.solve("highs", time_limit_sec=240)
si = problem.solve_info()
si.display()
assert si.termination_status == "OPTIMAL" or si.termination_status == "TIME_LIMIT", "Solver did not find an optimal solution."

print(f"\nStatus: {si.termination_status}")
print(f"Objective: total absolute residual = {si.objective_value:.6f}")

# --------------------------------------------------
# Evaluate and report
# --------------------------------------------------

solution_df = (
    model.select(
        Stock.ticker.alias("ticker"),
        Stock.sector.alias("sector"),
        Stock.benchmark_weight.alias("benchmark_weight"),
        Stock.previous_weight.alias("previous_weight"),
        Stock.avg_dollar_volume.alias("avg_dollar_volume"),
        selected.alias("selected"),
        weight.alias("weight"),
    )
    .where(
        Stock.x_selected(selected),
        Stock.x_weight(weight),
        weight > 0.0001,
    )
    .to_df()
    .sort_values("weight", ascending=False)
)

returns_wide = stock_returns_csv.pivot(
    index="date", columns="ticker", values="return"
).sort_index()
index_series = index_returns_csv.set_index("date").sort_index()
weights = solution_df.set_index("ticker")["weight"]
portfolio_returns = returns_wide[weights.index].mul(weights, axis=1).sum(axis=1)
tracking_diff = portfolio_returns - index_series["index_return"]

def annualized_tracking_error():
    monthly_rms = sqrt((tracking_diff ** 2).mean())
    return monthly_rms * sqrt(MONTHS_PER_YEAR)