S
Shyam
I am creating a table in Access to store historical prices of different
mutual funds. I get these share prices from different mutual fund sites and
I would like to consolidate them into one table so I can compare their
performances. So I have set up a table with a date field, which is set up as
the primary key of the table, indexed with no duplicates, and then additional
fields such as 0001 for fund 1, 0002 for fund 2 and so on.
I have collected the data in excel spreadsheets where I have the data for
each scheme in two columns, one called date and the other called the number
for the fund (0001, 0002, etc.) Since different funds have started at
different times, the start and end dates are different for each fund. I now
want to import them into the same table.
The first fund , 0001, imported successfully. When I tried to import the
second fund, 0002, what I hoped would happen was that the fund prices would
automatically slot themselves in the new column, 0002, in the table,
alongside the correct dates, and when new dates were required which werent
created when importing 0001, they would insert new rows for the additional
dates.
But none of the data from 0002 got imported and I got an error message about
the referential integrity of the imported data.
One option I have is to create the entire table in excel and then import it
into a table in access. But this would be a really messy and time consuming
option as I would have to take the data from multiple sheets in excel and
paste it into the correct cells in the master sheet manually, in the correct
places. I have over 400 such funds, each with about three years of daily
data, or about 1000 rows on average (some might have less than three months
and some over eight years), so this exercise will take me ages. I was hoping
that I could use the date as the primary key to somehow automate the
importing of the data so that the values will go up against the correct dates
automatically.
I hope I have outlined the problem clearly. The net result will be a time
series database of fund prices, with one table having a date field followed
by fields such as 0001,0002, etc. I would really appreciate any help in
solving this.
mutual funds. I get these share prices from different mutual fund sites and
I would like to consolidate them into one table so I can compare their
performances. So I have set up a table with a date field, which is set up as
the primary key of the table, indexed with no duplicates, and then additional
fields such as 0001 for fund 1, 0002 for fund 2 and so on.
I have collected the data in excel spreadsheets where I have the data for
each scheme in two columns, one called date and the other called the number
for the fund (0001, 0002, etc.) Since different funds have started at
different times, the start and end dates are different for each fund. I now
want to import them into the same table.
The first fund , 0001, imported successfully. When I tried to import the
second fund, 0002, what I hoped would happen was that the fund prices would
automatically slot themselves in the new column, 0002, in the table,
alongside the correct dates, and when new dates were required which werent
created when importing 0001, they would insert new rows for the additional
dates.
But none of the data from 0002 got imported and I got an error message about
the referential integrity of the imported data.
One option I have is to create the entire table in excel and then import it
into a table in access. But this would be a really messy and time consuming
option as I would have to take the data from multiple sheets in excel and
paste it into the correct cells in the master sheet manually, in the correct
places. I have over 400 such funds, each with about three years of daily
data, or about 1000 rows on average (some might have less than three months
and some over eight years), so this exercise will take me ages. I was hoping
that I could use the date as the primary key to somehow automate the
importing of the data so that the values will go up against the correct dates
automatically.
I hope I have outlined the problem clearly. The net result will be a time
series database of fund prices, with one table having a date field followed
by fields such as 0001,0002, etc. I would really appreciate any help in
solving this.