Note: This article was originally published in 2017. Some steps, commands, or software versions may have changed. Check the current Yahoo Finance documentation for the latest information.
In this step-by-step guide, you’ll learn 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 (http://kx.cloudingenium.com/content-providers/yahoo/yahoo-finance/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– askb– bidb2– ask (realtime)b3– bid (realtime)p– previous closeo– open
Dividends
y– dividend yieldd– dividend per sharer1– dividend pay dateq– ex-dividend date
Date
c1– changec– change & percentage changec6– change (realtime)k2– change percentp2– change in percentd1– last trade dated2– trade datet1– last trade time
Averages
c8– after hours changec3– commissiong– day’s lowh– day’s highk1– last trade (realtime) with timel– last trade (with time)l1– last trade (price only)t8– 1 yr target pricem5– change from 200 day moving averagem6– percent change from 200 day moving averagem7– change from 50 day moving averagem8– percent change from 50 day moving averagem3– 50 day moving averagem4– 200 day moving average
Misc
w1– day’s value changew4– day’s value change (realtime)p1– price paidm– day’s rangem2– day’s range (realtime)g1– holding gain percentg3– annualized gaing4– holdings gaing5– holdings gain percent (realtime)g6– holdings gain (realtime)t7– ticker trendt6– trade linksi5– order book (realtime)l2– high limitl3– low limitv1– holdings valuev7– holdings value (realtime)s6– revenue
52 Week Pricing
k– 52 week highj– 52 week lowj5– change from 52 week lowk4– change from 52 week highj6– percent change from 52 week lowk5– percent change from 52 week highw– 52 week range
Symbol Info
v– more infoj1– market capitalizationj3– market cap (realtime)f6– float sharesn– namen4– notess– symbols1– shares ownedx– stock exchangej2– shares outstanding
Volume
v– volumea5– ask sizeb6– bid sizek3– last trade sizea2– average daily volume
Ratios
e– earnings per sharee7– eps estimate current yeare8– eps estimate next yeare9– eps estimate next quarterb4– book valuej4– EBITDAp5– price / salesp6– price / bookr– P/E ratior2– P/E ratio (realtime)r5– PEG ratior6– price / eps estimate current yearr7– price /eps estimate next years7– 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! |
Summary
You’ve successfully learned obtain historical stock prices from yahoo finance (you can query them via excel too) part ii. If you run into any issues, double-check the prerequisites and ensure your Yahoo Finance environment is properly configured.