Creating Share Database

[Edgar]

Hi

I am an absolute beginner at Access and am trying to teach
myself the hard way.

I am designing a database for storing various data
relating to uk shares.

I currently have a web query set up in Excel that queries
the data by sector so I have 98 sheets with anyhting from
2 to 100 shares on it - the other info is share price,
high price, low price, % change, volume etc

The web query is set to run everyday and then i will be
exporting to access.

As I am a beginner I would like some help with the layout
of the database. What would be the best structure of the
tables - one per share, one per sector....

I will be using the database to run lots of queries for
eg: Comparing two shares volume over a month, finding the
biggest movers, tracking a shares price over a year...etc

Any help would be appreciated.

TIA

 

[Andrew Smith]

I set up something similar with these tables:

tblCompanies
CompanyID (autonumber, primary key)
Ticker (text, for the EPIC code)
CompanyName (text)

tblPrices
PriceID (autonumber, primary key)
CompanyID (foreign key, linked to tblCompanies)
PriceDate (Date/Time)
BidPrice
OfferPrice
MidPrice

tblStatisticTypes
StatisticID (autonumber, primary key)
StatisticShortName
StatisticLongName

tblCompanyStatistics
CompanyStatisticID (autonumber, primary key)
CompanyID (foreign key, linked to tblCompanies)
StatisticID (foreign key, linked to tblStatisticTypes)
StatisticDate
StatisticValue (currency data type - could also use double)

I haven't bothered with sectors, but if you want to do this then have
another table for sectors, and a foreign key field in tblCompanies
containing the sectorID.

tblStatisticTypes contains one entry for each type of statistic that I want
to track, including:

- Normalised EPS (historic)
- Normalised EPS (forecast)
- Gross gearing
- Net tangible asset value per share
- Number of shares in issue