2

I want to create a simple function that returns company name for every line that is filled with the Symbol via GoogleFinance function, however, I am not able to make the script work for every line.

Here is the example:

https://docs.google.com/spreadsheets/d/1cJDq0smLUmbylAnAep3WIz41GvqH_dhpnLjnLEpOmAQ/edit?usp=sharing

CODE 1:

// DOES NOT WORK    
=ARRAYFORMULA(IFS(ROW(B:B)=1,"NAME",A:A="","",TRUE,VLOOKUP(GOOGLEFINANCE(A:A, "name"),GOOGLEFINANCE(A:A, "name"),1,0)))

Once I change the A:A to A2 it loads the data correctly, but I did not find a way of repeating the function for each row

I tried a workaround:

// DOES NOT WORK
=ARRAYFORMULA(IFS(ROW(B:B)=1;"NAME";A:A="";"";TRUE;VLOOKUP(GOOGLEFINANCE(CONCATENATE("A";row()); "name");GOOGLEFINANCE(CONCATENATE("A";row()); "name");1;0)))

But the same result..

Any ideas how to make it work?

Thank you in advance!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
vohratom
  • 422
  • 7
  • 16
  • 4
    Please note that Excel does not have `GOOGLEFINANCE` nor `ARRAYFORMULA`. As such, any Excel tag is irrelevant, and I have removed them. – BigBen Sep 28 '22 at 13:55

1 Answers1

4

Use this

=BYROW(A2:A, LAMBDA(r, IF(r="",,GOOGLEFINANCE(r, "name"))))

enter image description here

Or this with the header

={"Name";BYROW(A2:A, LAMBDA(r, IF(r="",,GOOGLEFINANCE(r, "name"))))}

enter image description here

Osm
  • 2,699
  • 2
  • 4
  • 26
  • 1
    Working! Thank you very much Osm! ❤️ – vohratom Sep 28 '22 at 18:19
  • Hi Osm, I would love to use this piece of code but I need to do an IF statement to do the calculation only if in column C is stock type "Dividend" here is the piece of the code ```={"DIVIDEND"; BYROW(A2:A; LAMBDA(r;IF(C:C="Dividend";IFNA(SUBSTITUTE(IMPORTXML(CONCATENATE("https://finviz.com/quote.ashx?t=";r);'XML IMPORT'!C$3);".";",")/4);"-")))}``` I was thinking about ```r(C:C)``` but it did not work - any idea, please? :-) – vohratom Oct 10 '22 at 18:48
  • 1
    Hi @vohratom glad to hear back from you, make sure to see [Should posts be self-contained?](https://meta.stackexchange.com/questions/18669/should-posts-be-self-contained) and [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example); you have to [Ask a new question here](https://stackoverflow.com/questions/ask) with Google-sheet tag, the answers meant to benefit the poster and future visitors as well. see you [there](https://stackoverflow.com/questions/tagged/google-sheets). – Osm Oct 10 '22 at 19:19
  • Great point, thank you @Osm! Just posted here - https://stackoverflow.com/questions/74020088/how-to-execute-the-function-only-when-there-is-a-specific-value-in-another-colum – vohratom Oct 10 '22 at 19:55
  • 1
    Nice solution... I was just facing this too. FOr some reason `Filter` doesn't work, but byRow does...? weird, but manageable! – pgSystemTester Apr 01 '23 at 19:05