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:
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 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 |
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.
Argument | Description |
---|---|
stock | The 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_date | The 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_date | Optional. The latest date for which data will be retrieved. The default is start_date. |
interval | Optional. Specifies the interval each data value represents as follows: 0 = daily, 1 = weekly, 2 = monthly. The default is 0. |
headers | Optional. 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 – property5 | Optional. 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:
Property | Definition |
---|---|
Date | First valid trading day in the period |
Close | Closing price on the last trading day in the period |
Open | The opening price on the last trading day in the period |
High | The highest price of the highest day's high in the period |
Low | The lowest price of the lowest day's low in the period |
Volume | Volume 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)
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
16 Responses
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.
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.
Precisely what I was looking for, thanks for putting up.
No problem, glad to help!
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?
Yes, we are working on making those parameters available 🙂
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?
We are happy to help. Could you send us an email at info@wisesheets.io?
Your free trial is worthless. Can't use Wise functions!
You should be able to use all the functions and retrieve all the data available for Apple, Tesla, and Amazon. We are happy to help. Could you send us an email at info@wisesheets.io?
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 …
We are happy to help. Could you email us an example of the spreadsheet you are looking to build at info@wisesheets.io?
How do you represent a cash position? All I see are stocks in the examples.
Could you clarify what you mean?