Google finance as an argument for ArrayFormula

user_6638399 0
Viewed 3317
=ARRAYFORMULA(IF(ROW(B:B)=1, "Share Price", IF(ISBLANK(B:B), "", googlefinance(B:B, "price"))))

I am trying to make row 1 (my header row) have a name, and then below that, if a valid ticker symbol is an input in column B, have google finance fetch the price of that stock. However, I am getting N/A in all of the cells underneath "share price" and can't figure out why.

Help please.

edit: Link to the sheet: https://docs.google.com/spreadsheets/d/1lyYYzLrFHfjojlL27bQ6ligNz2DBVpSM24jYfAVBVQs/edit?usp=sharing

array-formulas google-sheets-formula google-apps-script google-finance google-sheets
2 Answers

GOOGLEFINANCE is kinda ArrayFormula already so this will not work as you would expect...

you will need to use script:

function onOpen(e){this.arrayThis("C1:C");}    //COLUMN WHERE YOU WANT TO HAVE THE RESULT
function arrayThis(range){
SpreadsheetApp.getActiveSpreadsheet().getRange(range).setValue(SpreadsheetApp.getActiveSpreadsheet().getRange(range).getCell(1,1).getFormula());
}

where C1 needs to be:

=IF(ROW(B1)=1, "Share Price", IF(B1="",,GOOGLEFINANCE(B1, "price")))

0

user_56326290

Use the following formula in the cell C1:

=MAP(B:B,LAMBDA(ticker,IF(ROW(ticker)=1,"Share Price",if(isblank(ticker),,GOOGLEFINANCE(ticker,"price")))))

Explanation

This is a new method since the introduction of LAMBDA and its helper functions in Google Sheets in August 2022.

Some inconveniences of GOOGLEFINANCE may now be solved by using MAP(LAMBDA) around it instead of ARRAYFORMULA. The trick here is, as far as I understand, that MAP(LAMBDA) calculates the specified formula for each row in the input array separately (effect similar to manually expanding the formula over the whole range), whereas ARRAYFORMULA passes the whole array as an argument to the formula (GOOGLEFINANCE is special and doesn't work intuitively with such input).

This general method can be used to pass another arguments to the GOOGLEFINANCE function in a similar way, that is from specified ranges. If one of such specified arguments should be start_date (in this case without end_date|num_days specified), then we additionally need to use INDEX(GOOGLEFINANCE(),2,2), because the output of GOOGLEFINANCE with start_date (and no end_date|num_days) will be a 2×2 array, where only the last value is the one we need.

Another example

A similar problem that I struggled with, to better illustrate this method: assume that we want the conversion rates between currencies specified in ranges A2:A, B2:B (with their appropriate three letter codes), in each case from a date specified in range C2:C. The solution would be the following formula in cell D2:

=MAP(A2:A,B2:B,C2:C,LAMBDA(currency_1,currency_2,date,INDEX(GOOGLEFINANCE("currency:" & currency_1 & currency_2,"price",date),2,2)))
user_200146330
Related