BACK TO TD-SECURITIES

Fixing Recon

challenge

  1. Manual Data Harvesting: The process required manually downloading 7 different attachments from various emails and importing them into the main sheet. This repetitive task consumed ~20 minutes daily and was prone to "copy-paste" errors.

  2. Fragmented Communication: Specific files needed to be forwarded to other teams. We often forgot to send them immediately, causing delays for downstream departments.

  3. Complex "Fallback" Scenarios: We deal with fallback fixings where one "Calculation Date" might be associated with multiple "Record Dates." In standard data processing, these duplicate dates would cause data collisions or overwrite each other.

  4. Compliance & Archiving Gaps: A critical step is "hardcoding" (finalizing) the data and archiving the file. Human operators often forgot this step, leaving us with un-saved formulas. Mistakes were sometimes only discovered 2 weeks later during audits.

challenge

solution

  1. Outlook-Excel Bridge: I pioneered a method to connect Excel directly to Outlook (a first for our team). The macro now scans the inbox, identifies the 7 specific emails, and auto-downloads the attachments instantly, eliminating the 20-minute manual setup.


  2. Auto-Distribution: I integrated an email trigger into the download loop. As soon as the relevant files are processed, the macro automatically emails them to the external teams, removing the need for manual forwarding.


  3. Dynamic Renaming Logic: To solve the fallback date conflict, I wrote a logic script that checks for duplicate Calculation Dates. If found, it automatically assigns unique aliases to these fixings to distinguish them, preserving data integrity for all record dates.


  4. Automated "Cleanup" & Approval: I built a self-executing closure protocol. Once the recon is done, the macro automatically hardcodes the values (removing volatile formulas), saves a timestamped archive version, and sends a final approval request email to teammates—ensuring 100% compliance every time.


Key Impact

  1. Spearheaded the department’s transition from IE to Microsoft Edge by architecting a new Selenium-based standard for web automation. My framework served as the foundational blueprint for the entire team, guiding other groups through complex legacy code migrations and establishing a modernized protocol for all future banking procedures.