May 20

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:

Data Source Type: REST/URL
Sample Query This example query uses Yahoo Finance quote properties for NASDAQ:

https://download.finance.yahoo.com/d/quotes.csv?s=^IXIC&f=nsl1opc1p2&e=.csv

Important! Yahoo Finance API does not support all the stock symbols.

 

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}

or

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

Country Exchange Suffix Delay Data Provider
United States of America American Stock Exchange N/A 15 min Direct from Exchange
United States of America BATS Exchange N/A Real-time Direct from Exchange
United States of America Chicago Board of Trade .CBT 10 min Interactive Data Real-Time Services
United States of America Chicago Mercantile Exchange .CME 10 min Interactive Data Real-Time Services
United States of America Dow Jones Indexes N/A Real-time Interactive Data Real-Time Services
United States of America NASDAQ Stock Exchange N/A 15 min Direct from Exchange
United States of America New York Board of Trade .NYB 30 min Interactive Data Real-Time Services
United States of America New York Commodities Exchange .CMX 30 min Interactive Data Real-Time Services
United States of America New York Mercantile Exchange .NYM 30 min Interactive Data Real-Time Services
United States of America New York Stock Exchange N/A 15 min Direct from Exchange
United States of America OTC Bulletin Board Market .OB 20 min Direct from Exchange
United States of America Pink Sheets .PK 15 min Direct from Exchange
United States of America S & P Indices N/A Real-time Interactive Data Real-Time Services
Argentina Buenos Aires Stock Exchange .BA 30 min Interactive Data Real-Time Services
Austria Vienna Stock Exchange .VI 15 min Telekurs
Australia Australian Stock Exchange .AX 20 min Interactive Data Real-Time Services
Belgium Brussels Stocks .BR 15 min
Brazil BOVESPA – Sao Paolo Stock Exchange .SA 15 min Interactive Data Real-Time Services
Canada Toronto Stock Exchange .TO 15 min Interactive Data Real-Time Services
Canada TSX Venture Exchange .V 15 min Interactive Data Real-Time Services
Chile Santiago Stock Exchange .SN 15 min Interactive Data Real-Time Services
China Shanghai Stock Exchange .SS 30 min Interactive Data Real-Time Services
China Shenzhen Stock Exchange .SZ 30 min Interactive Data Real-Time Services
Denmark Copenhagen Stock Exchange .CO 15 min Telekurs
France Euronext .NX 15 min Telekurs
France Paris Stock Exchange .PA 15 min Telekurs
Germany Berlin Stock Exchange .BE 15 min Telekurs
Germany Bremen Stock Exchange .BM 15 min Telekurs
Germany Dusseldorf Stock Exchange .DU 15 min Telekurs
Germany Frankfurt Stock Exchange .F 15 min Telekurs
Germany Hamburg Stock Exchange .HM 15 min Telekurs
Germany Hanover Stock Exchange .HA 15 min Telekurs
Germany Munich Stock Exchange .MU 15 min Telekurs
Germany Stuttgart Stock Exchange .SG 15 min Telekurs
Germany XETRA Stock Exchange .DE 15 min Telekurs
Hong Kong Hong Kong Stock Exchange .HK 15 min Interactive Data Real-Time Services
India Bombay Stock Exchange .BO 15 min Interactive Data Real-Time Services
India National Stock Exchange of India .NS 15 min National Stock Exchange of India
Indonesia Jakarta Stock Exchange .JK 10 min Interactive Data Real-Time Services
Israel Tel Aviv Stock Exchange .TA 20 min Telekurs
Italy Milan Stock Exchange .MI 20 min Telekurs
Japan Nikkei Indices N/A 30 min Interactive Data Real-Time Services
Mexico Mexico Stock Exchange .MX 20 min Telekurs
Netherlands Amsterdam Stock Exchange .AS 15 min Telekurs
New Zealand New Zealand Stock Exchange .NZ 20 min Interactive Data Real-Time Services
Norway Oslo Stock Exchange .OL 15 min Telekurs
Portugal Lisbon Stocks .LS 15 min
Singapore Singapore Stock Exchange .SI 20 min Interactive Data Real-Time Services
South Korea Korea Stock Exchange .KS 20 min Interactive Data Real-Time Services
South Korea KOSDAQ .KQ 20 min Interactive Data Real-Time Services
Spain Barcelona Stock Exchange .BC 15 min Telekurs
Spain Bilbao Stock Exchange .BI 15 min Telekurs
Spain Madrid Fixed Income Market .MF 15 min Telekurs
Spain Madrid SE C.A.T.S. .MC 15 min Telekurs
Spain Madrid Stock Exchange .MA 15 min Telekurs
Sweden Stockholm Stock Exchange .ST 15 min Telekurs
Switzerland Swiss Exchange .SW 30 min Telekurs
Taiwan Taiwan OTC Exchange .TWO 20 min Interactive Data Real-Time Services
Taiwan Taiwan Stock Exchange .TW 20 min Interactive Data Real-Time Services
United Kingdom FTSE Indices N/A 15 min Telekurs
United Kingdom London Stock Exchange .L 20 min Telekurs

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

11 comments

Skip to comment form

    • Murray on May 21, 2017 at 10:50 pm
    • Reply

    Hello again Juan,
    Could you please provide your link to part III (referred above para I) for the historic data
    Thank you
    Regards

  1. Juan, have you posted Part III regarding the use of Goggle Finance?

    1. Sorry, not yet… work’s been hectic. This is an example URL but I need to dive in to see what other options are there besides date range: https://www.google.com/finance/historical?output=csv&q=AAPL&startdate=May+22%2C+2015&enddate=May+21%2C+2017. Hope this helps for now.

    • R on May 25, 2017 at 7:48 am
    • Reply

    Hi Juan,
    Can you post a link example for a single equity (say:AAPL) historical daily base, including adjusted prices for a year?

    • cvan on May 26, 2017 at 11:57 pm
    • Reply

    hmm does not seem to be working for India index and stock. I used ^NSEI and INFY.NS with l1 as (f). I am not getting any results.

    • richard on June 7, 2017 at 10:25 am
    • Reply

    Thank you for the work you have done. I played with the Google site and didn’t see any options other than the date range. This issue I found with Google and this remaining Yahoo interface is won’t provide data on Mutual Funds. The Yahoo site that was shutdown did. Any thoughts on options if you want historical pricing on a Mutual Fund?

      • deios on June 10, 2017 at 9:00 am
      • Reply

      Yes, you can use https://eodhistoricaldata.com/ they provide Mutual Funds historical data + adjusted closes for them. And actually, it’s easy to switch from Yahoo Finance to EOD. Because they have very similar API, and basically, you just need to change url from ichart.finance… to eodhistoricaldata.com. That’s easy!

        • richard on June 10, 2017 at 10:50 am
        • Reply

        With regards to eodhistoricaldata.com on the yahoo blog there were individuals questioning the validity of this site. Questions were: site appeared right around yahoo interface termination; ownership of site; source of data. This site needs to clear up those questions before I would register with them.

    • Andrew MacLean on June 16, 2017 at 6:39 pm
    • Reply

    For historic data I have created a VBA/Excel workaround, a function call which takes the old ichart URL, converts it into something that works then fetches the data. Go to signalsolver.com and follow the menu to EmulateURL. Its free.

    • Tom Evers on June 27, 2017 at 2:20 pm
    • Reply

    there is probably a way to specify the historical range, but you can get a year of historical data like so…

    https://finance.yahoo.com/quote/goog/history?p=goog

    • Trish McConnell on November 22, 2017 at 11:06 am
    • Reply

    This worked at the beginning of November 2017 when I used it in Excel. On November 20 when I came back to my spreadsheet, I get a log in error to the web service of “restricted access”. It looks like Yahoo has restricted the service. I’m hoping they open it back up

Leave a Reply

%d bloggers like this: