Items tagged with portfolio



Portfolio Optimization with Google Spreadsheet and Maple

I will in this post show how to manage data and do portfolio optimization in Maple by using google spreadsheet.

You can either use a direct link to the data:

or you can set up your own google spreadsheet. If you choice to set up your own spreedsheet follow the below road map:

1) select which market you want to follow:




2) Create a new google spreadsheet and name two sheets Blad1 and Panel. In the first cell of Blad1 you put the formula:


you need to change the url to match your selection in 1).

3) In the first cell of Panel you put the name "Ticker" and then you copy all the ticker names from Blad1.

4) In the script editor you put in the below java script code:

function PanelCreation_Stock() 

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName("Blad1");
var dstSheet = ss.getSheetByName("Panel");
var curDat = new Date();
var day1 = curDat.getDay();
if(day1 == 0 || day1 == 1)
var lCol = dstSheet.getLastColumn();
var srcdate = dstSheet.getRange(1, 1, 1, lCol).getValues();

for(var k=1;k<=srcdate[0].length-1;k++)
if(Utilities.formatDate(srcdate[0][k],"GMT", "dd-MMM-yy") == Utilities.formatDate(curDat,"GMT", "dd-MMM-yy"))
var snRows = sourceSheet.getLastRow();
var dnRows = dstSheet.getLastRow();

var srcStock = sourceSheet.getRange("A2:A" + snRows).getValues();
var srcLastSale = sourceSheet.getRange("C2:C" + snRows).getValues();

var dstStock = dstSheet.getRange("A2:A" + dnRows).getValues();
var dstLastSale = dstSheet.getRange("Z2:Z" + dnRows).getValues();

for(var j=0;j<dnRows-1;j++)
var flag = "true";
var foundStock;
for(var i=0;i<snRows-1;i++) //snRows
var sStockVal = srcStock[i][0];

//var foundStock = ArrayLib.indexOf(dstStock,0, sStockVal);

for(var j=0;j<dnRows-1;j++)
if(dstStock[j][0].toString().toUpperCase() == srcStock[i][0].toString().toUpperCase())
flag = "true";
foundStock = j;
dstLastSale[foundStock][0] = srcLastSale[i][0];
var dnRows1 = dstSheet.getLastRow()+1;
dstSheet.getRange("A" + dnRows1).setValue(srcStock[i][0]);
for(var k=2;k<=lCol;k++)
if(dstSheet.getRange(dnRows1, k).getValue()=="")
dstSheet.getRange(dnRows1, k).setValue("n/a");
dstSheet.getRange(2, lCol+1, dstLastSale.length, 1).setValues(dstLastSale);

5) Set it to run each day at 12:00. The code will save the new last sale price for monday to friday with one days lag.

Now we can move on to Maple.

In Maple run the following code to load the data:


X := proc (Url) local theDLL, URLDownloadToFile, myDirectory, myFile, Destination, DL;


theDLL := "C:\\WINDOWS\\SYSTEM32\\urlmon.dll";


URLDownloadToFile := define_external('URLDownloadToFileA', pCaller::(integer[4]), szURL::string, szFileName::string, dwReserved::(integer[4]), lpfnCB::(integer[4]), 'RETURN'::(integer[4]), LIB = theDLL);


if FileTools[Exists]("C:\\mydir") = true then FileTools:-RemoveDirectory("C:\\mydir", recurse = true, forceremove = true) else end if;


myDirectory := "C:\\mydir";
myFile := "data1.csv";
Destination := cat(myDirectory, "\\", myFile);


DL := proc () local M;


URLDownloadToFile(0, Url, Destination, 0, 0);
M := ImportMatrix("C:\\mydir\\data1.csv", delimiter = ",", datatype = string);
M := Matrix(M, datatype = anything)


end proc;


return DL()


end proc:


data := X("");
L := LinearAlgebra:-Transpose(data);

If you use your own spreadsheet you need to change the url to match that spreadsheet.
Select File -> Publish to the web in google spreadsheet

We can now run the portfolio optimization in Maple:



Nr, Nc := ArrayTools:-Size(L):
symb := L[1 .. 1, 2 .. Nc]:
LL := L[2 .. Nr, 2 .. Nc]:
Nr, Nc := ArrayTools:-Size(LL):


# Removing stocks with missing observations
for i to Nc do if Occurrences("n/a", convert(Column(LL, i), list)) >= 1 then AA[i] := i else AA[i] := 0 end if
end do;


DD := RemoveInRange([seq(AA[i], i = 1 .. Nc)], 0 .. 1):
symbb := DeleteColumn(symb, DD):
LLL := map(parse, DeleteColumn(LL, DD)):
Nr, Nc := ArrayTools:-Size(LLL):


# Calculate Return
for j to Nc do
for i from 2 to Nr do


r[i, j] := (LLL[i, j]-LLL[i-1, j])/LLL[i-1, j]


end do
end do;


RR := Matrix([seq([seq(r[i, j], j = 1 .. Nc)], i = 2 .. Nr)], datatype = float[8]);
n, nstock := ArrayTools:-Size(RR):


# Portfolio Optimization
W := Vector(nstock, symbol = w):
y := Vector(n, fill = 2, datatype = float[8]):
s1 := Optimization[LSSolve]([y, RR])[2];
Nr, Nc := ArrayTools:-Size(s1):


j := 0:
for i to Nr do if s1[i] <> 0 then j := j+1; ss1[j] := symbb[1, i] = s1[i] end if end do;


Vector(j, proc (i) options operator, arrow; ss1[i] end proc);




I have a non linear Sharpe ratio with 3 portfolio weights w1,w2 and w2. I want to (globally) maximize the sharpe ratio by choosing w1,w2 and w2 subject to the constraints that each of the variables is in the range of 0 to 1, and that their summation is equal to 1. I also want the maximization to start at an initial point of [w1=0.35,w2=0.6,w3=0.05].

The function is:

SR:= (0.012w1+0.007w2+0.0384w3-0.009)/(stdev)

where stdev is the standard deviation of the portfolio ...

So, I am reading the blog post about Mean Absolute Deviation portfolio optimization that


claims that the traditional portfolio optimization problem can be expressed as seen below:

I am not sure however that it is 100% correct for example you have (r[i,t...

Ok, first of all I renamed the return matrix R (A is allready used)
I now think I got two of them (minimize portfolio variance and minimize portfolio variance for a
given expected return in matrix form) figured out:

Cov := Matrix(CovarianceMatrix(R), datatype = float):
pr := .6*max([seq(ExpectedValue(Column(R, i)), i = 1 .. nstock)]):

A := Matrix([[seq(1, i = 1 .. nstock)]], datatype = float):
b := Vector([1], datatype = float):
Aeq := Matrix(...

If I use a binary {0,1} linear program the cardinal constraint ie control the
number of 1's (long positions in portfolio) works beautiful. See attached worksheet.

The problem starts when I convert the problem into a constrained integer {-1,0,1}
linear problem where -1= short position, 0=no position and 1=long position.

In the first example when we add the constraint  add(w[i], i = 1 .. NC) = 4


n := 100:
nstock := 7:
corr := .8:

R := Matrix(nstock, nstock, proc (i, j) options operator, arrow; `if`(i = j, 1, corr) end proc):
CD := Matrix(LUDecomposition(evalf(R), 'method' = 'Cholesky'), datatype = float[8]):

ev := [seq((1/5)*(rand(-3 .. 4))(), i = 1 .. nstock)]:
st := [seq((rand(1 .. 2))(), i = 1 .. nstock)]:

Page 1 of 1