def clean_model_targets(self):
start_row = 4
end_row = self.schema(
IntCell(find_row("INPUTS!A", "Model End Row", target="B")))
raw_targets = self.schema_list(schema=self.MODEL_SCHEMA,
start=start_row,
end=end_row)
# Add the end dates for each of the targets
for target in raw_targets:
start = target["start"]
target["end"] = start + timedelta(days=7)
# Fix up the total investment targets
for raw_target in raw_targets:
for category in ["acq", "ret"]:
raw_target[f"target_{category}_investment"] = (
raw_target[f"target_{category}_expenses"]
["demand_creation"] +
raw_target[f"target_{category}_expenses"]
["leasing_enablement"] +
raw_target[f"target_{category}_expenses"]
["market_intelligence"] +
raw_target[f"target_{category}_expenses"]
["reputation_building"])
# Drop the extraneous period.
return raw_targets
def get_estimated_population(self):
tam_type = self.schema(
ChoiceCell(find("tam type"), choices=["radius", "zipcodes"])
)
if tam_type == "radius":
result = self.get_estimated_population_radius()
else:
result = self.get_estimated_population_zip()
result["population"] = self.schema(IntCell(find("est. population")))
return result
def update_segment_details(self, segment):
_, _, row = find(f"age segment: {segment['age_group']}")(self.workbook)
cols = list(
cols_until(
self.workbook,
matchp(exact="All"),
"B",
test_sheet="Output",
test_row=row,
)
)
def _find(predicate):
return find_row("Output!A", predicate, start_row=row)
schema = {
"income": CurrencyCell(_find("age segment")),
"group_population": IntCell(_find("population")),
"home_owners": {
"total": IntCell(_find("home owners")),
"family": IntCell(_find("family ho")),
"nonfamily": IntCell(_find("non-family ho")),
},
"renters": {
"total": IntCell(_find("renters")),
"family": IntCell(_find("family r")),
"nonfamily": IntCell(_find("non-family r")),
},
"market_size": IntCell(_find("est. market")),
"active_populations": ["renters.nonfamily", "renters.family"],
}
segment["income_groups"] = self.schema_list(
schema, locations=cols, sheet="Output"
)
segment["segment_population"] = self.schema(
IntCell(loc(sheet="Output", row=next_row(row), col=next_col(cols[-1])))
)
class ProjectExcelImporter(ExcelImporter):
"""
An excel importer base class with tools specifically tailored to
remarkably-style excel templates for projects.
"""
expected_type = None
expected_version = None
SPREADSHEET_KIND = StrCell(find_version("spreadsheet_kind"))
SPREADSHEET_VERSION = IntCell(find_version("spreadsheet_version"))
def clean(self, ctx):
"""
Clean a remarkably-style excel spreadsheet.
"""
self.check_version()
def check_version(self):
"""
Validate the VERSION tab of the spreadsheet.
"""
self.check_value(self.SPREADSHEET_KIND, expected=self.expected_type)
self.check_value(self.SPREADSHEET_VERSION, expected=self.expected_version)
class ModelingImporter(ProjectExcelImporter):
expected_type = "model"
expected_version = 1
OUTPUT_SCHEMA = {
"name": StrCell(find("model name")),
"dates": {
"start": DateCell(find("start date")),
"end": DateCell(find("end date")),
},
"property": {
"average_monthly_rent": CurrencyCell(find("average rent")),
"lowest_monthly_rent": CurrencyCell(find("lowest rent")),
"cost_per_exe_vs_rent": FloatCell(find("cost per exe vs rent")),
"total_units": IntCell(find("total units")),
"leasing": {
"change": IntCell(find("leasing change")),
"cds": IntCell(find("cancels & denials")),
"cd_rate": FloatCell(find("cd rate")),
"renewal_notices": IntCell(find("renewal notices")),
"renewals": IntCell(find("renewals")),
"renewal_rate": FloatCell(find("renewal rate")),
"resident_decisions": IntCell(find("resident decisions")),
"vacation_notices": IntCell(find("vacation notices")),
"rate": FloatCell(find("leasing rate")),
"units": IntCell(find("lease units")),
},
"occupancy": {
"move_ins": IntCell(find("move ins")),
"move_outs": IntCell(find("move outs")),
"rate": FloatCell(find("occupancy rate")),
"units": IntCell(find("occupancy units")),
"occupiable": IntCell(find("occupiable units")),
},
},
"funnel": {
"volumes": {
"usv": IntCell(find("usv volume")),
"inq": IntCell(find("inq volume")),
"tou": IntCell(find("tou volume")),
"app": IntCell(find("app volume")),
"exe": IntCell(find("exe volume")),
},
"costs": {
"usv": CurrencyCell(find("usv cost")),
"inq": CurrencyCell(find("inq cost")),
"tou": CurrencyCell(find("tou cost")),
"app": CurrencyCell(find("app cost")),
"exe": CurrencyCell(find("exe cost")),
},
"conversions": {
"usv_inq": FloatCell(find("usv conversions")),
"inq_tou": FloatCell(find("inq conversions")),
"tou_app": FloatCell(find("tou conversions")),
"app_exe": FloatCell(find("app conversions")),
"usv_exe": FloatCell(find("usv_exe conversions")),
},
},
"four_week_funnel_averages": {
"usv": IntCell(find("usv 4 week")),
"inq": IntCell(find("inq 4 week")),
"tou": IntCell(find("tou 4 week")),
"app": IntCell(find("app 4 week")),
"exe": IntCell(find("exe 4 week")),
},
"investment": {
"acquisition": {
"expenses": {
"demand_creation":
CurrencyCell(find("acquisition demand creation")),
"leasing_enablement":
CurrencyCell(find("acquisition leasing enablement")),
"market_intelligence":
CurrencyCell(find("acquisition market intelligence")),
"reputation_building":
CurrencyCell(find("acquisition reputation building")),
},
"total":
CurrencyCell(find("acquisition total")),
"romi":
IntCell(find("acquisition romi")),
"estimated_revenue_gain":
CurrencyCell(find("acquisition revenue gain")),
},
"retention": {
"expenses": {
"demand_creation":
CurrencyCell(find("retention demand creation")),
"leasing_enablement":
CurrencyCell(find("retention leasing enablement")),
"market_intelligence":
CurrencyCell(find("retention market intelligence")),
"reputation_building":
CurrencyCell(find("retention reputation building")),
},
"total":
CurrencyCell(find("retention total")),
"romi":
IntCell(find("retention romi")),
"estimated_revenue_gain":
CurrencyCell(find("retention revenue gain")),
},
"total": {
"total": CurrencyCell(find("total total")),
"romi": IntCell(find("total romi")),
"estimated_revenue_gain":
CurrencyCell(find("total revenue gain")),
},
},
}
MODEL_SCHEMA = {
"start": DateCell(model("week start")),
"target_leased_rate": FloatCell(model("lease up %")),
"target_lease_applications": IntCell(model("apps")),
"target_leases_executed": IntCell(model("exe")),
"target_lease_renewal_notices": IntCell(model("notice to renew")),
"target_lease_renewals": IntCell(model("renewals")),
"target_lease_vacation_notices": IntCell(model("notice to vacate")),
"target_lease_cds": IntCell(model("c/d")),
"target_delta_leases": IntCell(model("weekly delta leased units")),
"target_move_ins": IntCell(model("move ins")),
"target_move_outs": IntCell(model("move outs")),
"target_occupied_units": IntCell(model("occupied units")),
"target_acq_expenses": {
"demand_creation": CurrencyCell(model("aqc demand")),
"leasing_enablement": CurrencyCell(model("aqc leasing")),
"market_intelligence": CurrencyCell(model("aqc market")),
"reputation_building": CurrencyCell(model("aqc reputation")),
},
"target_ret_expenses": {
"demand_creation": CurrencyCell(model("ret demand")),
"leasing_enablement": CurrencyCell(model("ret leasing")),
"market_intelligence": CurrencyCell(model("ret market")),
"reputation_building": CurrencyCell(model("ret reputation")),
},
"target_usvs": IntCell(model("usvs")),
"target_inquiries": IntCell(model("inqs")),
"target_tours": IntCell(model("tou")),
}
def clean_output_data(self):
return self.schema(self.OUTPUT_SCHEMA)
def clean_model_targets(self):
start_row = 4
end_row = self.schema(
IntCell(find_row("INPUTS!A", "Model End Row", target="B")))
raw_targets = self.schema_list(schema=self.MODEL_SCHEMA,
start=start_row,
end=end_row)
# Add the end dates for each of the targets
for target in raw_targets:
start = target["start"]
target["end"] = start + timedelta(days=7)
# Fix up the total investment targets
for raw_target in raw_targets:
for category in ["acq", "ret"]:
raw_target[f"target_{category}_investment"] = (
raw_target[f"target_{category}_expenses"]
["demand_creation"] +
raw_target[f"target_{category}_expenses"]
["leasing_enablement"] +
raw_target[f"target_{category}_expenses"]
["market_intelligence"] +
raw_target[f"target_{category}_expenses"]
["reputation_building"])
# Drop the extraneous period.
return raw_targets
def clean(self, ctx):
super().clean(ctx)
self.cleaned_data = self.clean_output_data()
self.cleaned_data["targets"] = self.clean_model_targets()
class CampaignPlanImporter(ProjectExcelImporter):
expected_type = "campaign"
expected_version = 1
AUDIENCE_CHOICES = ["Acquisition", "Retention"]
STATUS_CHOICES = ["Not Started", "In Progress", "Complete"]
COST_TYPE_CHOICES = ["One-Time", "Monthly", "Weekly"]
META_COL_SCHEMA = {
"campaign_months": IntCell(find_meta("months")),
"campaign_weeks": IntCell(find_meta("weeks")),
"campaign_days": IntCell(find_meta("days")),
}
CATEGORY_SCHEMA = {
"name": StrCell(find_cat("tactic")),
"audience": NullChoiceCell(find_cat("audience"),
choices=AUDIENCE_CHOICES),
"tooltip": NullStrCell(find_cat("tooltip")),
"schedule": NullStrDateCell(find_cat("schedule")),
"status": ChoiceCell(find_cat("status"), choices=STATUS_CHOICES),
"notes": NullStrCell(find_cat("notes")),
"base_cost": DefaultCurrencyCell(find_cat(matchp(iexact="cost"))),
"cost_type": ChoiceCell(find_cat("cost type"),
choices=COST_TYPE_CHOICES),
"total_cost": DefaultCurrencyCell(find_cat("total cost")),
}
FUNNEL_CATEGORY_SCHEMA = dict(
CATEGORY_SCHEMA,
**{
"volumes": {
"usv": IntCell(find_cat("# of usv")),
"inq": IntCell(find_cat("# of inq")),
},
"costs": {
"usv": DefaultCurrencyCell(find_cat("usv cost")),
"inq": DefaultCurrencyCell(find_cat("inq cost")),
},
},
)
OVERVIEW_TARGET_SEGMENT_SCHEMA = {
"ordinal": StrCell(loc("A")),
"description": StrCell(loc("B")),
}
OVERVIEW_TARGET_INVESMENT_SCHEMA = {
"category": StrCell(loc("A")),
"total": DefaultCurrencyCell(loc("B")),
"acquisition": DefaultCurrencyCell(loc("C")),
"retention": DefaultCurrencyCell(loc("D")),
}
CATEGORY_TO_KEY = {
"Reputation Building": "reputation_building",
"Demand Creation": "demand_creation",
"Leasing Enablement": "leasing_enablement",
"Market Intelligence": "market_intelligence",
"Total": "total",
}
def build_category(self, category):
rows = rows_until_empty(self.workbook,
start_row=2,
test_sheet=category,
test_col="A")
tactics = self.schema_list(schema=self.CATEGORY_SCHEMA,
locations=rows,
sheet=category)
return {"tactics": tactics}
def build_funnel_category(self, category):
rows = rows_until_empty(self.workbook,
start_row=2,
test_sheet=category,
test_col="A")
tactics = self.schema_list(schema=self.FUNNEL_CATEGORY_SCHEMA,
locations=rows,
sheet=category)
return {"tactics": tactics}
def locate_overview_header_cell(self, predicate):
predicate = predicate if callable(predicate) else matchp(
iexact=predicate)
return find_row("Overview!A", predicate)(self.workbook)
def build_markdown(self, start_row):
rows = rows_until_empty(self.workbook,
start_row=start_row,
test_location="Overview!B")
# CONSIDER it would be nice to have an analogue of Django ORM's values(flat=True)
text_dicts = self.schema_list({"text": StrCell(loc("Overview!B"))},
locations=rows)
texts = [text_dict["text"] for text_dict in text_dicts]
return "\n".join(
texts) + "\n" # Reasonable people demand trailing newlines.
def build_markdown_for_header(self, predicate):
_, _, row = self.locate_overview_header_cell(predicate)
return self.build_markdown(start_row=row)
def build_overview_target_segments(self):
_, _, row = self.locate_overview_header_cell("target segments")
rows = rows_until_empty(self.workbook,
start_row=row + 1,
test_location="Overview!A")
return self.schema_list(self.OVERVIEW_TARGET_SEGMENT_SCHEMA,
locations=rows,
sheet="Overview")
def build_overview_objective(self, category):
# This will find the first occurence of the header, which given
# our current schema (with target investment headers at the very bottom
# of column A) should work fine.
return {
"title": category,
"description": self.build_markdown_for_header(category),
}
def build_overview_objectives(self):
return [
self.build_overview_objective("Reputation Building"),
self.build_overview_objective("Demand Creation"),
self.build_overview_objective("Leasing Enablement"),
self.build_overview_objective("Marketing Intelligence"),
]
def build_overview_assumptions(self):
return self.build_markdown_for_header("assumptions")
def build_overview_target_investments(self):
# Find "Total" row and work backwards
_, _, row = self.locate_overview_header_cell("total")
rows = rows_until_empty(self.workbook,
start_row=row,
test_location="Overview!A",
next_fn=prev_row)
items = self.schema_list(self.OVERVIEW_TARGET_INVESMENT_SCHEMA,
locations=rows,
sheet="Overview")
# Convert dictionaries with the category key *inside* them
# into nested dictionaries where the category key is *outside*.
# aka {"category": "Reputation Building", "total": "100"} -->
# {"reputation_building": {"total": "100"}}
return dict((self.CATEGORY_TO_KEY[item.pop("category")], item)
for item in items)
def build_overview(self):
def overview_str(predicate):
return self.schema(StrCell(find_overview(predicate)))
return {
"theme": overview_str("theme"),
"target_segments": self.build_overview_target_segments(),
"goal": overview_str("goal"),
"objectives": self.build_overview_objectives(),
"assumptions": self.build_overview_assumptions(),
"schedule": overview_str("schedule"),
"target_investments": self.build_overview_target_investments(),
}
def build_meta(self):
return self.schema(schema=self.META_COL_SCHEMA, col="B")
def clean(self, ctx):
super().clean(ctx)
# Build the meta table
self.cleaned_data["meta"] = self.build_meta()
# Build for each category
self.cleaned_data["reputation_building"] = self.build_category(
"Reputation Building")
self.cleaned_data["demand_creation"] = self.build_funnel_category(
"Demand Creation")
self.cleaned_data["leasing_enablement"] = self.build_category(
"Leasing Enablement")
self.cleaned_data["market_intelligence"] = self.build_category(
"Market Intelligence")
# Build the overview
self.cleaned_data["overview"] = self.build_overview()
class MarketImporter(ProjectExcelImporter):
expected_type = "tam" # TODO rationalize our naming here.
expected_version = 1
RENT_TO_INCOME_CATEGORIES = [
"Low",
"Moderately Low",
"Target",
"Moderately High",
"High",
]
SEGMENT_OVERVIEW_SCHEMA = {
"age_group": StrCell(loc("Output!A")),
"market_size": IntCell(loc("Output!B")),
"usv": IntCell(loc("Output!C")),
"growth": FloatCell(loc("Output!D")),
"future_size": IntCell(loc("Output!E")),
}
POPULATION_RADIUS_SCHEMA = {
"center": {
"type": "Point",
"coordinates": [
FloatCell(find("coordinates", "C")),
FloatCell(find("coordinates", "B")),
],
},
"radius": FloatCell(find("tam type", "C")),
"units": ChoiceCell(find("tam type", "D"), choices=["mi", "km"]),
}
@staticmethod
def POPULATION_ZIP_SCHEMA(row):
return {
"zip": StrCell(loc(sheet="Output", row=row)),
"outline": None,
"properties": None,
}
@staticmethod
def RTI_SCHEMA(category):
return {
"name": category,
"low": FloatCell(loc("B")),
"high": FloatCell(loc("C")),
}
TOTAL_SCHEMA = {
"segment_population": IntCell(find("est. population")),
"market_size": IntCell(find("total market size")),
"usv": IntCell(find("total usv")),
"future_size": IntCell(find("future population size")),
}
AVERAGE_SCHEMA = {
"age": IntCell(find("total average age")),
"growth": FloatCell(find("total average growth")),
}
def get_location(self):
return self.schema(StrCell(find("city, state")))
def get_estimated_population_radius(self):
return self.schema(self.POPULATION_RADIUS_SCHEMA)
def get_estimated_population_zip(self):
_, _, row = find("tam type")(self.workbook)
cols = cols_until_empty(self.workbook, "C", test_sheet="Output", test_row=row)
zip_codes = self.schema_list(
schema=self.POPULATION_ZIP_SCHEMA(row), locations=cols
)
return {"zip_codes": zip_codes}
def get_estimated_population(self):
tam_type = self.schema(
ChoiceCell(find("tam type"), choices=["radius", "zipcodes"])
)
if tam_type == "radius":
result = self.get_estimated_population_radius()
else:
result = self.get_estimated_population_zip()
result["population"] = self.schema(IntCell(find("est. population")))
return result
def get_rent_to_income_category(self, category):
_, _, row = find(matchp(exact=category))(self.workbook)
return self.schema(schema=self.RTI_SCHEMA(category), sheet="Output", row=row)
def get_rent_to_income_categories(self):
return [
self.get_rent_to_income_category(category)
for category in self.RENT_TO_INCOME_CATEGORIES
]
def get_rent_to_income_incomes(self):
_, _, row = find("rent | incomes")(self.workbook)
cols = cols_until_empty(self.workbook, "B", test_sheet="Output", test_row=row)
return self.schema_list(
CurrencyCell(loc(sheet="Output", row=row)), locations=cols
)
def get_rent_to_income_rental_rates(self):
_, _, row = find("rent | incomes")(self.workbook)
rows = rows_until_empty(self.workbook, next_row(row), test_location="Output!A")
return self.schema_list(CurrencyCell(loc("Output!A")), locations=rows)
def get_rent_to_income_data(self, income_count, rental_rate_count):
_, _, row = find("rent | incomes")(self.workbook)
locations = location_range_rect(
start_col="B",
end_col=advance_col(income_count - 1)("B"),
start_row=next_row(row),
end_row=advance_row(rental_rate_count)(row), # +1-1=0
sheet="Output",
row_major=False,
)
return self.schema_rect(FloatCell(), locations=locations)
def get_rent_to_income(self):
categories = self.get_rent_to_income_categories()
incomes = self.get_rent_to_income_incomes()
rental_rates = self.get_rent_to_income_rental_rates()
data = self.get_rent_to_income_data(
income_count=len(incomes), rental_rate_count=len(rental_rates)
)
return {
"categories": categories,
"incomes": incomes,
"rental_rates": rental_rates,
"data": data,
}
def update_segment_details(self, segment):
_, _, row = find(f"age segment: {segment['age_group']}")(self.workbook)
cols = list(
cols_until(
self.workbook,
matchp(exact="All"),
"B",
test_sheet="Output",
test_row=row,
)
)
def _find(predicate):
return find_row("Output!A", predicate, start_row=row)
schema = {
"income": CurrencyCell(_find("age segment")),
"group_population": IntCell(_find("population")),
"home_owners": {
"total": IntCell(_find("home owners")),
"family": IntCell(_find("family ho")),
"nonfamily": IntCell(_find("non-family ho")),
},
"renters": {
"total": IntCell(_find("renters")),
"family": IntCell(_find("family r")),
"nonfamily": IntCell(_find("non-family r")),
},
"market_size": IntCell(_find("est. market")),
"active_populations": ["renters.nonfamily", "renters.family"],
}
segment["income_groups"] = self.schema_list(
schema, locations=cols, sheet="Output"
)
segment["segment_population"] = self.schema(
IntCell(loc(sheet="Output", row=next_row(row), col=next_col(cols[-1])))
)
def get_segments(self):
_, _, row = find("target segment")(self.workbook)
rows = rows_until_empty(self.workbook, next_row(row), test_location="Output!A")
# Grab the overviews for each segment
segments = self.schema_list(self.SEGMENT_OVERVIEW_SCHEMA, locations=rows)
for segment in segments:
self.update_segment_details(segment)
return segments
def get_future_year(self):
return self.schema(IntCell(find("future year")))
def get_total(self):
return self.schema(self.TOTAL_SCHEMA)
def get_average(self):
return self.schema(self.AVERAGE_SCHEMA)
def clean(self, ctx):
super().clean(ctx)
self.cleaned_data["location"] = self.get_location()
self.cleaned_data["estimated_population"] = self.get_estimated_population()
self.cleaned_data["rent_to_income"] = self.get_rent_to_income()
self.cleaned_data["segments"] = self.get_segments()
self.cleaned_data["future_year"] = self.get_future_year()
self.cleaned_data["total"] = self.get_total()
self.cleaned_data["average"] = self.get_average()
def get_future_year(self):
return self.schema(IntCell(find("future year")))
class BaselinePerfImporter(ProjectExcelImporter):
expected_type = "baseline_perf"
expected_version = 1
DATES_VALID = ChoiceCell(find_meta("dates_valid"),
choices=["valid", "invalid"])
BASELINE_PERIODS = IntCell(find_meta("baseline_periods"))
START_ROW = IntCell(find_meta("first_baseline_row"))
END_ROW = IntCell(find_meta("last_perf_row"))
BASELINE_START = DateCell(find_meta("baseline_start_date"))
BASELINE_END = DateCell(find_meta("baseline_end_date"))
PERIOD_SCHEMA = {
"start": DateCell(find_period("start date")),
"end": DateCell(find_period("end date")),
"lease_stage_str": LeaseStagesCell(find_period("lease stage")),
"leased_units_start": IntCell(find_period("leased units @ start")),
"leased_units_end": IntCell(find_period("leased units @ end")),
"leases_ended": IntCell(find_period("ended")),
"lease_applications": IntCell(find_period("APPs")),
"leases_executed": IntCell(find_period("EXEs")),
"lease_cds": IntCell(find_period("CDs")),
"lease_renewal_notices": IntCell(find_period("Notices: Renewals")),
# Use matchp(iexact=...) to disambiguate with "Notices: Renewals"
"lease_renewals": IntCell(find_period(matchp(iexact="Renewals"))),
"lease_vacation_notices": IntCell(find_period("Notices: Vacate")),
"occupiable_units_start": IntCell(find_period("occupiable units")),
"occupied_units_start": IntCell(find_period("occupied units @ start")),
"occupied_units_end": IntCell(find_period("occupied units @ end")),
"move_ins": IntCell(find_period("move ins")),
"move_outs": IntCell(find_period("move outs")),
"acq_reputation_building": CurrencyCell(find_period("Reputation ACQ")),
"acq_demand_creation": CurrencyCell(find_period("Demand ACQ")),
"acq_leasing_enablement": CurrencyCell(find_period("Leasing ACQ")),
"acq_market_intelligence": CurrencyCell(find_period("Market ACQ")),
"ret_reputation_building": CurrencyCell(find_period("Reputation RET")),
"ret_demand_creation": CurrencyCell(find_period("Demand RET")),
"ret_leasing_enablement": CurrencyCell(find_period("Leasing RET")),
"ret_market_intelligence": CurrencyCell(find_period("Market RET")),
"usvs": IntCell(find_period("USVs")),
"inquiries": IntCell(find_period("INQs")),
"tours": IntCell(find_period("TOUs")),
}
def check_meta(self):
"""
Validate that the basic contents of our META tab are valid.
"""
self.check_value(self.DATES_VALID, expected="valid")
self.check_value(self.BASELINE_PERIODS,
expected=lambda value: value > 0)
def clean(self, ctx):
super().clean(ctx)
self.check_meta()
start_row = self.schema(self.START_ROW)
end_row = self.schema(self.END_ROW)
self.cleaned_data["baseline_start"] = self.schema(self.BASELINE_START)
self.cleaned_data["baseline_end"] = self.schema(self.BASELINE_END)
self.cleaned_data["periods"] = self.schema_list(
schema=self.PERIOD_SCHEMA, start=start_row, end=end_row)
# Sanity check that there is at least one period
if not self.cleaned_data["periods"]:
raise ExcelValidationError(
"BaselinePerfImporter.clean: Unable to load any periods from the spreadsheet."
)
# Sanity check that our first period has the same start date as the
# baseline start we pulled from the spreadsheet meta tab; if not, blow up.
baseline_start = self.cleaned_data["baseline_start"]
period_start = self.cleaned_data["periods"][0]["start"]
if baseline_start != period_start:
raise ExcelValidationError(
f"BaselinePerfImporter.clean: The spreadsheet looks broken. The first baseline period starts on {baseline_start} but the first period starts on {period_start}."
)
# Sanity check that period end dates are always lexically after the start dates
prev_period = None
for period in self.cleaned_data["periods"]:
if period["start"] >= period["end"]:
raise ExcelValidationError(
f"BaselinePerfImporter.clean: The spreadsheet looks broken. There is a period that begins on {period['start']} but ends *at or before* that, on {period['end']}."
)
if prev_period is not None and prev_period["end"] > period["start"]:
raise ExcelValidationError(
f"BaselinePerfImporter.clean: The spreadsheet looks broken. There is a period that ends ({prev_period['end']} after the next period begins ({period['start']}"
)