Python 64.6%
TypeScript 33.7%
CSS 0.8%
1"""Template: adjusted comparables grid (sales comparison) with live formulas.23Two ways to feed it (both tolerant to strings like "425 000 $", "+3 %", "Oui", "bon") :45A) Characteristics + unit rates (preferred — adjustments are computed by Excel formulas):6 sujet: {adresse, superficie: 1200, terrain: 6000, garage: "Oui", etat: "bon", ...}7 taux: {superficie: 120, terrain: 8, garage: 15000, etat: 10000} # $ per unit / per step8 echelles: {etat: ["mauvais", "moyen", "bon", "très bon"]} # ordinal scales (optional)9 taux_temps_mensuel: 0.005 # market trend (optional)10 comparables: [{adresse, prix, date, mois: 3, superficie: 1150, terrain: 5500, garage: "Oui",11 etat: "bon"}, ...]12 → ajustement = (sujet − comparable) × taux, in the workbook, cell by cell.1314B) Direct dollar adjustments (no `taux`): comparables: [{adresse, prix, temps: "+2 %",15 ajustements: {Superficie: -5000, Garage: -12000}, caracteristiques: {Garage: "Oui"}}]16"""1718from __future__ import annotations1920import unicodedata21from typing import Any2223from openpyxl.utils import get_column_letter as L2425from app.tools.coerce import boolish, cell, num, pct2627META = {"adresse", "nom", "comparable", "prix", "prix_vente", "date", "date_vente", "temps",28 "ajustement_temps_pct", "date_pct", "ajust_temps", "mois", "ajustements",29 "caracteristiques", "notes", "note", "source", "filename"}30QUALITY_WORDS = ["très mauvais", "mauvais", "passable", "moyen", "bon", "très bon", "excellent", "neuf"]3132DEFAULT = {33 "sujet": {"adresse": "Sujet — bungalow fictif, Gatineau", "superficie": 1200, "terrain": 6000,34 "garage": "Oui", "etat": "bon"},35 "taux": {"superficie": 120, "terrain": 8, "garage": 15000, "etat": 10000},36 "taux_temps_mensuel": 0.005,37 "comparables": [38 {"adresse": "45 rue Laurier", "prix": 425000, "date": "2026-03", "mois": 6, "superficie": 1150,39 "terrain": 5500, "garage": "Oui", "etat": "bon"},40 {"adresse": "12 rue Front", "prix": 398000, "date": "2026-01", "mois": 8, "superficie": 1250,41 "terrain": 7000, "garage": "Non", "etat": "moyen"},42 {"adresse": "88 boul. Saint-Joseph", "prix": 449000, "date": "2026-04", "mois": 5,43 "superficie": 1300, "terrain": 6200, "garage": "Oui", "etat": "très bon"},44 ],45}464748def _norm(s: str) -> str:49 s = unicodedata.normalize("NFD", str(s)).encode("ascii", "ignore").decode().lower().strip()50 return s.replace(" ", "_").replace("-", "_")515253def _label(key: str) -> str:54 return key.replace("_", " ").strip().capitalize()555657def _rank(value: Any, scale: list[str] | None) -> float | None:58 """Ordinal text → rank (0-based). Booleans → 1/0. Numbers pass through."""59 n = num(value, None)60 if n is not None and not isinstance(value, bool):61 return n62 b = boolish(value)63 if b is not None and not (isinstance(value, str) and scale and _norm(value) in [_norm(x) for x in scale]):64 return 1.0 if b else 0.065 if isinstance(value, str):66 words = [_norm(x) for x in (scale or QUALITY_WORDS)]67 v = _norm(value)68 if v in words:69 return float(words.index(v))70 for i, w in enumerate(words): # "bon état" → "bon"71 if w and w in v:72 return float(i)73 return None747576def _time_pct(c: dict[str, Any]) -> float | None:77 for k in ("temps", "ajustement_temps_pct", "date_pct", "ajust_temps"):78 if k in c:79 return pct(c[k], None)80 return None818283def build(p: dict[str, Any]) -> dict[str, Any]:84 if not p.get("comparables"):85 p = {**DEFAULT, **{k: v for k, v in p.items() if k != "comparables"}} if p.get("taux") is None else {**DEFAULT, **p}86 comps_raw = [c if isinstance(c, dict) else {"adresse": str(c)} for c in (p.get("comparables") or [])]87 taux_raw = {_norm(k): v for k, v in (p.get("taux") or p.get("unit_rates") or p.get("taux_unitaires") or {}).items()}88 scales = {_norm(k): list(v) for k, v in (p.get("echelles") or p.get("scales") or {}).items() if isinstance(v, list)}89 sujet_raw = p.get("sujet") or {}90 sujet: dict[str, Any] = {_norm(k): v for k, v in sujet_raw.items()} if isinstance(sujet_raw, dict) else {}91 sujet_label = (sujet_raw.get("adresse") or sujet_raw.get("nom") if isinstance(sujet_raw, dict)92 else str(sujet_raw or "Sujet — immeuble fictif, Gatineau")) or "Sujet"93 monthly = pct(p.get("taux_temps_mensuel", p.get("taux_temps_pct_mensuel")), None)94 mode_rates = bool(taux_raw)9596 # ---- characteristics (mode A) or adjustments (mode B)97 keys: list[str] = []98 for c in comps_raw:99 for k, v in c.items():100 nk = _norm(k)101 if nk in META or isinstance(v, (dict, list)):102 continue103 if nk not in keys:104 keys.append(nk)105 for k in (c.get("ajustements") or {}) if isinstance(c.get("ajustements"), dict) else {}:106 if _norm(k) not in keys:107 keys.append(_norm(k))108 for k in taux_raw:109 if k not in keys and k in sujet:110 keys.append(k)111 if mode_rates:112 keys = [k for k in keys if k in taux_raw] # only characteristics with a rate are adjusted113 text_keys: list[str] = [] # qualitative columns kept as text (mode B or no rank)114115 n = len(comps_raw)116 sheet: dict[str, Any] = {"name": "Comparables", "title": f"Grille de comparables ajustés — {sujet_label}",117 "inputs": [], "tables": [], "charts": [], "notes": []}118 row = 4119 inputs_map: dict[str, str] = {} # key → cell of subject value / rate120121 if mode_rates:122 sheet["inputs_title"] = "Hypothèses (cellules bleues modifiables)"123 # subject values in column B, rates in column D (labels in A and C)124 r = row125 for k in keys:126 sv = _rank(sujet.get(k), scales.get(k))127 if sv is None:128 sv = 0.0129 sheet["inputs"].append({"cell": f"B{r}", "label": f"Sujet — {_label(k)}"130 + (f" ({'/'.join(scales[k])})" if k in scales else ""),131 "value": sv, "format": "number"})132 sheet["inputs"].append({"cell": f"D{r}", "label": f"Taux — {_label(k)} ($/unité ou $/cran)",133 "value": num(taux_raw.get(k), 0.0) or 0.0, "format": "currency"})134 inputs_map[k] = f"$B${r}"135 inputs_map[k + "__taux"] = f"$D${r}"136 r += 1137 if monthly is not None:138 sheet["inputs"].append({"cell": f"D{r}", "label": "Tendance du marché (%/mois)", "value": monthly,139 "format": "percent"})140 inputs_map["__monthly"] = f"$D${r}"141 r += 1142 row = r + 2143144 # ---- main grid145 header_row = row146 first = header_row + 1147 last = first + n - 1148 columns: list[dict[str, Any]] = [{"header": "Comparable", "type": "text"}, {"header": "Prix de vente ($)", "type": "currency"},149 {"header": "Date de vente", "type": "text"}]150 col = 4 # D151 if mode_rates and monthly is not None:152 columns += [{"header": "Mois écoulés", "type": "number"}, {"header": "Ajust. temps (%)", "type": "percent"}]153 c_mois, c_temps = col, col + 1154 col += 2155 else:156 columns += [{"header": "Ajust. temps (%)", "type": "percent"}]157 c_mois, c_temps = None, col158 col += 1159 columns.append({"header": "Prix ajusté temps ($)", "type": "currency"})160 c_pat = col161 col += 1162 c_char: dict[str, int] = {}163 c_adj: dict[str, int] = {}164 if mode_rates:165 for k in keys:166 columns.append({"header": f"{_label(k)} (comp.)", "type": "number"})167 c_char[k] = col168 col += 1169 for k in keys:170 columns.append({"header": f"Ajust. {_label(k)} ($)", "type": "currency"})171 c_adj[k] = col172 col += 1173 else:174 for k in keys:175 columns.append({"header": f"{_label(k)} ($)", "type": "currency"})176 c_adj[k] = col177 col += 1178 c_total, c_final, c_gross, c_gross_pct = col, col + 1, col + 2, col + 3179 columns += [{"header": "Total ajustements ($)", "type": "currency"}, {"header": "Prix ajusté ($)", "type": "currency"},180 {"header": "Ajust. bruts ($)", "type": "currency"}, {"header": "Ajust. bruts (%)", "type": "percent"}]181182 rows: list[list[Any]] = []183 chars_text: list[list[Any]] = []184 for i, c in enumerate(comps_raw):185 cn = {_norm(k): v for k, v in c.items()}186 r = first + i187 name = str(cn.get("adresse") or cn.get("nom") or cn.get("comparable") or f"Comparable {i + 1}")188 line: list[Any] = [name, num(cn.get("prix", cn.get("prix_vente")), 0.0) or 0.0,189 str(cn.get("date") or cn.get("date_vente") or "—")]190 tp = _time_pct(cn)191 if c_mois is not None:192 line.append(num(cn.get("mois"), 0.0) or 0.0)193 line.append(f"={L(c_mois)}{r}*{inputs_map['__monthly']}" if tp is None else tp)194 else:195 line.append(tp if tp is not None else 0.0)196 line.append(f"=B{r}*(1+{L(c_temps)}{r})")197 adj_vals = {_norm(k): v for k, v in (cn.get("ajustements") or {}).items()} if isinstance(cn.get("ajustements"), dict) else {}198 text_row: list[Any] = [name]199 if mode_rates:200 for k in keys:201 rank = _rank(cn.get(k), scales.get(k))202 line.append(rank if rank is not None else 0.0)203 for k in keys:204 line.append(f"=({inputs_map[k]}-{L(c_char[k])}{r})*{inputs_map[k + '__taux']}")205 for k, v in cn.items():206 if k not in META and k not in keys and not isinstance(v, (dict, list)):207 if k not in text_keys:208 text_keys.append(k)209 else:210 for k in keys:211 v = adj_vals.get(k, cn.get(k))212 nv = num(v, None)213 if nv is None:214 line.append(0.0)215 if v not in (None, ""):216 if k not in text_keys:217 text_keys.append(k)218 elif isinstance(v, str) and v.strip().endswith("%"):219 line.append(f"={L(c_pat)}{r}*{nv}")220 else:221 line.append(nv)222 chars_text.append(text_row)223 adj_range = f"{L(min(c_adj.values()))}{r}:{L(max(c_adj.values()))}{r}" if c_adj else None224 line.append(f"=SUM({adj_range})" if adj_range else 0)225 line.append(f"={L(c_pat)}{r}+{L(c_total)}{r}")226 line.append(f"=SUMPRODUCT(ABS({adj_range}))" if adj_range else 0)227 line.append(f"=IF({L(c_pat)}{r}=0,0,{L(c_gross)}{r}/{L(c_pat)}{r})")228 rows.append(line)229 sheet["tables"].append({"anchor": f"A{header_row}", "columns": columns, "rows": rows})230231 # ---- statistics232 F, G = L(c_final), L(c_gross_pct)233 s0 = last + 3234 sheet["tables"].append({235 "anchor": f"A{s0}",236 "columns": [{"header": "Statistique", "type": "text"}, {"header": "Valeur", "type": "currency"}],237 "rows": [238 ["Minimum des prix ajustés", f"=MIN({F}{first}:{F}{last})"],239 ["Maximum des prix ajustés", f"=MAX({F}{first}:{F}{last})"],240 ["Moyenne simple (indicatif)", f"=AVERAGE({F}{first}:{F}{last})"],241 ["Médiane", f"=MEDIAN({F}{first}:{F}{last})"],242 ["Comparable le moins ajusté (rang)", f"=MATCH(MIN({G}{first}:{G}{last}),{G}{first}:{G}{last},0)"],243 ["Prix ajusté du comparable le moins ajusté", f"=INDEX({F}{first}:{F}{last},B{s0 + 5})"],244 ["Pondération réconciliée (poids inverses des ajust. bruts)",245 f"=SUMPRODUCT({F}{first}:{F}{last},1/(1+{G}{first}:{G}{last}))/SUMPRODUCT(1/(1+{G}{first}:{G}{last}))"],246 ],247 "row_formats": {4: "integer"},248 })249 next_row = s0 + 10250251 # ---- qualitative characteristics kept as text252 if text_keys:253 sheet["tables"].append({254 "anchor": f"A{next_row}",255 "columns": [{"header": "Comparable", "type": "text"}] + [{"header": _label(k), "type": "text"} for k in text_keys],256 "rows": [[str(c.get("adresse") or c.get("nom") or f"Comparable {i + 1}")]257 + [cell({_norm(k2): v for k2, v in c.items()}.get(k, "—")) for k in text_keys]258 for i, c in enumerate(comps_raw)],259 })260 if mode_rates and scales:261 sheet["notes"].append("Échelles ordinales : " + " ; ".join(f"{_label(k)} = {' < '.join(v)} (rang 0, 1, 2…)" for k, v in scales.items()))262 sheet["notes"] += [263 "Ajustement = (caractéristique du sujet − celle du comparable) × taux : le comparable est meilleur → ajustement négatif."264 if mode_rates else "On ajuste le comparable vers le sujet : le comparable est meilleur → ajustement négatif.",265 "Ordre des ajustements : conditions de vente, financement, marché (temps), puis caractéristiques physiques.",266 "La réconciliation pondère les indications (plus de poids au comparable le moins ajusté) ; la moyenne simple n'est qu'indicative.",267 "Repères du cours : ajustements bruts ≤ 25 % et nets ≤ 15 % pour un comparable fiable.",268 ]269 return {"filename": p.get("filename", "comparables_ajustes.xlsx"), "style": "uqo",270 "objective": f"Grille de comparables ajustés — {sujet_label}", "sheets": [sheet]}271