# 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
- How to add a JavaScript module to Google Sheets

## 0. Preliminary reading

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.
xlf-black-scholes-google

**HTML:**lines 4 to 143 CSS style, lines 144 to 189 JavaScript, lines 197 to 319 table class="waffle", lines 321 to 368 JavaScript [x scroll yellow content (visible on scroll down), y scroll mauve content]

## 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); }

**About code 2**

**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)**Line 6 and line 7:**Declare variables d1 and d2.**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, sigma^{2}.**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); }

**About code 3**

**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 functionThis 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)))) ; }

## Add a JavaScript module to Google sheets

- Open the Google Sheets workbook
- On the Menu Bar, select
- Click
- Then
**OK**
xlfbscode in the dialog box, click - This will add a tab named
**xlfbscode** - Delete the contents of the new javascript function MyFunction place holder
- Copy and paste
__JavaScript file available here with a .txt extension [3 KB]__ - Click the 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

__Embed Google Sheets on a website__. Accessed: 1 December 2017- Google sheets
__about__. Accessed: 1 December 2017 - Google Apps Script
__apps-script__. Accessed: 1 December 2017 **Download**the GAS (javascript) code for this module: xlfbscode.gs [3 KB]

**Development platform:**Google Sheets and its Script Editor, plus JavaScript**Revised:**Tuesday 16th of July 2019 - 05:43 PM, Pacific Time (PT)