0

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

PhilNos
  • 15
  • 5
  • First thing i can tell you is that if you use the IMPORTDATA function, you'll likely hit your daily quota from Alphavantage by around 11am. – MattKing Jul 16 '22 at 18:45
  • Doesn't the stock market open at 930? it makes sense there would be no IBM data before 930 am – MattKing Jul 16 '22 at 18:46
  • I use another provider with unlimited date limit, I just used alphavantage in this example beacuse of the easy to use demo api. – PhilNos Jul 16 '22 at 19:40
  • Yes, the general stock market opens at 9:30 but there is pre market trading starting at 4:00, lasting until 9:30 and thats the time window where I need the data from. – PhilNos Jul 16 '22 at 19:44

1 Answers1

2

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:

Sebastian Smiley
  • 831
  • 4
  • 15