How to Download Historical Stock Data into Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

You’ll need historical stock data from companies all over the world to perform financial stock analysis. With the blessings of Excel, you can easily download or extract the data for your preferred stock values. In this tutorial, we will show you how to download historical stock data into Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


How to Download Historical Stock Data into Excel: 7 Steps

We’ve included a sample data set in the image below, which includes the company names as well as their stock names. We’ve got a start date of about three months ago and an end date of today. Between the intervals, we will download the historical stock data of the three companies on a monthly basis. We’ll make sparklines with the closing values of the stock prices of the three companies. To do so, we’ll use Excel’s STOCKHISTORY function.

Steps to Download Historical Stock Data into Excel

 Notes:  The STOCK HISTORY function is only available with a Microsoft 365 subscription.

Step 1: Insert Stock Argument for STOCKHISTORY Function

  • Select cell C5 to insert the stock name (MSFT) of the Microsoft Corporation.
=STOCKHISTORY(C5

Steps to Download Historical Stock Data into Excel


Step 2: Insert Start Date and End Date

  • In the start_date argument, select cell B10.
=STOCKHISTORY(C5,B10

Steps to Download Historical Stock Data into Excel

  • For the end_date argument, select cell C10.
=STOCKHISTORY(C5,B10,C10

Steps to Download Historical Stock Data into Excel


Step 3: Select Interval to Show Historical Data

  • The interval argument returns how you want to get the historical data.
  • 0 = daily interval.
  • 1 = weekly interval.
  • 2 = monthly interval.
  • By default, it is set to zero (0). In our example, we will type 2 as we want to get the result in monthly
=STOCKHISTORY(C5,B10,C10,2

Steps to Download Historical Stock Data into Excel


Step 4: Apply Headers to Classify the Columns

  • To show headers in the result data table, define the header argument.
  • 0 = no headers.
  • 1 = show headers.
  • 2 = Show instrument identifier and headers.
  • In our data set, we will select 1 to show headers.
=STOCKHISTORY(C5,B10,C10,2,1

Steps to Download Historical Stock Data into Excel


Step 5: Enter Properties to Show in the Table

  • The Properties argument defines what you want to see in the column headers. Generally, there are 6 properties [properties1-properties6] you can apply to.
  • [properties1] = Date.
  • [properties2] = Close (the closing stock price at the end of the day).
  • [properties3] = Open (the opening stock price at the start of the day).
  • [properties4] = High (the highest stock rate on that day).
  • [properties5] = Low (lowest stock rate on that day).
  • [properties6] = Volume ( Numbers of shareholders).
  • We will enter the properties argument with the following formula:
=STOCKHISTORY(C5,B10,C10,2,1,0,1,2,3,4,5)

Steps to Download Historical Stock Data into Excel

  • As a result, as shown in the image below, you will receive historical stock data for Microsoft Corporation.

Steps to Download Historical Stock Data into Excel


Step 6: Get Historical Stock Data for Multiple Company

  • In cell B12, type the following formula with the start_date ($B$10) and end_date ($C$10) in the absolute form.
=STOCKHISTORY(C5,$B$10,$C$10,2,1,0,1,2,3,4,5)

Steps to Download Historical Stock Data into Excel

  • In cell E5, transpose the value of the closing price (C13:C15) with the following formula of the TRANSPOSE function.
=TRANSPOSE(STOCKHISTORY(C5,$B$10,$C$10,2,0,1))

Steps to Download Historical Stock Data into Excel

  • Therefore, you will get a transposed value of the range C13:C15.

Steps to Download Historical Stock Data into Excel

  • Use the AutoFill Tool to autofill the stock closing values of the two other companies ( Tesla and Amazon). Thus, cell E6 represents the stock closing value of Tesla on the date of 4/1/2022.

Steps to Download Historical Stock Data into Excel

  • In cell E9, to transpose the closing values with the dates, type the following formula with the TRANSPOSE function.
=(TRANSPOSE(STOCKHISTORY(C5,$B$10,$C$10,2,0)))

Steps to Download Historical Stock Data into Excel

  • Consequently, it will appear with the closing stock price along with their dates.

Steps to Download Historical Stock Data into Excel

  • To get only the dates, apply the previous formula nested with the INDEX function.
  • Type 1 for the row_num (row number) argument.
=INDEX((TRANSPOSE(STOCKHISTORY(C5,$B$10,$C$10,2,0))),1)

Steps to Download Historical Stock Data into Excel

  • As a result, only the dates will appear in the row, as it was the first row.

Sample Data

  • Press  Ctrl  +  X  to cut the date values.

Sample Data

  • Then, press  Ctrl + V  to paste in cell E4.

Sample Data


Step 7: Create Sparklines for Historical Stock Data

  • Select a cell.
  • Click on the Insert tab.

Sample Data

  • From the Sparklines group, select the Line option.

Sample Data

  • In the Data Range box, select the range E5:G5 for the Microsoft Corporation.
  • Finally, click OK.

Sample Data

  • As a result, you will be able to create your first sparkline for Microsoft Corporation. This displays the stock price’s ups and downs at the intervals you specify.

Sample Data

  • Simply drag the AutoFill Tool down to get the rest of the company’s sparklines.

Sample Data

  • Edit with a marker or color as you want to show the sparklines to be represented.

Download Historical Stock Data into Excel


Conclusion

I hope this article has given you a tutorial about how to download historical stock data into Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

Please contact us if you have any questions. Also, feel free to leave comments in the section below.


Related Articles


<< Go Back to Stocks In Excel| Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

2 Comments
  1. This is a great tutorial for downloading historical stock data into Excel. It is easy to follow and provides clear instructions. I would recommend this tutorial to anyone looking to import historical stock data into Excel.

    • Greetings INSTASAVETUBE,
      Countless thanks for your kind words of praise. The Exceldemy team has always been there to assist and has been working to make knowledge accessible to everyone globally. And without a doubt, you are the kind of great people that empower us.

      Best Regards,
      Bhubon Costa
      (Exceldemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo