Yahoo price scrape has failed

[eventide]

There's also streaming data on the yahoo websocket which can be used for hunderds of tickers simultaneously

wss://streamer.finance.yahoo.com/ (this is not a viewable link obvs, its a websocket endpoint)

but you need to decode the inbound protobufs, which is a satisfying challenge.
Trickier still but cherry on the cake is then to expose the streaming prices in excel


The websocket is integrated into how a traditional yahoo finance page for a stock works.
When you call eg https://uk.finance.yahoo.com/quote/VOD.L/
The page calls the v7 or v10 api initially to get ohlc etc
Then it sets up a websocket to continually update the last price and volume details, for very lightwieght data use

[spiderbill]

Looks like the Yahoo feeds are off again - both my Excel sheet using eventide's add-on and my HYPTUS on Libre Office are failing to return anything. I noticed it was down yesterday evening and it's still down today. Back to Google sheets.....

[Itsallaguess]

Looks like the Yahoo feeds are off again - both my Excel sheet using eventide's add-on and my HYPTUS on Libre Office are failing to return anything.

I noticed it was down yesterday evening and it's still down today.

Similar to the last outage, it's worth noting that the v10 Yahoo API is still operating without any issues -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=38834&hilit=api#p584100

Cheers,

Itsallaguess

[csearle]

It is working now.

john

Not for me it isn't. C.

[johnstevens77]

It is working now.

john

Not for me it isn't. C.

That was on April 20th!

It has failed for me too, today, May 5th

john

[Jam1]

Also failed for me tonight.

[staffordian]

Also failed for me tonight.

Same here

[Itsallaguess]

Also failed for me tonight.

Same here ..

I've knocked up a 'rough and ready' VBA routine that will use the still-working and recently more reliable v10 API from Yahoo and scrape prices rapidly for the Excel version of HYPTUSS.

For now, I've inserted an 'Alt Yahoo' button underneath the 'Portfolio Value' top-left area, using the steps shown in the video below from 55 seconds in, until the 3 minute mark -

https://player.vimeo.com/video/707670466

Here's the 'Alt Yahoo' VBA code to use with the above process -

Code: Select all

Private Sub CommandButton17_Click()

Dim lastrow, rowsdown, epic, url_string, http, sResp, price, regexp

Let lastrow = Range("C65536").End(xlUp).Row

' Clear existing price cells so that the user can see the new prices being populated
For rowsdown = 6 To lastrow
    Cells(rowsdown, 5) = ""
Next rowsdown


Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")

Set regexp = CreateObject("VBScript.RegExp")

For rowsdown = 6 To lastrow

'Set the Epic for the row
epic = Cells(rowsdown, 3)


' Set the Yahoo Finance v10 API URL for the EPIC in each row
Let url_string = "https://query2.finance.yahoo.com/v10/finance/quoteSummary/" & epic & ".l?modules=price"

'Get the Yahoo Finance JSON for the EPIC
With http
    .Open "GET", url_string, False
    .send
    sResp = .responseText
End With
On Error Resume Next


'Find the Yahoo Finance Yield in the returned JSON if it exists
With regexp
        .Pattern = "regularMarketPrice[\s\S]+?fmt"":""(.*?)"""
        If .Execute(sResp).Count > 0 Then
            price = .Execute(sResp)(0).SubMatches(0)
        End If
End With

' Place the v10 API Yahoo share price on the HYP sheet
Cells(rowsdown, 5) = price

Next rowsdown

End Sub

The above process works for the latest version of the HYPTUSS Excel download, and the only check required for anyone wanting to give it a go with an older version is to make a note of the 'CommandButton' number that exists before deleting the two original lines of button code at the 2-minute mark in the above video, and then after pasting in the above VBA code into the VBA Editor, make sure that the first CommandButton number is manually realigned to that same original number. In the above video, we can see that the original CommandButton number is 1, and in the latest version of the HYPTUSS Excel download, it's '17' as replicated in the above VBA code, but it is likely to be a different number in various older versions of the HYPTUSS tool, so just check before and after the copy and paste process that the number is the same as the first line of VBA button-code originally deleted...

The VBA password box is likely to appear at some stage early in the above process, and the open VBA password for HYPTUSS is the usual 'pleaseletmein'

As usual, the above steps are really quite simple for anyone with enough care and patience to follow the steps shown in the video, and I'd welcome any feedback if anyone gives it a go, but please, at first make sure that you work on a recent COPY of your current HYPTUSS file, in case anything un-towards happens during the above process...

Cheers,

Itsallaguess

[Laughton]

Back to Google sheets.....

Good luck with that - lots of N/As and ERROR messages on my spreadsheet yesterday and again today.

[simoan]

Back to Google sheets.....

Good luck with that - lots of N/As and ERROR messages on my spreadsheet yesterday and again today.

I have long suspected the Yahoo price feed may be disabled at some point. Hopefully this outage is temporary rather than permanent. Has anyone got any experience using the STOCKS data type in Excel? This looks potentially interesting to me, even though it would mean subscribing to Office 365.

All the best, Si

[Newroad]

Hi Simoan.

It's pretty easy in Excel.

You type in the stock (epic) code into a cell then press "Stocks" from the "Data Types" section of the "Data" ribbon and it populates it with the full name. Ideally, put in XLON before the epic code, e.g. "XLON:SHEL" in case there is a duplicate epic on a different exchange.

Then, when you hover over the name, a little tab appears which you can choose what information you want - which as far as I can tell populates the next free column.

It's certainly saved me much time from updating - and I can't recall it failing.

Regards, Newroad

[genou]

Has anyone got any experience using the STOCKS data type in Excel?

All the best, Si

What do you want to know? It works, but the universe it covers is restricted - no corporate bonds for example. I doubt it will do gilts either. It does do exchange rates.

[mc2fool]

Looks like the Yahoo feeds are off again - both my Excel sheet using eventide's add-on and my HYPTUS on Libre Office are failing to return anything.

I noticed it was down yesterday evening and it's still down today.

Similar to the last outage, it's worth noting that the v10 Yahoo API is still operating without any issues -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=38834&hilit=api#p584100

Cheers,

Itsallaguess

Rather curiously, so is the v6 API still working ok, so for those that want it's just a one character change, and still works for multiple stocks...

https://query1.finance.yahoo.com/v6/finance/quote?symbols=GOOG,AAPL

[spiderbill]

Good luck with that - lots of N/As and ERROR messages on my spreadsheet yesterday and again today.

Yes, I have three doing that in my portfolio this time round (CSN, UKW, NESF). Strange as they were all working when I set it up the other week.

cheers
Spiderbill

[staffordian]

I've knocked up a 'rough and ready' VBA routine that will use the still-working and recently more reliable v10 API from Yahoo and scrape prices rapidly for the Excel version of HYPTUSS.

For now, I've inserted an 'Alt Yahoo' button underneath the 'Portfolio Value' top-left area, using the steps shown in the video below from 55 seconds in, until the 3 minute mark -

https://player.vimeo.com/video/707670466

Here's the 'Alt Yahoo' VBA code to use with the above process -

Many thanks for doing this, it worked a treat.

The update did take longer, and Excel reported "Not responding" for the best part of a minute, but then filled all the prices almost simultaneously.

[ReformedCharacter]

Good luck with that - lots of N/As and ERROR messages on my spreadsheet yesterday and again today.

Yes, I have three doing that in my portfolio this time round (CSN, UKW, NESF). Strange as they were all working when I set it up the other week.

cheers
Spiderbill

I've been using Google Sheets for some years for my portfolio valuations and as you mention certain shares fail sometimes. I had 3 prices that returned an error on Friday. Usually it doesn't last long, hopefully it will be back to normal next week.

RC

[mc2fool]

I've knocked up a 'rough and ready' VBA routine that will use the still-working and recently more reliable v10 API from Yahoo and scrape prices rapidly for the Excel version of HYPTUSS.

For now, I've inserted an 'Alt Yahoo' button underneath the 'Portfolio Value' top-left area, using the steps shown in the video below from 55 seconds in, until the 3 minute mark -

https://player.vimeo.com/video/707670466

Here's the 'Alt Yahoo' VBA code to use with the above process -

Many thanks for doing this, it worked a treat.

The update did take longer, and Excel reported "Not responding" for the best part of a minute, but then filled all the prices almost simultaneously.

As I say above, the v6 API is still working and, like the v7 API, allows for the info for multiple stocks to be fetched in one go, so zips along to the point of being almost instant. All that's needed is to change occurrences of:

https://query1.finance.yahoo.com/v7/finance/quote... to
https://query1.finance.yahoo.com/v6/finance/quote...

(I don't know the setup for HYPTUSS but it looks from Itsallaguess's code that the query string may be hardwired in the code. In my spreadsheets I have the query string in a named cell in a "Parameters" sheet so such changes are trivially easy...)

[simoan]

Has anyone got any experience using the STOCKS data type in Excel?

All the best, Si

What do you want to know? It works, but the universe it covers is restricted - no corporate bonds for example. I doubt it will do gilts either. It does do exchange rates.

Thank you. I assumed it worked OK and was more interested in what level of data was provided, and if any data available on Yahoo is not available in Excel. My spreadsheet contains a lot of data available on Yahoo, including currency exchange rates, global stock market indices and commodity prices. On the equities front, I invest a lot in UK small caps and so would be interested in how well AIM listed companies are supported, for instance? Given the data is sourced from Refinitiv I would assume UK stocks have good data coversge.

All the best, Si

[Arborbridge]

Yahoo/Hyptuss is not working for me either, though it did last weekend.

I've skimmed through this thread, but most of it is in a foreign language to me - so is this something which will rectify itself, something I can fix myself, or an event where a new version of HYptuss will provide succour for idiots like me?

Arb.

[Urbandreamer]

Yahoo/Hyptuss is not working for me either, though it did last weekend.

I've skimmed through this thread, but most of it is in a foreign language to me - so is this something which will rectify itself, something I can fix myself, or an event where a new version of HYptuss will provide succour for idiots like me?

Arb.

My problem is that it assumes you are using the excel version, which I'm not. Were the code to be python for the libreoffice version I'm sure that I could make the code changes.

To me it sounds like it won't rectify itself, though our kindly developers will eventually try to produce a fix. Unfortunately it sounds like we are unlikely to get a quick fix as the excel "fixes" are just patches bound to fail soon.

At the end of the day, they are dependent upon others. Principally Yahoo.