google-sheetsarray-formulasgoogle-finance
Google Sheets, populate with multiple arrays
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?
Solution
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"))))
- ARRAYFORMULA option for checking for cell values on each row of Google Sheet
- Filter a range by array
- Google Sheet Table in one row
- Google Sheets Import CSV popup
- Calculating Average of last 10 Data Entries while Omitting Zeros
- Fastest way to search for a row in a large Google Sheet using/in Google Apps Script
- Google Apps Script showing previous day
- Automatically transfer data from the same cell in different sheet tabs to one table
- How to use the function .getLastRow() in google script
- Updating appscript dropdown in sidebar after a successful operation
- Getting Range not found when using getRange()
- How to generate an uuid in google sheet?
- Improve performance of onEdit simple trigger
- Query and Importrange function with conditions
- Google Apps Script attempting to utilize OAuthScopes is giving an error with no details and prevents saving
- Why can't I format my dates after they are returned from this function?
- Appscript Google sheets returns null
- RegExtract a value from a comma-separated string
- Conditional formatting if a cell is a duplicate value
- Google Sheets Image() function now requires users to hit Allow Access
- Can I use Google Apps Script instead of Formulas for doing calculations on a spreadsheet?
- Is it possible to display an iframe or webview in google sheets
- In the same Google Sheet Workbook, can you extract the info from a cell in Sheet A to a cell in Sheet B, and link it back to the same cell in Sheet A?
- GSPREAD bulk export of dataframes on separate sheets without triggering API write request limits
- How do I configure Apps Script time-based Triggers to execute successfully?
- Write the count files in folder to a spreadsheet
- Adding message to email when using addEditor()
- Regular expression for Unique Sort finding phone extensions from a multicolumn list
- Spreadsheet formulas calculation control
- Is there a way to keep two sheets synchronized?