2. Forecasting Stock Prices Modeling stock price behavior is a difficult (nay, impossible) and potentially very risky undertaking. But let's try it anyway! Suppose that over a certain time frame we expect a stock's price behavior to more-or-less fit a logistic growth model, dp dt - kp(1- (1) M where p is the price at time t, k is the growth rate, and M is an upper bound for the stock price. Choose a stock symbol that was in existence on your birthday. Some examples are AAPL, MSFT, F, KO, GE, but there are many more. Use Yahoo! Finance to generate the daily price history of the stock of your choice starting on your birthday and extending for 3 months. Click Download to save a CSV file of the history. Open the CSV in a spread sheet app like Excel or Google Sheets. It should look something like this: E 1 Date Open High Low Close Adj Close Volume 2 1982-11-03 1.322953 1.363503 1.282403 1.363503 0.003502 19367800 3 1982-11-04 1.353366 1.388847 1.317884 1.322953 0.003398 20734500 4 1982-11-05 1.33309 1.348297 1.312815 1.312815 0.003372 10055800 5 1982-11-08 1.282403 1.302678 1.257059 1.262128 0.003242 9391700 6 1982-11-09 1.282403 1.307747 1.25199 1.302678 0.003346 7003500 7 1982-11-10 1.302678 1.338159 1.262128 1.267196 0.003255 12409000 8 1982-11-11 1.267196 1.287471 1.246921 1.277334 0.003281 4226200 9 1982-11-12 1.282403 1.307747 1.257059 1.267196 0.003255 8631000 10 1982-11-15 1.226646 1.241852 1.21144 1.226646 0.003151 4478100 11 1982-11-16 1.201302 1.231715 1.165821 1.216508 0.003125 14736900 12 1982-11-17 1.246921 1.267196 1.221577 1.267196 0.003255 4221800 13 1982-11-18 1.287471 1.322953 1.246921 1.322953 0.003398 10189500 14 1982-11-19 1.312815 1.33309 1.29254 1.29254 0.00332 6376400 15 1982-11-22 1.277334 1.29254 1.262128 1.267196 0.003255 3925800 16 1982-11-23 1.246921 1.267196 1.221577 1.226646 0.003151 6115600 17 1982-11-24 1.241852 1.25199 1.226646 1.25199 0.003216 6110200 18 1982-11-26 1.282403 1.297609 1.267196 1.287471 0.003307 6862100 19 1982-11-29 1.277334 1.29254 1.262128 1.282403 0.003294 7368300 20 1982-11-30 1.312815 1.348297 1.272265 1.338159 0.003437 12691900 21 1982-12-01 1.373641 1.398985 1.348297 1.363503 0.003502 15961300 1982-12-02 1.368572 1.383778 1.353366 1.373641 0.003528 11769000 23 1982-12-03 1.368572 1.388847 1.348297 1.353366 0.003476 6481800 1982-12-06 1.37871 1.409122 1.343228 1.404053 0.003606 14984800 1982-12-07 1.424329 1.439535 1.409122 1.434466 0.003684 21140100 Delete the "Open", "Low", "Adj Close", and "Volume" columns so that only the "Date", "Close", and "High" columns remain. Add a column next to "Close" and compute the difference between the next day's price and the previous one. 25 fx =C3-C2 A B D 1 Date High 1982-11-03 2. 3 3 4 1982-11-04 1982-11-05 1982-11-08 Close Difference 1.363503 -0.04055 x 1.363503 1.388847 1.322953 =C3-C2 1.348297 1.312815 1.302678 1.262128 1.307747 1.302678 1.338159 1.267196 5 6 1982-11-09 1982-11-10 7 Then compute the average daily price and price change by using the AVERAGE command in the spread sheet app. fx =AVERAGE (D3: D65) A B D 1 Difference 2 63 Date High 1982-11-03 1983-01-31 1983-02-01 1983-02-02 Close 1.363503 1.363503 1.616942 1.606805 1.62708 1.591599 1.616942 1.606805 64 0.025344 -0.015206 00152061 0.003861936508 x 65 66 67 1.457196 ? =AVERAGE(D3: D65) Averages Max 68 Use the ratio of these numbers as the rate k in the differential equation (1). fx =D67/C67 A B D 1 Difference Date High 1982-11-03 Close 1.363503 1.363503 2 63 1983-01-31 1.616942 0.025344 64 1983-02-01 1.62708 1.606805 1.591599 1.606805 -0.015206 65 1983-02-02 1.616942 0.015206 66 67 1.4571965 0.003861936508 Averages Max 68 1.687905 69 0.002650251018 x 70 k=1=D67/C67 For the value of M in equation (1) we may use the maximum high price over the time frame. The spread sheet function for this is MAX. fx =MAX(B2:B65) . B D 1 Date High Difference 2 Close 1.363503 1.363503 1.616942 1.606805 1982-11-03 1983-01-31 1983-02-01 63 0.025344 64 1.62708 -0.015206 1.591599 1.606805 65 1983-02-02 1.616942 0.015206 66 67 1.687905 x 1.4571965 0.003861936508 Averages Max 68 =MAX(B2:B65) Repeat this entire process for your own stock symbol choice and dates. Write down a differential equation and initial value for your data. Then solve the initial value problem to find a function p that models your stock price. Plot the actual data and the function p on the same set of axes. Write a paragraph in complete sentences summarizing what you see. Does the function do a good job modeling the stock price? What are some problems with the model? How could you improve the model? Finally, use your solution function p to predict price of the stock in one more month (S0, 4 months after your birth date). A typical month has about 21 trading days, so add 21 to your last t-value to make this approximation. Then look up the actual price of the stock on that day. Did your function do a good job of approximating the actual price? Would you trust this function to approximate the stock's price on your first birthday? Explain. 2. Forecasting Stock Prices Modeling stock price behavior is a difficult (nay, impossible) and potentially very risky undertaking. But let's try it anyway! Suppose that over a certain time frame we expect a stock's price behavior to more-or-less fit a logistic growth model, dp dt - kp(1- (1) M where p is the price at time t, k is the growth rate, and M is an upper bound for the stock price. Choose a stock symbol that was in existence on your birthday. Some examples are AAPL, MSFT, F, KO, GE, but there are many more. Use Yahoo! Finance to generate the daily price history of the stock of your choice starting on your birthday and extending for 3 months. Click Download to save a CSV file of the history. Open the CSV in a spread sheet app like Excel or Google Sheets. It should look something like this: E 1 Date Open High Low Close Adj Close Volume 2 1982-11-03 1.322953 1.363503 1.282403 1.363503 0.003502 19367800 3 1982-11-04 1.353366 1.388847 1.317884 1.322953 0.003398 20734500 4 1982-11-05 1.33309 1.348297 1.312815 1.312815 0.003372 10055800 5 1982-11-08 1.282403 1.302678 1.257059 1.262128 0.003242 9391700 6 1982-11-09 1.282403 1.307747 1.25199 1.302678 0.003346 7003500 7 1982-11-10 1.302678 1.338159 1.262128 1.267196 0.003255 12409000 8 1982-11-11 1.267196 1.287471 1.246921 1.277334 0.003281 4226200 9 1982-11-12 1.282403 1.307747 1.257059 1.267196 0.003255 8631000 10 1982-11-15 1.226646 1.241852 1.21144 1.226646 0.003151 4478100 11 1982-11-16 1.201302 1.231715 1.165821 1.216508 0.003125 14736900 12 1982-11-17 1.246921 1.267196 1.221577 1.267196 0.003255 4221800 13 1982-11-18 1.287471 1.322953 1.246921 1.322953 0.003398 10189500 14 1982-11-19 1.312815 1.33309 1.29254 1.29254 0.00332 6376400 15 1982-11-22 1.277334 1.29254 1.262128 1.267196 0.003255 3925800 16 1982-11-23 1.246921 1.267196 1.221577 1.226646 0.003151 6115600 17 1982-11-24 1.241852 1.25199 1.226646 1.25199 0.003216 6110200 18 1982-11-26 1.282403 1.297609 1.267196 1.287471 0.003307 6862100 19 1982-11-29 1.277334 1.29254 1.262128 1.282403 0.003294 7368300 20 1982-11-30 1.312815 1.348297 1.272265 1.338159 0.003437 12691900 21 1982-12-01 1.373641 1.398985 1.348297 1.363503 0.003502 15961300 1982-12-02 1.368572 1.383778 1.353366 1.373641 0.003528 11769000 23 1982-12-03 1.368572 1.388847 1.348297 1.353366 0.003476 6481800 1982-12-06 1.37871 1.409122 1.343228 1.404053 0.003606 14984800 1982-12-07 1.424329 1.439535 1.409122 1.434466 0.003684 21140100 Delete the "Open", "Low", "Adj Close", and "Volume" columns so that only the "Date", "Close", and "High" columns remain. Add a column next to "Close" and compute the difference between the next day's price and the previous one. 25 fx =C3-C2 A B D 1 Date High 1982-11-03 2. 3 3 4 1982-11-04 1982-11-05 1982-11-08 Close Difference 1.363503 -0.04055 x 1.363503 1.388847 1.322953 =C3-C2 1.348297 1.312815 1.302678 1.262128 1.307747 1.302678 1.338159 1.267196 5 6 1982-11-09 1982-11-10 7 Then compute the average daily price and price change by using the AVERAGE command in the spread sheet app. fx =AVERAGE (D3: D65) A B D 1 Difference 2 63 Date High 1982-11-03 1983-01-31 1983-02-01 1983-02-02 Close 1.363503 1.363503 1.616942 1.606805 1.62708 1.591599 1.616942 1.606805 64 0.025344 -0.015206 00152061 0.003861936508 x 65 66 67 1.457196 ? =AVERAGE(D3: D65) Averages Max 68 Use the ratio of these numbers as the rate k in the differential equation (1). fx =D67/C67 A B D 1 Difference Date High 1982-11-03 Close 1.363503 1.363503 2 63 1983-01-31 1.616942 0.025344 64 1983-02-01 1.62708 1.606805 1.591599 1.606805 -0.015206 65 1983-02-02 1.616942 0.015206 66 67 1.4571965 0.003861936508 Averages Max 68 1.687905 69 0.002650251018 x 70 k=1=D67/C67 For the value of M in equation (1) we may use the maximum high price over the time frame. The spread sheet function for this is MAX. fx =MAX(B2:B65) . B D 1 Date High Difference 2 Close 1.363503 1.363503 1.616942 1.606805 1982-11-03 1983-01-31 1983-02-01 63 0.025344 64 1.62708 -0.015206 1.591599 1.606805 65 1983-02-02 1.616942 0.015206 66 67 1.687905 x 1.4571965 0.003861936508 Averages Max 68 =MAX(B2:B65) Repeat this entire process for your own stock symbol choice and dates. Write down a differential equation and initial value for your data. Then solve the initial value problem to find a function p that models your stock price. Plot the actual data and the function p on the same set of axes. Write a paragraph in complete sentences summarizing what you see. Does the function do a good job modeling the stock price? What are some problems with the model? How could you improve the model? Finally, use your solution function p to predict price of the stock in one more month (S0, 4 months after your birth date). A typical month has about 21 trading days, so add 21 to your last t-value to make this approximation. Then look up the actual price of the stock on that day. Did your function do a good job of approximating the actual price? Would you trust this function to approximate the stock's price on your first birthday? Explain