How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too) Part II

How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too) Part II

You’re probably here because the old yahoo finance query does not work in Excel anymore. As you can read in the updates section of my previous post How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too), the issue is Yahoo changing the URL and probably blocking Excel from accessing it. This post is going to focus in using another Yahoo service though building the URL to make the API call is different so better to start a new post. I’ll be doing a Part III regarding using Google instead. The key thing to note here is that though this will give us a treasure of information, we won’t have access to the historical data from what I can see. So go to Part III on how to use Google Finance if you need historical data.

So let’s get started:

So far, so good. Seems that we can get a csv using a simple URL, and the parameters are mostly the usual suspects. We can observe the following format:

https://download.finance.yahoo.com/d/quotes.csv?s={SYMBOLS}&f={DATA THAT WE WANT}

https://finance.yahoo.com/d/quotes.csv?s={SYMBOLS}&f={DATA THAT WE WANT}

the last part (&e=.csv) seems redundant considering we are using quotes.csv so it seems like we can drop it.

Next, note that we are able to use one or more Symbol. This means you can query up to 200 different symbols! For example:

http://finance.yahoo.com/d/quotes.csv?s=GOOGL,AAPL,MSFT,FB&f=sabo

So far, so good. Now comes the fun part. Like I mentioned earlier, there is a treasure of information available; all you need is knowing how to ask and Yahoo to be willing to give it to us for free still. So, here are the different letters we use with the f parameter to indicate what data we want back:

Pricing

• a – ask
• b – bid
• b2 – ask (realtime)
• b3 – bid (realtime)
• p – previous close
• o – open

Dividends

• y – dividend yield
• d – dividend per share
• r1 – dividend pay date
• q – ex-dividend date

Date

• c1 – change
• c – change & percentage change
• c6 – change (realtime)
• k2 – change percent
• p2 – change in percent
• d1 – last trade date
• d2 – trade date
• t1 – last trade time

Averages

• c8 – after hours change
• c3 – commission
• g – day’s low
• h – day’s high
• k1 – last trade (realtime) with time
• l – last trade (with time)
• l1 – last trade (price only)
• t8 – 1 yr target price
• m5 – change from 200 day moving average
• m6 – percent change from 200 day moving average
• m7 – change from 50 day moving average
• m8 – percent change from 50 day moving average
• m3 – 50 day moving average
• m4 – 200 day moving average

Misc

• w1 – day’s value change
• w4 – day’s value change (realtime)
• p1 – price paid
• m – day’s range
• m2 – day’s range (realtime)
• g1 – holding gain percent
• g3 – annualized gain
• g4 – holdings gain
• g5 – holdings gain percent (realtime)
• g6 – holdings gain (realtime)
• t7 – ticker trend
• t6 – trade links
• i5 – order book (realtime)
• l2 – high limit
• l3 – low limit
• v1 – holdings value
• v7 – holdings value (realtime)
• s6 – revenue

52 Week Pricing

• k – 52 week high
• j – 52 week low
• j5 – change from 52 week low
• k4 – change from 52 week high
• j6 – percent change from 52 week low
• k5 – percent change from 52 week high
• w – 52 week range

Symbol Info

• v – more info
• j1 – market capitalization
• j3 – market cap (realtime)
• f6 – float shares
• n – name
• n4 – notes
• s – symbol
• s1 – shares owned
• x – stock exchange
• j2 – shares outstanding

Volume

• v – volume
• a5 – ask size
• b6 – bid size
• k3 – last trade size
• a2 – average daily volume

Ratios

• e – earnings per share
• e7 – eps estimate current year
• e8 – eps estimate next year
• e9 – eps estimate next quarter
• b4 – book value
• j4 – EBITDA
• p5 – price / sales
• p6 – price / book
• r – P/E ratio
• r2 – P/E ratio (realtime)
• r5 – PEG ratio
• r6 – price / eps estimate current year
• r7 – price /eps estimate next year
• s7 – short ratio

Impressed? I am. But wait, there is more! You can access data from a number of exchanges around the world. Here is the list:

Exchanges available via Yahoo Finance

Hope this helps and Yahoo keeps it available for people to use around the world!