Question: pivot table and time series

I have managed to download data for Nasdaq, NYSE and Amex in Maple. However,
my problem is how do I store the data in a txt file on my desktop in the most optimal way.
I want an mpl file so I can schedul the task manager to run the file each day in the background.
This is as far as I have come:


CSV := 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);
myDirectory := "C:\\mydir":

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

DL := proc () URLDownloadToFile(0, Url, Destination, 0, 0):
ImportMatrix("C:\\mydir\\data1.csv", source = csv)
end proc:

return DL(), FileTools:-RemoveDirectory("C:\\mydir", recurse = true, forceremove = true)

end proc:

A1 := CSV("")[1 .. (), [1, 3]]:
A2 := CSV("")[2 .. (), [1, 3]]:
A3 := CSV("")[2 .. (), [1, 3]]:

A4 := Transpose(`<,>`(A1, A2, A3)):
A4[2, 1] := FormatTime():

ExportMatrix("C:\\Users\\marc\\Desktop\\test.txt", A4);


There are numerous problems here:

1) The data does not have a time stamp hence we have to use maples FormatTime().
The problem with this is that if you run it on a sunday you will get a new date even though
it is the same data as on friday. To get around that I was thinking you could compare the
last downloaded data with the last historical data (in the text file). If they are different a new
date and a new date row can be created.

2) We need to check that the data is stored in the right column. If the ticker symbol
does not exist in the historical file it need to be created in a new column (in row 1 column j)
This steep is very easy in vba and excel with pivot tables. However excel has stupid
restrictions like max 30 rows in a pivotable etc etc So excel is not an option!

The end result should look something like this:

Name                stock 1       stock 2      ...
2012-05-25          11             45          ...
2012-05-06          12             44          ...
...                        ...             ...           ...

It would be cool if you could store the data on the maple server or in a datatable
component. However, I realize that this will not work since the mpl file does not
support datatable components and I doubt you can reference one outside
of an maple worksheet.

Please Wait...