Kiran Kumar Yeruva

The pricing-update process was highly manual and resource-intensive. A 14-member team spent ~70 minutes per policy with ~40 locations, and very large commercial policies (1,000+ locations) could take multiple days. Inconsistent address formats, location numbering mismatches, and formatting differences across systems created frequent errors, rework, and delays.

A VBA-based automation that:

• Validated policy data and updated prior-term pricing in the current term
• Extracted and aggregated location and building attributes
• Indexed location numbers consistently across both systems
• Standardised common address variations (e.g., ST/Street, RD/Road)
• Identified unmatched addresses and produced separate exception sheets

VBA · Excel · Pre Rate PKG (Mainframe) · ATD mainframe database/application

Address standardisation and location indexing are critical for scaling automation in complex commercial policies. Exception reporting must be designed to highlight mismatches clearly, enabling SMEs to resolve issues quickly.

For new policies, client-provided location and building information arrived in Excel files and had to be entered manually into a mainframe application. Each building record took ~5 minutes to create, and policies ranged from a handful of locations to 60–70, often with multiple buildings per location — creating operational bottlenecks, high effort, error-prone data entry and scalability issues.

A VBA-based automation that:

• Read structured client data directly from Excel
• Validated input fields before upload
• Corrected ZIP codes and other data inconsistencies
• Automated mainframe navigation to create location and building records
• Supported both small and large commercial policies

VBA · Excel · Mainframe applications

Data validation and correction are essential before automation updates records in mainframe systems. Combining validation with automated navigation achieves scalability and accuracy even for large commercial policies.

The reconciliation process required comparing two source reports and classifying transactions using identifiers, dates, descriptions, amounts, references, and cheque information. Analysts had to manually separate cleared transactions, aged items, moved items, uncleared items, and ambiguous cases needing investigation — creating traceability gaps, high manual effort, and slow prioritisation of exceptions.

An Excel VBA reconciliation solution that:

• Consolidated multiple source reports into a single framework
• Performed rule-based transaction comparison across identifiers, dates, and amounts
• Organised outputs into actionable categories: cleared, matched, moved, aged, uncleared
• Reused ATD mainframe logic with minor changes for exception handling

Excel VBA · Multi-source reconciliation framework · Ageing logic · ATD mainframe database logic (reused)

Rule-based reconciliation combined with ageing logic improves both accuracy and prioritisation. Reusing proven ATD logic made exception handling more consistent across domains — demonstrating the value of component reuse in solution architecture.

The NAV reconciliation process required comparing Fund Administrator (FA) and Investment Manager (IM) data across 56 funds at both a total market value level and a security level. Analysts had to manually match records, identify discrepancies in quantity, price, and market value, and separate genuine breaches from duplicates, additional ISINs, and cash items — all before flagging accounts that required investigation.

File Consolidation — Consolidated FA and IM input files (XLSX, CSV, TXT) into standardised dumps, correcting data inconsistencies before reconciliation.

Level 1 (L1) Reconciliation — Compared FA vs IM total Market Value across all 56 funds; funds breaching ≥50 basis points were flagged to a Breached Accounts sheet, with daily data archived to track breaches ≥25 basis points over time.

Level 2 (L2) Reconciliation — Triggered on demand, created individual tabs per breached fund at the security level, comparing Quantity, Price, and Market Value differences, and flagging duplicate/additional ISINs and cash items separately.

Excel VBA · Multi-format file consolidation · Exception classification and ageing logic · ATD mainframe database logic (reused) · Historical breach tracking

A tiered reconciliation approach (L1 vs L2) improves efficiency by filtering high-risk funds early, while detailed security-level checks ensure accuracy. Reusing ATD logic across domains demonstrated the value of component reuse in solution architecture.

Investment operations teams regularly identify exceptions during NAV reconciliation, cash reconciliation, position reconciliation and other control activities. The NAV automation (Case 05) could identify breached funds — but identifying an exception was only the beginning. Spreadsheet- and email-based tracking made it difficult to maintain consistent ownership, status visibility, governance and reporting across the exception lifecycle.

A centralised exception management solution using Microsoft Power Platform, bringing together:

• Microsoft Dataverse for structured exception data
• A Model-Driven App for the operational user experience
• Power Automate for notifications and workflow automation
• Role-based security for controlled access
• Views and dashboards for operational and management visibility

Exception Identified → Exception Recorded → Ownership Assigned → Investigation Initiated → Action or Escalation → Resolution Recorded → Review and Closure

Designed with role-based access across four user groups — Operations Analysts, Reviewers/Team Leads, Managers, and Administrators — each scoped to their responsibilities in the exception lifecycle.

This initiative represents an important step in my transformation journey from process automation towards Solution Architecture. Rather than focusing only on automating an individual task, the solution considers the complete business process — data, users, ownership, security, workflow, governance, reporting and future scalability.