Google Sheets, rename googlefinance headers

Question

I am using googlefinance to pull data from multiple tickers and I would like to differentiate by having different column names.

1. One solution I thought about is to rename the headers, I tried using nested query but it did not work.

2. Another (not very elegant) solution that I am thinking is to populate a row above with the ticker name, every 6 cells in a row, then concat the two rows.

You can see here: https://docs.google.com/spreadsheets/d/1nDkIvVIWmibB2jiLyJCeHPNxdA11A1AMoavI-ICtSdE/edit?usp=sharing

What I tried and did not work:

=QUERY(GOOGLEFINANCE("C", "all", "02.01.2023", "20.12.2023", "weekly"), "SELECT newDate as Date", 1)
enter code here

and

=QUERY(GOOGLEFINANCE("C", "all", "02.01.2023", "20.12.2023", "weekly"), "SELECT * LABEL Date 'newDate'", 1)

The final outcome should be as example: USDCHF Date; USDCHF Open; .... APPL Date; AAPL Open; APPL Volume .... AMZN Date;

Answer 1

You may try:

=reduce(torow(,1),tocol(A:A,1),lambda(a,c,hstack(a,let(Σ,googlefinance(c, "all", "02.01.2023", "20.12.2023", "weekly"),vstack(index(regexextract(c,":(.*)$")&" "&chooserows(Σ,1)),chooserows(Σ,sequence(rows(Σ)-1,1,2)))))))