(BTW, if you can’t figure how that ‘between-the-commas’ formula works, you’re not alone. That could be done with VBA or perhaps with the ‘text to columns’ tool, but I’ve chosen to just use spreadsheet formulas. Excel will only import it as text, namely as lines of comma separated variables for each date, which need manual parsing. Query table parsing doesn’t work with this data. There are lots of ways to import that into Excel (including manually, as a csv file), but the method I use is an Excel query table. Historical prices are also available from the JSON feed, but I find it simpler to just use Yahoo’s data download service. It needs Excel 2010+:Ī couple of people asked in comments how to get historical stock prices. Consider first whether your own skills are sufficient to understand and accept the risks. This stuff is for private use only, and of course comes with no warranty whatsoever - expressed or implied, no support, no responsibility, no liability, nothing.
It helps if you know some VBA, but that isn’t essential. There’s a second function StockName() which returns the long name associated with the ticker code. It is implemented via a user-defined function called StockQuote(), which takes the Yahoo ticker code (STOCKCODE.exchangecode). I’ve written some code to instead get near real-time prices via Yahoo’s JavaScript Object Notation (JSON) data feed. Yahoo has also now included some simple obfuscation in its data download service (which has since been reverse-engineered, and may as a result be further protected). That resulted in a huge load on Yahoo’s service leading to it being discontinued last November. Unfortunately the API service was abused to the extent that some even purported to sell trading applications based around it (go figure). There have been lots of VBA implementations and even a direct spreadsheet formula implementation (no VBA). 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. Updated - now includes historical prices.
0 kommentar(er)
