How to Get Stock Prices in Excel? With 3 Different Options

This article will explore how to get stock prices in excel. We’ll provide you with three different options so that you can select the most suitable one for your needs.

Methods to Import Real Time Stock Prices To Excel

#1 Method: Excel STOCKHISTORY function
#2 Method: Stocks Feature in Excel
#3 Method: Someka Historical Stock Prices Template

Keeping up with the most recent stock prices is essential for investors, financial analysts, and anybody else with an interest in the stock market. If you want to make calculations with the latest stock prices, you’ll import the stock quotations to your spreadsheets.

Therefore, with its many features and adaptability, Excel is an essential tool for organizing and evaluating financial data. Because, Excel provides multiple ways to get current or past stock prices, whether you’re managing a portfolio, researching the market, or just interested in stock market patterns.

How to Get Stock Prices in Excel with 3 Different Options

This post will walk you through three efficient methods for obtaining stock prices in Excel: making use of the Someka Stock Prices Calculator, the Stocks feature, and the STOCKHISTORY function. After reading this tutorial, you should be able to select the approach that works best for you and start adding real-time stock data to your Excel spreadsheets.

Method 1: Excel STOCKHISTORY function

Excel’s STOCKHISTORY function was added recently to facilitate users’ ability to import historical stock data into spreadsheets.

With the help of this feature, which is part of Excel for Microsoft 365, users can easily retrieve a variety of information about individual stocks, such as volume and the open, high, low, and close prices over a given time frame.

How to use Stockhistory function in Excel?

Let’s start with the syntax of this function:

Here you’ll need at least the stock name and the start date information. Other areas are optional. We’ll go with the basic one.

In this formula, the most important part is to understand the meanings of each element in the syntax. This will help you bring exactly what you need on how to get stock prices in excel:

Mandatory Variables:

• Stock: This is the stock symbols of your interested companies.
• Start_date: This is the earliest date for the data.

Optional Variables:

• End_date: The latest date for the data.
• Interval: You can select daily, weekly, or monthly intervals. Here you’ll use 0 for daily, 1 for weekly, and 2 for monthly. Please remember the default is zero, so if you do not use this optional area, you’ll get daily intervals.
• Headers: Again optional areas to select whether to display or hide headers in your data arrays. Write 0 for no headers, write 1 for show headers, and write 2 for show instrument identifier and headers. Here the default is one, in other words it will show the headers if you do not identify any value here.
• Properties: The properties take values from zero to five to represent Date, Close, Open, High, Low, Volume values. If you specify any of them, it will bring you the mentioned properties. Otherwise, the default is 0 and1 here, which means to bring Date and Close values.

Excel STOCKHISTORY Function Examples:

Let’s make an example together to better understand how to get stock prices in Excel with STOCKHISTORY function:

We’ll look for the prices for Walt Disney with a symbol of DIS.

In the simplest form of the function, we have only input the stock symbol and start date, here’s how the formula works:

So, we have used a cell reference for the stock symbol. But if you’re writing the stocks name manually, please do not put it between a quotation mark, like “DIS”, “NKE”, “APPL”, etc.

As an additional note, when you only write the symbol, the function will pull data from the default exchange for the instrument. But you can also define a specific exchange with the 4-character ISO market identifier code (MIC), followed by a colon and then the symbol like, “XNAS:DIS”.

Also, the date arguments can be double-quoted dates (“01-01-2020”), formulas (“TODAY()”), or cell references pointing to date-containing cells.

Now let’s see the selected date’s closing price without any headers. So, we’ll also use 0 for no headers in the headers place and we’ll only write the Close property and leave other areas empty:

Lastly, let’s replace our constant date with a function. For example, if we are looking for Yesterday’s closing price, we’ll use Today function minus 1:

By setting an end date, selecting an interval (daily, weekly, or monthly), deciding whether to include headers, indicating which stock data properties (e.g., close price, volume), and selecting the sort order, you can tailor the data that is retrieved.

Excel will fill the cells with the requested stock history information when you press Enter.

KEY TAKEAWAYS FOR STOCKHISTORY FUNCTION:
- You need Internet connection to fetch data with this formula.
- It's important to learn the basics of  the syntax to get your data in the exact form you need.
- This function is only available in Microsoft 365 and Excel Online, and not compatible with earlier versions.
- If you want to check the data sources, you can visit Microsoft's source page.

Method 2: Stocks Feature in Excel

Microsoft Excel also provides a built-in Stocks feature to fetch real-time stock quotations as well as making some calculations.

Select your company names and then go to Data > Stocks from the top menu:

Excel may bring you a Data Selector window on the right side of your screen if the selected company is active more than one exchanges all around the world. So, you can select your choice and continue.

Now, your company names has changed and there’s small icon for you to select the data types that you want to export:

So, you can easily fetch data with this feature. You can make your list of companies and then select them together to start the Stocks tool.

Additionally, the data type list here includes not only stock markets data like Open Price, 52-week highs and lows, price changes, but also company information such as industry, employee number, headquarters, etc. This is one of the easiest ways to get stock prices in Excel.

In summary, you can transform the text into a “Stock” data type in Excel using the Stocks feature, which is a part of the Data Types group and connects to an online data source. With the help of this feature, you can quickly access and update stock data in your spreadsheet, including price, company name, and other financial metrics.

Method 3: Someka Historical Stock Prices Template

Now, we are introducing you a fully automated tool to fetch data of historical stock prices to your Excel spreadsheet. This is the easiest way of how to get stock prices in Excel.

This template is a stock price scrapper to allow to fetch data for any selected company stock with a list of dates.

>> Download Historical Stock Prices Scraper by Someka

With its powerful features, you’ll not need to think about making any arrangements on your Excel sheet:

• Automated data import
• Fetches data for NASDAQ and NYSE from Yahoo Finance
• Compatible with Excel 2010 and later versions
• Allows analysis and comparison
• Indicator explanations and company symbol list included
• Ready-to-download

And if you also need more deeper analysis for your stocks portfolio in addition to real time data, then we also have another Stock Prices Calculator tool in our collection:

>> Download Stock Prices Calculator

So, this is more of a portfolio analysis tool with real time stocks quotations.

Here’s an example from Someka Trade Journal analysis:

>> Download Someka Trade Journal Tool

Lastly, with Someka’s ready-tools designed for stock market actors, you can easily get stock prices in Excel and make analysis for your transactions and portfolio.

FINAL WORDS

Thus, in this article we have gone through the easiest ways of importing stock price data in Excel. Of course, there are many resources to track stock prices and you can build more complex spreadsheets with different data import functions.

But with Excel’s built-in STOCKHISTORY function and Excel STOCKS feature, you do not have to look further. If you have a list of company stocks, you can easily fetch data with an online connection.

However, using these functions will need expertise in Excel and you have to go through step-by-step how to get stock prices in Excel instructions. So we also provide you with ready to use Excel Stock Prices Scraper to fetch data, and also Excel Stock Prices Calculator to make analysis with real time data. Additionally, unless the Excel’s built-in functions, this tool is compatible with earlier versions of Excel, too.

Hope you enjoy our article!

Recommended Readings:

A Real Time Stock Quotes in Excel using Python

The Definitive Guide To Trading Journal: 5 Most-Asked Questions!

Does Martingale Strategy work for Roulette? Simulate with Excel Martingale Calculator