I used to develop several similar databases when I worked at A.G.Edwards headquarters in St. Louis, looking at stocks and other commodities. Ticker symbols should serve as a good unique key for each company, and with the historical or daily data being imported it should be consistent. One question before throwing a design idea out would be the type of analysis they want to run, daily by grouping (sector, index, etc.) of just that days figures, or in those groups for that day along with the entire last 10 years of data? Statistical variability with such a large time span across a group is going to move very little with just 1 day added each time the numbers are run, other than indicating "huge" market changes. Usually they are looking for trends - subtler changes in direction. It may be good to have the 10 year history to run as comparitive data against a trend, but that means your analysis is probably going to cover not just the day and 10 years, but a trend period (which could vary by grouping, but let's not get complicated yet) analysis as well looking at current week, month, 30 days, etc. With that volume of data and analysis, we did do those in access - but SQL Server is worth considering to keep everything more efficient.
OK, here's a stab at the design.
COMPANY TABLE
Ticker (key), Full name of company, Date Went Public (began trading), Exchange, Sector, Industry (assuming this is limited to being a single industry assigned to each company)
MARKET INDEX TABLE
Unique Key, Index Name
INDEX COMPANIES TABLE (many to many)
Ticker, Market Index Key (so each company can belong to several indexes as needed), DateBegin, DateEnd (so companies can move on and off an index without including their full history, just what applies)
Note- if allowing a company to belong to multiple sectors or industries, then a similar structure would be needed for those - removing those fields from the Company table.
END DAY DATA TABLE
Date, Ticker, OpenPrice, HighPrice, LowPrice, ClosePrice, Volume, OpenInterest
Hope that helps get some ideas started.
Tim Ritter
Fort Wayne, IN
--- In MS_Access_Professionals@yahoogroups.com, "Bob" <gilpatric59@...> wrote:
>
> Hi, I am interested in building a database for storing/analyzing end of day data for stock prices. At a minimum, I would store the following: ticker symbol, open price, high price, low price, close price, volume, open interest, sector, industry, exchange. This data would be collected on over 10,000 companies daily. Would want to upload a minimum of last 10 years of data. I would need to run a daily analysis of relationships between price and volume for entire market indexes, sectors, industries and individual companies. Are you able to get me started on a design?
>
Tidak ada komentar:
Posting Komentar