最近在跟好友搞 Evidence-based Investment。因爲有需要弄 crawler 爬一些資訊,幾次被牆的教訓後,覺得應該把資料存下來以利重複應用,故開始研究資料庫。

建立每隻股票的基本資訊,將作爲其他資料表的 foreign key 的參照來源

CREATE TABLE Profile (
  MarketNo TEXT PRIMARY KEY,
  MarketName TEXT
);

接著是個股每天的收盤價格。我們先收集 2009 - 2013 每個月每日的收盤價。

CREATE TABLE DatePrice (
  No  TEXT,
  Date  DATE,
  Price  REAL,
  FOREIGN KEY(No) REFERENCES Profile(MarketNo) ON UPDATE CASCADE
);

個股每年的配息等資訊

CREATE TABLE StockInfo (
  No TEXT,
  SurplusYear    INTEGER,
  DividendYear   INTEGER,
  ShareholdersMeeting    DATE,
  ExDividendDate DATE,
  ExDividendPrice    REAL,
  ExRightsDate   DATE,
  ExRightsPrice  REAL,
  PriceStaticsticsHighest    REAL,
  PriceStaticsticsMinium REAL,
  PriceStaticsticsAverage    REAL,
  CashDividendSurplus    REAL,
  CashDividendProvident  REAL,
  CashDividendTotal  REAL,
  StockDividendSurplus   REAL,
  StockDividendProvident REAL,
  StockDividendTotal REAL,
  TotalDividend  REAL,
  AnnualYield    REAL,
  FOREIGN KEY(No) REFERENCES Profile(MarketNo) ON UPDATE CASCADE
);

Comments

comments powered by Disqus