Value at Risk and RunWorkbook

This article demonstrates how RunWorkbook enables you to create readable and maintainable spreadsheets. It works through some of the examples used in the RunWorkbook: Black-Scholes and Values at risk screencast. (The first part of the screencast is covered in Black-Scholes and RunWorkbook.)

In a nutshell, RunWorkbook enables you to access one workbook from another. In this case, we create functionality in Portfolio.rsl, then reference it in Portfolio VAR analysis.rsl.

Value at Risk (Portfolio.rsl)

The Portfolio.rsl workbook contains a share portfolio that we're managing. Here's how it looks:

A portfolio of shares managed through Resolver One

We have a selection of long and short positions and we track:

  • The current value of these positions.
  • The Beta, which is a measure of correlation between this stock and the market. A Beta of 1.3 means that for a 100% move in the market, this stock moves by 130%. Betas can be positive or negative.
  • Projected prices for our stocks—from there we can project the total value of the portfolio.

In the Dashboard worksheet, we can experiment with different projections for the market index and calculate the projected value for all our positions:

The dashboard of our portfolio spreadsheet

If we think that the market is going to drop to 1000 points, then our positions on the Portfolio sheet are recalculated. Our projected dollar value becomes 204 million dollars.

VAR Analysis

Now, what if we have a range of projections to consider?

The Portfolio VAR analysis.rsl workbook sets out projections from 1,000 to 10,000 points, marked from red to green:

Analysing various projections on our portfolio with RunWorkbook

It works by using the RunWorkbook function to call Portfolio.rsl, passing in the required arguments, and obtaining the new projected value and percentage changes.

Cell B4 contains...

=RunWorkbook("Portfolio.rsl", projectedIndex:=A4)

We've passed in the value 1000 (the value in cell A4) via the named argument projectedIndex. The RunWorkbook function runs Portfolio.rsl, and sets the projectedIndex (a named cell) to 1000.

We can reference cell B4 in order to get at the values returned by Portfolio.rsl. For example, this returns the Current USD Value:

=$B4["Dashboard"]["Value", "Current USD value"]

The formula fetches the value in the cell referenced by Value and Current USD Value in the Portfolio.rsl Dashboard worksheet.

Each of the Workbook instances of Portfolio.rsl used in column B of Portfolio VAR analysis.rsl are unique; they don't share any state. You don't need to prepare the workbook that you want to call, there aren't any dlls to export... you just reference the .rsl file, set values and extract results.

Check it for yourself

D4 displays "204 million", as extracted from the Dashboard. Verify this result:

  1. Open Portfolio.rsl.

  2. In the Dashboard worksheet, enter the 'projected index' value of 1000

    The new Projected USD Value should match that shown in our VAR Analysis worksheet.

Further improvements

Because the formulae in column B is repeated, it might be more elegant and robust to implement it using a column level formula. But that's for another article...

Summing up

So now you know how to use RunWorkbook in two ways. First, to call one spreadsheet from another, like a function. Secondly, to perform the same analysis with a range of different assumptions. For more details on RunWorkbook see the RunWorkbook topic.

Comments