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 2nd argument.

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

 Equake1

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

 

Advertisements

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

  1. Pingback: #Excel Super Links #100 (Special Edition) – shared by David Hager | Excel For You

  2. Pingback: #Excel Super Links #108 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s