Excel share price performance calculator/matrix

[SAdata]

Hello fellow forumnites.

I have a dataset of JSE companies daily share prices over 5 years. I want to build a formula that works out daily/weekly/monthly/other time period combinations of all possible returns for these time frames accross the whole dataset and then ranks those returns from highest to smallest. I then want to focus on the most extreme of these for my own share research. Does anyone have any idea how I could go about this?

Thanks!

 

[GoB]

The non-macro "Excel way" is usually to transform the data one step at a time with formulas. You can hide the intermediate steps.

Put the data in a sheet - a basic method would be with sorted dates in the left column and share codes across.

E.g. for weekly returns:
Put the week-end dates in another sheet's left column (=cell above+7) with the same share codes.
Add the price lookups: VLOOKUP(date_cell, othersheet, COLUMN(), TRUE) - gets the most recent price prior to the weekly date.
Add your return calculations.

That would be a start... enough to chart all returns for a specific period and frequency.