Yahoo Finance is a great tool for accessing financial news and stock data. The data on the website includes key metrics, real-time pricing data, and historical price and financial data. The website is completely free and very popular amongst individual investors. However, one question that comes up often is, "how do you get the data available on Yahoo Finance in an Excel or Google Sheets spreadsheet for deeper analysis?". In this post, we will answer this question and teach you the paid and free methods there are.
How do I get stock data from Yahoo Finance to Excel?
There are four ways in which you can get stock data from Yahoo Finance onto your spreadsheet; manually copying/pasting from the website, programmatically scraping the website, exporting the data with Yahoo plus, and using Wisesheets. In this post, we will discuss each of these options except programming.
Real-Time Stock Data (Yahoo Finance and Wiseshets)
Real-time stock data, particularly pricing data, is critical because it is critical in making game-time decisions about whether or not to buy a particular stock.
Yahoo Finance Copy Paste
Copy and pasting real-time data to Excel is very difficult if you are looking for accurate data. Your best bet is to open the Yahoo Finance website with the stock you are tracking and wait for the site to automatically update the data. Unfortunately, the browser is often slow, and updates after a new quote has been submitted.
- Free to use
- All you need to have is the website open on your computer
- Can't track multiple stocks at once
- Can't run real-time calculations on those numbers like "price to sales ratios."
- Data tends to be more delayed than other methods
- Not real-time data
Yahoo Finance Plus
Besides Yahoo's free website, the company offers additional features for deeper stock analysis. For example, for those that subscribe to their paid service, you can more easily track strict prices in real-time and link it with your stock broker so you can easily track your portfolio.
- Real-time data access
- Many visualization tools
- Can link a stock broker account to track your portfolio
- Paid service with a cost ranging from $250 to $350 per year
- Can't track stock prices in Excel for additional calculations
If you like spreadsheets and want to get all the data you need, Wisesheets is the best option. You can easily get real-time stock data by using the =WISEPRICE function and just entering the ticker and parameter (i.e. AAPL, PRICE).
For example =WISEPRICE("AAPL", "Price").
Currently, the company supports tickers from 50+ exchanges, including (see full list):
- 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)
- TSX (Canada)
As well as the following real-time parameters:
- Changes Percentage
- Day Low
- Day High
- Year High
- Year Low
- Market Cap
- Price Avg 50
- Price Avg 200
- Avg Volume
- Previous Close
- Shares Outstanding
Once you have the function set up with your ticker and parameter, you can simply press the refresh button as often as you like to update the data in real-time.
- Integrated into Excel and Google Sheets
- Data updates when you want with the refresh button
- You can apply any calculations you'd like to the parameter list, and all data will be refreshed
- Real-time data delay is minimal
- $60 per year subscription for full access to all tickers
- You need basic knowledge of spreadsheets to take full advantage of it
Historical Data (Yahoo Finance and Wisesheets)
For deep stock analysis, historical data is often best because it provides context to how a company has performed financially over time. It also indicates the general direction of the company and its implications.
Yahoo Finance Manual Copy/Paste
Yahoo Finance contains many useful metrics like historical pricing data and company financials. However, copy/pasting this data is extremely time-consuming and very limited. Also, Yahoo limits the historical data to 4 years and 5 quarters. While you may think it is easy to copy these financials as tables onto Excel or Google Sheets, this is not the case – the data is intentionally structured, so you need to reformat it when it is copy/pasted.
Yahoo encourages users to purchase Yahoo Finance Plus, where data exporting to Excel is much easier.
As for key metrics, they are mostly limited to the current metrics such as P/E ratio, PEG, debt to equity, etc. One benefit is that for historical pricing data, you can easily select the period you'd like to get the data for and download it as a CSV file for your own analysis.
- Free to get the data
- Easy access to historical pricing data
- Lots of useful current key metrics
- Historical financials limited to 4 years and 5 quarters
- Copy/pasting key metrics and financials is very time-consuming because of the formatting
- No easy way to track stocks in Excel or Google sheets.
- Hard to compare many companies at once
Yahoo Finance Plus
Yahoo Finance Plus has the features that the free Yahoo Finance website does not offer. For example, using this service, you can download company financials right into Excel, get historical pricing data, and many more key metrics that are not available on the free website.
- You can get all data, including historical financials and pricing data, into Excel or the built-in visualizations
- It can be unaffordable for individual investors at a cost ranging from $250 to $350 per year
- Can't build dynamic models where you change the company ticker and get all the data you need
When it comes to historical financials, Wisesheets thrives. The tool offers access to the company's financials, key metrics, and growth metrics (annually and quarterly) going back 20 years by simply entering the company's ticker.
On top of this, you can get individual metrics for your financial models by using the WISE function.
The way the function is used is as follows =WISE("ticker", "parameter", "year or ttm", "quarter")
The company offers the following data.
The beauty of this function is that it allows you to get all the data you need for your financial models by only changing the ticker once it's set up.
The only downside of Wisesheets is that its only available on Excel and Google Sheets.
- Can build custom models and get data instantly on Google Sheets and Excel
- All data can be easily exported for the major global exchanges by entering the ticker
- Compare hundreds of companies in one view
- Build custom screener
- Get all financial statements, key metrics, and growth metrics at once
- Access segmented and geographic revenues
- You need a Wiseshests subscription that costs $60 per year
- Available only on Excel and Google Sheets
Now you know exactly how to get real-time and historical stock data on Excel and Google Sheets without any code. Making good stock investments is now up to you. We wish you the best in your investing journey.
To your investing success,
The Wisesheets Team