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 a Google spreadsheet. This is incredibly easy and it’s free.
Step One – Open Up A Google Sheet
The first step is to open up a new Google spreadsheet and then you can connect it with Google Finance. You will need a Google account for this if you don’t already have one.
Step Two – Import Quotes From 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”, “1/1/2013”, “12/31/2017”, “DAILY”)
Hit enter and your spreadsheet will automatically populate with the data like below:
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)
Step Three – Extract Finviz Data
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 also 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.
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 11 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 11 and we get $1024.03 billion.
You can verify this by looking at the market cap shown on the web page:
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 6.
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 also quickly run through various different stocks.
What To Do With The Data
Web scraping 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.
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!
Thank You For Reading
Joe Marwood is an independent trader and the founder of Decoding Markets. He worked as a professional futures trader and has a passion for investing and building mechanical trading strategies. If you are interested in more quantitative trading strategies, investing ideas and tutorials make sure to check out our program Marwood Research.
This post expresses the opinions of the writer and is for information or entertainment purposes only. It is not a recommendation or personalised investment advice. Joe Marwood is not a registered financial advisor or certified analyst. The reader agrees to assume all risk resulting from the application of any of the information provided. Past performance, historical or simulated results are not a reliable indicator of future returns and may not account for real world settings. Financial trading is full of risk and margin trading can lead to financial losses totalling more than what is in your investment account. We take care to present accurate analysis but mistakes in backtesting and presenting of analysis regularly occur. Please read the Full disclaimer.
Thank you to everyone who takes the time to leave a comment. Your feedback, constructive criticism and identification of mistakes is welcome. In order to concentrate on work I may not have time to respond to all comments.