BACK TO TD-SECURITIES

BASKET/TICKER Pricing

challenge

  1. Missing Historical Data: Ticker prices often have gaps (missing days), which breaks the entire basket calculation logic.

  2. Multi-Currency Chaos: Baskets mix various composition currencies (e.g., CAD, GBP) but require a unified output currency (e.g., USD). This requires processing 10+ years of daily data across mismatched files.

  3. Date Misalignment: Stock markets and FX markets have different holidays and "closed days," making standard lookups (like VLOOKUP) unreliable as dates rarely match perfectly.

  4. Incompatible Data Sources: Our validation source (MDS) provides data via a web-linked JSON format. Unlike Bloomberg, there is no native Excel formula to read this.

  5. The "GBp" Scaling Trap: Currencies like British Pence (GBp) are numerically 100x larger than Pounds (GBP). Missing this scaling factor destroys valuation accuracy.

challenge

solution

  1. Index Fall-back Logic: I implemented a proxy system. If a ticker price is missing, the macro grabs the daily return of its corresponding benchmark Index to mathematically "back-fill" the gap.


  2. Automated Conversion Pipeline: I wrote a nested looping architecture that identifies specific naming conventions in our database. It automatically imports the correct FX rate files based on the ticker's currency to handle the conversion before calculation.


  3. Smart Interpolation: Instead of simple lookups, I used XLOOKUP combined with an averaging algorithm. For missing FX dates, the system calculates the average rate from surrounding closed days to bridge the gap.


  4. Custom JSON Engine: I wrote a custom VBA ReadJson function from scratch. It parses the raw web data, navigates the JSON structure, and extracts the exact "Last Price" field for validation.


  5. Metadata-Driven Scaling: The tool pulls currency metadata via Bloomberg Add-in. I added a conditional logic layer (If Currency = "GBp" Then Price * 0.01) to automatically normalize units, eliminating the most common human error in the process.

Key Impact

This macro transformed a process that used to require high levels of concentration and manual cross-checking into a "one-click" operation. It didn't just save time; it established a standardized blueprint for how our team handles multi-currency reconciliation, significantly reducing the risk of human error in our reporting.