Integration in Google Sheets

less than 1 minute read

In this post, I will show you how to integrate the Portfolio Optimizer Web API in Google Sheets.

As a working example, I will display the mean-variance minimum variance frontier1 of 4 fictitious assets.

Note: A fully functional spreadsheet corresponding to this post is available here.

Integration in the Google Sheets script editor

This step relies on the Google Sheets JavaScript-like scripting language Google Apps Script.

Integration of the Portfolio Optimizer API

  1. Create a new Google Sheet and select select Tools > Script Editor to launch the script editor: Google Sheets Script Editor

  2. Replace the default script content with the following code:

  3. Which gives: Google Sheets Portfolio Optimizer Script Editor

Integration of the Minimum Variance Frontier API endpoint

  1. Still in the same script, copy and paste the following code:

  2. Which gives: Google Sheets Minimum Variance Script Editor

Integration in the Google Sheets sheet

  1. Back to the sheet, define the 4 assets imaginary returns and covariance matrix: Google Sheets Assets Returns And Covariance

  2. Call the computeMinimumVarianceFrontier method defined above, directly from the sheet: Google Sheets Minimum Variance Portfolios Computation

  3. Which outputs a list of pairs (portfolio volatility, portfolio return): Google Sheets Minimum Variance Portfolios Computation

  4. Which can be displayed in the (V,E) plane to make the mean-variance minimum variance frontier appear: Google Sheets Minimum Variance Portfolios Computation

–

  1. The mean-variance minimum variance frontier is the set of portfolios in the (V,E) plane with the lowest volatility for any given return. ↩