Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager

 

The key to returning valuable information to Excel is to find sources of xml data. In this example, the xml data is supplied by

https://earthquake.usgs.gov

The goal is to make an alert message for the latest strong earhquake occurring worldwide. The following 3 formulas return the information needed for that message.

Location: =FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//description/text”)

Magnitude: =FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//mag/value”)

Time: =TEXT(FILTERXML(WEBSERVICE(“https://earthquake.usgs.gov/fdsnws/event/1/query?format=xml&starttime=”&TEXT(TODAY(),”yyyy/mm/dd”)&”&endtime=”&TEXT(TODAY()+1,”yyyy/mm/dd”)&”&minmagnitude=5″),”//time/value”), “yyyy/mm/dd h:m”)&” UTC”

The WEBSERVICE function brings in the xml content from the web site and the FILTERXML function finds the desired data based on the node used in the 2^nd argument.

After these formulas return the desired data for the latest earhquake with a magnitude greater than 5.0, as shown in the figure

 

the TEXTJOIN function can be used to construct a readable alert message. The following array formula in cell B5 affords a formatted message from the cells A1:B3.

=TEXTJOIN(IF(COLUMN(A1:B3)=1,”: “,”; “),,A1:B3)

An important feature of this formula is the use of different delimiters based on the column where the information resides.

I hope that you find these techniques useful.

You can download the example file here.

earthquake_xml