The Motley Fool Discussion Boards

Previous Page

Investing/Strategies / Mechanical Investing

URL:  https://boards.fool.com/for-any-google-sheets-users-this-seems-to-be-a-34173811.aspx

Subject:  Re: Investopedia Historical Data Date:  4/4/2019  6:22 PM
Author:  StevnFool Number:  273817 of 278465

For any Google Sheets users, this seems to be a simple enough way to extract the historical adjusted close from Yahoo for a single ticker and date at a time. I won't guarantee that this will continue to work but it works today …

In my case:

Cell A2 contains the ticker symbol
Cell B1 contains the date for which I want the adjusted close price
Cell B2 contains the following formula

=index(importHTML("https://finance.yahoo.com/quote/" & $A2 & "/history?period1=" & B$1*86400-2209078800 & "&period2=" & B$1*86400-2209078800 & "&interval=1d&filter=history&frequency=1d", "table",1),2,6)

If you had a list of tickers in column A and a list of dates in row one, you can copy this formula across and down.

So far, I have been unsuccessful in getting Excel (using VBA to parse the data) to pull in similar data starting with the same basic URL.

StevnFool
Copyright 1996-2020 trademark and the "Fool" logo is a trademark of The Motley Fool, Inc. Contact Us