MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Stock & Exchange Rate History in Excel


June 29, 2020 - by Bill Jelen

A new STOCKHISTORY function debuted in the beta channel of Excel for Office 365 last week. The function is able to pull historical stock prices and exchange rates at a daily, weekly or monthly interval.

STOCKHISTORY is an example of one of the new Dynamic Array formulas. A single formula will spill into adajent cells. For an overview of Dynamic Arrays, read Excel 2019: Dynamic Arrays Can Spill article.


Caution

STOCKHISTORY will only be available in subscription versions of Office 365. If you own Excel 2013, Excel 2016, Excel 2019, (or in the future, Excel 2022) you will not have access to the STOCKHISTORY service.

Here is an example of a STOCKHISTORY formula returning monthly closing prices for the Coca-Cola stock closing price: =STOCKHISTORY(A1,"1/1/1975",TODAY(),2,1)

Monthly closing prices with STOCKHISTORY
Monthly closing prices with STOCKHISTORY

You can use the arguments in the STOCKHISTORY function to provide flexibility. It is worth learning the various options. The arguments are presented in order:

  • Stock: This could be text in quotes such as “IBM” or pointing to a cell such as A1.
  • Start_Date: wrap the start date in quotes or point to a cell containing a date. As you will see in the example above, if you specify a start date from before the stock was trading, the results will start at the oldest date available.
  • End_Date: if you leave this off, it defaults to today’s date. Note that the function does not provide intra-day results so in most cases, the latest date will be yesterday’s close. STOCKHISTORY does not show today’s closing price until many hours after the market closes.
  • Interval: This provides a lot of flexibility. Type 0 for daily, 1 for weekly, or 2 for monthly
  • Headers: Enter 0 to return the data without column headers. Use 1 for a single row of headers as shown above. Use 2 to get headers as shown below.

    STOCKHISTORY function arguments
    STOCKHISTORY function arguments

  • After the first five arguments, you can specify up to 6 additional arguments to specify what fields you would like to return and the order. If you leave these out, you will get date and closing price.

    STOCKHISTORY function arguments
    STOCKHISTORY function arguments

You can specify the values in any order. For example, one of the very old built-in stock charts in Excel requires Date, Volume, Open, High, Low, Close. In this case, the 6th through 10th arguments would be 0,5,2,3,4,1.

STOCKHISTORY function arguments
STOCKHISTORY function arguments

STOCKHISTORY supports individual stocks, Currency Pairs, ETFs, Index Funds, Mutual Funds, and Bonds.

For more details, watch our YouTube video.

One feature of STOCKHISTORY is that it is automatically providing number formatting on-the-fly. This is a new feature in Excel. Read more about FNV here: https://techcommunity.microsoft.com/t5/excel-blog/announcing-stockhistory/ba-p/1404338.

Because the feature is in beta right now, you have to opt in to the Office Insiders program as described here.

Title Photo: Jason Briscoe on Unsplash


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.