Historical Stock Price Analysis of 10 Popular Companies

Historical Stock Price Analysis: Unveiling Market Dynamics of 10 Popular Companies (2011-2021)

1. Executive Summary

This report presents a comprehensive analysis of historical stock price data for ten influential technology and consumer companies (Amazon, Apple, Facebook, Google, Microsoft, Netflix, Tesla, Uber, Walmart, Zoom) from 2011 to 2021. The analysis, conducted using Power BI, reveals significant upward price trends, high trading volumes (particularly for Tesla and Apple), and a positive correlation between price movements and volume. While overall market activity surged, especially from 2018-2021, quarterly patterns showed consistent activity. Key insights highlight the growth potential and volatility within these sectors, providing valuable information for investment strategies and market understanding.

2. Introduction

This project aims to transform complex raw historical stock price data into clear, interactive visualizations using Power BI. The focus is on 10 highly influential companies across technology and consumer sectors, analyzing their performance and trading dynamics from 2011 to 2021. The dashboard serves as a robust tool for understanding past market behavior, informing future investment strategies, and providing a clear view of overall market sentiment.

3. Problem Statement / Objective

The primary objective was to develop an interactive dashboard capable of answering critical questions regarding the historical performance of these major companies:

• What are the long-term price trends and volatility of these stocks?
• Which companies have experienced the most significant growth or decline?
• How has trading volume evolved, and what does it indicate about market interest?
• What were the all-time high (ATH) and all-time low (ATL) prices, and how do they compare?
• Are there any discernible quarterly patterns in trading activity and average prices?

4. Data Source

The analysis utilized comprehensive daily historical stock prices and trading volumes.

• Source: Data collected from reliable financial data providers.
• Scope: Covers the period from 2011 to 2021, including opening price, closing price, high, low, and volume for each of the 10 selected companies.
• Pre-processing: Data underwent cleaning and transformation to handle missing values, ensure consistency, meticulously format dates, and account for stock splits for accurate historical price comparisons.

5. Methodology & Tools

Tools Used:

• Microsoft Excel (for initial data handling/preparation)
• Power BI (for data modeling, DAX calculations, and dashboard visualization)

Key Steps and Features Utilized:

1. Data Collection & Aggregation: Historical daily stock data for all 10 companies was aggregated to ensure consistency across different timeframes and data points.
2. Data Cleaning & Transformation:
   • Handled any missing values or inconsistencies to ensure data completeness and accuracy.
   • Dates were meticulously formatted, and stock splits were accounted for to enable accurate price comparisons over time.
3. Data Modeling & Feature Engineering:
   • A comprehensive data model was built in Power BI, establishing clear relationships between company data and time dimensions.
   • Crucial metrics such as Average Opening Price by Year, Total Volume Traded, All-Time High (ATH) Price, and All-Time Low (ATL) Price were calculated as new features.
4. DAX Calculations: Powerful Data Analysis Expressions (DAX) were developed to create key metrics for the analysis:
   • Calculated Overall Average Stock Price, All-Time High (ATH) Price, and All-Time Low (ATL) Price.
   • Derived Total Volume Traded and individual company volumes.
   • Implemented measures for year-over-year and quarterly price and volume trends.
5. Interactive Dashboard Design: I designed an intuitive and visually rich Power BI dashboard, featuring:
   • Clear KPI cards to highlight overall average price, ATH, ATL, and total volume.
   • Line charts to visually track opening price trends and average stock prices over the years for each company.
   • Area charts to illustrate average stock price fluctuations over time.
   • Bar charts for total volume traded by year and company, and quarterly performance breakdowns.
   • Scatter plots to analyze the relationship between trading volume and price.
   • Interactive filters by company and year range, allowing users to drill down into specific periods or stock performances.

6. Key Findings & Insights

Overall Market Snapshot (2011-2021):

• Average Stock Price: Across all analyzed companies and the period, the average stock price was approximately $440.65.
• All-Time High (ATH) Price: The highest recorded price among these stocks was a remarkable $3,773.08, demonstrating significant peak valuations.
• All-Time Low (ATL) Price: Conversely, the lowest recorded price was $4.22, highlighting the substantial growth potential and inherent volatility within this sector.
• Total Volume Traded: A massive 463 Billion in total volume was traded across all companies, indicating high liquidity and sustained market activity.

Company Performance Summary:

Company Performance and Trends:

• Dynamic Price Movements: The dashboard clearly shows significant upward trends for many of these tech and consumer giants, particularly in the later years of the analysis period (2018-2021). Companies like Amazon and Google demonstrated substantial opening price appreciation over the years, reflecting their market dominance and innovation.
• Leading by Trading Volume: Tesla dominated in terms of total trading volume, with approximately 243.2 Billion shares traded, far surpassing other companies. Apple followed with around 89 Billion, underscoring their high investor interest and liquidity. This high volume often correlates with significant news, product launches, or market sentiment shifts.
• Diverse Performance Landscape: While some companies showed consistent growth, others like Netflix and Zoom also experienced periods of significant gains and fluctuations, reflecting the dynamic and sometimes unpredictable nature of the market, driven by factors such as subscriber growth, competition, and global events.

Volume and Quarterly Analysis:

• Surging Volume: Total trading volume generally increased year over year, with a notable acceleration from 2018 to 2021. This surge indicates heightened market activity and increased investor engagement in these companies, possibly driven by the accessibility of trading platforms and increased retail investor participation.
• Quarterly Consistency: Analysis of volume and average price by quarter revealed relatively consistent trading activity throughout the year. Q4 consistently showed the highest average price (around $481.13), potentially due to year-end financial reporting and holiday season consumer spending. Q1 led in volume (around 125 Billion shares), suggesting strong early-year investor activity.
• Price-Volume Relationship: The analysis consistently suggests that periods of significant price movements often coincided with higher trading volumes, a classic indicator of strong market conviction. This reinforces the idea that large price changes are often backed by substantial trading interest.

7. Project Impact & Value Proposition

This project demonstrates my capability to conduct comprehensive financial data analysis and present complex information in an accessible and interactive manner. This Power BI dashboard provides invaluable insights for:

• Investment Research: Aiding individual and institutional investors in understanding historical trends, identifying patterns, and informing potential future movements of popular stocks.
• Market Understanding: Offering a clear, data-driven view of overall market sentiment and the performance trajectories of key industry players within the tech and consumer sectors.
• Portfolio Management: Empowering individuals or financial analysts to effectively track, compare, and evaluate the performance of companies relevant to their investment portfolios.
• Educational Tool: Serving as a practical and engaging example for students, aspiring data analysts, or anyone interested in understanding real-world stock market data visualization and analysis.