The Motley Fool Discussion Boards

Previous Page

Investing/Strategies / Mechanical Investing


Subject:  Google Sheets Function: Yahoo Adj Close Date:  4/13/2019  2:33 PM
Author:  StevnFool Number:  273872 of 275947

Following on from this thread:

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

 * 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.


/** @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 = '' + ticker + '/history?period1=' + period1 + '&period2=' + period2 + '&interval=1d&filter=history&frequency=1d';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var position ='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 ='}'); // 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

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