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!

Guillermo Valles

Guillermo Valles

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 deals and learning the ropes.

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!

16 Responses

  1. Hi

    Before I purchase I just want to make sure it works properly. I was trying to make a reference to quarterly data.
    I used "=wise($A$1, $A$8, C6,C7)/1000000000"
    Where, $A$1=AAPL, $A$8=Revenue, C6=2019, C7=Q1.
    The following function incorrectly produces, $91.8 bil, which is data for 2019-12-28, i.e. 2019 Q4. I was trying to produce data for 2019Q1, i.e., 2018-12-29. That is $84.3 bil.

    In fact, referencing to Q1 data produces incorrect data.

    1. Hi Frank,

      We totally understand your concern with this. The reason why this happens is that the quarters are based on the date and year.

      As you can see the quarters are in sequential order q1, q2, etc, and the year is based on the date reported to the SEC.

      We understand this could cause confusion and for this reason, we have the “date” and “period” parameters on the WISE function so you can see the date and quarter of data you are retrieving.

      At the same time, we have the period ly and LQ so you get the latest year and latest quarter of data available and soon we will have ly-1, ly-2, etc, and the same for LQ. So you can be sure you are getting the exact data you need.

      Let us know if we can help with anything else.

  2. I'd like to have "shares closely held," "institutional holdings", "float", "Beta." I don't see these in your parameters. Can these be made available?

  3. I just signed up for a trial, and have tested it in Google Sheets and MS Excel (non-subscription). In Google SHeets I can easily use the Wise and Wiseprice functions to retrieve data (e.g. price, eps and industry). However, I cannot use the functions in MS Excel. Wisesheets is added to the ribbon and when I click it, the blue "Trial Statement Dump" panel appears (whichs works). However, not sure how to use the two functions?

  4. Problem : Mix historical data and actual data in one excel sheet
    I like your product very much, I was looking a long time for stuff like this, but I still have one problem
    How can I realize the following construction the easiest way :
    I would like to have an excel with columns b to z with latest prices from several stocks in b to z for today in the last row
    In the previous rows are the end of day prices, in the last row it must be the actual price with delay from today
    the starting historical date must be fix and the end of day from yesterday must be dynamic ( everyday one day +1)
    The last row is the actual price
    In column A is the respective date
    Suggestion of a solution
    2 excel sheets : one with historical data and one with actual price.
    After the actualization I add the actual price row of one sheet to the last row of the historical data sheet by VBA.
    I can manage the dynamic date by the following trick : In one cell ( A1) I got the actual date automatically =heute()
    In the formular =Wiseprice(ticker;etc;… "$A$1") with $A$1 as the dynamic end date
    Is this realizable or do you have a better idea ?
    I think I`m not alone with this problem. There must be a solution somewhere …

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts