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

Excel 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. You can create valuable charts, financial tables, valuation models, and more with Excel.

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 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:

Stock Metric name
Change (%)
Change (% after hours)
Change (after hours)
Company description
Currency
Employees
Exchange
Exchange abbreviation
Headquarters
High
Industry
Instrument type
Last trade time
Low
Market cap
Name
Open 
P/E
Previous Close
Price
Price (after hours)
Shares outstanding
Ticker symbol
Volume
Volume average

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 some parameters that are currently supported include the following:

Symbol
Name
Price
Changes Percentage
Change
Day Low
Day High
Year High
Year Low
Market Cap
Price Avg 50
Price Avg 200
Volume
Avg Volume
Exchange
Open
Previous Close
EPS
PE
Shares Outstanding

See all data parameters.

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

Excel real-time stock data

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.

ArgumentDescription
stockThe function returns historical price data about the financial instrument corresponding to this value. First, enter a ticker symbol in double quotes (e.g., "MSFT") or a reference to a cell containing the Stocks data type. This will pull data from the default exchange for the instrument. You can also refer to a specific exchange by entering a 4-character ISO market identifier code (MIC), followed by a colon, followed by the ticker symbol (e.g., "XNAS:MSFT").
start_dateThe earliest date for which data is retrieved. Note that if the interval is not 0 (daily), the first data point may be earlier than the start_date provided – it will be the first date of the period requested.
end_dateOptional. The latest date for which data will be retrieved. The default is start_date.
intervalOptional. Specifies the interval each data value represents as follows: 0 = daily, 1 = weekly, 2 = monthly. The default is 0.
headersOptional. Specifies whether to display headings as follows: 0 = no headers, 1 = show headers, 2 = show instrument identifier and headers. Default is 1 (i.e., show headers). When included, headers are rows of text that are part of the array returned from the function.
property0 – property5Optional. The columns that are retrieved for each stock as follows: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, and 5 = Volume. Only the indicated columns are returned in the order provided if any of them is present. Default is 0,1 (i.e., Date and Close).

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

PropertyDefinition
DateFirst valid trading day in the period 
CloseClosing price on the last trading day in the period 
OpenThe opening price on the last trading day in the period 
HighThe highest price of the highest day's high in the period 
LowThe lowest price of the lowest day's low in the period 
VolumeVolume traded during the period

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)

Apple historical stock price

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")

Apple's volume Excel

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/s 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.

Stock financials excel

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").

Excel stock dividend payments

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.

The third way is to get all the historical dividend payments for a company or ETF in a particular year. You can do this via the WISE function as follows =WISE("msft", "dividend", 2020).

yearly dividend payments excel

The second way is to get all the monthly or quarterly dividend payments of a company or ETF. You can do this via the WISE function as follows =WISE("aapl", "dividend", "lq").

monthly or quarterly dividend payments excel

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 and key metrics 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:

REIT DCF

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

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

stock financials sparklines

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.

excel portfolio tracker template

Conclusion

Harnessing the power of Excel to access and analyze stock data has never been more crucial. As the financial world becomes increasingly data-driven, the need for easily accessible and comprehensive data becomes paramount. The guide has underscored the importance of understanding stock data, especially fundamental data that forms the backbone of any serious investment decision.

Excel, with its innate versatility and user-friendly interface, provides the perfect platform for such financial explorations. But the addition of tools like Wisesheets further elevates the data accessibility and analysis capabilities, offering a more holistic view of stock performances, historical trends, and dividends.

While Excel remains a popular and trusted tool for many, Wisesheets’ ability to provide real-time data, historical financials, and key metrics give it an edge in in-depth stock analysis. The best approach, however, might be combining the strengths of both – Excel for its robust functionalities and Wisesheets for its detailed and expansive stock data coverage.

Ultimately, making informed investment decisions hinges on having accurate, up-to-date information at your fingertips. And with tools like Excel and Wisesheets, every investor, whether novice or expert, is better equipped to delve into the world of stocks with confidence and clarity. Investing, after all, isn't just about the money; it's about making informed decisions, and having the right tools at one's disposal is half the battle won.

Happy investing!

Hello! I'm a finance enthusiast who fell in love with the world of finance at 15, devouring Warren Buffet's books and streaming Berkshire Hathaway meetings like a true fan.

I started my career in the industry at one of Canada's largest REITs, where I honed my skills analyzing and facilitating over a billion dollars in commercial real estate deals.

My passion led me to the stock market, but I quickly found myself spending more time gathering data than analyzing companies.

That's when my team and I created Wisesheets, a tool designed to automate the stock data gathering process, with the ultimate goal of helping anyone quickly find good investment opportunities.

Today, I juggle improving Wisesheets and tending to my stock portfolio, which I like to think of as a garden of assets and dividends. My journey from a finance-loving teenager to a tech entrepreneur has been a thrilling ride, full of surprises and lessons.

I'm excited for what's next and look forward to sharing my passion for finance and investing with others!

Related Posts