Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Location
    Minneapolis, Minnesota, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stock price data (2000)

    Hi all. This is my first posting. I am relatively new to Access 2000.
    I would like to create a database consisting of 100 stocks.
    One table holds security information like cusip, ticker symbol, company name, and industry.
    A second table shows which industries belong to which sectors.
    Then I am stuck on how to handle the individual security information.

    The objective of the database is to calculate industry and sector correlations (eg: show the correlation between a composite of utility companies versus a composite of banks). To do this, the database would also need pricing information for the last 10 years, which I have in Excel spreadsheets with one spreadsheet per stock showing the open, high, low, close, shares outstanding and volume traded on the day.

    I do not know whether I should create a separate table for each stock, or use one big table for all the stocks together. Of course, by using one table instead of 100 tables, I would have to duplicate the ticker/cusip information for each record, so the database would necessarily be larger. On the other hand, I am not sure how to query tables in the same way I would query records when I am looking at creating composites.

    Moving forward, I would would need to adjust stock data for special events like stock splits, and sector data for the inclusion of new companies.

    Does anyone have an opinion on which way might be easier to modify and update, and which way might run more quickly?

    Is it possible to query tables in a similar way as one would query records (could I set up a table of table names and by querying this table (say, find all tables for bank stocks) create a new query that creates daily composite data.

    And who knows, maybe such a database shell already exists, that I can modify or expand.

    All thoughts would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stock price data (2000)

    I don't know if Access is the best tool for this. You mention that you want to calculate correlations. Access doesn't have built-in support for such statistical functions. It is possible to program them - see for example <post#=194931>post 194931</post#>.

    Microsoft Excel and statistical analysis applications such as SPSS, SPLUS, NCSS or SAS might be more suitable - there are lots of stock analysis spreadsheet applications, and the statistical packages are very strong in analyzing trends, correlations etc. If you do a Google search for something like "stock analysis", you'll get lots of hits. Combining with a keyword like "spreadsheet" or "statistical" or one of the brand names above will focus your search.

  3. #3
    New Lounger
    Join Date
    Apr 2003
    Location
    Minneapolis, Minnesota, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stock price data (2000)

    Thanks Hans.
    I agree with you about the statistical functions. I am looking at using R (an public domain version of S-Plus) for the statistical calculations.
    Many of these statistical packages excel at statistical functions and are weak at simplifying data manipulation.

    I was thinking of using and OBDC connection between the R-statistical package and a database where I could more easily update/append and otherwise manipulate the data before running any statistics.

    Before I tried it, I wanted to head off on the right path, which would either be using one large table using a ticker and date as a unique record identifier, or creating a table for each ticker. Having 100 tables would take up less space than one large table, but I would not know how to create queries referencing, say, software company stocks, where their symbols are the names of tables and not a field in the records.

    Hunt

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stock price data (2000)

    If you want to store the data in a database, I recommend creating two tables:

    1. A "Tickers" table containing a unique TickerID as primary key (for example an AutoNumber) plus "static" information about each ticker.

    2. A "StockInfo" table containing TickerID and date as composite primary key plus date-dependent information.

    Create a join between the tables on TicketID; set referential integrity for the relationship. This way, the static information is stored only once, while the dynamic data are still linked to the static data.

    You can create a query (or queries) based on both tables to combine information from the two. When importing the data into R, use this query.

  5. #5
    New Lounger
    Join Date
    Apr 2003
    Location
    Minneapolis, Minnesota, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stock price data (2000)

    Thanks HansV, I did just that.
    One table has an auto id for securities and security specific info like ticker, cusip, company name, etc.
    A second table has the pricing data including security id and date as the key plus open, high, low, close, volume and I included end of day market value.
    I also made a third table similar to the pricing data for reported data, which includes security id, date and shares outstanding. I could include sales and earnings at some time to this too.
    The reason I went with three tables is to help me keep the changes in shares outstanding straight when companies do stock splits or issue additional shares.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •