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")
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:
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 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.
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:
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.
You can verify this is correct by looking at the market cap shown on the Finviz web page for Apple:
Let’s see another example.
Try typing the following formula and you will get the Apple earnings date.
We are now simply importing the data from row 11, column 6 in the same table.
If you had wanted the PE you would have gone for row 1, column 4.
If you reference the ticker from a separate cell such as A2 you can build up the sheet with different stocks.
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.
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!