How to Import Nasdaq Stock and Options Data into a Google Spread Sheet

Published on: 2017-05-15 Last updated on: 2017-08-11

With Yahoo changing their site design, there is now a void to import data into sheets for the small personal investor. This guide will help you get some of the stock and options data from the Nasdaq website.
 

Getting Stock Data

The best way to get Nasdaq stock data is to use Google sheets import html function. The html function looks as follows:
=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),2,5)
The formula works as follows: If you want to import the whole table, then just get rid of the "index" part of the formula, and the specific row and column. As such:
=importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0)
Below is all the data we can get from the Nasdaq for now. On the plus side, the Nasdaq seems much more stable than Yahoo was. If you don't want to use the tables below, you can also try to copy and paste from this example google sheet.
 

Summary Data

1 Year Target=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),2,2)
Share Volume=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),4,2)
50 Day Average Share Volume=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),5,2)
Previous Close=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),6,2)
Market Cap=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),8,2)
P/E=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),9,2)
Forward P/E (1 year)=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),10,2)
Earnings per Share (EPS)=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),11,2)
Dividend=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),12,2)
Expected Dividend Date=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),13,2)
*If you just see a number, click "Format">>"Number">>"Date"
Dividend Payment Date=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),14,2)
*If you just see a number, click "Format">>"Number">>"Date"
Current Yield=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),15,2)
Beta=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),16,2)
Nasdaq Open Price=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),17,2)
Date of Open Price=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),18,2)
*If you just see a number, click "Format">>"Number">>"Date"
Nasdaq Close Price=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),19,2)
Date of Close Price=index(importhtml("http://www.nasdaq.com/symbol/"&$A$2,"table",0),20,2)
*If you just see a number, click "Format">>"Number">>"Date"

 

Getting Option Data

You can get option data in much the same way as stock data, using Google sheets import html for the table. The formula looks like this:
=index(importhtml("http://www.nasdaq.com/symbol/aapl/option-chain?dateindex=1&money=all","table",0),2,4)
The formula works as follows: With the options you have to import the whole table, and then use trial and error to find the row and column for whatever variable you want. I.E. Call bid, Put Bid, etc... check out the linked Google Sheet to get started. Click File >> Make a Copy to work with your own copy. See the example google sheet for nasdaq options here.

Comments