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!

You may also like...

13 Responses

  1. Murray says:

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

  2. wpace46 says:

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

  3. R says:

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

  4. cvan says:

    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.

  5. richard says:

    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 says:

      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 says:

        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.

        • Ann says:

          Eoddata has been around for several years. I used them for several years before I switched to to the Yahoo! Finance addin. I will be using them again.

          • imxuwang says:

            I am also checking EOD, but not sure how long their history is. EOD’s domain registered last year.

  6. Andrew MacLean says:

    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.

  7. Tom Evers says:

    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

  8. Trish McConnell says:

    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

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.