Indzara

Two Stocks Comparison Excel Template – Live Data

In this article, we will be discussing the Two Stocks Comparison Template that you can use to compare any two stocks.

The template can assist in

  • Comparing any 2 stocks on current price data as well as several attributes
  • Viewing Price history of the two stocks
  • Customizing the price history by time period (5 days, MTD, 1 Month, 3 Months, 6 Months, YTD, 1 Year, 5 Years) as well as time interval (Daily, Weekly, Monthly)
  • Viewing the price trends of two stocks on same axis or different axis.
Compare Microsoft vs Alphabet Inc. Stocks

Download

Video Demo

How to compare two stocks in Excel?

The template automatically pulls data from the Stocks Data type service and displays for analysis.

The user has the following input controls to customize the chart.

Input Controls

Two stocks to compare

Just type in a ticker symbol stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select-the-stock-from-the-list
Select-the-stock-from-the-list

You can select the stock you are interested in.

Period

The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date (MTD), 1 Month, 3 Months, 6 Months, Year Till Date (YTD), 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Customize Attributes

There are two attributes that can be compared side by side for the two stocks. You can modify them by choosing from the drop down as shown below.

Two Stocks Comparison - Customize Attributes
Two Stocks Comparison – Customize Attributes

Choose Axis

On the top right above the chart, there is an option to choose whether you would like the two price trends to be plotted on the same axis or different.

Same Axis or Different Axis
Same Axis or Different Axis

When you choose Same axis, if the two stocks price magnitudes are very different, the lower magnitude stock trend will be hard to read. Below is an example between MSFT and GOOG.

Two Stocks Comparison - Microsoft vs Alphabet- Same Axis
Two Stocks Comparison – Microsoft vs Alphabet- Same Axis

When we choose Different Axis, now the trends are easier to read.

Two Stocks Comparison - Microsoft vs Alphabet - Different Axis
Two Stocks Comparison – Microsoft vs Alphabet – Different Axis

How to refresh data?

If you have left Excel idle for a few minutes and would like to refresh the data, click on Refresh All in the Data ribbon.

How to refresh Stocks data
How to refresh Stocks data

Timezone

Last Trade Time

On the top right you can see the maximum of the last trade time of these stocks.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset

Sample Analysis

You can type in various combinations of the above inputs to analyze the stocks according to your needs. Here are some samples of what can be done using this template.

Ford vs Ferrari

Ford vs Ferrari – Stocks Comparison

Microsoft vs Apple

Microsoft vs Apple – Stocks Comparison

Mattel vs Hasbro

Hasbro vs Mattel- Stocks Comparison

Mastercard vs Visa

Mastercard vs Visa – Stocks Comparison

Facebook vs Twitter

Facebook vs Twitter – Stocks Comparison

Southwest Airlines vs American Airlines

Southwest Airlines vs American Airlines – Stocks Comparison

The template will work for non-US stocks as well. Here are some examples of Indian stocks.

Tata Communications vs Reliance Communications

Tata Communications vs Reliance Communications – Stocks Comparison

Wipro vs Infosys

Wipro vs Infosys – Stocks Comparison

Requirements

To pull live Stock data from the services you would need Microsoft 365 Subscription Plans for PC and Web

All the templates in this series can be downloaded from indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

6 Comments

  • Hello Indzara team,

    Thank you for the two shares comparison template.
    I recreated these and discovered some hopefully solvable problems.

    The comparison of known large stocks works excellently, but with smaller values it becomes problematic.
    For example:
    AbraSilver (XTSX: ABRA) vs. Aya Gold & Silver (XTSE: AYA), comparison 5 years, daily
    Although both start on the same day, the number of determined prices of the two shares differs from each other. One share traded more than the other during the same period. Thus, the chart comparison at the end is faulty by exactly this trading day difference.
    As a consequence, the evaluation of different/same axle is unfortunately also wrong.

    1. Question: Is there a way to adjust the number of days traded so that it is always the same for both values?
    2. Question: After the adjustment, can the missing trading day prices be compensated with # NV or the previous day’s price?
    3. Question: The chart shows missing trading days with a price of 0.0. The chart falls to zero. Even with # N/A, the chart drops to zero, although the chart defines the requirement to display the # N/A value not as a zero value, but as a line. Is there a solution here too?

    It would be great if you could solve the problem!

    Many greetings from Germany
    Volker Schnabel

    Reply
    • Thank you for highlighting the issue.

      We have fixed the same and uploaded a new version of the template. Currently, we will not take data to compare if both symbols have active trading days.

      Best wishes.

      Reply
      • Hello Indzara team,

        Thank you for quickly resolving the problem.
        It works perfectly now.

        Many greetings from Germany
        Volker Schnabel

        Reply
    • Thank you for showing interest in our template.

      Sheet is locked to prevent accidental damage to formulas. You can unprotect the sheet using the password indzara.

      Best wishes.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *