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)
View the worksheet in Google Sheets link
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
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, sigma2.
- 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 Tools > Script editor...
- Click File > New > Script File
- Then Add a name: xlfbscode in the dialog box, click OK
- 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 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
- 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: Friday 24th of February 2023 - 11:12 PM, Pacific Time (PT)