xushuwenn/redact/reserves-at-risk-calc

Reserves-at-Risk Calculation

When to Use

• Calculate commodity volatility metrics
• Analyze gold price exposure
• Compute Reserves-at-Risk (RaR) for countries

Critical Constraints

• Start from the provided workbook/template if the task refers to existing sheets, named tabs, blanks, or exact rows/cells; preserve the workbook structure rather than creating a new workbook or redesigning the layout.
• Search allowed directories for the input workbook before creating anything new. If the required workbook or referenced source sheets are truly missing after verification, stop and report the blocker; do not invent a replacement template or fabricate country rows, reserve values, or source data.
• Perform a systematic workbook search before declaring the template missing: check the task-provided path first, then search allowed working directories for plausible .xlsx, .xlsm, or .xls files; open the best candidate and verify it contains the task-referenced sheets and answer area before editing. If the workbook, referenced sheets, or required 2025 source rows/cells are still missing after verification, stop and report the blocker; do not create a substitute workbook, replacement sheets, copied layouts, web-derived substitute tables, or estimated values.
• Inspect the workbook before editing and confirm the relevant sheets (Gold price, Value, Volume, Total Reserves, Answer), actual header rows, country labels, answer-area rows, and date layout.
• Treat workbook reconnaissance as a gate before any serious external search: after confirming the workbook exists, inspect the actual Answer, Gold price, Value, Volume, and Total Reserves contents (not just sheet names) to identify row meanings, 2025 locations, formulas, and required target cells.
• Do not spend most of the run hunting IMF links before understanding the workbook. External retrieval is secondary and only justified after targeted workbook inspection shows exactly what local data is missing.
• If the task requires Excel-only computation or specific worksheet functions, keep returns, volatilities, averages, lookups, filtering, and RaR in workbook formulas. Use Python/openpyxl only to inspect the file, place formulas/text, copy ranges, and save; do not compute final task logic externally and paste numeric outputs.
• Treat method requirements as deliverable requirements. Keep returns, averages, volatility, filtering, reserve conversions, lookups, exposures, and RaR expressed in worksheet formulas in the specified cells. Python/openpyxl may inspect workbook structure, place formulas/text, copy ranges, save, reopen, recalculate, and verify only when the task allows it; it must not become the main calculation path or compute intermediate/final task logic for paste-back. If the task explicitly forbids external scripting/editing entirely, stop and report that tooling blocker rather than completing the workbook by a forbidden method.
• When the task names a required worksheet formula family or pattern (INDEX+MATCH, XLOOKUP, etc.), implement that method literally in the workbook cells; do not substitute direct references, Python dictionaries, or hard-coded pasted values.
• Use only workbook data or explicitly authorized external data. Do not invent, estimate, backfill, or substitute missing country/reserve values unless the task explicitly authorizes it.
• Preserve source sheets and unrelated cells; do not blank, overwrite, or "fix" input cells just to suppress formula/recalc errors.
• Do not modify pre-existing source/template cells outside the requested answer/output area just to clear workbook-wide errors, and do not rename sheets/tabs or rewrite workbook structure for tool compatibility. Repair the required formulas/ranges within the existing workbook semantics, or exclude a country only when workbook evidence shows there is truly no valid 2025 match.
• If the task specifies an exact completion token or protocol, follow it verbatim.
• Treat any exact completion token/protocol as a blocking deliverable: before the final response, re-check the instructions and output the exact required string alone with no extra prose, code fences, or summary text.
• Before performing any extraction, download, formula writing, or workbook creation, confirm the full deliverable scope from the task and verify that every referenced workbook/template dependency needed for all requested steps is present or truly missing after search. Do not begin Step 1 work on the assumption that later-step inputs will appear later.
• Re-check the environment/task protocol before any substantive work and again before the final response. Treat any required interaction/action/tool/interface syntax as mandatory for the entire run, not just the final message; do not improvise alternate tool-call styles, wrappers, helper tools, malformed action formats, or narrative final responses when an exact terminator is required.
• If the workbook file is .xlsx/.xlsm/.xls, inspect it with spreadsheet-aware tooling or Python workbook libraries, not plain-text file readers.
• Run a final pre-response gate: compare your planned last message character-for-character against the required completion protocol and remove any summary, explanation, code fence, filename reminder, or trailing text. If a specific token is required, output that token alone.

• If required source data is unavailable from the workbook or the explicitly authorized source, stop and report the blocker rather than mixing substitute external datasets with different definitions, years, or coverage. Do not combine unreconciled figures from multiple websites just to fill the workbook.
• Treat any plan to populate Value, Volume, Total Reserves, or other source sheets with sample, placeholder, provisional, or manual stand-in data as forbidden unless the task explicitly authorizes creating those inputs.
• Prefer minimal in-place repairs to the discovered workbook. Do not reorganize the Answer sheet, rebuild the workbook, recreate sheets, or rewrite layout/structure as a troubleshooting tactic when formulas can be repaired within the existing template.
• Track workbook identity explicitly by absolute path. Before inspecting or editing, print/read back the exact source workbook path you opened; before saving, print/read back the exact destination path. Do not reason from sheet listings or cell reads taken from a different workbook than the one you will modify.
• Treat your own validation findings as authoritative blocking signals. If recalculation or validation reports any remaining workbook error after your edits, do not declare success unless you can prove it is outside the deliverable scope, untouched by your changes, and not referenced by required outputs.
• Treat filesystem restrictions as blocking constraints. Before creating any script, temp file, or output workbook, confirm the path is explicitly authorized by the task/context.

Multi-Step Process

Before Step 1

• Treat the workbook as the source of truth for country lists, 2025 rows, headers, blank answer areas, and required row meanings.
• First parse the task into a full prerequisite checklist: required workbook/template file, required sheets, required source data for Steps 1-3, required output path, and any exact completion token. Do not start implementation until this checklist is reconciled against the workspace.
• Verify workbook layout from existing labels/formulas before bulk-writing anything; derive formulas from workbook evidence and explicit task text, not guessed interpretations or assumed row/column positions.
• Do not assume 2025 is on a fixed row or column; inspect the relevant source sheets to verify the actual year row/column, header row, and data ranges.
• Before extracting any 2025 country set from Value, Volume, or Total Reserves, explicitly identify the year-label row/column and read back a few candidate 2025 intersections. If the detected year/header position is missing or ambiguous, stop and inspect more narrowly rather than continuing with None or guessed indices.
• For country matching across sheets, use exact workbook labels or a verified one-to-one mapping derived from the workbook. Do not use prefix, substring, or fuzzy matching.
• Before bulk-filling formulas, write 1-2 representative cells, inspect the exact stored formula strings, and recalculate/check results.
• When writing formulas programmatically, verify the stored text is a plain Excel formula with valid quoting and sheet references before any fill-down. Do not write malformed strings like =\"Gold price\"!D430; use ='Gold price'!D430.
• Use a pilot write-recalc-readback loop for each formula family: write 1-2 representative cells, confirm the stored text begins with = and uses valid sheet/range syntax, recalculate, then verify the evaluated result and required formula method before filling the rest. Treat formula-engine compatibility as a hard gate before bulk fill: default to compatibility-safe formulas first when the task/template does not explicitly require a newer function (for example INDEX+MATCH over XLOOKUP). In that pilot check, verify formula dialect and arity too: use the separators expected by the workbook/engine, confirm functions have the correct number of arguments, and inspect the exact stored formula string after writing. If a sample throws #NAME?, parse errors, stray quoting, malformed cross-sheet references, blanks, wrong-row placement, extra/missing arguments, or separator mismatches, stop and fix that pattern before any bulk fill; do not spread an unverified formula family across the workbook and patch it later.
• Do not fill any required answer cell while the mapping, row meaning, or calculation interpretation is still uncertain. Resolve ambiguity from workbook labels, units, neighboring formulas, and task text first; if that still does not resolve it, stop and report the blocker instead of guessing.
• If external data access is blocked or repeatedly fails, stop retrying similar URLs and complete all workbook-dependent steps you can from local contents first.

• Start with file discovery and workbook reconnaissance: open the real workbook/template first, then confirm actual sheet names, header rows, country/year label locations, date/value formats, answer-area row meanings, and the source ranges needed for Steps 2 and 3 before writing anything.
• If any inspection output is truncated or incomplete, rerun narrower targeted reads until the relevant rows/columns/countries/date windows are fully visible; do not derive country lists, 2025 availability, mappings, or target ranges from partial previews.
• If a read returns placeholders, invalid paths, logging artifacts, binary-file previews, or other non-workbook content, treat that as a failed inspection and retry with a narrower, explicit workbook/sheet read before editing. Do not proceed from partial previews, placeholder text, or ambiguous inspection output.
• Record the exact Step 2 and Step 3 destination rows/cells from the existing Answer sheet before writing. Treat nearby labels/instructions as definitions to read, not blanks to reinterpret; do not shift, expand, relocate, or redesign the answer blocks.
• Before filling any target block, clear or overwrite only the intended destination cells and read them back to confirm stale formulas, duplicate countries, or leftovers from earlier attempts are gone.
• For country matching across sheets, use exact workbook labels or a conservative one-to-one normalization proven by workbook evidence; do not use prefix, substring, fuzzy matching, naive alias dictionaries, or manual country-to-column assumptions.