# Black-Scholes option pricing in Google Sheets and GAS

In this module

• The Black-Scholes option pricing model written in JavaScript (Google Apps Script - GAS)
• A comparison of Excel OneDrive and Google Sheets as a web page <iframe> element
• Custom JavaScript functions for cumulative normal distribution: xlfBSCall, xlf NormSDist, and xlfBSPut

If you are not up to speed on the Black-Scholes equations, then take a look at the Excel and VBA version link

## 1. The Black-Scholes model in Google Sheets

Example: parameters - the stock price at time 0, six months before expiration date of the option is $42.00, option exercise price is$40.00, the rate of interest on a government bond with 6 months to expiration is 5%, and the annual volatility of the underlying stock is 20%.

Calculation of the call price can be completed as a 5 step process. Step 1. d1; 2. d2; 3. N(d1); 4. N(d2); and step 5, C. The value for d1 and d2 are shown in rows 12 and 13 of figure 1. The probabilities for $N(\cdot)$ are estimated with the Google Docs statistical function: NORMSDIST. The theoretical price of the call option is $4.08 (Figure 1 row 18), and the theoretical price of the put option is$1.09 (Figure 1 row 19).

Syntax - Google Docs function  NORMSDIST(x), where x is the probability value.

The Google sheets example with built-in functions (figure 1 - down to row 20) is almost identical to its implementation in Excel.

Loading... - the Custom functions section at row 22 may show a Loading... or #NAME? error (figure 1 Google Sheets). The GAS code in this module, is bound to the container (the Google Sheet). Therefore, scripts are only available to users who have permission to edit.

Access to bound scripts: "Only users who have permission to edit a container can run its bound script. Collaborators who have only view access cannot open the script editor, although if they make a copy of the parent file, they become the owner of the copy and will be able to see and run a copy of the script." (Source: https://developers.google.com/apps-script/guides/bound)

### Google Sheets vs Excel Web App

Browser source code cf. code file.

• Excel Web App has an iframe with src attribute with URL to display the workbook, and the workbook can be interactive . The page source code is compact
• Google uses a CSS class named waffle (code 1 lines 197 to 319) Code 1 shows the View Source HTML for the CSS table in figure 1. 368 lines in this example. A "waffle" pattern is similar to the appearance of cells on a spreadsheet

HTML Code 1: rendering of  Google Sheets displays the Sheet as a HTML table.


html { overflow: visible; }
#sheets-viewport { overflow: auto; }
#sheets-viewport.widget-viewport { overflow: hidden; }
.grid-container { overflow: visible; background: white;}
.grid-table-container { overflow: visible; }
#top-bar {
margin: 0;
overflow: hidden;
}

#top-bar {
border-bottom: 1px solid #ccc;
}

#doc-title .name { font-size: 15px; }

font-size: 13px;
margin: 6px 0 0;
}

display: inline;
list-style-type: none;
margin: 0;
}

background-color: #fff;
font-weight: bold;
border: 1px solid #999;
}

border-bottom: 0;
}

#footer {
background: #f0f0f0;
border-top: 1px #ccc solid;
border-bottom: 1px #ccc solid;
font-size: 13;
}

.dash {
}

.ritz .waffle a { color: inherit;
}

.ritz .waffle .s2{	background-color:#a4c2f4;
text-align:right;
color:#000000;
font-family:'Arial';
font-size:10pt;
vertical-align:bottom;
white-space:nowrap;
direction:ltr;
}

.ritz .waffle .s11{border-left: none;background-color:#ffffff;}
.ritz .waffle .s0{background-color:#a4c2f4;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}
.ritz .waffle .s9{border-right: none;background-color:#ffffff;text-align:left;font-weight:bold;color:#1155cc;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}
.ritz .waffle .s5{border-left: none;background-color:#fce5cd;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}
.ritz .waffle .s13{background-color:#ffffff;
text-align:left;
color:#ffffff;
font-family:'Arial';
font-size:10pt;
vertical-align:bottom;
white-space:nowrap;
direction:ltr;
}

.ritz .waffle .s10{border-left: none;border-right: none;background-color:#ffffff;}

.ritz .waffle .s12{background-color:#ffffff;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}

.ritz .waffle .s7{background-color:#c9daf8;
text-align:right;
color:#000000;
font-family:'Arial';
font-size:10pt;
vertical-align:bottom;
white-space:nowrap;
direction:ltr;
}

.ritz .waffle .s6{background-color:#c9daf8;text-align:left;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}
.ritz .waffle .s1{background-color:#ffffff;text-align:right;color:#000000;font-family:'Arial';font-size:10pt;vertical-align:bottom;white-space:nowrap;direction:ltr;padding:2px 3px 2px 3px;}

.ritz .waffle .s8{background-color:#fce5cd;
text-align:left;
color:#000000;
font-family:'Arial';
font-size:10pt;
vertical-align:bottom;
white-space:nowrap;
direction:ltr;
}

.ritz .waffle .s3{border-right: none;
background-color:#fce5cd;
text-align:left;
color:#000000;
font-family:'Arial';
font-size:10pt;
vertical-align:bottom;
white-space:nowrap;
direction:ltr;
}

.ritz .waffle .s4{border-left: none;
border-right: none;
background-color:#fce5cd;
text-align:left;
color:#000000;
font-family:'Arial';
font-size:10pt;
vertical-align:bottom;
white-space:nowrap;
direction:ltr;
}

var activeSheetId;

function switchToSheet(id) {
document.getElementById('sheet-button-' + activeSheetId)
.className = '';
document.getElementById('sheet-button-' + id).className = 'active';
}

document.getElementById(activeSheetId).style.display = 'none';
document.getElementById(id).style.display = '';
activeSheetId = id;

// posObjs() is defined in embeddedObjectJs (see EmbeddedObjectHtmlBuilder.java)
posObjs();
return false;
}

function init() {
var optPageSwitcher;

function resize() {
var optTopBar = document.getElementById('top-bar');
var optFooter = document.getElementById('footer');
var sheetsViewport = document.getElementById('sheets-viewport');

(optTopBar ? optTopBar.offsetHeight : 0) + 'px';
}
(optTopBar ? optTopBar.offsetHeight : 0) -
(optFooter ? optFooter.offsetHeight : 0);
if (optPageSwitcher) {
}
}
resize();
window.onresize = resize;
}

function posObj(sheet, id, row, col, x, y) {
var rtl = false;
var sheetElement = document.getElementById(sheet);

if (!sheetElement) {
sheetElement = document.getElementById(sheet + '-grid-container');
}

if (sheetElement) {
rtl = sheetElement.getAttribute('dir') == 'rtl';
}

var r = document.getElementById(sheet+'R'+row);
var c = document.getElementById(sheet+'C'+col);

if (r && c) {
var objElement = document.getElementById(id);
var s = objElement.style;
var t = y;

while (r && r != sheetElement) {
t += r.offsetTop;
r = r.offsetParent;
}
var offsetX = x;

while (c && c != sheetElement) {
offsetX += c.offsetLeft;
c = c.offsetParent;
}

if (rtl) {
offsetX -= objElement.offsetWidth;
}

s.left = offsetX + 'px';
s.top = t + 'px';
s.display = 'block';
s.border = '1px solid #000000';
}
};

function posObjs() {
};

posObjs();
activeSheetId = '0'; switchToSheet('0');



## 2. The Black-Scholes model in JavaScript

The coding language for Google sheets is named Google Apps Script (GAS), and is an implementation of the JavaScript language. In this example, separate JavaScript function procedures are developed for the call option (code 2) and put option (code 3) equations. Google Sheets functions are not available in the Google Apps environment. There is no equivalent to the Excel VBA Application.WorksheetFunction property. The NORMSDIST WS function, requires a custom function to be coded. It is named xlfNormSDist in this example (code 5).

### 2.1 xlfBSCall

JavaScript Code 2: Function xlfBSCall returns the call price for a European option on a non dividend paying stock

/* ==================================================
The Black and Scholes (1973) formula for an option on a non dividend stock
OptType default "c" for Call */

function xlfBSCall(Stock, Exercise, Rate, Sigma, Time) {
var d1;
var d2;
d1 = (Math.log(Stock / Exercise) + (Rate + (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
d2 = (Math.log(Stock / Exercise) + (Rate - (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
return Stock * xlfNormSDist(d1) - Exercise * Math.exp(-Rate * Time) * xlfNormSDist(d2);
}



1. Line 5 to line 11: Declares a JavaScript function procedure, in the form function name(arg1, agr2, ...). The function code is between the opening "{" bracket (line 5) and the closing "}" (line 11)
2. Line 6 and line 7: Declare variables d1 and d2.
3. Line 9: Call JavaScript Math functions. Math.log(x) returns the natural logarithm of x, and Math.sqrt(x) returns the square root of x. JavaScript has four arithmetic operators ( + - * /). JavaScript does not recognize the spreadsheet ^ symbol as exponentiation, so the function Math.pow(x,y) returns the value of x to the power y, that is, sigma2.
4. Line 10: Is a custom function, xlfNormSDist (described in code 5) to return the cumulative normal density

### 2.2 xlfBSPut

JavaScript Code 3: Function xlfBSPut returns the put price for a European option on a non dividend paying stock
/* ================================================== */
/* Black Scholes call option */

function xlfBSPut(Stock, Exercise, Rate, Sigma, Time) {
var d1;
var d2;
d1 = (Math.log(Stock / Exercise) + (Rate + (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
d2 = (Math.log(Stock / Exercise) + (Rate - (Math.pow(Sigma, 2)) / 2) * Time) / (Sigma * Math.sqrt(Time));
return Exercise * Math.exp(-Rate * Time) * xlfNormSDist(-d2) - Stock * xlfNormSDist(-d1);
}



1. Line 15 to line 24: Declares a JavaScript function procedure, in the form function name(arg1, agr2, ...). The code 3 logic is identical to code 2 except for the Put value in line 23

### 2.3 xlfBSOption

JavaScript Code 4: Macro xlfBSOption with OptType argument.
/* ==================================================
The Black and Scholes (1973) formula for an option on a non dividend stock
OptType default "c" for Call */

function xlfBSOption(Stock, Exercise, Rate, Sigma, Time, OptType) {

var d1, d2;
d1 = (Math.log(Stock / Exercise) + (Rate + Sigma * Sigma / 2.0) * Time) / (Sigma * Math.sqrt(Time));
d2 = d1 - Sigma * Math.sqrt(Time);

if (OptType == "c")
return Stock * xlfNormSDist(d1) - Exercise * Math.exp(-Rate * Time) * xlfNormSDist(d2);
else
return Exercise * Math.exp(-Rate * Time) * xlfNormSDist(-d2) - Stock * xlfNormSDist(-d1);
}



### 2.4 xlfNormSDist

The Google Sheets NORMDIST cannot be called from the GAS module, so the approximation to the cumulative normal distribution function $N(x)$ from Abramowitz (1972) equations 26.2.1 p931, 26.2.17 p932; and Bellalah (2008, Appendix 6, p126) is provided in code 5.

The $N(x)$ approximation is:

$$N(x) = \begin{cases} 1-n(x) \times k \times (a1 + k \times (a2 + k \times (a3 + k \times (a4 + k \times a5)))), & \text{when x \ge 0} \\ 1-n(-x) & \text{when x \lt 0} \end{cases}$$

where

$$n(x) = \frac{1} { \sqrt 2 \pi} e^{-x^2 / 2}, \quad \text{and} \quad k = \frac {1}{1 + ax}$$

and a1 to a5 are assigned values in code 5.

JavaScript Code 5: Function xlfNormSDist is equivalent to the WS NORMSDIST function
This approximation is based on Abramowitz & Stegun (1964) eq 26.2.17 p126
/* ==================================================
The cumulative Normal distribution function: */

function xlfNormSDist(x) {

// constants
var a = 0.2316419;
var a1 =  0.31938153;
var a2 = -0.356563782;
var a3 =  1.781477937;
var a4 = -1.821255978;
var a5 =  1.330274429;

if(x<0.0)
return 1-xlfNormSDist(-x);
else
var k = 1.0 / (1.0 + a * x);
return 1.0 - Math.exp(-x * x / 2.0)/ Math.sqrt(2 * Math.PI) * k
* (a1 + k * (a2 + k * (a3 + k * (a4 + k * a5)))) ;
}



1. Open the Google Sheets workbook
2. On the Menu Bar, select Tools > Script editor...
3. Click File > New > Script File
4. Then Add a name: xlfbscode in the dialog box, click OK
5. This will add a tab named xlfbscode
6. Delete the contents of the new javascript function MyFunction place holder
7. Copy and paste JavaScript file available here with a .txt extension [3 KB]
8. Click the Save icon

## References

Abramowitz M, and I Stegun, (1972), Handbook of Mathematical Functions with Formulas, Graphs, and Mathematical Tables, National Bureau of Standards - Applied Mathematics Series #55.

Bellalah M, (2008), Exotic Derivatives and Risk: Theory, Extensions and Applications, World Scientific.

Mcpherson B, (2016), Going GAS, from VBA to Google Apps Script, O'Reilly.

## Resources

• Development platform: Google Sheets and its Script Editor, plus JavaScript
• Revised: Tuesday 5th of December 2017 - 01:28 PM, Pacific Time (PT)