CPI escalation tracker for leases and vendor contracts

Build me an app my finance team opens every month to manage the contracts whose rates are tied to inflation indexes. These are leases and vendor agreements with CPI escalation clauses, and today the work happens in a spreadsheet with manually pulled index values, which stops being reliable past a few dozen contracts. The app should replace that ritual with a register I can review, audit, and act on.

The main screen is a contract register read from a Google Sheets tab using Get Values. Each row is one contract and carries at least: contract name or ID, counterparty, current rate, base rate, base index period (the month the rate was set from), the FRED series ID the clause is tied to, lookback type, cap, floor, review month, and columns for the last applied rate, effective date, and the index vintage used. Read the header row and map columns by name rather than fixed positions, so the sheet can carry extra columns without breaking the app. Treat the sheet as the single source of truth: there is no separate database, and everything the app persists goes back into these columns.

Beside every contract, show the current index level pulled live from FRED with Get Series Observations for that row's series ID, plus the calculated new rate and the dollar impact versus the current rate. Show the percentage change, the new rate, and the annualized dollar delta. FRED is read only, so nothing about the index data is ever written anywhere except as a recorded value on an applied row. Batch or cache the index reads sensibly, since many rows will share the same series (FRED allows 120 requests per minute), and note that missing observations come back as the literal string "." and must be coerced before any math.

The calculation rules matter more than the UI polish, so bake these in. Support both a 12 month lookback (compare the index at the review period against the index 12 months earlier) and a multi year lookback (compare the review period against the contract's fixed base index period, however many years back that is). Honor caps and floors on every row: if the raw index change is 6.1 percent and the cap is 4 percent, the applied change is 4 percent, and the UI must show both the raw figure and the capped result with a clear marker that the cap bound the outcome. Same for floors, including negative index movement where a floor of zero prevents a decrease. Never silently swallow the difference between raw and bounded.

Pin a "Due in the next 90 days" view at the top of the register, filtered on the review month column, sorted by soonest first, and showing the dollar impact so the largest exposures are obvious. The rest of the portfolio lists underneath, filterable by counterparty, series, and whether a contract is capped. Make the 90 day window a setting.

Selecting a contract opens a detail page that makes the number auditable. Chart the index history behind the calculation using Get Series Observations over a range wide enough to cover the lookback, mark the two observation points the calculation used, and pull the series metadata with Get Series so the page can name the index in full (title, units, frequency, seasonal adjustment). Show the exact index vintage used: the observation period, the value, and the publication vintage of that value, using FRED's realtime parameters, and optionally Get Series Vintage Dates to show when the reading was last revised. Someone reviewing this six months later should be able to see precisely which published number drove the rate, since CPI values are revised.

An Apply action on the detail page writes the approved new rate and effective date back to the sheet with Update Values, stamping the applied rate, the effective date, the series ID, the observation period and value used, and the vintage into that contract's row. It then posts a summary to our finance channel in Slack with Send a Message, covering the contract, the old and new rate, the percentage change, whether a cap or floor applied, the index reading behind it, and who applied it. This is the hard rule of the whole app: never auto apply an increase. The app calculates, displays, and waits. No background job, no scheduled sweep, and no bulk action may write a rate change without a human clicking Apply on that contract. A bulk review queue is fine, but each Apply is its own deliberate click.

Include a "Map this clause" button that kicks off a background agent. I paste the raw escalation language out of the contract, for example "adjusted annually by the increase in the Consumer Price Index for All Urban Consumers, All Items, not to exceed 5 percent". The agent reads the clause, searches FRED with Search Series for the index it describes (national CPI-U all items such as CPIAUCSL, or a regional or metro CPI when the clause names a geography), confirms the candidate with Get Series to check the title, units, frequency, and that it is still actively updated, and then proposes back into the app: the series ID, the lookback window it read out of the clause, and how caps and floors should be handled. Show the proposal in the app with the agent's reasoning and the candidate series it considered, and let me accept or override every field. Accepting writes the mapping into that contract's row with Update Values; overriding lets me set the series and parameters myself. The agent proposes mappings only, and never applies a rate.

Design it for a finance person, not a developer: currency and percentages formatted properly, negative and capped outcomes visually distinct, and every screen that shows a calculated rate also showing the index reading and vintage that produced it. The Slack message should be attributed to the person who applied the change, since it doubles as the approval record.