Google Sheets, populate with multiple arrays

Question

I am using GOOGLEFINANCE() function to pull historical data from a ticker, all works good and I get an array with a fixed number a columns and a dynamic number of rows. Now I want to create a database and pull data from multiple tickers (which I store in column A, and I add manually). The database has to expand automatically based on which tickers I add as new rows.

I have made the example here: https://docs.google.com/spreadsheets/d/1nDkIvVIWmibB2jiLyJCeHPNxdA11A1AMoavI-ICtSdE/edit?usp=sharing

What I tried is to populate the initial google finance formula every 6 column and pull the data.

=ARRAYFORMULA(IF(MOD(COLUMN(A1:Z1) - COLUMN(A1) + 1, 6) = 1, GOOGLEFINANCE(A1:A, "all", "02.01.2023", "20.12.2023", "weekly"), ""))

That does not work. But maybe there is a better way?

Answer 1

You may try:

=reduce(torow(,1),tocol(A:A,1),lambda(a,c,hstack(a,googlefinance(c, "all", "02.01.2023", "20.12.2023", "weekly"))))