Excel Stock Data – The Ultimate Guide [Live & Historical Stock Data]

Being able to access and use Excel stock data correctly is a super helpful skill to be able to make sound investment decisions. Without data, a thorough analysis is impossible to complete. One of the best ways to access and analyze data for most people is through spreadsheets. In this guide, you will learn how to access real-time and historical fundamental data in Excel.

If you rather use Google Sheets, check out this guide instead.

Why is getting stock data in Excel is useful?

Before we begin, let us explain what fundamental data is. Fundamental data is obtained from the most basic information about a company or security. The statement of cash flows, balance sheet, and income statement are all examples of fundamental data that you can access for nearly any public company on a country's stock exchange. These statements contain a ton of valuable information about a company's performance, balance sheet health, and future potential.

Better yet, many ratios, such as ROIC, PE, Debt to equity, etc., calculated from the financial statements can be used to compare stocks across various financial performance categories.

This data is helpful as a part of your due diligence process to decide whether or not to invest in a particular stock or security. More specifically, getting stock data is a crucial part of the financial analysis process that allows you to determine the value of a company.

Getting stock data in Excel is extremely useful because the tool is designed to be easy to use and very powerful when analyzing financial data. In fact, this is one of the most popular choices for getting stock data.

How do you get stock data in Excel?

Getting stock data in Excel is actually one of the easiest things you can do. It's mainly just a matter of knowing where to look for it; after that, it becomes straightforward.

There are three types of data you can access when it comes to stock data: historical price and key metrics data, real-time data, dividend payment data, and historical fundamental/financial data. In this guide, you will learn how to get all of these types of data using two different methods. You can use these methods separately or combine them to be able to get all types of data in your Excel spreadsheet.

How to get real-time stock data in Excel?

For real-time stock data, there are two options you can use to get it; Wisesheets and Excel stocks.

Excel stocks data

The first method is to use Excel's stocks functionality which was implemented to allow those that hold Office 365 accounts to access stock data. There are two ways to navigate this functionality:

Excel stock tables

The first way is to create a table where you create a list of stock symbols you are looking to analyze, click on the data tab, select your stock list, and then click on the stocks option. If Excel recognizes these symbols as stocks that are supported, you'll be able to see this icon next to the stocks.

After this, you can add a column by clicking on the floating icon at the top right of the table and selecting the type of data you want in that column.

The supported data includes:

Wisesheets

Using Wisesheets, you can get real-time stock data very easily.

All you need to do is use the =WISEPRICE function, which requires the following arguments 

=WISEPRICE(ticker/s, parameter/s)

The number of stock exchanges supported is astounding, which means you will be able to get data from tickers from 50+ exchanges, including:

• XETRA (Germany)
• NSE (India)
• LSE (London)
• MCX (Russia)
• SIX (Switzerland)
• HKSE (Hong Kong)
• ASX (Australia)
• OSE (Norway)
• NYSE (US)
• NASDAQ (US)
• AMEX (US)
• EURONEXT (Europe)
• INDEX
• TSX (Canada)

See the complete exchange list.

The parameters that are currently supported include the following 

For a list of tickers, you can easily enter all these parameters in the function and get all the real-time data at once.

The best part about the WISEPRICE function is that whenever you'd like to update the data, just press the refresh button that's available on the WISEPRICE menu of Wisesheets.

How to get historical stock data on your spreadsheet?

For historical price data, you can use the Excel Stock history function or Wisesheets its all up to you.

Excel stock history function

You can get stock data directly from Microsoft by using the =STOCKHISTORY function. The function has the following syntax:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

Each of these arguments has the following functionality.

As for properties, these are the stock attributes you can access:

Notes:

• The order in which the properties are entered is the order in which they will be shown as a table; for example, if you enter close before open, the closing price will show before the opening price.
• When using this function, ensure enough cells are available for the data to populate.

Wisesheets

Accessing historical price data for stocks and ETFs is simple using Wisesheets.

The formula syntax works as follows: =WISEPRICE("ticker", "parameter", "number of days", "start date", "end date").

The list of available historical parameters includes:

• Open
• High
• Low
• Close
• AdjClose
• Volume
• Unadjusted Volume
• Change
• Change Percent
• Vwap
• Label
• ChangeOverTime

The number of days parameter is optional. However, if you do enter a value like 30, it will return the last 30 business days worth of historical data for whatever parameter you've selected along with the respective date.

For example =WISEPRICE("AAPL", "Close", 30)

In cases where you want to get this data for a specific period, like between January 1, 2022, and January 30, 2022, you can leave this parameter blank.

You have the option to enter a specific date range from which to get data. Make sure to use the yyyy-mm-dd format.

For example, if you are looking to get Apple's volume between March 1st and March 30th in 2022, you need to enter the following function command in a cell =WISEPRICE("AAPL", "volume", , "2022-03-01", "2022-03-30")

You can also use cell referencing to enter multiple parameters at once in the function and reference cells that are in the date format to ensure you are getting the data for the correct time period.

How to get stock financials and historical fundamentals to Excel?

Unfortunately, Excel stocks do not cover a company's historical financials, but with Wisesheets, you can quickly get them in two different ways:

Data Dump

Simply enter the ticker you are trying to get the stock data from, select annual or quarterly, and you will get all of the company's financials and key metrics on different tabs of your spreadsheet.

WISE Function

If you are looking for specific financial data points or key metrics for your models. You can also get them very quickly using the =WISE function:

The function accepts the following arguments:

=WISE(ticker, parameter/s, year or ttm, [quarter])

The list of parameters includes everything in the financial statements, key metrics, and growth metrics which you can see here.

Once you have a model set up using the function, you can simply change the ticker and get all of the financial data you need at once without copying-pasting financials from multiple websites.

How to get stock dividends on Excel?

Getting stock dividends on Excel is super simple using Wisesheets.

There are two ways to get dividend stock data. The first is to use the WISE function to get a company's current dividend yield.

You can do this using the following function call =WISE("ticker", "dividend yield", "ttm"). This will return the current dividend yield based on the latest stock price and dividend payment.

The second way is to get all the historical dividend payments for that particular company. You can do this via the WISEPRICE function as follows =WISEPRICE("ticker", "dividend").

As you can see, this will allow you to see all the past dividend payments that were made and perform any calculations or analyses you'd like.

Wisesheets vs Excel stocks

Excel stocks

Pros: 

• Free to use if you have a Microsoft 365 account 
• Includes all types of historical pricing data through the stock history function 
• Includes data outside of stocks like commodity prices, bond yields, etc.

Cons:

• Does not include historical financials or key metrics
• Real-time data can be significantly delayed 

Wisesheets

Pros: 

• Includes access to historical financials going back 20 years 
• Real-time data is only 15 minutes delayed and can be updated by the click of a button 
• Includes stock dividend data
• Advanced stock data such as segment revenues
• Pre-made stock analysis templates

Cons:

• $60 per year price 

Looking at both options, the best thing you can do is use Excel stocks and Wisesheets together and get all of the real-time and historical stock data you need for all types of analysis right on your Excel spreadsheet. When using these two, the possibilities are truly limitless. Below are some examples of advanced uses of stock data in Excel:

Advanced uses of stock data in Excel

Build a stock discounted cash flow valuation (DCF) in Excel

The best way to calculate stocks' intrinsic value is often by performing a discounted cash flow valuation (DCF) because it allows you to assess the current financial state of a company, make some thoughtful predictions, and evaluate the value of a company based on different scenarios.

Using a pre-made Wisesheets template or building your own, you can build a model to quickly allow you to calculate the value of a company under different assumptions like this:

If you would like to access this template, click here to get it for free.

Generate sparkline trends on financial statements

Using the =WISE function or Data Dump along with Excel's sparkline functionality, you can easily visualize trends on any company of your choice:

Build a Portfolio Tracking Spreadsheet in Excel

Tracking the performance of your stocks is very important to know how your portfolio is doing and can prepare you financially for taxation. With the =WISEPRICE function and =STOCKHISTORY, you can quickly build a dashboard that allows you to see your most significant holdings, their returns, or losses in one single view.

We hope this guide helps you take advantage of the Excel stocks and Wisesheets to make faster and better investments in the stock market.

Click here to sign up for a Wisesheets free trial account.

To your investing success,

The Wisesheets Team