A better way to calculate monthly returns?

[cyborgoddess]

I've been trying for two years to find a fast way to calculate montly returns for a universe of financial figures. I start out at a set value, (say $10,000) calculate a return based on that month's price change, then paste that number into a table. For the next month, I take that new value, calculate it against the next month's price change to create yet another new value and so on. Each month has roughly 10,000 entries that need to be calculated and I have to do it for 3 and 5 year time frames (so repeat the process 36 and 60 times).

Right now I am using a module that calls a series of SQL Statements to align the data (previous month's return plus new month's price change) then I use a loop to repeat the process 36/60 times.

The problem is that it takes forever, like 16 hours to calculate the returns. I know this isn't an easy project, but is there a better way?

Thanks,

Amy

 

[pbaldy]

What does the raw data actually look like (the structure of the table)? Better yet, can you post a sample db with the table(s) in it?

 

[cyborgoddess]

Sample Table

This is what the table looks like before I begin running my script:

Manager Month End % Ch MarketValue
M1 01/31/2000 3 10000
M1 02/28/2000 2
M1 03/31/2000 1
M1 04/30/2000 3
M1 05/31/2000 2
M1 06/30/2000 1
M2 01/31/2000 3 10000
M2 02/28/2000 2
M2 03/31/2000 1
M2 04/30/2000 3
M2 05/31/2000 2
M2 06/30/2000 1
M3 01/31/2000 3 10000
M3 02/28/2000 2
M3 03/31/2000 1
M3 04/30/2000 3
M3 05/31/2000 2
M3 06/30/2000 1


And this is what is the final output
Manager Month End % Ch. MarketValue
M1 01/31/2000 3 10000
M1 02/28/2000 2 10200
M1 03/31/2000 1 10302
M1 04/30/2000 3 10611.06
M1 05/31/2000 2 10823.2812
M1 06/30/2000 1 10931.51401
M2 01/31/2000 3 10000
M2 02/28/2000 2 10200
M2 03/31/2000 1 10302
M2 04/30/2000 3 10611.06
M2 05/31/2000 2 10823.2812
M2 06/30/2000 1 10931.51401
M3 01/31/2000 3 10000
M3 02/28/2000 2 10200
M3 03/31/2000 1 10302
M3 04/30/2000 3 10611.06
M3 05/31/2000 2 10823.2812
M3 06/30/2000 1 10931.51401

The market value gets calculated by taking the previous month's value *(1+%Ch/100).

Does that help?