fbpx

How to Get Stock Prices in Excel for Free

The hallmark newbie investor is someone who tells you gleefully that the stock they bought a few weeks ago is up +20%. As if short-term price movements have anything to do with the potential of the volatile tech company they probably invested their entire savings in. Let the emotional roller coaster begin.

When you first start off investing in stocks, it’s normal to check your portfolio six times a day during trading hours. We’ve all been there, but after a while, you pay less and less attention to what your stocks are getting up to. You prefer to stick with assets that help you sleep well at night – so-called SWAN stocks.

Investors of all experience levels will want to track their portfolio of stocks in one place instead of having to constantly log into a brokerage account or multiple brokerage accounts. These days, investors might hold stocks across half a dozen financial institutions. The simplest way to track your stock portfolio(s) is by using Microsoft Excel. If you don’t know how to use Excel, watch some videos on YouTube and jump right in. Setup your portfolio the way you want to and learn something in the process.

Once you have your stock portfolio setup in Excel, you’ll have a field with the stock prices. Ideally, you want this to update automatically every time you open the spreadsheet. Anyone who has been through this exercise knows how bad it sucks to update stock prices manually in a spreadsheet.

The problem is, financial data isn’t free. So, we set out to find some ways that you can import stock prices into an Excel file easily and for free.

Throughout this article, we will refer to an “application programming interface” or API. That’s a commonly used method for computers to transfer data to each other. Don’t worry. For the purposes of this guide, you won’t need to know anything about programming at all.

Importing Stock Price Data Into Excel for Free

Contrary to what some of our readers might think, only a tiny fraction of the financial support we receive from premium subscribers goes towards coke-fueled nights of debauchery in Hong Kong’s Wan Chai district. (Or as investment bankers call it, “entertaining clients.”) The rest of your hard-earned dollars go towards building financial tools and content to help you make better investment decisions.

For example, we developed a dividend growth strategy called Quantigence which provides an objective way to compare the 75 stocks in our universe that have paid and increased dividends for at least 25 years. We developed a methodology that takes in financial data and outputs a “Q-score” for each stock. Some of the data points we utilize include:

  • Dividend Payments
  • International Revenues
  • Stock Price
  • Market Cap

In order to obtain this data, we needed to license it from a data vendor which then charges us a pretty penny to distribute it. Most investors don’t have a need for such a rich data set. Simply being able to look up the price of a stock using the ticker in an Excel spreadsheet is the extent to which most armchair investors would use financial data. 

One of our premium subscribers asked if we knew a way to import stock prices into an Excel spreadsheet and we realized we needed to do the same exact thing. Historically, you could do this with the Yahoo Finance API. (Yahoo is a company that used to have something going for it before “she who you shall not criticize” ran it into the ground while making a mint in the process.) Unfortunately, Yahoo Finance discontinued their data feed. So, we took a good look around and here’s what we found.

How to Get Stock Prices in Excel for Free

What we’re looking for is a simple function that lets us enter a ticker symbol and receive the current stock price in return. There are a number of ways to do this depending on your operating system and Excel version.

Microsoft Office 365 Stock Price Data

If you happen to be using Microsoft Office 365, you are golden. Just check out a Microsoft article titled “Get a stock quote” which explains how you can pull stock price information into an Excel worksheet using simple native functionality. It’s basically right there under the Data tab.

The red square above denotes the functionality – Credit: Nanalyze

We had no idea this functionality exists, but it appears to work very well. You can also access some other basic stock information. It’s the best option available for retrieving stock prices, so just upgrade your version of Microsoft Office if need be, and you’ll be cooking with gas.

Refinitiv provides this external data to Microsoft, so we can probably assume this is here to stay. Note that this is supposed to work for Mac users as well, though we don’t have any of those hideous contraptions laying around to test it on.

If for whatever reason you’re unable to upgrade your version of Microsoft Excel, another option is Alpha Vantage.

Stock Prices From Alpha Vantage

Founded in 2017, Bawston startup Alpha Vantage took in a few million in funding to become a leading provider of stock APIs. As of now, the company offers a free Excel plugin which you can use to import stock price data. Just remember, you get what you pay for.

From what we can tell, Alpha Vantage outsourced the development work to some colorblind Bangladeshi high school kids who proceeded to build the most unusable user interface known to man. It’s an absolutely dreadful piece of software, but when you do get it installed, it seems to do the job. First, you’ll need to quickly register to get a free API key which you’ll then enter when prompted. Afterwards, here’s the function you need to use to retrieve stock prices:

  • =AVGetEquityQuote(“AAPL”,”price”)

Just replace AAPL with whatever stock ticker you’re trying to retrieve a price for.

It may not be pretty, but you get what you pay for. It’s kind of like how people who use Google Sheets (free) instead of Microsoft Excel (paid) try to convince themselves that they’re using a real spreadsheet. If you are a Google sheet user, you’ll also be able to download this same plug in to use with that pathetic excuse for a spreadsheet program.

If you’re looking for stock prices from foreign exchanges, you might want to check out Quandl.

Quandl Stock Price Data

Earlier, we talked about data that we needed to license for our dividend growth investing strategy, Quantigence. The firm we procure data from, Quandl, also offers a select set of free data which can be accessed in Excel if you download their plugin, register, and do some minimal configuration. This article about Installation & Authentication talks about the simple process.

  • Register with Quandl
  • After you register, you will be given an API key. Copy it down.
  • Download the executable

In order to continue after taking the above steps, you’ll need to really dig in to figure out how to use the add-in. It’s not user friendly at all and will take some time to figure out, no matter how familiar you are with software or the stock market.

Still Having Problems?

Whenever you provide something useful online, people assume that you should also support it as well. It’s not enough to provide them with useful information, you also need to hold their hand through the process and answer all their questions. Unfortunately, we can’t help you with whatever problems you’ve encountered while using any of the options we’ve just detailed – unless of course, you’re a Nanalyze Premium subscriber.

We are always here to help those who support us financially, so feel free to drop us an email with any questions you have, and we’ll be happy to help. Since we didn’t build Microsoft Office and we don’t work for Quandl, please make sure to peruse whatever online resources there are that might help address your questions first.

Conclusion

Over the years, we’ve seen numerous companies offer free stock price data and then revoke it for whatever reason. Yahoo Finance was the go-to data source for many years before they pulled their functionality. Today, Microsoft Office 365 is by far the best solution available for getting stock prices into an Excel sheet easily and for free.

Tracking your stock portfolio is a good practice, but don’t obsess over it. We’re investors, not speculators. That means we’re in it for the long haul. If a stock price falls, we don’t panic and start questioning our decisions. We enter our stock positions with conviction, and see dips as an opportunity to add to our positions.