Skip to main content
No. of Recommendations: 3
The URL I use in an Excel Macro for current prices is not working today.

For example:

http://finance.yahoo.com/d/quotes.csv?s=jnj,msft&f=l1p&a...

or

http://download.finance.yahoo.com/d/quotes.csv?s=jnj,msft&am...

responds with:

"Sorry, Unable to process request at this time -- error 999."

Are others experiencing the same?

I can obviously use other providers, but I liked Yahoo because I found it to be faster and more efficient for an Excel Macro and also they have better coverage of prices for things like options, futures, currencies, etc.

StevnFool
Print the post Back To Top
No. of Recommendations: 1
At some point, I think, Yahoo (Verizon) is going to inflict enough pain on you guys to finally get you to find new cheese.

FC
Print the post Back To Top
No. of Recommendations: 3
Yeah, if you do a "last 24 hour" search on google, lots of people are reporting this. I suspect it is not a temporary glitch, seeing as the first time it happened was the 1st day of a new month. We'll see.

FWIW, over the last week or so people have been complaining about not being able to get to their yahoo mail, cannot login, etc.

But maybe what we're seeing is how a data-center slowly fails after the last person has left. Things fail one at a time until the last until the final overheated bearing grinds itself into dust.

Luckily, I started getting prepared for yahoo quote downloads to go away back when they messed up the portfolios. Just have to wait a couple of days to see if it comes back. But all the others are not a convenient as yahoo's.

It'll be nasty if we have to start scraping the data from the html page. I can do that easily enough. But when I view the yahoo page in my browser, it loads so much crap the page never finished loading. I watch the status bar, it keeps contacting all sorts of other sites, geo-something, google_analytics_something, videos, ads,.. stuff up the wazoo. And bochame!, even an autoplay video. Even with an adblocker the stuff never stops and the computer slows to a crawl. I think this is the web-site version of how Sarah Hoyt describes the final moments of corporations and other organization. “Roll left and die”. Although I guess this would "roll in the mud and die."

Finance/yahoo/html is virtually useless now.
Print the post Back To Top
No. of Recommendations: 0
Yes, same problem. I would guess it's going to be permanent. Coincidentally, I will be off the grid for the next few days anyway. So it won't affect me until next week. I'm hoping some people smarter than me will have solved it by then. Maybe using LibreOffice?

John
Print the post Back To Top
No. of Recommendations: 0
And, darn, just last month I tweaked my daily price download script to repeat itself again every 30 minutes if any last-trade date is yesterday. Which it often is for mutual funds, because they don't report their price until about 8:30PM, unlike stocks.

::sigh:: all that work for nothing. Oh well, just have to do to same logic for my new data source. Bummer.
Print the post Back To Top
No. of Recommendations: 3
Print the post Back To Top
No. of Recommendations: 1
Thanks rharmelink, the JSON version of the query seems to work just fine.

For what it's worth, I had been using the getQuote() function in R's quantmod package and my quick workaround is this replacement function:

require(jsonlite)

getQuote <- function(ticks) {
qRoot <- "https://query1.finance.yahoo.com/v7/finance/quote?fields=sym...
z <- fromJSON(paste(qRoot, paste(ticks, collapse=","), sep=""))
z <- z$quoteResponse$result[,c("symbol", "regularMarketTime", "regularMarketPrice", "regularMarketChange", "longName")]
z$regularMarketTime <- as.POSIXct(z$regularMarketTime, origin = '1970-01-01 00:00:00')
return(z)
}

heink

P.S. The URL root was shortened in the preview. Here's what it should look like after the domain:

/v7/finance/quote?fields=symbol,longName,regularMarketPrice,regularMarketChange,regularMarketTime&formatted=false&symbols=
Print the post Back To Top
No. of Recommendations: 1
Finance/yahoo/html is virtually useless now


I've moved on. Not even looking anymore
Print the post Back To Top
No. of Recommendations: 0
Thank you rharmelink for the JSON tip. I've spent too much time with Yahoo recently. I could pull the JSON data one symbol at a time but could not figure how to request multiple quotes.
Print the post Back To Top
No. of Recommendations: 1
Thank you rharmelink for the JSON tip. I've spent too much time with Yahoo recently. I could pull the JSON data one symbol at a time but could not figure how to request multiple quotes.

The sample URL I gave did two quotes, for MMM and JNJ. Just do a comma-delimited list of ticker symbols and you can request hundreds of them.
Print the post Back To Top
No. of Recommendations: 3
I've updated my Excel macro to use bigcharts. For example:

http://bigcharts.marketwatch.com/quotes/multi.asp?view=q&...

This will work for currencies, stocks and options but I don't think it will work for futures. This is not a problem for me right now as I am not currently using futures.

It works reasonable fast too for about 15 tickers.

StevnFool
Print the post Back To Top
No. of Recommendations: 0
Ooops! I just tried to use rharmelink's URL for MMM and JNJ and this is what I got from Yahoo.

It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com.

It seems they are trying to cut off everyone but a casual once in a while user they can blanket with advertisements.

RAM
Print the post Back To Top
No. of Recommendations: 1
I just tried to use rharmelink's URL for MMM and JNJ and this is what I got from Yahoo.

The JSON file still works for me. It was the first one that was the problem in the first place.
Print the post Back To Top
No. of Recommendations: 0
My apologies to both you and Yahoo, it works for me using Chrome. Evidently I was confused by the output I received in IE.
RAM
Print the post Back To Top
No. of Recommendations: 4
I'm a broken record, and I'm sorry, but... Google. Sheets and the googlefinance() api. No macros, no scraping, no downloads, no nothing. Just... data, in your spreadsheet.

FC
Print the post Back To Top
No. of Recommendations: 1
I'm a broken record, and I'm sorry, but... Google. Sheets and the googlefinance() api. No macros, no scraping, no downloads, no nothing. Just... data, in your spreadsheet.

But...so...slow. But I mostly end up with their import functions instead of their API functions.

The API doesn't do option quotes, right?

For those interested in what you can get from the API:

https://support.google.com/docs/answer/3093281?hl=en
Print the post Back To Top
No. of Recommendations: 6
re: JSON

Losers. Yahoo is just run by losers.

When they changed the historical download capability in May, it took like 2 days until the workaround was all over the internet. Or people could just scrape it from the web page. The web page that was 10 times larger than the simple download.

Now in Nov they shut down the easy, simple, SMALL stock quote API, and replace it with a complex JSON data format. Again, they have to serve 10 times the number of bytes for the exact same data.

Yesterday I spend 1/2 day refreshing my trial scripts that downloaded quotes from other providers, and 3 hours updating my production scripts. Made them better, too--replaced a lot of ad-hoc inline (script) code with a call to an external function. That function can download quotes from 4 different providers, just by setting one parameter.
Probably half the time it took me was in cleaning up a bunch of old code.

Today, I'll revisit my code for extracting the Yahoo quotes from the JSON. That'll take maybe up until lunchtime.

For me, the most obnoxious thing is that Yahoo is the best source of getting NAV's for ETFs--just the ETF symbol with X before & after--ex. JTA and XJTAX. Google finance doesn't seem to provide these NAV symbols, so in a google sheet you have to grab it from yahoo. Which will not work now, and possibly won't work with the JSON data. Until web guru's figure out how. So....maybe next Wednesday.

EVentually Yahoo will probably close the JSON door, too. Then people will just scrape it off the web pages---adding even more overhead to Yahoo's data center. This race will continue until Yahoo throws in the towel and just stops providing data.

Amazing.
Print the post Back To Top
No. of Recommendations: 2
Ooops! I just tried to use rharmelink's URL for MMM and JNJ and this is what I got from Yahoo.

It has come to our attention that this service is being used in violation of the Yahoo Terms of Service. As such, the service is being discontinued. For all future markets and equities data research, please refer to finance.yahoo.com.

It seems they are trying to cut off everyone but a casual once in a while user they can blanket with advertisements.


Hah! Next thing you know, it'll come to their attention that the sun rises in the east.

Yeah, this is dumb. The people who are downloading quote data are doing it for a reason----and that reason won't be met by looking at the finance/yahoo/com web page.

Frankly, their finance web pages are unreadable now. It is loaded down with so much stuff--ads, trackers, videos, etc. that it kills my computer.

Each time they try to close a loophole, people figure out a way around it. The final end-result of the escalation will be that people will just scrape the web page. Like I already do at a couple of other sites. Once you figure out how to scrape data off of one web page, it's pretty easy to do it for others. The learning curve isn't very steep.

If they were smart, they'd offer a paid subsscription service for the simple data downloads they are shutting down. People would easily pay $5/mo, and that's so low that they'd not put in any effort to do workarounds to get the data for free. Of course, that would require somebody at Yahoo being smart.

They have the data, they should just monetize it instead of trying to prevent people from using it. The reason I keep coming back to the yahoo data is that what they have is better overall than what any other site that I've found has.
Print the post Back To Top
No. of Recommendations: 5
Today, I'll revisit my code for extracting the Yahoo quotes from the JSON. That'll take maybe up until lunchtime.

Probably too late, but I ran into an interest Google alternative today:

https://smf-add-in.blogspot.com/2017/11/tip-possible-google-...

It took me about 10 minutes to create this CSV file of S&P 500 stocks:

https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRob...

...which updates automatically.

Drawback is that the ticker list needs to be maintained within the Google sheet.
Print the post Back To Top
No. of Recommendations: 0
Slow? Puzzled... I have a workbook with 3 sheets of quotes filling up, 200 day historical prices, SMA functions, it takes... 10 seconds?

FC
Print the post Back To Top
No. of Recommendations: 1
Slow? Puzzled... I have a workbook with 3 sheets of quotes filling up, 200 day historical prices, SMA functions, it takes... 10 seconds?

Like I said, I mostly used their importHTML() and importData() functions, because I was extracting things out of web pages.

But, yes, their API functions are pretty fast. I hadn't really tried them because of my experience with the import functions. If I remember correctly, there's also a limit on the number of import functions that can be used.

When I ran into the article today on publishing a CSV file, it was really the first time I did a large number of GoogleFinance() functions. I was pleasantly surprised.
Print the post Back To Top
No. of Recommendations: 0
Well, I would have been done by lunchtime, but I got distracted by other things. It's good to be retired!
I got the yahoo JSON quotes working well before my daily 4PM automatic quote grabber ran. It's all good now, and gave me the opportunity to clean up a bunch of my scripts, now that the quote grabber is all in one function that everything else calls. Just flip a parameter and it will grab quotes from any of 4 sources.

I still have a problem in that Yahoo seems to be the only convenient place to get CEF NAV data. Unfortunately, googlefinance doesn't seem to have them, so my googlesheet portfolio is broken now. Starting to wonder if I should set up a private web server that would quickly grab NAV's from yahoo behind the scenes whenever it got accessed.
Print the post Back To Top
No. of Recommendations: 0
Thanks, Randy. I'd been looking for your imprimatur on the Google Sheets approach.

Tom
Print the post Back To Top
No. of Recommendations: 5
Speaking of wonderful Yahoo improvements, I now note that the Finance home page consumes a full CPU
at 100% load on my system, at least sometimes, in a way that manages to make the browser unresponsive in other tabs.
Perhaps some quirk of my system, but still, I am truly impressed with the consistency of their ability to find new ways to annoy people.
Replace actual financial headlines with a click bait feed? Sure! You wouldn't want people to have a reason to come to your site, right?

Jim
Print the post Back To Top
No. of Recommendations: 1
Speaking of wonderful Yahoo improvements, I now note that the Finance home page consumes a full CPU
at 100% load on my system, at least sometimes, in a way that manages to make the browser unresponsive in other tabs.
Perhaps some quirk of my system, but still, I am truly impressed with the consistency of their ability to find new ways to annoy people.


If it is a quirk of your system, it is also a quirk of mine.
The worst offender is Facebook, however. I have a 4-core 64-bit Xeon processor with 8 GBytes of RAM. If I go to Facebook, it takes a minute or two of 100% CPU processing to just display the stuff, and if I scroll a little, it saturates again. Sometimes, it saturates two processors. Other sites do this too, but not as bad. And some are pretty good.

I run Red Hat Enterprise Linux Server release 6.9 (Santiago) as an operating system and
firefox-52.4.0-1.el6_9.x86_64 as a web browser. (Firefox ESR)

More and more web sites put more and more crap on the screen. I had to abandon my weather app and find another because it would set up 5 or 6 sub-panels with the little arrows chasing their tails for extended periods of time when I just wanted to know what the weather for the day was. Plain text would do it but they larded it up with javascript, and hopped from one server to another, all technology show-off, but a PITA.
Print the post Back To Top
No. of Recommendations: 7
I now note that the Finance home page consumes a full CPU
at 100% load on my system, at least sometimes, in a way that manages to make the browser unresponsive in other tabs.


and

If I go to Facebook, it takes a minute or two of 100% CPU processing to just display the stuff,


Consider using a content blocker such as ublock origin: available for Chrome, Firefox, Edge, Opera, and Safari.

A small test on my system. Yahoo Finance takes about 1.5 sec to fully load & render. CPU usage goes to zero. Disable my ad blocker and it takes at least 5 seconds and the cpu usage never goes to zero. Basically pegging one cpu core.

All content blockers are not the same. Some of them now earn money by charging websites fees to let their ads through to the user.
Print the post Back To Top
No. of Recommendations: 1
Replace actual financial headlines with a click bait feed? Sure! You wouldn't want people to have a reason to come to your site, right?

Their news RSS feeds are a lot better. I subscribe to two (multiple restaurant stock, multiple retail stock). I've also set up a workbook with my add-in that can get them on the fly, including creating links. For example:

Ticker List ==>  WMT,AMZN 

Date Link Headline
2017-11-06 Link Why David Einhorn Is Betting against Amazon
2017-11-06 Link Amazon Fresh pulls the plug on deliveries in some markets
2017-11-06 Link [$$] Amazon's Roy Price Left Alleged Trail of Sexual Harassment
2017-11-06 Link Amazon Web Services' East Coast headquarters building for sale
2017-11-06 Link ‘Panic’ could set in after Saudi purge nabs ‘Warren Buffett of the Middle East’
2017-11-06 Link Jeff Bezos says this exercise convinced him to quit his Wall Street job and launch Amazon
2017-11-06 Link CVS: No Comment
2017-11-06 Link A Brief History of Retail
2017-11-06 Link ETFs with exposure to Amazon.com, Inc. : November 6, 2017
2017-11-06 Link Top Growth Stocks To Buy
2017-11-06 Link Michael Kors Holdings Ltd Is a Solid Retail Buy After Earnings Spike
2017-11-06 Link Amazon and Apple Getting Big Into Healthcare Won't Be Easy: Ex Apple CEO Sculley

And when I hover over the link, I see the summary of the story in a pop-up comment box. For example, if I hover over that second "Link", I see:

Amazon Fresh's business across several states is about to go stale now that the online retail giant is pulling the plug in some markets. In an email sent to customers across parts of California, Virginia, New York, New Jersey, Connecticut, Massachusetts, Pennsylvania, Delaware and Maryland, Amazon.com Inc. told customers their Fresh memberships would run until its last day of service on Nov. 30. The company didn't include an explanation or reasoning behind the decision to stop its same-day/next-day delivery of fresh produce and perishables.
Print the post Back To Top
No. of Recommendations: 0
In the same bind as everyone else, I stumbled on this:
http://investmentmoats.com/

While some of it is Yahoo bases, enough is Google based as it can be made to "work" pretty easily. It would have been great to have found it earlier with enough time to actually populate some of the sheets, but there's enough code and a decent backbone on the site to help many out.

Jeff
Print the post Back To Top
No. of Recommendations: 1
If you happen to be using Interactive Brokers, they have a really nice API that allows for real time quotes in Excel without any programming. I didn't even know it existed until Yahoo discontinued their CSV data. I got the whole thing working in 10 minutes, but I already had their Trader Workstation installed.

More info can be found here

https://interactivebrokers.github.io/tws-api/rtd_examples.ht...
Print the post Back To Top
No. of Recommendations: 0
If you happen to be using Interactive Brokers, they have a really nice API that allows for real time quotes in Excel without any programming.

Doesn't that require you to be logged in via their TWS for it to work?

I looked at it a few years ago and seem to recall that that was the case. I didn't persue it because of that. Plus, no need, since there were other places that were open & public.

FWIW, the previously-posted link to get yahoo quotes in JSON format worked great. Simple two line change to my download script. I just had to change the URL of the wget appropriately, then one new line to invoke jq (program) to parse the JSON and extract the price field(s).

Whenever that stops working, the next fallback will be to scrape the web page.
Print the post Back To Top
No. of Recommendations: 0
Indeed. You must be logged in to their tool, Trader Workstation. Since my MO is to get the quotes just before I make trades, it hasn't been a big deal for me.


The main reason I'm choosing to use this data source (as opposed to Yahoo JSON or something else), is Interactive Brokers has more incentive to maintain the data source. I'm actually paying Interactive Brokers, in the form commissions. They have a financial incentive to keep me happy. On the other hand, it is not apparent to me that Yahoo receives any revenue from users who use JSON (or the now defunct csv quoting), so what is there incentive to keep doing it? Direct visits to the web page generate advertisement, but pulling data doesn't.

The same argument could be made to any data source that you purchase vs. free data. As previously suggested in this thread, I wish Yahoo would just charge us $5 a month for their data.
Print the post Back To Top