Horizon Returns (Constituent-Level) v3 – CSV or SQL Server

v3 adds flexible data loading: use a CSV (default) or pull directly from SQL Server.

Install

pip install -r requirements.txt

Choose your data source

CSV (default)

Edit config.yaml:

source: csv
csv_path: your_daily_data.csv

Run:

python run_horizon_returns.py --config config.yaml --output horizon_returns_long.csv

Or pass --input directly (overrides config):

python run_horizon_returns.py --source csv --input your_daily_data.csv --output horizon_returns_long.csv

SQL Server

Provide a connection string and either a table or a full query.

In config.yaml:

source: sqlserver
sqlserver:
  conn: "mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BODBC%20Driver%2018%20for%20SQL%20Server%7D%3BSERVER%3Dhost%2C1433%3BDATABASE%3DDB%3BUID%3Duser%3BPWD%3Dpw%3BEncrypt%3Dyes%3BTrustServerCertificate%3Dyes%3B"
  schema: "dbo"         # optional if using table
  table: "IndexConstituents"   # (use either table or query)
  # query: "SELECT ... FROM dbo.IndexConstituents WHERE ..."

Or via CLI:

python run_horizon_returns.py \
  --source sqlserver \
  --conn "mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BODBC%20Driver%2018%20for%20SQL%20Server%7D%3BSERVER%3Dhost%2C1433%3BDATABASE%3DDB%3BUID%3Duser%3BPWD%3Dpw%3BEncrypt%3Dyes%3BTrustServerCertificate%3Dyes%3B" \
  --table IndexConstituents \
  --schema dbo \
  --output horizon_returns_long.csv

For query mode, supply --query "SELECT ..." instead of --table/--schema.

Frequency & Annualization (unchanged from v2)

• --frequency: daily | weekly | monthly | custom
• --periods_per_year: override (e.g., 250 instead of 252)
• Emits both raw and annualized rows by default; use --annualized_only or --non_annualized_only to filter.

Column mappings & fields

Edit in config.yaml:

date_col: TradeDate
benchmark_col: BenchmarkID
id_cols: ["Ticker", "CUSIP"]
return_fields: ["PRICE_RETURN_1D", "TOTAL_RETURN_1D_NET"]

Output format

as_of_date, horizon, annualized, return_field, BenchmarkID, Ticker, CUSIP,
horizon_return, window_length, n_obs_used, is_full_window

Requirements

• CSV-only: pandas, numpy, PyYAML
• SQL Server: add SQLAlchemy and pyodbc (see requirements.txt)

Notes

• As-of alignment to nearest available date on/before target.
• Returns expected to be per-row period returns given the dataset frequency (daily/weekly/monthly/custom).
• Use benchmarks and as_of_dates in config or CLI flags to scope runs.