Google spreadsheet tips and tricks

[Dynamic]

misterkrusty, I don't think fill color is settable on a per-sheet basis, so you're having to format the cells rather than the background of the sheet.

 

When you cut cells and paste them, the cells are cleared to the default formatting.

 

It may be possible to use the script editor to create a custom function that will do the pasting then apply the formatting you desire to the cells left behind. There's some guidance here, if that's an approach that might work for you:

http://www.mousewhisperer.co.uk/drivebunny/setting-cell-format-in-google-sheets-using-apps-script/

[racemize]

Old question I've never worked on figuring out and wasted a bit of time on this morning:

 

Is there a fast way to calculate cumulative gain from a set of gains?

 

E.g., annual gain in column A:

15%

4%

30%

 

Currently, I calculate total gain by: =(1+A1)*(1+A2)*(1+A3)-1

There's got to be something easier (other than creating a new column that is A+1)?  It would be a ProductSum, but I don't think such a function exists. 

 

 

 

[EliG]

Old question I've never worked on figuring out and wasted a bit of time on this morning:

 

Is there a fast way to calculate cumulative gain from a set of gains?

 

E.g., annual gain in column A:

15%

4%

30%

 

Currently, I calculate total gain by: =(1+A1)*(1+A2)*(1+A3)-1

There's got to be something easier (other than creating a new column that is A+1)?  It would be a ProductSum, but I don't think such a function exists.

 

Google Sheets

 

=ARRAYFORMULA(PRODUCT(1+A1:A3))-1

 

 

Excel or OpenOffice/LibreOffice

 

Type PRODUCT function:

 

=PRODUCT(1+A1:A3)-1

 

and hit Ctrl-Shift-Enter. The expression changes to an array formula:

 

{=PRODUCT(1+A1:A3)-1}

 

giving you the total return.

 

[EliG]

Ctrl-Shift-Enter works in Google, too. You can type:

 

=PRODUCT(1+A1:A3)-1

 

and hit Ctrl-Shift-Enter. Google adds ArrayFormula() wrapper.

 

[racemize]

Ctrl-Shift-Enter works in Google, too. You can type:

 

=PRODUCT(1+A1:A3)-1

 

and hit Ctrl-Shift-Enter. Google adds ArrayFormula() wrapper.

 

That's very helpful, thanks.  I was not familiar with Array Formula--I had thought it was used for a group of cells not a single one.

[Dynamic]

I've moved to script editor based..

 

eg:

=getMidPrice("AAPL180119C00100000")

 

where tools->script editor, then enter:

 

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;

}

 

ymmv

 

@bargainman - thanks for this approach.

 

I've implemented it in Scripts (Sheets also has Macros and Macro Recorder now, which is a useful interface for automating tasks and helping to write scripts)

 

I was looking for an Ask price and got it working, but now I notice that although the script is pulling the correct "ask" price from the query URL which I checked manually, some of the options prices on https://query2.finance.yahoo.com/v7/finance/options/ + TICKER are not updating, even daily, so are different from those shown on a site like marketwatch.com which does seem to be updating

 

I may ask Yahoo to look into this, but does anyone know if it's possible to query structured data from other sites in a similar way so I could implement an alternative? For now, I run a separate worksheet where I can copy and paste the table from the marketwatch site (use Ctrl+Shift+V to Paste Values only). I might try Record Macro to see if I can implement that in a script, but I suspect it won't record my visit to their site and the Copy part of the procedure.

[Hielko]

Old question I've never worked on figuring out and wasted a bit of time on this morning:

 

Is there a fast way to calculate cumulative gain from a set of gains?

 

E.g., annual gain in column A:

15%

4%

30%

 

Currently, I calculate total gain by: =(1+A1)*(1+A2)*(1+A3)-1

There's got to be something easier (other than creating a new column that is A+1)?  It would be a ProductSum, but I don't think such a function exists.

 

Google Sheets

 

=ARRAYFORMULA(PRODUCT(1+A1:A3))-1

 

 

Excel or OpenOffice/LibreOffice

 

Type PRODUCT function:

 

=PRODUCT(1+A1:A3)-1

 

and hit Ctrl-Shift-Enter. The expression changes to an array formula:

 

{=PRODUCT(1+A1:A3)-1}

 

giving you the total return.

Thanks, never figured this one out before as well.

[racemize]

.inx and SP500TR stopped working entirely today--anyone else having this issue?

[Dynamic]

Yes, having exactly the same issue. I suspect their data supplier has issue but sometimes the ticker needs modifying to include something before a colon, and I find out searching Google / Finance results. Today, even the suggested search results list results shows INDEXCBOE:.INX yet you click it and it doesn't find any results.

 

I wonder if it's down because it's end of month?

[Dynamic]

I had reported both to Google Finance's team using the feedback form.

 

SP500TR is now working again on both Google search and GOOGLEFINANCE("SP500TR", "price") function. That is more important to me than .INX

 

However, .INX is still not working and I cannot find the normal capital-only index under any other symbol in Google Finance search (except the .INX version that returns no results). Perhaps that will be fixed later.

 

If it becomes a persistent enough problem there are ways of scraping it from other sites using a macro function, but I'd rather use GOOGLEFINANCE() if possible.

 

UPDATE: SP500TR just stopped working again! That was short lived!

 

UPDATE 2: They both worked for a short while, then stopped working once more! Clearly something behind the scenes is changing from time to time, so maybe it will start working again soon

[racemize]

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]

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]

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]

sp500tr for google finance looks like it’s is working again.

[racemize]

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]

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]

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]

Has anyone ever tried to use the questrade api in googlesheets?