Excel Exercise

In this assignment, you will use Excel to create a small data file of your chosen stocks.

Please use a blank Excel sheet that you create from scratch for your database. Please list stock information for each of the first ten days of the semester. The schema (structure) you will be using in creating your database of stock information is as follows:

CompanyName(Symbol, Date, Open, High, Low, Close, PivotPoint, Volume)

Where CompanyName is the fullname of your stock company. For example, for the symbol CL, the name of your spreadsheet tab will be ColgatePalmolive. This will be the tab name in Excel.

Symbol is the stock symbol. Enter the symbol in all rows. For this example, CL will appear 10 times.

Date is the date of the stock prices. You will collect and update your spreadsheet with the pricing date for the first day of the semester and include that and the next 9 trading days. For Spring 2020, you will have the data for January 21 - February 3. The stock market does not trade on weekends or holidays. For the example above the dates will be January 21, 22, 23, 24, 27, 28, 29, 30, 31, and Feb 3. Format the date in the American format of mm/dd/yyyy. NOTE: Leading zeroes for the month and day will not display. That is ok.

Open - the price of the stock when the exchange opens. For the New York Stock Exchange and the NASDAQ this is 9:30 AM Eastern time.

High - The high price that the stock trades during the trading day.

Low - The low price that the stock trades during the trading day.

Close - The price of the stock when the exchange closes. For the New York Stock Exchange and the NASDAQ this is 4:00 Eastern time.

PivotPoint - A metric used to help determine when the direction of a stock price is changing either up or down. Usually this is for the previous day's value, but we will use the data for the same day. The formula is (High+Low+Close) / 3.You must create the formula in Excel terms to calculate the value. FORMAT THIS COLUMN TO TWO DECIMAL POINTS.

Volume - The number of shares traded during the trading day.

FORMAT NOTES:

• Column headings must be in the first row.
• The data for the first day of the assignment will be in the second row.
• The date for the last day of the assignment will be in row 11.
• Highlight the Volume column and format the number without the commas separating the sets of numbers. Highlight ->right click ->Format Cells ->Number ->uncheck Use 1000 Separator. NOTE: If you do not change the format you will have difficulties importing the data for Assignment 3.
• You may find the current and historical data for your stock at Finance.Yahoo.Com

Enter the stock symbol and look under "Historical Data." All of the data you need to enter is there.

• Create a Stock Chart of your data.
• Highlight all data under the rows Open, High, Low, Close. If you have formatted your data correctly, these rows will be in this order and also next to each other.
• From the toolbar click Insert -> Stock, Surface or Radar Chart
• Under stock charts click the Open-High-Low-Close type.
• Right click the body of the chart.
• Click "Edit" under Horizontal Category. At the moment this should just be numbers
• The Axis Label box opens
• Highlight the data under the Date column. Click OK twice.
• Move the chart so the upper left corner is in cell J1