Get Latest Storm Information in #Excel Using Only An Address and Storm Name

 

I recently published this article about the direction and distance of a tropical system from an address.

https://dhexcel1.wordpress.com/2017/09/12/using-excel-to-find-how-far-the-storm-is-from-your-location/

Go back and read this article to understand the first part of the model associated with the address (entered manually in cell B1).

Originally I wanted to include the abilty to add the storm coordinates from an internet source but I could not find one, so manually entering them was necessary. However, later I stumbled across just the xml source I was looking for from the National Hurricane Center site at NOAA. It only gives the current information on a storm, so it gets “stepped on” with each new advisory. This information is for storm number 15 for the 2017 Altantic hurricane season, which hhappens to be Maria.

http://www.nhc.noaa.gov/storm_graphics/AT15/atcf-al152017.xml

In order to access more than one system, I replaced the number with a defined name function called StormNum, which is then used as the URL in the WEBSERVICE function to return the desired xml document.

=WEBSERVICE(“http://www.nhc.noaa.gov/storm_graphics/AT”&StormNum&”/atcf-al”&StormNum&”2017.xml”)

I also needed a list of the storms for 2017. One complication was that the NHC now gives Potential Tropical Cyclones a number like those given to all tropical lows. Then, if the PTC does not develop into a low pressure system, it will not show up in online lists, like for example, Wikipedia. However, I was able to find a site that did include PTCs and I used Power Query to get the table containing the desired information (see worksheet SysNames). I massaged the column containing the storm designations as shown in column G. The formula used for this is =IF(PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))=””,”Not Yet”,PROPER(TRIM(RIGHT(SUBSTITUTE(A3,” “,REPT(” “,100)),100)))). Shown below:

 

The source from the PQ M code is:

=Web.Page(Web.Contents(“http://www.theweatherguys.com/index.php?config=&forecast=tropsystems&alt=tropallsystems”))

Now that I had the list of storms, I was able to construct the StormNum defined name formula as follows:

=IF(Storm_Number<10,”0″&Storm_Number,Storm_Number)

where Storm_Number =MATCH(DirectionCalc!$B$2,Storm_List,0). Cell B2 contains a data validation list with the storm names derived from the PQ.

Now, the FILTERXML function can extract various pieces of information from the xml doucment defined as Storm, as shown in the following figure.

 

For example, the storm latitude (in cell C4) is =FILTERXML(Storm,”//centerLocLatitude”).

The final formula for the storm message is:

=ROUND(Distance,0)&” miles “&TextDirection&” of “&Address&”, moving “&MID(FILTERXML(Storm,”//systemDirectionOfMotion”),1,FIND(“OR”,FILTERXML(Storm,”//systemDirectionOfMotion”))-1)&”at “&FILTERXML(Storm,”//systemSpeedMph”)&” mph.”

Remember, do no use this model in any corporate or commercial manner (only for personal use).

You can download the file here.

DirectionFromStorm_AutoLookup