I'm trying to display multiple stocks in a single table. I have the following formula For A2:

=QUERY(GOOGLEFINANCE(B1, "close", EDATE(TODAY(), -LEFT("36 Months", FIND(" ", "36 Months"))), TODAY(), "DAILY"), "offset 1", 0)

And the following formula for C2:

=QUERY(GOOGLEFINANCE(C1, "close", EDATE(TODAY(), -LEFT("36 Months", FIND(" ", "36 Months"))), TODAY(), "DAILY"), "select Col2 offset 1", 0)

What I want to do is have the data end on the same row. The issue is that not all stocks have the same number of data points as some are younger than others. For this example, MUTF:DOXGX oldest start date is May 6, 2022; approximately 34 months ago.

Here is how the table generates, where "..." is a skip in rows:

I'm trying to display multiple stocks in a single table. I have the following formula For A2:

=QUERY(GOOGLEFINANCE(B1, "close", EDATE(TODAY(), -LEFT("36 Months", FIND(" ", "36 Months"))), TODAY(), "DAILY"), "offset 1", 0)

And the following formula for C2:

=QUERY(GOOGLEFINANCE(C1, "close", EDATE(TODAY(), -LEFT("36 Months", FIND(" ", "36 Months"))), TODAY(), "DAILY"), "select Col2 offset 1", 0)

What I want to do is have the data end on the same row. The issue is that not all stocks have the same number of data points as some are younger than others. For this example, MUTF:DOXGX oldest start date is May 6, 2022; approximately 34 months ago.

Here is how the table generates, where "..." is a skip in rows:

As you can see, it shows that the MUTF:DOXGX stock ends on 2/20/2025, but that isn't true.

What I want to have instead is that the values for MUTF:DOXGX starts on its oldest date, which would be 5/6/2022. I figure that how this would work would be to take the difference between the last row of each column using =MAX(FILTER(ROW(C3:C), C3:C <> "")) - MAX(FILTER(ROW(B3:B), B3:B <> "")) then somehow using that returned value to tell the QUERY formula to just move the data down that many rows?

Edit: As per the suggestion of @leylou, here is my input values:

Here is my desired output:

• google-sheets
• google-sheets-formula
• google-finance

• Please provide sample input data and desired output. You may create one with the help of this link. – leylou Commented Mar 27 at 17:36
• What you can do here is to list all of the dates from 3 years ago up to today and then use a BYROW and VLOOKUP to search for the values for the corresponding dates. However, this would consume 1098 rows. We can add a filter afterwards to remove the rows with no data. – PatrickdC Commented Mar 27 at 18:03

1 Answer 1

You may try this and place it on A2 (as this formula already generates all columns including the dates):

=LET(dates, ARRAYFORMULA(TO_DATE(SEQUENCE((365*3)+1,1,TODAY()-(365*3)-1 + (16/24),1))),
     svspx, GOOGLEFINANCE(B1, "close", EDATE(TODAY(), -36), TODAY(), "DAILY"),
     svspxdata, IFERROR(BYROW(TOCOL(dates,1), LAMBDA(x, VLOOKUP(x,svspx,2,0)))),
     doxgx, GOOGLEFINANCE(C1, "close", EDATE(TODAY(), -36), TODAY(), "DAILY"),
     doxgxdata, IFERROR(BYROW(TOCOL(dates,1), LAMBDA(x, VLOOKUP(x,doxgx,2,0)))),
     QUERY(HSTACK(dates,svspxdata, doxgxdata),"SELECT * WHERE Col2 IS NOT NULL"))

given that B1 is MUTF:SVSPX and C1 is MUTF:DOXGX. I do not wish to add the output as my output produced 744 rows (not unless that is an issue to others).

NOTE: Changing the "DAILY" input to "WEEKLY" produces 154 rows.