0

The formula in cell A2 produces the following: GOOGLEFINANCE Tab

How do I create the following results table in a new tab? I want to:

  1. Copy the data from the GOOGLEFINANCE Tab column B to the RESULTS Tab corresponding year column.
  2. Update the date in the GOOGLEFINANCE Tab cell B2.
  3. Repeat steps 1 and 2 until there is no more data in the GOOGLEFINANCE Tab column B. RESULTS Tab

In addition I just noticed that the results are only returning through December 30th. Any idea why it is not returning December 31st?

6
  • I am pulling the data in the first three tables from another database and I can only do it for one year at a time, plus there is other data I don't need. I can't simply start with the results table. I am looking to store the calculations from the %G/L column into the results table, update the year (so the formulas can run again and create the second table) store those %G/L results, and repeat till there is no new results.
    – Lon
    Commented May 3 at 14:11
  • For clarification Tab1 has a formula that creates one table at a time (2023, 2022, ..., N). Once I update the date I lose the data for the previous year. I want Tab2 to copy Tab1 column C and paste into Tab 2 column B, then C, then ..., N.
    – Lon
    Commented May 3 at 15:29
  • 1
  • 1
    Your cross-post timing coincides with your first comment in response to my request that you improve/edit your question. You've now restated it 3 times. If you are interested in a solution it would be more productive to improve your initial question based on the feedback provided.
    – Blindspots
    Commented May 4 at 4:47
  • Thank you for your comments. I will likely delete this post and do a new one with the actual data so my question is more clear.
    – Lon
    Commented May 4 at 9:09

1 Answer 1

0

Use query() with a pivot clause, like this:

=let( 
  data, googlefinance("BTCUSD", "close", "2019-01-01", "2024-01-01"), 
  dates, choosecols(data, 1), 
  years, arrayformula(year(dates)), 
  dateLabels, arrayformula(text(dates, "MM/dd")), 
  query( 
    hstack(data, years, dateLabels), 
    "select Col4, max(Col2) group by Col4 pivot Col3", 
    1 
  ) 
)

See let(), choosecols(), arrayformula(), query() and hstack().

1
  • Great, thank you. The query() is way over my head but was able to make it work.
    – Lon
    Commented May 4 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.