Near real-time Yahoo stock quotes in Excel

You may know that Yahoo Finance has for a long time provided near real-time global stock quotes via its website and via a web API service, which many have used as a convenient source for Excel data. There have been lots of VBA implementations and even a direct spreadsheet formula implementation (no VBA).

Unfortunately the API service was abused to the extent that some even purported to sell trading applications based around it (go figure). That resulted in a huge load on Yahoo’s service leading to it being discontinued last November. Yahoo has also now included some simple obfuscation in its chart data download service (which has since been reverse-engineered, and may as a result be further protected).

I’ve written some code to instead get near real-time prices via the Yahoo web charting app’s JavaScript Object Notation (JSON) data feed. It is implemented via a user-defined function called StockQuote(), which takes the Yahoo ticker code (STOCKCODE.exchangecode). There’s a second function StockName() which returns the long name associated with the ticker code.

It helps if you know some VBA, but that isn’t essential. This stuff is for private use only, and of course comes with no warranty whatsoever, expressed or implied. It needs Excel 2010+:


Leave a Reply