Do you want to get stock data in Excel like Google Finance? If so, you're in the right place! In this guide, we will show you the best ways to get stock data in Excel. We will also provide some helpful tips on how to use this data to make informed investment decisions. So whether you are a beginner or an experienced investor, this guide has something for everyone!
Is Google Finance available in Excel?
Unfortunately, Google Finance is not available in Excel. However, you can use different alternatives to get the same data available and more following the methods in this guide.
What are the best ways to get stock data in Excel?
There are a few different ways that you can get stock data in Excel. One way is to use a third-party service such as Yahoo Finance or Bloomberg and copy-paste the data. Another way is to use an API (Application Programming Interface) to get the data directly from a data provider. However, the best way by far is to combine the built-in Excel Stocks function and Wisesheets to get all the stock data you need without coding or wasting time copy-pasting the data.
Historical stock data in Excel
Getting historical stock data depends on the type of data you are looking for. Here are the best ways to get stock data for each type.
Historical financials and key metrics (Wisesheets)
There are two ways to access historical financials and key metrics using Wisesheets. The first is to use the statement dump function of the add-in.
Here, all you need to do is search or a enter a stock ticker, select annual or quarterly data, and click on get data. You will then see the income statement, balance sheet, cash flow statement, key metrics, and growth metrics in different tabs of your Excel spreadsheet going back up to 19 years.
If you are looking to get only specific line items from the statement dump output, you can simply enter them in the WISE function and get them instantly.
The syntax of the function is simple:
=WISE(ticker, parameter/s, period, [quarter]).
As you can see, all you have to do is enter the ticker, the parameter/s you want to get, like "revenue", the period like 2020, "LY" (latest fiscal year), "LQ" (latest fiscal quarter), and quarter (only if you are looking for quarterly data).
Historical stock price data (STOCKHISTORY function)
If you want to get historical stock data in Excel, the best method is to use the built-in STOCKHISTORY function. The function syntax works as follows: =STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5]).
The function arguments definition can be seen here:
| Argument | Description |
|---|---|
| stock | Function returns historical price data about the financial instrument corresponding to this value. 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"). Learn more about our data sources. |
| start_date | The earliest date for which data is retrieved. Note that if 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. Default is start_date. |
| interval | Optional. Specifies the interval each data value represents as follows: 0 = daily, 1 = weekly, 2 = monthly. 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. If any of them is present, only the indicated columns are returned in the order provided. Default is 0,1 (i.e., Date and Close). |
The list of properties available includes:
| Property | Definition |
|---|---|
| Date | First valid trading day in the period |
| Close | Closing price on the last trading day in the period |
| Open | Opening price on the last trading day in the period |
| High | Highest price of the highest day's high in the period |
| Low | Lowest price of the lowest day's low in the period |
| Volume | Volume traded during the period |
Altogether to get Apple's close stock price from January 1st, 2022, until January 30th, 2022, you would need to enter the following command.
=STOCKHISTORY(“AAPL”, "01/01/2022", "01/30/2022",0,1,0,1).
Historical revenue breakdowns (Wisesheets)
Using the WISE function, you can also access historical revenue breakdown based on revenue segments and geographic segments.
For example, to get Apple's segment revenue breakdown from 2021 until 2019. All you need to do is enter the following function call:
=WISE("aapl", "segment revenues", {2021, 2020, 2019})
You can do the same for geographic segments, except you need to substitute "segment revenues" for "geographic revenues."
If you want to access this data every quarter, you must enter "LQ" (latest fiscal quarter), "LQ-1" (previous fiscal quarter), "LQ-2", etc., as the function's period.
Historical dividend stock data (Wisesheets)
There are two ways to access historical stock and ETF dividend data in Excel. The first is to use the WISE function, which you are already familiar with.
The function has to look like this:
=WISE("ticker", "dividend", period).
The key thing to remember is that you can only enter "LQ" and a range of LQ values as the period/s. So, for example, if you only enter "LQ" as the period, this will return the latest quarterly dividend paid by the company. However, if you enter something like:
=SUM(WISE("aapl", "dividend", {"lq", "lq-1", "lq-2", "lq-3}"))
This will return the sum of the last 4 dividend payments made by the company or ETF.
The second way to get historical dividend payment data is quite simple. Using the WISEPRICE function, all you have to do is enter something like =WISEPRICE("ticker", "dividend").
As you can see, this will return the entire dividend payment history of a stock or ETF along with the payment date declaration data, etc.
Real-time stock data in Excel
The three best ways of getting real-time stock data in your Excel spreadsheet are to use Excel stocks, and Wisesheets or combine the two of them together.
Excel stock tables
To get real-time stock data using Excel tables, you must create a table with the list of stocks you'd like to get real-time data for. Then click on the data tab at the top of the Excel ribbon, highlight your stock list, and click on "Stocks."
Within a few seconds Excel will recognize if these are valid symbols and mark them by the stock icon. From here, a floating table with a plus button will become visible for you to click on and select the data you would like to get.
The supported data includes:
| 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 |
After you have added the data, on the Data part of the Excel ribbon, you will see a refresh button to press if you would like the data to be updated.
Wisesheets (live price data)
Using Wisesheets getting real-time stock data on your spreadsheet is quite simple. After you install the Excel add-on, all you have to do is use the WISEPRICE function.
The syntax of the function works as follows:
=WISEPRICE(ticker/s, parameter/s).
The available parameters 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
- Earnings Announcement
- Timestamp
Altogether you can build a stock list like this one, reference all of the tickers inside the WISEPRICE function as well as the parameters, and get all the stock data you need in one function.
You can also refresh the data at any moment by pressing the WISPRICE menu of the Wisesheets add-in and pressing the refresh data button.
Wisesheets (key metrics)
Another type of accessible data only available through Wisesheets is key metrics. For this, you have to use the WISE function.
The function's syntax works as follows: =WISE(ticker, parameter/s, period, [quarter]).
The list of real-time parameters includes:
- Date
- Revenue Per Share
- Net Income Per Share
- Operating Cash Flow Per Share
- Free Cash Flow Per Share
- Cash Per Share
- Book Value Per Share
- Tangible Book Value Per Share
- Shareholders Equity Per Share
- Interest Debt Per Share
- Market Cap
- Enterprise Value
- PE Ratio
- Price To Sales Ratio
- POCF Ratio
- PFCF Ratio
- PB Ratio
- PTB Ratio
- EV To Sales
- Enterprise Value Over EBITDA
- EV To Operating Cash Flow
- EV To Free Cash Flow
- Earnings Yield
- Free Cash Flow Yield
- Debt To Equity
- …
Generally, for the period you enter a specific year like 2021, that will return the key metrics based on fiscal year financial results. However, you can enter "TTM", to get the real-time value of the particular metric you are looking for.
As you can see, in this case, you can access the real-time dividend yield of a particular company or ETF as well as the price to sales based on the latest financial results and stock price.
Excel Google Finance stock investment templates
Altogether following this guide, you can build any stock analysis template you desire by combining the different Wisesheets and Excel functions. Below are some of the most helpful stock investment templates you can build.
Discounted cash flow (DCF)
This is one of the best spreadsheet templates you can use because it first allows you to compare companies of a similar type by important key metrics. Then it will enable you to project future cash flows by entering any assumptions you like on the financial statements based on your research. Lastly, it combines historical stock data with real-time information to calculate the company's intrinsic value per share.
Stock tracker
This is a very useful template for tracking your investments. It includes all the important information you need to know about a company, as well as its share price. You can also easily see how much each stock has increased or decreased in value over time.
Stock screener
Using the WISE and WISEPRICE functions, you can compare hundreds of companies across the key numbers that matter to you in one view.
From here, you can quickly identify companies worth looking into at a deeper level and make better stock investments faster.
Tips and tricks for getting stock data in Excel
There are a few things to keep in mind when trying to get stock data in Excel:
– Make sure you use the correct ticker when getting stock data from international stocks from Excel stocks and Wisesheets. You can see their respective ticker extensions and markets covered here for Excel stocks and for Wisesheets here.
– Use cell referencing to get multiple data points in the WISE and WISEPRICE functions at once.
Conclusion
In conclusion, you cannot use Google Finance in Excel. Therefore using Wisesheets and Excel Stocks are the best ways to get real-time and historical stock data in your Excel spreadsheets.
You can use the WISE and WISEPRICE functions to get stock data for any company or ETF.
There are also several valuable templates you can build using Excel and Wisesheets to help with your stock analysis and investment decisions.
To your investment success!
