google-sheetsgoogle-apps-scriptgoogle-finance

Writing a googlefinance wrapper

I'm working on a Google Sheet to track my stock portfolio. I use the googlefinance function to retrieve a stock price or last day change, but learned that it does not support all exchanges that I trade on.

I then thought to write a wrapper called simply finance, passing off the fetching of prices to Yahoo Finance in case the exchange isn't supported by Google. The wrapper would also give me the flexibility to make my sheet a bit more clean as well. For instance, Google and Yahoo use different indicators for stock exchanges. For instance, the Hong Kong Exchange is HKG on Google but HK on Yahoo. I would just like to type the exchange code that I use, and handle it in the wrapper. Here's an array with examples:

// exchange code that I use, that Google uses, Yahoo uses, exchange currency
[HKG, HKG,    HK,   HKD],
[TYO, TYO,    T,    JPY],
[TPX, TPE,    TW,   TWD],
[KRX, KRX,    KS,   KRW],
[FRA, FRA,    F,    EUR],
[NDQ, NASDAQ, null, USD],
[NSY, NYSE,   null, USD]

I later stepped off the idea of using an array, but just hardcode a switch statement, but still giving the array gives some background.

Now consider the following sheet and script:

   A    B     C
1  TYO  9984  =finance(A1, B1, "price")
2  NDQ  AAPL  =finance(A2, B2, "price")
3  NSY  GE    =finance(A3, B3, "price")
4  HKG  0865  =finance(A4, B4, "price")

function finance(exchange, ticker, type) {
    if (exchange == 'TYO') { // googlefinance() doesn't support TYO         
        return yahoofinance(ticker + '.T', type);
    }
    else {
        switch (exchange) {
          case 'HKG': return googlefinance('HKG:'    + ticker, type); break;
          case 'NDQ': return googlefinance('NASDAQ:' + ticker, type); break;
          case 'NSY': return googlefinance('NYSE:'   + ticker, type); break;
        }
    }
}

function yahoofinance(ticker, type) {
    return true; // implement later
}

I have 2 questions:

Solution

  • Issues

    Issue 1:

    The code returns undefined because you are returning something undefined. Here:

    return googlefinance('HKG:'    + ticker, type)

    googlefinance hasn't been defined anywhere in the script.

    Your goal must be to return a string instead.

    Issue 2:

    Modification 1:

    The switch statement overcomplicates the code and it does not add value.

    You can replace it with a simple string concatenation ("a"+"b") or more convenient with template literals:

    return `=googlefinance("${exchange}:${ticker}", "${type}")`;

    this will return something in this format:

    =googlefinance("NDQ:AAPL", "price")

    but this will be a text in your sheet, it won't work as a formula.

    Modification 2:

    Change your approach. Instead of using a custom formula, use a regular google apps script function. You won't be able to use it as a custom formula then, but you can execute it in multiple ways, starting with a simple manual execution. Later, search other threads to see how you can execute that from custom menus or triggers.

    Solution - Regular Function approach:

    function regularFinance() {
  
  const type = "price";
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // put the name of your sheet
  const vals = sh.getRange('A1:B'+sh.getLastRow()).getValues();
  const formulas = [];
  
  vals.forEach(r=>{
     let dt = r[0]=='TYO'? yahoofinance(r[1], type):
            `=googlefinance("${r[0]}:${r[1]}", "${type}")`;             
     formulas.push([dt]);            
  })
  // paste data in column C
  sh.getRange(1,3,formulas.length,1).setValues(formulas);
  
  function yahoofinance(ticker, type) {
      return true; // implement later
  }

}

    Like I said, this function is not a custom formula. It is a regular function which needs to be executed. One way to do that is to manually execute it from the script editor:

    enter image description here

    Output:

    Make sure to correct the formulas. I am not familiar with what you want to achieve, so I will leave the formulas to you.

    enter image description here