Assignment -18 | Stock Performance Analysis

Excepted timing : 10 minutes

| Date       | Stock A | Stock B | Stock C |

|------------|---------|---------|---------|

| 2023-01-01 | 100     | 120     | 80      |

| 2023-01-02 | 105     | 118     | 82      |

| 2023-01-03 | 110     | 115     | 85      |



Task :
  1. Create a table to store daily stock prices for Stock A, Stock B, and Stock C.
  2. Calculate the daily percentage change for each stock using a formula ((Current Price - Previous Price) / Previous Price * 100).
  3. Use conditional formatting to highlight days where any stock price increased by more than 5%.
  4. Create a line chart to visualize the daily performance of Stock A, Stock B, and Stock C over the given period.
  5. Add a moving average trendline to the chart to smooth out price fluctuations.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

  1. Create a Table for Daily Stock Prices
    • Set up a table in Excel with columns for Date, Stock A, Stock B, and Stock C.
    • Enter the provided sample data into this table.
  2. Calculate Daily Percentage Change for Each Stock
    • In a new column next to each stock's price data (starting from the second row), use the formula:
    • Replace B2 with the cell reference of the current day's price and B1 with the previous day's price.
    • Apply this formula for each stock and drag it down to calculate percentage change for all days.
  3. Apply Conditional Formatting for Price Increase
    • Select the range of percentage change values for all stocks.
    • Go to the "Home" tab in Excel.
    • Click on "Conditional Formatting" and choose "New Rule."
    • Select "Format cells that contain" -> "Cell Value" -> "greater than" -> enter 5.
    • Choose a formatting style (e.g., fill color) to highlight days where any stock price increased by more than 5%.
  4. Create a Line Chart for Daily Performance
    • Select the Date and all three stock columns (Stock A, Stock B, Stock C) including headers.
    • Go to the "Insert" tab in Excel and click on "Line Chart" under the "Charts" group.
    • Excel will create a line chart visualizing the daily performance of Stock A, Stock B, and Stock C over the given period.
  5. Add a Moving Average Trendline to the Chart
    • Click on the line chart to select it.
    • Right-click on one of the lines (e.g., Stock A) in the chart and choose "Add Trendline."
    • In the "Format Trendline" pane, select "Moving Average" and customize settings like period (number of days) for smoothing out price fluctuations.