How do you move logic from Google Sheet formula to Javascript?-CodePudding

I have a function yahoofinance which queries Yahoo!Finance for stock data based on a stock ticker. Before I can call the function, I need to do some modifications to the input.

Tickers in my sheet are of the form [venue].[company], for example XNAS.GOOG (Google at Nasdaq), XAMS.INGA (ING Bank at Amsterdam) and XSWX.CRSP (Crispr at Swiss Exchange).

The codes that I use for the venues are international, but most API's don't use these same codes. For instance, the Amsterdam stock exchange officially is XAMS, but AMS on Google API and AS on Yahoo. So I have a sheet called Exchanges with all these venues listed. When calling my custom yahoofinance function, it takes the standard 'XMAS.INGA' as input, then cuts off the venue (XAMS) and looks up the Yahoo name (AS), then passes INGA.AS to the Yahoo API:

=yahoofinance(index(split(A2, "."), 0, 2) & "." & vlookup(index(split(A2, "."), 0, 1), Exchanges!E:J, 6, false))

function yahoofinance(ticker) {
  // send ticker to Yahoo API
}

The logic, which is now in cell B2 in the sheet, is getting complicated to follow. I would like to move it, if possible, into the function and pass only the ticker from A2:

=yahoofinance(A2)

function yahoofinance(ticker) {
    const venue = ticker.split();
    // etc

    // end up with INGA.AS, and pass to Yahoo API
}

My question is: is it possible to move the logic that is now in my sheet, into the function? For example, if and how can I perform the same vlookup from within yahoofinance? Is it even possible?

I am planning to really extend the functionality of yahoofinance, perhaps even rework it to a generic finance function which also takes as argument to which API you want to pass a ticker: =yahoofinance(ticker, api), so any help to get me started with a solid basis here is greatly appreciated.

CodePudding user response：

You can try this custom formula to get the modified value:

Script:

function yahoofinance(ticker) {
  var [venue, company] = ticker.split("\.");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Exchanges');
  var lastRow = sheet.getLastRow();
  // get Exchanges!E:J values
  var mapping = sheet.getRange(1, 5, lastRow, 6).getValues();
  // lookup equivalent, filter then get 6th column and append to company
  var modifiedTicker = company   '.'   mapping.filter(row => row[0] == venue).flat()[5];
  // you now have the modified ticker. Use it on your function.
  // ...
  return modifiedTicker;
}

B7 formula:

=index(split(A2, "."), 0, 2) & "." & vlookup(index(split(A2, "."), 0, 1), Exchanges!E:J, 6, false)

Script:

B7 formula:

Output: