Stock Portfolio Excel

You have $100,000 to invest in the stock market. In this project you will use Excel to keep track of your stocks or mutual funds, and monitor their performance, over the period of October 18th to October 29th. You must start with a blank excel spreadsheet (no templates). You must use excel formulas for the computations required for this assignments. You must work on the assignment on your own. Any deviation from this will result in a zero for the assignment.

1. You must choose at least 10 stocks/mutual funds to purchase with at least 1 fund (mutual fund, etf, ect.). Use websites such as www.cnbc.com or www.google.com/finance or www.marketwatch.com to research stocks and mutual funds http://time.com/money/5090045/best-mutual-funds-2018/ (ETF's are OK).

In the REAL-TIME QUOTE box, type in the name of the company you are interested in, and click on Symbol Lookup. You will be given the stock symbol for that company which you can type in to get a "QUOTE". A quote gives you the current price ("Last trade") for 1 share of the stock as well as other information. The stock market is only open Monday through Friday during business hours. For example, Apple has the symbol AAPL.

2. Create a workbook with multiple worksheets.
a. The first sheet should be named Stock Portfolio and must contain the following columns. Make sure include titles are given for each column (-20 Points if this is not done on its own sheet). All columns must have appropriate labels:
i. Label the first column Company Names. This column should contain the names of the stocks and funds. 

ii. Label the second column symbols. The second column should contain the symbols of the stocks/funds. 

iii. Label the third column purchase price. The third column should contain the purchase price of the stocks. This is the opening price of the stocks on first day of the date range given in the project (opening price is the same as the closing price on the previous day. So, if the first day is a Monday, you can find the opening price by looking at the closing price on the Friday before). You can find the opening prices for October 18th by getting the closing prices on October 15th. 

iv. The next column should contain the number of stocks purchased (note you can only buy stocks in whole number increments, you can not buy a fraction of a stock). 

v. The next column should contain the total value of the stocks you purchased for each company. So, for example, if you are buying 20 shares of a company at $100 a share, then the total value at purchase time was $2000. Once you have done this for each company, in the cell in this column after the last company, you will compute the total value of all the stocks purchased (this is the sum of the value of the stocks from each company). This is called the portfolio value. This computations must be done using equations in excel. That is, when I select a cell in this column, I should see an equation in the status bar at the top. Failure to do this will result in zero points for this part of the assignment. 

vi. The next column will contain the total purchase cost for each of the transactions. When a stock purchase is made, the company who does the purchasing charings a fixed fee, usually $9.95 per transaction. The purchase stock from one company counts as a transaction. So, for example, if you purschaed 20 shares of a company at $100 a share, then the total transaction cost is 20 *$100 + $9.95 = $2009.95. For each company you buy stocks from, you pay a $9.95 fee. After computing the purchase cost for each company, you will then compute the total purchase cost by summing the purchase price for each company. This will go into the cell immediately after the purchase price for the last company. This computations must be done using equations in excel. That is, when I select a cell in this column, I should see an equation in the status bar at the top. Failure to do this will result in zero points for this part of the assignment. 

vii. When you are purchasing the stocks in the preious step, you must follow the following rules: you must spend as close to $100,000 as possible (a few dollars under or over is fine). Each each stock must have a value of at least $2,500, and no one stock can have a value of more
than $50,000. If you have setup the excel file correctly in the previous steps, you would have to just adjust the number of stocks purchased for each company until your numbers satisfy the given conditions.
viii. The next 10 columns should contain the closing prices for each stock for the two week period indicated at the beginning of the assignment (note that the stock market is closed on holidays, so if there is a holiday during the two week period, then you may only have 9 columns instead of 10 columns). If you miss a day, you can look up the closing prices online.

b. Create worksheets for each day which are linked to first worksheet. You are going to format just one of these worksheets and then use the Move or Copy Sheet command under the Edit menu to create carbon copies.
i. Label the sheet tab for each day with the date for each business day, MON-FRI for the period of this project. The first column will contain the name of each you have purchased.
ii. Each sheet will include the opening and closing price for all the stocks that day. You will link to the first sheet to get these. To link the value to another sheet, start by typing "=" (without the quotation marks) into the cell, then select the appropriate cell on the first page using the mouse. You can then drag down to have the other values automatically filled in. WARNING: The first sheet has closing prices, you will use the previous close for the new open, this is potentially confusing ☺but you can use your superior quantitative reasoning skills to figure this out!
iii. On each sheet, the next two columns will contain for each stock price's change from the previous day in both dollar amount and percent.
iv. On each sheet, the next two columns will contain for each stock price's change from the original purchase price in both dollar amount and percent. You will get the original purchase price by linking the cell to the first sheet.
v. On each sheet, create a column which computes the total $ value of each of your stocks. This entails the closing price that day and the number of shares. SUM this column to get the daily portfolio $ value. The portfolio value should be placed in the cell immediately after the last total stock value.
vi. On each sheet, the next two columns will contain the change from the previous day's total for each stock in both dollar amount and percent.
vii. On each sheet, the next two columns will contain the change from the original total cost for each stock in both dollar amount and percent. Again, you should be linking to the original sheet to get the original cost.
viii. On each sheet, compute the total percentage gain for your entire portfolio value relative to the original cost. Make this cell nicely formatted and highly visible! Again, link to the original sheet.
ix. On each daily worksheet create a nicely labeled pie chart showing all of the stocks you own, and the percentage for each of the total portfolio value.

c. On a separate sheet, labeled Line Graphs, insert a line graph for each stock showing the closing price of each stock for every day in this period. Each stock should have a separate line graph (do not put them all in the same line graph), and each line graph should have appropriate labels (stock name, labeled x and y axes).

d. Create cells on the first sheet that will tell you the highest price of each stock for this period and the highest value of your portfolio (the portfolio is the value of all your stocks combined, for example when you first bought the stocks, you portfolio value should have been around $100,000). You can figure out the highest portfolio value by looking at the portfolio values for each day, and taking the one that is largest. This can be done using the Maximum function, or it could be one manually. Make these cells highly visible and clearly labeled.