Some Stocks on YAHOO can not be displayed by STOCK formula

At first I got the same result as you. But after a little fiddling the function is now working as expected:

To get it going perhaps enter the formula like this, with the symbol directly in the formula.

= STOCK("160119.SZ",price)

Then you should see this:

After that STOCK with references to symbols in other cells should work as expected.

SG

SG: Doesn't seem the most likely, but I couldn't say what VRZN might be doing. I'm updating 4 spreadsheets each day, for a total of ~45 calls to the API daily, which doesn't seem onerous.

I can say that a packet capture says that when Numbers makes the API call it goes here:

apple-iwork-finance.query.yahoo.com: type CNAME, class IN, cname geo-applefinance-cache.internal.query.g03.yahoodns.net

When I open http://apple-iwork-finance.query.yahoo.com with a browser, it's a (mostly) blank page, with a background.

Viewing source on the page shows the following (among lots of other less helpful text):

var englishHeader = 'Will be right back...';

var englishMessage1 = 'Thank you for your patience.';

var englishMessage2 = 'Our engineers are working quickly to resolve the issue.';

So ... Yeah, this is on the Yahoo! side. Whether it's temporary or not, I can't say.

Based on what I can tell through what I'm seeing, this is on the Yahoo! side. They've either changed or disabled the API functionality that Numbers is using. I'm not willing to assume that Apple is using the same API for the stocks app/widget.

I've been using the STOCK formula for months in multiple spreadsheets without issue. Today it's started acting up, and as of this moment, is completely non-functional on MacOS / iOS versions of Numbers.

Hoping that this is temporary, as it was really nice functionality to have.

I don't use the function except when trying to help here but, trying out your problem yesterday and today, I noticed problems with it. Yesterday it wouldn't give me data on AAPL, then it finally did and worked fine from then on. Today it wouldn't give me BLT.L (which is one of the examples in the description of the STOCK function), then after a while it did. The error I get for 160119.SZ has varied from "no price data available" to "no security named..." and never has worked, whether I type it into the formula or reference it from a cell.

I blame it on Yahoo but I have no way to prove it.

Stephen-L wrote:

I tried to type "= STOCK("160119.SZ",price)" in the cell, but the result remains same.😂

It's a little tricky. You can't just type in the formula. Try typing = to call up the Formula Editor then start typing STOCK to get the function to come up. Then click the symbol token within the suggested formula and replace that with "160119.SZ".

SG

I've been using both stock and stockh in a spreadsheet for over a year. As far as I've tested, neither function works at all as of about 9/7/2018 (the last day I know it worked). My spreadsheet didn't change, so I suspect this is Yahoo disabling this functionality. Network trace shows https sessions set up with yahoo when running spreadsheet as expected, so not a network or firewall issue.

SGIII, this is working for you?

=STOCK("160119.SZ",price) gives me an error each and every time. Lately that error is "Data for the ‘price’ attribute isn’t available for the selected symbol".

The whole thing seems flaky to me. STOCK("AAPL",price) gives me the same error now. It was working earlier today.

null0 wrote:

Based on what I can tell through what I'm seeing, this is on the Yahoo! side. They've either changed or disabled the API functionality that Numbers is using. ...

Today it's started acting up, and as of this moment, is completely non-functional on MacOS / iOS versions of Numbers.

Interesting comment. Do you suppose Yahoo! has a way to distinguish your machine from mine and has disabled the functionality for yours but not mine?🙂

SG

Been too busy with other things to shut down or restart as part of a test. I'm using Numbers 4.3 due to the calculation errors in 5.1. I just tried it on another computer (running Numbers 5.1) and I opened the My Stocks template. AAPL works but if I change any of the stocks to 160119.SZ, that one does not.

EDIT: Actually, all it will give me are the ones it started with, which have prices from the past. And if I change them and then change them back, it won't give me those prices either.

And you did all the usual things? Shut down and restart the Mac? Figuratively stomp on it? That sort of thing.

Also maybe fiddle with the 'My Stocks' template to make sure that is working.

SG

I started a new document based on the template. Doesn't work in that one. But still working fine in the document that was the source for the screenshots. Suspect a Numbers bug of some sort.

SG

Yes, seems to be working again.

Considering what Yahoo did to Excel users, I am surprised we have this functionality at all.

It was flaky at first but now has been working consistently.

SG