Analyze Historical Stock Data of Tesla Stock in Power BI

Problem statement

Analyze historical stock data to extract actionable insights about price behavior, volatility, trading activity, and relationships between price and volume. Students will transform and normalize the data, create analytical measures and visuals, and build an interactive Power BI report using both basic and advanced visuals (decomposition tree, drillthrough, bookmarks, selection pane, gauge, custom tooltips, Smart Narrations).

Dataset (Download Dataset)

Your dataset has columns:

• Date — trading date (DD-MM-YYYY)

• Open — opening price

• High — highest price during day

• Low — lowest price during day

• Close — closing price

• Adj Close — adjusted close for splits/dividends

• Volume — number of shares traded

Working with Tesla Dataset in Power BI: From Upload to Research Questions

1. Uploading the Tesla Dataset

1. Open Power BI Desktop.

2. Click on Home → Get Data → Text/CSV.

3. Browse and select your tesla.csv file.

4. Click Load to import the data.

5. If your dataset is from a different source (e.g., Excel, SQL), choose the respective option under Get Data.

2. Data Transformation in Power Query

Once the data is loaded:

1. Click Transform Data to open the Power Query Editor.

2. Ensure the Date column is in Date data type (if not, change it using the Data Type dropdown).

3. Remove unnecessary columns if they are not required for analysis.

4. Rename columns for clarity (e.g., Adj Close → Adjusted Close Price).

3. Adding Automatic Date-Related Columns

You can make Day Name and Month Name update automatically when new data is added.

3.1 Extract Day of the Week

• Select Date column.

• Go to Add Column → Date → Day → Name of Day.

• Rename the new column to Day.

• Dax:  Day = FORMAT([Date], "dddd")

• 
  

3.2 Extract Month Name

• Select Date column.

• Go to Add Column → Date → Month → Name of Month.

• Rename to Month.

• Dax: Month = FORMAT([Date], "mmmm")

• 
  

3.3 Extract Year

• Select Date column.

• Go to Add Column → Date → Year → Year.

• Rename to Year.

• Dax: Year = YEAR([Date])

• 
  

Note: These are dynamic transformations. When new data is added with future dates, Day, Month, and Year values will be generated automatically during refresh.

4. Adding Daily Price Change

To calculate the daily price change:

Steps:

1. In Power Query, go to Add Column → Custom Column.

2. Enter the formula:

Dax: Daily Price Change = [Close] - [Open]

Daily Price Change in Percentage 
Dax: Daily Price Change % = ([Close] - [Open]) / [Open] * 100

  

Rename the column to Daily Price Change.

Change the Data Type to Decimal Number.

5. Closing & Loading the Data

• Click Close & Apply in Power Query to return to Power BI Desktop.

• Your transformed dataset is now ready for analysis.

6. Possible Research Questions for the Tesla Dataset

Below are all possible research questions you can explore, along with step-by-step approaches.

RQ1: How has Tesla's stock price trend evolved over time?

Steps:

1. Use a Line Chart.

2. X-axis → Date.

3. Y-axis → Close Price.

4. Apply a Date hierarchy to allow Year, Month, and Day drill-down.

RQ2: Which day of the week shows the highest average closing price?

Steps:

1. Use a Bar Chart.

2. X-axis → Day.

3. Y-axis → Average of Close Price.

4. Sort by value to find the highest.

RQ3: Is there a seasonal pattern in Tesla stock prices?

Steps:

1. Use Month column.

2. Create a Line Chart with Month on X-axis and Average Close Price on Y-axis.

3. Check for recurring trends.

RQ4: What is the relationship between opening price and closing price?

Steps:

1. Use a Scatter Chart.

2. X-axis → Open Price.

3. Y-axis → Close Price.

4. Add a trend line to see correlation.

RQ5: How volatile is Tesla's stock (Daily Price Change)?

Steps:

1. Use a Column Chart.

2. X-axis → Date.

3. Y-axis → Daily Price Change.

4. Apply conditional formatting for positive/negative changes.

RQ6: What was the impact of major announcements on Tesla stock prices?

Steps:

1. Identify dates of announcements (e.g., earnings releases).

2. Filter the dataset for ±7 days around these events.

3. Compare average prices before and after.

RQ7: Which month historically yields the highest returns?

Steps:

1. Group data by Month.

2. Calculate Average Daily Price Change.

3. Use a Bar Chart to display results.

RQ8: How do high-volume trading days affect stock prices?

Steps:

1. Create a scatter plot with Volume on X-axis and Daily Price Change on Y-axis.

2. Check for patterns in high-volume days.

RQ9: How has Tesla’s stock performed year-over-year?

Steps:

1. Use Year column.

2. Create a bar chart with Year on X-axis, Average Close Price on Y-axis.

3. Compare across years.

RQ10: Is there a correlation between trading volume and price volatility?

Steps:

1. Use a Scatter Chart.

2. X-axis → Volume.

3. Y-axis → Absolute value of Daily Price Change.

4. Add a correlation line.

Other Features

1. Drillthrough

Question:
You want to analyze Tesla’s stock data by drilling through from a monthly summary report to a detailed daily view. How can you create a drillthrough page that shows detailed price and volume for a selected month?

Steps:

1. Create Drillthrough Page:

   • Add a new page in Power BI and rename it "Monthly Details".

   • In the Visualizations pane, locate the Drillthrough filters section.

   • Drag Month from your dataset into the Drillthrough filters field.

   • Add a Table visual to this page.

   • Include columns such as Date, Open, Close, High, Low, and Volume.

2. Enable Drillthrough:

• Go to your summary report page where you have data aggregated by month.

• Right-click on a month value in your visual (e.g., "January").

• Select Drillthrough → Monthly Details.

2. Gauge

Question:
Create a gauge visual to monitor Tesla’s daily price change compared to a set target.

Steps:

1. Prepare the Measure:

• In Power BI Desktop, go to the Modeling tab → New Measure.

• Write: Dax - Daily Change % = 

  DIVIDE( [Close Price] - [Open Price], [Open Price] ) * 100

2. Add Gauge Visual:

   • Insert a Gauge visual.

   • Drag Daily Change % into the Value field.

   • Set the Minimum as -10 and Maximum as 10.

   • Set the Target value (e.g., 2 for 2% increase).

3. Format:

• Change gauge colors to highlight when values exceed the target.

 3. Tooltip

Question:
Display a tooltip showing Tesla’s opening and closing prices when hovering over a data point in the line chart of daily closing prices.

Steps:

1. Create Tooltip Page:

   • Add a new page and set Page Information → Tooltip to ON.

   • Add a Card visual for Open Price and another for Close Price.

   • Include Date at the top for reference.

2. Enable Tooltip on Chart:

   • Go back to your main line chart (Date vs. Close Price).

   • In Visualizations pane → Tooltip, select your custom tooltip page.

3. Test:

• Hover over any point in the line chart to see the open and close prices.

4. Bookmarks

Question:
Create bookmarks to switch between two Tesla stock performance views — one for the full year and one focusing on the last 3 months.

Steps:

1. Create the First View:

   • Apply a filter for Full Year (e.g., all months selected).

   • Go to View → Bookmarks → Add Bookmark.

   • Name it "Full Year View".

2. Create the Second View:

   • Apply a date filter for the last 3 months.

   • Add another bookmark called "Last 3 Months".

3. Add Buttons for Navigation:

   • Insert two buttons labeled "Full Year" and "Last 3 Months".

   • Assign each button its respective bookmark in the Action property.

4. Test:

   • Click the buttons to toggle between views instantly.