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:

https://docs.google.com/spreadsheets/d/1L5-yUB0EWeBdJNMdELKBRmBQ1JJ0QymrtDLkVhHCVn8/pub?gid=649021574&single=true&output=csv

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:

NASDAQ
http://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=NASDAQ&render=download

NYSE
http://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=NYSE&render=download

AMEX
http://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=AMEX&render=download


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

=IMPORTDATA("http://www.nasdaq.com/screening/companies-by-industry.aspx?exchange=NASDAQ&render=download")

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)
  {    
    return;
  }
  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"))
    {
      return;
    }
  }
  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++)
  {
    dstLastSale[j][0]="n/a";
  }
  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);
    
    flag="false";
    for(var j=0;j<dnRows-1;j++)
    {
      if(dstStock[j][0].toString().toUpperCase() == srcStock[i][0].toString().toUpperCase())
      {
        flag = "true";
        foundStock = j;
        break;
      }
    }
    if(flag=="true")
    {
      dstLastSale[foundStock][0] = srcLastSale[i][0];            
    }
    else
    {
      var dnRows1 = dstSheet.getLastRow()+1;
      dstSheet.getRange("A" + dnRows1).setValue(srcStock[i][0]);
      dstSheet.getRange(dnRows1,lCol+1,1,1).setValue(srcLastSale[i][0]);
      for(var k=2;k<=lCol;k++)
      {
        if(dstSheet.getRange(dnRows1, k).getValue()=="")
        {
          dstSheet.getRange(dnRows1, k).setValue("n/a");
        }
      }
    }
  }
  dstSheet.getRange(1,lCol+1).setValue(curDat);  
  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; 

FileTools:-MakeDirectory("C:\\mydir"); 
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("https://docs.google.com/spreadsheets/d/1L5-yUB0EWeBdJNMdELKBRmBQ1JJ0QymrtDLkVhHCVn8/pub?gid=649021574&single=true&output=csv"); 
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:



with(Statistics):
with(ListTools):
with(LinearAlgebra): 
with(Optimization):
with(plots):

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); 
LineChart(s1);