Yahoo price scrape has failed

[Itsallaguess]

Unfortunately it sounds like we are unlikely to get a quick fix as the excel "fixes" are just patches bound to fail soon.

Heh...

If we'd given up when the tool needed patching due to a data-source issue, we'd have packed it all in over 10 years ago...

The whole thing's built on patches, and I expect it always will be as that's simply the nature of the game we're in...

Cheers,

Itsallaguess

[smokey01]

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

I’m having the same problem with google sheets. Also maybe 2 months ago I noticed one of my shares had fallen by a huge amount but when I investigated I found google sheets was just returning the wrong value. I then compared other share prices and the found minor discrepancies, sometimes returning a slightly higher value than other sources (such as yahoo, Hargreaves Lansdown) and at other times lower values. I have however noticed that the previous days price always matches the other sources.

[Gerry557]

Google Finance often fails to display the odd share price. I have a sheet with live prices etc that gets thrown out when it plays up. Clicking on the individual problematic share often shows the correct details. Occasionally it can't find a share for some reason but usually it gets sorted.

Currently UKW is one of those not working on my sheet but can be viewed via the website.

I manually updated the figure on my sheet this weekend to get the info I needed then changed it back to auto update when Google pull their finger out.

[Jam1]

Thank you Itsallaguess that is working for me.

The issue for Excel Data is that I cannot see yield as one of the selectable fields, such as Price etc

[GN100]

Thanks from me too itsallaguess. It worked for me and your video was a great help. Prices updating now on my HYPTUS and I can link these through to my other sheets

GN

[seagles]

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.

Easy to do and worked for me, except I have zcash in there and it defaults to the last price from row before and not "100". As not needed at the moment (use that for year end reporting), pity it will not let me overwrite it. Thanks for the update, much appreciated.

[Itsallaguess]

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.

Easy to do and worked for me, except I have zcash in there and it defaults to the last price from row before and not "100".

As not needed at the moment (use that for year end reporting), pity it will not let me overwrite it.

Thanks for the update, much appreciated.

Thanks for that - it only needs a tweak to the VBA to cope with ZCASH, and offers me an opportunity at the same time to improve the error-handling a bit, in case the v10 API process is ever fed an invalid ticker.

So this new VBA code can be used to help with both of those issues -

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

price = "????"

'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

If epic = "ZCASH" Then price = 100

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

Next rowsdown

End Sub

The above improved v10 Yahoo Price API code shown above can be used with the same video and instructions contained in my earlier post, linked below for completeness -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=38834&start=20#p587459

Thanks as well to the others who've also been reporting success with this tactical fix - good to know it's been helpful.

Cheers,

Itsallaguess

[mc2fool]

Thanks as well to the others who've also been reporting success with this tactical fix - good to know it's been helpful.

Indeed and fair enuff, but until an updated version is provided surely it's easier (and more likely to be compatible) to tell folks how to change from the (currently not working) v7 interface to the (still working fine) v6 one. I don't use and haven't looked at HYPTUSS for yonks but I've found a version from 2020 on my system (note to self, must do better at tidying up!) and it's simple-ish enough. This is for the Excel version:

Open the spreadsheet (enable macros, if it asks)
Hold down the Alt key and press F11
In the Visual Basic window that opens, double-click on VBAProject in the panel on the left
Enter the password pleaseletmein ('twas always so )
Double click on Microsoft Excel Objects then again on Sheet1
In the menu select Edit -> Replace
For Find What enter /v7/ and for Replace With enter /v6/
Under Search select Current Project
Click Replace All

Close the Replace window and the VB window and try it out (Get Yahoo Prices)

Assuming it works, don't forget to Save.

[peterh]

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

It's the same for me for CSN and UKW (though I've only just entered my portfolio into Google Sheets).

CSN throws up an error in the sheet, but you can successfully search for it in the web interface, but it's missing the name (at least at the time of writing!) - https://www.google.com/finance/quote/CSN:LON?hl=en

[kempiejon]

I came back to this thread thinking I might set aside half an hour to try out IAAG's suggestion up thread. I dutifully watched the video double checking the thread I see mc2fool's alternative. 2 minutes later my scape is working again. Ta mc2fool.

[genou]

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

The info available for a stock is
price; name; ticker; 52 wk hi/lo ; beta; change/change % ; currency; description; exchange / exchange abbreviation; headquarters ( i.e. registered office ) ; hi /low /open/close; industry; instrument type ; last trade time ; market cap ; P/E ; shares outstanding; volume; volume average; year incorporated.

AIM isn't a pond in which I fish. It finds Burford ( although it defaults to US listing, you can force it to XLON ), CVS Group and Fevertree, so I assume they are all there. I can't get it to find an index - it keeps returning funds that track the indices I try, but that may be my ignorance. It doesn't recognise some stuff I thought an M$ product would - e.g. $INX or UKX ( which MS Money recognises ).

[staffordian]

Easy to do and worked for me, except I have zcash in there and it defaults to the last price from row before and not "100". As not needed at the moment (use that for year end reporting), pity it will not let me overwrite it. Thanks for the update, much appreciated.

A quick tip if you ever need to manually enter a price.

Although it will not allow direct entry into the cell, you can enter what you want in a blank cell to the right of where the data is, then copy and paste it into the price cell. Direct entry is not allowed but copy/paste is.

[funduffer]

My HYPTUSS now working thanks to m2fool's /v6/ trick.

I also use the getdata function in my portfolio spreadsheets, which also do not return share prices at the moment.

Is there a simple way to get this function working again?

FD

[tramrider]

Indeed and fair enuff, but until an updated version is provided surely it's easier (and more likely to be compatible) to tell folks how to change from the (currently not working) v7 interface to the (still working fine) v6 one. I don't use and haven't looked at HYPTUSS for yonks but I've found a version from 2020 on my system (note to self, must do better at tidying up!) and it's simple-ish enough. This is for the Excel version:

Open the spreadsheet (enable macros, if it asks)
Hold down the Alt key and press F11
In the Visual Basic window that opens, double-click on VBAProject in the panel on the left
Enter the password pleaseletmein ('twas always so )
Double click on Microsoft Excel Objects then again on Sheet1
In the menu select Edit -> Replace
For Find What enter /v7/ and for Replace With enter /v6/
Under Search select Current Project
Click Replace All

Close the Replace window and the VB window and try it out (Get Yahoo Prices)

Assuming it works, don't forget to Save.

Is it possible for you or someone else to give a version of this helpful change to use with LibreOffice instead of Excel?

[Itsallaguess]

Is it possible for you or someone else to give a version of this helpful change to use with LibreOffice instead of Excel?

Fixed revisions for both Excel and LibreOffice versions are on their way, and I expect this thread will be updated when they're available.

Cheers,

Itsallaguess

[mc2fool]

Indeed and fair enuff, but until an updated version is provided surely it's easier (and more likely to be compatible) to tell folks how to change from the (currently not working) v7 interface to the (still working fine) v6 one. I don't use and haven't looked at HYPTUSS for yonks but I've found a version from 2020 on my system (note to self, must do better at tidying up!) and it's simple-ish enough. This is for the Excel version:

Open the spreadsheet (enable macros, if it asks)
Hold down the Alt key and press F11
In the Visual Basic window that opens, double-click on VBAProject in the panel on the left
Enter the password pleaseletmein ('twas always so )
Double click on Microsoft Excel Objects then again on Sheet1
In the menu select Edit -> Replace
For Find What enter /v7/ and for Replace With enter /v6/
Under Search select Current Project
Click Replace All

Close the Replace window and the VB window and try it out (Get Yahoo Prices)

Assuming it works, don't forget to Save.

Is it possible for you or someone else to give a version of this helpful change to use with LibreOffice instead of Excel?

Maybe someone else but not me. As I said, I don't use HYPTUSS myself and anyway I've never looked at and don't have LibreOffice installed ...

[GPhelan]

Many thanks to mc2fool for the simple update that has restored my own price capture tool.
It is based on the price acquisition code in LivePrice, but modified with custom JSON processing to allow the specification of an exchange code and with considerable extra VBA code to create a data file suitable for import into Quicken.
The one character update took seconds to implement and test, whereas updating the documentation for my future self took far longer!

[kiloran]

Is it possible for you or someone else to give a version of this helpful change to use with LibreOffice instead of Excel?

Fixed revisions for both Excel and LibreOffice versions are on their way, and I expect this thread will be updated when they're available.

Cheers,

Itsallaguess

HYPTUSS has been updated. See viewtopic.php?p=587999#p587999

--kiloran

[daveh]

I changed my version of HYPTUSS to the earlier API version and it was working, but it seems to have stopped working again today.

[kiloran]

I changed my version of HYPTUSS to the earlier API version and it was working, but it seems to have stopped working again today.

Same here.
Unless this is a short-term glitch, I think we need to implement the v10 solution suggested by Itsallaguess, and maybe include an alternative

--kiloran