Items tagged with spreadsheet

Feed

 

I from Russia. Excuse me, for my bad English.

I encountered a problem. I use Maple 2016.
I want to programmatically change the background color of a cell in the spreadsheet.
I know that I can change the background color of the cell via the context menu, but I do not want to do. I need the code.

Thank you very much, your Russian stupid student.

Limitation of Maple spreadsheet is only 100 x 52

I don't know why there's a limitation on the size but there's no way of increasing it either.  Is there?

 

Just to be sure that some outside operation wouldn't nudge the spreadsheet size I tried

with(Spread):
a:=CreateSpreadsheet():
b:=Matrix(100,100):

SetMatrix(a,b)
                           Error, (in Spread:-SetMatrix) spreadsheets cannot exceed 52 columns

So unfortunately until the feature gets built into Maple I don't believe there is a workaround.

It seems if I copy some text into one of the cells of a maple spreadsheet I get an error.

Specifically the error only occurs if a space exists in the text.  Also entering text with spaces produces the error as well.

 

hello there - typed into a cell produces   Error, missing operator or ';'

Similarily    5 4 we receive Error unexpected number

There is only two options for the cell properties under type - Symbolic and Floating Point.  Another option to fix this issue would be to have a Label option?  

 

 

Is there a bug for resizing the spreadsheet?  I am unable to drag the size to the full worksheet.  If it's a bug is there a workaround?

LL_104)_NASDAQ.mw
Portfolio_Optimization.txt

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

 

 

 

When I try to export my data from a spreadsheet to Excel, instead of numbers I get a long text containing numbers. For example instead of having just "11625.7677908435" I get "Typesetting:-mprintslash([11625.7677908435], [HFloat(11625.767790843516)])". 

How can I change the decimal to be displayed in the whole spreadsheet. The default decimals to be displayed is 4 in my version. How can I change it to another value? Also I don't want to click on "properties" each time I execute my worksheet. I want it to be automatically display  the number of digits I want. 

Trying to call matrix from escel spreadsheet by command

M=ExcelTools:-Import("Book1.xlsx")

and getting error      M=?"Book1.xlsx"?  in red box and message error.

What is wrong? I do not understand. file Book1.xlsx is present in the same directory as my maple worksheet and not empty.

Please advise.

team-iterator.mw
sample.xls

Howdy

I'm trying to import data from this spreadsheet into this program authored by C.Love.http://www.mapleprimes.com/questions/200480-Product-Grouping

Both files are in the same directory. it imports the data (to some degree), but has problems with order, when decimal points are introducing, also duplicates . see my annotations on the mw file.

 

 

Hello,

I have the following problem:

My function is defined by the determinant of 2 Heun functions

If I plot the phase I get something which looks quite what I'm looking for.

To get a better result I thought I would manually carry out the Wronskian as far as possible...

Doing some manipulations I get another form of the Wronskian which in fact should give the same result...

the problem is it doesnt :-(

I've added the spreadsheet....

I am generating a number of plots in Maple but would like to be able to collect the data points from the Maple plots and export them into an excel spreadsheet or similar spreadhseet program so that they can be loaded into other plotting software programs such as OriginLab.  

 

Is there a way to export the data points from plots produced in Maple into an .xls or .dat format?

Currently, the size of a table/matrix which can be exported to a spreadsheet is limited to 255 columns X 65535 rows. I believe this rule was implemented because of the maximum possible size of a spreadsheet(Am I right?) . This used to be the case in Excel 2003, but since Excel 2007, a spreasheet  can be : 1,048,576 rows by 16,384 columns.

 

Maybe it is time to update this rule?

 

Excel 2003 :http://office.microsoft.com/en-us/excel...

The attached worksheet defines a procedure CircumStrain() which is supposed to create and populate a spreadsheet with the solutions of several equations.  The problem is that the spreadsheet Strain is never created.  Would someone please evaluate the worksheet and perhaps suggest a fix.  Your assistance is appreciated.

Wayne

View 4865_Chan McMinn B.mw on MapleNet

Page 1 of 1