Hi all,
Problem Statement:
I want to compile a database that monitors the returns and other information of investment funds over time. Each month data about each fund is entered into the database.
To determine the layout I used the following method: I captured all the data I need in an excel sheet, in the form of records in a database. I then simply imported the data and used Acess's table analyzer to obtain table layouts and relationships.
Everything is hunky-dory up to here (since I didn't do much). My next step is to create a query which determines a return over a period ie if the return for Feb, Mar, May is 3%, 2% and 1% respectively then the return for the period (Feb to May) is (1+3%)*(1+2%)*(1+1%)-1 = 6.1% The return is usually needed for 3 months, 12 months and 60 months.
The easier way of determining this is by creating an index. All funds start of with a value say 100 at the start date. As the return for each month is captured the index grows or depreciates ie Index at Jan 1995 = 100. Return for Feb 3% then the index value becomes 100*(1+3%)= 103. Therefor if the index at the end of December 1995 is 116 then the return for the period is (116/100)-1 = 16%. But if there was no return for Jan 1995 (fund didn't exist) then there is no return for period Jan 1995 to dec 1995.
I tried to create this query but have the following problems: 1) I cannot seem to get all funds starting at 100 for Jan 1995. from here I cannot go anywhere.
I tried attaching the database but its just too big. Even after deleting most of the data it still is greater than 2M. Zipping gets me too 300KB. Any ideas?
Problem Statement:
I want to compile a database that monitors the returns and other information of investment funds over time. Each month data about each fund is entered into the database.
To determine the layout I used the following method: I captured all the data I need in an excel sheet, in the form of records in a database. I then simply imported the data and used Acess's table analyzer to obtain table layouts and relationships.
Everything is hunky-dory up to here (since I didn't do much). My next step is to create a query which determines a return over a period ie if the return for Feb, Mar, May is 3%, 2% and 1% respectively then the return for the period (Feb to May) is (1+3%)*(1+2%)*(1+1%)-1 = 6.1% The return is usually needed for 3 months, 12 months and 60 months.
The easier way of determining this is by creating an index. All funds start of with a value say 100 at the start date. As the return for each month is captured the index grows or depreciates ie Index at Jan 1995 = 100. Return for Feb 3% then the index value becomes 100*(1+3%)= 103. Therefor if the index at the end of December 1995 is 116 then the return for the period is (116/100)-1 = 16%. But if there was no return for Jan 1995 (fund didn't exist) then there is no return for period Jan 1995 to dec 1995.
I tried to create this query but have the following problems: 1) I cannot seem to get all funds starting at 100 for Jan 1995. from here I cannot go anywhere.
I tried attaching the database but its just too big. Even after deleting most of the data it still is greater than 2M. Zipping gets me too 300KB. Any ideas?
But it is still huge. I'll spend some time 2day and maybe I'll get something worthwile out there.