Can you create dynamic formulas in Google Sheets?

Question

So I'm just starting out creating a portfolio tracker within Google Sheets. I'm using the Google Finance methods to get the stocks name and all the relevant data that I need. The only issue is that I can't figure out how to populate the specific data I need without having to manually type out the same formula's for each stock I want data for.

For example... Each row in the first column would contain the ticker symbol for that specific stock. If I bought a new stock, I would just type in the ticker symbol in cell A1 and this would populate the necessary fields such as price and so on. If I bought another stock I would essentially do the same thing but now in A2.

I know that you can get the price of a stock by doing

=GOOGLEFINANCE(A1, "price")

but is there any way to make it dynamic? something like:

=GOOGLEFINANCE(A(Row(ref)), "price")?

Any suggestions would be helpful. Maybe there's even an addon that makes this process simpler, but I'm not sure.

Answer 1

try:

=ARRAYFORMULA(IFERROR(GOOGLEFINANCE(A1:A10, "price")))

Answer 2

You just have to write the function for A1:

=GOOGLEFINANCE(A1, "price")

And then drag the little square on the cell down. It will automatically pick up the correspondant number of the row in the A column.

You can set-up your sheet to have like 100 rows used, and when you add the ticker it will automatically calculate it.

If you don't want th #N/A to show you can do it like:

=IFERROR(GOOGLEFINANCE(A1, "price"))