3073 Reputation

16 Badges

9 years, 66 days

MaplePrimes Activity

These are Posts that have been published by MrMarc


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);




quandl ( has a great feature called superset (you need a free acount)
where the user can combine different data variables (4 000 000 to choice from) into a big
dataset (csv file) that can be downloaded from a permenant web url. This a great data feed
for maple. The problem is however that you have to use stringtools (quite messy) in maple to
extract the data. Hence, it would be great to have a simple procedure that only needs the web

Fridays Killer Questions 7city Learning:

Question) The number sequence is: 2 1 3 6 5 11 18 17 which number should come after 17?
Answer) 35

Fridays Killer Questions 7city Learning:

Question) You're the captain of a pirate ship and your crew gets to vote on how the gold is divided up. If fewer than half of the pirates agree with you, you die. How do you recommend apportioning the gold in such a way that you get a good share of the booty, but still survive?

Answer) You divide the booty evenly between the top 51% of the crew.

Fridays Killer Questions 7city Learning:

Question) Calculate the number of degrees between the hour hand and the minute hand of a clock (non digital) that reads 3:15.

Answer) The minute hand will be horizontal and the hour hand will also almost be horizontal but it will have moved ¼ of an hour 12 hours=360 degrees, 6 hours = 180 degrees, 3 hours =90 degrees, 1 hour = 30 degrees, ¼ of an hour=7.5 degrees

1 2 3 4 5 6 7 Page 1 of 7