Alpha Vantage API Excel

Question

I’ve got an Alpha Vantage API key and am having issues with parsing the data. I’m unsure how to approach parsing the data as there are JSON and CSV options. The CSV seems to be a download, I’m not trying to manually download a new file every time I want to update the stock price.

This is how I used to get the prices of stocks in Excel:

I would always use CTRL-ALT-F9 to refresh everything.

Unfortunately, Yahoo has stopped this service so I have to fix it.

Strath

Answer 1

You can use Alpha Vantage the same way. But it gives you more information for each call so you need to parse it. To get the latest stock price for example, this works:

=NUMBERVALUE(MID(WEBSERVICE("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&apikey=<key>&datatype=csv&symbol=MSFT&outputsize=compact"),49,6))

Defense

Thank you for your help, I really appreciate that! – Strath