Custom Excel VBA

The use of custom Excel VBA programming and @RISK APIs allows the automated analysis of historical data and construction of sophisticated risk models. Here, we present an application in the energy sector as an example.

Palisade Corporation developed an add-in that automates the construction of a risk analysis model to assess the Value-At-Risk  (VaR) of a portfolio of gas future contracts.  This application uses a Principal Component Analysis (PCA) to describe the variability of historical correlated forward price curves; this analysis allows the creation of a @RISK Monte Carlo simulation model to generate forward price curves and compare them against the current positions of the portfolio.

PCA is a statistical technique which can identify the main independent components (sources of risk or information) in data (In this example, historical prices of natural gas forward contracts.). There will generally be as many components as there are forward contracts in the analysis. Therefore, if we are analyzing monthly contracts up to 36 months forward, the analysis would reveal 36 components. In data which is highly correlated (such as natural gas forward prices), typically only 2 or 3 components are significant, accounting for nearly all the variation or “movement” in the data set. For the forward price curves of natural gas, the first principal component generally corresponds to a parallel shift in prices, while subsequent principal components correspond to relative price changes (i.e. a change in calendar spreads).

Using a VBA macro, historical data is analyzed using PCA. The macro constructs an Excel model and @RISK runs a simulation to generate forward price curves so the risk profile of the portfolio can be assessed. The figure below presents a result that shows the predicted performance a of sample portfolio where the VaR (@ 5%) is shown:



Valuing Natural Gas Storage Using Seasonal Principal Component Analysis,  Carlos Blanco, Ph.D., Financial Engineering Associates, 2002.

If you are interested in the implementation of this type of model, @RISK can be of great help. You can concentrate on the quality of the model and input data and let @RISK deal with the simulation and generation of reports.

» More about Palisade Custom Development

Dr. Javier Ordóñez
Director of Custom Development

@RISK is a great tool to create cost risk analysis models. An important component in this type of model is the consideration of risk events. A risk event is modeled using its probability of occurrence and its conditional impact. In other words, we need to model first that the risk occurs, and given its occurrence, we have to include the generated impact to the cost of our project.

The occurrence of a risk event can be easily modeled using a Binomial distribution, where n=1 and p= the probability of occurrence. The consequence can be modeled using a continuous distribution like the Uniform distribution. For example, if we have the risk of “Property damage” with a 50% chance of occurrence, and if that happens my project will suffer a 5-10% increase in the total cost, this logic can be constructed using @RISK. The formula is:

=1+(RiskBinomial(1,0.5)*RiskUniform(0.05,0.1))

During the Monte Carlo simulation, the result of the formula will be 1 or a number from 1.05 to 1.1. When the risk does not occur, the total cost of the project will be multiplied by 1, and when the risk occurs there will be an increase of 5% to 10%.

A more efficient alternative is the use of the RiskCompound function available in @RISK versions 5.x; the formula will be:

=RiskCompound(RiskBinomial(1,0.5),RiskUniform(.05,.1),RiskShift(1))


Using custom VBA Excel programming, you can build an interface that will facilitate the selection and definition of risk events. Internally, the formulas shown above can be constructed and written in a risk register form to assess the cost exposure of the project. Below is an example of an interface that uses a probability-impact matrix for the definition of a risk event. 



Palisade Corporation can help you build custom add-ins that will interact with @RISK to create a powerful analysis tool.

We will have some examples of VBA automation in our website soon – we will let you know when they are ready!  Stay tuned…

» More about Palisade Custom Development

Dr. Javier Ordóñez
Director of Custom Development

Palisade Corporation now offers custom development services. Our consulting team can help you to automate your risk and decision analysis models so they can be easily used by everyone in your company, or even outside of it. 

We offer different options that include Excel add-ins, Windows, and Web based applications. Our consultants can help you to design, program and deploy these applications. A typical application might connect an Excel spreadsheet to your company’s database, extract data, then adjust it to probability distributions so they can be used in dynamic risk or optimization models. The structure of reports can be also customized and published as PDFs, or to the Web.

Palisade Custom Development can incorporate Monte Carlo simulation, probability distributions, distribution fitting, graphs, reports, and many other features of @RISK into any Windows-based application. In addition, we can integrate genetic algorithm optimization from RISKOptimizer or Evolver. This allows you to apply powerful, proven analytics to applications outside Excel. Applications can be run in a desktop, network, or Web environment.

You may wish to customize your @RISK or DecisionTools Suite spreadsheet models, restricting access to model components for some users or automating reports and other aspects of your analysis. Using the DecisionTools built-in Excel Developer Kit (XDK) and custom Excel VBA programming language, Palisade can help you build powerful, easy-to-use risk models for one user or for an entire work group.

We are currently working on a new website where you will find more information and project samples.  Upcoming posts will discuss examples of custom Excel VBA programming.

» More about Palisade Custom Development

Dr. Javier Ordóñez
Director of Custom Development