Stuck using the linear optimisation function to optimise portfolio weights

Question

Context

I am currently seeking to build an optimisation function to build portfolio weights. Its akin to the excel solver or the google sheets solver function (albeit faulty). Though how it works differs to the excel VBA. Its the first time I am playing with it. Below is the script:

function PortfolioOptimisation() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    var assets = ['AssetOne','AssetTwo','AssetThree','AssetFour','AssetFive',
                  'AssetSix','AssetSeven','AssetEight']; //What I using to optimise variables
    var weights = ss.getRangeByName(assets); 
    // The variables to optimise
    var factors = ['OptimisationExpectedReturn','OptimisationExpectedVol','OptimisationNegativeReturn',
                   'OptimisationPositiveReturns','OptimisationPositiveRisk','OptimisationNegativeRisk',
                   'OptimisationSortinoRatio','OptimisationSharpeRatio']; //Store it in a variable as I do not want to keep typing up the named ranges. 
    var sumWeights = ss.getRangeByName('OptimisationWeightsSum')
    var optimalPortfolios = ss.getRangeByName(factors);


    // Call the optimiser engine
    var engine = LinearOptimizationService.createEngine();
    engine.addVariable(optimalPortfolios[0]);// Add first variable,
    // Add constraints: weights =1, Sum of weights =1, weights = greater than 0, less than or equal to 1.
    var constraint = engine.addConstraints([0.0],[1.0],[weights,sumWeights],[weights]);
    

This is what I am trying to apply it to: Spreadsheet

It contains the formulas in each cell that will be calculated using the optimisation function.

Problem

How do I execute the optimisation function to find the optimal values based on the 'portfolio section/column' in the spreadsheet? How could I improve my code above?

In the spreadsheet, in the second tab/sheet, on the first Portfolio name, for example, I want to optimise the weights of the assets by maximising the Sortino ratio and minimising it. So using the optimisation engine, what would be the best weights of the assets that could help me achieve this? I want to do the same thing for the other listed portfolios in the portfolio column.

Answer 1

Update

Simpler solution:

# Portfolio returns calculated
def portfolio_returns(weights, returns):
    """weights -> returns"""
    
    # take the weights, transpose it and take the matrix multiplication
    return weights.T @ returns

# Volatility
def portfolio_volatility(weights, covmat):
    """Weights -> Covariance"""
    
    # Weights transposes, matrix multiply with covmatrix and matrix multiply this with weights and square root the answer
    return (weights.T @ covmat @ weights)**0.5

# minimum vol for a certain return
from scipy.optimize import minimize
import numpy as np

def minimize_vol (target_return, er, Cov):
    
    # number of assets
    n = er.shape[0]
    # guess weights to achieve goal
    initial_guess = np.repeat(1/n, n)
    # make copies of this boundary for every asset
    boundary = ((0.0, 1.0),)*n
    # Return should be whatever the target is
    return_is_target = {
        'type': 'eq',
        'args': (er,),
        'fun': lambda weights, er: target_return - portfolio_returns(weights, er)
        
    }
    # weights should equal one
    weights_sum_1 = {
        'type':'eq',
        'fun': lambda weights: np.sum(weights) - 1
    }
    # Optimiser
    results = minimize(portfolio_volatility, initial_guess,
                       args=(cov,), method='SLSQP',
                       options={'disp': False},
                       constraints=(return_is_target, weights_sum_1),
                       bounds=boundary)
    return results.x

# Target weights
def optimal_weights(n_points, er, cov):
    """ Get a list of weights for min and max returns"""
    # generate the target return give the min and max returns
    target_rtns = np.linspace(er.min(), er.max(), n_points)
    # for target rtns, loop through the function for what this would be and give me a set of weights
    weights = [minimize_vol(target_return, er, cov) for target_return in target_rtns]
    return weights

# multi asset portfolio for mimimum volatility portfolio
def plot_Portfolio(n_points, er, cov):
    """
    plot Efficient portfolio for n assets
    """
    weights = optimal_weights(n_points, er, cov)
    Returns = [portfolio_returns(w,er) for w in weights]
    Covariance = [portfolio_volatility(w,cov) for w in weights]
    Portfolio_final = pd.DataFrame({"Returns":Returns, "Volatility": Covariance})
    return Portfolio_final.plot.line(x="Volatility", y="Returns");

--> Derived from Edhec course