1

The following Excel VBA code segment has worked for years, but stopped working around 28 Apr 2022. I receive the responseText, but the .getResponseHeader("Set-Cookie") returns null.

Set httpReq = New WinHttp.WinHttpRequest

DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
With httpReq
    .Open "GET", DownloadURL, False
    .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send
    .waitForResponse
    response = .responseText
    cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
End With
braX
  • 11,506
  • 5
  • 20
  • 33
DMyers
  • 11
  • 1
  • 1
    Reach out to whoever monitors the site. I tried to deactivate redirects with `httpReq.Option(WinHttpRequestOption_EnableRedirects) = False` but no success. It seems like the behavior of the site itself has changed. – TehDrunkSailor May 17 '22 at 09:48
  • [Of any help](https://stackoverflow.com/questions/44030983/yahoo-finance-url-not-working)? – T.M. May 18 '22 at 13:32
  • Not sure if its useful, but I found that searching for a specific company never returns the company itself, but a "similar to" list. Instead of "https://finance.yahoo.com/lookup?s=" & stockSymbol Try with "https://finance.yahoo.com/quote/" & stockSymbol & "?p=" & stockSymbol – Felipe La Rotta May 20 '22 at 04:24

2 Answers2

0

I tried the website you provided with an f "https://finance.yahoo.com/lookup?s=f" and I got returned a different website "https://finance.yahoo.com/lookup/all?s=f". I don't know if this is the problem with what you are experiencing.

The cookie or .responseText part of the website I can't answer that, but there are other option is to use something different than vba. I have used excel's Power query to do the same thing. examples:

  1. bitcoin
  2. investing website

Let's say that you want the table that is at the top left of the website https://finance.yahoo.com/quote/F/ after following the steps in the example and selecting Table 0 I get.

Column1 Column2
Previous Close 12.44
Open 12.88
Bid 13.69 x 21500
Ask 13.69 x 800
Day's Range 12.88 - 13.63
52 Week Range 11.79 - 25.87
Volume 77,962,613
Avg. Volume 73,243,411

If I pick Table 1 I get the one to the right. If you are wanting every other hour auto refresh rate that can be done see example 1. The problem is that this tool is not the fastest. I decided that the answer was not complete enough so the below part has been redone. I hope that you like the changes to the answer and it gets upvoted and checked as the solution. I hope that the power query part becomes part of your solution for other projects. Steps

  1. Build a table like below I named mine "tickers".
  2. Click on From Table/Range enter image description here
  3. power query opens up and you will see a one column table in there same as what is in excel. Next expand the queries on the left it then right click > New Query> Other Sources> Blank Query. I renamed this Query1 to f_stock. We have to reuse this f_stock name later.

enter image description here 4. Now click on Advanced Editor and paste in the below code.

// f_stock
let
    Source = (ticker) => let
        Source = Web.Page(Web.Contents("https://finance.yahoo.com/quote/" & ticker & "/")),
        #"Filtered Rows" = Table.SelectRows(Source, each ([ClassName] <> null)),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
        Expanded_Data = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2"}, {"Column1", "Column2"})
    in
        Expanded_Data
in
    Source

  1. Save the above then switch back to "tickers". Now click on "Custom Column". I change the column name to "Stats". In the formula box type in the code f_stock([Tickers]). Click Okay. You will probable get two info warnings popups here. "Information is required about data privacy. Continue" click " □ Ignore... Save". The other one I forgot what I clicked.
    enter image description here

  2. Your new column comes with the tables expand it by clicking on the button at the top of the column. enter image description here

  3. Click on Home>Close & load.

Shane S
  • 1,747
  • 14
  • 31
  • This doesn't address what OP wanted though. OP wants to get the cookie from the site, which is no longer being sent. In fact, OP even mentions that the `.responseText` is coming through. – TehDrunkSailor May 18 '22 at 04:59
0

I am facing the same problem. Cant deliver an answer in code, however what seems to be working is to download the data without using a cookie. Cons are, that after around 500 stocks the downloaded data becomes an error. so:

  1. download the first 500-pack of tickers
  2. wait 5 min
  3. repeat till you are done
One Time I
  • 13
  • 3
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jun 26 '22 at 17:33