README Google Sheets Example Not Working as Expected

[grantackerman1]

Hi Roman -

Thanks for this library! I'm just getting started with it on Google Sheets but can't seem to get the example in the README to work. In Google Sheets, I added your library with the Script ID, using static version 15 (v0.0.9). Then, I pasted this function into the Script Editor:

function computeERCPortfolioWeights(covarianceMatrix) {
// Note: The input range coming from the spreadsheet is directly usable.

// Compute the ERC portfolio weights
var ercWeights = PortfolioAllocation.equalRiskContributionWeights(covarianceMatrix);

// Return them to the spreadsheet
return (ercWeights);
}

In cells A1:B2 of my Google Sheet, I entered a sample covariance matrix: [[1,0.3],[0.3,1]]. In cell A4, I typed the following formula: =computeERCPortfolioWeights(A1:B2) but I get a #ERROR! with Error:unsupported input type (line 238).

I would've expected to get weights in cells A4:B4 or A4:A5.

Any help would be greatly appreciated.

[lequant40]

Hi !

Thanks for using the lib and for the feedback !

I reproduced the issue, I'll have a look asap.

[lequant40]

So, it would seem the issue comes from the new Apps Script runtime based on Chrome V8.
(If this new runtime is de-activated in the Spreadsheet Script Editor, everything works as intended)

Somehow, arrays provided are not arrays anymore...

Anyway, I made a new release of my lib (for Google Sheets only), which solves this particular issue -> v0.0.10-alpha:

You can simply switch to it, and everything should be fine.

Just tell me how it goes for this specific issue when you have time.

[grantackerman1]

Thanks Roman. That did the trick! Now I'm running into a different issue and would greatly appreciate any help:

I'm using the function below I created in Google Sheets, but am getting a #ERROR! that says "Error: unsupported problem detected (line 5838)". My goal is to calculate mean variance weights given an expected return stream, a covariance matrix, a target volatility, min weights, and max weights:

function computeWts(rets, cv, targetVol, minWts, maxWts) {
return (PortfolioAllocation.meanVarianceOptimizationWeights(rets, cv, {constraints: {optimizationMethod: 'maximumTargetVolatility', maxVolatility: targetVol, minWeights: minWts, maxWeights: maxWts}}));
}

The arguments I'm passing are below (and are all in cells in Google Sheets):

rets:
4.7%
5.0%
6.1%
4.0%
5.4%
2.0%
2.2%
2.4%
2.4%
3.6%

cv:
2.3% | 2.5% | 3.3% | 1.7% | 2.3% | -0.4% | -0.3% | -0.3% | 0.1% | 1.7%
2.5% | 2.9% | 3.8% | 1.9% | 2.5% | -0.4% | -0.3% | -0.3% | 0.2% | 1.9%
3.3% | 3.8% | 5.3% | 2.3% | 3.1% | -0.5% | -0.3% | -0.4% | 0.3% | 2.9%
1.7% | 1.9% | 2.3% | 1.4% | 1.8% | -0.3% | -0.3% | -0.3% | 0.0% | 1.1%
2.3% | 2.5% | 3.1% | 1.8% | 4.0% | -0.1% | 0.0% | 0.2% | 0.7% | 1.8%
-0.4% | -0.4% | -0.5% | -0.3% | -0.1% | 0.2% | 0.2% | 0.2% | 0.2% | -0.1%
-0.3% | -0.3% | -0.3% | -0.3% | 0.0% | 0.2% | 0.4% | 0.2% | 0.3% | 0.0%
-0.3% | -0.3% | -0.4% | -0.3% | 0.2% | 0.2% | 0.2% | 0.3% | 0.2% | 0.0%
0.1% | 0.2% | 0.3% | 0.0% | 0.7% | 0.2% | 0.3% | 0.2% | 0.5% | 0.4%
1.7% | 1.9% | 2.9% | 1.1% | 1.8% | -0.1% | 0.0% | 0.0% | 0.4% | 2.0%

targetVol:
5.5%

minWts:
3%
3%
3%
0%
0%
0%
0%
3%
0%
0%

maxWts:
35%
35%
35%
35%
35%
35%
35%
35%
35%
35%

[lequant40]

Re,

Good to hear the fix issue is solved.

I'll keep this issue to track the deployment of the code fix for Apps Script arrays management for the next release in Git.

I've created another issue to track the problem with the MVO optimization -> #8

But in a nutshell, this behaviour is normal: currently, having identical returns in input is not supported.

Cheers,

Roman

[lequant40]

Solved in v0.0.10