Hmm. A response that I wrote late last night did not appear. I'll be I just didn't hit Post comment. That's just as well, as this morning I came up with a much better response. The ideas are all the same as what I posted yesterday. There are some details that required some work. I think you will like what I have been able to do. Download the ZIP archive attached to this message.

Download 178_My-Data.zip
View file details

Extract the contents (2 files) and open the Maple worksheet. It might take about 30 seconds to execute the entire worksheet. If you do so, double-click on the final matrix (S5) to see what I have created.

When you go back and read the worksheet's details you will see that I have included some timings of different implementations of some of the steps.

I will post more snippets of code, but none of the matrix output is easily viewable through these posts. Bummer!

Here's the general approach:

Read in the data from an excel file

A:=ExcelTools:-Import("My-Data.xls", "FTSE-250");

Create a price table and construct the matrix with each stock in a separate column. If there is no price for a specific month, that stock's entry will be a 0.

for r in $1..Nr do
  STOCK[trunc(A[r,5]),trunc(A[r,1])] := A[r,4]
end do:

myS := (ss,yy,default) -> `if`(assigned(STOCK[ss,yy]),STOCK[ss,yy],default):
S := <
  < ``, seq( yy, yy=map2(op,2,{indices(STOCK)}) ) > |
  seq( < ss, seq( myS(ss,yy,0), yy=map2(op,2,{indices(STOCK)}) ) >,
         ss=map2(op,1,{indices(STOCK)}) )
>;

Add a row with the total return for each stock.

TotRet := proc( s )
  local i0;
  uses ArrayTools;
  i0 := SearchArray( s );
  return s[i0[-1]]-s[i0[1]]
end proc:
TotRet( S[2..,2] );
S3 := < S,
       < `tot return` , <seq( TotRet( S[2..,c] ), c=2..253 )> >^%T
      >;

The details are all in the worksheet. You should download the ZIP archive as it also contains the Excel spreadsheet from which all of the data is obtained. If you keep the worksheet and spreadsheet in the same folder, you should not have to change anything in the worksheet.

I'm sure you could now export the matrix I have constructed back into an Excel spreadsheet. I have not tried this, yet.

One suggestion I hope the developers will hear is that it would be very empowering if it was possible to do work in the worksheet while the matrix browser was running. As it is, you have to close the matrix browser window to pass control back to the spreadsheet. This appears to be very similar to the limitation that control cannot be passed between Maplet windows, but I digress.

Comments, questions, and suggestions are greatly appreciated.

Doug

---------------------------------------------------------------------
Douglas B. Meade  <><
Math, USC, Columbia, SC 29208  E-mail: mailto:meade@math.sc.edu       
Phone:  (803) 777-6183         URL:    http://www.math.sc.ed

Only 10 years? It's taken me 20 years to get to this level. Of course, Maple could not have done this 20 years ago either.  It's all a matter of evolution - of software and user knowledge. If you hang around the Maple community for a reasonable period of time you'll be surprised how much you will learn.

I'm glad  you found my work useful. I'm just waiting for someone else to show me even better ways to get the same results. I am, in fact, hoping someone can suggest some improvements - that's how I'll maximize my knowledge gain from this exercise.

Doug

---------------------------------------------------------------------
Douglas B. Meade  <><
Math, USC, Columbia, SC 29208  E-mail: mailto:meade@math.sc.edu       
Phone:  (803) 777-6183         URL:    http://www.math.sc.ed