Pull Yahoo JSON historical stock gegevens into Excel
An alternative to the EmulateURL CSV interface–Now with Adjusted Close prices
Yahoo’s JSON based interface
Many people were blindsided ter May 2018 by Yahoo Finance complicating the ancient and trusty http://ichart.finance.yahoo.com/table.csv URLs which returned Comma Separated Values (CSV) for historical stock gegevens. I have published a workaround for that problem, but I also desired to have a backup ready te case the interface gets even more obscure or goes away entirely.
Another free source of historical stock gegevens which te many ways is more powerful is the https://query1.finance.yahoo.com/v7/finance/chart URLs, however thesis URLs come back JSON (Java Script Object Notation) format gegevens, so are a little stiffer for Excel to overeenkomst with than the old CSV format.
There are a few distinct differences:
- JSON gegevens goes down to 1 minute granularity (1 day wasgoed the most granular for CSV).
- JSON gegevens is close to positivo time, only a minute or two delay on the one minute gegevens it seems, for USA exchanges.
- You can’t get dividends and splits with the JSON interface, at least I toevluchthaven’t figured out how. Fortunately the gegevens is split adjusted already, which is what I need. But you need to go elsewhere for dividends (EmulateURL works).
- You can’t specify a commence date and end date with JSON like you could with CSV, but you need to specify a range (e.g. “4y” would terugwedstrijd four years of gegevens).
- All timestamps are GMT (also known spil UTC) with JSON
Note that Yahoo! emerge to have immobile the Adjusted Close problem, so adjusted close prices are now available with this interface.
Implements for getting Yahoo JSON historical stock gegevens into Excel
Wij’ve developed a VBA function for requesting JSON historical stock gegevens then putting the gegevens into spreadsheet format, and have incorporated that code into a plain spreadsheet. Here is a brief movie of the code ter act: