Backtesting Framework

Backtesting in the context of a fundamental PM is not algorithmic trading. You are not building a quantitative strategy that auto-executes. You are building an evidence base that answers specific questions: Does this sector actually outperform when government capex rises? Do Capital Goods stocks lead or lag order inflow data? What macro environment has historically produced the best entry points? How long does a cycle typically last? Without backtesting, your investment thesis is an opinion. With backtesting, it becomes a calibrated conviction with historical probability weights attached. That is the difference between a retail investor and an institutional PM.

The framework is built in five modules, each answering a specific question, each runnable entirely in Excel or Google Sheets with free data.

Sector Performance Across Macro Environments

Objective

Quantify how Capital Goods and Infrastructure sector returns have varied across different macro environments, specifically: high versus low government capex, rising versus falling interest rates, high versus low commodity prices, and election versus non-election years.

Data Sources & Download Instructions

Step 1: Download NSE/BSE Sectoral Index Data. Go to bseindia.com, navigate to Market, then Indices, then Download Historical Data. Select "BSE Capital Goods" and choose date range. Download monthly closing values from April 2003 to present (gives you 3 full cycles). Also download Nifty 50 monthly closing values for relative performance calculation.

Format in Excel:

Column A: Date (Month-Year)
Column B: BSE Capital Goods Index Close
Column C: Nifty 50 Close
Column D: Capital Goods Monthly Return (= (B2-B1)/B1)
Column E: Nifty Monthly Return (= (C2-C1)/C1)
Column F: Relative Return (= D - E)  ← excess return vs market

Step 2: Download Government Capex Data. Go to indiabudget.gov.in, then Union Budget Documents, then Budget at a Glance. Download annual capital expenditure data from FY2004 to present. Also use cga.nic.in for monthly capex utilisation data.

Column G: Fiscal Year
Column H: Total Govt Capex (crore)
Column I: YoY Capex Growth % (= (H2-H1)/H1)
Column J: Capex as % of GDP
Column K: Capex Environment (= IF(I>10%,"High","Low"))

Step 3: Download RBI Interest Rate Data. Go to rbi.org.in, then Statistics, then DBIE (Database on Indian Economy). Path: DBIE, then Key Rates, then Repo Rate, then Download. Also download 10-year G-sec yield from the same DBIE portal.

Column L: Repo Rate (quarterly average)
Column M: 10-yr G-sec Yield (quarterly average)
Column N: Rate Direction (= IF(L2>L1,"Rising","Falling"))

Step 4: Download Commodity Price Data. Download copper prices, steel/iron ore prices, and Brent crude oil prices as monthly CSVs from publicly available historical chart data sources.

Column O: Copper Price (monthly average, USD/tonne)
Column P: Copper YoY Change %
Column Q: Commodity Environment (= IF(P>10%,"Rising","Falling"))

Step 5: Create the Master Analysis Sheet. Combine all data into one master sheet with monthly observations. You will have approximately 250 rows (months) covering FY2004 to present.

DATE | CG_RETURN | NIFTY_RETURN | REL_RETURN | CAPEX_ENV |
     RATE_DIR | COMMODITY_ENV | ELECTION_YR | COMBINED_ENV

For COMBINED_ENV, create a composite classification:

=IF(AND(CAPEX_ENV="High",RATE_DIR="Falling"),"IDEAL",
  IF(AND(CAPEX_ENV="High",RATE_DIR="Rising"),"EXECUTION",
  IF(AND(CAPEX_ENV="Low",RATE_DIR="Falling"),"MIXED",
  "AVOID")))

The Core Analysis: Average Returns by Environment

Create a pivot table (Insert, then PivotTable) with Rows as COMBINED_ENV (Ideal / Execution / Mixed / Avoid) and Values as Average of CG_RETURN, Average of NIFTY_RETURN, Average of REL_RETURN.

Note: These are approximate patterns based on historical cycle behaviour. Actual numbers will emerge from your data.

This analysis tells you: when to be overweight the sector (Ideal environment), when to be selective (Execution), when to be market weight (Mixed), and when to underweight (Avoid). This is your macro overlay investment trigger, not based on opinion, but on 20+ years of historical data.

Order Inflow Lead-Lag Analysis

Objective

Measure exactly how many months order inflow data leads stock price performance, giving you a quantified early warning system.

Data Collection

This is the labour-intensive part but enormously valuable. For 3 to 4 representative companies (L&T, KEC International, Thermax), collect quarterly order inflow data from their exchange filings going back 10 years. Go to bseindia.com, then Company, then Results, then select company, then download quarterly results PDFs. From each quarterly PDF, extract order inflow for the quarter (in crore) and order book outstanding (in crore).

Create a Composite Order Inflow Index equal to the average of the three companies' YoY growth rates. Download monthly stock prices for the same companies from NSE historical data.

The Lead-Lag Analysis

In Excel, use the CORREL function with a lag offset:

=CORREL(Order_Inflow_YoY_Range, OFFSET(Stock_Return_Range, lag, 0))

Run this for lag = 0, 1, 2, 3, 4, 5, 6, 7, 8 quarters.

Order inflow data has maximum predictive power for stock returns 2 quarters (6 months) into the future. When you see order inflows accelerating today, the highest probability of strong stock performance is 6 months out. Build positions now, before the market fully prices it in.

CURRENT QUARTER: [Date]

COMPANY           ORDER INFLOW   YOY GROWTH   SIGNAL
L&T               XX,XXX cr      +XX%         Green/Yellow/Red
KEC International  X,XXX cr      +XX%         Green/Yellow/Red
Thermax            X,XXX cr      +XX%         Green/Yellow/Red

COMPOSITE SIGNAL: [Average YoY Growth]
6-MONTH FORWARD IMPLICATION: [Based on lead-lag model]

Signal interpretation:
  Green: Composite >20% YoY = Strong forward return expected
  Yellow: Composite 5-20% YoY = Moderate forward return expected
  Red: Composite <5% YoY = Weak/negative forward return likely

Update this every quarter after results season. It takes 30 minutes and gives you a quantified forward-looking signal that most retail investors do not have.

Valuation Cycle Analysis

Objective

Build a historically calibrated valuation band for the sector so you know in real time whether current valuations are at trough, fair, or peak levels.

Data Sources

Download monthly PE ratio data for Nifty Infrastructure Index from NSE India (Indices, then P/E, P/B, Div. Yield). For BSE Capital Goods Index PE, go to bseindia.com, then Indices, then BSE Capital Goods, then PE/PB/Div History, then Download.

The Valuation Band Construction

Build this table in Excel with Date, Sector PE, Percentile Rank, and Valuation Zone.

Percentile rank formula:

=PERCENTRANK(entire_PE_range, current_PE_value)

Chart this as a visual dashboard: create a line chart with time on the X-axis and sector PE ratio on the Y-axis, with horizontal bands showing the percentile zones and annotations marking historical cycle peaks and troughs. This chart is your single most powerful visual tool for sector-level positioning. Every time you consider adding to Capital Goods exposure, check where the PE is in the historical percentile range. This prevents buying at 90th percentile valuations regardless of how compelling the narrative sounds.

The PE Normalisation Adjustment

Raw sector PE is distorted by cycle earnings. Build a normalised PE (Shiller-style):

Step 1: Calculate 5-year rolling average EPS for the sector index
Step 2: Divide current price by the 5-year rolling average EPS = Normalised PE
Step 3: Plot this alongside raw PE

Normalised PE = Current Index Level / AVERAGE(EPS_t, EPS_t-1, EPS_t-2, EPS_t-3, EPS_t-4)

The normalised PE eliminates peak-cycle earnings inflation, giving you a more stable valuation measure that better predicts future returns.

Commodity Cost Impact Analysis

Objective

Build a quantitative model that estimates EPC sector margin impact from commodity price movements, giving you early warning of margin compression before it appears in quarterly results.

The Commodity-Margin Regression

Download monthly copper, iron ore/steel HRC, and Brent crude prices (2003 to present). For 3 representative EPC companies (L&T, KEC, Thermax), calculate EBITDA margin for each quarter and raw material cost as percentage of revenue from quarterly P&L data.

In Excel, build a regression model. Dependent variable (Y): EBITDA margin (quarterly, for composite of 3 companies). Independent variables (X1, X2, X3): copper price YoY change with 1-quarter lag, steel/iron ore price YoY change with 1-quarter lag, crude oil YoY change with 1-quarter lag.

Use Excel's Data Analysis, then Regression (enable Analysis ToolPak first). The regression coefficients tell you the margin sensitivity to each commodity.

Practical use: When you see copper and steel both rising 15 to 20% YoY today, your model tells you to expect EPC sector EBITDA margins to compress by approximately 150 to 250 bps in the next 1 to 2 quarters. You can get ahead of this by reducing EPC exposure before the margin miss appears in results.

The Margin Early Warning Indicator

Build a monthly composite commodity pressure index:

=WEIGHTED_AVERAGE(
  Copper_YoY_Change * 0.40,    ← 40% weight (highest EPC impact)
  Steel_YoY_Change * 0.35,     ← 35% weight
  Crude_YoY_Change * 0.25      ← 25% weight
)

Plot this against sector EBITDA margin (quarterly) on a dual-axis chart. The visual will clearly show the lead-lag relationship: the commodity index rises 1 to 2 quarters before margin compression appears in results. Update this monthly. Takes 15 minutes. Gives you a 1 to 2 quarter early warning on EPC margin risk.

Individual Stock Backtest

Objective

Validate whether the financial filters from Section 8 actually identify outperforming stocks, and quantify the historical alpha generated by applying them.

Step 1: Build the Historical Universe (2010 to 2024)

For the BSE500 Capital Goods and Infrastructure universe, identify all companies that were listed and had sufficient data in 2010. This gives you approximately 60 to 80 companies. For each company, download 10-year ROCE (average and minimum), 10-year revenue CAGR, average EBITDA margin, average Net Debt/EBITDA, and average OCF/PAT ratio.

Step 2: Score Each Company Against Your Framework

Composite Score = Sum of (Score multiplied by Weight). Grade: A (4.0 to 5.0) = Core compounder candidates. B (3.0 to 3.9) = Quality stocks, standard position. C (2.0 to 2.9) = Cyclical traders, small tactical positions. D (below 2.0) = Avoid.

Step 3: Run the Historical Performance Test

For the top quartile (Grade A) versus bottom quartile (Grade D) companies identified by your framework as of January 2015 (a neutral mid-cycle point), download their stock price performance from January 2015 to December 2023 (8 years).

The alpha generated by the quality filter (Grade A versus Index) is your strategy's historical edge.

Step 4: Stress Test in Downturns

Specifically measure performance during January to March 2020 (COVID crash), September to December 2018 (NBFC crisis), and January to December 2011 (UPA policy paralysis). Calculate maximum drawdown for Grade A versus Grade D during each stress period.

Expected finding: Grade A companies (ROCE consistency plus low debt plus high OCF/PAT) show significantly lower maximum drawdowns in stress periods, not because they do not fall, but because they fall less and recover faster. This is the mathematical proof of balance sheet quality as a risk management tool.

The Complete Build Plan

WEEK 1: DATA COLLECTION (8-10 hours)
  Download BSE Capital Goods Index monthly (2003-present)
  Download Nifty 50 monthly (2003-present)
  Download government capex data FY2004-present
  Download RBI repo rate + G-sec yield (DBIE)
  Download copper, steel, crude monthly prices
  Set up master Excel file with all data combined

WEEK 2: MODULE 1 + 3 BUILD (6-8 hours)
  Build macro environment classification
  Run pivot table: sector returns by macro environment
  Download sector PE ratio historical data
  Build valuation percentile ranking chart

WEEK 3: MODULE 2 + 4 BUILD (6-8 hours)
  Collect order inflow data for L&T, KEC, Thermax (8 years)
  Build lead-lag correlation analysis
  Build commodity-margin regression model
  Create monthly composite commodity pressure index

WEEK 4: MODULE 5 BUILD (8-10 hours)
  Build historical universe (60-80 companies)
  Score top 30 companies on quality matrix
  Download historical stock prices for backtested portfolio
  Run performance comparison: Grade A vs Index vs Grade D

ONGOING MAINTENANCE (1-2 hours/month)
  Update macro environment classification
  Update order inflow dashboard (post each results season)
  Update commodity pressure index
  Run quarterly valuation percentile check
  Re-score any companies where fundamentals have changed

TOTAL BUILD TIME: ~30-35 hours
ONGOING: ~2 hours/month
TOOLS REQUIRED: Excel/Google Sheets + free internet access
COST: Zero (entirely free data sources)

FRED Data Integration

For global macro variables, FRED (Federal Reserve Economic Data) at fred.stlouisfed.org is the best free source. Download by going to the series page, then clicking Download, then selecting CSV with monthly frequency and full date range.

Add FRED global variables as additional columns in your master sheet. Run correlations between US ISM Manufacturing PMI and Indian Capital Goods sector returns. You will find a meaningful positive correlation with a 2 to 3 quarter lag, reflecting the global capex cycle's influence on Indian industrial activity.

The Final Integrated Dashboard

When all five modules are built, your complete analytical system integrates into one view:

SHEET 1: MASTER DATA
  All raw data: Index prices, macro variables, order inflows

SHEET 2: MODULE 1 - MACRO ENVIRONMENT
  Current environment: [IDEAL/EXECUTION/MIXED/AVOID]
  Expected forward return: [Based on historical avg]
  Confidence level: [Based on # of historical observations]

SHEET 3: MODULE 2 - ORDER INFLOW SIGNAL
  Composite order inflow YoY: [XX%]
  Historical lead-lag signal: [POSITIVE/NEUTRAL/NEGATIVE]
  6-month forward implication: [OUTPERFORM/MARKET/UNDERPERFORM]

SHEET 4: MODULE 3 - VALUATION DASHBOARD
  Current sector PE: [XX.Xx]
  Historical percentile: [XX%]
  Valuation zone: [DEEP VALUE/ATTRACTIVE/FAIR/RICH/PEAK]

SHEET 5: MODULE 4 - COMMODITY PRESSURE
  Composite commodity pressure: [XX%]
  Expected margin impact: [-XXX bps]
  Warning level: [GREEN/AMBER/RED]

SHEET 6: MODULE 5 - STOCK SCORES
  All 30 companies ranked by composite quality score
  Current price vs fair value entry range
  Buy/Hold/Wait/Avoid classification

SHEET 7: INTEGRATED SIGNAL
  Combines all 5 modules into one final signal:
  STRONG BUY / BUY / HOLD / REDUCE / AVOID
  With supporting evidence from each module

This is a genuine institutional-grade analytical infrastructure built entirely without a Bloomberg terminal or paid data service. All nine sections of the Capital Goods and Infrastructure masterclass are now complete.