Pull Historical Stock Price Data in Excel
Get Stock Price History Directly From Excel!
Stock Prices used to be a hassle to pull into Excel. I can remember having to work with custom VBA code and pulling Yahoo! Finance data from the internet hoping nothing would break. Eventually, Yahoo! was bought by Verizon and they started to charge for importing their data which broke many spreadsheet solutions Excel users had built over the years.
Luckily in 2019, Microsoft Excel actually built a new feature called Data Types. Data Types allow Excel users to pull data from databases Microsoft subscribes to directly into Excel and call attributes of the data (think City, City Population, City Crime Rate, etc…).
One data source Microsoft included in Data Types was stock information. This allowed Excel users to pull current stock prices directly into the spreadsheet in real-time! While it was great to be able to get current price information, a lot of users still longed for the ability to pull in historical stock pricing so they could look at trends and perform more technical analysis to help shape their decision-making.
With the release of Dynamic Array functions (functions that can output ranges of answers vs a single result) in early 2020, Pandora’s Box was opened for all sorts of new functionality within Excel.
One of the new functionalities that I had been personally lobbying Microsoft for years was the ability to pull historical stock price data directly inside Excel (without having to query data from a website). For those who are subscribed to Microsoft 365, this is now a reality! The STOCKHISTORY function was released to Excel in late 2020.
Take a look at what you can now quickly retrieve from a single formula within Excel:
Sample file download of this is available toward the end of the article.
Can you believe all that functionality is available with just one formula?! Just look below, there is a single formula sitting in Cell F4. Because it is a Dynamic Array function, the amount of data that gets output is variable based on the date range and other variables selected within the STOCKHISTORY function. Continue reading on to learn about the inputs of this function and see a few example formulas.
Even though this function is called “Stock” – History, this Excel function can also pull foreign exchange rates and a handful of cryptocurrency prices!
Function Inputs For STOCKHISTORY
There are two required inputs and a handful of option inputs available in the STOCKHISTORY function.
STOCK – Ticker Symbol of the stock you want the pricing for
- Must be the ticker symbol in quotes (eg “MSFT”) or a cell reference to a stock data type
START DATE – The initial date you want pricing for
- Note that if the Interval input does not = 0 (daily), the first data point may be earlier than the Start Date provided (it will be the first date of the period requested).
- END DATE (Optional) – When you want the history to end (defaults to Start Date value)
INTERVAL (Optional) – How often between your start/end date you wish to output a price
- 0 = Every trading day (default)
- 1 = Weekly
- 2 = Monthly
HEADERS (Optional)
- 0 = No headers
- 1 = Show Headers (default)
- 2 = Show instrument identifier and header
PROPERTIES 0 – 5 (Optional) – Additional metric columns you can pull historical data for
- 0 = Date (default)
- 1 = Close Price (default)
- 2 = Open Price
- 3 = Highest Price
- 4 = Lowest Price
- 5 = Volume
5 STOCKHISTORY Formula Examples
There are a variety of different ways you can use the STOCKHISTORY function to pull stock price data into your spreadsheet. Here is a list of examples we will cover in this section:
- Daily Prices For the Current Year (YTD)
- Weekly Prices For the Past 52 Weeks
- Sort Dates in Descending Order
- Current USD To CAD Exchange Rate
- Dates Going Across Headers (Past 3 Months)
1. Daily Prices For the Current Year (YTD)
The below STOCKHISTORY formula example will display stock data for Microsoft between January 1st and today’s date of the current year. You can also view this as a dynamic year-to-date (YTD) pull of the historical stock price data.
STOCKHISTORY Formula:
=STOCKHISTORY("MSFT",DATE(YEAR(TODAY()),1,1),TODAY())Excel Output Example:
2. Weekly Prices For Past 52 Weeks
The below STOCKHISTORY formula example will show you how to pull weekly prices for the past 52 weeks. You’ll notice in this example that the Interval input is set to Weekly (=1) to override the Daily default setting.
STOCKHISTORY Formula:
=STOCKHISTORY("MSFT",TODAY()-(7*51),TODAY(),1)Excel Output Example:
3. Sort Dates In Descending Order
The STOCKHISTORY function outputs its data in chronological (Ascending) order. This means the first row will begin with the start date and the last row will be the end date.
What if you want to switch this display your data in Descending order? To do this, you will need to incorporate an additional function called SORT.
STOCKHISTORY Formula:
=SORT(STOCKHISTORY("MSFT","1/1/2022","4/29/2022"),1,-1)Excel Output Example:
4. Latest USD To CAD Exchange Rate
The below STOCKHISTORY formula examples show how you can pull the latest foreign exchange rates in Microsoft Excel. Since FX rates are not published every day of the month, you will want to pull a block of days with STOCKHISTORY and whittle it down to the most current rate available.
Note: I am providing two solutions for this as the TAKE function in the first solution may not be available to you depending on your Excel version.
Solution #1 Logic Used:
- STOCKHISTORY Function – Pull any exchange rates that are available from the last 7 days
- SORT Function – Instead of pricing going in ascending order, utilize the SORT function to change the order to descending
- TAKE Function – Only keep the first 2 rows and first 2 columns of the spill range produced
STOCKHISTORY Formula:
=TAKE(SORT(STOCKHISTORY("USD:CAD",TODAY()-7,TODAY()),1,-1),2,2)Excel Output Example:
Solution #2 Logic Used:
- STOCKHISTORY Function – Pull any exchange rates that are available from the last 7 days
- SORT Function – Instead of pricing going in ascending order, utilize the SORT function to change the order to descending
- INDEX Function – Return the value at the intersection of the second row and second column
STOCKHISTORY Formula:
=INDEX(SORT(STOCKHISTORY("USD:CAD",TODAY()-7,TODAY()),1,-1),2,2)Excel Output Example:
5. Dates Going Across Headers (Past 3 Months)
The below STOCKHISTORY formula example shows you how you can pivot the dates from row labels to column labels.
Solution Logic Used:
- STOCKHISTORY Function – Pull the past 3 months worth of stock price and summarize by a monthly interval
- TRANSPOSE Function – Flips the rows and columns
STOCKHISTORY Formula:
=TRANSPOSE(STOCKHISTORY("MSFT",EDATE(TODAY(),-2),TODAY(),2))Excel Output Example:
Download STOCKHISTORY Function Example File
If you would like a copy of all the examples used throughout this article, feel free to use the download link below to get yourself a copy of the Excel file.
I Hope This Helped!
Hopefully, I was able to explain how you can use the STOCKHISTORY() Function to pull historical stock prices directly into your spreadsheet. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.