Dec 12

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

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


 

Introduction

I was working on creating a spreadsheet to calculate profits and losses on options positions but didn’t know how to populate excel with stock quotes. Back in the day there used to be an interface to get stock quotes with the MSN Money site but it is not supported anymore. The idea behind this spreadsheet was to use the latest and historic quotes to calculate intrinsic values of options and P&L for expired ones. Kind of just trying to keep track of my record and evaluate performance. Of course the issue we face is that stock prices move every second and maintain all that data manually is not worth it. After some research I tried using Google Finance to populate Excel to no avail but found Yahoo Finance supports this more easily. I ended up writing a post to help others with regards to that: How to: Obtain stock quotes from Yahoo finance (you can query them via Excel too).

Just recently, a reader asked about making the query date specific. Yahoo! Finance does support getting historical (closing) prices. It is a very basic interface, so it comes with a lot of limitations. If you just want historic closing prices, then this is the place for you. Within the limitations I have found (obviously there are more but from my simple needs), I have identified these:

  • You are NOT able to get more than one Stock or Index at a time
  • You are NOT able to download data for everything (exchange rates is one example, there are some “weird/foreign” stocks as well)

but now that I have completely taken all the enthusiasm away from you, let’s get into the exciting part of how to get this to work:


Solution

In order to create the web query that will provide us with historical stock (closing) prices, we need to supply Yahoo some information:

  1. Stock Symbol (Mandatory)
  2. Date Range (Optional, if not provided it will return all data available)
  3. Internal (Optional, defaults to days if not provided)

The URL is composed as follows (step by step):

  • Starting URL:
    • http://ichart.yahoo.com/table.csv?
  • Stock Symbol
    • http://ichart.yahoo.com/table.csv?s=AAPL
  • Starting Date
    • Month (goes from 0 to 11, don’t ask me why. So if you want July which is the seventh month of the year, you need to supply 06)
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0
    • Day
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1
    • Year
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010
    • Ending Date
      • Month (again, goes from 0 to 11)
        • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11
      • Day
        • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31
      • Year
        • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015
      • Interval
        • Here you supply one of the three trading periods supported:
Name Tag
Daily d
Weekly w
Monthly m
  • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m
  • And let’s tell Yahoo we want this as a CSV file
    • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv

We’re done. Now, if you click the URL: https://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv you will start the download of your CSV file containing all the historical prices for Apple (AAPL) since 2010 through the end of 2015. Did you notice how I used https instead of http? Yahoo Finance supports https if you want to have your queries protected, which I recommend.


Results and Conclusions

Now, let’s study some sample the output:

Date Open High Low Close Volume Adj Close
04/01/2010 213.429993 215.589996 190.25 192.059998 215986100 25.547121

Let’s go column by column:

  • Date
    • This is the date for which the values correspond
  • Open
    • The opening price for APPL for the given period/interval (week in this case)
  • High
    • The highest price for AAPL for the given period/interval (week in this case)
  • Low
    • The lowest price for AAPL for the given period/interval (week in this case)
  • Close
    • The closing price for AAPL for the given period/interval (week in this case)
  • Volume
    • The trading volume for AAPL for the given period/interval (week in this case)
  • Adjusted Close (Adj Close)
    • The closing price, adjusted for splits and the like. For example, there was a 7:1 split in Apple, so the adjusted close is less than 1/7th of the actual closing price back in 2010. I believe besides splits; it also considers dividends in the adjusted price. If you look at the price between 1/10/2015 and 2/11/2015, you can see there is a 0.509338 price differential which is very close to the .52 dividend paid.

UPDATE (May 20th, 2017)

Some sad news for users of Yahoo Finance to obtain free stock quotes: Yahoo has changed the way you form the URL and has made it somewhat more difficult for you to use the service. I think for most cases it is not a viable service anymore. As reported by some of the comments, now the URL requires a parameter called CRUMB which is obtained from the cookies set when establishing a session with Yahoo. In other words, that crumb is unique to your session.

Your options:

1) You can do something like this:

Parse the HTML when you make a query using the web page: Example URL: https://finance.yahoo.com/quote/SAN/history?period1=1483250400&period2=1495256400&interval=1d&filter=history&frequency=1d

2) Use the new URL and somehow get your cookie and obtain the CRUMB:

What do we know?

Yahoo changed their URL scheme to require an established session with cookies. Potentially you could use ‘curl’ to establish a connection to the main site, get a cookie, and then use that to get a crumb. With that crumb, now you can form your download URL. Because the Crumb is part of the cookie, it doesn’t change but is part of your browser so it won’t work on a different browser with a different cookie. Because of that, you might get an error like this:

{
    "finance": {
        "error": {
            "code": "Unauthorized",
            "description": "Invalid cookie"
        }
    }
}

Do note as well that times changed to POSIX/UNIX timestamps. You’ll need to convert your dates now.

3) Use download.finance.yahoo.com instead

A reader in the comments suggested it and it seems I was able to figure out how to use the API there. There is a catch of course: No historical prices… at least I wasn’t able to see an option for it. If you manage to get historical prices from Yahoo please share with us via the comments section. If this works for you, check out my post: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too) Part II

4) Use Google instead

This probably means that for regular users Yahoo Finance is no longer a viable option to obtain stock quotes in Excel. I also can’t seem to be able to access https://finance.yahoo.com/quote/GOOG/history?p=GOOG on the excel web browser. My guess is that Yahoo has blocked access for the Excel client (very sneaky.) At this point my suggestion would be to use Google Finance. I’ll try to put together a post and reference it here so people can convert over.

 

 

45 comments

Skip to comment form

    • wherestjb on May 2, 2016 at 5:33 pm
    • Reply

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

    • Jean-Christophe Emery on November 5, 2016 at 4:13 am
    • Reply

    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

    • Lindsey Lee on February 20, 2017 at 9:44 am
    • Reply

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

    • Raj K on April 10, 2017 at 7:12 pm
    • Reply

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

    • David on May 17, 2017 at 9:10 am
    • Reply

    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?

    • mbmarx on May 18, 2017 at 2:19 am
    • Reply

    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

    1. 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

        • lucas0033 on June 4, 2017 at 7:58 am
        • Reply

        nice, only issue is there is missing adjusted close price 🙁

    • jas on May 18, 2017 at 8:12 pm
    • Reply

    I think currently the link is not working.

    1. I think they shut the service down. Probably not making money from a widely use API service was the reason.

    • David on May 19, 2017 at 8:34 am
    • Reply

    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?

    1. 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 on May 21, 2017 at 9:30 pm
        • Reply

        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 on May 23, 2017 at 1:17 am
        • Reply

        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 on May 24, 2017 at 9:59 am
        • Reply

        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 on May 24, 2017 at 8:56 pm
          • Reply

          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 on July 5, 2017 at 7:30 am
        • Reply

        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 on July 5, 2017 at 11:37 am
        • Reply

        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

    • Raj K on May 20, 2017 at 7:29 am
    • Reply

    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

    1. Me too, I tried with the old parameters and just got NA back. Let us know if you get it to work.

    2. I think I was able to figure it out… just wasn’t able to get historical data. I wrote a post about it: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too) Part II

    • Martin on May 23, 2017 at 1:44 pm
    • Reply

    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.

    1. 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 on May 27, 2017 at 12:54 pm
        • Reply

        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!

    • Allen on May 25, 2017 at 7:40 pm
    • Reply

    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.

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

    • Hui on May 28, 2017 at 10:33 pm
    • Reply

    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?

    • Hui on May 28, 2017 at 10:39 pm
    • Reply

    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

    • Hui on May 29, 2017 at 12:03 pm
    • Reply

    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)

    1. 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 on May 30, 2017 at 1:23 am
        • Reply

        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 on June 2, 2017 at 1:34 am
        • Reply

        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.

    • deios on May 29, 2017 at 12:13 pm
    • Reply

    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.

  2. 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.

    • Mike on June 4, 2017 at 4:00 pm
    • Reply

    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.

    • Murray Cooper on June 7, 2017 at 6:45 am
    • Reply

    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 on June 8, 2017 at 11:48 pm
      • Reply

      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.

    • Ron on June 8, 2017 at 12:52 am
    • Reply

    Can GOOG provides doenloading EOD adjusted close prices ?

  3. 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)
    —–

      • Jack on August 26, 2017 at 4:17 am
      • Reply

      Hi David,

      Could you please show or paste the full version of your code? Thx!

  4. 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

    • rbarnesy on August 28, 2017 at 1:57 pm
    • Reply

    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.

      • marx on September 21, 2017 at 5:55 am
      • Reply

      yahoo data is not anymore reliable ,they have appended some zero values in between the list which is a mess.

  5. 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.

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

Leave a Reply

%d bloggers like this: