How to Use the New Stock History Function in Excel

For a while, one of the big advantages Google Sheets had over Excel was the ability to pull stock quotes easily. But that’s no longer the case as there is a new function in Excel that allows you to pull in stock price history. Below, I’ll cover how to use the StockHistory function.

How the function works

The function itself is fairly simple and requires just two arguments at a minimum, and that’s the stock ticker and the start date. By default, the function will return the closing prices from the start date until today. For instance, if I want to pull Tesla’s share price since the start of the year, this is what my formula will look like:

=STOCKHISTORY(“TSLA”,”2020-01-01″)

The formula will then generate an array. Here’s a portion of what it looks like:

If you want to pull just the most recent share price, here’s what you can do:

=STOCKHISTORY(“TSLA”,WORKDAY(TODAY(),-1))

Using the WORKDAY formula you can ensure that you’re going back one business day. You may need to adjust this if you’re on a weekend but basically you just need to manipulate the date to make this work. Note that this doesn’t appear to give you the current day’s close. When I ran this on a Friday, the most recent closing price it returned was from Thursday’s close. It’s clear this function’s intended for historical data rather than live or even delayed stock prices.

If you want to specify an end date for your data, you can enter a date in the third argument, right after the start date.

The function gives you many options, including which data points you want to pull in and what intervals you want. You can pull prices on a monthly or weekly basis by selecting either a 0 (daily), 1 (weekly), or 2 (monthly) for the interval argument. Here’s how I’d pull monthly prices for Tesla:

=STOCKHISTORY(“TSLA”,”2020-01-01″,,2)

It’s important to note that these aren’t monthly averages, they’re just the stock prices as of the end of the specified month. Although the date for the first entry suggests January 1 (the markets weren’t open that day), that’s actually the January 31 closing price.

You can choose whether you want to see the headers and you can also add more fields, including the opening price, the high, the low, and the volume. You can even determine if you want to even see the date (although that’s probably not a good idea when you’re looking at historical data).

It’s easy to make a template with this function since it populates the data for you. Using variables for the ticker, the start date, and the end date, I can quickly set up a sheet that’s easily updatable:

The only formula that I enter is the one cell for the STOCKHISTORY function:

=STOCKHISTORY(C2,C3,C4,0,1,0,1,2,3,4,5)

Where C2, C3, and C4 refer to the stock, start, and end dates. The numbers 1 through 5 are needed to ensure that all the fields are extracted.

If you want more details about this function including the different arguments, you can check out Microsoft’s official page for this function.

How can I get other (non-US) tickers?

One of the things you’ll notice from the above examples is that I didn’t enter any prefix for the stock ticker. The StockHistory function knew I was looking for Tesla’s stock price. However, if you want to pull data from other exchanges, including those outside the U.S. markets, you’ll need to add a prefix to make sure that you’re getting the right quote. And since the function won’t actually return the company name, you need to make sure you’re entering the ticker correctly into the function.

Refer to this link for all the different market identifiers. For instance, if I wanted to pull the share price of Air Canada, which trades on the Toronto Stock Exchange, I’d need to enter the ticker as follows:

XTSE:AC

In most cases, it looks as though it’s just an X before the exchange’s usual prefix but you’ll want to double check to make sure.

Why you may not find the StockHistory function on your version of Excel

Since the function’s in beta, StockHistory is not available for most users. You can, however, sign up for Microsoft’s Office Insider program which will give you access to functions while they’re in beta. To join the program, follow the steps outlined here.

If you liked this post on How to Use the New Stock History Function in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.