Author Topic: Google spreadsheet tips and tricks  (Read 40494 times)

racemize

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 2767
Re: Google spreadsheet tips and tricks
« Reply #60 on: August 03, 2018, 08:25:32 AM »
Given that google finance seems to be dying a horrible slow death, I spent most of yesterday working on an alternative solution for indices and stocks via alpha vantageís API. I can try to post it (involves scripts) if folks are interested.


Dynamic

  • Hero Member
  • *****
  • Posts: 617
Re: Google spreadsheet tips and tricks
« Reply #61 on: August 03, 2018, 08:28:30 AM »
It sounds good to have an alternative, and we can only learn from seeing the scripts.

Please do if it's not to much trouble.

Liberty

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 12873
  • libertyrpf.com
    • http://www.libertyrpf.com
Re: Google spreadsheet tips and tricks
« Reply #62 on: August 03, 2018, 10:26:24 AM »
Given that google finance seems to be dying a horrible slow death, I spent most of yesterday working on an alternative solution for indices and stocks via alpha vantageís API. I can try to post it (involves scripts) if folks are interested.

I'd be interested in having a look. I've been testing various alternatives and have looked at a few that are in development.

gjangal

  • Full Member
  • ***
  • Posts: 217
Re: Google spreadsheet tips and tricks
« Reply #63 on: August 03, 2018, 02:42:07 PM »
sp500tr for google finance looks like itís is working again.

racemize

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 2767
Re: Google spreadsheet tips and tricks
« Reply #64 on: August 03, 2018, 04:50:11 PM »
Here are the scripts I'm using:

/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
 
  try{
    // /rates/EUR
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
   
    var patharray = xpath.split("/");
    //Logger.log(patharray);
   
    for(var i=0;i<patharray.length;i++){
      json = json[patharray];
    }
   
    //Logger.log(typeof(json));
   
    if(typeof(json) === "undefined"){
      return "Node Not Available";
    } else if(typeof(json) === "object"){
      var tempArr = [];
     
      for(var obj in json){
        tempArr.push([obj,json[obj]]);
      }
      return tempArr;
    } else if(typeof(json) !== "object") {
      return json;
    }
  }
  catch(err){
      return "Error getting data"; 
  }
 
}

// Get your API key for free from this site: https://www.alphavantage.co/support/#api-key
api_key = GET ONE FROM ALPHAVANTAGE
url = 'https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol='


/**
* Imports JSON from Alpha Vantage into your spreadsheet Ex: getAlphaVantageClosePrice("AAPL")
* @param symbol Ticker symbol of your stock
* @customfunction
*/
function getAlphaVantageClosePrice(symbol) {
  var response = UrlFetchApp.fetch(encodeURI("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" + symbol + "&apikey=" + api_key)).getContentText();
  var data = JSON.parse(response);
  var timeSeries = data["Time Series (Daily)"];
  var value = 0;
  for (var dates in timeSeries) {
    value = parseFloat(timeSeries[dates]["4. close"]);
    break;
  }
  return value;
}


/**
* Imports JSON from Alpha Vantage into your spreadsheet Ex: getAlphaVantagePrice("AAPL")
* @param symbol Ticker symbol of your stock
* @customfunction
*/
function getAlphaVantagePrice(symbol) {
  var response = UrlFetchApp.fetch(encodeURI("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=" + symbol + "&interval=1min&apikey=" + api_key)).getContentText();
  var data = JSON.parse(response);
  var timeSeries = data["Time Series (1min)"];
  var value = 0;
  for (var dates in timeSeries) {
    value = parseFloat(timeSeries[dates]["4. close"]);
    break;
  }
  return value;
}

/**
* Imports JSON from Alpha Vantage into your spreadsheet Ex: getAlphaVantagePrice("AAPL")
* @param symbol Ticker symbol of your stock
* @customfunction
*/
function getAlphaVantagePriceChange(symbol) {
  var response = UrlFetchApp.fetch(encodeURI("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" + symbol + "&apikey=" + api_key)).getContentText();
  var data = JSON.parse(response);
  var timeSeries = data["Time Series (Daily)"];
  var initialValue = 0;
  for (var dates in timeSeries) {
    initialValue = parseFloat(timeSeries[dates]["1. open"]);
    break;
  }
  var response = UrlFetchApp.fetch(encodeURI("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=" + symbol + "&interval=1min&apikey=" + api_key)).getContentText();
  var data = JSON.parse(response);
  var timeSeries = data["Time Series (1min)"];
  var finalValue = 0;
  for (var dates in timeSeries) {
    finalValue = parseFloat(timeSeries[dates]["4. close"]);
    break;
  }
  return finalValue/initialValue-1;
}

function getMidPrice(ticker) {
  ticker = encodeURI(ticker);
  var response = UrlFetchApp.fetch("https://query2.finance.yahoo.com/v7/finance/options/" + ticker);
  var chain = JSON.parse(response.getContentText());
  var ask = parseFloat(chain.optionChain.result[0].quote.ask);
  var bid = parseFloat(chain.optionChain.result[0].quote.bid);
  var mid = (bid + ask) / 2;
  return mid;
}

racemize

  • Lifetime Member
  • Hero Member
  • *****
  • Posts: 2767
Re: Google spreadsheet tips and tricks
« Reply #65 on: August 03, 2018, 04:53:25 PM »
This gets the latest close price:
=getAlphaVantageClosePrice("^SP500TR")

I modify the close price with the current change to get daily TR via:
=getAlphaVantagePriceChange("^GSPC")

For stocks, you can use the above or a live pull (doesn't work on SP500TR, but will work on GSPC):
=getAlphaVantagePrice(B5) (where B5 is a stock ticker)
or:
=IMPORTJSON("https://api.iextrading.com/1.0/stock/AAPL/quote","latestPrice") (replace AAPL with a stock ticker)

Dynamic

  • Hero Member
  • *****
  • Posts: 617
Re: Google spreadsheet tips and tricks
« Reply #66 on: August 06, 2018, 08:41:38 AM »
Thanks @racemize. That looks very useful.

I note that Google Finance is currently working for .INX and SP500TR but odd things go awry from time to time and a backup is very useful.

Perhaps an IFERROR function would be good to use the script (which I imagine isn't going to be as fast as GOOGLEFINANCE) whenever GOOGLEFINANCE returns an error message. It would then say Loading... for a few moments until the AlphaVantage script has finished fetching the data.

york

  • Jr. Member
  • **
  • Posts: 66
Re: Google spreadsheet tips and tricks
« Reply #67 on: April 25, 2020, 12:45:19 PM »
Has anyone ever tried to use the questrade api in googlesheets?