0

I see the note on the Yahoo blog stating they've discontinued support for iChart and I see this answer Yahoo Finance Historical data downloader url is not working discussing the newer request format for historical data.

Here is the original snippet of code I was using to download the historical data (it was part of a loop that created a tab in a spreadsheet per stock symbol)

With Sheets(SheetName).QueryTables.Add(Connection:="URL;http://ichart.finance.yahoo.com/table.csv?s=" & yahoosymbols & "&a=09&b=29&c=1984&d=04&e=12&f=2020&g=w&ignore=.csv", Destination:=DestinationRange)
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = False
        .FieldNames = False
    End With

and here is the new code using the new version of the URL

With Sheets(SheetName).QueryTables.Add(Connection:="URL;http://query1.finance.yahoo.com/v7/finance/download/" & yahoosymbols & "?period1=947570400&period2=1577858400&interval=1wk&events=history&crumb=RhdXrQKrUXZ", Destination:=DestinationRange)
        .BackgroundQuery = True
        .TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SaveData = False
        .FieldNames = False
    End With

If I copy and paste the url (subbing a stock symbol for the yahoosymbols variable) then it works fine in a browser.

But when I run this code then I get an excel 1004 error and says it is unable to open the url

Any thoughts? I am not a VBA guy so might be making a real amateur mistake. Thanks

iCoyote
  • 1
  • 1
  • I would suggest Record Macro of building the web query manually and compare the generated code with yours. Another option could be Power Query if your Excel version supports it. – Slai May 28 '17 at 00:02
  • Nothing wrong with the link. Yahoo recently moved HTTP to HTTPS and also requires cookies (notice the "Crumb" at the end of URL)? The "Download Data" link on https://finance.yahoo.com/quote/YHOO/history?period1=947570400&period2=1577858400&interval=1wk&filter=history&frequency=1wk now has that. If you copy the link and open it in another browser you will notice that it will not open. You will need to establish a session with the Yahoo Finance server in order to get the crumb. I have not done this before so will have to check – Siddharth Rout May 28 '17 at 03:54
  • Thanks folks. I actually got this guy's solution to work. http://www.xlautomation.com.au/free-spreadsheets/yahoo-historical-price-extract It was a little different approach to the query than what I was using, but handled all of the cookie and crumb stuff well – iCoyote May 28 '17 at 17:01

1 Answers1

0

The urls you are using are not valid any more. But since you are not a vba guy and you only need YF data in Excel, why don't you use the Deriscope Excel addin? Disclaimer: I am the Deriscope's author.

Yannis
  • 125
  • 1
  • 5