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

As explained in the documentation to find the optimal values you should run engine.solve(). This will return the values, thus you will want to store them in a variable to then use them wherever you want.

...
var constraint = engine.addConstraints([0.0],[1.0],[weights,sumWeights],[weights]);

// Get the result of the optimization engine
var solution = engine.solve()

Also please bare in mind that solve() has a default deadline of 30 seconds. If you want to modify the default deadline time simply pass the amount of seconds you want as a paramater like this engine.solve(300). Also, check these methods that can be applied to your solution to, for instance, determine if it is either feasable or optimal.

Answer 2

A python solution

def ticker_list():
    tckr_list = ['AVV.L', 'SCT.L', 'ROR.L', 'OCDO.L', 'CCC.L', '3IN.L', 'AVST.L', 'ASC.L', 'SPX.L','ECM.L', 'TRN.L', 'PLTR']
    return tckr_list

def Optimize_MaxR_Vc():
    # after getting a list of your asset returns...
    
    # Number of assets in the portfolio
    tckr_list = ticker_list() # this should be for the number of assets you have. if saved as a
    Assets = tckr_list
    num_assets = len(Assets)

    # Lists of variables for Portfolio creation
    Portfolio_returns = []
    Portfolio_Volatilities = []
    Portfolio_GrossR = []
    Aveva_Returns_weight = []
    Softcat_Returns_weight = []
    Rotork_Returns_weight = []
    Ocado_Returns_weight = []
    Computacenter_Returns_weight = [] 
    TInfrastructure_Returns_weight = []
    Avast_Returns_weight = []
    ASOS_Returns_weight = []
    Spirax_Returns_weight = []
    Electrocomponents_Returns_weight = [] 
    Trainline_Returns_weight = []
    Palantir_Returns_weight = []

    #Optimising for expected returns and standard deviation
    Gross_rtn = Gross_return()

    for x in range (100000):
        weights = np.random.random(num_assets)
        weights /= np.sum(weights)
        Portfolio_returns.append(np.sum(weights * Portfolio_rtns.mean() * 250)) # expected returns
        Portfolio_Volatilities.append(np.sqrt(np.dot(weights.T,np.dot(Portfolio_rtns.cov() * 250, weights)))) # standard deviation 
        Portfolio_GrossR.append(np.sum(weights * Gross_rtn.mean() * 250)) # Gross returns
        Aveva_Returns_weight.append(weights[0])
        Softcat_Returns_weight.append(weights[1])  
        Rotork_Returns_weight.append(weights[2]) 
        Ocado_Returns_weight .append(weights[3]) 
        Computacenter_Returns_weight.append(weights[4]) 
        TInfrastructure_Returns_weight.append(weights[5])
        Avast_Returns_weight.append(weights[6])  
        ASOS_Returns_weight.append(weights[7])
        Spirax_Returns_weight.append(weights[8])
        Electrocomponents_Returns_weight.append(weights[9])
        Trainline_Returns_weight.append(weights[10])
        Palantir_Returns_weight.append(weights[11])

        # Create an array of data for portfolio
    Portfolio_returns = np.array(Portfolio_returns)
    Portfolio_Volatilities = np.array(Portfolio_Volatilities)
    Portfolio_GrossR = np.array(Portfolio_GrossR)
    Aveva_Returns_Weight = np.array(Aveva_Returns_weight)
    Softcat_Returns_Weight = np.array(Softcat_Returns_weight)
    Rotork_Returns_Weight = np.array(Rotork_Returns_weight)
    Ocado_Returns_Weight = np.array(Ocado_Returns_weight)
    Computacenter_Returns_Weight = np.array(Computacenter_Returns_weight)
    TInfrastructure_Returns_Weight = np.array(TInfrastructure_Returns_weight)
    Avast_Returns_Weight = np.array(Avast_Returns_weight)
    ASOS_Returns_Weight = np.array(ASOS_Returns_weight)
    Spirax_Returns_Weight = np.array(Spirax_Returns_weight)
    Electrocomponents_Returns_Weight = np.array(Electrocomponents_Returns_weight)
    Trainline_Returns_Weight = np.array(Trainline_Returns_weight)
    Palantir_Returns_Weight = np.array(Palantir_Returns_weight)


    #Creating a table
    Portfolios = pd.DataFrame({'Return': Portfolio_returns, 
                           'Volatility': Portfolio_Volatilities,
                           'Gross Return': Portfolio_GrossR,
                           'Aveva Weight': Aveva_Returns_weight,
                           'Softcat Weight': Softcat_Returns_weight, 
                           'Rotork Weight': Rotork_Returns_weight,
                            'Ocado Weight': Ocado_Returns_weight,  
                            'Computacenter Weight': Computacenter_Returns_weight,
                            '3Infrastructure Weight': TInfrastructure_Returns_weight,
                            'Avast Weight': Avast_Returns_weight,
                            'ASOS Weight': ASOS_Returns_weight,
                            'Spirax Weight': Spirax_Returns_weight,
                            'Electrocomponents': Electrocomponents_Returns_weight,
                            'Trainline': Trainline_Returns_weight,
                            'Palantir': Palantir_Returns_weight})


        # Custom Portfolios

# With this range, what different types of portfolios can we build? 
    # if volatitlity is within this range, where is volatility when you search for max return?
    Min_return = Portfolios[(Portfolios['Volatility']>=.135) & (Portfolios['Volatility']<=14.358)].min()['Return']
    Return = Portfolios.iloc[np.where(Portfolios['Return']==Min_return)]
    Min_return_1 = Portfolios[(Portfolios['Volatility']>=.200) & (Portfolios['Volatility']<=9.00)].min()['Return']
    Return_2 = Portfolios.iloc[np.where(Portfolios['Return']==Min_return_1)]
    Min_return_2 = Portfolios[(Portfolios['Volatility']>=.300) & (Portfolios['Volatility']<=8.00)].min()['Return']
    Return_3 = Portfolios.iloc[np.where(Portfolios['Return']==Min_return_2)]
    Min_return_3 = Portfolios[(Portfolios['Volatility']>=.400) & (Portfolios['Volatility']<=7.00)].min()['Return']
    Return_4 = Portfolios.iloc[np.where(Portfolios['Return']==Min_return_3)]
    Min_return_4 = Portfolios[(Portfolios['Volatility']>=.500) & (Portfolios['Volatility']<=6.00)].min()['Return']
    Return_5 = Portfolios.iloc[np.where(Portfolios['Return']==Min_return_4)]
    Min_return_5 = Portfolios[(Portfolios['Volatility']>=.600) & (Portfolios['Volatility']<=5.00)].min()['Return']
    Return_6 = Portfolios.iloc[np.where(Portfolios['Return']==Min_return_5)]
    Min_return_6 = Portfolios[(Portfolios['Volatility']>=.700) & (Portfolios['Volatility']<=4.00)].min()['Return']
    Return_7 = Portfolios.iloc[np.where(Portfolios['Return']==Min_return_6)]
    Min_return_7 = Portfolios[(Portfolios['Volatility']>=.800) & (Portfolios['Volatility']<=3.00)].min()['Return']
    Return_8= Portfolios.iloc[np.where(Portfolios['Return']==Min_return_7)]
    Min_return_8 = Portfolios[(Portfolios['Volatility']>=.900) & (Portfolios['Volatility']<=2.00)].min()['Return']
    Return_8= Portfolios.iloc[np.where(Portfolios['Return']==Min_return_8)]
    Min_return_9 = Portfolios[(Portfolios['Volatility']>=.100) & (Portfolios['Volatility']<=1.00)].min()['Return']
    Return_9= Portfolios.iloc[np.where(Portfolios['Return']==Min_return_9)]
    
    Final_MaxOp = pd.concat([Return,Return_2, Return_3, Return_4, Return_5, Return_6,
                        Return_7, Return_8, Return_9])

    return Final_MaxOp

I saved it as a module in python lab so that to run it, all I needed to do was:

Portfolio = P.Optimize_MaxR_Vc() # load the results

Portfolio # show the results

P is the module I saved it under so I imported it as from Portfolio import P

Before coming up with the ranges, run:

  # What is the max returns? 
   max(Portfolio_returns)
    
  #What is the min volatility?
   min(Portfolio_Volatilities)

You can separate the various parts of this code into different functions and run them to test out different ranges.

Answer 3

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