can't use webservice function in excel to get xml data

  • 1
  • Question
  • Updated 11 months ago
  • (Edited)
I am trying to use webservice function in excel to get weather data in xml format. 

My formula returned #value!, and I couldn't figure out why. Here is my formula - 

 =webservice("http://api.wunderground.com/api/KEY/conditions/q/CA/San_Francisco.json") 

If I paste the http://api.wunderground.com/api/KEY/conditions/q/CA/San_Francisco.json to a web browser, I do can access the info.

Could anyone please help? Thanks a lot!
Photo of Lei Lei

Lei Lei

  • 1 Post
  • 0 Reply Likes

Posted 3 years ago

  • 1
Photo of afelicioni

afelicioni

  • 227 Posts
  • 43 Reply Likes
Made a quick attempt to get this task working under LibreOffice, but should be portable to Excel... First I think you need to ask for the XML output.
Using the following formula, I am able to get current temperature string on a cell
=FILTERXML(WEBSERVICE("http://api.wunderground.com/api/__YOUR_KEY_HERE__/conditions/q/CA/San_Francisco.xml");"/response/current_observation/temperature_string")
(Edited)
Photo of Cameron Stewart

Cameron Stewart

  • 1 Post
  • 0 Reply Likes
I am also attempting to do the same and return the same error.

If I use the query string:
http://api.wunderground.com/api/KEY/forecast/geolookup/q/nz/takanini.xml

I get valid data in excel.

If i attempt to look for a historic set, It doesnt work for some reason :/
http://api.wunderground.com/api/KEY/history_20170101/q/nz/takanini.xml

Both querys work when tested in the browser!

:] halp!
(Edited)