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

You may also like...

46 Responses

  1. wherestjb says:

    Thanks! Can this be done to get historical market cap on a particular date?

  2. Jean-Christophe Emery says:

    Hi,

    How should I modify the command in order to get EPS?
    I was thinking I simply have to add &f=e but it doesn’t work.
    Any advice?

    Thanks

  3. Lindsey Lee says:

    Useful tool. Thanks for sharing. How do I modify the code in order to download only the adjusted close?

  4. Raj K says:

    How often do they update the quotes for stocks & mutual funds? Daily or Weekly?

  5. David says:

    Hi Juan,

    I’ve been doing this for years, but my download tool stopped working today. Clicking the URL you constructed ( https://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv ) results in this error message:

    “Our engineers are working quickly to resolve the issue.”

    But I wonder if Yahoo has permanently moved on to a completely different way of constructing its URLs. I just went to their website and to download the same AAPL data as in your example ( time period Jan 01, 2010 – Dec 31, 2015), it seems like they are now using lengthy URLS like these:

    https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1262329200&period2=1451545200&interval=1d&events=history&crumb=CFU1WAYAsDu, https://finance.yahoo.com/quote/AAPL/history?period1=1262329200&period2=1451545200&interval=1d&filter=history&frequency=1d

    Help! I have no idea how to manually construct a URL like this. Can you figure it out?

  6. mbmarx says:

    Guys the Icharts link not supported by Yahoo anymore when you click the link you get the page we are working on this and we come back soon .Any idea any other source can be used for historical data ;reply me back to
    m b ma [email protected] get out the space in the email

  7. jas says:

    I think currently the link is not working.

  8. David says:

    Hi Juan,

    I’ve been doing this for years, but my download tool stopped working yesterday. Clicking the URL you constructed ( https://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv ) results in this error message:

    “Our engineers are working quickly to resolve the issue.”

    But I wonder if Yahoo has permanently moved on to a completely different way of constructing its URLs. I just went to their website, and to download the same AAPL data as in your example ( time period Jan 01, 2010 – Dec 31, 2015), it seems like they are now using lengthy URLs like these:

    https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1262329200&period2=1451545200&interval=1d&events=history&crumb=CFU1WAYAsDu, https://finance.yahoo.com/quote/AAPL/history?period1=1262329200&period2=1451545200&interval=1d&filter=history&frequency=1d

    Help! I have no idea how to manually construct a URL like this. Can you figure it out?

    • Juan Carlos says:

      I am still looking around for alternatives but it seems Yahoo is making it if not impossible, very difficult to use their data via Excel. Might I suggest migrating over to Google? You can do a query like this one: https://www.google.com/finance/historical?output=csv&q=AAPL&startdate=May+22%2C+2015&enddate=May+21%2C+2017

      • Murray says:

        Hello Juan,
        Nice website/blog. Damn Yahoo!
        This google code works.
        Are you able to throw it into a rough macro, please, so users can play with it as an alternative to Yahoo.
        I wanted Australian stocks/Index but it appears it doesn’t want to stray outside the US
        I have emailed Barchart to see if they want to grant free access to historical pricing
        Thank you
        Murray

      • Murray says:

        Ok. I have managed to get historical data from “the someone else” (before they shut it down too!).
        But it is messy because they appear to be using unix date stamping and stock codes (which they get from “6” – a Swiss data supplier)
        Can post excel vba code here when working, for those interested.

      • Jim says:

        This works perfectly! I’ve used it on a handful of stocks without any issues. However, America Movil (AMX), does not work when I plug that symbol in. It is a Mexican stock. When I go to Google finance and manually copy the link from the download to excel link it provides this:

        http://www.google.com/finance/historical?q=NYSE%3AAMX&ei=O5glWZD0HZOLecPlgbAJ&output=csv

        The stock exchange is listed in this example but I don’t think that should matter since other stocks that have worked also provide the stock exchange. I’m just wondering if you know in Google that there is a certain format for maybe foreign stocks that you need to follow?

        • Murray says:

          Google uses this format: Exchange & “:” & Stock Code. eg ASX:TLS to get Telstra from Australian exchange
          Also Google uses specific CID numbers to describe stocks. I can not find a table of these codes but they are Stock unique.
          Go to Google finance Historic prices for your stock selection. Then copy the link. Also you need to change the dates on the web page and you will see a number in the link (before link copy). This is the number of history days. It appears to be limited to around 12 months for daily. Also there is no Volume data for non US stocks.

      • Nina says:

        Thanks Juan Carlos your google link gives me the data I need. When I try to change the ticker to reference a cell with the ticker it no longer works. Do you know how to reference a cell for the ticker and date?

      • Nina says:

        Thanks for the info Juan. It works nicely. Do you know if you can change the ticker and date to reference a cell instead of having them directly in the link?

        https://www.google.com/finance/historicaloutput=csv&q=AAPL&startdate=May+22%2C+2015&enddate=May+21%2C+2017

  9. Raj K says:

    I found that download.finance.yahoo.com still works but I am not sure how to get the price for a specific date..

    https://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=sl1

  10. Martin says:

    From YAHOO.com Historical Data, right clicked on “Download Data” and selected “Copy shortcut” to get the URL and crumb. I added this to my Excel download and I can build working URLs (that download the data when I manually copy the URL to my browser). When Excel runs the request, it fails:
    Description : Unable to open (url). Cannot download the information you requested.
    HelpContext : 1001004
    HelpFile : “xlmain11.chm”
    Number : 1004

    YAHOO Changes:
    Date range is now specified by period1 and period2, as Seconds Since 1970 = (days since 1/1/1970) * 24 * 60 * 60.
    The .csv file is no longer quote.csv, it is now ticker.csv, as in: YHOO.csv
    Also, the download .csv data is in reverse date order. This can be solved by sorting.
    The column order has changed, it is now:
    Date Open High Low Close Adj Close Volume
    I may compute my own adjusted values.
    Still working on the solution.
    Yahoo please document how we can resume this very beneficial service.

    • Juan Carlos says:

      Keep in mind the crumb is session specific. In other words, the crumb you get in Internet Explorer won’t work in Chrome or Excel even if it is the same computer. To make matters worse, I can’t open yahoo finance from within Excel. My guess is that they blocked any browser that identifies itself as Excel to avoid people from using their API directly from within Excel.

      • Dravo says:

        I have been working for years with this URL until it stopped working, first on mobile laptops, and since May 20 on my desktop.
        The Google site works for me, but (1) it only downloads 4000 trading days at a time (about 16 years) and (2) no splits and dividends, so no Adjusted Prices.
        To calculate portfolio weighting, in my opinion you need more than 20 years. I usually use 30.
        The old ichart site got its data from CSI, the new query (see David) gets its data from Reuter. I use that now and it works for me, but not in VBA.
        If you are right that the new site doesn’t want to communicate with Excel, then VBA is not an option, but VBS and Delphi will be. VBS is pretty close to VBA as a programmer’s language but has some limitations.
        You can call VBS scripts from Excel’s VBA if you want.
        I found this site very helpful!
        Thanks!

  11. Allen says:

    We all need to reach out to Yahoo for this fix. They are the leaders in Financial information (for the retail investor) – it seems they still are very active in their financial reporting…ITS THE ONLY THING THEY’RE GOOD AT. If their icharts API isn’t going to work, we’ll find a solution around it, but I’ll be damned if I ever go to their website again until this is fixed.

  12. Thanks for the update! I was wondering why my application all the sudden quit working. lol Thanks Yahoo!

  13. Hui says:

    I was able to read (programmatically) a value from the historical data page source after a string “CrumbStore” and get the crumb value. Then I used this value to construct the URL. Strangely it still returns me an http 401. When I tried it on a browser, whatever crumb value is on the page, that would be the value in the download link URL. I don’t know what’s going on there. Anyone knows better?

  14. Hui says:

    I was able to parse the historical data page to get a crumb value — this is something after a string “CrumbStore” in page source. But when I used it to construct a URL to download data programmatically, I still get an http 401 error. On a browser, the crumb store value is exactly the same as the one in csv download link

  15. Hui says:

    I eventually got it working. First crawl the historical price page to get the crumb value, and then send the second request with the same cookie as the crawl page request, so the session is still valid and crumb is valid as well. I am doing this with Java but I think people can achieve the same with Excel (I am not an expert on Excel though)

    • Juan Carlos says:

      Yes, the crumb value is session specific (which means is unique in each computer & browser combination.) As you point out, if you access the page first and obtain the crumb, you can use that subsequently with no problems. With Excel though, I could not even access the site via the excel web browser. My guess is that Yahoo is blocking it via de use of the user agent string on the browser. Not sure if you can change that on Excel so that Yahoo doesn’t find out.

      • Dravo says:

        You can access it through Excel VBA by setting the Internet Explorer object (Set IE = CreateObject(“InternetExplorer.Application”) and navigating to the source as defined by David and Martin (the query URL with the crumb). When you set IE = CreateObject (“MSXML2.serverXMLHTTP”) and try to get the data by an Open/Get and Send call, you understandably get the unauthorized access message. When navigating the Internet Explorer to the query URL, the download window appears from which you have to determine the window handle (working with SendKeys is not always reliable). In that parent window, you have to determine the handle of the child window with the caption “Save”, “Open”, etc. whatever you want. Once you have that handle, you can give an automated click and “Close” the window.
        Hope this helps. I believe in Java, Delphi, etc. you do it similarly. You have to download the Dividends and Splits separately if you are interested in calculating the Adjusted Closes yourself. I find that an improvement over the old ichart site. The relevant URL’s with Crumbs are found in a similar way.
        When the dust is settled, we will find that multi-threaded downloading of 25 stocks at a time is still possible using VBS (the “Bee swarm” technique) or another programming environment that, unlike VBA, allows for multi-threading.
        I read somewhere in a related thread about calculating portfolio weighting by optimizing the Curtosis. Can someone help me with some references?
        Thanks.

      • Dravo says:

        Juan Carlos,

        If the crumb value were unique for each pc & browser, it would mean it is also browser dependent. That would imply that automated downloading through CreateObject (“MSXML2.serverXMLHTTP”) and the-like is not possible as these techniques are browser independent, and, hence, a factor of two to three faster. That does seem to contradict Hui’s last post and the experience that I see in my own programming environment, even in VBA and VBS.

        Although manual and automated downloading and/or opening the csv-file is still possible and more reliable, scraping the site is much faster and works like a charm, faster and simpler than with the old site, still providing Date-O-H-L-C-V-AdjC plus dividends and splits, all within the same scrape.

        In the end, I find the new site more versatile than before, both for manual and automated download, given Martin’s directions for using period1 and period2.

  16. deios says:

    Unfortunately, the project ichart.finance.yahoo.com is dead. They closed it and it seems it will not be opened. But I found very nice and cheap alternative: https://eodhistoricaldata.com/. Works with CSV, no need to parse JSON and other complex things.

  17. Grateful for deios’s comment. eodhistoricaldata.com is great, only $4.99/mo and I have signed up.
    What an example of the free market! Yahoo created a problem, for whatever reason. And eod stepped in and solved it. Thanks also to Juan Carlos.

  18. Mike says:

    For those of you that have used the free Yahoo stock download server URLs for years but then noticed that is hasn’t worked for the past 2 months (a lot of people are upset as their software is built around this service) … here is an alternate Yahoo URL (the Yahoo finance stock history page that allows you to “manually” download the data) – works great (see details in image). Another guy mentioned it above – it works fine for me so I encourage others to use it as well.

    See my Twitter post on this (with an image attached with details since I can’t attach an image here) – https://twitter.com/AIStockForecast/status/871464610746036225

    The details in the image will explain how to customize the URL (company symbol, start and end dates, frequency) – then you can automatically download the data in .csv format (via your browser). Even if the URL end portion changes depending on the session – you can simply get the end part of the URL by going to the website (hover the mouse over the download button so that URL will appear) and update your custom URL – then you can download company data continuously.

    If you don’t like this then the Google servers work fine – I had to update my software to use the Google format – the Google data already accounts for stock splits (both ways) so there are advantages. However, from what I’ve seen it only goes back to 2001 whereas Yahoo allows you to get data much further back.

  19. Murray Cooper says:

    Comment:
    I found a solution to the broken Yahoo Finance API here:
    http://www.xlautomation.com.au/free-spreadsheets/yahoo-historical-price-extract

    If you find it helpful, please be courteous and Thank him!
    Regards
    Murray

    • Dravo says:

      Thanks, Murray,

      I wasn’t aware of the efforts of XLAutomation in scraping the data from the new site of Yahoo.

      What I do differently is that I get the Crumb and Cookie values from https://finance.yahoo.com/quote/” & Ticker & “/history?period1=” & d1 & “&period2=” & d2 & “&interval=1d&filter=history&frequency=1d using the InternetExplorer in VBA, and parse the Crumb and Cookie values from it. “Ticker” is the input of the ticker symbol as string and “d1” and “d2” are the dates in the required Unix format. I use the WinHTTPRequest method to parse FROM THE SAME SITE the historical data of the tickers that I need using these Crumb & Cookie values, as such request will not work without those values. I program this in VBS under Winhost and use the “Bee-Swarm” technique to collect the data of some 25 tickers in parallel. Scraping using the InternetExplorer doesn’t need a Cookie and a Crumb, but is much slower than a WinHTTPRequest. By using this URL, you get the dividends and splits in the same WinHTTPRequest which saves you a lot of time. For people who are interested in adjusted closes, using this URL you get the OHLC-closes only adjusted for splits, the volumes adjusted for splits, the unadjusted closes, the splits, and the dividends adjusted for splits. For people who are not interested in speed, just use the InternetExplorer in VBA or VBS and forget about Crumbs and Cookies.

  20. Ron says:

    Can GOOG provides doenloading EOD adjusted close prices ?

  21. Here’s a Python code fragment, for what it’s worth. And one can call Python code from Excel (https://stackoverflow.com/questions/3567365/calling-python-script-from-excel-vba):

    —–
    import requests, csv, re, time, datetime, calendar

    symbol = ‘VFINX’
    startdate = str(calendar.timegm(datetime.datetime(2017,5, 1).timetuple())) # May 1, 2017, in POSIX
    stopdate = str(calendar.timegm(datetime.datetime(2017,6,30).timetuple())) # June 30, 2017

    payload = {‘period1’: startdate, ‘period2’: stopdate, ‘interval’: ‘1d’} # apparently unneeded: ‘filter’: ‘history’, ‘frequency’: ‘1d’
    URL1 = ‘https://finance.yahoo.com/quote/’ + symbol + ‘/history’
    URL2 = ‘https://query1.finance.yahoo.com/v7/finance/download/’ + symbol

    r = requests.get(URL1, params=payload) # query human-readable Yahoo finance history page, to get crumb & cookie

    crumb = re.findall(r'”CrumbStore”:{“crumb”:”.+?”‘, r.text)[0][23:-1] # snag first instance of crumb definition

    payload.update({‘crumb’: crumb})
    csvtext = requests.get(URL2, params=payload, cookies=r.cookies).text # get csv

    with open(‘testfile.csv’,’w’) as csvfile:
    csvfile.write(csvtext)
    ——-

    The returned csv files don’t include a column for the symbol. To add that, replace the last two lines with:

    —–
    rows = re.findall(‘^.*$’, csvtext, re.MULTILINE) # parse the csv file into a list of strings, one per row
    with open(r’C:/Users/David/Dropbox/Documents/Finances/testfile.csv’,’w’) as csvfile:
    print(‘Symbol,’ + rows[0], file=csvfile) # add symbol column
    for row in rows[1:-1]:
    print(symbol + ‘,’ + row, file=csvfile)
    —–

  22. Roger Hill says:

    Here is the code I use.

    Sub GetData()

    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Set DataSheet = ActiveSheet

    Range(“C7”).CurrentRegion.ClearContents
    i = 7
    qurl = “http://download.finance.yahoo.com/d/quotes.csv?s=” + Cells(i, 1)
    i = i + 1
    While Cells(i, 1) “”
    qurl = qurl + “+” + Cells(i, 1)
    i = i + 1
    Wend
    qurl = qurl + “&f=” + Range(“C2”)
    Range(“c1″) = qurl
    QueryQuote:
    With ActiveSheet.QueryTables.Add(Connection:=”URL;” & qurl, Destination:=DataSheet.Range(“C7”))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
    End With

    j = Range(“A7”).End(xlDown).Row

    For k = 7 To j

    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Inc. Common Stoc”, “”)
    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Inc. Common St”, “”)
    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Inc. Co St”, “”)
    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Inc. Co”, “”)
    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Inc. (The)”, “”)
    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Inc. Com”, “”)
    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Inc.”, “”)
    Cells(k, “C”).Value = Replace(Cells(k, “C”).Value, “, Incorporated C”, “”)

    Next

    Range(“C7”).CurrentRegion.TextToColumns Destination:=Range(“C7”), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, other:=False

    ‘turn calculation back on
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    ‘ Range(“C7:H2000”).Select
    ‘ Selection.Sort Key1:=Range(“C8”), Order1:=xlAscending, Header:=xlGuess, _
    ‘ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Columns(“C:C”).ColumnWidth = 25
    Rows(“7:2000”).RowHeight = 16
    Columns(“J:J”).ColumnWidth = 8.5
    Range(“h2”).Select

    End Sub

  23. rbarnesy says:

    I would like to download a date range of historical BID prices for an etf that always has an active bid/ask but trades infrequently. Is the standard O,H,L,C,V format the only option? I can get single line quotes no problem.

  24. Yahoo seem to have ceased this service in November 2017 for the following reason:

    >>>
    It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com.
    <<<

    Their lawyers haven't said what the actual violations are, but I suspect that people were downloading streaming quotes and bundling them into their own products for which they were charging. I would imagine that the terms of service said that they should be for the user's own personal use, although no doubt this was expressed in 114 pages or thereabouts.

    Their engineers could get around this by just providing end of day quotes for the previous day, and only updating these at the end of each day for the respective markets. Personally I would find this more convenient as i could then download the quotes anytime the following day, instead of having to do it at specific periods.

    Unfortunately there is no way of communicating this idea to Yahoo as far as I can see.

    But for now, the only way to get quotations into Quicken or similar products is to have two screens open and manually copy each quotation from one to the other. The Yahoo Finance web page provides lots of stock graphs much better than was available on the paid for Quicken product bought some years ago, but there is no overall graph for the total of any individual portfolio that I can find.

  25. Harz Delf says:

    You could use MarketXLS as an alternative.
    I heard Yahoo Finance API had been discontinued.

  26. SignalSolver.com still has the tools for getting to the Yahoo historical data, you can even use the old URLs, done by simple URL emulation. And if you need to use the newer JSON real-time Yahoo data, there are spreadsheets for that too. Free source code, no strings. 🙂

Leave a Reply

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

%d bloggers like this: