The Problem

Property managers know what residents are currently paying. They know what units are listed for. They can pull a rent roll that shows occupied units and an availability report showing vacant units. What they don't have is a unified view of rent performance across the entire lease lifecycle.

When an executive asks "Are we pushing rents effectively?" or "What's the gap between classic and renovated units?" the answer requires manual data aggregation across multiple reports, calculations in Excel, and assumptions about which data points matter. By the time you compile the answer, it's already outdated.

This creates three blind spots:

Pricing disconnect: You set asking rents on available units without knowing what recent applications or lease executions are achieving. If asking rents are $50 higher than what you're actually getting, you're extending vacancy duration for no revenue gain.

Renovation ROI uncertainty: You're renovating units expecting rent premiums but can't quickly verify if classic vs. renovated spreads are holding, expanding, or compressing. Capital decisions get made on assumptions rather than current performance data.

Loss to lease invisibility: In-place rents lag market rents over time. Without systematic measurement, you don't know which floor plans or unit types have the widest gaps, where renewal increases should be concentrated, or if recent move-ins are actually closing the gap.

The Measurement Framework

We track rent performance across four stages of the lease lifecycle:

1. In-Place Rents: What current residents are paying. This is your baseline revenue but lags market by 6-12 months on average due to lease terms.

2. Recent Lease Achievements: Weighted average rent of renewals and new move-ins over the last X days (typically 30-90 days, user-configurable). This shows what the property is actually achieving in current market conditions.

3. Current Applications: Weighted average rent of units with applications in progress (approval or lease execution stage). This is forward-looking revenue that hasn't closed yet.

4. Asking Rents: What's advertised on currently available units. This is your price signal to the market.

Each metric is bifurcated by:

• Floor plan (1x1, 2x2, 3x2, etc.)
• Renovation status (classic vs. renovated)

The variance between these four stages reveals pricing effectiveness, market positioning, and revenue optimization opportunities.

Building the Dataset

PMS systems export rent rolls and availability reports as separate files. Neither provides the comparative analysis needed for strategic decisions.

Rent Roll Data:

• Unit number and type
• Current resident
• Lease start/end dates
• Current monthly rent
• Move-in date (for determining if it's a recent move-in)

Availability Report:

• Unit number and type
• Vacant date
• Make-ready status
• Asking rent
• Application status (if any)
• Days vacant

Additional Classification:

• Unit renovation status (classic vs. renovated) from master unit list

The processing:

from pathlib import Path
from openpyxl import load_workbook
from collections import defaultdict

# Load source workbook
BOOK_PATH = Path("WAM_with_calcs.xlsx")
wb = load_workbook(BOOK_PATH)
ws_rr = wb["RentRoll_with_calcs"]

# Read unit classification directly from rentroll data
def get_bedbath_from_rentroll(ws_rr, unit_type: str) -> str:
    """
    Get bed/bath configuration from RentRoll data section.
    Returns format like "2x2" or "1x1, 2x2" for mixed types.
    """
    bedbath_set = set()

    # Start at data rows (77+)
    for r in range(77, 1001):
        unit_cell = ws_rr[f"E{r}"].value  # Unit Type column
        if unit_cell is None:
            continue

        if str(unit_cell).strip() == str(unit_type).strip():
            bed = ws_rr[f"Q{r}"].value   # Bed column
            bath = ws_rr[f"R{r}"].value  # Bath column

            if bed and bath:
                try:
                    bed_str = str(int(bed)) if float(bed) == int(float(bed)) else str(float(bed))
                    bath_str = str(int(bath)) if float(bath) == int(float(bath)) else str(float(bath))
                    bedbath_set.add(f"{bed_str}x{bath_str}")
                except:
                    pass

    if bedbath_set:
        return ", ".join(sorted(bedbath_set))
    return ""

Identify recent activity using lookback window:

from datetime import datetime, timedelta

# Extract lookback parameter from source
lookback_cell = ws_rr["L73"].value
lookback_days = int(lookback_cell) if lookback_cell else 90

# Calculate date ranges
run_date = ws_rr["B75"].value
if isinstance(run_date, datetime):
    cutoff_date = run_date - timedelta(days=lookback_days)
else:
    # Handle Excel serial dates
    base = datetime(1899, 12, 30)
    date_obj = base + timedelta(days=int(run_date))
    cutoff_date = date_obj - timedelta(days=lookback_days)

# Process each unit from rentroll
for row in range(77, max_row):  # Data starts row 77
    move_in_date = ws_rr[f"J{row}"].value

    if move_in_date and move_in_date >= cutoff_date:
        # This is recent activity (renewal or move-in)
        # Classification logic depends on PMS data structure
        pass

Calculate in-place rent metrics by unit type and renovation status:

# Group units by base floorplan code
def get_base_code(raw_fp: str) -> str:
    """
    Normalize floorplan code for grouping.
    Examples: A1U/A1R/A1P -> A1, BU / BU ALT -> BU
    """
    s = str(raw_fp or "").strip().upper()
    if not s:
        return ""
    s = s.replace(" ALT", "")
    if len(s) >= 2 and s[1].isdigit():
        return s[:2]
    return s.split()[0]

# Aggregate by floorplan and renovation status
floorplan_groups = defaultdict(lambda: {
    'total': 0,
    'occupied': 0,
    'inplace_rent_sum': 0,
    'recent_renewals': 0,
    'renewal_rent_sum': 0,
    # ... additional metrics
})

# Process units from rentroll
for row in range(77, max_row):
    unit_type = ws_rr[f"E{row}"].value
    renovation_status = ws_rr[f"renovation_col{row}"].value  # From classification
    current_rent = ws_rr[f"rent_col{row}"].value

    base_code = get_base_code(unit_type)
    key = (base_code, renovation_status)

    floorplan_groups[key]['total'] += 1
    if current_rent:  # Occupied
        floorplan_groups[key]['occupied'] += 1
        floorplan_groups[key]['inplace_rent_sum'] += current_rent

Portfolio Consolidation

For portfolio-wide reporting, we extract metrics from each property's Summary sheet and consolidate:

from pathlib import Path
from openpyxl import load_workbook
from collections import defaultdict
from datetime import datetime

BASE_DIR = Path("WAM")
OUT_DIR = BASE_DIR / "Out"

def extract_property_rent_metrics(wam_file: Path):
    """Extract complete metrics from ALL UNITS, CLASSIC, and RENOVATED sections"""
    wb = load_workbook(wam_file, data_only=True)

    # Parse property name from filename (WAM_PropertyName_YYYYMMDD.xlsx)
    parts = wam_file.stem.split("_")
    property_name = "_".join(parts[1:-1])

    ws = wb["Summary"]

    def extract_section(section_name):
        # Find section start row
        section_row = None
        for row in range(1, 100):
            val = ws[f"E{row}"].value
            if val and section_name.upper() in str(val).upper():
                section_row = row + 3  # Data starts 3 rows after title
                break

        if not section_row:
            return []

        # Extract unit type rows
        unit_types = []
        row = section_row

        while row < section_row + 50:
            unit_type = ws[f"E{row}"].value

            if not unit_type or "Total" in str(unit_type):
                break

            # Extract all columns
            def get_val(cell_ref, default=0):
                val = ws[cell_ref].value
                return float(val) if isinstance(val, (int, float)) else default

            unit_data = {
                'unit_type': str(unit_type).strip(),
                'total': int(get_val(f"G{row}", 0)),
                'occupied': int(get_val(f"H{row}", 0)),
                'inplace_rent': get_val(f"I{row}", 0),
                'renewals_count': int(get_val(f"J{row}", 0)),
                'renewals_rent': get_val(f"K{row}", 0),
                'moveins_count': int(get_val(f"M{row}", 0)),
                'moveins_rent': get_val(f"N{row}", 0),
                'apps_count': int(get_val(f"P{row}", 0)),
                'apps_rent': get_val(f"Q{row}", 0),
                'available_count': int(get_val(f"R{row}", 0)),
                'available_rent': get_val(f"S{row}", 0),
                'market_rent': get_val(f"Y{row}", 0),
                'recent_apps_rent': get_val(f"AA{row}", 0),
            }

            unit_types.append(unit_data)
            row += 1

        return unit_types

    return {
        'property': property_name,
        'all_units': extract_section("ALL UNITS"),
        'renovated': extract_section("RENOVATED UNITS"),
        'classic': extract_section("CLASSIC UNITS"),
    }

# Find all WAM files and group by date
wam_files = sorted(OUT_DIR.glob("WAM_*.xlsx"))
files_by_date = defaultdict(list)

for f in wam_files:
    parts = f.stem.split("_")
    date_str = parts[-1]
    date_obj = datetime.strptime(date_str, "%Y%m%d")
    files_by_date[date_obj].append(f)

# Use most recent date
latest_date = max(files_by_date.keys())
latest_files = files_by_date[latest_date]

# Extract metrics from all properties
properties_data = []
for wam_file in latest_files:
    metrics = extract_property_rent_metrics(wam_file)
    properties_data.append(metrics)

Portfolio-level aggregation by unit type and renovation status:

# Aggregate across all properties
portfolio_summary = defaultdict(lambda: {
    'total_units': 0,
    'total_occupied': 0,
    'inplace_rent_sum': 0,
    'occupied_count': 0,
    'renewals_count': 0,
    'renewals_rent_sum': 0,
    'moveins_count': 0,
    'moveins_rent_sum': 0,
    # ... additional metrics
})

for prop_data in properties_data:
    for section in ['all_units', 'renovated', 'classic']:
        for ut in prop_data[section]:
            key = (ut['unit_type'], section)

            portfolio_summary[key]['total_units'] += ut['total']
            portfolio_summary[key]['total_occupied'] += ut['occupied']

            # Weighted sums for averaging
            portfolio_summary[key]['inplace_rent_sum'] += (
                ut['inplace_rent'] * ut['occupied']
            )
            portfolio_summary[key]['renewals_rent_sum'] += (
                ut['renewals_rent'] * ut['renewals_count']
            )
            portfolio_summary[key]['moveins_rent_sum'] += (
                ut['moveins_rent'] * ut['moveins_count']
            )

# Calculate portfolio weighted averages
for key, data in portfolio_summary.items():
    if data['total_occupied'] > 0:
        data['avg_inplace'] = data['inplace_rent_sum'] / data['total_occupied']
    if data['renewals_count'] > 0:
        data['avg_renewal'] = data['renewals_rent_sum'] / data['renewals_count']
    if data['moveins_count'] > 0:
        data['avg_movein'] = data['moveins_rent_sum'] / data['moveins_count']

The Dashboard Output

The final Excel workbook contains three primary views, each with toggle controls for lookback period:

Property-Level Summary:

Three tables per property (All Units, Renovated Units, Classic Units) showing:

• Unit type breakdowns (1x1, 2x2, 3x2, etc.)
• Occupied count and average in-place rent
• Recent renewals + move-ins with counts and average rent
• Current applications with counts and average rent
• Available units with counts, asking rent, and average days vacant
• Variance columns showing $ and % differences between stages

Color coding:

• Green: Recent/app/asking rents exceeding in-place by 5%+
• Yellow: Within ±5% of in-place
• Red: Below in-place rents (negative variance)

Portfolio Rollup:

Aggregated view across all properties for same unit type and renovation status. Weighted averages account for unit counts at each property. Shows portfolio-wide patterns (e.g., all renovated 2x2s are achieving X% premium over in-place).

Variance Analysis:

Focused view on gaps:

• Which floor plans have widest in-place vs. market gaps (renewal opportunity)
• Where asking rents exceed recent achievements (potential vacancy extension)
• Classic vs. renovated rent spreads by floor plan (renovation ROI validation)

Interpreting the Metrics

The four-stage view reveals different operational dynamics:

In-place vs. recent achievements:

Wide positive variance (recent rents 8%+ above in-place) indicates strong market conditions and opportunity to push renewal increases. Negative variance (recent below in-place) suggests market softening or need to reduce asking rents.

Recent achievements vs. current applications:

When application rents significantly exceed recent lease executions, you're testing higher pricing. If applications convert at this rate, you've found additional pricing power. If conversion slows, you're above market clearing price.

Applications vs. asking rents:

Large gaps here indicate pricing volatility. If asking rents are $100 higher than application rents, units aren't leasing at asking price. Either concessions are being offered or asking rents were reduced mid-marketing period.

Classic vs. renovated spreads:

The premium renovated units command over classic units validates renovation capex. If renovated 2x2s achieve only $75/month premium but renovation costs $8K per unit, ROI doesn't support continued investment. If premium is $200/month, accelerate the program.

Strategic Applications

Revenue optimization: Properties showing recent rents below in-place rents signal market deterioration. Price reductions or concessions may be necessary. Properties with wide positive variance can push renewals more aggressively.

Pricing discipline: When asking rents consistently exceed recent lease achievements by 10%+, you're optimizing for rate over occupancy. This may be strategic in supply-constrained markets but extends average days to lease in balanced markets.

Renovation planning: Bifurcation by renovation status quantifies ROI in real-time. If classic units are leasing at 95% occupancy with $150 less rent than renovated units, the business case for renovation is strong. If both are at 90% occupancy with only $50 spread, capital is better deployed elsewhere.

Lease-up strategy: New properties or properties exiting major renovation can track progress by comparing in-place rents (stabilized units) vs. asking rents (remaining inventory). Convergence indicates successful absorption.

Competitive positioning: When portfolio-wide analysis shows your in-place rents lag market rents by 12% while competitors are at 8%, you have embedded revenue growth through renewal management. Conversely, if you're at parity with market, growth must come from market rent increases rather than closing internal gaps.

Why This Matters

Rent rolls show what you're collecting today. Availability reports show what you're asking. Neither shows trajectory, positioning, or whether your pricing strategy is actually working.

The WAM report synthesizes operational data into strategic intelligence. It answers:

• Are we closing the gap between in-place and market rents?
• Are recent move-ins validating our asking rents or requiring concessions?
• Do renovated units justify the capital investment?
• Which floor plans have the most revenue upside through renewals?

Weekly refresh keeps this analysis current. Decisions get made on data from the last 30-90 days rather than quarterly averages that obscure inflection points.

Rent rolls and availability reports already contain this information. Weekly processing extracts the comparative analysis and variance calculations that manual review misses. Executive questions that previously required hours of Excel work now have immediate answers with current data.