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: The formula works as follows:

• ImportHtml - The function we are using in google sheets
• A2 - The cell where the ticker symbol is (ie: GOOGL, AAPL) can point to any cell.
• table - The type of html element we are importing.
• 0 - This number represents which table we are importing. In this case, it is the first table.
• 2,2 - Row and Column. In this case, we are importing the 2nd row, 5th column.

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: 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: The formula works as follows:

• ImportHtml - The function we are using in google sheets
• A2 - The cell where the ticker symbol is (ie: GOOGL, AAPL) can point to any cell.
• table - The type of html element we are importing.
• dateindex = 1 - The dateindex gives you the expiration time of the option. =1 means the next periods option. Dateindex=2,3 will be the next period and then the 3rd and so on. =-1 lists all time periods. =-3 near term.
• money=all - This variable decides how near the money the options are. All=all contracts, In = In the money, out=out of the money, leaving the field out gets you near the money.
• 0 - This number represents which table we are importing. In this case, it is the first table.
• 2,2 - Row and Column. In this case, we are importing the 2nd row, 4th column.

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