Using a database with excel

[Jeff]

Hi,

I have a program that takes 3000 Policy Funds and accumulates them over 361
periods (monthly for 30 years) The 3000 have different starting fund values
and each fund changes based on a set of returns (there are 361 returns).
Also there are 7000 scenarios - so there are 7000 sets of 361 returns. The
7000 scenarios are split into 7 csv files with 361000 rows (but I also
combined the csv files into 1 gigantic file with 7x361k rows). The macro I
have takes the 3000 funds and loads this into a matrix, then loads then
calculates the Fund at each time period.

I also have an array that writes the rates from the csv file to an array

For i = Scenario# to Scenario#+361
Input #1, CsvReturns(i)
Next i

Then the fund is calculated

For t = 1 to 361
Fund(t) = Fund(t-1) * CsvReturn(t) * Decrement
Next t

To complicate things there is a decrement that depends on the fund level
Decrement = 0.95 if Fund(t=0) < Fund(t)
Decrement = 1.05 if Fund(t=0) >=Fund(t)
There is also a lot more conditions - but I use this for illustrative purpose.

In the end All I want is the average ending fund value at time 361.
So I will have 3000 Policies for 7x1000 scenarios and I want the average.

My macro is very slow because it has to load each policy into an array, then
load the scenario, then calculate the fund value. Reading from the csv file
is also slow.

This is basically how the whole macro works

For ScenarioSet=1 to 7
For Scenario = 1 to 1000
For Policy = 1 to 3000
For TimePeriod = 1 to 361
Calculate Fund(Policy) (based on conditions)
Next TimePeriod
Next Policy
Next Scenario
Next ScenarioSet

My question is - can I use access or some database utility to write the fund
to because there is such a large amount of data?

Does anyone have any suggestions to speed up the calculation - or tips on
how this is usually handled. - I was thinking it might be faster to write to
Access and then read from access.

Thanks for your help

 

[PY & Associates]

A bit difficult to visualize layout of your files.

[Reading from the csv file is also slow;
there are 7000 sets of 361 returns;
7 csv files with 361000 rows (but I also combined the csv files into 1
gigantic file with 7x361k rows]
We do not know what it means by 361 returns.
This does not fit into one row.
Have you considered keeping 7 sheets instead of 7 csv files?
This way you do not need to read them anymore.

Also now you can
use ranges instead of looping one by one;
look up figures using vlookup.

 

[PY & Associates]

With no reference to using Access, we revise our statement thus:
Lay 200 scenarios across a worksheet with 361 rows deep of returns as one
table;
7000 scenarios need 35 tables;
One worksheet has 65,536 rows to accommodate 130 tables of 500 rows each;
Stack all 35 tables at every 500 rows in the same sheet;

The rest is obvious

--
Regards

A bit difficult to visualize layout of your files.

[Reading from the csv file is also slow;
there are 7000 sets of 361 returns;
7 csv files with 361000 rows (but I also combined the csv files into 1
gigantic file with 7x361k rows]
We do not know what it means by 361 returns.
This does not fit into one row.
Have you considered keeping 7 sheets instead of 7 csv files?
This way you do not need to read them anymore.

Also now you can
use ranges instead of looping one by one;
look up figures using vlookup.

--
Regards

Hi,

I have a program that takes 3000 Policy Funds and accumulates them over 361
periods (monthly for 30 years) The 3000 have different starting fund values
and each fund changes based on a set of returns (there are 361 returns).
Also there are 7000 scenarios - so there are 7000 sets of 361 returns. The
7000 scenarios are split into 7 csv files with 361000 rows (but I also
combined the csv files into 1 gigantic file with 7x361k rows). The

macro

I
have takes the 3000 funds and loads this into a matrix, then loads then
calculates the Fund at each time period.

I also have an array that writes the rates from the csv file to an array

For i = Scenario# to Scenario#+361
Input #1, CsvReturns(i)
Next i

Then the fund is calculated

For t = 1 to 361
Fund(t) = Fund(t-1) * CsvReturn(t) * Decrement
Next t

To complicate things there is a decrement that depends on the fund level
Decrement = 0.95 if Fund(t=0) < Fund(t)
Decrement = 1.05 if Fund(t=0) >=Fund(t)
There is also a lot more conditions - but I use this for illustrative purpose.

In the end All I want is the average ending fund value at time 361.
So I will have 3000 Policies for 7x1000 scenarios and I want the average.

My macro is very slow because it has to load each policy into an array, then
load the scenario, then calculate the fund value. Reading from the csv file
is also slow.

This is basically how the whole macro works

For ScenarioSet=1 to 7
For Scenario = 1 to 1000
For Policy = 1 to 3000
For TimePeriod = 1 to 361
Calculate Fund(Policy) (based on conditions)
Next TimePeriod
Next Policy
Next Scenario
Next ScenarioSet

My question is - can I use access or some database utility to write the fund
to because there is such a large amount of data?

Does anyone have any suggestions to speed up the calculation - or tips on
how this is usually handled. - I was thinking it might be faster to

write

to
Access and then read from access.

Thanks for your help