One of the main frustrations of Excel’s online offering is the lack of support for VBA which significantly limits the functionality of web published spreadsheets.
Recently Excel have added the ability to use the Solver engine in an online setting but only in edit mode and without the accompanying VBA support it is not possible to automate tasks that would normally be routine in the desktop version of Excel.
One such example in Finance is the solving of non-linear optimisation problems such as the Mean Variance optimal portfolio problem. Using the Sharpe ratio as the objective function to maximise the optimal portfolio weights can be easily found using the Solver engine’s GRG2 algorithm which can handle such mixed integer quadratic problems with relative ease. How though to make this functionality available online ?
The small Excel Online spreadsheet example embedded below uses the iterative Simulated Annealing method which is a meta heuristic optimisation algorithm to arrive at satisfying weights that are arbitrarily close to the globally optimal weights. It is a particularly powerful method that can be used to find the solution for many non-linear problems.
To see this in action click on the embedded spreadsheet above and hit F9 to recalculate. You should see the weights and chart update. The yellow triangle shows the Globally Optimal Mean Variance ( Max Sharpe ratio ) portfolio while the pinkish square represents the Simulated Annealing solution. The portfolio also obeys the cardinality constraints of having no short positions ( weights sum to 100%) and having Cash constrained to a fixed weight of 5%.
peterurbani 