The format of these files is: cts(date,symbol,open,high,low,close,volume), splits(date,symbol,post,pre), dividend(date,symbol,dividend).

Create another table DAILY_PRCNT, with fields: TDATE,SYMBOL,PRCNT which will have the daily percentage gain/loss adjusted for dividends and splits.

(all code must be SQL, etc.).

1) HINT: MSFT (Microsoft) on 2004-11-12 closed at 29.97. They issued a dividend of 3.08, with ex-dividend date of 2004-11-15. Meaning anyone who buys the stock on-or-after 2004-11-15 is NOT entitled to the dividned. On 2004-11-12 it was $29.97 equity, by morning 2004-11-15 it turned into (26.89 equity + 3.08 cash). When markets closed on 2004-11-15 at 27.39, the gain was from 26.89 to 27.39. (preclose - dividend) * (1+r) = close (29.97 - 3.08)*(1+r) = 27.39 r = (27.39/(29.97 - 3.08))-1 = 0.018594 or 1.8594% daily gain. Can test: (29.97 - 3.08) * (1+0.018594) = 27.390, which matches closing price on 2004-11-15.

2) HINT: splits, MSFT did a 1 to 2 split on 2003-02-18. During a split, each share of a company gets turned into several shares of lower value each. The total value held by investors is not changed. Closing price on 2003-02-14 is 48.30 Closing price on 2003-02-18 is 24.96 On 2003-02-14 it was 48.30 stock, by morning 2003-02-18 it turned into 2 * 24.150 equity (total value still 48.30). (prevclose * pre/post) * (1+r) = close (48.30 * 1/2) * (1+r) = 24.96 The dain/loss is caluclated from 24.15 (value after split) to 24.96 (closing price on 2003-02-18). r = (24.96 / (48.30 * 1/2))-1 = 0.033540 or 3.3540% daily gain. Can test: (48.30 * 1/2) * (1+ 0.033540) = 24.96 Loss is just a negative percentage.

Please write a sql query for daily percentage for 1 and 2