Yahoo price scrape has failed

[spiderbill]

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

Yep, same with the Eventide Excel extension.

[teecee90]

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.

Tried to follow these instructions but when doing the find and replace stage I just get an error message saying 'search text is not found'

[Kerryn]

I think you need to change the V7 to V6 reference in the sheet "Parameters", although I don't think it will help ATM!

[formoverfunction]

I have apps that appear to use Yahoo data. Both have stopped working in the last few days.

I think I saw an article a little time ago that suggested that Yahoo were trying to monetize the api and bill annually for feeds.

I wonder if that's why so many opensource app have stopped working all at the same time?

At the moment I have mop and jStock not updating.The long it al goes on the more focused I get just taking data straight from the LSE.

[mc2fool]

Tried to follow these instructions but when doing the find and replace stage I just get an error message saying 'search text is not found'

Don't bother, just download the fixed version. viewtopic.php?p=588001#p588001

[formoverfunction]

Update to jStock yesterday, so all working now.

[niord]

Update to jStock yesterday, so all working now.

I have no idea what that means, but mine is not. Libreoffice 7.5 Hyp top up version b-v

[DTerrence]

Nor has my Excel Yahoo Price Scrape

[formoverfunction]

https://jstock.org/ it's a desktop app.

https://www.youtube.com/watch?v=x7JkMbRshMs

I use a range of preset screeners and the price change notifications. Mainly to help me deciding on my monthly top ups.

It used to use Yahoo data, but that may have moved on.

It requires a gmail account if you want email notifications. I have dedicated Rasperry Pi running it.

I use it in quite a simple way, but I find it a useful tool. I have been using it for almost a decade and it's has quite an active development community.

I hold my portfolio as a watch list and just run scans across it for price movements etc and it happily just chugs away in the background.

[Arborbridge]

Tried to follow these instructions but when doing the find and replace stage I just get an error message saying 'search text is not found'

Don't bother, just download the fixed version. viewtopic.php?p=588001#p588001

That's not working either, this morning.

[GN100]

I realise that this thought might be anathema to many here but is it possible to pay for a data feed that would work for our fairly simple requirements and if so does anyone know the cost?

GN

[Darka]

I realise that this thought might be anathema to many here but is it possible to pay for a data feed that would work for our fairly simple requirements and if so does anyone know the cost?

GN

I use my Microsoft 365 subscription (£79.99 per year) for 1TB disk space, all the latest Office apps including Excel with its "STOCKS" data type.
For my portfolio, this give me everything but yield which I enter in manually to my spreadsheet once per month.

No hassle, and it just works for my shares and IT's; when I was working, I was a professional software engineer but even I don't want to be tweaking code constantly anymore.

regards,
Darka

[kiloran]

See viewtopic.php?p=591536#p591536

--kiloran

[stacker512]

We’re sorry for the inconvenience, but API-level access to Yahoo Finance quotes data has been disabled.

Yahoo Finance licenses data from 3rd-party providers that do not currently authorize us to redistribute these data in API form. Licenses that authorize redistribution come with a greater cost that varies depending on a number of factors, including whether the data is for personal or commercial use, the type of data, the volume of queries, and additional features which may be available.

We would appreciate your feedback to ensure that we can continue to serve your needs. By understanding your intended use of these API data, we will be better able to acquire the appropriate licenses. We appreciate your feedback, and we read every response.

So it looks like we have to pay for this data?

[simoan]

I realise that this thought might be anathema to many here but is it possible to pay for a data feed that would work for our fairly simple requirements and if so does anyone know the cost?

GN

I use my Microsoft 365 subscription (£79.99 per year) for 1TB disk space, all the latest Office apps including Excel with its "STOCKS" data type.
For my portfolio, this give me everything but yield which I enter in manually to my spreadsheet once per month.

No hassle, and it just works for my shares and IT's; when I was working, I was a professional software engineer but even I don't want to be tweaking code constantly anymore.

regards,
Darka

I decided to pay Microsoft the princely sum of £6 to play with the STOCKS data type for a month, and thought I'd quickly report on what I've found, should others find it helpful.

Firstly, updating my spreadsheet was not difficult, or too time consuming, although the automatic recognition algorithm Microsoft used to convert to the equivalent STOCKS data type takes some coaxing, particularly for shares with the same ticker on multiple indices e.g. BHP or RIO. You really have to be careful! I have quite a large portfolio on account of my overweight position in small caps and have found no equity for which data is unavailable - so good news. This includes some very small (sub £100m market cap) AIM listed companies. You can set the prices to update in Excel every 5 minutes, if you wish, and this works well although on occasion the price refresh fails for some unspecified reason.

Coverage of UK fixed income such as prefs is patchy to non-existent, as with Yahoo. Stock market index data coverage is much worse than Yahoo. In particular, it's absurd given the data is sourced from Refinitiv (part of London Stock Exchange Group) that price data for all UK FTSE indices is not available. S&P500, NASDAQ, DAX, NIKKEI and both main Indian bourses are supported; CAC, HANG SENG, SHANGHAI etc. are not. There's no coverage of commodity prices but currency exchange rate coverage is good using the CURRENCY data type.

All in all, it grates having to pay Microsoft £60 a year for reliable prices, even though that includes all manner of storage etc. that I don't really need. Maybe if the Excel stock prices updated on IOS platforms for use on iPhone and iPad it would be worth it, but for some reason they do not. How rubbish is that? As such I'm reviewing my options in the absence of a working price feed from Yahoo.

I'd just like to give huge thanks to eventide for making his add-in freely available. I switched from using Excel WEBSERVICE calls in Excel 2013 to his TlfAddin, which has very happily worked faultlessly for years.

All the best, Si

[Julian]

I don't update the pricing in my investment tracking spreadsheet that often, I'm quite a hands-off buy-and-forget investor and I don't like to concern myself with ongoing market fluctuations so it came as quite a surprise yesterday when trying to refresh the prices on my 70 holding investment portfolio and got 70 out of 70 failures. This forum was my first port of call, understandably since the price fetching code incorporated into my quite complicated (many pages of Excel VBA code) home-spun spreadsheet is based on code I found in the various financial tools here. Thanks to all who so quickly highlighted the problem and started discussing solutions.

My take on this is that despite Kiloran's link to a thread discussing v10 still working, stacker512's post with that Yahoo! statement makes me think that might only be a temporary route and since I am a Microsoft 365 subscriber anyway I might as well just go the Excel built in route and use the STOCKS data type and rejig my code to adapt to that. If I wanted to be more functional about it (explicitly calling a function to get a price) it looks as if the STOCKHISTORY() function with the parameters appropriately set (date range today only, only return a single value for the day, supress all return data except the price) would also suffice although as with quite a few functions that's worksheet only not a native VBA function but presumably it can be called via Application.Worksheetfunction.* but I think I'm going to do the price fetches in the worksheet rather than from VBA function calls.

I decided to pay Microsoft the princely sum of £6 to play with the STOCKS data type for a month, and thought I'd quickly report on what I've found, should others find it helpful.

... You can set the prices to update in Excel every 5 minutes, if you wish, and this works well although on occasion the price refresh fails for some unspecified reason.
...

Thanks for taking the time to do that Si. I had a quick play myself and I think it will do what I need it to do with one exception which is probably an irritation rather than a show-stopper since it only affects 4 of my 70 holdings.

Unfortunately I haven't been able to get the STOCKS mechanism to access prices for my Vanguard UK mutual funds. It doesn't seem to recognise them from any of their Sedol, Mex, Isin or Citi codes, or the codes that I somehow found that worked for Yahoo (embedded in my spreadsheet but I have no idea how I found those years ago; academic in any event since they also don't work with the Excel STOCKS mechanism). I've done a fair bit of internet searching today and so far have not found any solution. I would be very happy to hear from anyone who has found a solution to this issue. This is an example of one of my funds that is causing me issues - https://www.fundslibrary.co.uk/FundsLib ... ccount.pru

Re your experiences Si, in particular the second bit I quoted ...

1 - How do you set the frequency of the update? I couldn't immediately see that. Since I don't refresh prices very often I would only want it to fetch new prices on spreadsheet startup.

2 - What happens when a price refresh fails? Does the cell where the price is supposed to go display some other value ("NaN", "Error" or something like that) rather than a numerical value? In my current VBA price fetching function I have code to look for and handle price lookup errors and also to flag up suspiciously large price changes (maybe due to me not realising that some share had split or consolidated) and in such cases giving an error message and retaining the previously fetched price so that my various portfolio totals don't break or potentially become misleading pending my fixing any share split/consolidation issues.

I suspect I might not have found appropriately detailed documentation for the STOCKS data type yet since the Microsoft pages I have found so far don't seem to cover these two items but you seem to have come into contact with both so any tips there would be much appreciated.

- Julian

[Darka]

1 - How do you set the frequency of the update? I couldn't immediately see that. Since I don't refresh prices very often I would only want it to fetch new prices on spreadsheet startup.

2 - What happens when a price refresh fails? Does the cell where the price is supposed to go display some other value ("NaN", "Error" or something like that) rather than a numerical value? In my current VBA price fetching function I have code to look for and handle price lookup errors and also to flag up suspiciously large price changes (maybe due to me not realising that some share had split or consolidated) and in such cases giving an error message and retaining the previously fetched price so that my various portfolio totals don't break or potentially become misleading pending my fixing any share split/consolidation issues.

- Julian

For question 1, right click on any of the STOCK data types, select "Data Type | Refresh Settings", this brings up an option screen and from that you can choose "Auto every 5 mins", "On File Open" or "Manual".

For question 2, if it fails then it will show the last value, but you will get a "Can't update" kind of message shown at the top of excel which you can click to get rid of, when the updates work again it will of course then show the correct new value.
I don't see this very often and have been using the STOCKS data type for quite some time now.

regards,
Darka

[Itsallaguess]

Unfortunately I haven't been able to get the STOCKS mechanism to access prices for my Vanguard UK mutual funds.

It doesn't seem to recognise them from any of their Sedol, Mex, Isin or Citi codes, or the codes that I somehow found that worked for Yahoo (embedded in my spreadsheet but I have no idea how I found those years ago; academic in any event since they also don't work with the Excel STOCKS mechanism).

I've done a fair bit of internet searching today and so far have not found any solution. I would be very happy to hear from anyone who has found a solution to this issue.

This is an example of one of my funds that is causing me issues -

https://www.fundslibrary.co.uk/FundsLibrary.BrandedTools/Pru/DataOnline/PdfFactsheet/7ae79d92-ac51-441a-8f66-68a360330af5?displayThirdPartyFactsheetLink=True&fundType=retirement_account.pru

The HYPTUSS tool has a price-sheet specifically for funds, and that'll work to be able to pull in a price based on your ISIN codes -

The sheet is called 'FT Funds', and you would manually complete the yellow highlighted area in the example below for the fund you've listed, and then hitting the 'Update Prices' button will get you the most recent price and the associated currency -



Source - HYPTUSS tool

The coding process used for the 'FT Funds' sheet is not the Yahoo API feed used elsewhere in the tool, and it's been robust for a number of years now, so hopefully it can help fill in the blanks for your fund prices by copying any updated data from that FT Funds sheet into your main portfolio sheet or elsewhere...

Cheers,

Itsallaguess

[Julian]

Fabulous. Thanks to both of you - Darka and itsallaguess. It looks as if I have everything I need now to rework my price updating stuff.

- Julian

[simoan]

As a quick addendum to using the STOCKS type in Excel on Microsoft 365... My one month trial expired on 26th June whilst I was away from the PC. Happily, it turns out that the prices are still updating, now free of charge! FWIW I am running the spreadsheet in the Edge Browser on a Win10 PC. Things may be different if you are using the Win10 App, which I couldn't be bothered to download and install for the purposes of my one month trial.

All the best, Si