Automated Rate Calculation & Management System

Client: Multi-tier precious metals dealer with wholesale and retail operations, using NetSuite for pricing management, rate configuration, and external API integration.

The client operated a complex multi-tier pricing model where retail market rates were calculated from wholesale rates, which were themselves derived from spot rates using proprietary formulas involving multiple multipliers and fixed premiums. Managing these interdependent calculations manually was error-prone and time-consuming.

Staff logged into a third-party precious metals pricing service multiple times per day to check current spot rates, then manually entered the values into a spreadsheet alongside multiplier constants. They used Excel formulas to calculate wholesale rates, then calculated market rates by applying an additional markup.

The calculation complexity created frequent errors: staff occasionally transposed digits when copying spot rates, used outdated multiplier values when formulas weren't refreshed, or forgot to round to the nearest $0.02 increment as required by industry standards. The manual process also lacked transparency—there was no visual representation of how rates flowed through the calculation tiers.

We implemented an integrated system combining automated rate retrieval, visual calculation displays, and formula-driven rate management with audit trails.

The Scheduled Script runs on a configurable interval (typically every 30 minutes) to automatically retrieve current spot rates from the external pricing API. It applies the multi-tier calculation formulas automatically, with each rate retrieval creating a new configuration record with a timestamp for complete history.

The Suitelet interface provides a visual calculation display showing the exact formula flow with live values in color-coded boxes. Users can see spot rate → wholesale rate → market rate with each multiplier and operation displayed visually, making the pricing structure immediately clear.

When users modify multipliers or premiums, client-side JavaScript recalculates all dependent rates in real-time before saving, showing exactly how changes will affect wholesale and market pricing. The system creates new configuration records on save rather than editing existing ones, preserving a complete audit trail.

Technical Implementation

Architecture: Three-component system (Scheduled Script, Suitelet, Client Script) with shared calculation logic. Scheduled Script handles automated retrieval, Suitelet provides visual interface, Client Script performs real-time calculations in browser.

Key Components: Automated rate retrieval from external API with OAuth/token authentication, JavaScript implementation of Excel MROUND function for industry-standard penny rounding, dynamic HTML generation creating color-coded calculation boxes with live formula display, server-side serverWidget form generation with field groups and custom layouts, and an audit trail system with new record creation on every save.

Notable Decisions: MROUND implementation uses standard rounding to match Excel's nearest-even-multiple behavior, the Suitelet creates new records rather than editing existing ones to maintain a complete pricing audit trail, field-level permissions separate calculation fields (editable) from raw API response data (read-only), the client script performs calculations in browser before submission for instant feedback, and the script automatically selects the newest rate configuration by internal ID descending sort.

• Eliminated manual spot rate transcription and login process
• Formula-driven calculations eliminated math errors and rounding inconsistencies
• 30-minute automated refresh (configurable to 15 minutes) during volatile markets
• Visual interface improved partner communications and accelerated training
• Complete audit trail with every rate change preserved as new record
• On-demand "Retrieve Rate" button provides manual override during volatility

Key Takeaways

• Color-coded formula display transformed opaque math into intuitive visual flow
• Automation with manual override: scheduled for routine, on-demand for volatility
• Creating new records vs editing existing ones provided audit compliance without complexity