5-minute stock screener w/ google spreadsheets & yahoo finance

The great free tools keep getting better.  Here I’m Google Docs and Yahoo finance to set up a stock screen.

In Google Spreadsheets, the =importData() function lets you pull data from various sources such as xml, html, csv, RSS and Atom feeds, etc.

For this screener example I pulled a .csv into this function:

=importData(“URL”)

  • URL = the URL of the CSV or TSV file. This imports a comma- or tab-separated file.

It just so happens that Yahoo lets you pull data as RSS or CSV via HTTP GET: start with a base URL and then add parameters and values after a question mark (?). For the company news RSS feed there is only one parameter: s for the ticker symbol(s) to search for.

http://finance.yahoo.com/rss/headline?s=ticker(s)

Ticker symbols are case insensitive. You can separate multiple ticker symbols with a comma:

http://finance.yahoo.com/rss/headline?s=jci,epd,xom

In this case Yahoo! Finance returns an RSS 2.0 feed in response to this request. You can subscribe to the request URL.  Similarly, Yahoo! Finance’s lets you get all kinds of stock data back in a CSV file.  The google docs function can work with the csv to build our screener.  Here are some useful tags we can can specify to get what we’re after:

a Ask k5 % Change from 52-W
a2 Average Daily Volume l Last Trade with Time
a5 Ask Size l1 Last Trade (Price Only)
b Bid l2 High Limit
b2 Ask (RT) l3 Low Limit
b3 Bid (RT) m Day’s Range
b4 Book Value m2 Day’s Range (RT)
b6 Bid Size m3 50-Day MA
c % Change m4 200-D MA
c1 Change m5 Change from 200-D MA
c3 Commission m6 % Change from 200-D MA
c6 Change (RT) m7 Change from 50-D MA
c8 After Hour Change (RT) m8 % Change from 50-D MA
d Dividend/Sahre n Name
d1 Last Trade Date n4 Notes
d2 Trade Date o Open
e Earning/Share p Previous Close
e1 Error Indication p1 Price Paid
e7 EPS Estimate Current Year p2 Change in %
e8 EPS Estimate Next Year p5 Price/Sales
e9 EPS Estimate Next Quarter p6 Price/Book
f6 Float Shares q Ex-Dividend Date
g Day’s Low r P/E Ratio
h Day’s High r1 Dividend Pay Date
j 52-Week Low r2 P/E Ratio (RT)
k 52-Week High r5 PEG Ratio
g1 Holding Gain % r6 Price/EPS Estimate Current Year
g3 Annualized Gain r7 Price/EPS Estimate Next Year
g4 Holding Gain s Symbol
g5 Holding Gain % (RT) s1 Shares Owned
g6 Holding Gain (RT) s7 Short Ratio
i More Info t1 Last Trade Time
i5 Order Book (RT) t6 Trade Links
j1 Market Capitalization t7 Ticker Trend
j3 Market Cap (RT) t8 1 Year Target
j4 EBITDA v Volume
j5 Cahnge from 52-W Low v1 Holding Value
j6 % Change from 52-W Low v7 Holding Value (RT)
k1 Last Trade with Time (RT) w 52-W Range
k2 Change % (RT) w1 Day’s Value Change
k3 Last Trade Size w4 Day’s Value Change(RT)
k4 Change from 52-W High x Stock Exchange
y dividend Yeild

My screener in Google spreadsheets:

=importData(“http://finance.yahoo.com/d/quotes.csv?s=IBM+VGR+CTL+LLY+SPH+AGNC+ED+NLY+SMTX+IID+BKH+EPD… +cni+ino+io+ohi&f=snw1va2rdqy”)

Note the format string:

snw1va2rdqy

It returns, in this order, the following values per stock queried:

  1. Symbol
  2. Name
  3. Change
  4. Volume
  5. Average Daily Volume
  6. P/E Ratio
  7. Dividend
  8. Ex-Dividend Date
  9. Yield

Resulting 5-minute screener: http://bit.ly/du7KZj

Advertisements

Author: rdubnick

I'll get back to you on this...

8 thoughts on “5-minute stock screener w/ google spreadsheets & yahoo finance”

  1. Great idea, and very creative.
    I will try it shortly.

    Can you tell me just where does Yahoo keep this list of attributes?
    At this point, I see that among others, they also have Free Cash Flow, and Institutional Ownership available online at their site, but I’d prefer to do as you have done above.

    Thank you,
    Sei

      1. THANK YOU…Very much.

        I have asked this question of Yahoo Finance Support, and received nothing but a stream of disconnected response, none of which had value.

        I did go to the link you provided, and it is exactly what I am looking for, EXCEPT, it appears to be an older version and does not contain FREE CASH FLOW, and INSTITUTIONAL OWNERSHIP.
        These are two items that are central to my strategy and screening.

        If I could prevail upon you again, do you know where I might be able to locate those attributes and symbols (tags)?

        Thank you again for your original response.
        Sei.

  2. This is a great spreadsheet you have created. Do you have an updated version? Would you be willing to share it (i.e., allow me to copy it as a starting point for my own analysis)?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s