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'm attaching the database and hoping for any recommendations.
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'm attaching the database and hoping for any recommendations.
