CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsfinancealpha-vantageexcel-365
CuriousDev
CuriousDev

Reputation: 1275

Price at Particular Date and Time

How to I query price at a particular date and time using Alphavantage API.

For eg: I tried this:

https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=^INFY&interval=1min&outputsize=compact&apikey=***********

However I want to pass both Date and Time and need the HIGH for that particular minute for that symbol.

I am using the Excel 365 Add-On but I can use Google Sheets as well.

Possible?

Upvotes: 1

Views: 371

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

The url will give you a json. You have then to parse it and then apply a formula to retrieve the max value and date/hour/minute it occurs. To parse the json, try with your own api key :

function getAllDataJSON(code) {
  var url = 'https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol='+code+'&interval=1min&apikey='+YOURAPIKEY
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())['Time Series (1min)']
  var resultat = []
  for (var elem in eval(data)){
    resultat.push([elem,eval(data[elem]['1. open']),eval(data[elem]['2. high']),eval(data[elem]['3. low']),eval(data[elem]['4. close']),eval(data[elem]['5. volume'])])
  }
  return resultat
}

If you want extended period, the answer will be a csv file

function getAllDataCSV(code){
  // last month : slice=year1month1 (by default) ... until slice=year2month12 (farthest month from today)
  // interval : 1min
  var url = 'https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY_EXTENDED&slice=year1month1&symbol='+code+'&interval=1min&apikey='+apikey
  var csv = UrlFetchApp.fetch(url).getContentText();
  return Utilities.parseCsv(csv)
}

Upvotes: 1

Related Questions