1. Part 3

INF2080

Project 4: Final Project

 

Part 3: Stocks

 

This is a photograph of a young woman with a laptop.

© Yuri Arcurs/shutterstock

You are an intern at a brokerage firm. You offer to create worksheets and charts. Your boss asks you to analyze the stock for What a Wonderful World Travel by graphing the closing cost of the stock during a ten-day period.

 

You have researched and collected this company’s stock for ten consecutive market days. Using the data provided, create a worksheet showing the dates and prices; and convert the worksheet to a line graph chart. You will also have to make suggestions about when, during that ten-day period, would have been a good time to sell the stock.



Data

 

What a Wonderful World Travel Stock Prices, November 17 to November 30 of the current year

 

Date Open High Low Close
17-Nov 36.95 37.37 36.48 36.95
18-Nov 37.43 37.78 37.10 37.19
19-Nov 37.24 37.41 35.90 36.15
20-Nov 35.99 36.50 35.35 37.45
23-Nov 36.70 37.05 36.10 36.40
24-Nov 37.15 37.64 36.66 38.61
25-Nov 37.83 38.15 37.57 37.81
26-Nov 38.09 38.24 37.50 39.12
27-Nov 37.92 38.19 37.52 37.62
30-Nov 37.90 38.03 37.35 38.50

 

Instructions

 

Step 1: Create a new Excel worksheet.

 

Step 2: Format columns A, B, C, and D widths to 12.0. Choose right alignment.

 

Step 3: Format column A to Dates (choose the 0/00/0000 option under Type).

 

Step 4: Format columns B to E to Numbers with two decimal places, and use the 1000 separator.

 

Step 5: Make the column A heading “Date.” Use bold and underline for the title.

 

Step 6: Make the column B heading “Open.” Use bold and underline for the title.

 

Step 7: Make the column C heading “High.” Use bold and underline for the title.

 

Step 8: Make the column D heading “Low.” Use bold and underline for the title.

 

Step 9: Make the column E heading “Close.” Use bold and underline for the title.

 

Step 10: Enter the data provided in the appropriarte place.

 

Step 11: Create a line with markers chart that reflects the information from the closing information.

 

Step 12: Move the chart to a new worksheet. Name this worksheet “Chart 1.”

 

Step 13: On the Chart1 worksheet, go to the Layout tab and create the following titles:

  • chart title: “What a Wonderful World Travel—Closing Prices”
  • horizontal axis title: “Day”
  • vertical axis title: “Price”

Step 14: Format the chart data and the chart area using colours. Use complementary colours.

 

Step 15: Highlight the chart title and set the font to 16-pt bold. Place the words Closing Prices on a second line.

 

Step 16: Highlight the horizontal and vertical axes titles (Day and Price), and set the font size to 16-pt bold.

 

Step 17: Save your worksheet as “Stocks.”

 

Step 18: Go back to the Stocks tab of the workbook. In column F, create the title “What to Do.” Format the same way as the other titles have been formatted.

 

Step 19: You will now create an IF statement that determines what would have been the best time to sell the stock during those ten days. Use appropriate terms such as sell or keep as your output.

 

Step 20: Save your worksheet again.

 

Step 21: In your worksheet, include a summary statement that identifies what day would have been a good time to sell and explain why.

 

Step 22: Save your Chart 1 worksheet as a web page so anyone using a web browser would be able to open the file.