No. of Recommendations: 10
Following on from this thread: https://boards.fool.com/investopedia-historical-data-3417139...

and taking some tips from Randy, I wrote a Google Sheets Function to download the adjusted close
price for a stock from Yahoo Finance. I am not a programmer so this is probably missing lots of
error checking, etc but it seems to work and it performs a lot better than my previous efforts in
the thread referenced above.

I tried this in a sheet with 30 instances of the function and for all 30 cases, I had the third 
parameter point to the same cell. When I changed the value in this cell it seems to be able to 
refresh all thirty values at the same time.

Example of usage would be: =yahooAdjClose(A2, B1, A1) in say cell B2

Where:
 * Cell A2 contains the Value "SPY"
 * Cell B1 contains the Value "31 Dec 2018"
 * Cell A1 contains the Value 0

If you want to refresh the data, just change the value of cell A1.

The code is below.

Enjoy
StevnFool

/** @OnlyCurrentDoc */


/**
 * Gets historical adjusted close price from Yahoo Finance.
 * Note that if there is no price data for the data entered it will return the adjusted close price for the most recent day prior
 * to that if there is a price within the previous 7 calendar days.
  
 * @param {string} ticker The ticker symbol of the stock.
 * @param {string} dateString The date for which to retrive the adjusted close price. It should be a string that Javascription will understand as a date. Example: "31 Dec 2018".
 * @param {number} refreshVar This can be any value. If the value is changed, it will cause the function to refresh.
 * @return The adjusted close price for the ticker and date
 * @customfunction
 */
function yahooAdjClose(ticker, dateString, refreshVar) {
  var d = new Date(dateString);
  var period2 = (d.valueOf() / 1000) + 64800; // convert date from milliseconds and shift to match the Yahoo dates
  var period1 = period2 - 604800; // 604800 is the number of seconds in a week. The idea here is to pull in a week of data
                                  // so that if the selected date is a closed market day, it will pull in data for the previous
                                  // week and show the most recent adjusted close
  var url = 'https://finance.yahoo.com/quote/' + ticker + '/history?period1=' + period1 + '&period2=' + period2 + '&interval=1d&filter=history&frequency=1d';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var position = xml.search('adjclose'); // finds first instance of "adjclose" in the test of the returned page
  xml = xml.slice(position); // removes all text from the string 'xml' prior to 'adjclose'
  position = xml.search('}'); // finds the first instance of "}" which is the first character after the price value
  return(Number(xml.slice(10, position))); // Returns the portion of text that contains the value and converts it to a number
}
Print the post  

Announcements

What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.