The stock market is a prominent topic in recent public discussion. Rather than focus on the market itself, this post examines methods for acquiring stock data, reformatting it for use, generating various calculations and visualizations to facilitate analyses, and yielding insights. I will share my approach and point out some useful metrics and observations along the way, focusing on the seven stocks that make up the S&P 500’s technology component (referred to commonly as the Magnificent Seven or Mag7).
My tools are Python and Excel. The overall workflow is illustrated below.
With the help of some Python scripting and Yahoo Finance Web service, I am able to get historical data for the stocks for free. Using Python, specifically its pandas library, and Excel, I can do additional calculations and generate basic visualizations both in Python (using matplotlib and scipy libraries) and Excel (part of Office 365). There are 3 different scripts in use here although they could be a single script doing the necessary work, but I kept them separate because I had already written them at different times and there was no real need to merge them into one when I can just reuse them. If you’re not interested in the Python code implementation details, click here skip ahead to the visualizations; otherwise, read on.
First, let’s talk about getting the data. yfinance is a handy Python library (it needs to be explicitly installed with: pip install yfinance) that’s free to use for general purposes. Its method download() allows specifying the range of time to get the stock information for. The returned data is stored in memory in a pandas dataframe. For example, if we get TSLA (stock symbol for Tesla) data in a range, the dataframe will look something like this:Date Close High Low
TSLA TSLA TSLA
1/2/2020 28.68400002 28.71333313 28.11400032
1/3/2020 29.5340004 30.26666641 29.12800026
1/6/2020 30.10266685 30.10400009 29.33333206
…
This is all well and good except there’s an extra level added between the column header row and data rows that states the stock symbol. That needs to be removed by flattening this multi-level column headers, remove that extra row keeping only the top-level column names before saving as a CSV file so subsequent processes can parse them correctly. This is something to be aware of. I, for one, found this out after I inspected that data in order to see why my other parts of the processes were unable to recognize the CSV format correctly.
Once that’s done, I save the filtered dataframe retaining only these columns: ‘Date’, ‘Close’, ‘High’, ‘Low’. For this exercise, I only need the Date and Close (which contains the daily closing price) but I kept the High and Low column values in the CSV in case I want to do other analyses. Once the data is reformatted and filtered, they are ready to be saved in a CSV file. One CSV file per stock data for now is fine. I chose to download and save past 15 years of data to-date, meaning until end of March 2025 for this exercise. And I’ll keep the range the same for the rest of the Mag7 stocks. The TSLA CSV file content looks like this at this point:
Next, another Python script takes over to read this CSV file, and calculate the Appreciation Rate for both the entire dataset (remember, I saved 15 years worth of data in the CSV file), and past 5 years from the same dataset. Doing this for 15 and 5 years provides additional insights to overall stock performance long-term and medium to short team trends. It’s important to recognize that the data is for each trading day (excluding weekends and holidays), and I’m not interested in daily appreciation rates, rather over different ranges of years. That means, I need to do several things: 1) From the latest row (i.e. last row), determine the row 4 years prior (for current year and previous 4 years, yielding total 5 years of range). 2) Extract the month from the Date column value for each row, then group by month to get the last price of each month over the years. 3) Extract the very first closing price from first row and very last closing price from the last row to get the starting price and ending price to calculate appreciation rate for the full dataset of 15 years 4) Create another dataframe that only contains the past 5 years of data and extract the very first closing price from its first row and very last closing price from its last row to get the starting price and ending price to calculate appreciation rate for just the 5 years. These are trickiest and most-error prone operations/code in my view as we need to very careful in computing the starting and ending rows for both 15 and 5 year ranges which are arranged per day and we need to use months and years for appreciation rate (and later as we’ll see for year-over-year growth) calculations.
Calculating the actual Appreciation Rate is simple, now that we have two different dataframes (one containing 15 years worth of prices; another containing 5 years’ worth). The formula is:
(end_price - start_price) / start_price * 100 — This yields the rate in percentage.
Next, the same script plots a line chart showing the 5 year trend.
The other calculation I need is year-over-year (YoY) growth rate of the stock based on the same CSV dataset. And I only want the most recent 5 years’ YoY, not for the entire 15 years. Once again, I parse the date column and extract the month and year from each row, then calculate the starting date 5 years in the past, and the ending date remains at the latest date recorded in the CSV file. YoY growth rate also needs to be per year, so this required setting up 2 dataframes…one containing starting year’s data, another containing the following year’s data, then merging them into a single dataframe on ‘Date’ column where the starting year’s date is renamed to ‘Date_PrevYear’ and starting year’s closing price is renamed to ‘Close_PrevYear’. The current year’s column names remain as ‘Date’, ‘Close’. After merging, the Data we can use the YoY growth rate formula, which is:
((Current Year Price - Previous Year Price)/Previous Year Price) * 100 — This yields the rate in percentage.
Remember, I’m interested only the past 5 years, I need to filter the dataframe for that range by getting the most recent year with: latest_year = df["Date"].dt.year.max() and deducting 4 from it and saving the resulting range in a new dataframe with this line: df_recent = df[df["Date"].dt.year >= latest_year - 4] and get the last available date in each year (BEWARE: we can’t simply use a hard-coded date because each month can have different numbers of days and the dates recorded are not necessarily calendar days, they’re actual trading dates so they’re not guaranteed to be the last calendar day of the month!) with: df_yearly = df_recent.groupby("Year").last()
A sample printout of the YoY growth rate in past 5 years for AMZN (Amazon) may look like this:Date YoY_Growth
2021-12-31 2.38%
2022-12-30 -50.19%
2023-12-29 80.49%
2024-12-27 45.92%
2025-03-28 6.84%
The hardest parts of the process are done! Next, we can start creating the visualizations either in Python or in Excel or in both.
Below are the final visualizations and analyses for all Mag7 stocks based on the above processes using real stock data.
YoY Growth Rate: Past 5 years
Note: that for 2025, complete data was available up to only Q1 at the time of writing. For other years, full 12 months’ data were used in calculation of YoY growth rate.
Analysis
Every Mag7 stock tanked in 2022 (compared to 2021)! This is due to several factors: Fed raised interest rates multiple times, supply chain issues, Russia-Ukraine war and tensions with China, and possibly stock valuation reset (sell-off by investors).
Starting early 2023 however, tech stocks began an uptrend (see the factors in the 5-year trend analysis above).
So far (Q1) 2025 has been a lukewarm year for the Mag7 stocks so far. This can be due to several factors: 1) The AI-driven rally has slowed. 2) Tariffs, inflation, and geopolitical risk factors 3) Investors are moving away from tech and into consumer staples and financials, which have shown stronger performance so far 4) Slower cloud growth and concerns over AI monetization regarding Microsoft accounts for MSFT contraction in YoY growth.
Some experts predict that unless AI or cloud computing sees another breakthrough, tech stocks may continue to trade sideways (neither bullish, nor bearish…no clear direction).
Stock Appreciation Rate: Past 5 Years
Stock Appreciation Rate: Past 15 Years
Analysis
Despite the tumultous historical and especially recent stock prices for Tesla, TSLA remains in the top 2 of Mag7 stocks in terms of appreciation rate in both 15 years period and 5 years period. An eye-watering 45,987% appreciation rate over the past 15 years in Nvidia stock keeps it a clear leader in that category (1,540% stock price growth in past 5 years).
Each Stock’s Past 5 Years Trend
Analysis
We notice that tech stocks took a hit near the late part of 2022 and generally started uptrend starting early 2023.
They faced a rough patch largely due to rising interest rates, high inflation, and economic uncertainty. The Federal Reserve’s aggressive rate hikes made borrowing more expensive, which hit growth-focused tech companies particularly hard. Additionally, global events like the Russia-Ukraine war and supply chain disruptions added to market instability.
Starting early 2023 however, tech stocks began an uptrend, driven by cooling inflation, optimism around AI, and expectations that the Fed would slow rate hikes. The hype around artificial intelligence, particularly companies like Nvidia, helped fuel a rally in the sector. Investors also regained confidence as economic conditions stabilized and demand for cloud computing, semiconductors, and AI-related technologies surged.
In the early 2024 onward, we see an upswing across all tech stocks except Tesla. This may be explained by the increasing competition from other automakers, particularly in China and Europe, put pressure on Tesla’s market share.
NOTE: I have placed light purple areas (shape: rounded rect, then grouped it with its chart for each) to highlight those periods.
To help me continue to produce and share more content that’s informative, and ad-free, please consider making a one-time or a recurring small donation by clicking here (securely transacted via PayPal™).
