VbaFin.com

Visual Basic for Financial Professionals
   Home      Build Functions
This is an example of COM Add-in containing a collection of functions that load to and retrieve data from the Excel application memory allowing for easy data exchange between spreadsheets. It also features and executable file which registers the dll and installs the COM Add-in.

The package contains the following:
  • BuildFunctions.dll - the source code
  • BuildFunctions.dll - the compiled code
  • BuildFunctions.exe - executable which registers the dll and installs the COM Add-in
  • BuildFunctions.exe - the source code of the executable
  • BuildFunctions.xls - spreadsheets with BuildFunctions examples
To install the COM Add-in place the BuildFunctions.exe and BuildFunctions.dll in the same folder (any folder) and click on the BuildFunctions.exe.


To download the package click here.

Below is a description of the Build functions:

BuildDataGrid
 Function
Loads a matrix of data into memory
 Inputs
Grid Name, Rows Header, Columns Header, Data Matrix
 Comment
A minimum of one row and one column required
 
GetGridPoint
 Function
Retrieves a data point from a Data Matrix loaded with BuildDataGrid
 Inputs
Grid Name, Row Point, Column Point
 Comment
Similar to an Excel Vlookup where the actual column name is used instead of an offsetting number
 
BuildCurve
 Function
Loads a curve represented by an array of dates and an array of numbers into memory
 Inputs
Curve Name, Terms, Rates
 Comment
A minimum of two data points required
 
GetRate
 Function
Retrieves a Rate from a Curve data loaded with BuildCurve
 Inputs
Curve Name, Terms, Rates
 Comment
Log-linear interpolation between points and a flat rate outside the dates range is used
 
BuildVolTerm
 Function
Loads a volatility term (strikes and volatilities) into memory
 Inputs
Volatility Name, Strikes, Volatilities
 Comment
A minimum of three data points required
 
BuildVolGrid
 Function
Loads a volatility grid (strikes, terms and volatilities) into memory
 Inputs
Volatility Name, Strikes, Terms, Volatilities
 Comment
Strikes and Dates must be in ascending order
 
BuildVolData
 Function
Loads a volatility data (strikes, terms, forwards and volatilities) into memory
 Inputs
Volatility Name, Strikes, Terms, Forward Rate, Volatilities
 Comment
Strikes and Dates must be in ascending order, Strikes input as a percentage of Forward Rates
 
GetVol
 Function
Retrieves volatility from Data loaded with BuildVolTerm, BuildVolGrid or BuildVolData
 Inputs
Volatility Name, Strike, Term (no term needed when data is loaded with BuildVolTerm)
 Comment
Volga-vanna interpolation between Strikes, log-linear between Terms, flat outside the range