No. of Recommendations: 14
I believe there are already Excel (or other programming or website) tools available to obtain historical price quotes for stock symbols using Yahoo and JSON data, but I thought I'd share an Excel workbook that captures a few elements for a single stock. This version captures the close and adjusted close by date for a full history, and separately any dividends by date in that history. This could be used as a starting point to build a workbook that cycles through a list of stocks to populate historical prices for them (e.g., in a separate tab using an additional macro).
I'm not an expert on capturing data like this. For example, I don't really know much about "WinHTTP..." and related tools in Excel macros. But starting with a helpful VBA macro from a friend, the attached workbook works on my Windows PC to gather the data history.
This workbook allows you to specify a stock symbol and how many trading days you would like history for. "SPY" and "260" are the initial values shown. "260" trading days is a little more than a calendar year. Type in the symbol and trading days you want, then click on the macro button to execute. If trading days is set to 30000, a full history should be generated for any symbol. For example, for "^GSPC" the full history for the S&P 500 index starts at 12/30/1927.
Several caveats about this workbook and its VBA macros. I'm not sure it will work on other computers (PCs and Macs, with different setups). Given my uncertainty on "WinHTTP..." type commands, this may not be the best way to approach grabbing the JSON data. Or some other coding may be useful to make it more robust, etc., and/or I'm sure there are more elegant ways to extract the data items. There is only limited error-checking included, so some situations may arise that cause an error it's not prepared for. In other words, use at your own risk. And if someone with better skills/knowledge than me would like to incorporate improvements, feel free to borrow what's here, or use this as a starting point, to create a better version.
Let me know if you try this and are successful, if you have any problems, or if there is some glaring error in how the macro retrieves the JSON data.
---
Here's the link to download the Excel workbook:
https://www.dropbox.com/scl/fi/uml6sa8q46dsclixtas...Use the download symbol near the upper right, and indicate that you simply want to download, without signing into dropbox or moving it to another dropbox account.
Note that this is a macro-enabled Excel workbook, so you may get a "security risk" message of some sort when opening the workbook. On my PC, I had to go to file explorer, right-click on the file name and, in Properties, click on "Unblock" near the bottom to allow the macros to be used.
If you would like to try this but don't want to download a macro-enabled workbook for safety reasons, I could share a non-macro version of the workbook (.xlsx) and separately share a text version of the macro code. Then you could assemble the ingredients and save as your own ".xslm" version that allows you to run the macros.
