19th Ave New York, NY 95822, USA

How To Scrape Stock Data From Finviz And Google Finance

LOREM IPSUM DOLOR SIT AMET
Web scrape Finviz data

If you want to backtest trading strategies it’s important to have good quality data. I use Norgate Data for my end of day price quotes and am very happy with the quality and service.

However, there are times when I want to scrape stock data from the web. For example, if I want to extract fundamentals or get quotes for a stock that’s not covered in my Norgate subscription.

In this article I will show you how you can scrape stock data from Finviz and Google straight into Google sheets. This is incredibly easy and it’s free.

Importing Data From Google Finance

The first step is to open up a new Google sheet and then you can connect it with Google Finance. You will need a Google account for this if you don’t already have one.

Once you have your Google spreadsheet open you can easily import quotes and price data using Google Finance.

Importing stock quotes with google finance is straightforward and there is already a lot of detailed information on it provided by Google which you can find here.

For example, maybe you want to download daily quotes for Apple stock for the last five years. If so, you can simply write the following formula:

=GOOGLEFINANCE("AAPL", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")

Hit enter and your spreadsheet will automatically populate with the data like below:Example of historical stock data with Google Finance

Or maybe you want to import some fundamental data like the PE ratio. In Google this is stored as an attribute and can be accessed as follows:

=GoogleFinance("AAPL","PE")

Example of PE ratio with Google Finance data

There are lots more attributes you can access depending on what you need.

For example, if you put your ticker symbol in cell A2 and the attribute in column B you can import the data with a simple formula such as:

=GOOGLEFINANCE($A$2, B2) or =GOOGLEFINANCE($A$2, B3)

How to extract stock data with Google Finance example

How To Extract Finviz Data With Google Sheets

Google finance data is easy to use but it is not really web scraping and it does have some limitations when it comes to accessing different data points.

Another option is FINVIZ

Finviz has an excellent stock screener with over 60 filters that we can import directly into Google Sheets.

To do this we need to make use of the IMPORTHTML function in Google Sheets.

IMPORTHTML is simply a command we can use in Google Sheets to scrape data from a table or a list within a web page.

The syntax is written as follows:

IMPORTHTML(url, query, index)

The url is the webpage that contains the data we want, query is the type of structure, the list or table that the data belongs to. And index identifies which table or list should be returned.

You could use IMPORTHTML to extract a list of world demographics. Or you could use it to extract a list of email addresses.

A word of caution though. Not all websites allow you to scrape their data and you can get in trouble if it is against their terms of use.

Scraping Stock Data From Finviz

You can use IMPORTHTML to scrape stock data from the stock screener on Finviz.

For example, let’s say we want to scrape the market cap of Apple and plug it into our spreadsheet.

The table used to be table 11. But now the correct table to use is table 8.

Simply type the following formula into Google Sheets:

=index(IMPORTHTML("http://finviz.com/quote.ashx?t="&"AAPL","table", 8),2,2)

You will see that in the formula above, the URL is the Finviz web page for Apple and Table 8 is the table on the webpage that contains all the data.

(If you are unsure of the table you can sometimes find it by right clicking on the table and clicking inspect element in Safari browser. However, sometimes it is a matter of trial and error to get the right table number.)

The first ‘2’ in the formula represents the row in the table that we want and the second ‘2’ represents the column.

So we are pulling the data from column two, row two, in table 8 and we get $2476.71 billion.

formula web scrape finviz aapl data

You can verify this is correct by looking at the market cap shown on the Finviz web page for Apple:

aapl market cap finviz

Let’s see another example.

Try typing the following formula and you will get the Apple earnings date.

=index(IMPORTHTML("http://finviz.com/quote.ashx?t="&"AAPL","table", 8),11,6)

We are now simply importing the data from row 11, column 6 in the same table.

apple earnings date google sheets

If you had wanted the PE you would have gone for row 1, column 4.

aapl finviz earnings table You can now see how easy it would be to reproduce the whole table of data. All you need is a line of code for each cell.

If you reference the ticker from a separate cell such as A2 you can build up the sheet with different stocks.

finviz scrape aapl

What To Do With The Data

Web scraping with Finviz and Google sheets can be a powerful tool and used in various ways.

One thing I like to do is to import fundamental data from Finviz on a daily basis and thereby build up a database of fundamental statistics over time.

I can then compare that data with my trade signals and see if there are any relationships. Anything that might improve my signals which are mostly price based.

Alternatively, you can use this method to more quickly see the metrics of any trading signal that comes your way.

You can also use it to easily track a portfolio or download historical stock quotes from Google which can be imported into other software.

Of course, IMPORTHTML will work with other websites too and so there are plenty of other data sources that might be useful. If you are looking for a way to export finviz to excel then you can try linking in with your Google Sheets or a plugin such as “SMF-AddIn,” by Randy Harmelink.

Using The Finviz API

Utilising the Finviz API, allows the data to be pulled into a Google sheet. Updating this sheet every day allows you to maintain a live dashboard of your favorite stocks.

This data can then be stored into a separate database in order to build up a historical database of prices over time. Macros or automations can also be used to automate this process so that the data builds up without any manual intervention.

Similarly, if you are familiar with a programming language like Python you can use the Finviz API to scrape the data and automate it that way. This web scraper from David Morrison extracts data from finviz and stores in a sqlite database. There is also this Github code from Mariostoev.

Always bear in mind that price data is often delayed. For stocks, the delay is usually 15-minutes during market hours but fundamentals can take even longer to update.

If you need the data to be update much quicker than that then consider subscribing to Finviz Elite. I find that it’s worth subscribing just to get rid of the ads.

Video Tutorial

So guys (and gals) if you know of any other websites that allow this type of web scraping please let us know in the comments!


Comments (76)

If you are familiar with web scraping with a programming language like Python, I have written a scraper that extracts data from finviz and stores in a sqlite database. Here is a link to the code: https://github.com/dwmorrison33/python-meetup/blob/master/finviz_scraper.py

Awesome mate, cheers for that!

Of a sudden fin in table has changed. So google spreadsheet won’t work. How to find TABLEs on finviz site?

Use trial and error. Last time I checked it was 8. Now someone says it is 7.

still broken

As of July 4, 2022 the table number is 9.

Random guy in Japan is saying thank you from the future!

“SMF-AddIn,” by Randy Harmelink, is a free Excel app that works very well. Have used it to retrieve Finviz data for years, but can be used with many other financial/stock data sites as well.

Something that connects with Excel would be amazing. I’ll look into it thanks!

XLQ by Qmatix
https://www.qmatix.com/
A solution to working with market data in Microsoft Excel since 2000

XLQ allows you to work with market data in the way you want. No interface or program design restrictions. Create your own layout or easily modify any existing spreadsheets to have them always updated and up to date, even in real-time.

It works great!

Can you tell me if it is possible to remove the “*”s that are in front and behind the data fetched from Finviz i.e. *-26.86%* ?

Yes, do a Find and Replace. CTRL+F. Put ‘*’ into the find field. And put a space ‘ ‘ into the replace field and Replace All.

Highlighting a sheet and using CMD F and I get those highlighted in Google Sheets that should be changed all good but then I get “No Matches Found” when trying to replace the * with a ” ” or “A” in that workbook or specific sheet. Odd. Thanks for any further thoughts there.

I found an Adv Find and Replace plugin for Google Sheets that did do the trick of replacing the “*” with ” “. Thanks again.

Hmm not sure why. It works for me. Maybe you need to copy paste the values first (instead of formulas)?

I am experiencing the same issue. The ‘Find’ works and highlights the cells containing the ‘*’, but as soon as you change to Find and Replace the highlighting goes away and the attempt returns ‘No Matches Found’; frustrating because it found them until replace was added to the equation.

FWIW, I’m using this technique to pull in Finviz data and then export it to a Data Studio dashboard. When I send data to Google, I want whole numbers, not the abbreviated numbers that come in from Finviz. Additionally, I also needed a programatic way to remove the asterisks. I use the following Excel function for all of this:
=if(RegExMatch(A1,”B”), split(split(A1,”B”),”*”)*1000000000,if(RegExMatch(A1,”M”), split(split(A1,”M”),”*”)*1000000,if(RegExMatch(A1,”K”), split(split(A1,”K”),”*”)*100000)))
where A1 is the cell with the import data.

I still have to manually drag the formula every time I bring data into my spreadsheet, but that’s it.

REGEX Is the to automate it:

=REGEXREPLACE(A1,”\*”,) takes the content in A1, searches for * and replaces it with nothing.

If you want to do this automatically to data acquired by IMPORTHTML, you replace A1 above with the entire IMPORTHTML command as shown below:

=REGEXREPLACE(index(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&”AAPL”,”table”, 11),11,6),”\*”,)

Regards,

I was able to remove the pesky “*” by using a substitute function in the formula i.e.

=substitute(index(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&”MU”,”table”, 11),1,12),”*”,””)

Now I will try to replace the Stock symbol with a A1 so that I can easily replicate them across a sheet.

Is it possible to get the Industry field above the Finviz company data table? Thanks

Were you ever able to find a way to replace the stock symbol with a specific cell input?

Not sure what you mean stock symbol can be input with simple formula from list of tickers if that is what you require.

Hi Joe,

how is it possible to export the complete heat map e.g. for S&P500 to a google sheet?

I have found an example here, it should look like that: https://scontent-frx5-1.xx.fbcdn.net/v/t1.0-9/43599485_1954377207951210_9152938430081007616_o.jpg?_nc_cat=108&_nc_ht=scontent-frx5-1.xx&oh=54ab70c58ab513f0e5845ddd69a61c75&oe=5C4BFB2B

Greetings from Germany

Looks like the heat map from finviz and the charts are coming from stockcharts. That’s very cool. What is the original site? If anyone else knows how to do this please chip in. Might need to look over the Google docs or is this Excel?

I got this from german option traders, they use it for a morning routine before they start. I want to recreate and modify it. I don’t have the original file, only a picture how it looks like. I know that the heat map is from finviz but I don’t know how to integrate it into a google sheet.

I will inform you when I have found out how it works.

Hi Joe, as someone who has been looking for ways to adopt more quant driven strategies for momentum small cap trading, I appreciated your excellent post. I’ve also been downloading Finviz data after each market close, along with some corresponding fundamental data. Unfortunately, I failed to factor in the fact that today’s unanticipated market holiday of Nov. 5, would wipe out the previous day’s data. If there’s any way for you to share this information with me, I would highly appreciate it. I am happy to share my tracking data, which goes back to March 2017, if anyone is interested.

Sorry but I don’t have the data available, I also only track a small watchlist of tickers. Maybe someone else can help you, thanks!

Elie Litvin, I also have been tracking stocks everyday for the past 6 months off finviz. if you want to share your data with me I’ll share mine.

Here is a Google Sheet I created with Google FInance and Finviz data. It nearly complete. I would like also to get Industry/Sector for each company and RT Stock price data if possible without a subscription. If anyone can figure out how to get that data I’d appreciate knowing how. Thanks

https://docs.google.com/spreadsheets/d/16fxsDg14fhd9-NuyNaU2TUdUWbMvsGTmzZ0JKUBtUv8/edit?usp=sharing

Hi,
it seems that it is no longer working. Do you have an alternative for this?

Here it is…

=substitute(index(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&A3,”table”, 9),3,1),”*”,””)

Replace the two “B28” with the ticker symbol or cell

=IF(ISBLANK(B28),,index(SPLIT(SUBSTITUTE(index(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&B28,”table”, 9),3,1),”*”,””), “|”), 0, 1))

I tried to use that code, but it did not seem to work. i am trying to grab the sector and industry but it does not want to pull
=IF(ISBLANK(B4,,index(SPLIT(SUBSTITUTE(index(IMPORTHTML(“https://finviz.com/quote.ashx?t=”&B4,”table”, 9),3,1),”*”,””), “|”), 0, 1)))

Did you ever figure this out, Jason? Trying to do the exact same thing…

Thanks,
Brian

Jason,
This worked,
=SUBSTITUTE(index(IMPORTHTML(“https://finviz.com/quote.ashx?t=”&A13,”table”,9),3,1),”*”,””)

I got the the code below to work with Finviz.
I get all three entires, not sure how to split the output so I only get the first entry.
Maybe someone else does.

=(SUBSTITUTE(index(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&A2,”table”, 6),3,0),”*”,””))
Output: Technology | Consumer Electronics | USA

Hi Rohit,

I tried your link and I got an #Error!
I have been searching all night and haven’t found a formula to grab the sector and industry data from Finviz, Google Finance or Yahoo Finance.
Do you have any insight?

is there any site i can scrape or any method i can use to get historical p/e values for a stock? like P/E for ticker for past yr, 5 yrs, 10 yrs? or the avg p/e over past yr, 5 yrs, 10 yrs?

or same questions but for EPS? b/c obviously i can calc the P/E therefrom.

Free data is likely to be very sketchy. You could try quandl or intrinio.

It appears that a find and replace to remove the ‘*’ also removes the import formula for Finviz leaving the figure as static.

Hi Joe,
Did anyone find a solution to removing the asterisks in a importhtml function ?

Ron,
This worked for me, where “&A135 is the ticker symbol.
=SUBSTITUTE(index(IMPORTHTML(“https://finviz.com/quote.ashx?t=”&A135,”table”,11),8,2),”*”,””)

=MID(D2,2,LEN(D2)-2) where D2 is a finviz result like *2.40%*

May i know how do you know what is the right number of table to input? I saw some put 9 and some put 11?

It is table 11

Hi, how do you get rid of the “B” in the Finviz Market Cap # and convert that to its full numerical number? For example Apple Market cap is shown as “1024.03B”, how do I convert that into 1,024,030,000,000 etc?? Thanks

You got any solutions for this becay I stuck with same problem

would you have Scraping Stock Data From Finviz to excel formula?

Not sure what you mean.

Have successfully scraped dividend info from finviz for several months into a Google spreadsheet without any difficulties. Suddenly over the last few days the info is not available, at least the error in the cells is #N/A with the explanation “Could not fetch url: http://finviz.com/quote.ashx?t=ENB“. Do you, or anyone know what is causing this?

Some cells do populate correctly but, most don’t. This is the formula that I’ve been using without a problem: =SUBSTITUTE(index(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&AA3,”table”, 11),7,2),”*”,””). Thanks in advance for any guidance you might be able to provide.

“Could not fetch url: http://finviz.com/quote.ashx?t=ENB“
Having the same problem. Hopefully someone has a solution and we’re not being blocked by finviz.com.

Thanks Aaron! At least I know I’m not alone.

I have the same problem, so you are not alone

I’m having the same problem as well. I just recreated my sheet one row at a time and found that the formulas worked fine until I got to around 42 rows worth of data. Then some of the rows that were working fine glitched and returned #N/A. I’m thinking that something has happened at Google where Sheets are no longer able to load data when the # of rows or formulas get too voluminous. We always had this problem in large files, but if you waited long enough all of the rows would eventually get populated. But now it seems that this is no longer true and nothing works in these larger files.

I have no idea why one works and the other doesn’t but, this formula seems to work…at least for the time being. =if(AA3=””,””,split(index(importhtml(CONCATENATE(“https://finviz.com/quote.ashx?t=”,AA3,””),”table”,11),7,2),”*”))

Mark

Never mind, now it’s not working either.

the error appears because when you enter the finviz website, a pop up appears that forces you to accept the privacy terms
https://i.gyazo.com/d5693add43e83d1f043810fe3f3e2125.png
that is why the formulas do not reach the table
there is no solution for that

Aw, thanks for the explanation as I would have never figured it out. Back to the drawing board.

Hi – I am having the same problem. I did find a work-around where you can load data directly from the web into excel. Here is a link: https://www.youtube.com/watch?v=NdUZx_yyEqY

does anyone know how to scrape 65 day moving average of a stock from stockcharts.com, or any other website, to import to my excel spreadsheet?

Why not just calculate the moving average in excel?

I can, but for simplicity sake I just want today’s value from a chart, so I can then calc todays stock price % premium/discount from the 65dSMA in my spreadsheet. And want to know if I can pull data from stockcharts at all. Just trying to keep it as easy as possible, seeing as it is already calculated.

I see that finviz already calculates the stock’s % prem/disc to the 50dSMA in the table (table 11 is it ?).
would this be correct to import it into Google sheets?:
=index(IMPORTHTML(“https://finviz.com/quote.ashx?t=”&”GOOG”,”table”,11),12,6)

if anyone interested, this worked:

=index(IMPORTHTML(“http://finviz.com/quote.ashx?t=”&”GOOG”,”table”, 8),12,6)

Hi Folks,
as a result of the IMPORTHTML for table 11 I only get “[TABLE]” instead of the value. this is for every ticker symbol.
Any idea?
Thanks
Gregor

Hi, I am having a difficulty to import into google sheets dividend percentage for a title (ideally that I can assign the name from a table in Google sheet). Would you be able to help? All above methode give me error, thanks Ludek

Agreed. Have been looking for a few days now for a fix for this (finding dividends/share and dividend yield). Hoping someone smarter than me can figure it out for the rest of us 🙂

Im trying to pull data from a table just like in the example. The datapoint is on column 10, row 1.

=index(IMPORTHTML(“https://finviz.com/quote.ashx?t=”&B2,”table”,2),1,10) and index shows an error saying that the third index parameter for index can only be between 0 and 1 (inclusive).

How can I fix this? The AAPL example is using a 2 for the third parameter…

Table 11 now refers to the Buy/Sell ratings, so the example in your article doesn’t work.

Can you post a new example of something from the main statistical table? Trying to figure it out now. Thanks.

It turns out the table you want is now 8, and the numbering is a little different. Here’s an example to get EPS next quarter, =INDEX(IMPORTHTML(“http://finviz.com/quote.ashx?t=AAPL”, “table”, 8),3,6)

Thanks very much for this–I was just trying to figure out what had happened and this comment saved me from going down the wormhole.

None of these ImportHTML formulas posted here in comments are working for me. Did Finviz just block it entirely?

doesnt work for me either. Anyone?

Please see comments above by Nunn and check article. The table in Finviz has changed to table 8.

For some reason cell says #REF!
And says: Function INDEX parameter 2 value is 2. Valid values are between 0 and 1 inclusive.

Any idea why and how to solve?

Hi Joe

just wondering if you think it’s possible to scrape the top 10 daily top % change stocks, in other words to scrape an actual screener result on Finviz? My hope would be to have a sheet that automatically scrapes the top 10 or so ticker symbols from a search and then later on automatically updates their daily change % too. I’ve been struggling to find any resources to explain how to do this, I’m very new to basic programming and data scraping.

Thanks

Data table is now table 7.

Leave a comment