Category Archives: web

#Excel Short and Sweet Tip #29: Inserting Icons Using a User-Defined Function

Disclaimer: You need the Excel version included in Office 365 for this technique to work.

The insertion of icons in Excel 2016 is accomplished from the ribbon by selecting Insert, Icons. There are a number of catagories to select from, as shown in ths figure.

MakeIcon1

However, recently I have been interested (obsessed?) with worksheet UDFs and their ability to invoke actions or shapes. In this case, I wanted to see if a UDF would insert an icon into the worksheet. This is the VBA function I made with help of the macro recorder. Place this in a general module in your worksheet.

Function MakeIcon(fName As String)

iString =        “https://hubblecontent.osi.office.net/ContentSVC/Content/Download?provider=MicrosoftIcon&

fileName=” & fName & “.svg”

ActiveSheet.Pictures.Insert iString

End Function

Now, enter the formula =MakeIcon(“Man”) in cell A1 and you will get the following result.

MakeIcon

Unfortunately, there are several inherent Excel limitations that prevent the full utilization of this function. First, you have to know the correct name of the icon to produce it. It would be nice if Microsoft provided a list of the icon names, but I could not locate one. Then, I tried to get names by macro recording the insertion of multiple icons, but only the “last” selected icon URL is recorded. Even so, I hope that this technique is useful to you.

The example file can be downloaded here.

MakeIcon

Advertisements

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:

 StormUpdate2

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.

 StormUpdate1

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

Bible Verse In Any Language Using #Excel by David Hager

 

I recently demonstrated what became a popular Excel technique – looking up a Bible verse using Excel’s web functions. On LinkedIn, I received a comment from Raul stating that he did not understand English, only Spanish. So, I decided to add language translating to the model. I tried to find a (free) way to do this directly from an API, but I could not. I turned instead to the translation technique I had already published which use a VBA procedure. I combined both of the methods as demonstrated in the links shown below.

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

https://dhexcel1.wordpress.com/2017/07/03/lookup-a-bible-verse-using-excel-wo-vba-by-david-hager/

You can read both of these articles to see the details of how each was constructed. Meanwhile, the key formula in cell H5 is:

=Translate(mString,LangCode)&T(NOW())

where mString = IF(ISERROR(FIND(“/b>”,oString)),oString,MID(oString,FIND(“/b>”,oString)+3,255))

and oString = FILTERXML(WEBSERVICE(“http://labs.bible.org/api/?passage=”&TheBook&&#8221; “&TheChapter&”:”&TheVerse&”&type=xml”),”//text”)

and LangCode = INDEX(LanguageCodes,MATCH(Language,LanguageNames,0))

Use the dropdowns in H2:J2 to select verse and language.

BibleVerseMultLanguages1

Sometimes the query has to be run twice in order to work. I have not been able to solve this problem, so please run the query a 2nd time if the cell containing the verse is blank. Alternatively, if you click in the formula in H5 and press Enter, it should calculate as desired. The problem is likely due to the Excel web functions becoming confused during recalculation. I attempted to correct this by adding &T(NOW()) to the end of the formula in cell H5. It appeared to help, but I cannot guarantee it. You can also try pressing Ctrl-Alt-F9 for recalculation.

This should be useful to everyone worldwide.

You can download the file here.

BibleVerseMultLanguages

#Excel: Getting the Sunrise and Sunrise Times From an Address by David Hager

A while back I worked for quite some time on returning the sunrise and sunset from an address using VBA. I was not successful, so I shelved that project. But, after publishing several popular articles using Excel’s web functions, I decided to revisit this project using those functions. As always, implementation of this technique requires the appropriate xml data sources and the corresponding return values. I could not locate a single xml source to do this, so I finally found 3 xml sources that, if combined, would theoretically provide me with the desired result. Here are the 3 API sources.

http://maps.googleapis.com/maps/api

http://api.timezonedb.com/ ‘Requires a free API key

http://new.earthtools.org/

The 1st API takes a detailed address and returns latitude and longitude for that location. The 2nd API takes latitude and longitude as input and returns a daylight savings time (dst) value and a GMT offset (gmtOffset) value. The 3rd API takes latitude, longitude, dst, gmtOffset and the current day and returns the sunrise and sunset times. For the demonstration, address is a defined name formula.

address =”4163 W. Sparrow St., Orange, Tx.”

However, a named cell could also be used to contain the address.

The two formulas needed to return latitude and longitude from googleapis.com are

latitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false&#8221;),”//lat”)

longitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false&#8221;),”//lng”)

Note that the Excel ENCODEURL function is needed to convert the address to a form compatible with the API.

The latitude and longitude coordinates are then used as parameters in the timezonedb.com API to return values for daylight savings time and GMT offset. An API key is required by the web site owner, but you can register and get a free one at https://timezonedb.com/register. The API key is stored in the defined name API_Key.

dst = FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&”&#8221;),”//dst”)

gmtOffset = (FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&”&#8221;),”//gmtOffset”)/3600)-1

Finally, all of the parameters from the previous 2 APIs plus the TODAY function are used in the earthtools.org API to reurn sunrise and sunset times for the specified location.

sunrise= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunrise”),”hh:mm:ss”)

sunset= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunset”),”hh:mm:ss”)

Note that the date parameters can be changed if the day to get results for is not today.

The results of the formulas used in this technique can be seen in this figure.

SunriseSunset1

The formulas have been converted to values in the model due to my inability to share my API key, but you add back in what you want to see with the formulas described in this article (and in defined names).

You can download the file here.

SunriseSunsetInfo

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