BERT Example Functions

Here are some useful functions you can use in Excel with BERT. If you’re not familiar with BERT or landed here via search engine, have a look at the front page for quick install and usage instructions.

These functions aren’t guaranteed to be efficient, correct, comprehensive, or necessarily useful. You are encouraged to modify them for your needs. Unless otherwise noted, all code on this page is in the public domain.

If you have a suggestion, comment, or request for a particular function, please let us know.

Using These Functions

To use any of these functions, you just need to save the source file into your BERT startup folder. Right-click on one of the Save links below and select Save link as…

Save the file into `Documents\BERT\functions`. You’ll see a note in the BERT console when a new file is loaded.

You can also copy the code below and paste it into your own file. any file in the BERT startup folder will be automatically reloaded when you save changes.

Data Management

Sort

You can sort data with excel, but if that data changes it won’t re-sort. Also you lose the original order. Use this function to insert a sorted range that’s actively linked to the original range.

Shuffle

Shuffling data can be useful for bootstrap analysis or for randomizing fixed data sets (dice, cards).

Data Analysis

Dimensionality Reduction

Dimensionality reduction using Pricipcal Components Analysis (PCA)

Statistics

Matrix Correlation

Get a correlation matrix from multiple correlated data sets.

Test for Normality

Two tests for normality in a data set: the Shaprio-Wilk and Kolmogorov-Smirnov tests. In each case, the exported functions return the p-value from the test.

Box-Cox Transform

Given a set of data, find lambda for the Box-Cox transform.

Matrix Functions

Eigensystem

Functions for getting eigenvalues and eigenvectors from a matrix.

Finance

Trinomial Lattice Option Pricing

Generate call & put values using a trinomial lattice.

Binomial Lattice Option Pricing

Generate call & put values using a binomial lattice.

Binomial Lattice Tree

Generate the binomial lattice price matrix in a spreadsheet.

Graphics

Histogram

This function is an example of using R graphics in Excel with the BERT graphics device. It generates a basic histogram of the input data.

Drawing Maps

Using the `maps` package, it’s easy to create graphical maps from a list of countries (you could do the same thing for other geographic entities, like US states or French départments).

Before using this function, make sure to install the `maps` package; use Packages > Install Packages from the BERT console menu.

Utilities

Getting the Caller Reference

Not a function in and of itself, but an example of how to use the Excel API to get a reference to the calling cell (the cell containing the function).