I use a formula like this to get stock data:
=IMPORTDATA("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY_EXTENDED&symbol=IBM&interval=15min&slice=year1month1&apikey=demo")
Basically my goal is to filter the data by date and time with a query formula, so the table with the raw data does not show up.
I want the data from Column 1, Column 3 and Column 6, filtered by date (in this case 7/12/2022 from cell J1) and time (between 4:15:00 and 9:30:00).
I tried this formula
=QUERY(IMPORTDATA("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY_EXTENDED&symbol=IBM&interval=15min&slice=year1month1&apikey=demo"),"select Col1, Col3, Col6 WHERE Col1 > datetime '"&TEXT(J1 time(4,15,0),"yyyy-mm-dd HH:mm:ss")&"' and Col1 <= datetime '"&TEXT(J1 time(9,30,0),"yyyy-mm-dd HH:mm:ss")&"'")
but the only result I can get are the headers.
Here is a link to the Sheet
CodePudding user response：
Answer
The following formula should produce the result you desire:
=QUERY(IMPORTDATA("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY_EXTENDED&symbol=IBM&interval=15min&slice=year1month1&apikey=demo"),"SELECT Col1, Col3, Col6 WHERE Col1 > "&J1 TIME(4,15,0)&" AND Col1 <= "&J1 TIME(9,30,0))
For easier reading, here is the second argument of =QUERY isolated.
"SELECT Col1, Col3, Col6 WHERE Col1 > "&J1 TIME(4,15,0)&" AND Col1 <= "&J1 TIME(9,30,0)
Explanation
Behind the scenes, all date and time values in Google Sheets are stored as simple numbers. Therefore, simple number comparison can be used in a query to determine if one date is greater than another, skipping a lot of the in-between with the =TEXT function and the datetime argument. The provided =QUERY simply compares each value in Col1 to the sum of J1 and your provided time value.
Functions used: