BHEL  Stock Performance Analysis in Power BI 

Problem Statement:
Analyze BHEL’s stock price trends and trading behavior over time. Identify patterns in daily price changes, volatility, and trading volume. Develop insights through advanced calculations and visualizations, including moving averages, cumulative returns, gain/loss classification, and forecast future stock prices to support informed investment decisions.

Dataset Description:
The dataset contains historical stock data for BHEL, including daily records of Open, High, Low, Close, Adjusted Close prices, and trading Volume from 2020 onwards. Each row represents one trading day, capturing market movements and trading activity.

Open BHEL Stock Dataset 

🔹 Step 1: Load the Dataset

1. Open Power BI Desktop.

2. Go to Home > Get Data > CSV/Excel.

3. Browse and select your BHEL_Stock dataset.

4. Click Load to bring the data into Power BI.

---

🔹 Step 2: Transform the Data

Click Transform Data to open Power Query Editor.

✅ Key Transformations:

• Change Date to Date type.

• Change Open, High, Low, Close, Adj Close, and Volume to appropriate numeric formats.

• Remove any null or blank rows.

✅ Create new columns (M Query):

1. Daily Change (Close - Open)

= Table.AddColumn(#"Previous Step", "Daily Change", each [Close] - [Open])

2. Volatility (High - Low)

= Table.AddColumn(#"Previous Step", "Volatility", each [High] - [Low])

Click Close & Apply.

---

🔹 Step 3: DAX Measures in Modeling

1. Average Closing Price

Avg_Closing = AVERAGE('Bhel_Stock'[Close])

2. Maximum Volume

Max_Volume = MAX('Bhel_Stock'[Volume])

3. Total Volume

Total_Volume = SUM('Bhel_Stock'[Volume])

4. Daily Percent Change

percent_Change = 
    DIVIDE(SUM(BHEL_Stock[Close]) - SUM(BHEL_Stock[Open]), SUM(BHEL_Stock[Open])) * 100

5. Extract Year

Year = YEAR( MAX('BHEL_Stock'[Date]) )

6. Extract Month

Month = FORMAT( MAX('BHEL_Stock'[Date]), "MMMM")

---

Research Questions with Detailed Steps

---

RQ1: How has BHEL's stock price trended over time?

📊 Visual:

Line Chart

✅ Steps:

1. Go to Report View.

2. Insert a Line Chart.

3. Axis: Date

4. Values: Open, Close, Adj Close

5. Format the chart for:

   • Distinct colors per line

   • Data labels

   • Tooltip showing exact date and prices

6. Add slicers for:

   • Year (Year column)

   • Month (Month column)

---

RQ2: Which months/years had the highest or lowest closing prices?

📊 Visual:

Bar Chart or Matrix

✅ Steps:

1. Insert a Clustered Column Chart.

2. Axis: Month or Year

3. Values: Avg_Closing (DAX)

4. Add sorting: Highest to lowest

5. Use conditional formatting to highlight peak months/years.

---

RQ3: What is the daily volatility in BHEL stock?

📊 Visual:

Line Chart or Table

✅ Steps:

1. Insert a Line Chart.

2. Axis: Date

3. Value: Volatility column (from M Query)

4. Add conditional color gradient for visual flair.

5. Optional: Add tooltip with High and Low values.

---

RQ4: How does trading volume vary across time?

📊 Visual:

Area Chart or Column Chart

✅ Steps:

1. Insert an Area Chart.

2. Axis: Date

3. Value: Volume

4. Add slicer for Year to allow filtering.

5. Tooltip: Show Volume, Date, and Close

---

RQ5: What is the relationship between opening and closing prices?

📊 Visual:

Scatter Chart

✅ Steps:

1. Insert a Scatter Chart.

2. X-axis: Open

3. Y-axis: Close

4. Details: Date

5. Play Axis (Optional): Year or Month for animation.

6. Tooltip: Add Daily Change and Percent_Change

---

RQ6: On which days did the stock price gain or fall significantly?

📊 Visual:

Table with Conditional Formatting or Bar Chart

✅ Steps:

1. Insert a Table visual.

2. Columns: Date, Open, Close, Daily Change, Percent_Change

3. Apply conditional formatting:

   • Green for large gains

   • Red for large losses

4. Add slicers to filter by Year, Month

---

RQ7: What was the average daily percent change per month/year?

📊 Visual:

Bar Chart

✅ Steps:

1. Insert a Bar Chart.

2. Axis: Month (or Year)

3. Values: Create new measure:

   Avg_Daily_Percent = AVERAGE('Bhel_Stock'[Percent_Change])
   

4. Add slicer: Year

5. Sort by highest gain or loss

---

RQ8: Which months/years saw the highest trading volume?

📊 Visual:

Stacked Column Chart

✅ Steps:

1. Insert a Stacked Column Chart.

2. Axis: Month

3. Legend: Year

4. Values: Volume

5. Use data labels to show totals

---

Interactive Dashboard Design

---

✅ Final Layout:

Section	Visuals
Price Trend	Line Chart (Open, Close, Adj Close)
Volatility	Line or Table (High - Low)
Volume Analysis	Area or Column Chart
Performance Table	Table with %Change, Daily Change
Slicers	Year, Month
KPIs	Card Visuals: Max Volume, Avg Close, Total Volume
Comparative Insights	Scatter Chart (Open vs Close)
Monthwise Highlights	Column Chart with sorting

---

 Interactivity Tips:

• Use slicers for Year, Month to filter entire dashboard.

• Use drill-through to move from year → month → day level.

• Add a reset button using bookmarks to revert filters.

• Enable tooltips for charts for richer insights.

---

Export Options:

• Use File > Export > PDF to generate static report.

• Use Publish to Power BI Service to share with colleagues.

• Use Performance Analyzer for optimizing visuals.

---