This project is designed to take advantage of common and easily-obtainable resources to create a mock portfolio of stocks, mutual funds, crypto instruments and commodities. Each student will be given $100,000 (Not real. Sorry.) to invest as s/he wishes early in the semester, and we will check in periodically on results. There will be a prize awarded to the student who closes out the semester with the portfolio with the highest market value. We will devote a full class period working together to get familiar with Yahoo and set up our portfolios, and another class period at the end of the semester to discuss what we learned. The portfolio simulation accounts for 20% of your grade for this course. There are four deliverables, and the points awarded for each of them are expressed in parentheses: 1. Initial setup of portfolio and manipulation in Excel (5 points) 2. Valuation number 1 (5 points) 3. Valuation number 2 (5 points) 4. Final valuation and analysis (5 points) Students will download their portfolios from Yahoo, analyze them in Excel, and upload their spreadsheets to Canvas for each of the above deliverables. For deliverables two through four, students will answer the following questions on the spreadsheet itself: 1. What has happened to the Dow and S&P 500 since the last valuation? 2. What has happened to the crypto and oil markets since the last valuation? 3. What have been the big drivers to the changes in the markets? 4. How much has your portfolio increased/decreased in value? 5. Is your gain/loss consistent with the changes in the market indices? Why/Why not? 6. What changes have you made in your portfolio since your last submission? Why? 7. What changes do you think you should make to your portfolio? 1) Navigate to finance.yahoo.com 2) If you do not have a Yahoo email account, create one 3) Click My Portfolio at the top of the page 4) Click Create Portfolio in the middle of the page 5) Click Screeners at the top of the page to get categories of stocks to consider 6) Review these categories for familiarity and to get an idea of what is out there, and then choose seven issues for your portfolio. At least one must be a mutual fund (look at Top Mutual Funds), and the rest must be corporate equities. Feel free to search for the symbols associated with companies you want to follow but that are not listed here. Record the symbols of the entities you will follow. 7) Click Markets, then click Cryptocurrencies, pick one, record its Symbol 8) Click Markets, record the Symbol GC-F, which is Gold 9) Click Markets, record the Symbol CL-F, which is Crude Oil 10) Click My Portfolio at the top of the page 11) For each of the ten issues you have selected, add them to your portfolio as follows: a. Click Add Symbol in the middle of the page b. Click Expand/Collapse All in the middle of the page c. Assume that you want to buy $10,000 of each of your issues. For each one, divide $10,000 by the stock price indicated under the Symbol to get Shares Enter Shares d. e. Enter stock price as Cost/Share f. Check to make sure that each Market Value is approximately $10,000 and correct as necessary 12) Record the level of the Dow, S&P 500, and Nasdaq markets for the day you created your portfolio. You will need these in the future. ECON 320-Financial Markets and the Monetary System Exporting Your Portfolio to Excel 12 Jan 22 1. Navigate to finance.yahoo.com and log in 2. Click My Portfolio at the top of the page 3. Click Export in the middle of the page 4. Open Excel 5. Click File, then Open, and find the file you just downloaded. It will be called quotes.csv 285 quates Home Draw Page Layout Formules Data Review View all Formeting n General Cal Rody BI -1 -AA A.A. $-%9 HA Co Ay 5 Parma- Da O Pussible Data Lass Some features might be lost if you save this workbook in the comma-delmined (ce) fumat. To preserve these features, save it Excl AL fymbo 2 Time Owne Open High MILIT 12/23/21100ST MOMM MRS 154 12/25/213425 1.3 132.32 24.1 176.21 12/21/23 26:00 1ST S 1000 1/30 150 Volume Trade Purchase Quantity 1401 3839412 1430 251 457231 15142 175.29 000 41 176 AF Ty 3 AMEN 01 AMPS 7) Add a couple of lines at the top to make your sheet pretty and to identify it; make sure to put your name and date on it. 8) Delete the Time, Change, High, Low, Volume, Trade Date, Commission, High and Low Limit columns. 9) Add columns for Gain/(Loss), Original and Current Dow, NASDAQ and S&P 3 11) Add formulas to calculate Gain/(Loss): =(current price-purchase price) *quantity 12) Add a label and a formula in a cell to total your Gain/(Loss) calculations. In my sheet, formula would be: =sum(G6:g9) 13) Clean up the column labels and number formatting so that your sheet tells a good story Let's agree to round everything up to whole numbers where it makes sense - not for share prices or numbers of shares - so our sheets are easy to read. This project is designed to take advantage of common and easily-obtainable resources to create a mock portfolio of stocks, mutual funds, crypto instruments and commodities. Each student will be given $100,000 (Not real. Sorry.) to invest as s/he wishes early in the semester, and we will check in periodically on results. There will be a prize awarded to the student who closes out the semester with the portfolio with the highest market value. We will devote a full class period working together to get familiar with Yahoo and set up our portfolios, and another class period at the end of the semester to discuss what we learned. The portfolio simulation accounts for 20% of your grade for this course. There are four deliverables, and the points awarded for each of them are expressed in parentheses: 1. Initial setup of portfolio and manipulation in Excel (5 points) 2. Valuation number 1 (5 points) 3. Valuation number 2 (5 points) 4. Final valuation and analysis (5 points) Students will download their portfolios from Yahoo, analyze them in Excel, and upload their spreadsheets to Canvas for each of the above deliverables. For deliverables two through four, students will answer the following questions on the spreadsheet itself: 1. What has happened to the Dow and S&P 500 since the last valuation? 2. What has happened to the crypto and oil markets since the last valuation? 3. What have been the big drivers to the changes in the markets? 4. How much has your portfolio increased/decreased in value? 5. Is your gain/loss consistent with the changes in the market indices? Why/Why not? 6. What changes have you made in your portfolio since your last submission? Why? 7. What changes do you think you should make to your portfolio? 1) Navigate to finance.yahoo.com 2) If you do not have a Yahoo email account, create one 3) Click My Portfolio at the top of the page 4) Click Create Portfolio in the middle of the page 5) Click Screeners at the top of the page to get categories of stocks to consider 6) Review these categories for familiarity and to get an idea of what is out there, and then choose seven issues for your portfolio. At least one must be a mutual fund (look at Top Mutual Funds), and the rest must be corporate equities. Feel free to search for the symbols associated with companies you want to follow but that are not listed here. Record the symbols of the entities you will follow. 7) Click Markets, then click Cryptocurrencies, pick one, record its Symbol 8) Click Markets, record the Symbol GC-F, which is Gold 9) Click Markets, record the Symbol CL-F, which is Crude Oil 10) Click My Portfolio at the top of the page 11) For each of the ten issues you have selected, add them to your portfolio as follows: a. Click Add Symbol in the middle of the page b. Click Expand/Collapse All in the middle of the page c. Assume that you want to buy $10,000 of each of your issues. For each one, divide $10,000 by the stock price indicated under the Symbol to get Shares Enter Shares d. e. Enter stock price as Cost/Share f. Check to make sure that each Market Value is approximately $10,000 and correct as necessary 12) Record the level of the Dow, S&P 500, and Nasdaq markets for the day you created your portfolio. You will need these in the future. ECON 320-Financial Markets and the Monetary System Exporting Your Portfolio to Excel 12 Jan 22 1. Navigate to finance.yahoo.com and log in 2. Click My Portfolio at the top of the page 3. Click Export in the middle of the page 4. Open Excel 5. Click File, then Open, and find the file you just downloaded. It will be called quotes.csv 285 quates Home Draw Page Layout Formules Data Review View all Formeting n General Cal Rody BI -1 -AA A.A. $-%9 HA Co Ay 5 Parma- Da O Pussible Data Lass Some features might be lost if you save this workbook in the comma-delmined (ce) fumat. To preserve these features, save it Excl AL fymbo 2 Time Owne Open High MILIT 12/23/21100ST MOMM MRS 154 12/25/213425 1.3 132.32 24.1 176.21 12/21/23 26:00 1ST S 1000 1/30 150 Volume Trade Purchase Quantity 1401 3839412 1430 251 457231 15142 175.29 000 41 176 AF Ty 3 AMEN 01 AMPS 7) Add a couple of lines at the top to make your sheet pretty and to identify it; make sure to put your name and date on it. 8) Delete the Time, Change, High, Low, Volume, Trade Date, Commission, High and Low Limit columns. 9) Add columns for Gain/(Loss), Original and Current Dow, NASDAQ and S&P 3 11) Add formulas to calculate Gain/(Loss): =(current price-purchase price) *quantity 12) Add a label and a formula in a cell to total your Gain/(Loss) calculations. In my sheet, formula would be: =sum(G6:g9) 13) Clean up the column labels and number formatting so that your sheet tells a good story Let's agree to round everything up to whole numbers where it makes sense - not for share prices or numbers of shares - so our sheets are easy to read