How to Get Data from Yahoo Finance into a Google Spreadsheet

Published on: 2016-03-19 Last updated on: 2017-05-15

Yahoo finance provides a lot of great data for analyzing stocks, allowing you to determine if a stock is under or overvalued, and informing your investment decisions. In this post, we will go over some formulas you can use in google sheets to import yahoo finance data. All the formulas will use a ticker symbol to find the data for the relevant stock. Please note that data from Yahoo finance can only be used for personal purposes. You cannot republish or resell Yahoo data, as this would violate the terms of service with Yahoo, and also would hurt the data providers who are selling data to Yahoo.

There are 2 main ways to import data from Yahoo that I know of. The first way is to use codes in the Yahoo URLs that import the data. These codes could be seen as the Yahoo API. The second way, and I think the best way, is Google Sheet's Import Html function. Which can import html from any webpage given the web address. We are going to take advantage of the Yahoo finance link structure to use Import HTML to get a bunch of data. In theory, you can get any information published on Yahoo finance.

Yahoo data is now down, here is an alternative

As of May 2017 it seems Yahoo finally got rid of their old table legacy formula, making most of the variables below useless! In the meantime you can try import data from Nasdaq using this tutorial.

You can also try import from Finviz using this sheet as a guide.

https://goo.gl/tGiXDg

I will also work on getting more data directly from the SEC which should be more stable. Like us on Facebook to be informed of updates.


 

HTML Table and CSV Import Formulas

The html function looks as follows:
=index(ImportHtml("http://finance.yahoo.com/q/ae?s="&A2&"+Analyst+Estimates","table",13),2,5)
The formula works as follows: The other way, using the "API" symbols, uses google's Importdata function. We will need this to import somethings not in tables, like the ticker symbol name. Here is what this formula looks like:
=importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$A2,"&f=n"))

How to deal with #REF! errors

Sometimes when importing data into google sheets you will get a #REF! error. This is a known issue in google sheets. When this happens, you should use the iferror function to make a second try at populating the cell. It looks like this:

=iferror (first try at data,second try at data repeating the first formula)

This is an example of how it looks for market cap: =iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),1,2),=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),1,2))

Confused? Don't worry, you don't have to understand the formulas. If you want, you can just copy and paste from the values listed below. However, it is good to understand the formulas in case Yahoo changes it webpages. You can also comment below if they do, and I will try update this list. So now below, are the formulas for each metric:
 

Summary Data

Copy and paste the formulas below into Google Sheets to get the information. These formulas assume the ticker symbols are in Cell A2.
Name=importData(concatenate("http://finance.yahoo.com/d/quotes.csv?s=",$A2,"&f=n"))
Current Price (Use Google Finance)=GOOGLEFINANCE($A$2,"price")
Previous Close=iferror(index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",2),1,2),index(IMPORTHTML("http://finance.yahoo.com/q?s="&$A$2,"table",3),1,2))
Open=iferror(index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",2),2,2),index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",3),2,2))
Bid=iferror(index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",2),3,2),index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",3),3,2))
Ask=iferror(index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",2),4,2),index(IMPORTHTML("http://finance.yahoo.com/q?s="&$A$2,"table",3),4,2))
1 year estimated price target=iferror(index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",2),5,2),index(IMPORTHTML("http://finance.yahoo.com/q?s="&$A$2,"table",3),5,2))
Beta=iferror(index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",2),6,2),index(IMPORTHTML("http://finance.yahoo.com/q?s="&$A$2,"table",3),6,2))
Earnings Date=iferror(index(IMPORTHTML("http://finance.yahoo.com/q?s="& $A$2,"table",2),7,2),index(IMPORTHTML("http://finance.yahoo.com/q?s="&$A$2,"table",3),7,2))

 

Valuation Data

Market Cap=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),1,2) Click for format in Millions
Current Enterprise Value=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),2,2) Click for format in Millions
Trailing P/E (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),3,2)
Forward P/E=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),4,2)
5 year expected Price to Earnings Growth (PEG Ratio)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),5,2)
Price to Sales (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),6,2)
Price to Book (mrq)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),7,2)
Enterprise Value/Revenue (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),8,2)
Enterprise Value/EBITDA (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="&$A$2&"+Key+Statistics","table", 11),9,2)

 

Income Statement

Fiscal Year End Date=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 14),2,2)
Most Recent Quarter Date=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 14),3,2)
Profit Margin (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 16),2,2)
Operating Margin (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 16),3,2)
Return on Assets (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 18),2,2)
Return on Equity (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 18),3,2)
Revenue (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),2,2) Click for format in Millions
Revenue Per Share (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),3,2)
Quarterly Revenue Growth (yoy)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),4,2)
Gross Profit (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),5,2)
EBITDA (ttm)6:=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),6,2) Click for format in Millions
Net Income Avl to Common (ttm):=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),7,2)
Diluted EPS (ttm):=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),8,2)
Quarterly Earnings Growth (yoy) (Percent)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 20),9,2)

 

Balance Sheet

Total Cash (mrq)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 22),2,2)
Total Cash Per Share (mrq)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 22),3,2)
Total Debt (mrq)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 22),4,2)
Total Debt/Equity (mrq)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 22),5,2)
Current Ratio (mrq)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 22),6,2)
Book Value Per Share (mrq)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 22),7,2)
Operating Cash Flow (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 24),2,2)
Levered Free Cash Flow (ttm)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 24),3,2)

 

Share Statistics

Avg Vol (3 month)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),2,2)
Avg Vol (10 day)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),3,2)
Shares Outstanding=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),4,2)
Float=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),5,2)
% Held by Insiders=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),6,2)
% Held by Institutions=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),7,2)
Shares Short (as of Feb 29, 2016)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),8,2)
Short Ratio (as of Feb 29, 2016)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),9,2)
Short % of Float (as of Feb 29, 2016)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),10,2)
Shares Short (prior month)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 30),11,2)

 

Stock Price History

Beta=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 28),2,2)
52-Week Change=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 28),3,2)
S&P500 52-Week Change=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 28),4,2)
52-Week High (Apr 28, 2015)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 28),5,2)
52-Week Low (Aug 24, 2015)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 28),6,2)
50-Day Moving Average=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 28),7,2)
200-Day Moving Average=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 28),8,2)

 

Dividends and Splits

Forward Annual Dividend Rate=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),2,2)
Forward Annual Dividend Yield=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),3,2)
Trailing Annual Dividend Yield=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),4,2)
Trailing Annual Dividend Yield=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),5,2)
5 Year Average Dividend Yield=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),6,2)
Payout Ratio=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),7,2)
Dividend Date=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),8,2)
Ex-Dividend Date=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),9,2)
Last Split Factor (new per old)=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),10,2)
Last Split Date=index(IMPORTHTML("http://finance.yahoo.com/q/ks?s="& $A$2&"+Key+Statistics","table", 32),11,2)

 

Earnings per Share (EPS) Estimates

Current Quarter EPS Estimate
Average Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,2)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,2)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,2)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,2)
Last year EPS for Current Quarter=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,2)

Next Quarter EPS Estimate
EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,3)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,3)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,3)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,3)
Last year EPS for Next Quarter=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,3)

Current Year EPS Estimate
EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,4)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,4)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,4)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,4)
Last year EPS for Current Year=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,4)

Next Year EPS Estimate
EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,5)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,5)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,5)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,5)
Last year EPS for Next Year=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,5)
Current Quarter EPS Estimate
Average Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,2)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,2)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,2)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,2)
Last year EPS for Current Quarter=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,2)

Next Quarter EPS Estimate
EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,3)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,3)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,3)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,3)
Last year EPS for Next Quarter=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,3)

Current Year EPS Estimate
EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,4)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,4)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,4)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,4)
Last year EPS for Current Year=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,4)

Next Year EPS Estimate
EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),2,5)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),3,5)
Low EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),4,5)
High EPS Estimate=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),5,5)
Last year EPS for Next Year=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 10),6,5)

Earnings (EPS) History - Thanks to Mayur!
Previous Quarter EPS Est=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 16),2,5)
EPS Actual=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 16),3,5)
Difference (Actual-Est)=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 16),4,5)
Surprise %=index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table", 16),5,5)

 

Revenue Estimates

Current Quarter Revenue Estimate
Average Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),2,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),2,2))
Number of Analysts=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),3,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),3,2))
Low Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),4,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),4,2)))
High Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),5,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),5,2))
Year Ago Sales from Current Quarter=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),6,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),6,2))
% Sales Growth (year/est)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),7,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),7,2))

Next Quarter Revenue Estimate
Average Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),2,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),2,3))
Number of Analysts=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),3,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),3,3))
Low Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),4,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),4,3)))
High Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),5,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),5,3))
Year Ago Sales from Next Quarter=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),6,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),6,3))
% Sales Growth (year/est)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),7,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),7,3))

Current Year Revenue Estimate
Average Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),2,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),2,4))
Number of Analysts=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),3,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),3,4))
Low Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),4,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),4,4)))
High Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),5,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),5,4))
Year Ago Sales from Current Year=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),6,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),6,4))
% Sales Growth (year/est)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),7,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),7,4))

Next Year Revenue Estimate
Average Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),2,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),2,5)) Click for format in Millions
Number of Analysts=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),3,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),3,5))
Low Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),4,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),4,5)))
High Revenue Estimate=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),5,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),5,5))
Year Ago Sales from Next Year=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),6,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),6,5))
% Sales Growth (year/est)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",13),7,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",14),7,5))

 

Growth Estimates for the Stock, the Industry, Sector, and S&P

Growth Estimate for the Stock (Based on ticker in Cell A2)
Current Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),2,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),2,2))
Next Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),3,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),3,2))
This Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),4,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),4,2))
Next Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),5,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),5,2))
Past 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),6,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),6,2))
Next 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),7,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),7,2))
P/E (For Comparison to Sector)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),8,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),8,2))
PEG Ratio (For Comparison to Sector)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),9,2),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),9,2))

Growth Estimates for the Sector (Based on ticker in Cell A2)
Current Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),2,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),2,3))
Next Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),3,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),3,3))
This Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),4,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),4,3))
Next Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),5,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),5,3))
Past 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),6,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),6,3))
Next 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),7,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),7,3))
P/E=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),8,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),8,3))
PEG Ratio=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),9,3),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),9,3))

Growth Estimates for the Industry (Based on ticker in Cell A2)
Current Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),2,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),2,4))
Next Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),3,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),3,4))
This Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),4,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),4,4))
Next Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),5,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),5,4))
Past 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),6,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),6,4))
Next 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),7,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),7,4))
P/E=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),8,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),8,4))
PEG Ratio=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),9,4),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),9,4))

Growth Estimates for the S&P
Current Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),2,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),2,5))
Next Quarter (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),3,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),3,5))
This Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),4,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),4,5))
Next Year (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),5,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),5,5))
Past 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),6,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),6,5))
Next 5 Years (Per Year) (%)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),7,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),7,5))
P/E (For Comparison to Sector)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),8,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),8,5))
PEG Ratio (For Comparison to Sector)=iferror(index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",25),9,5),index(IMPORTHTML("http://finance.yahoo.com/q/ae?s="&$A$2&"+Analyst+Estimates","table",26),9,5))

 

Analyst Targets


Analyst Targets
Average (Mean) Analyst Target=index(IMPORTHTML("http://finance.yahoo.com/q/ao?s="&$A$2&"+Analyst+Opinion","table", 12),1,2)
Median Analyst Target=index(IMPORTHTML("http://finance.yahoo.com/q/ao?s="&$A$2&"+Analyst+Opinion","table", 12),2,2)
Highest Analyst Target=index(IMPORTHTML("http://finance.yahoo.com/q/ao?s="&$A$2&"+Analyst+Opinion","table", 12),3,2)
Lowest Analyst Target=index(IMPORTHTML("http://finance.yahoo.com/q/ao?s="&$A$2&"+Analyst+Opinion","table", 12),4,2)
Number of Analysts=index(IMPORTHTML("http://finance.yahoo.com/q/ao?s="&$A$2&"+Analyst+Opinion","table", 12),5,2)
Recommendation Rating Thanks to Mayur (1-5 where 1=buy and 5=sell)=index(IMPORTHTML("http://finance.yahoo.com/q/ao?s="&$A$2&"+Analyst+Opinion","table", 10),1,2)
Please note: Should Yahoo finance change the layout of their website or tables this data will be incorrect. Please occasionally check against the pages on Yahoo to be sure the numbers are being imported correctly.

The IMPORTHTML function is powerful and can be used to import data from any website. Please be sure to comply with a website's terms of service. This data is for personal non-commercial use only.

Related

Comments