Storing stock prices only is in general not very useful. We usually want to store additional static information about companies and related derivatives such as stock options and futures. Economic theory tells us that looking for cycles and trends in historical price data is more or less a waste of time; therefore, creating a database seems be even more pointless. Of course you don't have to believe the theory, and anyway creating a stock prices database is a fun technical challenge. Also a database is useful for portfolio optimization (see the recipe, Optimizing an equal weights 2 asset portfolio).
We will base the design on the star schema pattern covered in Implementing a star schema with fact and dimension tables. The fact table will hold the prices, with a date dimension table, asset dimension table, and a source dimension table as in the following diagram:
Obviously, the schema will evolve over time with tables, indexes, and columns added or removed as needed. We will use the schema in the Populating the stock prices database recipe.
The schema is defined in the database_tables.py
file in this book's code bundle:
from sqlalchemy import Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Date from sqlalchemy import ForeignKey from sqlalchemy import Integer from sqlalchemy import String Base = declarative_base()
class StockPrice(Base): __tablename__ = 'stock_price' id = Column(Integer, primary_key=True) date_id = Column(Integer, ForeignKey('date_dim.id'), primary_key=True) asset_id = Column(Integer, ForeignKey('asset_dim.id'), primary_key=True) source_id = Column(Integer, ForeignKey('source_dim.id'), primary_key=True) open_price = Column(Integer) high_price = Column(Integer) low_price = Column(Integer) close_price = Column(Integer) adjusted_close = Column(Integer) volume = Column(Integer)
class DateDim(Base): __tablename__ = 'date_dim' id = Column(Integer, primary_key=True) date = Column(Date, nullable=False, unique=True) day_of_month = Column(Integer, nullable=False) day_of_week = Column(Integer, nullable=False) month = Column(Integer, nullable=False) quarter = Column(Integer, nullable=False) year = Column(Integer, nullable=False)
class AssetDim(Base): __tablename__ = 'asset_dim' id = Column(Integer, primary_key=True) symbol = Column(String, nullable=False, unique=True) name = Column(String, nullable=False) # Could make this a reference to separate table category = Column(String, nullable=False) country = Column(String, nullable=False) # Could make this a reference to separate table sector = Column(String, nullable=False)
class SourceDim(Base): __tablename__ = 'source_dim' id = Column(Integer, primary_key=True) name = Column(String, nullable=False) url = Column(String)