1

I am pulling data from google finance to google sheet using below formula:

=GOOGLEFINANCE("NASDAQ:AAPL","price",TODAY()-10,TODAY())

and the result is like below

pic

what i want the result to be like is below so that when i put new stock in next row result show be like below

pic2

is there any google sheet expert who can help me as i am new to google sheet and searching on internet haven't haven't me any solution.

player0
  • 124,011
  • 12
  • 67
  • 124
Zac
  • 323
  • 7
  • 14

1 Answers1

2

paste this in B1:

=ARRAYFORMULA(TEXT(SEQUENCE(1, 10, TODAY()-9, 1), "dd/mm/yyyy"))

enter image description here

and this in B2 and drag down:

=ARRAYFORMULA(IFNA(HLOOKUP(B$1:K$1, TRANSPOSE(TEXT(
 GOOGLEFINANCE("NASDAQ:"&A2, "price", TODAY()-10, TODAY()), 
 {"dd/mm/yyyy", "@"})), 2, 0)))

enter image description here

update:

to exclude weekend you can do:

=INDEX(TRANSPOSE(QUERY(TEXT(SEQUENCE(10, 1, TODAY()-9, 1), 
 {"dd/mm/yyyy", "ddd"}), "where not Col2 matches 'Sat|Sun'")), 1)

enter image description here

however to exclude holidays it needs to be done like this:

=INDEX(TRANSPOSE(QUERY(TEXT(SEQUENCE(10, 1, TODAY()-9, 1), 
 {"dd/mm/yyyy", "ddd"}), 
 "where not Col2 matches 'Sat|Sun' 
    and not Col1 matches '"&TEXTJOIN("|", 1, M2:M)&"'")), 1)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you its working but is there a way to change date for both formula at same time and is there a way to ignore holidays as well – Zac Jan 04 '21 at 21:06
  • thank you its works. last question how do i change data like if i want stock price of last 30 days from today. what value i have to change, please share screenshot. – Zac Jan 04 '21 at 21:35
  • in B2 formula you change range `B$1:K$1` to include 30 columns and `TODAY()-10` to `TODAY()-30` – player0 Jan 04 '21 at 21:48
  • in B1 formula you change `SEQUENCE(10, 1, TODAY()-9` to `SEQUENCE(30, 1, TODAY()-29` – player0 Jan 04 '21 at 21:49