logo domkeconsulting

Beratung und Entwicklung - Mehr Produktivität für Microsoft Office

logo domkeconsulting

Consultant and Developer - Get more productivity from Microsoft Office

Automated risk calculation for structured financial products

In a bank huge and complex Excel spreadsheets are used by traders to calculate the risk-per-book for structured financial products. The Excel spreadsheets do not calculate itself the risk, but are used as front-end for calculations done by server-based Monte-Carlo simulations. Due to audit requirements, the risk calculations must run additionally in a "controlled and automated environment" without user interaction. The calculation framework and the Excel spreadsheets are constantly enhanced by the quantitative analysts from the trading department. This leads to the necessity of a constant update of the "controlled and automated environment".


  • Develop a method to transfer the Excel spreadsheets and software libraries (need to change references and links)
  • Develop control procedures for the traders for the start or repetion of calculation runs
  • Develop a program for the automated risk calculation on dedicated workstations

Components of the application

  • VB.NET Windows Forms Application (Deployment Utility) to transfer components
  • ASP.NET Web Frontend for traders (start / repeat risk calculation jobs)
  • ASP.NET Web Frontend for administrators for job surveillance
  • VB.NET Console Application to automate Excel on dedicated workstations


Word: Add-in for paper tray configuration and printing of copies

Standard Issue: The correct paper tray settings in Word documents for different printers is a standard issue. Setting defaults in the document template (e.g. first page = stationery tray, other pages = white paper tray) is not possible, if different printers are used in the company. Letting users configure the settings manually is time-consuming and error-prone.

Request: Users should have a simple interface to select the trays, without reffering to the actual printer. Additionally, they must be able to print copies with stamp-like information ("Copy for file", "Reminder on ..." etc.).

Solution: A global Add-in is used and adds a second print button to the standard toolbar. The underlying VBA program checks the current printer, looks up information in an INI file on the network about the tray settings and allows quick selection of the trays. Additionally, the user can select standard copy stamps or create individual stamp information, which is saved in document variables to the document for reuse. Output on different printers with a large range of tray settings is no longer an issue.

Dialog "Print Menue"

Firmenbezogene Druckeinstellungen

Dialog "Copy options"

Dependencies: This solution depends on whether the VBA program can identify the current printer and compare it to a "printer type", e.g. "HP 4050" PCL, "HP 8000 PS". Only then a lookup of tray settings is possible.

Alternative solution: In another project it was not possible to identify a printer, because the Add-in was used in different locations with no convention about printer names ("HP 4050 PS-Second Flor" vs. "Printer 3 Room 871"). The administrator-friendly solution is as follows: the VBA program asks the user only once for each used printer which tray settings to select, and saves the data in the user profile. The settings are afterwards used for all following print jobs, or can be edited and changed through a custom dialog.