I am proficient in Excel but new to Access. I'd avoid using Access entirely except that the volume of data I am working with is overwhelming Excel.
I want to analyze 20 years worth of daily stock price data across 500-1000 stocks, each with >10 variables to track. I can do all the analysis I need for a small number of stocks in Excel, but want to generalize it to a bigger study.
In essence, I really just want to use Access as a data store. I don't think I really need to run complicated queries. Rather, I want to be able to save new data I get off the web or other databases, pull it into Excel, run various analyses, store it back into Access, etc.
Is there a simple example out there of donig these kind of basic functions? I am confortable using VBA. Once the Access database is set up, how does one create a new field and populate it with a calculation (eg. the product of two other fields)?
Finally, I have a specific question about this application. I would like to calculate a ranking for a particular field across all stocks in the database for each day. That is, say for 2/1/2002 I want to know if particular stock's yearly increase is 227th out of 500 stocks. This requires analyzing a cross section of stocks then running a macro or VBA against that data set. Has anybody done something like this before?
Thanks for any pointers, I know exactly what I want to do here but am getting stuck on the basic interaction between Excel and Access.
THANKS!
I want to analyze 20 years worth of daily stock price data across 500-1000 stocks, each with >10 variables to track. I can do all the analysis I need for a small number of stocks in Excel, but want to generalize it to a bigger study.
In essence, I really just want to use Access as a data store. I don't think I really need to run complicated queries. Rather, I want to be able to save new data I get off the web or other databases, pull it into Excel, run various analyses, store it back into Access, etc.
Is there a simple example out there of donig these kind of basic functions? I am confortable using VBA. Once the Access database is set up, how does one create a new field and populate it with a calculation (eg. the product of two other fields)?
Finally, I have a specific question about this application. I would like to calculate a ranking for a particular field across all stocks in the database for each day. That is, say for 2/1/2002 I want to know if particular stock's yearly increase is 227th out of 500 stocks. This requires analyzing a cross section of stocks then running a macro or VBA against that data set. Has anybody done something like this before?
Thanks for any pointers, I know exactly what I want to do here but am getting stuck on the basic interaction between Excel and Access.
THANKS!